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