--使用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;

--使用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;

-使用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;

--使用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;