我test表中的sfzhm字段中装载了身份证号码,有15位的,也有18位的记录,如张三在test中登记的记录有15位的也有18位的,请问如何才能通过sql语句查询所有所有张三的记录,我的sql语句:select * from where GetID(sfzhm)='张三的十八位身份证号码' 其中GetID是15位转18位的函数,函数能正常使用,可是上述sql语句无法执行,请问大家如何实现?
调试欢乐多
CREATE OR REPLACE FUNCTION GetID(oldCardNumber in varchar2)
return varchar2 is vResult varchar2(18);
TYPE char_type IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER;--定义字符串数组
TYPE int_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;--定义整型数组 strJiaoYan char_type ;--初始化
intQuan int_type;--临时变量
strTemp varchar2(25);
intTemp number;
i number;
n varchar(2);Begin
strJiaoYan(1) := '1';
strJiaoYan(2) := '0';
strJiaoYan(3) := 'X';
strJiaoYan(4) := '9';
strJiaoYan(5) := '8';
strJiaoYan(6) := '7';
strJiaoYan(7) := '6';
strJiaoYan(8) := '5';
strJiaoYan(9) := '4';
strJiaoYan(10) := '3';
strJiaoYan(11) := '2';
intQuan(1) := 7;
intQuan(2) := 9;
intQuan(3) := 10;
intQuan(4) := 5;
intQuan(5) := 8;
intQuan(6) := 4;
intQuan(7) := 2;
intQuan(8) := 1;
intQuan(9) := 6;
intQuan(10) := 3;
intQuan(11) := 7;
intQuan(12) := 9;
intQuan(13) := 10;
intQuan(14) := 5;
intQuan(15) := 8;
intQuan(16) := 4;
intQuan(17) := 2;
intQuan(18) := 1;
if length(oldCardNumber)=18 then
return oldCardNumber;
elsif length(oldCardNumber)=15 then
--身份证年份前加19
n := '19';
strTemp := SUBSTR(oldCardNumber,1,6) || n || SUBSTR(oldCardNumber,7,9);
-- DBMS_OUTPUT.Put_line(strTemp);
i:= 1;
intTemp := 0;
While i<18 loop
intTemp := intTemp + TO_NUMBER(SUBSTR(strTemp,i,1)) * intQuan(i);
--DBMS_OUTPUT.Put_line(intTemp);
i := i+1;
End Loop;
intTemp := MOD(intTemp,11); --DBMS_OUTPUT.Put_line(intTemp+1);
vResult := strTemp || strJiaoYan(intTemp+1);
return vResult;
else
return oldCardNumber;
end if;
End GetID;
2 '01234589123456'
3 end
4 from dual;CASEWHENLENGTH('01234567891234
------------------------------
01234589123456
小试一下,还行,思路就这个吧。把字符串'012345678912345678'改为字段就ok
1、创建表sty_import_temp用于存放导入的数据,将身份证粘贴到表sty_import_temp的字段“sfz”。 建表语句:create table sty_import_temp(sfz varchar2(18),sfz_modify_15 varchar2(18));
2、创建表STY_AC01_TEMP,用于抽取表AC01里边的数据。
建表语句:CREATE TABLE STY_AC01_TEMP(AAC001 NUMBER(20),AAC002 VARCHAR2(18),AAC002_MODIFY_15 VARCHAR2(18));
3、执行plsql程序。
4、用以下语句获取查询结果:
SELECT A.SFZ 导入的原始身份证,
B.AAC001 查询到的个人编号,
B.AAC002 查询到的身份证号码
FROM STY_IMPORT_TEMP A, AC01 B
WHERE DECODE(LENGTH(A.SFZ),
15,
SFZ,
18,
DECODE(LENGTH(A.SFZ),
15,
SFZ,
18,
SUBSTR(A.SFZ, 1, 6) || SUBSTR(A.SFZ, 9, 9))) =
DECODE(LENGTH(B.AAC002),
15,
B.AAC002,
18,
SUBSTR(B.AAC002, 1, 6) || SUBSTR(B.AAC002, 9, 9))
ORDER BY A.SFZ, B.AAC001;
感谢