oracle存儲過程

存儲過程

1、創建

create procedure 過程名（變量名 in 變量類型...變量名 out 變量類型...）is

//定義變量  注：變量類型后不需要指定大小

begin

//執行的語句

end

例：項目中所用的：

CREATE OR REPLACE PROCEDURE PROC_CBBS_FILES

------存儲過程說明

 --/******************************************************

  --/*Procedure     :PROC_CBBS_FILES                                                -----存儲過程

  --/*Discription   :把mv_f_xinxg_files視圖中的數據依次插入mv_f_cbbs_files表中    -----存儲過程描述

  --/*Version      :1.0---初始版本

  --/*Author       :郝曉利

  --/*Create Date  :2014/08/26

 --/*****************************************************

 AS

  insert_str long; ----插入表語句

BEGIN

  FOR x IN(select * frommv_f_xinxg_files)

    LOOP

    -----循環

    insert_str := 'INSERT INTOmv_f_cbbs_files(MO_ID,CAPTION,TIME,FILES,PROV_ID,PROV_NAME,TACHE_NAME,BUSI_TYPE)

               VALUES(''' ||x.MO_ID || ''',''' || x.CAPTION || ''',''' || x.TIME || ''',''' ||

               x.FILES || ''','''|| x.PROV_code || ''',''' || x.PROV_NAME || ''',''' || x.TACHE_NAME || ''','''||x.busi_type|| ''',)';

    ----執行insert語句

    EXECUTE IMMEDIATE insert_str;

    COMMIT; -----提交

  END LOOP; -----結束循環

END; -----BEGIN  END

例：①無返回值的存儲過程：

請寫一個過程，可以向 book 表添加書，要求通過 java 程序調用該過程。

--in:表示這是一個輸入參數，默認為 in

--out:表示一個輸出參數

Sql 代碼

1. create or replace procedure sp_pro7(spBookId in number,spbookNa

me in varchar2,sppublishHouse in varchar2) is

2. begin

3. insert into book values(spBookId,spbookName,sppublishHouse);

4. end;

5. /

--在 java 中調用

Java 代碼

1. //調用一個無返回值的過程

2. import java.sql.*;

3. public class Test2{

4. public static void main(String[] args){

5.

6. try{

7. //1.加載驅動

8. Class.forName("oracle.jdbc.driver.OracleDriver");

9. //2.得到連接

10. Connection ct = DriverManager.getConnection("jdbc:o

racle:thin@127.0.0.1:1521:MYORA1","scott","m123");

11.

12. //3.創建 CallableStatement

13. CallableStatement cs = ct.prepareCall("{callsp_pro7(?,?,?)}");

14. //4.給?賦值

15. cs.setInt(1,10);

16. cs.setString(2,"笑傲江湖");

17. cs.setString(3,"人民出版社");

18. //5.執行

19. cs.execute();

20. } catch(Exception e){

21. e.printStackTrace();

22. } finally{

23. //6.關閉各個打開的資源

24. cs.close();

25. ct.close();

26. }

27. }

28.}

②有返回值的存儲過程（非列表）

例：編寫一個過程，可以輸入雇員的編號，返回該雇員的姓名。

Sql 代碼

1. --有輸入和輸出的存儲過程

 create or replace procedure sp_pro8(spno in number, spName out varchar2)is

 begin

 select ename into spName from empwhere empno=spno;

 end;

 /

Java 代碼

 import java.sql.*;

 public class Test2{

 public static void main(String[]args){

 try{

 //1.加載驅動

 Class.forName("oracle.jdbc.driver.OracleDriver");

 //2.得到連接

 Connection ct =DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");

 //3.創建 CallableStatement

/*CallableStatement cs = ct.prepareCall("{callsp_pro7(?,?,?)}");

//4.給?賦值

cs.setInt(1,10);

cs.setString(2,"笑傲江湖");

cs.setString(3,"人民出版社");*/

//看看如何調用有返回值的過程

//創建 CallableStatement

/*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?)}");//給第一個？賦值

cs.setInt(1,7788);

//給第二個？賦值

cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);

 //5.執行

cs.execute();

//取出返回值,要注意？的順序

String name=cs.getString(2);

System.out.println("7788 的名字"+name);

} catch(Exception e){

e.printStackTrace();

} finally{

//6.關閉各個打開的資源

cs.close();

ct.close();

}

}

}注：1、對于過程的輸入值，使用setXXX,對于輸出值，使用registerOutParameter,問號的順序要對應，同時考慮類型。

2、取出過程返回值的方法是CallableStatement提供的getXXX（輸出參數的位置）同時考慮輸出的參數類型

案例擴張：編寫一個過程，可以輸入雇員的編號，返回該雇員的姓名、工資和崗位。

Sql 代碼

1. --有輸入和輸出的存儲過程

2. create or replace procedure sp_pro8

3. (spno in number, spName out varchar2,spSal out number,spJob outvarchar2) is

4. begin

5. select ename,sal,job into spName,spSal,spJob from emp where empno=spno;

6. end;

7. /

Java 代碼

1. import java.sql.*;

2. public class Test2{

3. public static void main(String[] args){

5. try{

6. //1.加載驅動

7. Class.forName("oracle.jdbc.driver.OracleDriver");

8. //2.得到連接

9. Connection ct =DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");

11. //3.創建 CallableStatement

12. /*CallableStatement cs = ct.prepareCall("{callsp_pro7(?,?,?)}");

13. //4.給?賦值

14. cs.setInt(1,10);

15. cs.setString(2,"笑傲江湖");

16. cs.setString(3,"人民出版社");*/

18. //看看如何調用有返回值的過程

19. //創建 CallableStatement

20. /*CallableStatement cs = ct.prepareCall("{callsp_pro8(?,?,?,?)}");

22. //給第一個？賦值

23. cs.setInt(1,7788);

24. //給第二個？賦值

25. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);

26. //給第三個？賦值

27. cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);

28. //給第四個？賦值

29. cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);

31. //5.執行

32. cs.execute();

33. //取出返回值,要注意？的順序

34. String name=cs.getString(2);

35. String job=cs.getString(4);

36. System.out.println("7788 的名字"+name+" 工作："+job);

37. } catch(Exception e){

38. e.printStackTrace();

39. } finally{

40. //6.關閉各個打開的資源

41. cs.close();

42. ct.close();

43. }

44. }

45.}

③有返回值的存儲過程（列表[結果集]）

案例：編寫一個過程，輸入部門號，返回該部門所有雇員信息。

由于 oracle 存儲過程沒有返回值，它的所有返回值都是通過 out 參數來替代的，列表同樣也不例外，但由于是集合，所以不能用一般的參數，必須要用pagkage了。所以要分兩部分：

返回結果集的過程

1.建立一個包，在該包中，定義類型 test_cursor,是個游標。 如下：

Sql 代碼

create or replace package testpackage as

TYPE test_cursor is ref cursor;

end testpackage;

2.建立存儲過程。如下：

Sql 代碼

1. create or replace procedure sp_pro9(spNo in number,p_cursor outtestpackage.test_cursor) is

2. begin

3. open p_cursor for select * from emp where deptno = spNo;

5. end sp_pro9;

3.如何在 java 程序中調用該過程

Java 代碼

1. import java.sql.*;

2. public class Test2{

3. public static void main(String[] args){

5. try{

6. //1.加載驅動

7. Class.forName("oracle.jdbc.driver.OracleDriver");

8. //2.得到連接

9. Connection ct =DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");

11. //看看如何調用有返回值的過程

12. //3.創建 CallableStatement

13. /*CallableStatement cs = ct.prepareCall("{callsp_pro9(?,?)}");

15. //4.給第？賦值

16. cs.setInt(1,10);

17. //給第二個？賦值

18. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);

20. //5.執行

21. cs.execute();

22. //對象強轉為結果集

23. ResultSet rs=(ResultSet)cs.getObject(2);

24. while(rs.next()){

25. System.out.println(rs.getInt(1)+" "+rs.getString(2));

26. }

27. } catch(Exception e){

28. e.printStackTrace();

29. } finally{

30. //6.關閉各個打開的資源

31. cs.close();

32. ct.close();

33. }

34. }

35.}

運行，成功得出部門號是 10 的所有用戶

④編寫分頁過程

例：編寫一個存儲過程，要求可以輸入表名、每頁顯示記錄數、當前

頁。返回總記錄數，總頁數，和返回的結果集。

Sql 代碼

1. select t1.*, rownum rn from (select * from emp) t1 whererownum<=10;

2. --在分頁時，大家可以把下面的 sql 語句當做一個模板使用

3. select * from

4. (select t1.*, rownum rn from (select * from emp) t1 whererownum<=10)

5. where rn>=6;

建立一個包，在該包中，我定義類型 test_cursor,是個游標。如下：

Sql 代碼

1. create or replace package testpackage as

2. TYPE test_cursor is ref cursor;

3. end testpackage;

 --開始編寫分頁的過程

5. create or replace procedure fenye

6. (tableName in varchar2,

7. Pagesize in number,--一頁顯示記錄數

8. pageNow in number,

9. myrows out number,--總記錄數

10. myPageCount out number,--總頁數

11. p_cursor out testpackage.test_cursor--返回的記錄集

12. ) is

13.--定義部分

14.--定義 sql 語句字符串

15.v_sql varchar2(1000);

16.--定義兩個整數

17.v_begin number:=(pageNow-1)*Pagesize+1;

18.v_end number:=pageNow*Pagesize;

19.begin

20.--執行部分

21.v_sql:='select * from (select t1.*, rownum rn from (select * from'||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;

22.--把游標和 sql 關聯

23.open p_cursor for v_sql;

24.--計算 myrows 和 myPageCount

25.--組織一個 sql 語句

26.v_sql:='select count(*) from '||tableName;

27.--執行 sql,并把返回的值，賦給 myrows；

28.execute inmediate v_sql into myrows;

29.--計算 myPageCount

30.--if myrows%Pagesize=0 then 這樣寫是錯的

31.if mod(myrows,Pagesize)=0 then

32. myPageCount:=myrows/Pagesize;

33.else

34.myPageCount:=myrows/Pagesize+1

35.end if;

36.--關閉游標

37.close p_cursor;

38.end;

39./

--使用 java 測試

//測試分頁

Java 代碼

import java.sql.*;

public class FenYe{

public static void main(String[] args){

  try{

  //1.加載驅動

   Class.forName("oracle.jdbc.driver.OracleDriver");

  //2.得到連接

  Connection ct =DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");

 //3.創建 CallableStatement

 CallableStatement cs = ct.prepareCall("{callfenye(?,?,?,?,?,?)}");

 //4.給第？賦值

 cs.seString(1,"emp");

 cs.setInt(2,5);

 cs.setInt(3,2);

 //注冊總記錄數

 cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);

 //注冊總頁數

 cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);

 //注冊返回的結果集

 cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);

 //5.執行

 cs.execute();

 //取出總記錄數 /這里要注意，getInt(4)中 4，是由該參數的位置決定的

 int rowNum=cs.getInt(4);

 int pageCount = cs.getInt(5);

 ResultSet rs=(ResultSet)cs.getObject(6);

 //顯示一下，看看對不對

 System.out.println("rowNum="+rowNum);

 System.out.println("總頁數="+pageCount);

 while(rs.next()){

 System.out.println("編號： "+rs.getInt(1)+" 名字："+rs.getString(2)+"工資："+rs.getFloat(6));

 }

 } catch(Exception e){

 e.printStackTrace();

 } finally{

 //6.關閉各個打開的資源

 cs.close();

 ct.close();

 }

 }

}

運行，控制臺輸出：

rowNum=19

總頁數：4

編號：7369 名字：SMITH 工資：2850.0

編號：7499 名字：ALLEN 工資：2450.0

編號：7521 名字：WARD 工資：1562.0

編號：7566 名字：JONES 工資：7200.0

編號：7654 名字：MARTIN 工資：1500.0

--新的需要，要求按照薪水從低到高排序，然后取出 6-10

過程的執行部分做下改動，如下：

Sql 代碼

begin

--執行部分

v_sql:='select * from (select t1.*, rownum rn from (select * from'||tableName||' order by sal) t1 where rownum<='||v_end||')

where rn>='||v_begin;

重新執行一次 procedure,java 不用改變，運行，控制臺輸出：

rowNum=19

總頁數：4

編號：7900 名字：JAMES 工資：950.0

編號：7876 名字：ADAMS 工資：1100.0

編號：7521 名字：WARD 工資：1250.0

編號：7654 名字：MARTIN 工資：1250.0

編號：7934 名字：MILLER 工資：1300.0

2、㈠在oracle客戶端調用過程有兩種方法：

   ①exec 過程名（參數值...）

   ②call 過程名（參數值...）

實際begin

     存儲過程名;

end;（或者右擊存儲過程名并點擊測試）

 　㈡在JAVA中調用存儲過程方法：

　①加載驅動：Class.forName("oracle.jdbc.driver.OracleDriver");

　②得到鏈接：ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:數據庫名","用戶名","密碼")

　③創建CallableStatement接口對象：cs=ct.prepareCall("{call過程名（？，？）}");

　④給？賦值：cs.setString(1,"haoxiaoli");//？為你插入的字段個數，與SQL語句有關

　⑤執行我們的語句：cs.execute();

注：如果卡了記得在oracle中先提交commit

3、顯示錯誤：

show error;