大家好,我想将我的循环里的变量(I)连接到一张表中,可是老是做不了,请高手指点!以下是我的代码:
create or replace procedure p_update_vip_monthfee(v_month in varchar2 ,code out number) is TYPE t_cursor IS ref CURSOR;
v_cursor t_cursor;
v_sql varchar2(1000);
v_user_no varchar2(20);
v_user_bill varchar2(10);
BEGIN
for I IN 851..859 loop
OPEN v_cursor FOR select c.user_id,c.avg_bill from ucr_sta_custmgr.cms_user_0851_200906 c;
dbms_output.put_line(I);
loop
fetch v_cursor into v_user_no,v_user_bill;
EXIT WHEN v_cursor%NOTFOUND;
v_sql:='update tf_f_cust_vip_'||v_month||' a set a.month_fee ='||v_user_bill ||
' WHERE a.user_id='||v_user_no||' and a.eparchy_code=0'||I;
execute immediate v_sql;
dbms_output.put_line(v_sql);
end loop;
CLOSE v_cursor;
--commit;
end loop;
code:=0;
EXCEPTION
WHEN OTHERS THEN
code:=-1;
ROLLBACK;
return;
dbms_output.put_line('ERROR:'||SQLERRM);
END p_update_vip_monthfee;注意:我想把 OPEN v_cursor FOR select c.user_id,c.avg_bill from ucr_sta_custmgr.cms_user_0851_200906 c;
中的用I替换,用什么办法呢,我改成字符串,然后执行,但是出异常了!
create or replace procedure p_update_vip_monthfee(v_month in varchar2 ,code out number) is TYPE t_cursor IS ref CURSOR;
v_cursor t_cursor;
v_sql varchar2(1000);
v_user_no varchar2(20);
v_user_bill varchar2(10);
BEGIN
for I IN 851..859 loop
OPEN v_cursor FOR select c.user_id,c.avg_bill from ucr_sta_custmgr.cms_user_0851_200906 c;
dbms_output.put_line(I);
loop
fetch v_cursor into v_user_no,v_user_bill;
EXIT WHEN v_cursor%NOTFOUND;
v_sql:='update tf_f_cust_vip_'||v_month||' a set a.month_fee ='||v_user_bill ||
' WHERE a.user_id='||v_user_no||' and a.eparchy_code=0'||I;
execute immediate v_sql;
dbms_output.put_line(v_sql);
end loop;
CLOSE v_cursor;
--commit;
end loop;
code:=0;
EXCEPTION
WHEN OTHERS THEN
code:=-1;
ROLLBACK;
return;
dbms_output.put_line('ERROR:'||SQLERRM);
END p_update_vip_monthfee;注意:我想把 OPEN v_cursor FOR select c.user_id,c.avg_bill from ucr_sta_custmgr.cms_user_0851_200906 c;
中的用I替换,用什么办法呢,我改成字符串,然后执行,但是出异常了!
解决方案 »
- 求一句SQL语句应该怎么写
- 菜鸟问题:用imp命令导入数据库,是不是先要创建一个数据库?
- oracle的存储过程问题?
- 关于数据集合的问题,大虾们请进
- jsp中用insert不能录入数据库的问题!
- 请教一个数据合并的sql语句
- SQLPLUS studentx/studentx@ T: 166 111.4.4:Or7中@后面的T是什么意思
- 100分,紧急,有关oracle CLOB 的问题,请问如何将CLob的类型的字段插入到数据库中?除了使用Stream来插入,
- DELPHI中不用第三方控件如何调用存储过程返回结果集
- 如何实现oracle排序,请具体一点,必有重谢~
- oracle中查询连续5个月都有值的记录
- 物化视图 能同步视图吗
v_cursor t_cursor;
-- add new var
v_sql1 varchar2(1000);
v_sql varchar2(1000);
v_user_no varchar2(20);
v_user_bill varchar2(10);
BEGIN
for I IN 851..859 loop
v_sql1 := 'select c.user_id,c.avg_bill from ucr_sta_custmgr.cms_user_0'||I||'_200906 c';
OPEN v_cursor FOR v_sql1;
dbms_output.put_line(I);