ORACLE动态SQL笔记

转:http://blog.chinaunix.net/uid-20682026-id-3215006.html

 

 

declare
  sql_statement varchar2(2000);
 
  AMOUNT1 number;
  bank varchar2(100):='101860001011700';
  pi  number     :=2;
begin
  sql_statement := 'select AMOUNT'||pi||' from YPY_BANKCOST_AGE_TEMP_ALL  where bank='||bank;
  execute immediate sql_statement  into AMOUNT1;
    
  dbms_output.put_line('数据' ||  AMOUNT1 || '的工资为' || AMOUNT1);
end;
create table CUX.YPY_BANKCOST_AGE_ALL
(
BATCH_ID	NUMBER,
BANK		VARCHAR2(150),
BANKDESC	VARCHAR2(240),
AMOUNT1		NUMBER,
AMOUNT2		NUMBER,
AMOUNT3		NUMBER,
AMOUNT4		NUMBER,
AMOUNT5		NUMBER,
AMOUNT6		NUMBER,
AMOUNT7		NUMBER,
AMOUNT8		NUMBER,
AMOUNT9		NUMBER,
AMOUNT10	NUMBER,
SUMDEBIT	NUMBER,
TOTALMON	NUMBER,
DATE_CREATE  DATE default sysdate ,
ATTRIBUTE_CATEGORY VARCHAR2(150),
ATTRIBUTE1         VARCHAR2(150),
ATTRIBUTE2         VARCHAR2(150),
ATTRIBUTE3         VARCHAR2(150),
ATTRIBUTE4         VARCHAR2(150),
ATTRIBUTE5         VARCHAR2(150),
ATTRIBUTE6         VARCHAR2(150),
ATTRIBUTE7         VARCHAR2(150),
ATTRIBUTE8         VARCHAR2(150),
ATTRIBUTE9         VARCHAR2(150),
ATTRIBUTE10        VARCHAR2(150),
ATTRIBUTE11        VARCHAR2(150),
ATTRIBUTE12        VARCHAR2(150),
ATTRIBUTE13        VARCHAR2(150),
ATTRIBUTE14        VARCHAR2(150),
ATTRIBUTE15        VARCHAR2(150),
CREATION_DATE      DATE default sysdate ,
CREATED_BY         NUMBER default -1,
LAST_UPDATE_DATE   DATE,
LAST_UPDATED_BY    NUMBER,
LAST_UPDATE_LOGIN  NUMBER
 );

我的表结构

 

 

下面的验证是在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;