PLSQL批量操作与动态SQL.doc

1、 批量绑定：批量绑定是指单条SQL操作能传递所有PLSQL表的数据。通过批量绑定，可以极大地加快数据处理速度，提高应用程序性能。批量绑定语句forall主要针对insert、update、delete等语句，forall绑定的表必须是PLSQL表，即单列多行的数据结构(类似于c语言中的数组)，如：索引表、嵌套表、变长数组，不能是记录表。2、批量绑定语句forall的语法：(一次次的提取数据)语法一：(i作为PLSQL表的下标被引用)forall i in lower.upper sql_statement;解释：对PLSQL表中下标为i(取值范围为lower和upper之间，包括边界，i可以是其他满足命名规则的名字)的数据进行批量绑定。例子：DECLARE TYPE int_table_type IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;-声明索引表类型，其中的数-据类型为integer，索引表类型是PLSQL表类型的一种 TYPE varchar2_table_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; -声明索引表类型，-其中的数据类型为varchar2(20) name_table varchar2_table_type;-用声明的索引表类型声明变量，即声明一个PLSQL表 id_table int_table_type; -用声明的索引表类型声明变量，即声明另一个PLSQL表BEGIN FOR i IN 1.100000 LOOP id_table(i) := i;-为PLSQL表赋值 name_table(i) := aaa | i;-为另一个PLSQL表赋值 END LOOP; FORALL i IN 1.100000 INSERT INTO intest VALUES(id_table(i),name_table(i);- 将PLSQL表-id_table和name_table下标为i的数据插入到intest表中，这里i从1到100000END;/语法二：(用于下标不连续的情况)forall i in indices of collection between lower and upper sql_statement;解释：对PLSQL表中下标为i（i的取值范围为indices取得的collection(PLSQL表)的所有下标值，同时i在范围为lower到upper之间，包括边界）的数据进行批量绑定。例子：DECLARE TYPE int_table_type IS TABLE OF binary_integer INDEX BY BINARY_INTEGER; id_table int_table_type;BEGIN FOR i IN 1.10 LOOP id_table(i) := i; END LOOP; FORALL i IN INDICES OF id_table BETWEEN 1 AND 5 DELETE FROM intest WHERE iid = id_table(i);-对PLSQL表id_table下标为i(i取PLSQL表id_table的下标值，同时满足大于等于1小于等于5)的数据进行-批量绑定同时进行相关操作，这里表intest的iid等于id_table(1.5)的数据会被删END;/语法三：(用于处理重复数据)forall i in values of index_collection sql_statement;解释：对PLSQL表中下标为i(i的取值范围为index_collection表中的值)的数据进行批量绑定。例子：DECLARE TYPE int_table_type IS TABLE OF binary_integer INDEX BY BINARY_INTEGER; TYPE varchar2_table_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; name_table varchar2_table_type; id_table int_table_type;BEGIN id_table(1):=5; id_table(5):=1; name_table(5):=aa5; name_table(1):=aa4; name_table(3):=aa4; FORALL i IN VALUES OF id_table insert into intest values(id_table(i),name_table(i);-对PLSQL表id_table和name_table下标为i(i取PLSQL表id_table中的值)的数据进行批量绑定，同时进行-操作。这里会将id_table(1) 、id_table(5) 、name_table(1) 、name_table(5)插入到intest表中END;/3、批量提取(一次提取大批数据)：bulk collect用于取得批量数据，适用于select into、fetch into和DML语句(select、update、insert、delete等)。语法：bulk collect into collection1,collection2select into中使用批量提取的例子：DECLARE TYPE name_table_type IS TABLE OF emp.ename%type INDEX BY BINARY_INTEGER; name_table name_table_type; BEGIN SELECT ename BULK COLLECT INTO name_table FROM emp; FOR i IN 1.name_table.count LOOP dbms_output.put_line(name_table(i); END LOOP;END;/DML语句中使用批量提取的例子：DECLARE TYPE varchar2_table_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; name_table varchar2_table_type;BEGIN DELETE FROM intest WHERE iid 100 RETURNING iname BULK COLLECT INTO name_table;-将intest表中iid小于100的记录删除，同时将删除的iname批量提取到name_table中 FOR i IN 1.name_table.count LOOP dbms_output.put_line(name_table(i); END LOOP;END;/fetch into中使用批量提取的例子：DECLARE TYPE varchar2_table_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; CURSOR int_cursor IS SELECT iname FROM intest; name_table varchar2_table_type; BEGIN OPEN int_cursor; FETCH int_cursor BULK COLLECT INTO name_table; CLOSE int_cursor; FOR i IN 1.name_table.count LOOP dbms_output.put_line(name_table(i); END LOOP;END;/4、动态SQL：创建过程的时候就已经编译好的sql叫静态sql，这种sql只能用于处理特定的操作，若要根据情况做出不同的操作，则必须使用动态SQL。动态sql是在运行时建立的sql语句，若要根据不同情况生成相应的sql语句，就必须使用动态sql了。使用动态sql的例子：CREATE OR REPLACE PROCEDURE grant_sys_priv( v_pri_name VARCHAR2, v_username VARCHAR2)IS sql_str VARCHAR2(200);BEGIN sql_str := grant | v_pri_name | to | v_username; EXECUTE IMMEDIATE sql_str;END;/使用如上过程为scott用户授予create any view权限：exec grant_sys_priv(create any view,scott);参数被单引号引起来表示字符串类型。5、绑定变量：使用绑定变量可以避免硬解析，提高性能，但绑定变量不能替换实际的数据库对象(表、视图、列等)。绑定变量用:开头，使用using关键字或RETURNING INTO为绑定的变量赋值。如果变量作为参数时，还是必须得用字符串连接，如：create or replace procedure drop_table(v_tablename in varchar2)isbegin execute immediate drop table | v_tablename; -不能写成execute immediate drop table :tablename using v_tablename;写成这样执行时会出错，-即执行exec drop_table(t);时出错end;/不使用绑定变量的例子：BEGINFOR i IN 10.50 LOOPEXECUTE IMMEDIATE select ename from scott.emp where deptno = | i;-这条语句不能看到查询结果 END LOOP;END;/使用单个绑定变量的例子：BEGINFOR i IN 10.50 LOOP EXECUTE IMMEDIATE select ename from scott.emp where deptno = :deptno USING i; END LOOP; END;/使用多个绑定变量的例子：BEGIN EXECUTE IMMEDIATE update scott.emp set sal = (sal+:addsal) where deptno = :dno USING &sal,&dno; END;/解释：:addsal和:dno是绑定变量，它们分别用&sal和&dno为其赋值，&代表在输入框中输值。带返回值和绑定变量的例子：(returning into语句只能用在DELETE, EXECUTE IMMEDIATE, INSERT和UPDATE语句中，若语句未对任何数据行有影响，则returning into语句将不可用)DECLARE salary scott.emp.sal%TYPE;BEGIN EXECUTE IMMEDIATE update scott.emp set sal = (sal + :addsal ) where empno = :empno RETURNING sal into :salary USING &addsal,&empno RETURNING INTO salary; dbms_output.put_line(salary);END;/处理多行数据，其中带有绑定变量：DECLARE TYPE emp_cursor_type IS REF CURSOR; emp_cursor emp_cursor_type; emp_record emp%ROWTYPE;BEGIN OPEN emp_cursor FOR select * from emp where deptno = :deptno USING &deptno; LOOP FETCH emp_cursor INTO emp_record; EXIT WHEN emp_cursor%NOTFOUND; dbms_output.put_line(emp_record.ename); END LOOP; CLOSE emp_cursor;END;/绑定变量、批量提取综合运用：declare type ename_table_type is table of emp.ename%type index by binary_integer; type sal_table_type is table of emp.sal%type index by binary_integer; ename_table ename_table_type; sal_table sal_table_type; sql_stat varchar2(100);begin sql_stat := update emp s