PL/SQL之DBMS_SQL程序包使用（1）（学习笔记）

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;