程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> ORACLE學習之PL/SQL編程——本地動態SQL,oracle本地動態

ORACLE學習之PL/SQL編程——本地動態SQL,oracle本地動態

編輯:Oracle教程

ORACLE學習之PL/SQL編程——本地動態SQL,oracle本地動態


目錄

  • 讀前注意與概述
  • 動態SQL簡介
  • 使用EXCUTE IMMEDIATE語句
  • 處理多行查詢
  • 在動態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';--DML
BEGIN
   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 DECLARE
2    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 BEGIN
5    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 BEGIN
11    EXECUTE IMMEDIATE c_string BULK COLLECT INTO emp_name_table,emp_sal_table;--裝載更新前的雇員名和工資
12    FOR i IN 1..emp_name_table.count LOOP
13       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 LOOP
17       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 LOOP
12       dbms_output.put_line('名字:'||emp_name_table(i));
13    END LOOP;
14 END;

 

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved