溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
存儲過程
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;
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
