代码如下:CREATE OR REPLACE PROCEDURE SETTLEMENT_PROD_SHY_copy(V_IN_RULE_TABLE IN NUMBER)
AS
type ref_cursor is ref cursor;
sum_info_base_cursor ref_cursor;
info_base_cursor ref_cursor; v_PROD_ID VARCHAR2(10);
v_PROD_NAME VARCHAR2(100);
v_in_trunk_type VARCHAR2(15);
v_out_trunk_type VARCHAR2(15);
v_org_service_class VARCHAR2(1);
v_trm_service_class VARCHAR2(1);
v_org_special_num_type VARCHAR2(2);
v_trm_special_num_type VARCHAR2(2);
v_org_dealer_code VARCHAR2(4);
v_trm_dealer_code VARCHAR2(4);
v_trm_region_code VARCHAR2(1);
v_net_type VARCHAR2(2);
v_connect_type VARCHAR2(2); sum_info_base_sql varchar2(2000);
info_base_sql varchar2(2000);
insert_rule_sql varchar2(1000);
truncate_sql varchar2(1000);BEGIN
IF V_IN_RULE_TABLE = 1 THEN
truncate_sql := 'truncate table TEMP_SETTLE_TEST_RULE_SHY';
execute immediate truncate_sql;
open sum_info_base_cursor for
SELECT PROD_ID,SELECT_CLAUSE,PROD_NAME FROM TMP_RPT_CUSTOMIZE_TEST1_SHY WHERE SELECT_CLAUSE LIKE '%SUM_INFO_BASE%'; loop
fetch sum_info_base_cursor
into v_PROD_ID,info_base_sql,v_PROD_NAME;
exit when sum_info_base_cursor%notfound;
dbms_output.put_line(info_base_sql);
------------------------------------------------------------
open info_base_cursor for info_base_sql; ----------主要是这里的问题
loop
fetch info_base_cursor
into v_in_trunk_type,v_out_trunk_type,v_org_service_class,v_trm_service_class,v_org_special_num_type,v_trm_special_num_type,v_org_dealer_code,v_trm_dealer_code,v_trm_region_code,v_net_type,v_connect_type;
exit when info_base_cursor%notfound;
dbms_output.put_line(v_in_trunk_type + '11111');
insert_rule_sql := 'INSERT INTO TEMP_SETTLE_TEST_RULE_SHY (v_PROD_ID,v_PROD_NAME,v_in_trunk_type,v_out_trunk_type,v_org_service_class,v_trm_service_class,v_org_special_num_type,v_trm_special_num_type,v_org_dealer_code,v_trm_dealer_code,v_trm_region_code,v_net_type,v_connect_type)';
execute immediate insert_rule_sql;
end loop;
close info_base_cursor;
end loop;
close sum_info_base_cursor;
END IF ;
commit;
END;
open info_base_cursor for info_base_sql
主要是这一句!因为info_base_sql是上一个游标中循环出来的一个保存sql语句的字段,varchar2类型
现在要赋给游标。。问题出现了,本来应该是一段sql语句的查询结果赋给游标,现在却变成赋给游标的是字段,所以会报错问题是找到了,可是没有解决方法,希望各位大虾帮帮小弟!着急,小弟就在线等答案了!!!!
AS
type ref_cursor is ref cursor;
sum_info_base_cursor ref_cursor;
info_base_cursor ref_cursor; v_PROD_ID VARCHAR2(10);
v_PROD_NAME VARCHAR2(100);
v_in_trunk_type VARCHAR2(15);
v_out_trunk_type VARCHAR2(15);
v_org_service_class VARCHAR2(1);
v_trm_service_class VARCHAR2(1);
v_org_special_num_type VARCHAR2(2);
v_trm_special_num_type VARCHAR2(2);
v_org_dealer_code VARCHAR2(4);
v_trm_dealer_code VARCHAR2(4);
v_trm_region_code VARCHAR2(1);
v_net_type VARCHAR2(2);
v_connect_type VARCHAR2(2); sum_info_base_sql varchar2(2000);
info_base_sql varchar2(2000);
insert_rule_sql varchar2(1000);
truncate_sql varchar2(1000);BEGIN
IF V_IN_RULE_TABLE = 1 THEN
truncate_sql := 'truncate table TEMP_SETTLE_TEST_RULE_SHY';
execute immediate truncate_sql;
open sum_info_base_cursor for
SELECT PROD_ID,SELECT_CLAUSE,PROD_NAME FROM TMP_RPT_CUSTOMIZE_TEST1_SHY WHERE SELECT_CLAUSE LIKE '%SUM_INFO_BASE%'; loop
fetch sum_info_base_cursor
into v_PROD_ID,info_base_sql,v_PROD_NAME;
exit when sum_info_base_cursor%notfound;
dbms_output.put_line(info_base_sql);
------------------------------------------------------------
open info_base_cursor for info_base_sql; ----------主要是这里的问题
loop
fetch info_base_cursor
into v_in_trunk_type,v_out_trunk_type,v_org_service_class,v_trm_service_class,v_org_special_num_type,v_trm_special_num_type,v_org_dealer_code,v_trm_dealer_code,v_trm_region_code,v_net_type,v_connect_type;
exit when info_base_cursor%notfound;
dbms_output.put_line(v_in_trunk_type + '11111');
insert_rule_sql := 'INSERT INTO TEMP_SETTLE_TEST_RULE_SHY (v_PROD_ID,v_PROD_NAME,v_in_trunk_type,v_out_trunk_type,v_org_service_class,v_trm_service_class,v_org_special_num_type,v_trm_special_num_type,v_org_dealer_code,v_trm_dealer_code,v_trm_region_code,v_net_type,v_connect_type)';
execute immediate insert_rule_sql;
end loop;
close info_base_cursor;
end loop;
close sum_info_base_cursor;
END IF ;
commit;
END;
open info_base_cursor for info_base_sql
主要是这一句!因为info_base_sql是上一个游标中循环出来的一个保存sql语句的字段,varchar2类型
现在要赋给游标。。问题出现了,本来应该是一段sql语句的查询结果赋给游标,现在却变成赋给游标的是字段,所以会报错问题是找到了,可是没有解决方法,希望各位大虾帮帮小弟!着急,小弟就在线等答案了!!!!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货