oracle动态游标入门二
来源：互联网 发布：淘宝有名的女装店铺 编辑：程序博客网 时间：2024/05/09 15:06
欢迎访问我的blog
http://blog.csdn.net/xjzdr/
下面的存储过程是在调试一个存储过程中记录的，供自己备忘。
-*********下面开始票据合并的运算测试
建立的两个表结构如下：bill_out原始的表，bil_out_A合并用的临时表.
create table BILL_TJ_OUT_A
(
BILL_ID NUMBER,
BILL_NO VARCHAR2(60),
BILL_SIGN VARCHAR2(10),
START_NO NUMBER,
END_NO NUMBER,
ALL_NUM NUMBER,
CURRENT_NO NUMBER,
VALIDEND_NO NUMBER,
MAXCHECKNO NUMBER,
BILL_TIME DATE,
BILLBIGTYPE VARCHAR2(10),
BILL_TYPEID NUMBER,
BILL_STATE NUMBER,
FROM_ORGID NUMBER,
TO_ORGID NUMBER,
USER_ID NUMBER,
ADD_TIME DATE,
SOURCE_BILLID NUMBER,
SOURCE_BILLNO VARCHAR2(60),
PARENT_BILLID NUMBER,
PARENT_BILLNO VARCHAR2(60),
MACHINE_ID VARCHAR2(60),
BILL_MEMO VARCHAR2(600),
TEMP1 NUMBER,
TEMP2 VARCHAR2(10),
TO_ORGNAME VARCHAR2(50),
REMAIN_NUM NUMBER,
BILLENDDATE DATE,
USERID NUMBER
);
create table BILL_TJ_OUT
(
BILL_ID NUMBER,
BILL_NO VARCHAR2(60),
BILL_SIGN VARCHAR2(10),
START_NO NUMBER,
END_NO NUMBER,
ALL_NUM NUMBER,
CURRENT_NO NUMBER,
VALIDEND_NO NUMBER,
MAXCHECKNO NUMBER,
BILL_TIME DATE,
BILLBIGTYPE VARCHAR2(10),
BILL_TYPEID NUMBER,
BILL_STATE NUMBER,
FROM_ORGID NUMBER,
TO_ORGID NUMBER,
USER_ID NUMBER,
ADD_TIME DATE,
SOURCE_BILLID NUMBER,
SOURCE_BILLNO VARCHAR2(60),
PARENT_BILLID NUMBER,
PARENT_BILLNO VARCHAR2(60),
MACHINE_ID VARCHAR2(60),
BILL_MEMO VARCHAR2(600),
TEMP1 NUMBER,
TEMP2 VARCHAR2(10),
TO_ORGNAME VARCHAR2(50),
REMAIN_NUM NUMBER,
BILLENDDATE DATE,
USERID NUMBER
);
两个表的结构是一样的。
心得：
1、PLSQL中用||进行字符串的合并
2、execute immediate strSql2;--执行一个动态的SQL语句
3、DBMS_OUTPUT.PUT_LINE(strSql2);输出一个SQL语句
4、用双单引号表示一个SQL语句中和单引号：
5、提取日期类型的变量中的年月：select to_char(t_bill_time_2,'mm') into t_bill_time_2_mm from dual;--提取日期中的月，t_bill_time_2必须是日期类型，不能为字符类型。
四、简单游标的使用
--窗口发售票据的发出合并用的存储过程
CREATE OR REPLACE PROCEDURE Pro_Remain_Merge_Win
(
p_orgid_win string,--窗口的ID号
p_userid string
)
is
TYPE My_CurType IS REF CURSOR;
CUR_1 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型
strSql1 varchar2(1000);
strSql2 varchar2(1000);
t_bill_typeid number;--窗口发售的票据种类的ID号
t_orgid_zhan number;--窗口所在车站的ID号
--AC_WHERE VARCHAR2(100);
t_to_orgid number;
t_bill_id number;
BEGIN
--########准备数据
delete bill_tj_out_a
where bill_tj_out_a.user_id=p_userid;
commit;
insert into bill_tj_out_a
select * from bill_tj_out
where bill_tj_out.user_id=p_userid;
commit;
--返回窗口使用的票据的种类
select bill_wintobilltype.billtypeid into t_bill_typeid
from bill_org,bill_wintobilltype
where bill_org.org_id=bill_wintobilltype.winid
and bill_org.org_id=p_orgid_win;
--返回车站的ID号
select bill_org.parent_id into t_orgid_zhan
from bill_org where org_id=p_orgid_win;
strSql1:='SELECT bill_id,bill_sign,start_no,end_no,bill_typeid FROM bill_main WHERE userid='||p_userid;
DBMS_OUTPUT.PUT_LINE(strSql1);
OPEN CUR_1 FOR strSql1;--打开动态游标
LOOP
FETCH CUR_1 INTO t_bill_id,t_to_orgid;
EXIT WHEN CUR_1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('t_to_orgid='||t_to_orgid);
strSql2:='delete bill_main where bill_id='||t_bill_id;
strSql2:=strSql2|| 'and start_no='||'16506';
DBMS_OUTPUT.PUT_LINE(strSql2);
DBMS_OUTPUT.PUT_LINE(t_bill_id);
execute immediate strSql1;--执行一个动态的SQL语句
commit;
END LOOP;
CLOSE CUR_1;
end Pro_Remain_Merge_Win;
五、游标的钳套使用
2008年1月11日早上8点：合并窗口的发出，以1501窗口，30604用户为参数，在合并前是113条记录，合并后为8条记录。
调试结果不正确的。
CREATE OR REPLACE PROCEDURE Pro_Remain_Merge_Win
(
p_orgid_win string,--窗口的ID号
p_userid string
)
is
TYPE My_CurType IS REF CURSOR;
CUR_1 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型
CUR_2 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型
strSql1 varchar2(3000);
strSql2 varchar2(3000);
strSql3 varchar2(3000);
t_bill_typeid number;--窗口发售的票据种类的ID号
t_orgid_zhan number;--窗口所在车站的ID号
--下面的为表合并时需要使用的临时变量
t_bill_id_1 number;
t_bill_typeid_1 number;
t_bill_sign_1 varchar2(12);
t_start_no_1 number;
t_end_no_1 number;
t_all_num_1 number;
t_bill_time_1 date;
t_bill_time_1_yyyy number;--bill_time中的年
t_bill_time_1_mm number;--bill_time中的有
--
t_bill_id_2 number;
t_bill_typeid_2 number;
t_bill_sign_2 varchar2(12);
t_start_no_2 number;
t_end_no_2 number;
t_all_num_2 number;
t_bill_time_2 date;
t_bill_time_2_str varchar2(40);--日期的串格式，在更新时要使用
t_bill_time_2_yyyy number;--bill_time中的年
t_bill_time_2_mm number;--bill_time中的有
BEGIN
--########准备数据(先删除后插入) select * from bill_tj_out where userid=30604
delete bill_tj_out_a
where bill_tj_out_a.userid=p_userid;
commit;
insert into bill_tj_out_a select * from bill_tj_out
where bill_tj_out.userid=p_userid;
commit;
--返回窗口使用的票据的种类
select bill_wintobilltype.billtypeid into t_bill_typeid
from bill_org,bill_wintobilltype
where bill_org.org_id=bill_wintobilltype.winid
and bill_org.org_id=p_orgid_win;
--返回车站的ID号
select bill_org.parent_id into t_orgid_zhan
from bill_org where org_id=p_orgid_win;
--将数据提交到bill_tj_out_a表中，在bill_tj_out_a表中进行合并
delete bill_tj_out
where bill_tj_out.user_id=p_userid;
commit;
--execute dbms_output.enable(999999999999999999999);
strSql1:='SELECT bill_id,bill_typeid,bill_sign,start_no,end_no, all_num,bill_time FROM bill_tj_out_a WHERE userid='||p_userid
||' order by bill_sign,start_no';
OPEN CUR_1 FOR strSql1;--打开动态游标
LOOP
FETCH CUR_1 INTO t_bill_id_1,t_bill_typeid_1,t_bill_sign_1,t_start_no_1,t_end_no_1,t_all_num_1,t_bill_time_1;
EXIT WHEN CUR_1%NOTFOUND;
select to_char(t_bill_time_1,'yyyy') into t_bill_time_1_yyyy from dual;--提取日期中的年
select to_char(t_bill_time_1,'mm') into t_bill_time_1_mm from dual;--提取日期中的月
OPEN CUR_2 FOR strSql1;--打开动态游标2
LOOP
FETCH CUR_2 INTO t_bill_id_2,t_bill_typeid_2,t_bill_sign_2,t_start_no_2,t_end_no_2,t_all_num_2,t_bill_time_2;
EXIT WHEN CUR_2%NOTFOUND;
select to_char(t_bill_time_2,'yyyy') into t_bill_time_2_yyyy from dual;--提取日期中的年
select to_char(t_bill_time_2,'mm') into t_bill_time_2_mm from dual;--提取日期中的月
if( t_bill_typeid_1=t_bill_typeid_2 and (t_end_no_1+1)=t_start_no_2 and t_bill_time_1_yyyy=t_bill_time_2_yyyy and t_bill_time_1_mm=t_bill_time_2_mm and t_bill_sign_1=t_bill_sign_2) then
--合并到第一条，并删除第二条记录
t_bill_time_2_str:=to_char(t_bill_time_2,'yyyy-mm-dd hh24:mi:ss');
strSql2:='update bill_tj_out_a set end_no='|| t_end_no_2||',bill_time=to_date('''|| t_bill_time_2_str||''',''yyyy-mm-dd hh24:mi:ss'')' ||' where bill_id='||t_bill_id_1;
--DBMS_OUTPUT.PUT_LINE('strSql2='||strSql2);
strSql3:='delete bill_tj_out_a where bill_id='||t_bill_id_2;
--DBMS_OUTPUT.PUT_LINE('strSql3=-'||strSql3);
execute immediate strSql3;--执行一个动态的SQL语句
execute immediate strSql3;--执行一个动态的SQL语句
end if;
END LOOP;
CLOSE CUR_2;
END LOOP;
CLOSE CUR_1;
commit;
end Pro_Remain_Merge_Win;
六、关于钳套游标的使用的数据测试
测试数据：
表中有三行数据：（重庆东8月1日到5日杂费收据2的发售数据）
bill_id分别为：16359、16361、16363
（1）：
存储过程如下：
CREATE OR REPLACE PROCEDURE Pro_Remain_Merge_Win_1
(
p_orgid_win string,--窗口的ID号
p_userid string
)
is
TYPE My_CurType IS REF CURSOR;
CUR_1 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型
CUR_2 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型
strSql1 varchar2(3000);
strSql2 varchar2(3000);
strSql3 varchar2(3000);
t_bill_typeid number;--窗口发售的票据种类的ID号
t_orgid_zhan number;--窗口所在车站的ID号
--下面的为表合并时需要使用的临时变量
t_bill_id_1 number;
t_bill_typeid_1 number;
t_bill_sign_1 varchar2(12);
t_start_no_1 number;
t_end_no_1 number;
t_all_num_1 number;
t_bill_time_1 date;
t_bill_time_1_yyyy number;--bill_time中的年
t_bill_time_1_mm number;--bill_time中的有
--
t_bill_id_2 number;
t_bill_typeid_2 number;
t_bill_sign_2 varchar2(12);
t_start_no_2 number;
t_end_no_2 number;
t_all_num_2 number;
t_bill_time_2 date;
t_bill_time_2_str varchar2(40);--日期的串格式，在更新时要使用
t_bill_time_2_yyyy number;--bill_time中的年
t_bill_time_2_mm number;--bill_time中的有
BEGIN
--返回窗口使用的票据的种类
select bill_wintobilltype.billtypeid into t_bill_typeid
from bill_org,bill_wintobilltype
where bill_org.org_id=bill_wintobilltype.winid
and bill_org.org_id=p_orgid_win;
--返回车站的ID号
select bill_org.parent_id into t_orgid_zhan
from bill_org where org_id=p_orgid_win;
commit;
--execute dbms_output.enable(999999999999999999999);
strSql1:='SELECT bill_id,bill_typeid,bill_sign,start_no,end_no, all_num,bill_time FROM bill_tj_out WHERE userid='||p_userid
||' order by bill_sign,start_no';
OPEN CUR_1 FOR strSql1;--打开动态游标
LOOP
FETCH CUR_1 INTO t_bill_id_1,t_bill_typeid_1,t_bill_sign_1,t_start_no_1,t_end_no_1,t_all_num_1,t_bill_time_1;
EXIT WHEN CUR_1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('载始一次外循环---');
DBMS_OUTPUT.PUT_LINE('t_bill_id_1='||t_bill_id_1);
OPEN CUR_2 FOR strSql1;--打开动态游标2
LOOP
FETCH CUR_2 INTO t_bill_id_2,t_bill_typeid_2,t_bill_sign_2,t_start_no_2,t_end_no_2,t_all_num_2,t_bill_time_2;
EXIT WHEN CUR_2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('t_bill_id_2='||t_bill_id_2);
END LOOP;
CLOSE CUR_2;
END LOOP;
CLOSE CUR_1;
commit;
end Pro_Remain_Merge_Win_1;
测试结果如下：
载始一次外循环---
t_bill_id_1=16359
t_bill_id_2=16359
t_bill_id_2=16361
t_bill_id_2=16363
载始一次外循环---
t_bill_id_1=16361
t_bill_id_2=16359
t_bill_id_2=16361
t_bill_id_2=16363
载始一次外循环---
t_bill_id_1=16363
t_bill_id_2=16359
t_bill_id_2=16361
t_bill_id_2=16363
（2）存储过程如下：
CREATE OR REPLACE PROCEDURE Pro_Remain_Merge_Win_1
(
p_orgid_win string,--窗口的ID号
p_userid string
)
is
TYPE My_CurType IS REF CURSOR;
CUR_1 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型
CUR_2 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型
strSql1 varchar2(3000);
strSql2 varchar2(3000);
strSql3 varchar2(3000);
t_bill_typeid number;--窗口发售的票据种类的ID号
t_orgid_zhan number;--窗口所在车站的ID号
--下面的为表合并时需要使用的临时变量
t_bill_id_1 number;
t_bill_typeid_1 number;
t_bill_sign_1 varchar2(12);
t_start_no_1 number;
t_end_no_1 number;
t_all_num_1 number;
t_bill_time_1 date;
t_bill_time_1_yyyy number;--bill_time中的年
t_bill_time_1_mm number;--bill_time中的有
--
t_bill_id_2 number;
t_bill_typeid_2 number;
t_bill_sign_2 varchar2(12);
t_start_no_2 number;
t_end_no_2 number;
t_all_num_2 number;
t_bill_time_2 date;
t_bill_time_2_str varchar2(40);--日期的串格式，在更新时要使用
t_bill_time_2_yyyy number;--bill_time中的年
t_bill_time_2_mm number;--bill_time中的有
BEGIN
--返回窗口使用的票据的种类
select bill_wintobilltype.billtypeid into t_bill_typeid
from bill_org,bill_wintobilltype
where bill_org.org_id=bill_wintobilltype.winid
and bill_org.org_id=p_orgid_win;
--返回车站的ID号
select bill_org.parent_id into t_orgid_zhan
from bill_org where org_id=p_orgid_win;
commit;
--execute dbms_output.enable(999999999999999999999);
strSql1:='SELECT bill_id,bill_typeid,bill_sign,start_no,end_no, all_num,bill_time FROM bill_tj_out WHERE userid='||p_userid
||' order by bill_sign,start_no';
OPEN CUR_1 FOR strSql1;--打开动态游标
LOOP
FETCH CUR_1 INTO t_bill_id_1,t_bill_typeid_1,t_bill_sign_1,t_start_no_1,t_end_no_1,t_all_num_1,t_bill_time_1;
EXIT WHEN CUR_1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('开始一次外循环---');
DBMS_OUTPUT.PUT_LINE('t_bill_id_1='||t_bill_id_1);
OPEN CUR_2 FOR strSql1;--打开动态游标2
LOOP
FETCH CUR_2 INTO t_bill_id_2,t_bill_typeid_2,t_bill_sign_2,t_start_no_2,t_end_no_2,t_all_num_2,t_bill_time_2;
EXIT WHEN CUR_2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('t_bill_id_2='||t_bill_id_2);
strSql2:='delete bill_tj_out where bill_id='||t_bill_id_2;
execute immediate strSql2;--执行一个动态的SQL语句
END LOOP;
CLOSE CUR_2;
END LOOP;
CLOSE CUR_1;
commit;
end Pro_Remain_Merge_Win_1;
执行结果如下：
开始一次外循环---
t_bill_id_1=16359
t_bill_id_2=16359
t_bill_id_2=16361
t_bill_id_2=16363
开始一次外循环---
t_bill_id_1=16361
开始一次外循环---
t_bill_id_1=16363
结果说明：外循环有三次执行，内循环虽然删除了该表的数据，但不影响外循环的循环次数。
原来提取时是循环3次，在内循环中删除了数据，但外循环仍执行3次循环。
（3）存储过程如下：
CREATE OR REPLACE PROCEDURE Pro_Remain_Merge_Win_1
(
p_orgid_win string,--窗口的ID号
p_userid string
)
is
TYPE My_CurType IS REF CURSOR;
CUR_1 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型
CUR_2 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型
strSql1 varchar2(3000);
strSql2 varchar2(3000);
strSql3 varchar2(3000);
t_bill_typeid number;--窗口发售的票据种类的ID号
t_orgid_zhan number;--窗口所在车站的ID号
--下面的为表合并时需要使用的临时变量
t_bill_id_1 number;
t_bill_typeid_1 number;
t_bill_sign_1 varchar2(12);
t_start_no_1 number;
t_end_no_1 number;
t_all_num_1 number;
t_bill_time_1 date;
t_bill_time_1_yyyy number;--bill_time中的年
t_bill_time_1_mm number;--bill_time中的有
--
t_bill_id_2 number;
t_bill_typeid_2 number;
t_bill_sign_2 varchar2(12);
t_start_no_2 number;
t_end_no_2 number;
t_all_num_2 number;
t_bill_time_2 date;
t_bill_time_2_str varchar2(40);--日期的串格式，在更新时要使用
t_bill_time_2_yyyy number;--bill_time中的年
t_bill_time_2_mm number;--bill_time中的有
BEGIN
--返回窗口使用的票据的种类
select bill_wintobilltype.billtypeid into t_bill_typeid
from bill_org,bill_wintobilltype
where bill_org.org_id=bill_wintobilltype.winid
and bill_org.org_id=p_orgid_win;
--返回车站的ID号
select bill_org.parent_id into t_orgid_zhan
from bill_org where org_id=p_orgid_win;
commit;
--execute dbms_output.enable(999999999999999999999);
strSql1:='SELECT bill_id,bill_typeid,bill_sign,start_no,end_no, all_num,bill_time FROM bill_tj_out WHERE userid='||p_userid
||' order by bill_sign,start_no';
OPEN CUR_1 FOR strSql1;--打开动态游标
LOOP
FETCH CUR_1 INTO t_bill_id_1,t_bill_typeid_1,t_bill_sign_1,t_start_no_1,t_end_no_1,t_all_num_1,t_bill_time_1;
EXIT WHEN CUR_1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('开始一次外循环---');
DBMS_OUTPUT.PUT_LINE('t_bill_id_1='||t_bill_id_1);
OPEN CUR_2 FOR strSql1;--打开动态游标2
LOOP
FETCH CUR_2 INTO t_bill_id_2,t_bill_typeid_2,t_bill_sign_2,t_start_no_2,t_end_no_2,t_all_num_2,t_bill_time_2;
EXIT WHEN CUR_2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('内循环---');
DBMS_OUTPUT.PUT_LINE('t_bill_id_2='||t_bill_id_2);
strSql2:='delete bill_tj_out where bill_id='||t_bill_id_2;
execute immediate strSql2;--执行一个动态的SQL语句
--重新打开外循环
CLOSE CUR_1;
OPEN CUR_1 FOR strSql1;--打开动态游标
END LOOP;
CLOSE CUR_2;
END LOOP;
CLOSE CUR_1;
commit;
end Pro_Remain_Merge_Win_1;
测试结果：
开始一次外循环---
t_bill_id_1=16359
内循环---
t_bill_id_2=16359
内循环---
t_bill_id_2=16361
内循环---
t_bill_id_2=16363
说明：在内循环中重新关闭了外循环，并重新打开外循环，重新打开外循环时，将会获取新的数据行。
CLOSE CUR_1;
OPEN CUR_1 FOR strSql1;--打开动态游标
（4）
存储过程如下：
CREATE OR REPLACE PROCEDURE Pro_Remain_Merge_Win_1
(
p_orgid_win string,--窗口的ID号
p_userid string
)
is
TYPE My_CurType IS REF CURSOR;
CUR_1 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型
CUR_2 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型
strSql1 varchar2(3000);
strSql2 varchar2(3000);
strSql3 varchar2(3000);
t_bill_typeid number;--窗口发售的票据种类的ID号
t_orgid_zhan number;--窗口所在车站的ID号
--下面的为表合并时需要使用的临时变量
t_bill_id_1 number;
t_bill_typeid_1 number;
t_bill_sign_1 varchar2(12);
t_start_no_1 number;
t_end_no_1 number;
t_all_num_1 number;
t_bill_time_1 date;
t_bill_time_1_yyyy number;--bill_time中的年
t_bill_time_1_mm number;--bill_time中的有
--
t_bill_id_2 number;
t_bill_typeid_2 number;
t_bill_sign_2 varchar2(12);
t_start_no_2 number;
t_end_no_2 number;
t_all_num_2 number;
t_bill_time_2 date;
t_bill_time_2_str varchar2(40);--日期的串格式，在更新时要使用
t_bill_time_2_yyyy number;--bill_time中的年
t_bill_time_2_mm number;--bill_time中的有
BEGIN
--返回窗口使用的票据的种类
select bill_wintobilltype.billtypeid into t_bill_typeid
from bill_org,bill_wintobilltype
where bill_org.org_id=bill_wintobilltype.winid
and bill_org.org_id=p_orgid_win;
--返回车站的ID号
select bill_org.parent_id into t_orgid_zhan
from bill_org where org_id=p_orgid_win;
commit;
--execute dbms_output.enable(999999999999999999999);
strSql1:='SELECT bill_id,bill_typeid,bill_sign,start_no,end_no, all_num,bill_time FROM bill_tj_out WHERE userid='||p_userid
||' order by bill_sign,start_no';
OPEN CUR_1 FOR strSql1;--打开动态游标
LOOP
FETCH CUR_1 INTO t_bill_id_1,t_bill_typeid_1,t_bill_sign_1,t_start_no_1,t_end_no_1,t_all_num_1,t_bill_time_1;
EXIT WHEN CUR_1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('开始一次外循环---');
DBMS_OUTPUT.PUT_LINE('t_bill_id_1='||t_bill_id_1);
OPEN CUR_2 FOR strSql1;--打开动态游标2
LOOP
FETCH CUR_2 INTO t_bill_id_2,t_bill_typeid_2,t_bill_sign_2,t_start_no_2,t_end_no_2,t_all_num_2,t_bill_time_2;
EXIT WHEN CUR_2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('内循环---');
DBMS_OUTPUT.PUT_LINE('t_bill_id_2='||t_bill_id_2);
strSql2:='delete bill_tj_out where bill_id='||t_bill_id_1;--
execute immediate strSql2;--执行一个动态的SQL语句
--重新打开个循环
CLOSE CUR_1;
OPEN CUR_1 FOR strSql1;--打开动态游标
END LOOP;
CLOSE CUR_2;
END LOOP;
CLOSE CUR_1;
commit;
end Pro_Remain_Merge_Win_1;
执行结果如下：
开始一次外循环---
t_bill_id_1=16359
内循环---
t_bill_id_2=16359
内循环---
t_bill_id_2=16361
内循环---
t_bill_id_2=16363
开始一次外循环---
t_bill_id_1=16361
内循环---
t_bill_id_2=16361
内循环---
t_bill_id_2=16363
开始一次外循环---
t_bill_id_1=16363
内循环---
t_bill_id_2=16363
（5）
存储过程如下：
CREATE OR REPLACE PROCEDURE Pro_Remain_Merge_Win_1
(
p_orgid_win string,--窗口的ID号
p_userid string
)
is
TYPE My_CurType IS REF CURSOR;
CUR_1 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型
CUR_2 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型
strSql1 varchar2(3000);
strSql2 varchar2(3000);
strSql3 varchar2(3000);
t_bill_typeid number;--窗口发售的票据种类的ID号
t_orgid_zhan number;--窗口所在车站的ID号
--下面的为表合并时需要使用的临时变量
t_bill_id_1 number;
t_bill_typeid_1 number;
t_bill_sign_1 varchar2(12);
t_start_no_1 number;
t_end_no_1 number;
t_all_num_1 number;
t_bill_time_1 date;
t_bill_time_1_yyyy number;--bill_time中的年
t_bill_time_1_mm number;--bill_time中的有
--
t_bill_id_2 number;
t_bill_typeid_2 number;
t_bill_sign_2 varchar2(12);
t_start_no_2 number;
t_end_no_2 number;
t_all_num_2 number;
t_bill_time_2 date;
t_bill_time_2_str varchar2(40);--日期的串格式，在更新时要使用
t_bill_time_2_yyyy number;--bill_time中的年
t_bill_time_2_mm number;--bill_time中的有
BEGIN
--返回窗口使用的票据的种类
select bill_wintobilltype.billtypeid into t_bill_typeid
from bill_org,bill_wintobilltype
where bill_org.org_id=bill_wintobilltype.winid
and bill_org.org_id=p_orgid_win;
--返回车站的ID号
select bill_org.parent_id into t_orgid_zhan
from bill_org where org_id=p_orgid_win;
commit;
--execute dbms_output.enable(999999999999999999999);
strSql1:='SELECT bill_id,bill_typeid,bill_sign,start_no,end_no, all_num,bill_time FROM bill_tj_out WHERE userid='||p_userid
||' order by bill_sign,start_no';
OPEN CUR_1 FOR strSql1;--打开动态游标
LOOP
FETCH CUR_1 INTO t_bill_id_1,t_bill_typeid_1,t_bill_sign_1,t_start_no_1,t_end_no_1,t_all_num_1,t_bill_time_1;
EXIT WHEN CUR_1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('开始一次外循环---');
DBMS_OUTPUT.PUT_LINE('t_bill_id_1='||t_bill_id_1);
OPEN CUR_2 FOR strSql1;--打开动态游标2
LOOP
FETCH CUR_2 INTO t_bill_id_2,t_bill_typeid_2,t_bill_sign_2,t_start_no_2,t_end_no_2,t_all_num_2,t_bill_time_2;
EXIT WHEN CUR_2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('内循环---');
DBMS_OUTPUT.PUT_LINE('t_bill_id_2='||t_bill_id_2);
strSql2:='delete bill_tj_out where bill_id='||t_bill_id_1;--
execute immediate strSql2;--执行一个动态的SQL语句
END LOOP;
CLOSE CUR_2;
END LOOP;
CLOSE CUR_1;
commit;
end Pro_Remain_Merge_Win_1;
执行结果如下：
开始一次外循环---
t_bill_id_1=16359
内循环---
t_bill_id_2=16359
内循环---
t_bill_id_2=16361
内循环---
t_bill_id_2=16363
开始一次外循环---
t_bill_id_1=16361
内循环---
t_bill_id_2=16361
内循环---
t_bill_id_2=16363
开始一次外循环---
t_bill_id_1=16363
内循环---
t_bill_id_2=16363
说明：和上一个存储过程的区别是删除了风循环中重新打开外循环游标的命令：
--重新打开个循环
CLOSE CUR_1;
OPEN CUR_1 FOR strSql1;--打开动态游标
七、测试窗口合并正确的存储过程：
CREATE OR REPLACE PROCEDURE Pro_Remain_Merge_Win
(
p_orgid_win string,--窗口的ID号
p_userid string
)
is
TYPE My_CurType IS REF CURSOR;
CUR_1 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型
CUR_2 My_CurType;--指示CUR_1的类型为My_CurType，而My_CurType是游标类型
strSql1 varchar2(3000);
strSql2 varchar2(3000);
strSql3 varchar2(3000);
t_bill_typeid number;--窗口发售的票据种类的ID号
t_orgid_zhan number;--窗口所在车站的ID号
--下面的为表合并时需要使用的临时变量
t_bill_id_1 number;
t_bill_typeid_1 number;
t_bill_sign_1 varchar2(12);
t_start_no_1 number;
t_end_no_1 number;
t_all_num_1 number;
t_bill_time_1 date;
t_bill_time_1_yyyy number;--bill_time中的年
t_bill_time_1_mm number;--bill_time中的有
--
t_bill_id_2 number;
t_bill_id_2_merge number;--在表bill_tj_out_a表中，找到的可以合并的记录的ID号
t_bill_typeid_2 number;
t_bill_sign_2 varchar2(12);
t_start_no_2 number;
t_end_no_2 number;
t_all_num_2 number;
t_bill_time_2 date;
t_bill_time_2_str varchar2(40);--日期的串格式，在更新时要使用
t_bill_time_2_yyyy number;--bill_time中的年
t_bill_time_2_mm number;--bill_time中的有
BEGIN
t_bill_id_2_merge:=0;--刚开始时的初始值
delete bill_tj_out_a where userid=p_userid;
commit;
--返回窗口使用的票据的种类
select nvl(bill_wintobilltype.billtypeid,0) into t_bill_typeid
from bill_org,bill_wintobilltype
where bill_org.org_id=bill_wintobilltype.winid
and bill_org.org_id=p_orgid_win;
--返回车站的ID号
select nvl(bill_org.parent_id,0) into t_orgid_zhan
from bill_org where org_id=p_orgid_win;
--execute dbms_output.enable(999999999999999999999);
strSql1:='SELECT bill_id,bill_typeid,bill_sign,start_no,end_no, all_num,bill_time FROM bill_tj_out WHERE userid='||p_userid
||' order by bill_sign,start_no,end_no';
strSql2:='SELECT bill_id,bill_typeid,bill_sign,start_no,end_no, all_num,bill_time FROM bill_tj_out_a WHERE userid='||p_userid
||' order by bill_sign,start_no';
delete bill_tj_out_a where userid=p_userid;
commit;
OPEN CUR_1 FOR strSql1;--打开bill_tj_out的动态游标
LOOP
FETCH CUR_1 INTO t_bill_id_1,t_bill_typeid_1,t_bill_sign_1,t_start_no_1,t_end_no_1,t_all_num_1,t_bill_time_1;
EXIT WHEN CUR_1%NOTFOUND;
select to_char(t_bill_time_1,'yyyy') into t_bill_time_1_yyyy from dual;--提取日期中的年
select to_char(t_bill_time_1,'mm') into t_bill_time_1_mm from dual;--提取日期中的月
OPEN CUR_2 FOR strSql2;--打开动态游标2
LOOP
FETCH CUR_2 INTO t_bill_id_2,t_bill_typeid_2,t_bill_sign_2,t_start_no_2,t_end_no_2,t_all_num_2,t_bill_time_2;
EXIT WHEN CUR_2%NOTFOUND;
select to_char(t_bill_time_2,'yyyy') into t_bill_time_2_yyyy from dual;--提取日期中的年
select to_char(t_bill_time_2,'mm') into t_bill_time_2_mm from dual;--提取日期中的月
if(t_bill_sign_1=t_bill_sign_2 and t_bill_typeid_1=t_bill_typeid_2 and t_bill_time_1_yyyy=t_bill_time_2_yyyy
and t_bill_time_1_mm=t_bill_time_2_mm and (t_end_no_2+1=t_start_no_1)) then
t_bill_id_2_merge:=t_bill_id_1;
end if;
END LOOP;
CLOSE CUR_2;
--判断是否可以合并。不可以合并则插入到bill_tj_out_a中
if t_bill_id_2_merge<>0 then
update bill_tj_out_a set bill_tj_out_a.bill_id=t_bill_id_1,end_no=t_end_no_1,bill_time=t_bill_time_1,all_num=all_num+t_all_num_1
where bill_id=t_bill_id_2 and userid=p_userid;
delete bill_tj_out where bill_id=t_bill_id_2_merge and userid=p_userid;
commit;
t_bill_id_2_merge:=0;
else
insert into bill_tj_out_a select * from bill_tj_out where userid=p_userid and bill_id=t_bill_id_1;
delete bill_tj_out where bill_id=t_bill_id_1 and userid=p_userid;
commit;
end if;
--t_bill_id_2_merge:=0;*/
close CUR_1;
OPEN CUR_1 FOR strSql1;
END LOOP;
CLOSE CUR_1;
commit;
end Pro_Remain_Merge_Win;
欢迎访问我的blog
http://blog.csdn.net/xjzdr/
- oracle动态游标入门二
- Oracle动态游标入门
- Oracle动态游标入门一
- 【oracle游标三】动态游标
- oracle pl/sql 入门+ 数组使用+游标+动态SQL
- oracle pl/sql 入门+ 数组使用+游标+动态SQL
- oracle 动态游标
- oracle动态游标实例
- oracle动态游标
- oracle动态游标
- Oracle动态游标
- oracle动态游标
- Oracle REF动态游标
- 入门oracle之游标
- oracle 游标（显示游标，隐式游标，动态游标）
- oracle 游标使用 二
- Oracle游标（二）
- Oracle游标（三）REF动态游标
- 认证与访问控制（０８－０１－０８）
- 譬如朝露，去日无多（０８－０１－０８）
- 心理学，法律，注册会计师（０８－０１－０８）
- 冲刺正高（０８－０１－０８）
- ORACLE中的锁（LOCK）
- oracle动态游标入门二
- Microsoft Windows XP SP2 With Update 200801 bida[WMZ] 2008贺岁安装版
- 怎么现在每个网站都有博客啊
- 又见麻将
- Notepad++ v4.7.5
- Operator New/Delete
- 使用Java的Swing操作进度条高级操作
- 概念jdk1.5,jdk5.0,j2sdk,j2eesdk,jre 联系与区别
- MFC关于GetClientRect()函数的问题
