oracle的動態sql是指在語句塊使用execute immediate 執行sql語句，sql語句可以使用存儲過程傳的參數進行拼接，本文針對varchar2和number兩種類型的參數類型，進行sql拼接並執行。
功能：輸入日期區間，銷售數量滿足上限和下限的產品id
1.正常傳值
輸出結果
2.若果number類型的參數傳空，會報ora-00936:缺失表達式，可以在存儲過程中增加對參數null值的判斷
oracle 動態拼接傳入參數，varchar2類型可以使用'''|| IN_START_DTIME || ''' ，number類型可以使用'||IN_DOWN_LIMIT||' ； 拼接的過程需要注意校驗參數的合法性，增加存儲過程的容錯性。臨時表使用了會話級，存儲過程執行完，可以通過查詢存儲過程獲取結果。
② oracle存儲過程中循環for in是如何使用的
1、首先編寫存儲過程的整體結構，如下圖所示定義變數。
③ 如何實現Oracle資料庫中的動態游標
createorreplace空攔凳斗旅procereP_TEST_SQLis
TYPEref_cursor_typeISREFCURSOR;--定義一個動態游標
tablenamevarchar2(200)default'ess_client';
v_sqlvarchar2(1000);
mobilevarchar2(15);
usrsref_cursor_type;
begin
--使用連接符拼接成一條完整SQL
v_sql:='selectusrmsisdnfrom'||tablename||'whererownum<11';
--打開游標
openusrsforv_sql;
loop
fetchusrsintomobile;
exitwhenusrs%notfound;衡跡
insertintotmp(usrmsisdn)values(mobile);
endloop;
closeusrs;
commit;
endP_TEST_SQL;
④ oralce 異常處理中控制繼續循環
你在異常處理部分想繼續坦陸游循環是不可能的，因為已經跳到異常處理部分了，是不能悉租再回去繼續執行的。建議你將相關的功能寫到一個plsql 函數中，在這個函數內部處理no_data_found異常，循環體在函數外面讓銷，根據函數的返回值判定是否繼續循環。也就是說，循環體內是一個處理了異常的函數。
⑤ 關於oracle8i的帶參數游標用法的問題
oracle資料庫游標使用大全
sql是用於訪問oracle資料庫的語言，pl/sql擴展和加強了sql的功能，它同時引入了更強的程序邏輯。 pl/sql支持dml命令和sql的事務控制語句。ddl在pl/sql中不被支持，這就意味作在pl/sql程序塊中不能創建表或其他任何對象。較好的pl/sql程序設計是在pl/sql塊中使用象dbms_sql這樣的內建包或執行execute immediate命令建立動態sql來執行ddl命令，pl/sql編譯器保證對象引用以及用戶的許可權。
下面我們將討論各種用於訪問oracle資料庫的ddl和tcl語句。
查詢
select語句用於從資料庫中查詢數據，當在pl/sql中使用select語句時，要與into子句一起使用，查詢的返回值被賦予into子句中的變數，變數的聲明是在delcare中。select into語法如下：
select [distict|all]{*|column[,column,...]}
into (variable[,variable,...] |record)
from {table|(sub-query)}[alias]
where............
pl/sql中select語句只返回一行數據。如果超過一行數據，那麼就要使用顯式游標（對游標的討論我們將在後面進行），into子句中要有與select子句中相同列數量的變數。into子句中也可以是記錄變數。
%type屬性
在pl/sql中可以將變數和常量聲明為內建或用戶定義的數據類型，以引用一個列名，同時繼承他的數據類型和大小。這種動態賦值方法是非常有用的，比如變數引用的列的數據類型和大小改變了，如果使用了%type,那麼用戶就不必修改代碼，否則就必須修改代碼。
例：
v_empno scott.emp.empno%type;
v_salary emp.salary%type;
不但列名可以使用%type,而且變數、游標、記錄，或聲明的常量都可以使用%type。這對於定義相同數據類型的變數非常有用。
delcare
v_a number(5):=10;
v_b v_a%type:=15;
v_c v_a%type;
begin
dbms_output.put_line
(''v_a=''||v_a||''v_b=''||v_b||''v_c=''||v_c);
end
sql>/
v_a=10 v_b=15 v_c=
pl/sql procere successfully completed.
sql>
其他dml語句
其它操作數據的dml語句是:insert、update、delete和lock table,這些語句在pl/sql中的語法與在sql中的語法相同。我們在前面已經討論過dml語句的使用這里就不再重復了。在dml語句中可以使用任何在declare部分聲明的變數，如果是嵌套塊，那麼要注意變數的作用范圍。
例：
create or replace procere fire_employee (pempno in number)
as
v_ename emp.ename%type;
begin
select ename into v_ename
from emp
where empno=p_empno;
insert into former_emp(empno,ename)
values (p_empno,v_ename);
delete from emp
where empno=p_empno;
update former_emp
set date_deleted=sysdate
where empno=p_empno;
exception
when no_data_found then
dbms_output.put_line(''employee number not found!'');
end
dml語句的結果
當執行一條dml語句後，dml語句的結果保存在四個游標屬性中，這些屬性用於控製程序流程或者了解程序的狀態。當運行dml語句時，pl/sql打開一個內建游標並處理結果，游標是維護查詢結果的內存中的一個區域，游標在運行dml語句時打開，完成後關閉。隱式游標只使用sql%found,sql%notfound,sql%rowcount三個屬性.sql%found,sql%notfound是布爾值，sql%rowcount是整數值。
sql%found和sql%notfound
在執行任何dml語句前sql%found和sql%notfound的值都是null,在執行dml語句後，sql%found的屬性值將是：
. true :insert
. true :delete和update，至少有一行被delete或update.
. true :select into至少返回一行
當sql%found為true時,sql%notfound為false。
sql%rowcount
在執行任何dml語句之前，sql%rowcount的值都是null,對於select into語句，如果執行成功，sql%rowcount的值為1,如果沒有成功，sql%rowcount的值為0，同時產生一個異常no_data_found.
sql%isopen
sql%isopen是一個布爾值，如果游標打開，則為true, 如果游標關閉，則為false.對於隱式游標而言sql%isopen總是false，這是因為隱式游標在dml語句執行時打開，結束時就立即關閉。
事務控制語句
事務是一個工作的邏輯單元可以包括一個或多個dml語句，事物控制幫助用戶保證數據的一致性。如果事務控制邏輯單元中的任何一個dml語句失敗，那麼整個事務都將回滾，在pl/sql中用戶可以明確地使用commit、rollback、savepoint以及set transaction語句。
commit語句終止事務，永久保存資料庫的變化，同時釋放所有lock,rollback終止現行事務釋放所有lock，但不保存資料庫的任何變化,savepoint用於設置中間點，當事務調用過多的資料庫操作時，中間點是非常有用的，set transaction用於設置事務屬性，比如read-write和隔離級等。
顯式游標
當查詢返回結果超過一行時，就需要一個顯式游標，此時用戶不能使用select into語句。pl/sql管理隱式游標，當查詢開始時隱式游標打開，查詢結束時隱式游標自動關閉。顯式游標在pl/sql塊的聲明部分聲明，在執行部分或異常處理部分打開，取數據,關閉。下表顯示了顯式游標和隱式游標的差別：
表1 隱式游標和顯式游標
隱式游標 顯式游標
pl/sql維護，當執行查詢時自動打開和關閉 在程序中顯式定義、打開、關閉，游標有一個名字。
游標屬性前綴是sql 游標屬性的前綴是游標名
屬性%isopen總是為false %isopen根據游標的狀態確定值
select語句帶有into子串，只有一行數據被處理 可以處理多行數據，在程序中設置循環，取出每一行數據。
使用游標
這里要做一個聲明，我們所說的游標通常是指顯式游標，因此從現在起沒有特別指明的情況，我們所說的游標都是指顯式游標。要在程序中使用游標，必須首先聲明游標。
聲明游標
語法：
cursor cursor_name is select_statement;
在pl/sql中游標名是一個未聲明變數，不能給游標名賦值或用於表達式中。
例：
delcare
cursor c_emp is select empno,ename,salary
from emp
where salary>2000
order by ename;
........
begin
在游標定義中select語句中不一定非要表可以是視圖，也可以從多個表或視圖中選擇的列，甚至可以使用*來選擇所有的列 。
打開游標
使用游標中的值之前應該首先打開游標，打開游標初始化查詢處理。打開游標的語法是：
open cursor_name
cursor_name是在聲明部分定義的游標名。
例：
open c_emp;
關閉游標
語法：
close cursor_name
例：
close c_emp;
從游標提取數據
從游標得到一行數據使用fetch命令。每一次提取數據後，游標都指向結果集的下一行。語法如下：
fetch cursor_name into variable[,variable,...]
對於select定義的游標的每一列，fetch變數列表都應該有一個變數與之相對應，變數的類型也要相同。
例：
set serveriutput on
declare
v_ename emp.ename%type;
v_salary emp.salary%type;
cursor c_emp is select ename,salary from emp;
begin
open c_emp;
fetch c_emp into v_ename,v_salary;
dbms_output.put_line(''salary of employee''|| v_ename
||''is''|| v_salary);
fetch c_emp into v_ename,v_salary;
dbms_output.put_line(''salary of employee''|| v_ename
||''is''|| v_salary);
fetch c_emp into v_ename,v_salary;
dbms_output.put_line(''salary of employee''|| v_ename
||''is''|| v_salary);
close c_emp;
end
這段代碼無疑是非常麻煩的，如果有多行返回結果，可以使用循環並用游標屬性為結束循環的條件，以這種方式提取數據，程序的可讀性和簡潔性都大為提高，下面我們使用循環重新寫上面的程序：
set serveriutput on
declare
v_ename emp.ename%type;
v_salary emp.salary%type;
cursor c_emp is select ename,salary from emp;
begin
open c_emp;
loop
fetch c_emp into v_ename,v_salary;
exit when c_emp%notfound;
dbms_output.put_line(''salary of employee''|| v_ename
||''is''|| v_salary);
end
記錄變數
定義一個記錄變數使用type命令和%rowtype，關於%rowstype的更多信息請參閱相關資料。
記錄變數用於從游標中提取數據行，當游標選擇很多列的時候，那麼使用記錄比為每列聲明一個變數要方便得多。
當在表上使用%rowtype並將從游標中取出的值放入記錄中時，如果要選擇表中所有列，那麼在select子句中使用*比將所有列名列出來要安全得多。
例：
set serveriutput on
declare
r_emp emp%rowtype;
cursor c_emp is select * from emp;
begin
open c_emp;
loop
fetch c_emp into r_emp;
exit when c_emp%notfound;
dbms_out.put.put_line(''salary of employee''||r_emp.ename||''is''|| r_emp.salary);
end loop;
close c_emp;
end;
%rowtype也可以用游標名來定義，這樣的話就必須要首先聲明游標：
set serveriutput on
declare
cursor c_emp is select ename,salary from emp;
r_emp c_emp%rowtype;
begin
open c_emp;
loop
fetch c_emp into r_emp;
exit when c_emp%notfound;
dbms_out.put.put_line(''salary of employee''||r_emp.ename||''is''|| r_emp.salary);
end loop;
close c_emp;
end;
帶參數的游標
與存儲過程和函數相似，可以將參數傳遞給游標並在查詢中使用。這對於處理在某種條件下打開游標的情況非常有用。它的語法如下：
cursor cursor_name[(parameter[,parameter],...)] is select_statement;
定義參數的語法如下：
parameter_name [in] data_type[{:=|default} value]
與存儲過程不同的是，游標只能接受傳遞的值，而不能返回值。參數只定義數據類型，沒有大小。
另外可以給參數設定一個預設值，當沒有參數值傳遞給游標時，就使用預設值。游標中定義的參數只是一個佔位符，在別處引用該參數不一定可靠。
在打開游標時給參數賦值，語法如下：
open cursor_name[value[,value]....];
參數值可以是文字或變數。
例：
decalre
cursor c_dept is select * from dept order by deptno;
cursor c_emp (p_dept varachar2) is
select ename,salary
from emp
where deptno=p_dept
order by ename
r_dept dept%rowtype;
v_ename emp.ename%type;
v_salary emp.salary%type;
v_tot_salary emp.salary%type;
begin
open c_dept;
loop
fetch c_dept into r_dept;
exit when c_dept%notfound;
dbms_output.put_line(''department:''|| r_dept.deptno||''-''||r_dept.dname);
v_tot_salary:=0;
open c_emp(r_dept.deptno);
loop
fetch c_emp into v_ename,v_salary;
exit when c_emp%notfound;
dbms_output.put_line(''name:''|| v_ename||'' salary:''||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
end loop;
close c_emp;
dbms_output.put_line(''toltal salary for dept:''|| v_tot_salary);
end loop;
close c_dept;
end;
游標for循環
在大多數時候我們在設計程序的時候都遵循下面的步驟：
1、打開游標
2、開始循環
3、從游標中取值
4、檢查那一行被返回
5、處理
6、關閉循環
7、關閉游標
可以簡單的把這一類代碼稱為游標用於循環。但還有一種循環與這種類型不相同，這就是for循環，用於for循環的游標按照正常的聲明方式聲明，它的優點在於不需要顯式的打開、關閉、取數據，測試數據的存在、定義存放數據的變數等等。游標for 循環的語法如下：
for record_name in
(corsor_name[(parameter[,parameter]...)]
| (query_difinition)
loop
statements
end loop;
下面我們用for循環重寫上面的例子：
decalre
cursor c_dept is select deptno,dname from dept order by deptno;
cursor c_emp (p_dept varachar2) is
select ename,salary
from emp
where deptno=p_dept
order by ename
v_tot_salary emp.salary%type;
begin
for r_dept in c_dept loop
dbms_output.put_line(''department:''|| r_dept.deptno||''-''||r_dept.dname);
v_tot_salary:=0;
for r_emp in c_emp(r_dept.deptno) loop
dbms_output.put_line(''name:''|| v_ename||'' salary:''||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
end loop;
dbms_output.put_line(''toltal salary for dept:''|| v_tot_salary);
end loop;
end;
在游標for循環中使用查詢
在游標for循環中可以定義查詢，由於沒有顯式聲明所以游標沒有名字，記錄名通過游標查詢來定義。
decalre
v_tot_salary emp.salary%type;
begin
for r_dept in (select deptno,dname from dept order by deptno) loop
dbms_output.put_line(''department:''|| r_dept.deptno||''-''||r_dept.dname);
v_tot_salary:=0;
for r_emp in (select ename,salary
from emp
where deptno=p_dept
order by ename) loop
dbms_output.put_line(''name:''|| v_ename||'' salary:''||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
end loop;
dbms_output.put_line(''toltal salary for dept:''|| v_tot_salary);
end loop;
end;
游標中的子查詢
語法如下：
cursor c1 is select * from emp
where deptno not in (select deptno
from dept
where dname!=''accounting'');
可以看出與sql中的子查詢沒有什麼區別。
游標中的更新和刪除
在pl/sql中依然可以使用update和delete語句更新或刪除數據行。顯式游標只有在需要獲得多行數據的情況下使用。pl/sql提供了僅僅使用游標就可以執行刪除或更新記錄的方法。
update或delete語句中的where current of子串專門處理要執行update或delete操作的表中取出的最近的數據。要使用這個方法，在聲明游標時必須使用for update子串，當對話使用for update子串打開一個游標時，所有返回集中的數據行都將處於行級（row-level)獨占式鎖定，其他對象只能查詢這些數據行，不能進行update、delete或select...for update操作。
語法：
for update [of [schema.]table.column[,[schema.]table.column]..
[nowait]
在多表查詢中，使用of子句來鎖定特定的表,如果忽略了of子句，那麼所有表中選擇的數據行都將被鎖定。如果這些數據行已經被其他會話鎖定，那麼正常情況下oracle將等待，直到數據行解鎖。
在update和delete中使用where current of子串的語法如下：
where{current of cursor_name|search_condition}
例：
delcare
cursor c1 is select empno,salary
from emp
where comm is null
for update of comm;
v_comm number(10,2);
begin
for r1 in c1 loop
if r1.salary<500 then
v_comm:=r1.salary*0.25;
elseif r1.salary<1000 then
v_comm:=r1.salary*0.20;
elseif r1.salary<3000 then
v_comm:=r1.salary*0.15;
else
v_comm:=r1.salary*0.12;
end if;
update emp;
set comm=v_comm
where current of c1l;
end loop;
end
⑥ oracle存儲過程中如何執行動態SQL語句 詳細
有時需要在oracle
存儲過程中執行動態SQL
語句
，例如表名是動態的，或欄位是動態的，
或查詢命令是動態的，可用下面的方法：
set
serveroutput
ondeclaren
number;sql_stmt
varchar2(50);
t
varchar2(20);beginexecute
immediate
'alter
session
set
nls_date_format=''YYYYMMDD''';
t
:=
't_'
||
sysdate;
sql_stmt
:=
'select
count(*)
from
'
||
t;
execute
immediate
sql_stmt
into
n;
dbms_output.put_line('The
number
of
rows
of
'
||
t
||
'
is
'
||
n);end;
如果動態SQL
語句
很長很復雜，則可用包裝.
CREATE
OR
REPLACE
PACKAGE
test_pkgISTYPE
cur_typ
IS
REF
CURSOR;
PROCEDURE
test_proc
(v_table
VARCHAR2,t_cur
OUT
cur_typ);END;/
CREATE
OR
REPLACE
PACKAGE
BODY
test_pkgISPROCEDURE
test_proc
(v_table
VARCHAR2,t_cur
OUT
cur_typ)ISsqlstr
VARCHAR2(2000);BEGINsqlstr
:=
'SELECT
*
FROM
'||v_table;
OPEN
t_cur
FOR
sqlstr;END;END;/
在oracle
中批量導入，導出和刪除表名以某些字元開頭的表
spool
c:\a.sql
⑦ oracle 存儲過程兩個for循環 怎麼寫
這種情況必須定義行類型的變數來解決：
declare
row_data tb_student%ROWTYPE
for row_data in tb_student loop
update student st set st.class_name = row_data.class_name
where st.class_id = row_data.class_id
end loop;
但這樣種循環更新效率確實很低，SQL是面向集合的運算，像你這種需求可以用一條更新SQL外加子查詢來解決，不建議用循環來做。
⑧ oracle 中動態sql語句，表名為變數，怎麼解
表名可用變數，但一般需要用到動態sql，舉例如下：
declare
v_date varchar2(8);--定義日期變數
v_sql varchar2(2000);--定義動態sql
v_tablename varchar2(20);--定義動態表名
begin
select to_char(sysdate,'yyyymmdd') into v_date from al;--取日期變數
v_tablename := 'T_'||v_date;--為動態表命名
v_sql := 'create table '||v_tablename||'
(id int,
name varchar2(20))';--為動態sql賦值
dbms_output.put_line(v_sql);--列印sql語句
execute immediate v_sql;--執行動態sql
end;
執行以後，就會生成以日期命名的表。
⑨ 如何在oracle存儲過程中執行動態sql語句
給你一個案例對這些，使用execute immediate就可以了，存儲過程和語句塊也是一樣的，自己改一改，沒區別的。
語法格式
EXECUTEIMMEDIATEdynamic_string
[INTO{define_variable[,define_variable]...|record}]
[USING[IN|OUT|INOUT]bind_argument[,[IN|OUT|INOUT]bind_argument]...]
[{RETURNING|RETURN}INTObind_argument[,bind_argument]...];
1，操作DDL語句，這也是動態SQL的常用操作之一
如下所示使用動態SQL創建資料庫表：
DECLARE
l_dync_sqlVARCHAR2(100);
BEGIN
l_dync_sql:='CREATETABLEcux_dync_test(idNUMBER,creation_dateDATE)';
EXECUTEIMMEDIATEl_dync_sql;
END;
2，操作DML語句，使用USING子句可以按照順序將輸入的值綁定到變數，如果動態SQL只有單行輸出的話可以直接使用INTO來接收輸出值，如下所示。
DECLARE
l_dync_sqlVARCHAR2(100);
l_person_nameVARCHAR2(140);
l_ageNUMBER;
BEGIN
l_dync_sql:='SELECTperson_name,ageFROMcux_cursor_testWHEREperson_id=:1';
EXECUTEIMMEDIATEl_dync_sql
INTOl_person_name,l_age--使用into語句接手動態SQL的輸出，如果輸出多行則出錯
USING101;--給綁定變數賦值
dbms_output.put_line('PersonName:'||l_person_name);
dbms_output.put_line('Age:'||l_age);
END;
⑩ oracle筆記-動態SQL
第 章 動態SQL
為何使用動態SQL
實現動態SQL有兩種方式 DBMS_SQL和本地動態SQL（EXECUTE IMMEIDATE）
主要從以下方面考慮使用哪種方式
是否知道涉及的列數和類型
DBMS_SQL包括了一個可以 描述 結果集的存儲過程（DBMS_SQL DESCRIBE_COLUMNS） 而本地動態SQL沒有
是否知道可能涉及的綁定變數數和類型
DBMS_SQL允許過程化的綁定語句的輸入 而本地動態SQL需要在編譯時確定
是否使用 數組化 操作（Array Processing）
DBMS_SQL允許 而本地動態SQL基本不可以 但可以用其他方式實現（對查詢可用FETCH BULK COLLECT INTO 對INSERT等 可用一個BEGIN … END塊中加循環實現）
是否在同一個會話中多次執行同一語句
DBMS_SQL可以分析一次執行多次 而本地動態SQL會在每次執行時進行軟分析
是否需要用REF CURSOR返回結果集
僅本地動態SQL可用REF CURSOR返回結果集
如何使用動態SQL
DBMS_SQL
調用OPEN_CURSOR獲得一個游標句柄
調用PARSE分析語句 一個游標句柄可以用於多條不同的已分析語句 但一個時間點僅一條有效
調用BIND_VARIABLE或BIND_ARRAY來提供語句的任何輸入
若是一個查詢（SELECT語句） 調用DIFINE_COLUMN或DEFINE_ARRAY來告知Oracle如何返回結果
調用EXECUTE執行語句
若是一個查詢 調用FETCH_ROWS來讀取數據 可以使用COLUMN_VALUE從SELECT列表根據位置獲得這些值
否則 若是一個PL/SQL塊或帶有RETURN子句的DML語句 可以調用VARIABLE_VALUE從塊中根據變數名獲得OUT值
調用CLOSE_CURSOR
注意這里對任何異常都應該處理 以關閉游標 防止泄露資源
本地動態SQL
EXECUTE IMMEDIATE 語句
[INTO {變數 變數 … 變數N | 記錄體}]
[USING [IN | OUT | IN OUT] 綁定變數 … 綁定變數N]
[{RETURNING | RETURN} INTO 輸出 [ … 輸出N]…]
注意本地動態SQL僅支持弱類型REF CURSOR 即對於REF CURSOR 不支持BULK COLLECT
最後說明
lishixin/Article/program/Oracle/201311/18948