declare
idnumber NEW_CPARCH.ISCODE%TYPE;
CPCODEd NEW_CPARCH.CPCODE%TYPE;
begin
select ISCODE into idnumber from NEW_CPARCH;
select CPCODE into CPCODEd from NEW_CPARCH;
Case LENGTH(idnumber)
when 18 then
if((ascii(substr(idnumber,17))>57 and ascii(substr(idnumber,17))<65) or ascii(substr(idnumber,17))>90 or ascii(substr(idnumber,17))<48) then
DBMS_OUTPUT.put_line(idnumber||'身份证号有误');
end if;
when 17 then
if(ascii(substr(idnumber,16))>57 or ascii(substr(idnumber,16))<48) then
DBMS_OUTPUT.put_line(idnumber||'身份证号有误');
end if;
when 15 then
if(ascii(substr(idnumber,14))>57 or ascii(substr(idnumber,14))<48) then
DBMS_OUTPUT.put_line(idnumber||'身份证号有误');
end if;
end case;
end;根据 假设人员档案(PSARCH)内有一字段 ISCODE(身份证编号)为15、17或18位。如字段ISCODE为18位,则第18位可以为字母;反之只能是数字。用SQL写一段程序,选出此表内身份证号码不合法的人员的信息。要求写有足够的中文注释。来写的,为什么一直显示的都是when 18的结果,哪里错了
idnumber NEW_CPARCH.ISCODE%TYPE;
CPCODEd NEW_CPARCH.CPCODE%TYPE;
begin
select ISCODE into idnumber from NEW_CPARCH;
select CPCODE into CPCODEd from NEW_CPARCH;
Case LENGTH(idnumber)
when 18 then
if((ascii(substr(idnumber,17))>57 and ascii(substr(idnumber,17))<65) or ascii(substr(idnumber,17))>90 or ascii(substr(idnumber,17))<48) then
DBMS_OUTPUT.put_line(idnumber||'身份证号有误');
end if;
when 17 then
if(ascii(substr(idnumber,16))>57 or ascii(substr(idnumber,16))<48) then
DBMS_OUTPUT.put_line(idnumber||'身份证号有误');
end if;
when 15 then
if(ascii(substr(idnumber,14))>57 or ascii(substr(idnumber,14))<48) then
DBMS_OUTPUT.put_line(idnumber||'身份证号有误');
end if;
end case;
end;根据 假设人员档案(PSARCH)内有一字段 ISCODE(身份证编号)为15、17或18位。如字段ISCODE为18位,则第18位可以为字母;反之只能是数字。用SQL写一段程序,选出此表内身份证号码不合法的人员的信息。要求写有足够的中文注释。来写的,为什么一直显示的都是when 18的结果,哪里错了
这个NEW_CPARCH只有一个值吗,这样写不报错?不搞个游标循环下?
create table t1 (id number,peo varchar2(18));insert into t1 values (1,'421024198902193456');
insert into t1 values (2,'42102419890219345x');
insert into t1 values (3,'4210241989026');
insert into t1 values (4,'421024198902195');
insert into t1 values (5,'4210241989219dddd5');
insert into t1 values (6,'4210241989219123');
select case when regexp_like(peo,'^(^\d{15}$|^\d{18}$|^\d{17}(\d|X|x))$') then peo else '身份证号:'||peo||'格式错误' end peo
from t1 peo
-------------------------------------------
1 421024198902193456
2 42102419890219345x
3 身份证号:4210241989026格式错误
4 421024198902195
5 身份证号:4210241989219dddd5格式错误
6 身份证号:4210241989219123格式错误
游标先不急我先看看一条记录能不能实现
idnumber NEW_CPARCH.ISCODE%TYPE;
cursor c is
select ISCODE from NEW_CPARCH;
begin
open c;
loop
FETCH c into idnumber;
if regexp_like(idnumber,'^(^\d{15}$|^\d{18}$|^\d{17}(\d|X|x))$') then
dbms_output.put_line(idnumber);
else
dbms_output.put_line('身份证号:'||idnumber||'格式错误');
end if;
exit when c%notfound;
end loop;
close c;
end;为什么出现重复身份证号:2324234234232234f 格式错误
234324324234324234
身份证号:66666666666666f 格式错误
身份证号:66666666666666f 格式错误666666666666666f我只放了一条啊
idnumber NEW_CPARCH.ISCODE%TYPE;
cursor c is
select ISCODE from NEW_CPARCH;
begin
open c;
loop
exit when c%notfound;
idnumber := null;
FETCH c into idnumber;
if(idnumber is not null) then
if regexp_like(idnumber,'^(^\d{15}$|^\d{18}$|^\d{17}(\d|X|x))$') then
dbms_output.put_line(idnumber);
else
dbms_output.put_line('身份证号:'||idnumber||'格式错误');
end if;
end if;
end loop;
close c;
end;
这个只是针对数字简单的判断 如果要详细的话 还要把数字分解 什么省市 年月 啥的 比较麻烦了 一般情况也不用这么严格