 动态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…]]; 
|
本类阅读TOP10