plsql 什么时候用游标和 bulk collect

2025-01-23 12:03:03

推荐回答（1个）

回答1：

[sql] view plain copy

当运行一个pl/sql程序时， pl/sql语句引擎会执行pl/sql语句。但如果在这个过程中引擎遇到sql语句，它会把这个语句传给sql引擎（后台发生上下文切换）。
在PL/SQL和SQL引擎(engines)中，太多的上下文切换(context switches)会影响性能。这个会发生在当一个循环为集合中的每个元素执行一个单个SQL语句时。而使用批挷定能显著提高性能。
在SQL语句中，为PL/SQL变量指定值称为挷定(binding)，
　　DML语句能传递所有集合元素到一个单个操作中，这过程称为批挷定（bulk binding）。
　　如果集合有20个元素，批挷定让你用单个操作等效于执行与20个SELECT，INSERT， UPDATE或DELETE语句。这个技术通过减少在PL/SQL和SQL引擎(engines)间的上下文切换来提高性能。批挷定包括：
　　1．带INSERT, UPDATE, and DELETE语句的批挷定：在FORALL语句中嵌入SQL语句
　　2．带SELECT语句的批挷定：在SELECT语句中用BULK COLLECT 语句代替INTO 。

作用：BULK COLLECT提供对数据的高速检索。
优点：可以将多个行引入一个或多个集合中，而不是单独变量或记录中,减少了上下文切换，性能高。
缺点：1.消耗更多的内存(PGA)；由于该数集合据存储在每个会话中,假设一个会话多消耗5M，内存，那么1000个就消耗约5G内存。
2.BULK COLLECT INTO的目标对象必须是集合类型。
3.不能对使用字符串类型作键的关联数组使用BULK COLLECT子句。
4.如果有一个隐式的数据类型转换，复合目标的集合(如对象类型集合)就不能用于 BULK COLLECTINTO 子句中。
bulk collect语句可以使用三种方式：
1在select into语句中使用bulk collect

2在fetch into中使用bulk collect

3在returning into中使用bulk collect
[sql] view plain copy
create table empl_tbl(last_name varchar2(20),
first_name varchar2(10),
salary number(10));

--创建表
create table demo_t(
id number(5),
name varchar2(50),
sales number
);

[sql] view plain copy
--在select into语句中使用bulk collect
DECLARE
TYPE contractList IS TABLE OF sad.cm_contracts_t.contract_number%TYPE;
contracts contractList;
BEGIN
-- Limit the number of rows to 100.
SELECT contract_number BULK COLLECT INTO contracts FROM sad.cm_contracts_t
WHERE ROWNUM <= 100;

--在fetch into中使用bulk collect
DECLARE
TYPE contracts_list IS TABLE OF sad.cm_contracts_t%ROWTYPE;
contracts_l contracts_list;
CURSOR c1 IS
SELECT contract_number,contract_id FROM contracts_l WHERE deptno > 10;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs;
END;

--在returning into中使用bulk collect
--CREATE TABLE item AS SELECT * FROM sad.sad_prm_item_ti;
declare
-- Local variables here
TYPE lineid IS TABLE OF item.lineid%TYPE;
line_id lineid;
begin
DELETE FROM item t WHERE t.lineid='1'
RETURNING lineid BULK COLLECT INTO line_id;
dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN line_id.FIRST .. line_id.LAST
LOOP
dbms_output.put_line('item : '||line_id(i));
END LOOP;
end;

[sql] view plain copy
-- Created on 2013/12/4 by ZWX190516
DECLARE
-- Local variables here
all_row NUMBER(10);
all_rows_bluk NUMBER(10);
temp_last_name empl_tbl.last_name%TYPE;
--首先，定义一个Index-by表数据类型
TYPE last_name_tab IS TABLE OF empl_tbl.last_name%TYPE INDEX BY BINARY_INTEGER;
last_name_arr last_name_tab;
--定义一个Index-by表集合变量
dis_count NUMBER;

--数据量
data_count NUMBER :=100000;
--记录时间
t1 NUMBER;
t2 NUMBER;
t3 NUMBER;
t4 NUMBER;
BEGIN
all_row := 0;
all_rows_bluk := 0;
temp_last_name := ' ';

--写入1W笔数据
FOR i IN 1 .. data_count LOOP
INSERT INTO empl_tbl
(last_name, first_name, salary)
VALUES
('carl' || (i), 'wu' || (data_count-1), i);
END LOOP;

COMMIT;

--记录时间
SELECT DBMS_UTILITY.get_time INTO t1 FROM DUAL;

--查询不相同数据使用时间
SELECT COUNT(DISTINCT last_name) "Distinct Last Name"
INTO dis_count
FROM empl_tbl;

--记录时间
SELECT DBMS_UTILITY.get_time INTO t2 FROM DUAL;

--使用简单游标实现
BEGIN
FOR cur IN (SELECT last_name FROM empl_tbl ORDER BY last_name) LOOP
IF cur.last_name != temp_last_name THEN
all_row := all_row + 1;
END IF;
temp_last_name := cur.last_name;
END LOOP;
dbms_output.put_line('all_rows are ' || all_row);
END;

--记录时间
SELECT DBMS_UTILITY.get_time INTO t3 FROM DUAL;
--使用Bulk Collect来实现
BEGIN
SELECT last_name BULK COLLECT INTO last_name_arr FROM empl_tbl;
FOR i IN 1 .. last_name_arr.count LOOP
IF temp_last_name != last_name_arr(i) THEN
all_rows_bluk := all_rows_bluk + 1;
END IF;
temp_last_name := last_name_arr(i);
END LOOP;
dbms_output.put_line(' BULK COLLECT all_rows are ' || all_rows_bluk);
END;
--记录时间
SELECT DBMS_UTILITY.get_time INTO t4 FROM DUAL;

DBMS_OUTPUT.put_line('Execution Time (hsecs)');
DBMS_OUTPUT.put_line('---------------------');
DBMS_OUTPUT.put_line('distince: ' || TO_CHAR(t2 - t1));
DBMS_OUTPUT.put_line('cursor one by one : ' || TO_CHAR(t3 - t2));
DBMS_OUTPUT.put_line('cursor BULK COLLECTe : ' || TO_CHAR(t4 - t3));

END;

最新问答

plsql 什么时候用游标和 bulk collect

暑假就要去美国了,还没给自己取过英文名。

几样古董，请专家鉴定下值多少钱，谢谢

奥迪车A6L出风口只有一个出风口制冷,其余出风口是热风

启动计算机时,听见有‘嘀……嘀‘声、黑屏,其故障为

比利时签证递交后要多久才能办下来

做小吃的，现在卖的不好，想换项目，求建议，经常去学校去卖，现在有手抓饼，烤冷面，熏肉大饼，鸡柳薯条

农村的房子（平房）办房产证有必要吗？办上有什么好处？

倩女幽魂用脚本会封号吗

鉴别盐酸和稀硫酸，最好选用的试剂是（　　）A．石蕊试液B．锌粒C．AgNO3溶液D．BaCl2溶

plsql 什么时候用游标 和 bulk collect

plsql 什么时候用游标和 bulk collect