當編寫PL/SQL塊時,靜態SQL語句只能完成一些固定任務。為了使得PL/SQL塊可以靈活的處理SQL語句,需要使用動態SQL。動態SQL包括本地動態SQL和DBMS_SQL動態SQL兩種實現方法,其中本地動態SQL只適用於oracle服務器端,而DBMS_SQL動態SQL不僅適用於oracle服務器端,而且可以在某些客戶端應用程序中使用。從oracle 11g開始,允許使用超過32Kb的本地動態SQL語句。本節學習任務:
例如:簡單的CRUD操作
SELECT * FROM emp_learn WHERE employee_id=v_eno;
INSERT INTO emp_learn (employee_id,first_name) VALUES (v_eid,v_name);
UPDATE emp_learn SET salary=salary*1.1 WHERE first_name=v_name;
DELETE FROM emp_learn WHERE employee_id=v_eid;
CREATE TABLE temp(col1 INT,col2 VARCHAR2(20));--DDL
GRANT SELECT ON emp_learn TO smith;--DCL
DELETE FROM emp_learn WHERE salary>:a;--帶有占位符
SELECT first_name,salary FROM emp_learn WHERE employee_id=:1;--帶有占位符
a)靜態SQL是在編寫PL/SQL塊時直接嵌入的SQL語句,動態SQL是在執行PL/SQL塊時動態輸入的SQL語句。
b)靜態SQL性能優於動態SQL。因此當編寫PL/SQL塊時,如果功能完全確定,則使用靜態SQL;如果不能確定要執行的SQL語句,則使用動態SQL。
a)使用EXECUTE IMMEDIATE語句:用於處理 除多行查詢之外的任何其他動態SQL,包括DDL語句、DCL語句、DML語句以及單行SELECT語句。
b)使用OPEN-FOR、FETCH和CLOSE語句:用於處理多行查詢操作。
c)使用批量動態SQL:用於加快SQL語句處理,提高PL/SQL的性能。
EXCUTE IMMEDIATE dyn_string;
dyn_string指定存放DDL、DCL或者DML的語句。
示例:
DECLARE ddl_string VARCHAR2(200):='&ddl_string';--DDL dcl_string VARCHAR2(200):='&dcl_string';--DCL dml_string VARCHAR2(200):='&dml_string';--DML BEGIN EXECUTE IMMEDIATE ddl_string; EXECUTE IMMEDIATE dcl_string; EXECUTE IMMEDIATE dml_string; END;
EXCUTE IMMEDIATE dyn_string USING bind_arg1 [,bind_arg2,...]
EXCUTE IMMEDIATE dyn_string RETURNING INTO variable1 [, variable2,...];
1 DECLARE 2 dml_string VARCHAR2(200):='UPDATE emp_learn SET salary=:sal WHERE LOWER(first_name)=LOWER(:name) RETURNING salary INTO :newsal'; 3 v_newsal emp_learn.salary%TYPE; 4 BEGIN 5 EXECUTE IMMEDIATE dml_string USING &1,'&2' RETURNING INTO v_newsal; 6 dbms_output.put_line('新工資為:'||v_newsal); 7 END;
EXCUTE IMMEDIATE dyn_string INTO variable1 [,variable2,...] [USING bing_arg1 [,bind_arg2,...]];
示例:處理單行查詢
DECLARE v_select1 VARCHAR2(200):='SELECT * FROM emp_learn WHERE employee_id=&eno'; v_select2 VARCHAR2(200):='SELECT * FROM emp_learn WHERE employee_id=:eno'; emp_record emp%ROWTYPE; BEGIN EXECUTE IMMEDIATE v_select1 INTO emp_record; dbms_output.put_line('姓名:'||emp_record.first_name||',職位:'||emp_record.job_id); EXECUTE IMMEDIATE v_select2 INTO emp_record USING &1; dbms_output.put_line('姓名:'||emp_record.first_name||',職位:'||emp_record.job_id); END;
EXCUTE IMMEDIATE只能用於處理單行查詢,為了動態處理select語句所返回的多行數據,需要定義游標變量,並使用open for、fetch和close語句共同完成,步驟:
a)定義游標變量:TYPE cursor_type IS REF CURSOR;
cursor_var cursor_type;
b)打開游標變量獲取結果集:OPEN cursor_var FOR dyn_string [USING bind_args];
c)提取數據:FETCH cursor_var INTO vars;
d)關閉游標變量:CLOSE cursor_var;
示例:處理多行查詢
DECLARE TYPE emp_cursor_type IS REF CURSOR; emp_cursor emp_cursor_type; emp_record emp_learn%ROWTYPE; v_string VARCHAR2(200):='SELECT * FROM emp_learn WHERE department_id=:dno'; BEGIN OPEN emp_cursor FOR v_string USING &1; LOOP FETCH emp_cursor INTO emp_record; EXIT WHEN emp_cursor%NOTFOUND; dbms_output.put_line('雇員名:'||emp_record.first_name); END LOOP; CLOSE emp_cursor; END;
使用在動態SQL中使用批量綁定,可以加快批量數據的處理速度,提高性能。需要注意的是:使用批量綁定時,集合元素需要使用SQL固有數據類型(例如number、char等),而不能使用PL/SQL特有的數據類型(例如BINARY_INTGER、BOOLEAN等)。
FORALL index IN lower_boud..upper_bound
EXECUTE IMMEDIATE dyn_string ...;
DECLARE TYPE name_table_type IS TABLE OF emp.first_name%TYPE; emp_name_table name_table_type:=name_table_type('&name1','&name2','&name3'); dml_string VARCHAR2(100):='UPDATE emp_learn SET salary=salary*1.2 WHERE LOWER (first_name)=LOWER(:1)'; BEGIN FORALL i IN 1..emp_name_table.count EXECUTE IMMEDIATE dml_string USING emp_name_table(i); END;
批量綁定查詢語法:EXECUTE IMMEDIATE dyn_string BULK COLLECT INTO collect_vars;
在DML返回子句上使用批量綁定語法:EXECUTE IMMEDIATE dyn_string RETURNING BULK COLLECT INTO collect_vars;
1 DECLARE 2 TYPE emp_name_table_type IS TABLE OF emp_learn.first_name%TYPE; 3 TYPE enp_sal_table_type IS TABLE OF emp_learn.salary%TYPE; 4 emp_name_table emp_name_table_type;--裝雇員名 5 emp_sal_table enp_sal_table_type; --裝工資 6 v_deptno NUMBER:=&dno; 7 c_string VARCHAR2(100):='SELECT first_name,salary FROM emp_learn WHERE department_id='||v_deptno; 8 dml_string VARCHAR(150):= 9 'UPDATE emp_learn SET salary=salary*1.1 WHERE department_id=:1 RETURNING first_name,salary INTO :2,:3'; 10 BEGIN 11 EXECUTE IMMEDIATE c_string BULK COLLECT INTO emp_name_table,emp_sal_table;--裝載更新前的雇員名和工資 12 FOR i IN 1..emp_name_table.count LOOP 13 dbms_output.put_line('雇員:'||emp_name_table(i)||',前工資:'||emp_sal_table(i)); 14 END LOOP; 15 EXECUTE IMMEDIATE dml_string USING v_deptno RETURNING BULK COLLECT INTO emp_name_table,emp_sal_table;--執行更新並返回數據 16 FOR i IN 1..emp_name_table.count LOOP 17 dbms_output.put_line('雇員:'||emp_name_table(i)||',後工資:'||emp_sal_table(i)); 18 END LOOP; 19 END;
這個功能可以一次性提取游標結果集中的所有數據,很方便哦,使用一次後我就喜歡上它了,語法:
FETCH cursor_var BULK COLLECT INTO collect_var;
1 DECLARE 2 TYPE emp_name_cursor_type IS REF CURSOR; 3 TYPE emp_name_table_type IS TABLE OF emp_learn.first_name%TYPE; 4 emp_name_cursor emp_name_cursor_type; 5 emp_name_table emp_name_table_type; 6 c_string VARCHAR2(150):='SELECT first_name FROM emp_learn WHERE department_id=:1'; 7 BEGIN 8 OPEN emp_name_cursor FOR c_string USING '&dno'; 9 FETCH emp_name_cursor BULK COLLECT INTO emp_name_table; 10 CLOSE emp_name_cursor; 11 FOR i IN 1..emp_name_table.count LOOP 12 dbms_output.put_line('名字:'||emp_name_table(i)); 13 END LOOP; 14 END;