DBMS_SQL
dbms_sql
用途：執行動態語句
由於execute immediate語句的簡捷易用，dbms_sql包的使用已經非常少了，而且很早就傳言說要廢棄，但其功能強大，而且有
些時候無法被替代，因此還是值得學習的。
使用場景：當我們的單條語句超過32K時，有位置數目或型別的輸入、輸出引數時，需要更精細化的控制時只能使用DBMS_SQL包
。
官方文件plsql package中包含了幾十頁該包的講解，在說明其功能的同時，也暗示了其重要地位。
使用方法：
（1）對於一般的select操作：
開啟遊標-》解析-》定義列-》執行-》插入行（fetch）-》關閉遊標
（2）對於insert、update語句：
開啟遊標-》解析-》繫結變數-》執行-》關閉遊標
（3）對ddl和delete語句：
開啟遊標-》解析-》執行-》關閉遊標
開啟：開啟遊標時需要給遊標命名
解析階段：對動態語句的語法語義進行檢查，並與遊標名對應
列定義、列長度定義、列值定義：對列進行說明
變數繫結或陣列繫結：對於大多數動態語句，需要在執行時輸入變數作為查詢條件，變數繫結或陣列繫結階段就是定義這些變
量的屬性
執行階段：呼叫execute函式執行語句
插入（fetch）：fetch_Rows函式返回滿足查詢條件的資料
關閉遊標
在解析後立即呼叫last_error_position函式可以獲得出錯資訊。
例1：編譯時已經知道語句的內容，但不知道要查詢的值
功能：刪除emp表中salary列數值大於執行時所輸入數值的所有資料
CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS
cursor_name INTEGER; --遊標名 integer型別
rows_processed INTEGER; --執行遊標的語句 integer型別
BEGIN
cursor_name := dbms_sql.open_cursor; --開啟遊標
DBMS_SQL.PARSE(cursor_name, 'DELETE FROM emp WHERE sal > :x',dbms_sql.native);--遊標名為cursor_name，內容是單
引號中內容，native是固定的格式
DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', salary);--將輸入引數salary賦給遊標cursor_name中變數：x
rows_processed := dbms_sql.execute(cursor_name);--執行遊標cursor_name
DBMS_SQL.close_cursor(cursor_name);--關閉遊標
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
此過程完全可以用其他方式替代。
執行結果：
SQL>
Procedure created
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
14 rows selected
SQL> exec demo(salary => '1500');
PL/SQL procedure successfully completed
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
7 rows selected
================================
例2：DDL語句或不含值的DML（DELTET）的執行 將語句作為變數輸入，包中解析並執行
CREATE OR REPLACE PROCEDURE exec(STRING IN varchar2) AS --string是要輸入的語句
cursor_name INTEGER;
ret INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.native);
ret := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
呼叫語句為：
exec('create table acct(c1 integer)');
exec@hq.com('CREATE TABLE acct(c1 INTEGER)');
================================
例3：假設目標表和源表都有以下列：
(id NUMBER，
name VARCHAR2(30)，
birthdate DATE)
建立儲存過程進行資料的複製：
CREATE OR REPLACE PROCEDURE copy (
source IN VARCHAR2,
destination IN VARCHAR2) IS
id_var NUMBER;
name_var VARCHAR2(30);
birthdate_var DATE;
source_cursor INTEGER;
destination_cursor INTEGER;
ignore INTEGER;
BEGIN
-- Prepare a cursor to select from the source table:
source_cursor := dbms_sql.open_cursor;
DBMS_SQL.PARSE(source_cursor,'SELECT id, name, birthdate FROM ' || source,DBMS_SQL.native);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id_var);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name_var, 30);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, birthdate_var);
ignore := DBMS_SQL.EXECUTE(source_cursor);
-- Prepare a cursor to insert into the destination table:
destination_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(destination_cursor,'INSERT INTO ' || destination ||' VALUES (:id_bind, :name_bind,
:birthdate_bind)',DBMS_SQL.native);
-- Fetch a row from the source table and insert it into the destination table:
LOOP
IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN
-- get column values of the row
DBMS_SQL.COLUMN_VALUE(source_cursor, 1, id_var);
DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name_var);
DBMS_SQL.COLUMN_VALUE(source_cursor, 3, birthdate_var);
-- Bind the row into the cursor that inserts into the destination table.
DBMS_SQL.BIND_VARIABLE(destination_cursor, ':id_bind', id_var);--指定destination_cursor中：id_bind賦值id_var
DBMS_SQL.BIND_VARIABLE(destination_cursor, ':name_bind', name_var);--指定destination_cursor中：name_bind賦值
name_var
DBMS_SQL.BIND_VARIABLE(destination_cursor, ':birthdate_bind',birthdate_var);--指定destination_cursor中：
birthday_bind賦值birthday_var
ignore := DBMS_SQL.EXECUTE(destination_cursor);
ELSE
-- No more rows to copy:
EXIT;
END IF;
END LOOP;
-- Commit and close all cursors:
COMMIT;
DBMS_SQL.CLOSE_CURSOR(source_cursor);
DBMS_SQL.CLOSE_CURSOR(destination_cursor);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(source_cursor) THEN
DBMS_SQL.CLOSE_CURSOR(source_cursor);
END IF;
IF DBMS_SQL.IS_OPEN(destination_cursor) THEN
DBMS_SQL.CLOSE_CURSOR(destination_cursor);
END IF;
RAISE;
END;
/
=======================
例4：bulk dml 批量執行
(1)普通形式
declare
stmt varchar2(200);
dept_no_array dbms_sql.Number_Table;
c number;
dummy number;
begin
dept_no_array(1) := 10;
dept_no_array(2) := 20;
dept_no_array(3) := 30;
dept_no_array(4) := 40;
dept_no_array(5) := 30;
dept_no_array(6) := 40;
stmt := 'delete from emp where deptno = :dept_array';
c := dbms_sql.open_cursor; --遊標名為c 為什麼要定義成number型別呢？
dbms_sql.parse(c, stmt, dbms_sql.native);
dbms_sql.bind_array(c, ':dept_array', dept_no_array, 1, 4); --這句是本例的難點，
--意思是將輸入dept_no_array中從1到4個值作為變數賦值給：dept_array
--結果應該是將emp中deptno為10、20、30、40的資料刪除
dummy := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
exception
when others then
if dbms_sql.is_open(c) then
dbms_sql.close_cursor(c);
end if;
raise;
end;
/
(2)bulk dml-insert
declare
stmt varchar2(200);
empno_array dbms_sql.Number_Table; --此處使用了number_table
empname_array dbms_sql.Varchar2_Table;
c number;
dummy number;
begin
for i in 0 .. 9 loop
empno_array(i) := 1000 + i;
empname_array(I) := 'emp'||i;
end loop;
stmt := 'insert into emp(empno,ename) values(:num_array, :name_array)';
c := dbms_sql.open_cursor;
dbms_sql.parse(c, stmt, dbms_sql.native);
dbms_sql.bind_array(c, ':num_array', empno_array); --將陣列empno_array作為引數賦值給c中的：num_array
dbms_sql.bind_array(c, ':name_array', empname_array); --將陣列empname_array作為引數賦值給c中的：name_array
dummy := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
exception
when others then
if dbms_sql.is_open(c) then
dbms_sql.close_cursor(c);
end if;
end;
/
(3)bulk dml-update
--和insert語句相比，只是語句不同，處理方式一樣
--以下語句在scott使用者下不能執行，理解意思就可以了
declare
stmt varchar2(200);
emp_no_array dbms_sql.Number_Table;
emp_addr_array dbms_sql.Varchar2_Table;
c number;
dummy number;
begin
for i in 0 .. 9 loop
emp_no_array(i) := 1000 + i;
emp_addr_array(I) := get_new_addr(i);
end loop;
stmt := 'update emp set ename = :name_array where empno = :num_array';
c := dbms_sql.open_cursor;
dbms_sql.parse(c, stmt, dbms_sql.native);
dbms_sql.bind_array(c, ':num_array', empno_array);
dbms_sql.bind_array(c, ':name_array', empname_array);
dummy := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
exception
when others then
if dbms_sql.is_open(c) then
dbms_sql.close_cursor(c);
end if;
raise;
end;
/
=================
例5：定義陣列
declare
c number;
d number;
n_tab dbms_sql.Number_Table;
indx number := -10;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'select n from t order by 1', dbms_sql);
dbms_sql.define_array(c, 1, n_tab, 10, indx);
d := dbms_sql.execute(c);
loop --進行迴圈操作
d := dbms_sql.fetch_rows(c); --將查詢c的一條結果放入d中
dbms_sql.column_value(c, 1, n_tab); --將查詢c的結果一次放入n_tab中
exit when d != 10; --在d=10時退出迴圈
end loop;
dbms_sql.close_cursor(c);
exception when others then
if dbms_sql.is_open(c) then
dbms_sql.close_cursor(c);
end if;
raise;
end;
/
dbms_sql的define_array不太好理解，根據官方文件的語法：
DBMS_SQL.DEFINE_ARRAY (
c IN INTEGER,
position IN INTEGER,
cnt IN INTEGER,
lower_bnd IN INTEGER);
含義為：
c ID number of the cursor to which you want to bind an array.
position Relative position of the column in the array being defined.The first column in a statement has
position 1.
table_variable Local variable that has been declared as
cnt Number of rows that must be fetched.
lower_bnd Results are copied into the collection, starting at this lower bound index.
也就是說，上面的 dbms_sql.define_array(c, 1, n_tab, 10, indx);含義為將遊標c中第一列值從index開始的10個數值放入
到已經定義好的n_tab中。
(2)
declare
c number;
d number;
n_tab dbms_sql.Number_Table;
d_tab1 dbms_sql.Date_Table;
v_tab dbms_sql.Varchar2_Table;
d_tab2 dbms_sql.Date_Table;
indx number := 10;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'select * from multi_tab order by 1', dbms_sql.native);
dbms_sql.define_array(c, 1, n_tab, 5, indx);
dbms_sql.define_array(c, 2, d_tab1, 5, indx);
dbms_sql.define_array(c, 3, v_tab, 5, indx);
dbms_sql.define_array(c, 4, d_tab2, 5, indx);
d := dbms_sql.execute(c);
loop
d := dbms_sql.fetch_rows(c);
dbms_sql.column_value(c, 1, n_tab);
dbms_sql.column_value(c, 2, d_tab1);
dbms_sql.column_value(c, 3, v_tab);
dbms_sql.column_value(c, 4, d_tab2);
exit when d != 5;
end loop;
dbms_sql.close_cursor(c);
exception
when others then
if dbms_sql.is_open(c) then
dbms_sql.close_cursor(c);
end if;
end;
/
==================
例6：描述列
declare
c number;
d number;
col_cnt integer;
f boolean;
rec_tab dbms_sql.desc_tab;
col_num number;
procedure print_rec(rec in dbms_sql.desc_rec) is
begin
dbms_output.new_line;
dbms_output.put_line('col_type = ' || rec.col_type);
dbms_output.put_line('col_maxlen = ' || rec.col_max_len);
dbms_output.put_line('col_name = ' || rec.col_name);
dbms_output.put_line('col_name_len = ' || rec.col_name_len);
dbms_output.put_line('col_schema_name = ' || rec.col_schema_name);
dbms_output.put_line('col_schema_name_len = ' || rec.col_schema_name_len);
dbms_output.put_line('col_precision = ' || rec.col_precision);
dbms_output.put_line('col_scale = ' || rec.col_scale);
dbms_output.put('col_null_ok = ');
if (rec.col_null_ok) then
dbms_output.put_line('true');
else
dbms_output.put_line('false');
end if;
end;
begin
c := dbms_sql.open_cursor;
--如果希望檢視兩個表，直接在bonus後面加表名就可以了
dbms_sql.parse(c, 'select * from scott.bonus', dbms_sql.native);
d := dbms_sql.execute(c);
dbms_sql.describe_columns(c, col_cnt, rec_tab);--括號裡分別為遊標名稱、select查詢中的列數、待查詢列的描述
/*
* Following loop could simply be for j in 1..col_cnt loop.
* Here we are simply illustrating some of the PL/SQL table
* features.
*/
col_num := rec_tab.first;
if (col_num is not null) then
loop
print_rec(rec_tab(col_num));
col_num := rec_tab.next(col_num);
exit when(col_num is null);
end loop;
end if;
dbms_sql.close_cursor(c);
end;
/
結果為：
col_type = 1
col_maxlen = 10
col_name = ENAME
col_name_len = 5
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = 0
col_null_ok = true
col_type = 1
col_maxlen = 9
col_name = JOB
col_name_len = 3
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = 0
col_null_ok = true
col_type = 2
col_maxlen = 22
col_name = SAL
col_name_len = 3
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = -127
col_null_ok = true
col_type = 2
col_maxlen = 22
col_name = COMM
col_name_len = 4
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = -127
col_null_ok = true
============================
例7：returning子句
(1)單行插入
create or replace procedure single_Row_insert
(c1 number, c2 number, r out number) is
c number;
n number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'insert into tab values (:bnd1, :bnd2) ' ||
'returning c1*c2 into :bnd3', 2);
dbms_sql.bind_variable(c, 'bnd1', c1);
dbms_sql.bind_variable(c, 'bnd2', c2);
dbms_sql.bind_variable(c, 'bnd3', r);
n := dbms_sql.execute(c);
dbms_sql.variable_value(c, 'bnd3', r); -- get value of outbind variable
dbms_Sql.close_Cursor(c);
end;
/
(2)單行更新
create or replace procedure single_Row_update
(c1 number, c2 number, r out number) is
c number;
n number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'update tab set c1 = :bnd1, c2 = :bnd2 ' ||
'where rownum < 2' ||
'returning c1*c2 into :bnd3', 2);
dbms_sql.bind_variable(c, 'bnd1', c1);
dbms_sql.bind_variable(c, 'bnd2', c2);
dbms_sql.bind_variable(c, 'bnd3', r);
n := dbms_sql.execute(c);
dbms_sql.variable_value(c, 'bnd3', r);-- get value of outbind variable
dbms_Sql.close_Cursor(c);
end;
/
(3)單行刪除
create or replace procedure single_Row_Delete
(c1 number, c2 number, r out number) is
c number;
n number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'delete from tab ' ||
'where rownum < 2 ' ||
'returning c1*c2 into :bnd3', 2);
dbms_sql.bind_variable(c, 'bnd1', c1);
dbms_sql.bind_variable(c, 'bnd2', c2);
dbms_sql.bind_variable(c, 'bnd3', r);
n := dbms_sql.execute(c);
dbms_sql.variable_value(c, 'bnd3', r);-- get value of outbind variable
dbms_Sql.close_Cursor(c);
end;
/
(4)多行插入
create or replace procedure multi_Row_insert
(c1 dbms_sql.number_table, c2 dbms_sql.number_table,
r out dbms_sql.number_table) is
c number;
n number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'insert into tab values (:bnd1, :bnd2) ' ||
'returning c1*c2 into :bnd3', 2);
dbms_sql.bind_array(c, 'bnd1', c1);
dbms_sql.bind_array(c, 'bnd2', c2);
dbms_sql.bind_array(c, 'bnd3', r);
n := dbms_sql.execute(c);
dbms_sql.variable_value(c, 'bnd3', r);-- get value of outbind variable
dbms_Sql.close_Cursor(c);
end;
/
(5)多行更新
create or replace procedure multi_Row_update
(c1 number, c2 number, r out dbms_Sql.number_table) is
c number;
n number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'update tab set c1 = :bnd1 where c2 = :bnd2 ' ||
'returning c1*c2 into :bnd3', 2);
dbms_sql.bind_variable(c, 'bnd1', c1);
dbms_sql.bind_variable(c, 'bnd2', c2);
dbms_sql.bind_array(c, 'bnd3', r);
n := dbms_sql.execute(c);
dbms_sql.variable_value(c, 'bnd3', r);-- get value of outbind variable
dbms_Sql.close_Cursor(c);
end;
/
(6)多行刪除
create or replace procedure multi_row_delete
(c1 dbms_Sql.number_table,
r out dbms_sql.number_table) is
c number;
n number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'delete from tab where c1 = :bnd1' ||
'returning c1*c2 into :bnd2', 2);
dbms_sql.bind_array(c, 'bnd1', c1);
dbms_sql.bind_array(c, 'bnd2', r);
n := dbms_sql.execute(c);
dbms_sql.variable_value(c, 'bnd2', r);-- get value of outbind variable
dbms_Sql.close_Cursor(c);
end;
/
(7)多行繫結處理
create or replace foo (n number, square out number) is
begin square := n * n; end;/
create or replace procedure bulk_plsql
(n dbms_sql.number_Table, square out dbms_sql.number_table) is
c number;
r number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'begin foo(:bnd1, :bnd2); end;', 2);
dbms_sql.bind_array(c, 'bnd1', n);
dbms_Sql.bind_Array(c, 'bnd2', square);
r := dbms_sql.execute(c);
dbms_Sql.variable_Value(c, 'bnd2', square);
end;
/
例4和例7不太理解，待深入
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-755441/，如需轉載，請註明出處，否則將追究法律責任。