execute immediate在存儲過程中的使用

execute immediate 在存儲過程中使用較為頻繁，其作用是巨大的，下面簡單介紹其基本用法

1：在存儲過程中，我們知道隻能使用DML語句，那麼如果要使用DDL語句呢，就得使用execute immediate。例如：

   ​[email protected]>create or replace procedure pro1

  2     is 

  3     begin 

  4      create table table1(id int);

  5    end;

​6  /

​Warning: Procedure created with compilation errors.

因為沒有存儲過程（匿名塊也一樣），不能使用DDL語句，是以報錯，接下來我們使用execute immediate在存儲過程中使用DDL語句

[email protected]>create or replace procedure pro1

  2    is 

  3  begin 

  4  execute immediate 'create table table1(id int)';

  5  end;

  6  /

Procedure created.

執行該存儲過程就可以建立一個表了，但是這裡要注意你的使用者必須有建立表的權限，并且這個權限不是在角色裡面的賦予的。否則會報錯。

[email protected]>conn / as sysdba 

Connected.

[email protected]>grant create table to wang;

Grant succeeded.

[email protected]>conn wang/beijing 

Connected.

[email protected]>exec pro1;  

2：在寫存儲過程中，可能會碰到你查詢的表是變量值，這個是時候直接使用變量是不行的，因外其會把變量名作為表名，而不是把變量值作為表名（這跟SHELL有所不同）。

例：​

​

[email protected]>create or replace procedure pro2 

  2   is 

  3  v_table_name varchar2(20);

  4  v_count varchar2(20);

  5   begin 

  6  v_table_name :='testemp';

  7  select count(*) into v_count from v_table_name;

  8  dbms_output.put_line(v_count);

  9  end;

 10  /

Warning: Procedure created with compilation errors.

可以使用字元串截取你要是使用的SQL語句，在使用execute immediate 這個SQL實作該功能，這裡需要說明的是普通的存儲過程是通過select xxx into yyy from zz 來進行指派的，但是使用execute immediate是使用execute immediate sql into zz來進行複制的。

例如：使用exexute immediate 來實作上面的功能

[email protected]>create or replace procedure pro2

  2       is 

  3     v_table_name varchar2(20);

  4     v_count number;

  5     v_sql varchar2(100);

  6     begin 

  7      v_table_name:='testemp';

  8      v_sql:='select count(*) from   ' || v_table_name;

  9     execute immediate v_sql into v_count;

 10     dbms_output.put_line(v_count);

 11     end;

 12    /

Procedure created.

[email protected]>exec pro2;

PL/SQL procedure successfully completed.

[email protected]>set serverout on

[email protected]>exec pro2;

14

這跟SHELL裡面是不一樣的，我們看到在使用DML語句後面接變量時，其不會使用變量的值，但是在SEHLL裡面是使用的、，例如：

腳本内容：​

[[email protected]_source /home/oracle]$cat test.sh

#!/bin/bash 

table_name=testemp

fun(){

table_name=$1

sqlplus -s wang/beijing << EOF

set feedback off pagesize 0 linesize 200

select count(*) from $table_name;

exit;

EOF

}

main(){

v_count=`fun $table_name`

echo $v_count

}

main

執行結果：​

[[email protected]_source /home/oracle]$sh test.sh

14