oracle cursor 动态sql 语句,ORACLE动态SQL笔记

下面的验证是在PL/SQL developer8 上面编写和验证。窗口为SQL WINDOWS;

1,使用execute immediate处理DDL操作

在PL/SQL处理DDL语句时，execute immediate后面只需要带有ddl语句文本即可，而不需要into和using子句：

example1:

create or replace procedure pro_drop_table(p_table_name varchar2) is

sql_statement varchar2(100);

begin

sql_statement := 'drop table ' || p_table_name;

execute immediate sql_statement;

end;

表test2已经存在数据库中，验证如下：

begin

pro_drop_table('test2');

end;

2,使用execute immediate处理DCL操作

在PL/SQL处理DDL语句时，execute immediate后面只需要带有dcl语句文本即可，而不需要into和using子句：

example2:

create or replace procedure pro_grant_sys_priv(p_priv     varchar2,

p_username varchar2) is

sql_statement varchar2(100);

begin

sql_statement := 'grant ' || p_priv || ' to ' || p_username;

execute immediate sql_statement;

end;

验证如下：

begin

pro_grant_sys_priv('create session', 'scott');

end;

3,使用execute immediate处理DML操作

当使用execute immediate 处理dml语句时，如果dml语句没有占位符，也没有returning子句，那么在execute immediate

语句之后不需要带有using和returning into子句。

(1)处理无占位符和returning子句的DML语句

declare

sql_statement varchar2(100);

begin

sql_statement := 'update emp set sal = sal* 1.1 where deptno=30';

execute immediate sql_statement;

end;

(2)处理包含占位符的DML语句

declare

sql_statement varchar2(100);

begin

sql_statement := 'update emp set sal = sal* (1+:percent/100) ' || ' where deptno=:dno'';

execute immediate sql_statement using &1,&2;

end;

(3)处理包含RETURNING子句的DML语句

declare

salary        number(6, 2);

sql_statement varchar2(100);

begin

sql_statement := 'update emp set sal = sal *(1+:percent/100)' ||  ' where empno = :eno  returning sal into :salary ';

execute immediate sql_statement

using &1, &2

returning into salary;

dbms_output.put_line('新工资:' || salary);

end;

4,使用execute immediate 处理单行查询

declare

sql_statement varchar2(100);

emp_record    emp%rowtype;

begin

sql_statement := 'select * from emp where empno = :eno';

execute immediate sql_statement

into emp_record

using &1;

dbms_output.put_line('雇员 ' || emp_record.ename || '的工资为' ||

emp_record.sal);

end;

5,动态SQL处理多行查询语句，需要使用OPEN-FOR,FETCH和CLOSE 语句。

---定义游标变量---打开游标变量---循环游标变量---关闭游标变量

(1),定义游标变量语法

declare type cursor_name is ref cursor;

cursor_variable cursor_name;

(2),打开游标变量语法

OPEN cursor_variable for dynamic_string [USING bind_argument[,bin_argument]...];

--dynamic_string是动态的select语句,bind_argument用于指定存放传递给动态select语句值的变量。

(3),循环提取数据语法

FETCH cursor_variable INTO {var1[,var2]..|  record_var};

--var是用于接收提取结果的变量；record_var是用于接收提取结果的记录变量。

(4),关闭游标

CLOSE cursor_variable；

(5),查询示例：

declare

type emp_cur_type is ref cursor;

emp_cur       emp_cur_type;

emp_record    emp%rowtype;

sql_statement varchar2(200);

begin

sql_statement := 'select * from emp where deptno = :dno';

open emp_cur for sql_statement

using &dno;

loop

fetch emp_cur

into emp_record;

exit when emp_cur%notfound;

dbms_output.put_line('雇员名：' || emp_record.ename || '，工资' ||

emp_record.sal);

end loop;

end;

6,在动态SQL语句中使用BULK子句，实际是动态SQL语句将变量绑定为集合元素。

集合类型可以是PL/SQL所支持的索引表，嵌套表和VARRY；当集合元素必须使用SQL数据类型(number,char等)，

而不能使用PL/SQL数据类型(如binary_integer,boolean等)；有三种支持BULK子句：EXECUTE IMMEDIATE,FETCH,

FORALL，下面分别介绍在这三种语句中使用BULK子句的方法。

(1)使用bulk子句处理DML语句返回子句

declare

type ename_table_type is table of emp.ename%type index by binary_integer;

type sal_table_type is table of emp.sal%type index by binary_integer;

ename_table   ename_table_type;

sal_table     sal_table_type;

sql_statement varchar2(200);

begin

sql_statement := 'update emp set sal = sal*(1+percent/100)' ||

' where deptno=:dno' ||

' returing ename,sal into :name,:salary';

execute immediate sql_statement

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;