一、靜態SQL和動態SQL的概念。
1、靜態SQL
靜態SQL是我們常用的使用SQL語句的方式，就是編寫PL/SQL時，SQL語句已經編寫好了。因為靜態SQL是在編寫程序時就確定了，我們只能使用SQL中的DML和事務控制語句，但是DDL語句，以及會話控制語句卻不能再PL/SQL中直接使用，如動態創建表或者某個不確定的操作時，這就需要動態SQL來實現。
2、 動態SQL
動態SQL是指在PL/SQL編譯時SQL語句是不確定的，如根據用戶輸入的參數的不同來執行不同的操作。編譯程序對動態語句部分不進行處理，只是在程序運行時動態創建語句，對語句進行分析，病執行該語句。
靜態SQL的優勢是性能較高，但不靈活。動態SQL的優勢是靈活，缺點是性能稍差。
?
二、動態創建DML、DDL的SQL語句。
動態創建SQL有一下幾類：
1、DDL語句、DCL語句、非查詢的DML語句、單行查詢的SELECT語句，這類可以使用EXECUTE IMMEDIATE語句執行。
2、多行查詢的SELECT語句可以使用游標來實現。
3、通過DBMS_SQL程序包實現。
下面來介紹以上3種情況：
?
1、使用EXECUTE IMMEDIATE語句處理相關語句：
語法：
EXECUTE IMMEDIATE dynamic_sql_string
[into define_variable_list]
[using bind_argument_list];
例：
動態創建表t1
--
處理DDL、DCL語句，根據用戶輸入的表明及字段名動態創建表t1
DECLARE
tablename
VARCHAR2
(
20
);
--
表名
field1
VARCHAR2
(
20
);
--
字段1名稱
datatype1
VARCHAR2
(
20
);
--
字段1類型
field2
VARCHAR2
(
20
);
--
字段2名稱
datatype2
VARCHAR2
(
20
);
--
字段2類型
str_sql
VARCHAR2
(
500
);
--
拼接SQL語句的字符串
BEGIN
tablename :
=
'
t1
'
;
field1:
=
'
id
'
;
datatype1:
=
'
number
'
;
field2:
=
'
name
'
;
datatype2:
=
'
varchar(20)
'
;
str_sql :
=
'
create table
'
||
tablename
||
'
(
'
||
field1
||
'
'
||
datatype1
||
'
,
'
||
field2
||
'
'
||
datatype2
||
'
)
'
;
EXECUTE
IMMEDIATE str_sql;
EXCEPTION
WHEN
OTHERS
THEN
DBMS_OUTPUT.put_line(
'
操作失敗!
'
);
END
;
?
動態插入數據；
--
動態處理費查詢的DML語句:向剛才創建的表中插入數據
DECLARE
v_id
NUMBER
;
--
輸入序號；
v_name
VARCHAR
(
20
);
--
輸入姓名；
str_sql
VARCHAR2
(
500
);
--
保存拼接的SQL語句
BEGIN
v_id :
=
&
vid;
v_name :
=
'
&name
'
;
str_sql :
=
'
insert into t1 values(:1,:2)
'
;
--
使用占位符代表變量
EXECUTE
IMMEDIATE str_sql
USING v_id,v_name;
--
使用變量替換SQL中的占位符,v_id替換:1,v_name替換:2，依此類推。
COMMIT
;
--
執行完畢后直接提交
END
;
?
查詢表中的數據有多少行
--
處理單行查詢的SELECT舉例,查詢表中的數據有多少行
DECLARE
v_count
NUMBER
;
str_sql
VARCHAR2
(
500
);
BEGIN
str_sql :
=
'
select count(*) from t1
'
;
EXECUTE
IMMEDIATE str_sql
INTO
v_count;
--
將查詢的結果存放到變量v_count中。
DBMS_OUTPUT.put_line(v_count);
END
;
?
綁定變量的優缺點：
1）可以再庫緩存中共享游標，節省了CPU等資源，可以避免額外開銷。
2）SQL語句使用綁定變量可以避免被注入攻擊。
3）綁定變量是一種減少應用程序在分析查詢時使用栓鎖數目的可靠方法。
不適合使用變量綁定的情況：
1）對于隔相當長一段時間才執行一次的SQL語句，利用綁定變量的好處hi被不能有效利用而抵消。
2）在數據倉庫的情況下。
3）在對建有索引的字段，且字段非常大時，利用綁定變量可能會導致查詢計劃錯誤，從而導致查詢效率非常低。
?
實現DDL語句中的注意事項：
PL/SQL塊使用動態SQL執行DDL語句的時候與其它不同，在DDL中不能使用綁定變量。
?
實現DML語句中的注意事項：
不能使用綁定變量替換實際的數據庫對象名（表，視圖，列等），只能替換字面兩，如果對象名在運行時生成的，我們只能使用字符串拼接。
?
?
2、通過游標實現多行查詢的SELECT語句
REF游標可以處理返回屆國際的動態SQL。實現動態SQL的REF游標聲明和普通REF游標相同，知識OPEN時綁定的是動態SQL字符串。
例：查詢emp表中所有的數據。
DECLARE
TYPE ref_cur
IS
REF
CURSOR
;
rc ref_cur;
emprow emp
%
ROWTYPE;
v_sql
VARCHAR2
(
100
):
=
'
select * from emp where deptno = :x
'
;
--
動態執行的SQL語句
BEGIN
OPEN
rc
FOR
v_sql USING
30
;
--
打開游標，綁定執行的SQL語句，并傳遞參數
LOOP
FETCH
rc
INTO
emprow;
EXIT
WHEN
rc
%
NOTFOUND;
dbms_output.put_line(
'
name:
'
||
emprow.ename
||
'
sal:
'
||
emprow.sal);
END
LOOP;
CLOSE
rc;
END
;
?
3、DBMS_SQL程序包
DBMS_SQL程序包是系統提供給我們的另一種使用動態SQL的方法。程序包中封裝了一些列存儲過程，幫助我們動態執行SQL。
使用DBMS_SQL包實現動態SQL的步驟如下：
1）將要執行的SQL語句或一個語句塊放到一個字符串變量中。
2）使用DBMS_SQL包的parse過程來分析該字符串。
3）使用DBMS_SQL包的bind_variable過程來綁定變量。
4）使用DBMS_SQL包的execute函數來執行語句。
例：使用DBMS_SQL創建表
DECLARE
tablename
VARCHAR2
(
20
) :
=
'
t2
'
;
--
表名
field1
VARCHAR2
(
20
) :
=
'
id
'
;
--
字段1名稱
datatype1
VARCHAR2
(
20
) :
=
'
number
'
;
--
字段1類型
field2
VARCHAR2
(
20
) :
=
'
name
'
;
--
字段2名稱
datatype2
VARCHAR2
(
20
) :
=
'
varchar(20)
'
;
--
字段2類型
v_sql
VARCHAR2
(
500
) :
=
'
create table
'
||
tablename
||
'
(
'
||
field1
||
'
'
||
datatype1
||
'
,
'
||
field2
||
'
'
||
datatype2
||
'
)
'
;
--
拼接SQL語句的字符串
v_cursor
NUMBER
;
--
定義光標
v_row
NUMBER
;
--
行數
BEGIN
v_cursor:
=
dbms_sql.open_cursor;
--
為處理打開光標
dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);
--
分析語句；
v_row:
=
DBMS_SQL.
execute
(v_cursor);
--
執行sql語句；
dbms_sql.close_cursor(v_cursor);
--
關閉光標；
DBMS_OUTPUT.put_line(v_row);
END
;
向表中插入一條數據：
DECLARE
v_id
NUMBER
:
=
&
vid;
v_name
VARCHAR2
(
20
) :
=
'
&vname
'
;
v_sql
VARCHAR2
(
100
) :
=
'
insert into t2 values(:id,:name)
'
;
v_cursor
NUMBER
;
v_row
NUMBER
;
BEGIN
v_cursor:
=
dbms_sql.open_cursor;
dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);
dbms_sql.bind_variable(v_cursor,
'
:id
'
,v_id);
dbms_sql.bind_variable(v_cursor,
'
:name
'
,v_name);
v_row :
=
dbms_sql.
execute
(v_cursor);
dbms_sql.close_cursor(v_cursor);
COMMIT
;
DBMS_OUTPUT.put_line(v_row);
END
;
?
查詢EMP中的數據
DECLARE
V_DEPTNO
NUMBER
:
=
&
DEPTNO;
V_SQL
VARCHAR2
(
100
) :
=
'
select empno,ename,sal from emp where deptno = :deptno
'
;
V_CURSOR
NUMBER
;
V_NO
NUMBER
;
V_ENAME
VARCHAR2
(
20
);
V_SAL
NUMBER
;
v_start
NUMBER
;
BEGIN
V_CURSOR :
=
DBMS_SQL.OPEN_CURSOR;
--
打開游標
DBMS_SQL.PARSE(V_CURSOR, V_SQL, DBMS_SQL.NATIVE);
--
解析動態SQL語句
DBMS_SQL.BIND_VARIABLE(V_CURSOR,
'
:deptno
'
, V_DEPTNO);
--
傳遞參數
DBMS_SQL.DEFINE_COLUMN(V_CURSOR,
1
, V_NO);
--
定義輸出的列，和查詢的列相匹配
DBMS_SQL.DEFINE_COLUMN(V_CURSOR,
2
, V_ENAME,
20
);
DBMS_SQL.DEFINE_COLUMN(V_CURSOR,
3
, V_SAL);
v_start :
=
DBMS_SQL.
execute
(V_CURSOR);
--
執行SQL語句，需要有接受返回值
LOOP
EXIT
WHEN
DBMS_SQL.FETCH_ROWS(V_CURSOR)
<=
0
;
--
解析游標，
DBMS_SQL.COLUMN_VALUE(V_CURSOR,
1
, V_NO);
--
將當前行的數據寫入上面對應的列中。
DBMS_SQL.COLUMN_VALUE(V_CURSOR,
2
, V_ENAME);
DBMS_SQL.COLUMN_VALUE(V_CURSOR,
1
, V_SAL);
DBMS_OUTPUT.PUT_LINE(
'
no:
'
||
V_NO
||
'
enmae:
'
||
V_ENAME
||
'
sal:
'
||
V_SAL);
--
輸出內容
END
LOOP;
dbms_sql.close_cursor(v_cursor);
--
關閉游標
END
;
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
