create or replace function GET_ADDRESSNAME(STRCODE in VARCHAR2)
return varchar2 is
Result varchar2(100);
tempAddress varchar2(100);
begin
tempAddress := '';
SELECT T.ADDRESS
INTO tempAddress
FROM ADDRESSCODE T
WHERE T.ADDRCODE = STRCODE;
Result := tempAddress;
return Result;
end GET_ADDRESSNAME;
create or replace function FUN_PID15TO18(pid15 in char) return char is
TYPE array_17_number IS VARRAY(17) OF NUMBER;
TYPE array_11_char IS VARRAY(11) OF char;
Result varchar2(18);
v_check_number integer := 0;
v_check_char char(1);
v_factor array_17_number := array_17_number(7,
9,
10,
5,
8,
4,
2,
1,
6,
3,
7,
9,
10,
5,
8,
4,
2);
v_mod array_11_char := array_11_char('1',
'0',
'X',
'9',
'8',
'7',
'6',
'5',
'4',
'3',
'2');
begin
if (length(pid15) = 18) then
return pid15;
elsif (length(pid15) = 15) then
result := substr(pid15, 1, 6) || '19' || substr(pid15, 7, 9);
FOR i IN 1 .. 17 LOOP
v_check_number := to_number(substr(result, i, 1)) * v_factor(i) +
v_check_number;
END LOOP;
v_check_number := mod(v_check_number, 11);
v_check_char := v_mod(v_check_number + 1);
result := result || v_check_char;
return result;
else
raise_application_error(-20001, 'Length of pid should be 15 or 18!');
end if;
end FUN_PID15TO18;
//存儲過程
create or replace procedure PROC_ADD_T1 is
v_sqlerrm varchar2(500);
BEGIN
FOR i IN 1 .. 100000 LOOP
INSERT INTO T1 (T1C1, T1C2) VALUES ('TEST' || i, '123456');
END LOOP;
--UPDATE T1 SET T1C1 = '0';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_sqlerrm := SUBSTR(SQLERRM, 1, 300);
DBMS_OUTPUT.put_line('ERR=' || v_sqlerrm);
ROLLBACK;
end PROC_ADD_T1;
create or replace procedure PROC_PID15TO18(pid in varchar2, tabName in varchar2) is
v_sqlerrm varchar2(500);
v_sql varchar2(200);
BEGIN
v_sql:='UPDATE ' || tabName || ' SET ' || pid || '=' ||
' CASE WHEN LENGTH(' || pid || ')=15 THEN ' ||
'FUN_PID15TO18(' || pid || ')' ||
' WHEN LENGTH(' || pid || ')=18 THEN ' ||
pid ||
' ELSE ' ||
'''000000000000000000''' ||
' END ';
EXECUTE IMMEDIATE v_sql;
COMMIT;
EXCEPTION
when others then
v_sqlerrm := substr(SQLERRM, 1, 300);
dbms_output.put_line('ERR=' || v_sqlerrm);
rollback;
end PROC_PID15TO18;
create or replace procedure PRO_GETREPORT_PEOPLE is
TYPE cursor_typ IS REF CURSOR;
TYPE array_age_char1 IS VARRAY(7) OF NUMBER;
TYPE array_age_char2 IS VARRAY(7) OF NUMBER;
TYPE array_age_char3 IS VARRAY(7) OF NUMBER;
TYPE array_age_char4 IS VARRAY(7) OF NUMBER;
TYPE array_age_char5 IS VARRAY(7) OF NUMBER;
TYPE array_age_char6 IS VARRAY(7) OF NUMBER;
TYPE array_age_char7 IS VARRAY(7) OF NUMBER;
TYPE array_age_char8 IS VARRAY(7) OF NUMBER;
TYPE array_age_char9 IS VARRAY(7) OF NUMBER;
TYPE array_age_char10 IS VARRAY(7) OF NUMBER;
TYPE array_age_char11 IS VARRAY(7) OF NUMBER;
TYPE array_age_char12 IS VARRAY(7) OF NUMBER;
TYPE array_age_char13 IS VARRAY(7) OF NUMBER;
TYPE array_age_char14 IS VARRAY(7) OF NUMBER;
v_sqlerrm varchar2(500);
strSql varchar2(1000);
cur cursor_typ;
type L_EMP_RECORD is record(
SEX varchar2(50),
AGE number);
L_EMP L_EMP_RECORD;
v_age1 array_age_char1 := array_age_char1(0, 0, 0, 0, 0, 0, 0);
begin
strSql := 'SELECT * FROM (SELECT I.SEX SEX,MONTHS_BETWEEN(SYSDATE,I.BIRTHDAY)/12 AGE FROM INDIVIDUAL I,CONTACT C ' ||
'WHERE I.PID=C.PID AND I.NAME=C.NAME ' ||
'AND C.PROVINCE=''33''' || 'AND C.CITY=''04'') DataAll';
open cur for strSql;
loop
FETCH cur
INTO L_EMP.SEX, L_EMP.AGE;
exit when cur%notfound;
IF (L_EMP.SEX = '01' AND L_EMP.AGE > 3 AND L_EMP.AGE <= 7) THEN
v_age1(2) := v_age1(2) + 1;
END IF;
end loop;
close cur;
dbms_output.put_line('3~7：男' || ' ' || v_age1(2));
end PRO_GETREPORT_PEOPLE;
-- Create sequence
create sequence SEQ_ADDRESSCODE
minvalue 1
maxvalue 99999999
start with 1021
increment by 1
cache 20;