整理：oracle pl/sql 入门+ 数组使用+游标+动态SQL

转自于：http://dolphin-ygj.iteye.com/blog/4402041.1 PL/SQL简介

  PL/SQL是ORACLE的过程化语言，包括一整套的数据类型、条件结构、循环结构和异常处理结构，PL/SQL可以执行SQL语句，SQL语句中也可以使用PL/SQL函数。

1.2 创建PL/SQL程序块

DECLARE
…
BEGIN
…
EXCEPTION
END;
1.3 PL/SQL数据类型

名称
类型
说明

NUMBER
数字型
能存放整数值和实数值，并且可以定义精度和取值范围

BINARY_INTEGER
数字型
可存储带符号整数，为整数计算优化性能

DEC
数字型
NUMBER的子类型，小数

DOUBLE PRECISION
数字型
NUMBER的子类型，高精度实数

INTEGER
数字型
NUMBER的子类型，整数

INT
数字型
NUMBER的子类型，整数

NUMERIC
数字型
NUMBER的子类型，与NUMBER等价

REAL
数字型
NUMBER的子类型，与NUMBER等价

SMALLINT
数字型
NUMBER的子类型，取值范围比INTEGER小

VARCHAR2
字符型
存放可变长字符串，有最大长度

CHAR
字符型
定长字符串

LONG
字符型
变长字符串，最大长度可达32，767

DATE
日期型
以数据库相同的格式存放日期值

BOOLEAN
布尔型
TRUE OR FALSE

ROWID
ROWID
存放数据库的行号


例子：
DECLARE
  ORDER_NO NUMBER(3);
  CUST_NAME VARCHAR2(20);
  ORDER_DATE DATE;
  EMP_NO INTEGER:=25;
  PI CONSTANT NUMBER:=3.1416;
BEGIN
NULL;
END;
1.4 处理PL/SQL的异常

1.4.1 PL/SQL的异常

例如：
DECLARE
  X NUMBER;
BEGIN
  X:= ’yyyy’;--Error Here
EXCEPTION   WHEN VALUE_ERROR THEN
  DBMS_OUTPUT.PUT_LINE(’EXCEPTION HANDED’);
END;

实现技术：
EXCEPTION WHEN first_exception THEN
…
WHEN second_exception THEN
…
WHEN OTHERS THEN 
/*THERS异常处理器必须排在最后，它处理所有没有明确列出的异常。*/
…
END;

1.4.2 预定义异常

异常名称
ORACLE错误
说明

CURSOR_ALREADY_OPEN
ORA-6511
试图打开一个已打开的光标

DUP_VAL_ON_INDEX
ORA-0001
试图破坏一个唯一性限制

INVALID_CURSOR
ORA-1001
试图使用一个无效的光标

INVALID_NUMBER
ORA-1722
试图对非数字值进行数字操作

LOGIN_DENIED
ORA-1017
无效的用户名或者口令

NO_DATA_FOUND
ORA-1403
查询未找到数据

NOT_LOGGED_ON
ORA-1012
还未连接就试图数据库操作

PROGRAM_ERROR
ORA-6501
内部错误

ROWTYPE_MISMATCH
ORA-6504
主变量和光标的类型不兼容

STORAGE_ERROR
ORA-6500
内部错误

TIMEOUT_ON_RESOURCE
ORA-0051
发生超时

TOO_MANY_ROWS
ORA-1422
SELECT INTD命令返回的多行

TRANSACTION_BACKED_OUT
ORA-006
由于死锁提交被退回

VALUE_ERROR
ORA-6502
转换或者裁剪错误

ZERO_DIVIDE
ORA-1476
试图被零除


　

1.4.3 自定义异常处理

DECLARE
  BAD_ROWID EXCEPTION;
  X ROWID;
PRAGMA EXCEPTION_INIT(BAD_ROWID，-01445);
BEGIN
  SELECT ROWID INTO X FROM TAB
  WHERE ROWNUM=1;
EXCEPTION WHEN BAD_ROWID THEN
  DBMS_OUTPUT.PUT_LINE(’CANNOT QUERY ROWID FROM THIS VIEW’);
END;

  注意：-01445 因为PRAGMA EXCEPTION_INIT命令把这个变量(-01455)连接到
这个ORACLE错误，该语句的语法如下：
  PRAGMA EXCEPTION_INIT(exception_name， error_number);
其中error_number是负数，因为错误号被认为负数，当定义错误时记住使用负号

1.4.4 自定义异常

  异常不一定必须是oracle返回的系统错误，用户可以在自己的应用程序中创
建可触发及可处理的自定义异常
DECLARE
  SALARY_CODE VARCHAR2(1);
  INVALID_SALARY_CODE EXCEPTION;
BEGIN
  SALARY_CODE:=’X’;
  IF SALARY_CODE NOT IN(’A’， ’B’， ’C’) THEN
  RAISE INVALID_SALARY_CODE;
  END IF;
EXCEPTION WHEN INVALID_SALARY_CODE THEN
  DBMS_OUTPUT.PUT_LINE(’INVALID SALARY CODE’);
END;

1.5 在PL/SQL中单条记录的查询

  在PL/SQL内，有时在没有定义显式光标的情况下需要查询单条记录，并把记录的数据赋给变量。
DECLARE
  ln_dno NUMBER;
  lvs_dname VARCHAR2(40);
BEGIN
  SELECT DEPT_NO，DEPT_NAME
    INTO ln_dno，lvs_dname
  FROM dept
  WHERE DEPT_NO=1;
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(ln_dno)||’.’||lvs_dname);
EXCEPTION WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE(’NO DATA_FOUND’);
  WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE(’TOO_MANY_ROWS’);
END;

1.6 用光标查询多条记录

    光标（CURSOR）是指向一个称为上下文相关区的区域的指针，这个区域在服务器的处理过程全局区（PGA）内，当服务器上执行了一个查询后，查询返回的记录集存放在上下文相关区，通过光标上的操作可以把这些记录检索到客户端的应用程序。

1.6.1 使用光标的基本方法

DECLARE
  CURSOR C1 IS SELECT VIEW_NAME FROM ALL_VIEWS
    WHERE ROWNUM<=10
    ORDER BY VIEW_NAME;
  VNAME VARCHAR2(40);
BEGIN
  OPEN C1;
  FETCH C1 INTO VNAME;
  WHILE C1%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT)||’ ’||VNAME);
    FETCH C1 INTO VNAME;
  END LOOP;
END;

属性
含量

%FOUND
布尔型属性，当最近一次该记录时成功返回，则值为TRUE

%NOTFOUND
布尔型属性，它的值总与%FOUND属性的值相反

%ISOPEN
布尔型属性，当光标是打开时返回TRUE

%ROWCOUNT
数字型属性，返回已从光标中读取的记录数


　

1.6.2 使用光标FOR循环

DECLARE
  CURSOR C1 IS
  SELECT VIEW_NAME
    FROM ALL_VIEWS
    WHERE ROWNUM<=10
    ORDER BY VIEW_NAME;
BEGIN
  FOR I IN C1 LOOP
    DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
  END LOOP;
END LOOP;
EXCEPTION WHEN OTHERS THEN
  NULL;
END;

1.6.3 带参数的光标

DECLARE
  CURSOR C1(VIEW_PATTERN VARCHAR2) IS
    SELECT VIEW_NAME
    FROM ALL_VIEWS
    WHERE VIEW_NAME LIKE VIEW_PATTERN||’%’ AND
    ROWNUM<=10
    ORDER BY VIEW_NAME;
  VNAME VARCHAR2(40);
BEGIN
  FOR I IN C1(’USER_AR’) LOOP
    DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(??);
  FOR I IN C1(’USER’) LOOP
    DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
  END LOOP;
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(’AAA’);
END;

1.7 创建代表数据库记录和列的变量

变量名 基表名.列名%TYPE
DECLARE
  D_NO DEPT.DEPT_NO%TYPE;
  D_NAME DEPT.DEPT_NAME%TYPE;
BEGIN
  SELECT DEPT_NO，DEPT_NAME INTO D_NO，D_NAME
  FROM DEPT;
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(D_NO));
EXCEPTION WHEN NO_DATA_FOUND THEN
  NULL;
END;

变量名 基表名%ROWTYPE
DECLARE
  D VEQU12%ROWTYPE;
BEGIN
  SELECT ASSET12ID，ASSET12NAME
    INTO D.ASSET12ID， D.ASSET12NAME
    FROM VEQU12;
  DBMS_OUTPUT.PUT_LINE(D.ASSET12ID);
EXCEPTION
WHEN NO_DATA_FOUND THEN
  NULL;
WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE(’TOO_MANY_ROWS’);
END;
说明：
  当用户要创建一个变量来表示一个基表列或者要创建多个变量来代表一整条记录时，可以实际使用%TYPE属性和%ROWTYPE属性，使用%TYPE属性和%ROWTYPE属性可以保证当基表的结构或者其中某列的数据类型改变了时，用户的PL/SQL代码仍可正常工作。

1.8 怎样用PL/SQL表实现数组功能

  PL/SQL表与其他过程化语言(如C语言)的一维数组类似。实现PL/SQL表需要创建一个数据类型并另外进行变量说明。
  Type <类型名> Is
  Table Of <数据类型>
  Index by Binary_Integer;
以下为一个例子：
Declare
  Type Array_type is
    Table Of Number
    Index by Binary_Integer;
  My_Array Array_type;
Begin
  For I In 1..10 Loop
    My_Array(I) := I*2;
  End Loop;
  For I In 1..10 Loop
    Dbms_Output.Put_line(To_char(My_Array(I)));
  End Loop;
End;
******************
　　因为在PL/SQL 中并没有数组. 这是偶查资料找的范例和自己写的范例来解释如何在PL/SQL 中使用数组. 也许很多人已知道， 不过就是让不知道的朋友们了解一下吧。

---------------------- 单维数组 ------------------------
DECLARE
TYPE emp_ssn_array IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;

best_employees emp_ssn_array;
worst_employees emp_ssn_array;

BEGIN
best_employees(1) := ’123456’;
best_employees(2) := ’888888’;

worst_employees(1) := ’222222’;
worst_employees(2) := ’666666’;

FOR i IN 1..best_employees.count LOOP
DBMS_OUTPUT.PUT_LINE(’i=’|| i || ’， best_employees= ’ ||best_employees(i)
|| ’， worst_employees= ’ ||worst_employees(i));
END LOOP;

END;


---------------------- 多维数组 ------------------------ 
DECLARE

TYPE emp_type IS RECORD
( emp_id employee_table.emp_id%TYPE，
emp_name employee_table.emp_name%TYPE，
emp_gender employee_table.emp_gender%TYPE );

TYPE emp_type_array IS TABLE OF
emp_type INDEX BY BINARY_INTEGER;

emp_rec_array emp_type_array;
emp_rec emp_type;

BEGIN
emp_rec.emp_id := 300000000;
emp_rec.emp_name := ’Barbara’;
emp_rec.emp_gender := ’Female’;

emp_rec_array(1) := emp_rec;

emp_rec.emp_id := 300000008;
emp_rec.emp_name := ’Rick’;
emp_rec.emp_gender := ’Male’;

emp_rec_array(2) := emp_rec;

FOR i IN 1..emp_rec_array.count LOOP
DBMS_OUTPUT.PUT_LINE(’i=’||i
||’， emp_id =’||emp_rec_array(i).emp_id
||’， emp_name =’||emp_rec_array(i).emp_name
||’， emp_gender = ’||emp_rec_array(i).emp_gender);
END LOOP; 

END;
-------------- Result --------------
i=1， emp_id =300000000， emp_name =Barbara， emp_gender = Female
i=2， emp_id =300000008， emp_name =Rick， emp_gender = Male


注：在PL/SQL 中是没有数组(Array) 概念的. 但是如果程序员想用Array 的话， 就得变通一下， 用TYPE 和Table of Record 来代替多维数组， 一样挺好用的。
emp_type 就好象一个table 中的一条record 一样， 里面有id， name，gender等。emp_type_array 象个table， 里面含有一条条这样的record (emp_type)，就象多维数组一样。
************************
游标是什么:
　　游标字面理解就是游动的光标。
　　用数据库语言来描述:游标是映射在结果集中一行数据上的位置实体，有了游标用户就可以访问结果集中的任意一行数据了，将游标放置到某行后，即可对该行数据进行操作，例如提取当前行的数据等等。
　　游标的分类:
　　显式游标和隐式游标
　　显示游标的使用需要4步:
　　1.声明游标
　　CURSOR mycur(vartype number) is
　　select emp_no，emp_zc from cus_emp_basic
　　where com_no = vartype;
　　2.打开游标
　　open mycur(000627) 注:000627:参数
　　3.读取数据
　　fetch mycur into varno，varprice;
　　4.关闭游标
　　close mycur;
　　游标的属性：
　　oracle 游标有4个属性: %ISOPEN ， %FOUND ， %NOTFOUND， %ROWCOUNT
　　%ISOPEN 判断游标是否被打开，如果打开%ISOPEN 等于true，否则等于false
　　%FOUND %NOTFOUND 判断游标所在的行是否有效，如果有效，则%FOUNDD等于true，否则等于false
　　%ROWCOUNT 返回当前位置为止游标读取的记录行数。
　　示例:
　　set serveroutput on;
　　declare
　　varno varchar2(20);
　　varprice varchar2(20);
　　CURSOR mycur(vartype number) is
　　select emp_no，emp_zc from cus_emp_basic
　　where com_no = vartype;
　　begin
　　if mycur%isopen = false then
　　open mycur(000627);
　　end if;
　　fetch mycur into varno，varprice;
　　while mycur%found
　　loop
　　dbms_output.put_line(varno||%27，%27||varprice);
　　if mycur%rowcount=2 then
　　exit;
　　end if;
　　fetch mycur into varno，varprice;
　　end loop;
　　close mycur;
　　end;
　　pl/sql 记录的结构和c语言中的结构体类似，是由一组数据项构成的逻辑单元。
　　pl/sql 记录并不保存再数据库中，它与变量一样，保存再内存空间中，在使用记录时候，要首先定义记录结构，然后声明记录变量。可以把pl/sql记录看作是一个用户自定义的数据类型。
　　set serveroutput on;
　　declare
　　type person is record
　　(
　　empno cus_emp_basic.emp_no%type，
　　empzc cus_emp_basic.emp_zc%type);
　　person1 person;
　　cursor mycur(vartype number)is
　　select emp_no，emp_zc from cus_emp_basic
　　where com_no=vartype;
　　begin
　　if mycur%isopen = false then
　　open mycur(000627);
　　end if;
　　loop
　　fetch mycur into person1;
　　exit when mycur%notfound;
　　dbms_output.put_line(%27雇员编号:%27||person1.empno||%27，地址:%27||person1.empzc);
　　end loop;
　　close mycur;
　　end;
　　典型游标for 循环
　　游标for循环示显示游标的一种快捷使用方式，它使用for循环依次读取结果集中的行数据，当form循环开始时，游标自动打开(不需要open)，每循环一次系统自动读取游标当前行的数据(不需要fetch)，当退出for循环时，游标被自动关闭(不需要使用close)使用游标for循环的时候不能使用open语句，fetch语句和close语句，否则会产生错误。
　　set serveroutput on;
　　declare
　　cursor mycur(vartype number)is
　　select emp_no，emp_zc from cus_emp_basic
　　where com_no=vartype;
　　begin
　　for person in mycur(000627) loop
　　dbms_output.put_line(%27雇员编号:%27||person.emp_no||%27，地址:%27||person.emp_zc);
　　end loop;
　　end;

**********************

一、 什么是光标
Oracle 使用两种光标：显式光标和隐式光标。不管语句返回多少条纪录， PL/SQL 为使用的每一条 UPDATE 、 DELETE 和 INSERT 等 SQL 命令隐式的声明一个光标。（要管理 SQL 语句的处理，必须隐式的给它定义一个光标。）用户声明并使用显示光标处理 SELECT 语句返回的多条记录。显示的定义光标一种结构，它使用户能够为特定的语句指定内存区域，以便以后使用。
二、 光标的作用
当 PL/SQL 光标查询返回多行数据时，这些记录组被称为活动集。 Oracle 将这种活动集存储在您创建的显示定义的已命名的光标中。Oracle 光标是一种用于轻松的处理多行数据的机制，没有光标， Oracle 开发人员必须单独地、显式地取回并管理光标查询选择的每一条记录。
光标的另一项功能事，它包含一个跟踪当前访问的记录的指针，这使您的程序能够一次处理多条记录。
三、 使用显示光标的基本方法
步骤如下：
1 、声明光标
声明光标的语法如下：
DECLARE cursor_name
Is
SELECT statement
其中， cursor_name 是您给光标指定的名称； SELECT statement 是给光标活动集返回记录的查询。
声明光标完成了下面两个目的：
给光标命名；
将一个查询与光标关联起来。
值得注意的是，必须在 PL/SQL 块的声明部分声明光标；给光标指定的名称是一个未声明的标识符，而不是一个 PL/SQL 变量，不能给光标名称赋值，也不能将它用在表达式中。 PL/SQL 块使用这个名称来引用光标查询。
例： DECLARE
CURSOR c1
Is
SELECT VIEW_NAME FROM ALL_VIEWS
WHERE ROWNUM<=10 ；
另外还可以在光标定义语句中声明光标的参数，例：
CURSOR c1 （ view _nbr number ）
Is
SELECT VIEW_NAME FROM ALL_VIEWS
WHERE ROWNUM<= view _nbr ；
光标参数只对相应的光标是可见的，不能在光标范围之外引用该光标的参数。如果试图这样做， Oracle 将返回一个错误，指出该变量没有定义。
2 、打开光标
打开光标的语法如下：
OPEN cursor_name ；
其中 cursor_name 是您以前定义的光标名称。
打开光标将激活查询并识别活动集，可是在执行光标取回命令之前，并没有真正取回记录。 OPEN 命令还初始化了光标指针，使其指向活动集的第一条记录。光标被打开后，直到关闭之前，取回到活动集的所有数据都是静态的，换句话说，光标忽略所有在光标打开之后，对数据执行的 SQL DML 命令（ INSERT 、 UPDATE 、 DELETE 和 SELECT ）。因此只有在需要时才打开它，要刷新活动集，只需关闭并重新打开光标即可。
3 、从光标中取回数据
FETCH 命令以每次一条记录的方式取回活动集中的记录。通常将 FETCH 命令和某种迭代处理结合起来使用，在迭代处理中， FETCH 命令每执行一次，光标前进到活动集的下一条记录。
FETCH 命令的语法：
FETCH cursor_name INTO record_list ；
其中， cursor_name 是前面定义的光标的名称； record_list 是变量列表，它接受活动集中的列。 FETCH 命令将活动集的结果放置到这些变量中。
执行 FETCH 命令后，活动集中的结果被取回到 PL/SQL 变量中，以便在 PL/SQL 块中使用。每取回一条记录，光标的指针就移向活动集的下一条记录。
例：
FETCH C1 INTO VNAME;
WHILE C1%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT)||’ ’||VNAME);
END LOOP;
其中，使用属性 ’%FOUND’ 使得当 FETCH 到达活动集的结尾时，不会引发异常。其它属性及含义见下表：
属性 含量
%FOUND 布尔型属性，当最近一次该记录时成功返回，则值为 TRUE
%NOTFOUND 布尔型属性，它的值总与 %FOUND 属性的值相反
%ISOPEN 布尔型属性，当光标是打开时返回 TRUE
%ROWCOUNT 数字型属性，返回已从光标中读取的记录数
属性 含量
%FOUND 布尔型属性，当最近一次该记录时成功返回，则值为 TRUE
%NOTFOUND 布尔型属性，它的值总与 %FOUND 属性的值相反
%ISOPEN 布尔型属性，当光标是打开时返回 TRUE
%ROWCOUNT 数字型属性，返回已从光标中读取的记录数
4 、关闭光标
CLOSE 语句关闭以前打开的光标，使得活动集不确定。当用户的程序或会话结束时， Oracle 隐式关闭光标。光标被关闭后，就不能对它执行任何操作了 ， 否则将引发异常。
CLOSE 语句的语法是：
CLOSE cursor_name ；
其中， cursor_name 是以前打开的光标的名称。
完整的程序代码如下：
DECLARE
CURSOR C1 IS SELECT VIEW_NAME FROM ALL_VIEWS
WHERE ROWNUM<=10
ORDER BY VIEW_NAME;
VNAME VARCHAR2(40);
BEGIN
OPEN C1;
FETCH C1 INTO VNAME;
WHILE C1%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT)||’’||VNAME);
END LOOP;
END;
…… CLOSE C1;
四、 小结
光标是一种结构 ， 能够以一次一条记录的方式处理多行查询的结果 . 为每条 DML 语句创建隐式光标 ， 而显式光标是由用户创建的 ， 以便处理返回多条记录的查询。而且 ， 通过消除反复地分析代码 ， 光标提高了代码的处理速度。
**************************
一般的PL/SQL程序设计中，在DML和事务控制的语句中可以直接使用SQL，但是DDL语句及系统控制语句却不能在PL/SQL中直接使用，要想实现在PL/SQL中使用DDL语句及系统控制语句，可以通过使用动态SQL来实现。

　　首先我们应该了解什么是动态SQL，在Oracle数据库开发PL/SQL块中我们使用的SQL分为：静态SQL语句和动态SQL语句。所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。

　　Oracle中动态SQL可以通过本地动态SQL来执行，也可以通过DBMS_SQL包来执行。下面就这两种情况分别进行说明：

　　一、本地动态SQL

　　本地动态SQL是使用EXECUTE IMMEDIATE语句来实现的。

　　1、本地动态SQL执行DDL语句：

　　需求：根据用户输入的表名及字段名等参数动态建表。

create or replace procedure proc_test
(
    table_name in varchar2，     --表名
    field1 in varchar2，          --字段名
    datatype1 in varchar2，      --字段类型
    field2 in varchar2，          --字段名
    datatype2 in varchar2        --字段类型
) as 
    str_sql varchar2(500);
begin 
    str_sql:=’create table ’||table_name||’(’||field1||’ ’||datatype1||’，’||field2||’ ’||datatype2||’)’;
    execute immediate str_sql;   --动态执行DDL语句
    exception 
        when others then 
            null;
end ;

　　以上是编译通过的存储过程代码。下面执行存储过程动态建表。

 
SQL> execute proc_test(’dinya_test’，’id’，’number(8) not null’，’name’，’varchar2(100)’);

PL/SQL procedure successfully completed

SQL> desc dinya_test;
Name Type          Nullable Default Comments 
---- ------------- -------- ------- -------- 
ID   NUMBER(8)

NAME VARCHAR2(100) Y

SQL>

　　到这里，就实现了我们的需求，使用本地动态SQL根据用户输入的表名及字段名、字段类型等参数来实现动态执行DDL语句。

　　2、本地动态SQL执行DML语句。

　　需求：将用户输入的值插入到上例中建好的dinya_test表中。

create or replace procedure proc_insert
(
    id in number，                                 --输入序号
    name in varchar2                             --输入姓名
) as 
    str_sql varchar2(500);
begin 
    str_sql:=’insert into dinya_test values(:1，:2)’;
    execute immediate str_sql using id，name; --动态执行插入操作
    exception 
        when others then 
            null;
end ;

　　执行存储过程，插入数据到测试表中。

 
SQL> execute proc_insert(1，’dinya’);
PL/SQL procedure successfully completed
SQL> select * from dinya_test;
        ID      NAME
        1      dinya

　　在上例中，本地动态SQL执行DML语句时使用了using子句，按顺序将输入的值绑定到变量，如果需要输出参数，可以在执行动态SQL的时候，使用RETURNING INTO 子句，如：

declare
    p_id number:=1;
    v_count number;
begin 
    v_string:=’select count(*) from table_name a where a.id=:id’;
    execute immediate v_string into v_count using p_id;  
end ;

　　更多的关于动态SQL中关于返回值及为输出输入绑定变量执行参数模式的问题，请读者自行做测试。

　　二、使用DBMS_SQL包

　　使用DBMS_SQL包实现动态SQL的步骤如下：A、先将要执行的SQL语句或一个语句块放到一个字符串变量中。B、使用DBMS_SQL包的parse过程来分析该字符串。C、使用DBMS_SQL包的bind_variable过程来绑定变量。D、使用DBMS_SQL包的execute函数来执行语句。

　　1、使用DBMS_SQL包执行DDL语句

　　需求：使用DBMS_SQL包根据用户输入的表名、字段名及字段类型建表。

create or replace procedure proc_dbms_sql
(
    table_name in varchar2，       --表名
    field_name1 in varchar2，      --字段名
    datatype1 in varchar2，        --字段类型
    field_name2 in varchar2，      --字段名
    datatype2 in varchar2         --字段类型
)as
    v_cursor number;              --定义光标
    v_string varchar2(200);      --定义字符串变量
    v_row number;                  --行数
begin
    v_cursor:=dbms_sql.open_cursor;     --为处理打开光标
    v_string:=’create table ’||table_name||’(’||field_name1||’ ’||datatype1||’，’||field_name2||’ ’||datatype2||’)’;
    dbms_sql.parse(v_cursor，v_string，dbms_sql.native);    --分析语句
    v_row:=dbms_sql.execute(v_cursor);   --执行语句
    dbms_sql.close_cursor(v_cursor);     --关闭光标
    exception
        when others then
            dbms_sql.close_cursor(v_cursor);  --关闭光标
            raise;
end;

　　以上过程编译通过后，执行过程创建表结构：

SQL> execute proc_dbms_sql(’dinya_test2’，’id’，’number(8) not null’，’name’，’varchar2(100)’);

PL/SQL procedure successfully completed

SQL> desc dinya_test2;
Name Type          Nullable Default Comments 
---- ------------- -------- ------- -------- 
ID   NUMBER(8)                               
NAME VARCHAR2(100) Y                         

SQL>

　　2、使用DBMS_SQL包执行DML语句

　　需求：使用DBMS_SQL包根据用户输入的值更新表中相对应的记录。

　　查看表中已有记录：

SQL> select * from dinya_test2;
       ID NAME
        1 Oracle
        2 CSDN
        3 ERP
SQL>

　　建存储过程，并编译通过：

create or replace procedure proc_dbms_sql_update
(
    id number，
    name varchar2
)as
    v_cursor number;            --定义光标
    v_string varchar2(200);   --字符串变量
    v_row number;               --行数
begin
    v_cursor:=dbms_sql.open_cursor;    --为处理打开光标
    v_string:=’update dinya_test2 a set a.name=:p_name where a.id=:p_id’;
    dbms_sql.parse(v_cursor，v_string，dbms_sql.native);   --分析语句
    dbms_sql.bind_variable(v_cursor，’:p_name’，name);     --绑定变量
    dbms_sql.bind_variable(v_cursor，’:p_id’，id);          --绑定变量
    v_row:=dbms_sql.execute(v_cursor);　　　　　　　　　　　--执行动态ＳＱＬ
    dbms_sql.close_cursor(v_cursor);                        --关闭光标
    exception
        when others then
            dbms_sql.close_cursor(v_cursor);                --关闭光标
            raise;
end;

　　执行过程，根据用户输入的参数更新表中的数据：

SQL> execute proc_dbms_sql_update(2，’csdn_dinya’);

PL/SQL procedure successfully completed

SQL> select * from dinya_test2;
       ID NAME
        1 Oracle
        2 csdn_dinya
        3 ERP
SQL>

　　执行过程后将第二条的name字段的数据更新为新值csdn_dinya。这样就完成了使用dbms_sql包来执行DML语句的功能。

　　使用DBMS_SQL中，如果要执行的动态语句不是查询语句，使用DBMS_SQL.Execute或DBMS_SQL.Variable_Value来执行，如果要执行动态语句是查询语句，则要使用DBMS_SQL.define_column定义输出变量，然后使用DBMS_SQL.Execute， DBMS_SQL.Fetch_Rows， DBMS_SQL.Column_Value及DBMS_SQL.Variable_Value来执行查询并得到结果。
 
　　总结说明：

　　在Oracle开发过程中，我们可以使用动态SQL来执行DDL语句、DML语句、事务控制语句及系统控制语句。但是需要注意的是，PL/SQL块中使用动态SQL执行DDL语句的时候与别的不同，在DDL中使用绑定变量是非法的（bind_variable(v_cursor，’:p_name’，name)），分析后不需要执行DBMS_SQL.Bind_Variable，直接将输入的变量加到字符串中即可。另外，DDL是在调用DBMS_SQL.PARSE时执行的，所以DBMS_SQL.EXECUTE也可以不用，即在上例中的v_row:=dbms_sql.execute(v_cursor)部分可以不要。