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。這對于定義相同數據類型的變量非常有用。delcarev_a number(5):=10;v_b v_a%type:=15;v_c v_a%type;begindbms_output.put_line('v_a='||v_a||'v_b='||v_b||'v_c='||v_c);endsql>/v_a=10 v_b=15 v_c=pl/sql procedure successfully completed.sql> 其他dml語句 其它操作數據的dml語句是:insert、update、delete和lock table,這些語句在pl/sql中的語法與在sql中的語法相同。我們在前面已經討論過dml語句的使用這里就不再重復了。在dml語句中可以使用任何在declare部分聲明的變量，如果是嵌套塊，那么要注意變量的作用范圍。 例：create or replace procedure 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中游標名是一個未聲明變量，不能給游標名賦值或用于表達式中。 例：delcarecursor c_emp is select empno,ename,salaryfrom empwhere salary>2000order 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 ondeclarev_ename emp.ename%type;v_salary emp.salary%type;cursor c_emp is select ename,salary from emp;beginopen 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 ondeclarev_ename emp.ename%type;v_salary emp.salary%type;cursor c_emp is select ename,salary from emp;beginopen c_emp;loopfetch 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 ondeclarer_emp emp%rowtype;cursor c_emp is select * from emp;beginopen c_emp;loopfetch 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 ondeclarecursor c_emp is select ename,salary from emp;r_emp c_emp%rowtype;beginopen c_emp;loopfetch 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]....]; 參數值可以是文字或變量。 例：decalrecursor c_dept is select * from dept order by deptno;cursor c_emp (p_dept varachar2) isselect ename,salaryfrom empwhere deptno=p_deptorder by enamer_dept dept%rowtype;v_ename emp.ename%type;v_salary emp.salary%type;v_tot_salary emp.salary%type;beginopen c_dept;loopfetch 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);loopfetch 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)loopstatementsend loop; 下面我們用for循環重寫上面的例子：decalrecursor c_dept is select deptno,dname from dept order by deptno;cursor c_emp (p_dept varachar2) isselect ename,salaryfrom empwhere deptno=p_deptorder by enamev_tot_salary emp.salary%type;beginfor r_dept in c_dept loopdbms_output.put_line('department:'|| r_dept.deptno||'-'||r_dept.dname);v_tot_salary:=0;for r_emp in c_emp(r_dept.deptno) loopdbms_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 empwhere deptno not in (select deptnofrom deptwhere 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} 例：delcarecursor c1 is select empno,salaryfrom empwhere comm is nullfor update of comm;v_comm number(10,2);beginfor r1 in c1 loopif r1.salary<500 thenv_comm:=r1.salary*0.25;elseif r1.salary<1000 thenv_comm:=r1.salary*0.20;elseif r1.salary<3000 thenv_comm:=r1.salary*0.15;elsev_comm:=r1.salary*0.12;end if;update emp;set comm=v_commwhere current of c1l;end loop;end
菜鳥學堂：
