一、流程式控制制語句 1) 迴圈語句 == loop .. end loop 簡單的迴圈,至少被執行一次 == while ... loop end loop == for 2) 控制語句 == goto 用於跳轉到指定的標號去執行,不建議使用 語法: goto 標號名 == null 語句 null語 ...
一、流程式控制制語句
1) 迴圈語句
== loop .. end loop
簡單的迴圈,至少被執行一次
create table userinfo (id number, name varchar2(30)) ; //創建一個表 create or replace procedure sp_04 (p_name varchar2) is //創建一個存儲過程,迴圈往表裡添10條數據 v_num number :=1; begin loop insert into userinfo values (v_num,p_name); exit when v_num=10; v_num :=v_num+1; end loop; end; exec sp_04('xxxx') ;
== while ... loop end loop
create or replace procedure sp_05 (p_name varchar2) is v_num number :=11; begin while v_num <=20 loop insert into userinfo values (v_num,p_name); v_num :=v_num+1; end loop; end;
== for
create or replace procedure sp_06 (p_name varchar2) is v_num number :=21; begin for i in 21..30 loop //可以 寫成 for i reverse in 21..30 loop , // reverse表示反轉,按相反的順序添加 insert into userinfo values (v_num,p_name); v_num :=v_num+1; end loop; end;
2) 控制語句
== goto
用於跳轉到指定的標號去執行,不建議使用
語法: goto 標號名
set serveroutput on; declare i number:=1; begin loop dbms_output.put_line('i的值是'||i); if i=10 then goto end_loopAAA; end if; i:=i+1; end loop; <<end_loopAAA>> --//這是在聲明一個標號 dbms_output.put_line('程式執行結束'); end;
== null 語句
null語句不執行任何操作,主要是為了讓程式好讀
declare v_sal emp.sal%type; v_ename emp.ename%type; begin select ename,sal into v_ename,v_sal from emp where empno=&no; if v_sal<8000 then update emp set sal=99999 where ename=v_ename; else null; end if; end;
二、存儲過程練習
//練習一 向 book 表添一本書,寫一個存儲過程,向表中添加一條數據,在java程式中調用
create table book ( bookid number, bookname varchar2(50), pubhouse varchar2(50) ) create or replace procedure sp_addbook(sp_bookid in number,sp_bookname in varchar2,pubhouse in varchar2) is --//in 表示是輸入參數 begin insert into book (bookid,bookname,pubhouse) values(sp_bookid,sp_bookname,pubhouse); end;
在java中調用
public static void test3(){ Connection conn=null; CallableStatement stm=null; try{ conn=DBUtil.getConn(); stm=conn.prepareCall("{call sp_addbook(?,?,?)}"); stm.setInt(1, 50); stm.setString(2, "紅岩"); stm.setString(3, "清華大學出版社"); stm.execute(); } catch(Exception ex){ ex.printStackTrace(); } finally{ DBUtil.close(null,stm,conn); } }
//練習二 輸入一個書的編號,返回書名 (有輸入和輸出的存儲過程)
create or replace procedure sp_getbookname (sp_bookid in number ,sp_bookname out varchar2) is --//out 表示這個參數是輸出參數 begin select bookname into sp_bookname from book where bookid= sp_bookid; end;
在java中調用
//根據書的id查詢書名( 即有輸入,也有輸出的存儲過程) public static void test4(){ Connection conn=null; CallableStatement stm=null; try{ conn=DBUtil.getConn(); stm=conn.prepareCall("{call sp_getbookname(?,?)}"); stm.setInt(1, 50); //傳一個輸入參數 (書的id) stm.registerOutParameter(2, OracleTypes.VARCHAR); //指定輸入參數所對應的類型 stm.execute(); String bookName=stm.getString(2); //取存儲過程輸出參數返回的值 2,代表是第2個問號 System.out.println(bookName); } ... }
//練習三 一個存儲過程同時有多個輸出參數
create or replace procedure sp_getempinfo (sp_empno number , sp_totalsal out number, sp_job out varchar2, sp_name out varchar2) is begin select sal*12+nvl(comm,0)*12, job, ename into sp_totalsal,sp_job,sp_name from emp where empno =sp_empno; end; public static void test5(){ Connection conn=null; CallableStatement stm=null; try{ conn=DBUtil.getConn(); stm=conn.prepareCall("{call sp_getempinfo(?,?,?,?)}"); stm.setInt(1, 7788); //傳一個輸入參數 ,員工id stm.registerOutParameter(2, OracleTypes.NUMBER); //sp_totalsal stm.registerOutParameter(3, OracleTypes.VARCHAR); //sp_job stm.registerOutParameter(4, OracleTypes.VARCHAR); //sp_name stm.execute(); System.out.println("姓名是"+stm.getString(4)); System.out.println("崗位是"+stm.getString(3)); System.out.println("年薪是"+stm.getString(2)); }catch(Exception ex){ ex.printStackTrace(); }finally{ DBUtil.close(null,stm,conn); } }
//例四 返回列表(結果集)
編寫一個過程,輸入部門號,查詢出該部門所有員的工信息
Oracle 中的存儲過程沒有返回值 ,所以它返回內容的時候,都是用 out 參數,對於返回列表也不例外,但由於返回的是列表,不能用一般的參數,所以要用 package
1) 建一個包
create or replace package testpackage as TYPE test_cursor is ref cursor ; --//聲明瞭一個游標類型 end testpackage;
2) 建一個存儲過程
create or replace procedure sp_testquery (sp_deptno in number, p_cursor out testpackage.test_cursor ) is begin open p_cursor for select * from emp where deptno=sp_deptno; --// 把游標和查詢關聯起來 end;
3) java程式
public static void getEmpList(){ Connection conn=null; CallableStatement stm=null; try{ conn=DBUtil.getConn(); stm=conn.prepareCall("{call sp_testquery(?,?)}"); //deptno, p_cursor stm.setInt(1, 20); //給部門編號傳值 stm.registerOutParameter(2, OracleTypes.CURSOR); //給第二個參數(存儲過程的輸出參數) 指定類型 stm.execute(); ResultSet rs= (ResultSet)stm.getObject(2); while(rs.next()){ System.out.print(rs.getString("ename")+"\t"); System.out.print(rs.getString("job")+"\t"); System.out.println(rs.getString("sal")); } }catch(Exception ex){ ex.printStackTrace(); }finally{ DBUtil.close(null,stm,conn); } } }
三、分頁存儲過程
1) 分頁語句 :
select * from ( select t1.*,rownum rn from ( select * from emp) t1 where rownum <=10 ) where rn >5;
2) 建一個包
create or replace package pagePackage as type p_cursor is ref cursor ; --//聲明一個游標類型 end pagePackage;
3) 存儲過程
create or replace procedure sp_fenye ( tableName in varchar2 , --表名 pageSize in number, --每頁多少條記錄 pageIndex in number, --當前是第幾頁 sp_cursor out pagePackage.p_cursor, --用於返回記錄集的游標 myrowCount out number, -- 返回記錄總數 mypageCount out number -- 返回總頁數 ) is v_sql varchar2 (2000); v_begin number :=(pageIndex-1)* pageSize +1; v_end number :=pageIndex*pageSize; begin v_sql:=' select * from ( select t1.*,rownum rn from
( select * from '||tableName||') t1 where rownum <='||v_end||' ) where rn >= '||v_begin ; open sp_cursor for v_sql; v_sql:='select count(*) from '||tableName; execute immediate v_sql into myrowCount; --//查詢記錄總數,立即查詢 /*計算mypageCount if mod(myrowCount,pageSize) ==0 then mypageCount:=myrowCount/pageSize; else mypageCount:=myrowCount/pageSize+1; end if; */ mypageCount :=ceil (myrowCount/pageSize); --//這句可以替代上面的邏輯 end;
4) java程式
public static void getListByPage(int pageSize, int pageIndex,String tableName){ Connection conn=null; CallableStatement stm=null; ResultSet rs=null; try{ conn=DBUtil.getConn(); stm=conn.prepareCall("{call sp_fenye(?,?,?,?,?,?)}"); stm.setString(1,tableName); //tableName stm.setInt(2, pageSize); //pageSize 設為每頁5條 stm.setInt(3, pageIndex); //pageIndex ,設當前頁是第二頁 stm.registerOutParameter(4, OracleTypes.CURSOR); //指定第四個參數的返回類型為游標類型 stm.registerOutParameter(5, OracleTypes.NUMBER); //接收總記錄數 stm.registerOutParameter(6, OracleTypes.NUMBER); //接收總頁數 stm.execute(); rs=(ResultSet)stm.getObject(4) ; //打開返回的游標 while(rs.next()){ System.out.print(rs.getString("ename")+"\t"); System.out.print(rs.getDouble("sal")+"\t"); System.out.println(rs.getString("job")+"\t"); } System.out.println("總行數:"+stm.getInt(5)); System.out.println("總頁數:"+stm.getInt(6)); }catch(Exception ex){ ex.printStackTrace(); }finally{ DBUtil.close(rs,stm,conn); } }
四、異常處理
//例外
預定義例外,非預定義例外 和 自定義例外
預定義例外用於處理常見的Oracle 錯誤
非預定義例外用於處理預定義例外不能處理的例外
自定義例外,用於處理與Oracle錯誤無關的其他情況
//例子 set serveroutput on; declare v_ename emp.ename%type ; begin select ename into v_ename from emp where empno=&no; dbms_output.put_line(v_ename); Exception when no_data_found then dbms_output.put_line('數據沒找到'); end;
預定義例外是由 pl/sql提供的系統例外
1) case_not_found
在編寫 case 語句的時候同, 如果when 子句沒有包含必須的 case 分支
create or replace procedure sp_g(spno number) is v_sal emp.sal%type; begin select sal into v_sal from emp where empno=spno; case when v_sal<1000 then dbms_output.put_line('少於1000'); when v_sal<2000 then dbms_output.put_line('少於2000'); end case; exception when case_not_found then dbms_output.put_line('case 語句沒有得到匹配的條件'); end;
2) cursor_already_open
當重新打開已打開的游標時,會觸發
declare cursor emp_cursor is select ename,sal from emp; begin open emp_cursor; for emp_record1 in emp_cursor loop -- 這裡又打開了一次游標 dbms_output.put_line(emp_record1.ename); end loop; exception when cursor_already_open then dbms_output.put_line('游標已經打開'); end;
3) dup_val_on_index
在唯一索引所對應的例上插入重覆的值時觸發
begin insert into dept values(10,'公案安部','北京'); exception when dup_val_on_index then dbms_output.put_line('插入重覆列了'); end;
創建索引 create index 索引名 on 表名(列名)
4) invaild_cursor
試圖在不合法的游標上操作時
例如: 試圖從沒有打開的游標提取數據,或是關閉沒有打開的游標
declare cursor emp_cursor is select ename,sal from emp; emp_record emp_cursor%rowtype; -- //emp_record是這個游標變數的名字, begin
open emp_cursor;//打開游標 fetch emp_cursor into emp_record; dbms_output.put_line(emp_record.ename); close emp_cursor; exception when invalid_cursor then dbms_output.put_line('請檢測游標是否打開'); end;
五、批量生成測試數據
create table TestKKK (idAAA number,dateAAA varchar2(50),randomAAA number , nameAAA varchar2(50)); insert into TestKKK (idAAA ,dateAAA ,randomAAA,nameAAA) select rownum , to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss'), trunc(dbms_random.value(0, 100)), dbms_random.string('x', 20) from dual connect by level <= 100000 //一般用於遞歸查詢