declare
cursor o_plan is select * from ads.plan where assessor is not null and type='周' and year='2009' order by serial;--查出旧表中所有的数据一行中
cursor n_plan(n_pindex ads.new_plan.pindex%type,o_owner_id ads.new_plan.owner_id%TYPE) is
select pindex,owner_id from ads.new_plan where pyear=2009 and ptype='周' and pindex=n_pindex and owner_id=o_owner_id order by pindex ;
o_rplan ads.plan%ROWTYPE;--从旧表中取一行,
v_pindex ads.new_plan.pindex%TYPE;--周次编号
v_owner_id ads.new_plan.owner_id%TYPE;--用户编号
BEGIN
OPEN o_plan;
LOOP
FETCH o_plan INTO o_rplan;
EXIT WHEN o_plan%NOTFOUND;
OPEN n_plan(o_rplan.serial,o_rplan.flag_user_id);
LOOP
FETCH n_plan INTO v_pindex,v_owner_id;
EXIT WHEN n_plan%NOTFOUND;
update ads.new_plan set qvyu_advice_owner_id=o_rplan.assessor_id and qvyu_advice_owner_name=o_rplan.assessor
where ptype='周' and pyear='2009' and pindex=o_rplan.serial and owner_id=o_rplan.flag_user_id;
END LOOP;
CLOSE n_plan;
END LOOP;
CLOSE o_plan;
END ;简单的意思是这样的,new_plan中的2个字段是空的,我要从plan表中得到这2个字段的值,且插入值,
然后参数set qvyu_advice_owner_id=o_rplan.assessor_id and qvyu_advice_owner_name=o_rplan.assessor
这个2个值是从,plan行中得到的,是不是也要声明为变量,我这里没有,是直接从,这个表中的某行取得。请多指教。谢谢。急
cursor o_plan is select * from ads.plan where assessor is not null and type='周' and year='2009' order by serial;--查出旧表中所有的数据一行中
cursor n_plan(n_pindex ads.new_plan.pindex%type,o_owner_id ads.new_plan.owner_id%TYPE) is
select pindex,owner_id from ads.new_plan where pyear=2009 and ptype='周' and pindex=n_pindex and owner_id=o_owner_id order by pindex ;
o_rplan ads.plan%ROWTYPE;--从旧表中取一行,
v_pindex ads.new_plan.pindex%TYPE;--周次编号
v_owner_id ads.new_plan.owner_id%TYPE;--用户编号
BEGIN
OPEN o_plan;
LOOP
FETCH o_plan INTO o_rplan;
EXIT WHEN o_plan%NOTFOUND;
OPEN n_plan(o_rplan.serial,o_rplan.flag_user_id);
LOOP
FETCH n_plan INTO v_pindex,v_owner_id;
EXIT WHEN n_plan%NOTFOUND;
update ads.new_plan set qvyu_advice_owner_id=o_rplan.assessor_id and qvyu_advice_owner_name=o_rplan.assessor
where ptype='周' and pyear='2009' and pindex=o_rplan.serial and owner_id=o_rplan.flag_user_id;
END LOOP;
CLOSE n_plan;
END LOOP;
CLOSE o_plan;
END ;简单的意思是这样的,new_plan中的2个字段是空的,我要从plan表中得到这2个字段的值,且插入值,
然后参数set qvyu_advice_owner_id=o_rplan.assessor_id and qvyu_advice_owner_name=o_rplan.assessor
这个2个值是从,plan行中得到的,是不是也要声明为变量,我这里没有,是直接从,这个表中的某行取得。请多指教。谢谢。急
解决方案 »
- 一个号码段分组问题
- 怎样在linux下执行oracle端执行sql文件
- 如何导出oracle数据库的sql 脚本?
- 我建了一个视图,但现在查询起来速度特别慢,不知道是怎么回事,建立视图的代码如下,请大家帮忙看一下,多谢了!
- sequence的nextval小于当前最大id,如何让它大于最大id呢?
- 请教OracleType里面没有Boolean的解决办法
- 简单问题:急!送分20
- oracle的字符串连接函数是什么,如何用
- 求助各位大佬 pl/sql
- oracle中merge into查询越来越慢,大家知道什么原因吗,
- oracle workflow 审批操作完成页面跳转如何客制化?
- [收集贴]本贴收集SQL常用的存储过程和自定义函数,大家有的话发出来晒晒!
PL/SQL: ORA-00933: SQL 命令未正确结束
ORA-06550: 第 20 行, 第 11 列:
PL/SQL: SQL Statement ignored
我找到相应的地方,但是看不出有什么问题,
20行,就是我的update这行,
刚才我把and pindex=o_rplan.serial and owner_id=o_rplan.flag_user_id这个更改为了and pindex=v_pindex and owner_id=v_owner_id;
更新条件是new_plan表的pindexwner_id对应plan表的serial、flag_user_id不知道楼主是不是这个意思?update ads.new_plan a
set (qvyu_advice_owner_id, qvyu_advice_owner_name)
=(select assessor_id,ssessor FROM ads.plan where a.pindex=serial and a.owner_id=flag_user_id)
where ptype='周' and pyear='2009' and EXISTS(SELECT 1 FROM ads.PLAN WHERE a.pindex=serial and a.owner_id=flag_user_id);
where ptype='周' and pyear='2009' and pindex=o_rplan.serial and owner_id=o_rplan.flag_user_id;
改成
update ads.new_plan set qvyu_advice_owner_id=o_rplan.assessor_id , qvyu_advice_owner_name=o_rplan.assessor
where ptype='周' and pyear='2009' and pindex=o_rplan.serial and owner_id=o_rplan.flag_user_id;
試試
update table set col1=val1, col2=val2 [,...]
where condition...
您是直接用sql语句而不用,游标是这样吗?