dbms_sql程序包
dbms_sql程序包是系統提供給我們的另一種使用動態SQL的方法：
使用DBMS_SQL包實現動態的SQL的步驟如下：
1.將要執行的SQL語句或者一個語句放到一個字符串變量中
2.使用DBMS_SQL包的parse過程來分析該字符串
3.使用DBMS_SQL包的bind_varable過程綁定變量
4.使用DMBS_SQL包的execute函數和執行語句：
示例1：
--使用DBMS_SQL包執行DDL語句
--需求：使用DBMS_SQL包根據用戶輸入的表名，字段名及字段類型建表
DECLARE
TABLE_NAME VARCHAR2(20); --表名
FIELD1 VARCHAR2(20); --字段名
DATATYPE1 VARCHAR2(20); --字段類型
FIELD2 VARCHAR2(20); --字段名
DATATYPE2 VARCHAR2(20); --字段類型
V_CURSOR NUMBER; --定義光標
V_STRING VARCHAR2(200); --定義字符串變量
V_ROW NUMBER; --行數
BEGIN
TABLE_NAME := 't2';
FIELD1 := 'id';
DATATYPE1 := 'NUMBER';
FIELD2 := 'name';
DATATYPE2 := 'VARCHAR2(20)';
V_CURSOR := DBMS_SQL.OPEN_CURSOR; --為處理打開光標
V_STRING := 'create table ' || TABLE_NAME || '(' || FIELD1 || ' ' ||
DATATYPE1 || ',' || FIELD2 || ' ' || DATATYPE2 || ')';
DBMS_SQL.PARSE(V_CURSOR, V_STRING, DBMS_SQL.NATIVE); --分析語句
V_ROW := DBMS_SQL.EXECUTE(V_CURSOR); --執行語句
DBMS_SQL.CLOSE_CURSOR(V_CURSOR); --關閉光標
DBMS_OUTPUT.PUT_LINE(V_ROW);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(V_CURSOR); --關閉光標
END;
SELECT * FROM t2;
示例2：
--使用DBMS_SQL包執行DML語句insert
DECLARE
ID NUMBER:=&ID;
NAME VARCHAR2(20):='&name';
v_cursor NUMBER; --定義光標
v_string VARCHAR2(200); --定義字符串變量
v_row NUMBER; --行數變量
BEGIN
v_cursor:=dbms_sql.open_cursor; --打開光標
v_string:='insert into t2 values(:id,:name)';
dbms_sql.parse(v_cursor,v_string,dbms_sql.native); --分析語句
dbms_sql.bind_variable(v_cursor,'id',ID); --綁定變量
dbms_sql.bind_variable(v_cursor,'name',NAME); --綁定變量
v_row:=dbms_sql.execute(v_cursor); --執行動態SQL
COMMIT;
dbms_sql.close_cursor(v_cursor); --關閉光標
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(v_cursor); --關閉光標
END;
示例3：
-使用DBMS_SQL包執行DML語句
--需求：使用DBMS_SQL包將表中t2的id=1的名稱改為Marry
DECLARE
ID NUMBER := &ID;
NAME VARCHAR2(20) := '&NAME';
V_CURSOR NUMBER; --定義光標
V_STRING VARCHAR2(200); --定義字符串變量
V_ROW NUMBER; --行數變量
BEGIN
V_CURSOR := DBMS_SQL.OPEN_CURSOR; --打開光標
V_STRING := 'update t2 set name=:name where id=:id';
DBMS_SQL.PARSE(V_CURSOR, V_STRING, DBMS_SQL.NATIVE); --分析語句
DBMS_SQL.BIND_VARIABLE(V_CURSOR, 'name', NAME); --綁定變量
DBMS_SQL.BIND_VARIABLE(V_CURSOR, 'id', ID); --綁定變量
V_ROW := DBMS_SQL.EXECUTE(V_CURSOR); --執行動態SQL
COMMIT;
DBMS_SQL.CLOSE_CURSOR(V_CURSOR); --關閉光標
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(V_CURSOR); --關閉光標
RAISE;
END;
示例4：
--使用DBMS_SQL包執行DML語句delete
DECLARE
ID NUMBER := &ID; --定義id
V_CURSOR NUMBER; --定義光標
V_ROW NUMBER; --定義行數
V_STRING VARCHAR2(200); --定義字符串變量
BEGIN
V_CURSOR:=DBMS_SQL.OPEN_CURSOR; --打開光標
V_STRING := 'delete from t2 where id=:id';
DBMS_SQL.PARSE(V_CURSOR, V_STRING, DBMS_SQL.NATIVE); --分析語句
DBMS_SQL.BIND_VARIABLE(V_CURSOR, 'id', ID); --綁定字段ID
V_ROW := DBMS_SQL.EXECUTE(V_CURSOR); --執行動態SQL
COMMIT;
DBMS_SQL.CLOSE_CURSOR(V_CURSOR); --關閉光標
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(V_CURSOR); --關閉光標
RAISE;
END;
查詢
--示例五:
使用DBMS_SQL包執行DML語句select
DECLARE
V_ID emp.deptno%TYPE:= &ID; --定義變量
V_STRING VARCHAR2(200); --定義字符串變量
V_EMPNO NUMBER;
V_NAME VARCHAR2(20);
V_CURSOR NUMBER; --定義光標
V_ROW NUMBER; --定義行
BEGIN
v_cursor:=dbms_sql.open_cursor; --打開光標
V_STRING := 'select empno,ename from emp where deptno=:deptno';
DBMS_SQL.PARSE(V_CURSOR, V_STRING, DBMS_SQL.NATIVE); --分析語句
DBMS_SQL.BIND_VARIABLE(V_CURSOR, 'deptno', V_ID); --綁定字段
DBMS_SQL.DEFINE_COLUMN(V_CURSOR,1,V_EMPNO);
DBMS_SQL.DEFINE_COLUMN(V_CURSOR,2,V_NAME,20); --如果是有長度需要指定找長度
V_ROW := DBMS_SQL.EXECUTE(V_CURSOR); --執行
LOOP
EXIT WHEN DBMS_SQL.FETCH_ROWS(V_CURSOR) <= 0; --解析游標，
DBMS_SQL.COLUMN_VALUE(V_CURSOR,1,V_EMPNO); --將當前行的數據寫入上面對應的列中。
DBMS_SQL.COLUMN_VALUE(V_CURSOR,2,V_NAME);
DBMS_OUTPUT.PUT_LINE('no:' || V_EMPNO || ' enmae:' || V_NAME); --輸出內容
END LOOP;
DBMS_SQL.CLOSE_CURSOR(V_CURSOR); --關閉游標
END;
