嗨 甲骨文【4】

來源:互聯網
上載者:User

動態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…]];


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。