一、 sql语句
var x number;
var 1 numner;
exec :x :=7369;
exec :x :=7369;
select name from emp where id=:x;
select name from emp where id=:1;
二、 plsql语句
1. select语句
execute immediate '带绑定变量的目标sql' into 目标变量 using 绑定变量具体值
declare
v_name varchar2(10);
begin
execute immediate 'select name from emp where id=:1' into v_name using 7369;
dbms_output.putline(v_name);
end;
/2. dml语句
注意目标sql有几个绑定变量，using后就跟多少输入值。using后的输入值只与绑定变量位置有关，与名称无关，所以写insert into emp(id,name,job) values(:1,:1,:1)也是可以的
declare
v_sql_1 varchar2(4000);
v_sql_2 varchar2(4000);
tmp_1 number;
tmp_2 number;
begin
v_sql_1 := 'insert into emp(id,name,job) values(:1,:2,:3)'
execute immediate v_sql_1 using 7369,'xiaoming','dba';
tmp_1 := sql%rowcount;
v_sql_2 := 'insert into emp(id,name,job) values(:1,:1,:1)'
execute immediate v_sql_1 using 7370,'xiaoh','dba';
tmp_2 := sql%rowcount;
dbms_output.putline(to_char(tmp_1+tmp_2));
commit;
end;
/3. 动态sql
returning关键字可以和带绑定变量的sql连用，取出受该sql影响的行的对应字段值
declare
v_sql_1 varchar2(4000);
v_column varchar2(10);
v_name varchar2(10);
begin
v_column := 'id';
v_sql_1 := 'delete from emp where ' || v_column || ' = :1 returning name into :2';
execute immediate v_sql_1 using 7369 returning into v_name;
dbms_output.putline(v_name);
commit;
end;
/
三、 plsql中的批量处理
1. 为什么批量处理更高效？
Oracle使用两个引擎来处理PL/SQL代码块——PL/SQL引擎处理过程化代码，SQL引擎处理SQL语句。两个引擎之间每次数据传递和交互称为一次上下文切换（context switch），每一次上下文切换都会带来额外的开销。
如果PL/SQL代码遍历一个集合，并且对该集合中的每行执行相同的DML操作，则可以通过一次操作将整个集合批量绑定到DML语句来减少上下文切换。
2. 批量处理分类
-
BULK COLLECT：可以提高从查询加载数据集合（表变量）时的性能，将查询结果一次性地fetch到变量中，而不是通过cursor一条条处理，提高数据获取效率（减少SQL引擎->PL/SQL引擎的context switch）。语法如下：
fetch cursor_name bulk collect into [自定义数组] <limit 常量值>
<limit 常量值>表示一次只fetch 指定的记录数（通常为1000），避免一次fetch所有数据占用大量PGA-
FORALL：用于 INSERT, UPDATE, and DELETE，一次修改多行数据，提高DML效率（减少PL/SQL引擎->SQL引擎的context switch）。
语法如下：
forall i in 1..[自定义数组长度]
execute immediate '带绑定变量的目标sql' using 绑定变量具体值
下面是几个例子
3. 单独使用 bulk collect将表数据fetch至变量
可以在select into,fetch into,returning into语句使用bulk collect，注意所有的into变量都必须是collections.
--在select into语句中使用bulk collect
DECLARE
TYPE sallist IS TABLE OF employees.salary%TYPE;
sals sallist;
BEGIN
SELECT salary BULK COLLECT INTO sals FROM employees where rownum<=50;
--接下来使用集合中的数据
END;
/--在fetch into中使用bulk collect
DECLARE
TYPE deptrectab IS TABLE OF departments%ROWTYPE;
dept_recs deptrectab;
CURSOR cur IS SELECT department_id,department_name FROM departments where department_id>10;
BEGIN
OPEN cur;
FETCH cur BULK COLLECT INTO dept_recs;
--接下来使用集合中的数据
END;
/--使用limit限制每次fetch的量
SET SERVEROUTPUT ON
DECLARE
TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;
l_tab t_bulk_collect_test_tab;
CURSOR c_data IS SELECT * FROM bulk_collect_test;
BEGIN
OPEN c_data;
LOOP
FETCH c_data
BULK COLLECT INTO l_tab LIMIT 10000;
EXIT WHEN l_tab.count = 0;
-- Process contents of collection here.
DBMS_OUTPUT.put_line(l_tab.count || ' rows');
END LOOP;
CLOSE c_data;
END;
/
--输出
10000 rows
10000 rows
10000 rows
10000 rows
2578 rows
PL/SQL procedure successfully completed.-- 在returning into中使用bulk collect
CREATE TABLE emp AS SELECT * FROM employees;
DECLARE
TYPE numlist IS TABLE OF employees.employee_id%TYPE;
enums numlist;
TYPE namelist IS TABLE OF employees.last_name%TYPE;
names namelist;
BEGIN
DELETE emp WHERE department_id=30
RETURNING employee_id,last_name BULK COLLECT INTO enums,names;
DBMS_OUTPUT.PUT_LINE('deleted'||SQL%ROWCOUNT||'rows:');
FOR i IN enums.FIRST .. enums.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('employee#'||enums(i)||':'||names(i));
END LOOP;
END;
/
-- 结果
deleted6rows:
employee#114:Raphaely
employee#115:Khoo
employee#116:Baida
employee#117:Tobias
employee#118:Himuro
employee#119:Colmenares
4. 单独用forall进行批量DML
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70);
BEGIN
FORALL i IN depts.FIRST..depts.LAST
UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
END;
/-- 对比常规FOR..LOOP绑定和批量绑定FORALL插入10000行花费的时间
SET SERVEROUTPUT ON
DECLARE
TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;
l_tab t_forall_test_tab := t_forall_test_tab();
l_start NUMBER;
l_size NUMBER := 10000;
BEGIN
-- 数据初始化
FOR i IN 1 .. l_size LOOP
l_tab.extend;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).code := TO_CHAR(i);
l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
END LOOP;
EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';
-- Time regular inserts.
l_start := DBMS_UTILITY.get_time;
FOR i IN l_tab.first .. l_tab.last LOOP
INSERT INTO forall_test (id, code, description)
VALUES (l_tab(i).id, l_tab(i).code, l_tab(i).description);
END LOOP;
DBMS_OUTPUT.put_line('Normal Inserts: ' ||
(DBMS_UTILITY.get_time - l_start));
EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';
-- Time bulk inserts.
l_start := DBMS_UTILITY.get_time;
FORALL i IN l_tab.first .. l_tab.last
INSERT INTO forall_test VALUES l_tab(i);
DBMS_OUTPUT.put_line('Bulk Inserts : ' ||
(DBMS_UTILITY.get_time - l_start));
COMMIT;
END;
/
-- 输出结果
Normal Inserts: 305
Bulk Inserts : 14
5. bulk collect + forall 批量操作
DECLARE
TYPE lv_emp_name_tbl IS TABLE OF VARCHAR2(50);
lv_emp_name lv_emp_name_tbl;
CURSOR guru99_det IS SELECT emp_name FROM emp;
BEGIN
OPEN guru99_det;
LOOP
-- 批量取出数据，每次5000条
FETCH guru99_det BULK COLLECT INTO lv_emp_name LIMIT 5000;
-- 批量更新数据
FORALL i IN lv_emp_name.FIRST .. lv emp_name.LAST
UPDATE emp SET salaiy=salary+5000 WHERE emp_name=lv_emp_name(i);
COMMIT;
EXIT WHEN guru99_det%NOTFOUND;
END LOOP;
CLOSE guru99_det;
END;
/CREATE OR REPLACE PROCEDURE fast_proc (p_array_size IN PLS_INTEGER DEFAULT 100)
IS
TYPE ARRAY IS TABLE OF tmo0820%ROWTYPE;
l_data ARRAY;
CURSOR c IS
SELECT *
FROM tmo0820;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;
FORALL i IN 1..l_data.COUNT
INSERT INTO t2 VALUES l_data(i);
DBMS_OUTPUT.put_line(l_data.count || ' rows');
COMMIT;
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
END fast_proc;
/
参考
https://www.cnblogs.com/ivanfu/archive/2012/04/06/2434983.html
https://www.guru99.com/pl-sql-bulk-collect.html
https://xuejiangtao.iteye.com/blog/1168570
https://oracle-base.com/articles/9i/bulk-binds-and-record-processing-9i
483
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
