您好,登錄后才能下訂單哦!
Oracle動(dòng)態(tài)SQL和靜態(tài)SQL比較
1.靜態(tài)SQLSQL與動(dòng)態(tài)SQL
Oracle編譯PL/SQL程序塊分為兩個(gè)種:其一為前期聯(lián)編(early binding),即SQL語(yǔ)句在程序編譯期間就已經(jīng)確定,大多數(shù)的編譯情況屬于這種類(lèi)型;另外一種是后期聯(lián)編(late binding),即SQL語(yǔ)句只有在運(yùn)行階段才能建立,例如當(dāng)查詢(xún)條件為用戶(hù)輸入時(shí),那么Oracle的SQL引擎就無(wú)法在編譯期對(duì)該程序語(yǔ)句進(jìn)行確定,只能在用戶(hù)輸入一定的查詢(xún)條件后才能提交給SQL引擎進(jìn)行處理。通常,靜態(tài)SQL采用前一種編譯方式,而動(dòng)態(tài)SQL采用后一種編譯方式。
2.動(dòng)態(tài)SQL程序開(kāi)發(fā)
理解了動(dòng)態(tài)SQL編譯的原理,也就掌握了其基本的開(kāi)發(fā)思想。動(dòng)態(tài)SQL既然是一種”不確定”的SQL,那其執(zhí)行就有其相應(yīng)的特點(diǎn)。Oracle中提供了Execute immediate語(yǔ)句來(lái)執(zhí)行動(dòng)態(tài)SQL,語(yǔ)法如下:
Excute immediate 動(dòng)態(tài)SQL語(yǔ)句 using 綁定參數(shù)列表 returning into 輸出參數(shù)列表;
對(duì)這一語(yǔ)句作如下說(shuō)明:
1)動(dòng)態(tài)SQL是指DDL和不確定的DML(即帶參數(shù)的DML)
2)綁定參數(shù)列表為輸入?yún)?shù)列表,即其類(lèi)型為in類(lèi)型,在運(yùn)行時(shí)刻與動(dòng)態(tài)SQL語(yǔ)句中的參數(shù)(實(shí)際上占位符,可以理解為函數(shù)里面的形式參數(shù))進(jìn)行綁定。
3)輸出參數(shù)列表為動(dòng)態(tài)SQL語(yǔ)句執(zhí)行后返回的參數(shù)列表。
4)由于動(dòng)態(tài)SQL是在運(yùn)行時(shí)刻進(jìn)行確定的,所以相對(duì)于靜態(tài)而言,其更多的會(huì)損失一些系統(tǒng)性能來(lái)?yè)Q取其靈活性。
為了更好的說(shuō)明其開(kāi)發(fā)的過(guò)程,下面列舉一個(gè)實(shí)例:
設(shè)數(shù)據(jù)庫(kù)的他表,其數(shù)據(jù)為如下:
ID | NAME | SAL |
10 | scott | 3000 |
20 | tom | 5000 |
30 | jerry | 4500 |
要求:
1.創(chuàng)建該表并輸入相應(yīng)的數(shù)據(jù)。
2.根據(jù)特定ID可以查詢(xún)到其姓名和薪水的信息。
3.根據(jù)大于特定的薪水的查詢(xún)相應(yīng)的員工信息。
根據(jù)前面的要求,可以分別創(chuàng)建三個(gè)過(guò)程(均使用動(dòng)態(tài)SQL)來(lái)實(shí)現(xiàn):
過(guò)程一:(創(chuàng)建表并插入數(shù)據(jù))
18:16:27 SCOTT@ prod>create or replace procedure p1 as 18:16:36 2 flag number; 18:16:36 3 begin 18:16:36 4 select count(*) into flag from all_tables where table_name='T1'; 18:16:36 5 if (flag=0) then 18:16:36 6 execute immediate 'create table t1(id number,name varchar2(10),sal number)'; 18:16:36 7 else 18:16:36 8 insert into t1 values (10,'scott',3000); 18:16:36 9 insert into t1 values (20,'tom',5000); 18:16:37 10 insert into t1 values (30,'jerry',4500); 18:16:37 11 end if; 18:16:37 12 end p1; 18:16:38 13 / Procedure created. Elapsed: 00:00:00.20 18:16:40 SCOTT@ prod>exec p1; PL/SQL procedure successfully completed. Elapsed: 00:00:00.18 18:16:47 SCOTT@ prod>select * from t1; ID NAME SAL ---------- ---------- ---------- 10 scott 3000 20 tom 5000 30 jerry 4500 Elapsed: 00:00:00.01 18:16:52 SCOTT@ prod>
過(guò)程二:(按id查詢(xún)用戶(hù)信息)
18:40:24 SCOTT@ prod>create or replace procedure p2 (p_id number) as 18:40:26 2 v_name varchar2(10); 18:40:26 3 v_sal number; 18:40:26 4 begin 18:40:26 5 execute immediate 'select name,sal from t1 where id=:1' into v_name,v_sal using p_id; 18:40:26 6 dbms_output.put_line(v_name ||' Salary is: '||to_char(v_sal)); 18:40:26 7 exception 18:40:26 8 when others then 18:40:26 9 dbms_output.put_line('No Data Found'); 18:40:26 10 end p2; 18:40:26 11 / Procedure created. Elapsed: 00:00:00.07 18:40:27 SCOTT@ prod>exec p2(10); scott Salary is: 3000 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 18:40:32 SCOTT@ prod>exec p2(20); tom Salary is: 5000 PL/SQL procedure successfully completed. Elapsed: 00:00:00.02 18:40:40 SCOTT@ prod>exec p2(30); jerry Salary is: 4500 PL/SQL procedure successfully completed. Elapsed: 00:00:00.02 18:40:45 SCOTT@ prod>
過(guò)程三:(查詢(xún)薪水大于某個(gè)值的員工)
18:48:59 SCOTT@ prod>create or replace procedure p3(p_sal number) as 18:50:55 2 r_t1 t1%rowtype; 18:50:55 3 type c_type is ref cursor; 18:50:56 4 c1 c_type; 18:50:56 5 begin 18:50:56 6 open c1 for ' 18:50:56 7 select * from t1 18:50:56 8 where sal >:1' 18:50:56 9 using p_sal; 18:50:56 10 loop 18:50:56 11 fetch c1 into r_t1; 18:50:56 12 exit when c1%notfound; 18:50:56 13 dbms_output.put_line('Salary higher '||to_char(p_sal)||' Name is:'); 18:50:56 14 dbms_output.put_line('ID is ' ||to_char(r_t1.id)||' Name is: '||r_t1.name); 18:50:56 15 end loop; 18:50:56 16 close c1; 18:50:56 17 end p3; 18:50:57 18 / Procedure created. Elapsed: 00:00:00.12 18:50:58 SCOTT@ prod>exec p3(2000); Salary higher 2000 Name is: ID is 10 Name is: scott Salary higher 2000 Name is: ID is 20 Name is: tom Salary higher 2000 Name is: ID is 30 Name is: jerry PL/SQL procedure successfully completed. Elapsed: 00:00:00.02 18:51:15 SCOTT@ prod>
注意:在過(guò)程二中的動(dòng)態(tài)SQL語(yǔ)句使用了占位符“:1“,其實(shí)它相當(dāng)于函數(shù)的形式參數(shù),使用”:“作為前綴,然后使用using語(yǔ)句將p_id在運(yùn)行時(shí)刻將:1給替換掉,這里p_id相當(dāng)于函數(shù)里的實(shí)參。另外過(guò)程三中打開(kāi)的游標(biāo)為動(dòng)態(tài)游標(biāo),它也屬于動(dòng)態(tài)SQL的范疇,其整個(gè)編譯和開(kāi)發(fā)的過(guò)程與execute immediate執(zhí)行的過(guò)程很類(lèi)似。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。