转：ORACLE 动态SQL

Oracle实际上比SQL Server好一些，但绝对没有传说中那么强。这是这些天一识Oracle庐山真面目的感受。看来好多事情多如此，听的要比见到的完美。接着第一句说，Oracle的动态SQL就很棒，我专门学习了一下。记录如下。

先说说动态SQL是什么，看这句熟悉的：
select * from a_table where a_variable=a_declarevalue;
再写句动态的：
select * from a_table where a_variable=:a_dynamicvalue;
两句的区别很明显，后者多一个占位符，这个以冒号开始的变量可以灵活地执行不同条件的where语句。
这是动态SQL语句的优势，接下来的功能就是它的独门功夫了--执行DDL,DCL语句。

动态SQL的执行
1 EXECUTE IMMEDIATE语句

EXECUTE IMMEDIATE dynamic_string

[INTO {define_variable[,define_variable]…| record}]

[USING [IN | OUT | IN OUT] bind_argument[,[IN | OUT / IN OUT] bind_argumnet]…]

[{RETURNING | RETURN} INTO bing_argument[,bind_argument]…];

下面是它的使用

处理DDL操作(CREATE,ALTER,DROP)

CREATE OR REPLACE PROCEDURE drop_table(table_name VARCHAR2)

IS

  Sql_statemet VARCHAR2(100);

BEGIN

  Sql_statement:=’DROP TABLE’ || table_name;

  EXECUTE IMMEDIATE sql_statement;

END;

/

建立过程drop_table后，调用如下：

SQL> exec drop_table(‘worker’)

 

处理DCL操作(GRANT REVOKE)

SQL> conn system/manager

CREATE OR REPLACE PROCEDURE grant_sys_priv(priv VARCHAR2,username VARCHAR2)

IS

  Sql_stat VARCHAR2(100);

BEGIN

  Sql_stat:=’GRANT “ || priv|| ’ TO ’|| username;

EXECUTE IMMEDIATE sql_stat;

END;

/

调用

SQL> exec grant_sys_priv(‘CREATE SESSION’,’SCOTT’)

处理DML操作(INSERT UPDATE DELETE)

如果DML语句带有占位符，那么在E I语句中则要带USING子句
如果DML语句带有RETURNING子句，那么E I语句中要带有RETURNINGINTO子句

例子，处理单行查询：
DECLARE
  sql_stat VARCHAR2(100);
  emp_record tbl%ROWTYPE;
BEGIN
  sql-stat:='SELECT * FROM tbl WHERE tblno=:no';
  EXECUTE IMMEDIATE sql_stat INTO emp_record USING &1;
  dbms_output.put_line(emp_record.ename||emp_record.sal);
END;
/

2 使用OPEN-FOR，FETCH  和 CLOSE 语句处理多行查询

动态处理SELECT语句步骤：定义游标->打开游标->循环提取数据->关闭游标
定义：
TYPE cursortype IS REF CURSOR;
cursor_variable cursortype;
打开：
OPEN cursor_variable FOR dynamic_string
[USING bind_argument[,bing_argument]...];
提取：
FETCH cursor_variable INTO {var1[,var2]...| recor_var};
关闭：
CLOSE cursor_variable;

显示特定部门雇员姓名和工资
DECLARE
  TYPE empcurtype IS REF CURSOR;
  emp_cs empcurtype;
  emp_record emptable%ROWTYPE;
  sql_stat VARCHAR2(100);
BEGIN
  sql_stat:='select * from emptable where deptno=:dno';
  OPEN emp_cs FOR sql_stat USING &dno;
  LOOP
    FETCH emp_cs INTO emp_record;
    EXIT WHEN emp_cs%NOTFOUND;
    dbms_output.put_line(emp_record.ename||emp_record.sal);
  END LOOP;
  CLOSE emp_cs; 
END;
/

3 使用批量动态SQL(9i)
BULK子句可以加快批量数据的处理速度。有三种语句支持BULK子句的方法。
1 使用EXECUTE IMMEDIATE，语法为：
EXECUTE IMMEDIATE dynamic_string
[BULK COLLECT INTO define_variable[,define_variable …]]
[USING [IN | OUT | IN OUT] bind_argument[,[IN | OUT / IN OUT] bind_argumnet]…]
[{RETURNING | RETURN}
BULK COLLECT INTO return_variable[,return_variable…]]; 

用于DML处理多行子句，例子：为某部门所有员工增加%比的工资
DECLARE ……
BEGIN
  sql_stat:='UPDATE emptbl SET sal=sal*(1+:percent/100)' ||
                'WHERE deptno=:dno' ||
                'RETURNING ename,sal INTO :name,:salary';

  EXECUTE IMMEDIATE sql_stat USING &percent,&dno
    RETURNING BULK COLLECT INTO ename_table,sal_table;

  FOR i IN 1.ename_table.COUNT LOOP
    dbms_output.put_line( ename_table(i) ||sal_table(i) );
  END LOOP;

END；
/

2 FETCH语句，语法为
FETCH dynamic_cursor
BULK COLLECT INTO define_variable[,dyfine_variable ...];

3 FORALL语句。适用于DML，不适用于动态的SELECT语句。FORALL语句要与E I 结合使用。语法为

FORALL index IN lower bound..upper bound

EXECUTE IMMEDIATE dynamic_string

USING bind_argument | bind_argumnet(index)

[,bind_argument | bind_argumnet(index)]…

[{RETURNING | RETURN} BULK COLLECT

INTO bind_argument[,bind_argument…]];

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=182656