forall中使用buLK
时间:2011-09-13
来源:互联网
报错:
SQL命令为正确结束
定位在13行
FORALL i IN 1 .. ename_table.count
EXECUTE IMMEDIATE sql_stat USING ename_table(i)
RETURNING BULK COLLECT INTO sal_table;
这个语句的使用 有什么问题嘛?
SQL命令为正确结束
定位在13行
[Copy to clipboard] [ - ]
CODE:
--forall
DECLARE
TYPE ename_table_name IS TABLE OF emp.ename%TYPE ;
TYPE sal_table_name IS TABLE OF emp.sal%TYPE;
ename_table ename_table_name;
sal_table sal_table_name;
sql_stat VARCHAR2(200);
BEGIN
ename_table:=ename_table_name('SCOTT','SMITH','CLARK');
sql_stat := 'update emp set sal=sal*1.1 where ename=:1'||'returing sal into :2';
--12
FORALL i IN 1 .. ename_table.count
EXECUTE IMMEDIATE sql_stat USING ename_table(i)
RETURNING BULK COLLECT INTO sal_table;
FOR j IN 1 .. ename_table.COUNT LOOP
dbms_output.put_line('employee: ' || ename_table(j) || ' New salary: ' ||
sal_table(j));
END LOOP;
END;
DECLARE
TYPE ename_table_name IS TABLE OF emp.ename%TYPE ;
TYPE sal_table_name IS TABLE OF emp.sal%TYPE;
ename_table ename_table_name;
sal_table sal_table_name;
sql_stat VARCHAR2(200);
BEGIN
ename_table:=ename_table_name('SCOTT','SMITH','CLARK');
sql_stat := 'update emp set sal=sal*1.1 where ename=:1'||'returing sal into :2';
--12
FORALL i IN 1 .. ename_table.count
EXECUTE IMMEDIATE sql_stat USING ename_table(i)
RETURNING BULK COLLECT INTO sal_table;
FOR j IN 1 .. ename_table.COUNT LOOP
dbms_output.put_line('employee: ' || ename_table(j) || ' New salary: ' ||
sal_table(j));
END LOOP;
END;
FORALL i IN 1 .. ename_table.count
EXECUTE IMMEDIATE sql_stat USING ename_table(i)
RETURNING BULK COLLECT INTO sal_table;
这个语句的使用 有什么问题嘛?
作者: it_china_110 发布时间: 2011-09-13
代码如下:
SQL> select ename, sal, sal*1.1 new_sal from emp where ename in('SCOTT', 'SMITH', 'CLARK');
ENAME SAL NEW_SAL
---------- --------- ----------
SMITH 800.00 880
CLARK 2450.00 2695
SCOTT 3000.00 3300
SQL>
SQL> DECLARE
2 TYPE ename_table_name IS TABLE OF emp.ename%TYPE;
3 TYPE sal_table_name IS TABLE OF emp.sal%TYPE;
4 ename_table ename_table_name;
5 sal_table sal_table_name;
6 sql_stat VARCHAR2(200);
7 BEGIN
8 ename_table := ename_table_name('SCOTT', 'SMITH', 'CLARK');
9
10 sql_stat := 'update emp set sal=sal*1.1 where ename=:1 returning sal into :2';
11
12 FORALL i IN 1 .. ename_table.count
13 EXECUTE IMMEDIATE sql_stat USING ename_table(i) RETURNING BULK COLLECT INTO sal_table;
14
15
16 FOR j IN 1 .. ename_table.COUNT LOOP
17 dbms_output.put_line('employee: ' || ename_table(j) || ' New salary: ' || sal_table(j));
18 END LOOP;
19
20 END;
21 /
employee: SCOTT New salary: 3300
employee: SMITH New salary: 880
employee: CLARK New salary: 2695
PL/SQL procedure successfully completed
SQL>
作者: bell6248 发布时间: 2011-09-13
相关阅读 更多
热门阅读
-
office 2019专业增强版最新2021版激活秘钥/序列号/激活码推荐 附激活工具
阅读:74
-
如何安装mysql8.0
阅读:31
-
Word快速设置标题样式步骤详解
阅读:28
-
20+道必知必会的Vue面试题(附答案解析)
阅读:37
-
HTML如何制作表单
阅读:22
-
百词斩可以改天数吗?当然可以,4个步骤轻松修改天数!
阅读:31
-
ET文件格式和XLS格式文件之间如何转化?
阅读:24
-
react和vue的区别及优缺点是什么
阅读:121
-
支付宝人脸识别如何关闭?
阅读:21
-
腾讯微云怎么修改照片或视频备份路径?
阅读:28