1 declare
2 v_id number;
3 v_depid number;
4 cursor c1 is select * from emp for update of empid;
5 begin
6 for i in c1 loop
7 v_id:=i.id;
8 select s.deptno from scott.emp s into v_depid where s.empno=v_id;
9 update emp set empid=v_depid where current of c1;
10 end loop;
11* end;
SQL> /
for i in c1 loop
*
ERROR 位于第 6 行:
ORA-06550: 第 8 行, 第 35 列:
PL/SQL: ORA-00933: SQL 命令未正确结束
ORA-06550: 第 8 行, 第 2 列:
PL/SQL: SQL Statement ignored我的PL/SQL哪里有问题?
我自己建了个表,内容和SCOTT.emp基本相同.建表的时候没有部门编号这个列,增加后想把SCOTT.EMP表中对应的部门编号放到新emp中的部门编号这个列中.可是我写的更新游标不行,望各位前辈指导~~~
谢谢,谢谢~~~~!
declare
v_id number;
v_depid number;
cursor c1 is select * from emp ;
begin
for i in c1 loop
v_id:=i.id;
select s.deptno from scott.emp s into v_depid where s.empno=v_id;
update emp set empid=v_depid where id=v_id;
end loop;
commit;
end;
v_id number;
v_depid number;
cursor c1 is select * from emp ;
begin
for i in c1 loop
v_id:=i.id;
select s.deptno into v_depid from scott.emp s where s.empno=v_id;
update emp set empid=v_depid where id=v_id;
end loop;
commit;
end;
另外,这样的表更新只见解用一个更新语句就可以,不必用游标
用查询更新操作
在Oracle中,可以根据查询结果来更新表的数据。比如,在申请基金项目时,不允许一个人在大学申请项目又在大学的研究所中申请项目,则可使用下面例1的语句来实现检查。例1:
SQL>update colle_subjects set app_flag='0'
Where per_id in ( select per_id from univ_subjects );例2:
SQL>update comfort set ( noon, midnigt )=
(select humiddity,temperature from weather
where city='MANCHESTER')
where City='WALPOLE' and
Sampledate=to_date('22-dec-2000','DD-MON-YYYY');例3:
UPDATE sales PARTITION (feb96) s
SET s.account_name = UPPER(s.account_name);§6.2.3 用相关子查询更新操作
同样可以在更新中使用相关的查询操作,如:例1.复杂UPDATE 语句:
UPDATE emp a
SET deptno =
(SELECT deptno
FROM dept
WHERE loc = ’BOSTON’),(sal, comm) =
(SELECT 1.1*AVG(sal), 1.5*AVG(comm)
FROM emp b
WHERE a.deptno = b.deptno)WHERE deptno IN
(SELECT deptno
FROM dept
WHERE loc = ’DALLAS’
OR loc = ’DETROIT’);
例2:相互更新的例子:UPDATE TABLE(SELECT projs
FROM dept d WHERE d.dno = 123) p
SET p.budgets = p.budgets + 1;
为什么就不正确了,望高人指点
明天揭贴