牛刀小試PLSQL編程之辟穀篇

條件分支語句
概述：PL/SQL中提供了三種條件分支語句：if---then、if---then---else、if---then---elsif---else
案例：若員工職位是PRESIDENT，則為其增加工資1000；若員工職位是MANAGER，則為其增加工資500；其它職位的員工工資只增加200

create or replace procedure my_pro(currNo number) isv_job emp.job%type;beginselect job into v_job from emp where empno=currNo;if v_job='PRESIDENT' thenupdate emp set sal=sal+1000 where empno=currNo;elsif v_job='MANAGER' thenupdate emp set sal=sal+500 where empno=currNo;elseupdate emp set sal=sal+200 where empno=currNo;end if;end;

控制結構語句
概述：常用的包括loop迴圈、while迴圈、for迴圈

--loop--loop是PL/SQL中最簡單的迴圈語句，它以loop開頭、以end loop結尾。這種迴圈至少會被執行一次--案例：輸入使用者名稱，並迴圈添加10個使用者到users表中，使用者編號從1開始增加create or replace procedure my_pro(currName varchar2) isv_num number:=1; --定義使用者編號變數，並賦初值為1beginloopinsert into users values(v_num, currName);exit when v_num=10; --當編號為10時，退出迴圈v_num:=v_num+1;     --令編號自增end loop;end;

--while--只有條件為true時，才會執行迴圈體語句。它以while...loop開頭、以end loop結尾--案例：輸入使用者名稱，並迴圈添加10個使用者到users表中，使用者編號從11開始增加create or replace procedure my_pro(currName varchar2) isv_num number:=11;beginwhile v_num<=20 loopinsert into users values(v_num, currName);v_num:=v_num+1;end loop;end;

--forbeginfor i in reverse 1..10 loopinsert into users values(i,'玄霄');end loop;end;

--goto--用於跳轉到特定標號去執行語句。由於使用goto語句會增加程式的複雜性，使得應用程式可讀性變差，故不建議使用--其基本文法為goto lable，這裡的lable的是已經定義好的標號名declarei int :=1;beginloopdbms_output.put_line('輸出 i=' || i);if i=10 thengoto myend; --跳轉到myend標號的位置，再向下執行end if;i:=i+1;end loop;dbms_output.put_line('迴圈結束');<> --做一個標號dbms_output.put_line('迴圈結束22');end;

--null--它不會執行任何操作，並且會直接將控制傳遞到下一條語句。使用它的主要好處是可以提高PL/SQL的可讀性declarev_sal emp.sal%type;v_name emp.ename%type;beginselect sal,ename into v_sal,v_name from emp where empno=&no;if v_sal<3000 thenupdate emp set comm=sal*0.1 where ename=v_name;elsenull; --可以認為它是一個空語句，什麼都不幹end if;end;

例外處理
概述：Oracle將例外分為預定義例外、非預定義例外、自訂例外三種
           預定義例外用於處理常見的Oracle錯誤
           非預定義例外則處理預定義例外不能處理的例外
           自訂例外用於處理與Oracle錯誤無關的其它情況
自訂例外：預定義例外和非預定義例外都是與Oracle錯誤相關的，並且出現的Oracle錯誤會隱含的觸發相應的例外
                     而自訂例外與Oracle錯誤沒有任何關聯，它是由開發人員為特定情況所定義的例外

--自訂案例：編寫一個PL/SQL塊，接收一個員工的編號，並為其工資增加1000元，若該員工不存在，請提示create or replace procedure my_ex(myNo number) ismyex exception; --定義一個例外beginupdate emp set sal=sal+1000 where empno=myNo;if sql%notfound then --這裡sql%notfound表示沒有update成功raise myex;  --這裡raise表示觸發myex例外end if;exceptionwhen myex thendbms_output.put_line('錯誤：沒有更新任何使用者');end;

非預定義例外：它用於處理與預定義例外無關的Oracle錯誤
                         使用預定義例外只能處理21個Oracle錯誤，而當使用PL/SQL開發應用程式時，可能會遇到其它的一些Oracle錯誤
                         比如在PL/SQL塊中執行DML語句時，違反了約束規定等等
預定義例外：它是由PL/SQL所提供的系統例外。當PL/SQL應用程式違反了Oracle規定的限制時，則會隱含的觸發一個內部例外
                     PL/SQL為開發人員提供了二十多個預定義例外，下面介紹一些常用的例外

zero_divide          --當執行類似於【2/0】操作時，會觸發該例外logon_denide         --當使用者非法登入時，會觸發該例外not_logged_on        --如果使用者沒有登入，便執行DML操作，會觸發該例外storage_error        --如果超出了記憶體空間或記憶體被損壞，會觸發該例外timeout_on_resource  --如果Oracle在等待資源時，出現了逾時現象，會觸發該例外--case_not_found：在開發PL/SQL塊中編寫case語句時，如果在where子句中沒有包含必須的條件分支，則會觸發該例外create or replace procedure my_pro(myno number) isv_sal emp.sal%type;beginselect sal into v_sal from emp where empno=myno;casewhen v_sal<1000 thenupdate emp set sal=sal+100 where empno=myno;when v_sal<2000 thenupdate emp set sal=sal+200 where empno=myno;end case;exceptionwhen case_not_found then --當查出來的薪水是3000的時候，便會觸發該例外dbms_output.put_line('錯誤：case語句沒有與'||v_sal||'相匹配的條件');end;--cursor_already_open：當重新開啟已經開啟的遊標時，會隱含的觸發該例外declarecursor cursor_emp is select ename,sal from emp;beginopen cursor_emp;for emp_record in cursor_emp loopdbms_output.put_line(emp_record.ename);end loop;exceptionwhen cursor_already_open thendbms_output.put_line('錯誤：遊標已開啟，請不要重複開啟');end;--dup_val_on_index：在唯一索引所對應的列上插入重複值時，會隱含的觸發該例外begininsert into dept values(10, '公關部', '北京');exceptionwhen dup_val_on_index thendbms_output.put_line('錯誤：在dept.detpno上不能出現重複值');end;--invaild_cursor：當視圖在不合法的遊標（如：從未開啟的遊標上提取資料或關閉未開啟的遊標等）上執行操作時，會觸發該例外declarecursor cursor_emp is select ename,sal from emp;emp_record cursor_emp%rowtype;begin--open cursor_emp; --開啟遊標fetch cursor_emp into emp_record;dbms_output.put_line(emp_record.ename);close cursor_emp;exceptionwhen invalid_cursor thendbms_output.put_line('錯誤：請檢測遊標cursor_emp是否已開啟');end;--invalid_number：當輸入的資料有誤時，會觸發該例外beginupdate emp set sal=sal+'1oo'; --不如將數字100寫成了1ooexceptionwhen invalid_number thendbms_output.put_line('錯誤：您所輸入的數字1oo不正確');end;--no_data_found：當執行select--into--from沒有返回行時，即查詢到的資料不存在時，會觸發該例外declarev_sal emp.sal%type;beginselect sal into v_sal from emp where ename='&name';exceptionwhen no_data_found thendbms_output.put_line('錯誤：該員工不存在');end;--too_many_rows：當執行select--into--from時，如果返回多行的值，即查詢到的資料不止一條時，會觸發該例外declarev_ename emp.ename%type;beginselect ename into v_ename from emp;exceptionwhen too_many_rows thendbms_output.put_line('錯誤：傳回值應為一行，這裡卻返回了多行的資料');end;--value_error：在執行賦值操作時，若變數的長度不足以容納實際資料，則會觸發該例外declarev_ename varchar2(5);beginselect ename into v_ename from emp where empno=&no;dbms_output.put_line(v_ename);exceptionwhen value_error thendbms_output.put_line('錯誤：變數的長度不足');end;

Oracle分頁

--無傳回值的預存程序create table book(bookId number, bookName varchar2(50), publishHouse varchar2(50));--in表示這是一個輸入參數，預設為in。out表示這是一個輸出參數create or replace procedure my_pro_book(proBookId in number, proBookName in varchar2, proPublishHouse varchar2) isbegininsert into book values(proBookId, proBookName, proPublishHouse);end;--下面是Java代碼CallableStatement cstmt = java.sql.Connection.prepareCall("{call my_pro_book(?,?,?)}");cstmt.setInt(1, 10);cstmt.setString(2, "盜墓筆記");cstmt.setString(3, "中國友誼出版公司");cstmt.execute();

--有傳回值的預存程序(非列表)create or replace procedure my_pro_book(proBookId in number, proBookName out varchar2, proPublishHouse out varchar2) isbeginselect bookName,publishHouse into proBookName,proPublishHouse from book where bookId=proBookId;end;--下面是Java代碼CallableStatement cstmt = java.sql.Connection.prepareCall("{call my_pro_book(?,?,?)}");cstmt.setInt(1, 10); //給第一個問號賦值cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR); //給第二個問號賦值，可以理解為註冊值cstmt.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR); //給第三個問號賦值cstmt.execute(); //執行該預存程序String publishHouse = cstmt.getString(3); //取出該預存程序的傳回值。注意所取參數值的問號順序，它由該參數的位置決定

--有傳回值的預存程序(列表[結果集])--說明：由於Oracle預存程序沒有傳回值，它的所有傳回值都是通過out參數替代的，列表同樣也不例外--說明：但由於是集合，所以不能用一般的參數，必須要用packagecreate or replace package my_package_emp as type my_cursor is ref cursor;end my_package_emp; --建立一個my_package_book包，並在該包中聲明了一個my_cursor類型的遊標create or replace procedure my_pro_emp(currNo in number, cursor_emp out my_package_emp.my_cursor) isbeginopen cursor_emp for select * from emp where deptno=currNo;end;--下面是Java代碼CallableStatement cstmt = java.sql.Connection.prepareCall("{call my_pro_emp(?,?)}");cstmt.setInt(1, 10);cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR); //此時為該參數註冊的類型為CURSORcstmt.execute();ResultSet rs = (ResultSet)cstmt.getObject(2) //得到結果集。用ResultSet接收getObject()傳回值的同時，注意造型while(rs.next()){System.out.println(rs.getInt(1)+" "+rs.getString(2));}

--編寫Oracle分頁的預存程序create or replace package my_package_pagination as type my_cursor_pagination is ref cursor;create or replace procedure my_pro_pagination(tableName in varchar2,                                               --表名pageSize in number,                                                  --分頁大小。即每頁顯示的記錄數pageNumber in number,                                                --當前頁碼rowCount out number,                                                 --總記錄數pageCount out number,                                                --總頁數cursor_pagination out my_package_pagination.my_cursor_pagination) is --返回的記錄集v_sql varchar2(1000);                                                --定義分頁的SQL語句字串v_beginNo number:=(pageNumber-1)*pageSize+1;v_endNo number:=pageNumber*pageSize;beginv_sql:='select * from (select rownum myno, aa.* from (select * from '||tableName||' order by sal) aa where rownum<='||v_endNo||') where myno>='||v_beginNo;open cursor_pagination for v_sql;          --關聯遊標和SQL語句v_sql:='select count(*) from '||tableName; --組織一個SQL語句execute immediate v_sql into rowCount;     --執行一個SQL語句，並將返回的值賦給rowCountif mod(rowCount,pageSize)=0 then           --計算pageCountpageCount:=rowCount/pageSize;elsepageCount:=rowCount/pageSize+1;end if;close cursor_pagination; --關閉遊標end;--以下是Java代碼CallableStatement cstmt = java.sql.Connection.prepareCall("{call my_pro_pagination(?,?,?,?,?,?)}");cstmt.setString(1, "emp");                                      --指定表名，即待分頁顯示的表cstmt.setInt(2, 5);                                             --指定分頁大小cstmt.setInt(3, 2);                                             --指定顯示的當前頁碼cstmt.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER); --註冊總記錄數cstmt.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER); --註冊總頁數cstmt.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);  --註冊返回的結果集cstmt.execute();Integer rowCount = cstmt.getInt(4);          --取出總記錄數Integer pageCount = cstmt.getInt(5);         --取出總頁數ResultSet rs = (ResultSet)cstmt.getObject(6) --得到結果集while(rs.next()){System.out.println("編號：" + rs.getInt(1) + "  姓名：" + rs.getString(2) + "  工資：" + rs.getFloat(6));}