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行中得到的,是不是也要声明为变量,我这里没有,是直接从,这个表中的某行取得。请多指教。谢谢。急
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语句而不用,游标是这样吗?