create or replace procedure llx_22 is
v_bir date;
v_id employ.id%TYPE;
CURSOR c_emp
IS SELECT s_fun12(idcard),id
FROM employ;
BEGIN
OPEN c_emp;
FETCH c_emp INTO v_bir,v_id;
WHILE c_emp %FOUND LOOP
BEGIN
UPDATE employ
SET birthday = v_bir
WHERE id = v_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('找不到ID‘|| v_id||’');
FETCH c_emp INTO v_bir,v_id;
END;
END LOOP;
CLOSE c_emp;
END;
create function s_fun12(card varchar2) return
date is idate date;
begin
IF length(card)=18 THEN
select to_Date('19'||substr(card,7,8),'yyyy-mm-dd') into idate from dual;
ELSIF length(card)=15 THEN
select to_Date(substr(card,7,6),'yyyy-mm-dd') into idate from dual;
ELSE
DBMS_OUTPUT.PUT_LINE('身份证号错误');
END IF;
return idate;
END;
运行不出结果 是不是进了死循环什么的啊 求高手解决 表里面有 id name idcard date
v_bir date;
v_id employ.id%TYPE;
CURSOR c_emp
IS SELECT s_fun12(idcard),id
FROM employ;
BEGIN
OPEN c_emp;
FETCH c_emp INTO v_bir,v_id;
WHILE c_emp %FOUND LOOP
BEGIN
UPDATE employ
SET birthday = v_bir
WHERE id = v_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('找不到ID‘|| v_id||’');
FETCH c_emp INTO v_bir,v_id;
END;
END LOOP;
CLOSE c_emp;
END;
create function s_fun12(card varchar2) return
date is idate date;
begin
IF length(card)=18 THEN
select to_Date('19'||substr(card,7,8),'yyyy-mm-dd') into idate from dual;
ELSIF length(card)=15 THEN
select to_Date(substr(card,7,6),'yyyy-mm-dd') into idate from dual;
ELSE
DBMS_OUTPUT.PUT_LINE('身份证号错误');
END IF;
return idate;
END;
运行不出结果 是不是进了死循环什么的啊 求高手解决 表里面有 id name idcard date
UPDATE employ
SET birthday =
to_date((case length(idcard)
when 18 then substr(idcard,7,8)
when 15 then '19'||substr(idcard,7,6)
else null),'yyyymmdd');
那就把我写的那条SQL外面包一层存储过程好了.
when 18 then substr(idcard,7,8)
when 15 then '19'||substr(idcard,7,6)
else null),'yyyymmdd') from employ;
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('找不到ID‘|| v_id||’');
这句话可能是进入了死循环,没看出那边有给 NO_DATA_FOUND 在哪边定义和赋值,而且一般而言不会把exception放在循环里面吧!
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('找不到ID‘|| v_id||’');
FETCH c_emp INTO v_bir,v_id;
END;
END LOOP; 改下
FETCH c_emp INTO v_bir,v_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('找不到ID‘|| v_id||’');
END;
END LOOP;