你的位置:首页 > 数据库

[数据库]ORACLE学习之PL/SQL编程——本地动态SQL


目录

  • 读前注意与概述
  • 动态SQL简介
  • 使用EXCUTE IMMEDIATE语句
    1. 处理DDL和DCL语句
    2. 处理DML语句
    3. 处理单行查询
  • 处理多行查询
  • 在动态SQL中使用批量绑定

读前注意与概述

  当编写PL/SQL块时,静态SQL语句只能完成一些固定任务。为了使得PL/SQL块可以灵活的处理SQL语句,需要使用动态SQL。动态SQL包括本地动态SQL和DBMS_SQL动态SQL两种实现方法,其中本地动态SQL只适用于oracle服务器端,而DBMS_SQL动态SQL不仅适用于oracle服务器端,而且可以在某些客户端应用程序中使用。从oracle 11g开始,允许使用超过32Kb的本地动态SQL语句。本节学习任务:

  • 学会使用本地动态SQL处理DDL、DCL、和DML语句。
  • 学会使用本地动态SQL处理查询语句。
  • 学会在本地动态SQL中使用批量绑定。

 

动态SQL简介

  • 静态SQL:指直接嵌入在PL/SQL块中的SQL语句,用于完成特定的或相对固定的任务。

      例如:简单的CRUD操作

      SELECT * FROM emp_learn WHERE employee_id=v_eno;

      INSERT INTO emp_learn (employee_id,first_name) VALUES (v_eid,v_name);

      UPDATE emp_learn SET salary=salary*1.1 WHERE first_name=v_name;

      DELETE FROM emp_learn WHERE employee_id=v_eid;

  • 动态SQL:指在执行PL/SQL块时动态输入的SQL语句。当在PL/SQL块中嵌入DDL或者DCL语句时,必须使用动态SQL。另外,如果需要执行更加灵活的select和DML语句(爆好占位符“:”冒号开始),也需要使用动态SQL.

      CREATE TABLE temp(col1 INT,col2 VARCHAR2(20));--DDL

      GRANT SELECT ON emp_learn TO smith;--DCL

      DELETE FROM emp_learn WHERE salary>:a;--带有占位符

      SELECT first_name,salary FROM emp_learn WHERE employee_id=:1;--带有占位符

  • 比较静态和动态SQL

      a)静态SQL是在编写PL/SQL块时直接嵌入的SQL语句,动态SQL是在执行PL/SQL块时动态输入的SQL语句。

      b)静态SQL性能优于动态SQL。因此当编写PL/SQL块时,如果功能完全确定,则使用静态SQL;如果不能确定要执行的SQL语句,则使用动态SQL。

  • 动态SQL处理方法

      a)使用EXECUTE IMMEDIATE语句:用于处理 除多行查询之外的任何其他动态SQL,包括DDL语句、DCL语句、DML语句以及单行SELECT语句。

      b)使用OPEN-FOR、FETCH和CLOSE语句:用于处理多行查询操作。

      c)使用批量动态SQL:用于加快SQL语句处理,提高PL/SQL的性能。

 

使用EXCUTE IMMEDIATE语句

  • 处理DDL、DCL和无占位符和returning的DML语句,语法:  

    EXCUTE IMMEDIATE dyn_string;

    dyn_string指定存放DDL、DCL或者DML的语句。

  示例:

DECLARE  ddl_string VARCHAR2(200):='&ddl_string';--DDL  dcl_string VARCHAR2(200):='&dcl_string';--DCL  dml_string VARCHAR2(200):='&dml_string';--DMLBEGIN  EXECUTE IMMEDIATE ddl_string;  EXECUTE IMMEDIATE dcl_string;  EXECUTE IMMEDIATE dml_string; END;

  • 处理包含占位符的DML语句语法:

   EXCUTE IMMEDIATE dyn_string USING bind_arg1 [,bind_arg2,...]

  • 处理包含returning into子句的DML语句(只能接收单行数据,否则需使用批量绑定)语法:

   EXCUTE IMMEDIATE dyn_string RETURNING INTO variable1 [, variable2,...];

1 DECLARE2  dml_string VARCHAR2(200):='UPDATE emp_learn SET salary=:sal WHERE LOWER(first_name)=LOWER(:name) RETURNING salary INTO :newsal';3  v_newsal emp_learn.salary%TYPE;4 BEGIN5  EXECUTE IMMEDIATE dml_string USING &1,'&2' RETURNING INTO v_newsal;6  dbms_output.put_line('新工资为:'||v_newsal); 7 END;

 


  • 处理单行查询,语法:

    EXCUTE IMMEDIATE dyn_string INTO variable1 [,variable2,...] [USING bing_arg1 [,bind_arg2,...]];

  示例:处理单行查询

DECLARE  v_select1 VARCHAR2(200):='SELECT * FROM emp_learn WHERE employee_id=&eno';  v_select2 VARCHAR2(200):='SELECT * FROM emp_learn WHERE employee_id=:eno';  emp_record emp%ROWTYPE;BEGIN  EXECUTE IMMEDIATE v_select1 INTO emp_record;  dbms_output.put_line('姓名:'||emp_record.first_name||',职位:'||emp_record.job_id);  EXECUTE IMMEDIATE v_select2 INTO emp_record USING &1;  dbms_output.put_line('姓名:'||emp_record.first_name||',职位:'||emp_record.job_id);END;

 

处理多行查询

   EXCUTE IMMEDIATE只能用于处理单行查询,为了动态处理select语句所返回的多行数据,需要定义游标变量,并使用open for、fetch和close语句共同完成,步骤:

    a)定义游标变量:TYPE cursor_type IS REF CURSOR;

              cursor_var cursor_type;

    b)打开游标变量获取结果集:OPEN cursor_var FOR dyn_string [USING bind_args];

    c)提取数据:FETCH cursor_var INTO vars;

    d)关闭游标变量:CLOSE cursor_var;  

  示例:处理多行查询

DECLARE  TYPE emp_cursor_type IS REF CURSOR;  emp_cursor emp_cursor_type;  emp_record emp_learn%ROWTYPE;  v_string VARCHAR2(200):='SELECT * FROM emp_learn WHERE department_id=:dno';BEGIN  OPEN emp_cursor FOR v_string USING &1;  LOOP    FETCH emp_cursor INTO emp_record;    EXIT WHEN emp_cursor%NOTFOUND;    dbms_output.put_line('雇员名:'||emp_record.first_name);  END LOOP;  CLOSE emp_cursor;END;

 

在动态SQL中使用批量绑定

  使用在动态SQL中使用批量绑定,可以加快批量数据的处理速度,提高性能。需要注意的是:使用批量绑定时,集合元素需要使用SQL固有数据类型(例如number、char等),而不能使用PL/SQL特有的数据类型(例如BINARY_INTGER、BOOLEAN等)。

  • 在动态DML上使用批量绑定,语法:

    FORALL index IN lower_boud..upper_bound

        EXECUTE IMMEDIATE dyn_string ...;

DECLARE  TYPE name_table_type IS TABLE OF emp.first_name%TYPE;  emp_name_table name_table_type:=name_table_type('&name1','&name2','&name3');  dml_string VARCHAR2(100):='UPDATE emp_learn SET salary=salary*1.2 WHERE LOWER (first_name)=LOWER(:1)';BEGIN  FORALL i IN 1..emp_name_table.count   EXECUTE IMMEDIATE dml_string USING emp_name_table(i);END;

  • 批量绑定查询、在DML返回子句上使用批量绑定

    批量绑定查询语法:EXECUTE IMMEDIATE dyn_string BULK COLLECT INTO collect_vars;

    在DML返回子句上使用批量绑定语法:EXECUTE IMMEDIATE dyn_string RETURNING BULK COLLECT INTO collect_vars;

 1 DECLARE 2  TYPE emp_name_table_type IS TABLE OF emp_learn.first_name%TYPE; 3  TYPE enp_sal_table_type IS TABLE OF emp_learn.salary%TYPE; 4  emp_name_table emp_name_table_type;--装雇员名 5  emp_sal_table enp_sal_table_type; --装工资 6  v_deptno NUMBER:=&dno; 7  c_string VARCHAR2(100):='SELECT first_name,salary FROM emp_learn WHERE department_id='||v_deptno; 8  dml_string VARCHAR(150):= 9    'UPDATE emp_learn SET salary=salary*1.1 WHERE department_id=:1 RETURNING first_name,salary INTO :2,:3';10 BEGIN11  EXECUTE IMMEDIATE c_string BULK COLLECT INTO emp_name_table,emp_sal_table;--装载更新前的雇员名和工资12  FOR i IN 1..emp_name_table.count LOOP13    dbms_output.put_line('雇员:'||emp_name_table(i)||',前工资:'||emp_sal_table(i));14  END LOOP;15  EXECUTE IMMEDIATE dml_string USING v_deptno RETURNING BULK COLLECT INTO emp_name_table,emp_sal_table;--执行更新并返回数据16  FOR i IN 1..emp_name_table.count LOOP17    dbms_output.put_line('雇员:'||emp_name_table(i)||',后工资:'||emp_sal_table(i));18  END LOOP;19 END;

  • 在fetch语句章使用批量提取

    这个功能可以一次性提取游标结果集中的所有数据,很方便哦,使用一次后我就喜欢上它了,语法:

    FETCH cursor_var BULK COLLECT INTO collect_var;

 1 DECLARE 2  TYPE emp_name_cursor_type IS REF CURSOR; 3  TYPE emp_name_table_type IS TABLE OF emp_learn.first_name%TYPE; 4   emp_name_cursor emp_name_cursor_type; 5   emp_name_table emp_name_table_type; 6  c_string VARCHAR2(150):='SELECT first_name FROM emp_learn WHERE department_id=:1'; 7 BEGIN 8  OPEN emp_name_cursor FOR c_string USING '&dno'; 9  FETCH emp_name_cursor BULK COLLECT INTO emp_name_table;10  CLOSE emp_name_cursor;11  FOR i IN 1..emp_name_table.count LOOP12    dbms_output.put_line('名字:'||emp_name_table(i));13  END LOOP;14 END;