1。第一个: 这是一个简单的把一条数据插入到某一个表中的sql
执行: exec 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 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_testisv_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_empBEGINOPEN 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;
2。第二个: 是从一个表中查出来多条数据,然后把这多条数据通过循环插入到某一个表中
执行: exec 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_testisv_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 ISselect t.wgno from p_wginfo t where t.wgtype = 3;--SELECT 2,1,1,sysdate FROM dual; --这里创建了一个游标 c_empBEGINOPEN 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;
3。 第三个: 两个游标的写法,并且第二个游标是带参数的.
执行: exec 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;
CREATE OR REPLACE PROCEDURE p_testis-- 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 --游标1ISselect t.wgno from p_wginfo t where t.wgtype = 3;CURSOR c2 --游标2ISselect 1,1,'1'--sum(y.zgzs) as zgrs, count(*) as qys,to_char(to_date(y.lhrq, 'yyyy-mm-dd'), 'yyyy-mm') as nyfrom 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');BEGINOPEN c_emp; --打开游标1LOOP --做循环FETCH c_emp INTO v_wgno; --,v_zgrs,v_qys,v_date;EXIT WHEN c_emp%NOTFOUND; --当没有结果集以后,就结束循环open c2;LOOPfetch 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;
4。带参数的存储过程(完整版)。
执行: exec p_test('2009-01-01','2009-10-01');
- CREATE OR REPLACE PROCEDURE p_test(stime in varchar2,etime in varchar2) -- 创建带参数的存储过程
- is
- -- 以下定义了四个变量,由于这些变量赋值后要插入到tj_jtcd这个表中所以类型都要定义成一致
- 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_wgno -- 游标1 取得所有的网格编号 共有95条
- IS
- select t.wgno from p_wginfo t where t.wgtype = 3;
-
- CURSOR c_data(wgid varchar2) -- 游标2 这是一个带参数的游标,这个参数是从第一个网格中取得,并且作为这个查 询中的一个条件
- IS
- select
- nvl(sum(y.zgzs),0) as zgrs, nvl(count(*),0) as qys,to_char(to_date(y.lhrq, 'yyyy-mm-dd'), 'yyyy-mm') as ny
- from y_laodj_jtcd y
- where
- to_date(y.lhrq,'yyyy-mm-dd') between to_date(stime,'yyyy-mm-dd') and to_date(etime,'yyyy-mm-dd') and
- 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 and c.wgno = wgid)) group by to_char(to_date(y.lhrq, 'yyyy-mm-dd'), 'yyyy-mm');
-
-
-
- BEGIN
-
- OPEN c_wgno; -- 打开游标1
- LOOP -- 做循环
- FETCH c_wgno INTO v_wgno; --对遍历游标1中的值
- EXIT WHEN c_wgno%NOTFOUND; -- 当没有结果集以后,就结束循环
-
- open c_data(v_wgno); -- 打开游标c2
- LOOP
- fetch c_data into v_zgrs,v_qys,v_ny; -- 遍历游标2里的值
- EXIT WHEN c_data%NOTFOUND;
- -- 以下是执行插入操作,插入的值来自于游标1,2中遍历的值
- 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; -- 结束循环,并关闭游标2
- close c_data;
-
- END loop; -- 结束循环,并关闭游标1
- CLOSE c_wgno;
-
- commit; -- 事务处理 提交 (这里有待完善)
-
- end p_test;