oracle存储过程笔记...

oracle存储过程笔记

CREATE OR REPLACE PROCEDURE p_test
is
v_id    TJ_JTCD.Id%TYPE;
v_zgrs  TJ_JTCD.ZGRS%TYPE;
v_qys   TJ_JTCD.QYS%TYPE;
v_date  TJ_JTCD.Createdate%TYPE;
CURSOR   c_emp   IS   SELECT   2,1,1,sysdate   FROM   dual;   --这里创建了一个游标 c_emp
BEGIN
OPEN   c_emp;     --打开这个游标
LOOP              --做循环
FETCH   c_emp   INTO   v_id,v_zgrs,v_qys,v_date;
EXIT   WHEN   c_emp%NOTFOUND;    --当没有结果集以后,就结束循环
insert into tj_jtcd values(v_id,v_zgrs,v_qys,v_date);
END loop;
end p_test;

CREATE OR REPLACE PROCEDURE p_test
is
v_id    TJ_JTCD.Id%TYPE;
--v_zgrs  TJ_JTCD.ZGRS%TYPE;
--v_qys   TJ_JTCD.QYS%TYPE;
v_date  TJ_JTCD.Createdate%TYPE;
CURSOR   c_emp   IS
select t.wgno from p_wginfo t where t.wgtype = 3;
--SELECT   2,1,1,sysdate   FROM   dual;   --这里创建了一个游标 c_emp
BEGIN
OPEN   c_emp;     --打开这个游标
LOOP              --做循环
FETCH   c_emp   INTO   v_id; --,v_zgrs,v_qys,v_date;
EXIT   WHEN   c_emp%NOTFOUND;    --当没有结果集以后,就结束循环
insert into tj_jtcd values(tjfx_sequence.nextval,1,1,v_date,v_id);
END loop;
commit;
end p_test;

CREATE OR REPLACE PROCEDURE p_test
is
-- v_id    TJ_JTCD.Id%TYPE;
v_wgno    tj_jtcd.wgno%type;
v_zgrs    tj_jtcd.zgrs%type;
v_qys     tj_jtcd.qys%type;
v_ny      tj_jtcd.ny%type;
CURSOR   c_emp    --游标1
IS
select t.wgno from p_wginfo t where t.wgtype = 3;
CURSOR c2  --游标2
IS
select 1,1,'1'
--sum(y.zgzs) as zgrs, count(*) as qys,to_char(to_date(y.lhrq, 'yyyy-mm-dd'), 'yyyy-mm') as ny
from dual d ;
--where y.id in(select b.zjj_id from b_Laodj_Jtcd b where b.ent_id in (select c.ent_id from c_ygdw c where 1 = 1)) group by to_char(to_date(y.lhrq, 'yyyy-mm-dd'), 'yyyy-mm');
BEGIN
OPEN   c_emp;     --打开游标1
LOOP              --做循环
FETCH   c_emp   INTO   v_wgno; --,v_zgrs,v_qys,v_date;
EXIT   WHEN   c_emp%NOTFOUND;    --当没有结果集以后,就结束循环
open c2;
LOOP
fetch c2 into v_zgrs,v_qys,v_ny;
EXIT   WHEN   c2%NOTFOUND;
insert into tj_jtcd(id,zgrs,qys,createdate,wgno,ny) values(tjfx_sequence.nextval,v_zgrs,v_qys,sysdate,v_wgno,v_ny); --插入数据
end loop;
close c2;
END loop;
CLOSE  c_emp;
commit;
end p_test;