Oracle第一个存储过程。。。不容易

来源：互联网发布：windows 活动目录编辑：程序博客网时间：2024/05/01 06:36

 create or replace procedure registerto(e_code in  varchar2,e_name in  varchar2,e_inname in varchar2,e_id in varchar2)ist_gorup073 int;t_emp05 int;t_code varchar2(10) :=substr(e_code,3,2);cursor csr_emp     is     Select RKEY       From data0005      Where EMPL_CODE = e_code;     row_emp csr_emp%rowtype;cursor csr_code     is     select rkey from data0073 where user_id like '%18MIS%';     row_code csr_code%rowtype;cursor csr_code1     is     select rkey from data0073 where user_id like '%18'||substr(e_code,3,2)||'%';     row_code1 csr_code1%rowtype;begin   -- select count(*) into t_count from data0005 where empl_code=e_code;  -- if(t_count==0)  --{  --Review Input Information                  insert into data0005 (RKEY, EMPL_CODE, EMPLOYEE_NAME, ABBR_NAME, EMPLOYEE_ID, ADDRESS_LINE_1, ADDRESS_LINE_2, ADDRESS_LINE_3, STATE, ZIP, PHONE, NOTE_PAD_POINTER, PAY_RATE_1, PAY_RATE_2, PAY_RATE_3, START_TIME_1, START_TIME_2, START_TIME_3, ACTIVE_FLAG, BUYER_FLAG, INACTIVE_DATE, USER_ID)        values (Seq_0005.nextval,e_code,e_name,e_inname,e_id, '100,DONGJINGTING,ZHONGYANGMEN ', 'YANGZHOU ', 'JIANGSU', 'JS ', '0020230003', '8625-5600305  ', 537, 1.0000000, 0.0000000, 0.0000000, 80000, 0, 0, 'Y', 'N', to_date('12/13/2011', 'mm/dd/yyyy'), null);               open csr_emp;        fetch csr_emp into row_emp;          t_emp05 :=row_emp.rkey;       dbms_output.put_line('data0005 rkey:='||t_emp05);           --select rkey into t_emp05 from data0005 where emoloyee_name=e_name;     --  t_code :=substr(e_code,3,2);        -- case t_code               --  when 'MS' then select rkey into t_group073 from data0073 where user_id like '%18MIS%';                -- else select rkey into t_group073 from data0073 where user_id like '%'||substr(e_code,1,4)||'%';        -- end case;                      dbms_output.put_line('t_code:---'||t_code);          open csr_code;        fetch csr_code into row_code;             open csr_code1;        fetch csr_code1 into row_code1;        case t_code          when 'MS' then            t_gorup073 :=row_code.rkey;            else         t_gorup073 :=row_code1.rkey;              end case;        dbms_output.put_line('0073 rket='||t_gorup073);                    insert into data0073 (RKEY, USER_ID, USER_FULL_NAME, USER_LOGIN_NAME, USER_PASSWORD, USER_STATION, USER_GROUP_FLAG, LANGUAGE_FLAG, GROUP_PTR, DEF_PRINTER_PTR, EMPLOYEE_PTR, NETWORK_ID, NODE_ID, SESSION_ID)        values (Seq_0073.nextval,e_code,e_name,e_inname, null, 0, 2, 'E',t_gorup073, -1,t_emp05, ' ', ' ', 0);  -- }  --else   -- {   --   e_code :='erro';    --}      if t_emp05 is not null and t_gorup073 is not null then    commit;    end if; close csr_emp; close csr_code; close csr_code1; end registerto;

调用如下：

begin  -- Call the procedure  registerto('31EM8','GAO LIANG8','LGAOYZ8','YS00098');end;