有经验的南昌网站建设,网站开发实训心得,网络营销课程学什么,我想做个网站要多少钱PL/SQL程序中运行SQL语句是存在开销的#xff0c;因为SQL语句是要提交给SQL引擎处理#xff0c;这种在PL/SQL引擎和SQL引擎之间的控制转移叫做上下文却换#xff0c;每次却换时#xff0c;都有额外的开销 请看下图#xff1a; 但是#xff0c;FORALL和BULK COLLEC… PL/SQL程序中运行SQL语句是存在开销的因为SQL语句是要提交给SQL引擎处理这种在PL/SQL引擎和SQL引擎之间的控制转移叫做上下文却换每次却换时都有额外的开销 请看下图 但是FORALL和BULK COLLECT可以让PL/SQL引擎把多个上下文却换压缩成一个这使得在PL/SQL中的要处理多行记录的SQL语句执行的花费时间骤降 请再看下图 BULK COLLECT 加速查询
采用BULK COLLECT可以将查询结果一次性地加载到collections中而不是通过cursor一条一条地处理 可以在select into fetch into returning into语句使用BULK COLLECT 注意在使用BULK COLLECT时所有的INTO变量都必须是collections
select into语句中使用bulk collect
DECLARE TYPE sallist IS TABLE OF employees.salary%TYPE;sals sallist;
BEGINSELECT salary BULK COLLECT INTO sals FROM employees where rownum50;--接下来使用集合中的数据
END;
/
在fetch into中使用bulk collect
DECLARETYPE deptrectab IS TABLE OF departments%ROWTYPE;dept_recs deptrectab;CURSOR cur IS SELECT department_id,department_name FROM departments where department_id10;
BEGINOPEN cur;FETCH cur BULK COLLECT INTO dept_recs;--接下来使用集合中的数据
END;
/
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;
BEGINDELETE emp WHERE department_id30RETURNING employee_id,last_name BULK COLLECT INTO enums,names;DBMS_OUTPUT.PUT_LINE(deleted||SQL%ROWCOUNT||rows:);FOR i IN enums.FIRST .. enums.LASTLOOPDBMS_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 BULK COLLECT 对大数据DELETE UPDATE的优化
DECLARE
--按rowid排序的cursor
--删除条件是ooxx这个需根据实际情况来定CURSOR mycursor IS SELECT rowid FROM t WHERE OOXX ORDER BY rowid;TYPE rowid_table_type IS TABLE OF rowid index by pls_integer;v_rowid rowid_table_type;
BEGINOPEN mycursor;LOOPFETCH mycursor BULK COLLECT INTO v_rowid LIMIT 5000;--5000行提交一次EXIT WHEN v_rowid.count0;FORALL i IN v_rowid.FIRST..v_rowid.LASTDELETE t WHERE rowidv_rowid(i);COMMIT;END LOOP;CLOSE mycursor;
END;
/
限制BULK COLLECT 提取的记录数
语法 FETCH cursor BULK COLLECT INTO ...[LIMIT rows] 其中rows可以是常量变量或者求值的结果是整数的表达式 假设你需要查询并处理1W行数据你可以用BULK COLLECT一次取出所有行然后填充到一个非常大的集合中 可是这种方法会消耗该会话的大量PGAAPP可能会因为PGA换页而导致性能下降 这时LIMIT子句就非常有用它可以帮助我们控制程序用多大内存来处理数据
DECLARECURSOR allrows_cur IS SELECT * FROM employees;TYPE employee_aat IS TABLE OF allrows_cur%ROWTYPE INDEX BY BINARY_INTEGER;v_emp employee_aat;
BEGINOPEN allrows_cur;LOOPFETCH allrows_cur BULK FETCH INTO v_emp LIMIT 100;/*通过扫描集合对数据进行处理*/FOR i IN 1 .. v_emp.countLOOPupgrade_employee_status(v_emp(i).employee_id);END LOOP;EXIT WHEN allrows_cur%NOTFOUND;END LOOP;CLOSE allrows_cur;
END;
/ FORALL注意事项
使用FORALL时应该遵循如下规则
FORALL语句的执行体必须是一个单独的DML语句比如INSERTUPDATE或DELETE。不要显式定义index_row它被PL/SQL引擎隐式定义为PLS_INTEGER类型并且它的作用域也仅仅是FORALL。这个DML语句必须与一个集合的元素相关并且使用FORALL中的index_row来索引。注意不要因为index_row导致集合下标越界。lower_bound和upper_bound之间是按照步进 1 来递增的。在sql_statement中不能单独地引用集合中的元素只能批量地使用集合。在sql_statement中使用的集合下标不能使用表达式。
BULK COLLECT介绍
BULK COLLECT子句会批量检索结果即一次性将结果集绑定到一个集合变量中并从SQL引擎发送到PL/SQL引擎。
通常可以在SELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。下面逐一描述BULK COLLECT在这几种情形下的用法
BULK COLLECT的注意事项
BULK COLLECT INTO 的目标对象必须是集合类型。只能在服务器端的程序中使用BULK COLLECT如果在客户端使用就会产生一个不支持这个特性的错误。不能对使用字符串类型作键的关联数组使用BULK COLLECT子句。复合目标(如对象类型)不能在RETURNING INTO子句中使用。如果有多个隐式的数据类型转换的情况存在多重复合目标就不能在BULK COLLECT INTO子句中使用。如果有一个隐式的数据类型转换复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO子句中DECLARE
CURSOR cur IS
select * from NEWLOG4 t where TO_CHAR(t.autudt,YYYY-MM-DD) 2021-03-09and pushstate3;
TYPE rec IS TABLE OF NEWLOG4%ROWTYPE;
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT 5000;
FORALL i IN 1 .. recs.COUNT
INSERT INTO NEWLOG4_202103 VALUES recs (i);COMMIT;EXIT WHEN cur%NOTFOUND;END LOOP;CLOSE cur;END;INSERT/*parallel(10)*/ INTO NEWLOG4_202103 select * from NEWLOG4 t where TO_CHAR(t.autudt,YYYY-MM-DD) 2021-03-09and pushstate3;delete /*parallel(10)*/ from NEWLOG4 nologging where TO_CHAR(autudt,YYYY-MM-DD) 2021-03-09and pushstate3;INSERT/*parallel(10)*/ INTO NEWLOG4_202103 select * from NEWLOG4 t where TO_CHAR(t.autudt,YYYY-MM-DD) 2021-03-09and pushstate3;alter session enable parallel dml; DECLARE
CURSOR cur IS
select/*parallel(8)*/ rowid from NEWLOG4 t where TO_CHAR(t.autudt,YYYY-MM-DD) 2021-03-03and pushstate3;
--TYPE rec IS TABLE OF NEWLOG4%ROWTYPE;
TYPE rowid_table_type IS TABLE OF rowid index by pls_integer;v_rowid rowid_table_type;
--recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO v_rowid LIMIT 1000;
EXIT WHEN v_rowid.count0;
FORALL i IN 1 .. v_rowid.COUNT
---delete NEWLOG4 where current of recs (i);
delete/*parallel(8)*/ from NEWLOG4 nologging where rowidv_rowid (i);COMMIT;EXIT WHEN cur%NOTFOUND;END LOOP;CLOSE cur;END;DECLARE
v_exists NUMBER (10, 0);
v_exists1 NUMBER (10, 0);
--recs rec;
BEGIN
select /*parallel(12)*/ count(1)into v_exists from NEWLOG4 t where TO_CHAR(t.autudt,YYYY-MM-dd) 2021-03-06and pushstate3;
WHILE (TRUE) LOOPdelete /*parallel(12)*/ from NEWLOG4 nologging where TO_CHAR(autudt,YYYY-MM-DD) 2021-03-06and pushstate3;EXIT WHEN v_exists1v_exists1;v_exists1: v_exists11;if (v_exists10000) thenCOMMIT;end if;END LOOP;COMMIT;END;select count(1) from NEWLOG4 t where TO_CHAR(t.autudt,YYYY-MM-DD) 2021-03-01select b.sid, b.username, b.serial#, a.spid, b.paddr, c.sql_text, b.machinefrom v$process a, v$session b, v$sqlarea cwhere a.addr b.paddrand b.sql_hash_value c.hash_value;
CREATE OR REPLACE PROCEDURE NEWLOG4_SUB_TABLE2 (delete_date in varchar2)
IS-- table_name1 VARCHAR2(50);-- create_table_sql VARCHAR2(4000);
-- insert_data_sql VARCHAR2(4000);delete_data_sql VARCHAR2(4000);
-- v_exists INT:0;-- v_exists NUMBER (10, 0);CURSOR cur IS
select * from NEWLOG4 t where TO_CHAR(t.autudt,YYYY-MM-DD) delete_date and pushstate3;
TYPE rec IS TABLE OF NEWLOG4%ROWTYPE;
recs rec;
BEGIN--将FATHER_TABLE表中取上月记录 添加到新创建的分表中。-- insert_data_sql : INSERT INTO || table_name1 || SELECT * FROM NEWLOG4 WHERE autudt (systimestamp - NUMTODSINTERVAL(30,day)) and pushstate3;-- EXECUTE IMMEDIATE insert_data_sql;
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT 5000;
FORALL i IN 1 .. recs.COUNT
INSERT INTO NEWLOG4_202103_10 VALUES recs (i);COMMIT;EXIT WHEN cur%NOTFOUND;END LOOP;CLOSE cur;--删除FATHER_TABLE表中时间在上个月范围内的所有数据--delete_data_sql : DELETE FROM NEWLOG4 WHERE autudt (systimestamp-NUMTODSINTERVAL(30,day))and pushstate3;delete_data_sql :delete /*parallel(10)*/ from NEWLOG4 nologging where TO_CHAR(t.autudt,YYYY-MM-DD) delete_date and pushstate3;EXECUTE IMMEDIATE delete_data_sql;COMMIT;--EXCEPTION--WHEN OTHERS THEN-- ROLLBACK;
END NEWLOG4_SUB_TABLE2;CREATE OR REPLACE PROCEDURE NEWLOG4_SUB_TABLE
IStable_name1 VARCHAR2(50);create_table_sql VARCHAR2(4000);insert_data_sql VARCHAR2(4000);delete_data_sql VARCHAR2(4000);
-- v_exists INT:0;v_exists NUMBER (10, 0);
BEGINSELECT NEWLOG4_ || TO_CHAR(ADD_MONTHS(SYSDATE, -1), YYYYMM) INTO table_name1 FROM DUAL;select count(1) into v_exists from user_tables where table_nameUPPER(table_name1);--dbms_output.put_line(sname);--dbms_output.put_line(table_name1);if (v_exists 1)then-- dbms_output.put_line(sname);create_table_sql : create table || table_name1 || (autudt TIMESTAMP(6),authentype VARCHAR2(2000),userid VARCHAR2(2000),orgid VARCHAR2(2000),org2id VARCHAR2(2000),realname VARCHAR2(2000),success VARCHAR2(2000),idpname VARCHAR2(2000),idpip VARCHAR2(2000),vistorip VARCHAR2(2000),vistorbrowser VARCHAR2(2000),spid VARCHAR2(2000),spurl VARCHAR2(2000),info VARCHAR2(2000),autdesc VARCHAR2(2000),taketime VARCHAR2(2000),orgnamefullpath VARCHAR2(2000),ines INTEGER default 0,logid VARCHAR2(32),inputaccount VARCHAR2(2000),channel NUMBER(32),pushstate NUMBER(2) default 0,appid VARCHAR2(50));EXECUTE IMMEDIATE create_table_sql;commit;end if;--将FATHER_TABLE表中取上月记录 添加到新创建的分表中。insert_data_sql : INSERT INTO || table_name1 || SELECT * FROM NEWLOG4 WHERE autudt (systimestamp - NUMTODSINTERVAL(30,day)) and pushstate3;EXECUTE IMMEDIATE insert_data_sql;--删除FATHER_TABLE表中时间在上个月范围内的所有数据delete_data_sql : DELETE FROM NEWLOG4 WHERE autudt (systimestamp-NUMTODSINTERVAL(30,day))and pushstate3;EXECUTE IMMEDIATE delete_data_sql;COMMIT;--EXCEPTION--WHEN OTHERS THEN-- ROLLBACK;
END NEWLOG4_SUB_TABLE;CREATE OR REPLACE PROCEDURE NEWLOG4_day_TABLE (delete_date in varchar2)
ISinsert_data_sql VARCHAR2(4000);delete_data_sql VARCHAR2(4000);CURSOR cur IS
select * from NEWLOG4 t where TO_CHAR(t.autudt,YYYY-MM-DD) delete_date and pushstate3;
TYPE rec IS TABLE OF NEWLOG4%ROWTYPE;
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT 5000;
FORALL i IN 1 .. recs.COUNT
--EXECUTE IMMEDIATE insert_data_sql;
INSERT INTO NEWLOG4_DAY_INTERVAL_PARTITION VALUES recs (i);COMMIT;EXIT WHEN cur%NOTFOUND;END LOOP;CLOSE cur;--EXCEPTION--WHEN OTHERS THEN-- ROLLBACK;
END NEWLOG4_day_TABLE;
