oracle存儲(chǔ)過程
存儲(chǔ)過程
創(chuàng)新互聯(lián)堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都網(wǎng)站制作、成都網(wǎng)站設(shè)計(jì)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時(shí)代的迭部網(wǎng)站設(shè)計(jì)、移動(dòng)媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！
1、創(chuàng)建
create procedure 過程名（變量名 in 變量類型...變量名 out 變量類型...）is
//定義變量 注：變量類型后不需要指定大小
begin
//執(zhí)行的語句
end
例：項(xiàng)目中所用的：
CREATE OR REPLACE PROCEDURE PROC_CBBS_FILES
------存儲(chǔ)過程說明
--/******************************************************
--/*Procedure :PROC_CBBS_FILES -----存儲(chǔ)過程
--/*Discription :把mv_f_xinxg_files視圖中的數(shù)據(jù)依次插入mv_f_cbbs_files表中 -----存儲(chǔ)過程描述
--/*Version :1.0---初始版本
--/*Author :郝曉利
--/*Create Date :2014/08/26
--/*****************************************************
AS
insert_str long; ----插入表語句
BEGIN
FOR x IN(select * frommv_f_xinxg_files)
LOOP
-----循環(huán)
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|| ''',)';
----執(zhí)行insert語句
EXECUTE IMMEDIATE insert_str;
COMMIT; -----提交
END LOOP; -----結(jié)束循環(huán)
END;-----BEGIN END
例：①無返回值的存儲(chǔ)過程：
請(qǐng)寫一個(gè)過程，可以向 book 表添加書，要求通過 java 程序調(diào)用該過程。
--in:表示這是一個(gè)輸入?yún)?shù)，默認(rèn)為 in
--out:表示一個(gè)輸出參數(shù)
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 中調(diào)用
Java 代碼
1. //調(diào)用一個(gè)無返回值的過程
2. import java.sql.*;
3. public class Test2{
4. public static void main(String[] args){
5.
6. try{
7. //1.加載驅(qū)動(dòng)
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.創(chuàng)建 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.執(zhí)行
19. cs.execute();
20. } catch(Exception e){
21. e.printStackTrace();
22. } finally{
23. //6.關(guān)閉各個(gè)打開的資源
24. cs.close();
25. ct.close();
26. }
27. }
28.}
②有返回值的存儲(chǔ)過程（非列表）
例：編寫一個(gè)過程，可以輸入雇員的編號(hào)，返回該雇員的姓名。
Sql 代碼
1. --有輸入和輸出的存儲(chǔ)過程
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.加載驅(qū)動(dòng)
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到連接
Connection ct =DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
//3.創(chuàng)建 CallableStatement
/*CallableStatement cs = ct.prepareCall("{callsp_pro7(?,?,?)}");
//4.給?賦值
cs.setInt(1,10);
cs.setString(2,"笑傲江湖");
cs.setString(3,"人民出版社");*/
//看看如何調(diào)用有返回值的過程
//創(chuàng)建 CallableStatement
/*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?)}");//給第一個(gè)？賦值
cs.setInt(1,7788);
//給第二個(gè)？賦值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
//5.執(zhí)行
cs.execute();
//取出返回值,要注意？的順序
String name=cs.getString(2);
System.out.println("7788的名字"+name);
} catch(Exception e){
e.printStackTrace();
} finally{
//6.關(guān)閉各個(gè)打開的資源
cs.close();
ct.close();
}
}
}注：1、對(duì)于過程的輸入值，使用setXXX,對(duì)于輸出值，使用registerOutParameter,問號(hào)的順序要對(duì)應(yīng)，同時(shí)考慮類型。
2、取出過程返回值的方法是CallableStatement提供的getXXX（輸出參數(shù)的位置）同時(shí)考慮輸出的參數(shù)類型
案例擴(kuò)張：編寫一個(gè)過程，可以輸入雇員的編號(hào)，返回該雇員的姓名、工資和崗位。
Sql 代碼
1. --有輸入和輸出的存儲(chǔ)過程
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.加載驅(qū)動(dòng)
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.創(chuàng)建 CallableStatement
12. /*CallableStatement cs = ct.prepareCall("{callsp_pro7(?,?,?)}");
13. //4.給?賦值
14. cs.setInt(1,10);
15. cs.setString(2,"笑傲江湖");
16. cs.setString(3,"人民出版社");*/
18. //看看如何調(diào)用有返回值的過程
19. //創(chuàng)建 CallableStatement
20. /*CallableStatement cs = ct.prepareCall("{callsp_pro8(?,?,?,?)}");
22. //給第一個(gè)？賦值
23. cs.setInt(1,7788);
24. //給第二個(gè)？賦值
25. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
26. //給第三個(gè)？賦值
27. cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);
28. //給第四個(gè)？賦值
29. cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);
31. //5.執(zhí)行
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.關(guān)閉各個(gè)打開的資源
41. cs.close();
42. ct.close();
43. }
44. }
45.}
③有返回值的存儲(chǔ)過程（列表[結(jié)果集]）
案例：編寫一個(gè)過程，輸入部門號(hào)，返回該部門所有雇員信息。
由于 oracle 存儲(chǔ)過程沒有返回值，它的所有返回值都是通過 out 參數(shù)來替代的，列表同樣也不例外，但由于是集合，所以不能用一般的參數(shù)，必須要用pagkage了。所以要分兩部分：
返回結(jié)果集的過程
1.建立一個(gè)包，在該包中，定義類型 test_cursor,是個(gè)游標(biāo)。 如下：
Sql 代碼
create or replace package testpackage as
TYPE test_cursor is ref cursor;
end testpackage;
2.建立存儲(chǔ)過程。如下：
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程序中調(diào)用該過程
Java代碼
1. import java.sql.*;
2. public class Test2{
3. public static void main(String[] args){
5. try{
6. //1.加載驅(qū)動(dòng)
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. //看看如何調(diào)用有返回值的過程
12. //3.創(chuàng)建 CallableStatement
13. /*CallableStatement cs = ct.prepareCall("{callsp_pro9(?,?)}");
15. //4.給第？賦值
16. cs.setInt(1,10);
17. //給第二個(gè)？賦值
18. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
20. //5.執(zhí)行
21. cs.execute();
22. //對(duì)象強(qiáng)轉(zhuǎn)為結(jié)果集
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.關(guān)閉各個(gè)打開的資源
31. cs.close();
32. ct.close();
33. }
34. }
35.}
運(yùn)行，成功得出部門號(hào)是 10的所有用戶
④編寫分頁過程
例：編寫一個(gè)存儲(chǔ)過程，要求可以輸入表名、每頁顯示記錄數(shù)、當(dāng)前
頁。返回總記錄數(shù)，總頁數(shù)，和返回的結(jié)果集。
Sql 代碼
1. select t1.*, rownum rn from (select * from emp) t1 whererownum<=10;
2. --在分頁時(shí)，大家可以把下面的 sql 語句當(dāng)做一個(gè)模板使用
3. select * from
4. (select t1.*, rownum rn from (select * from emp) t1 whererownum<=10)
5. where rn>=6;
建立一個(gè)包，在該包中，我定義類型 test_cursor,是個(gè)游標(biāo)。如下：
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,--一頁顯示記錄數(shù)
8. pageNow in number,
9. myrows out number,--總記錄數(shù)
10. myPageCount out number,--總頁數(shù)
11. p_cursor out testpackage.test_cursor--返回的記錄集
12. ) is
13.--定義部分
14.--定義 sql語句字符串
15.v_sql varchar2(1000);
16.--定義兩個(gè)整數(shù)
17.v_begin number:=(pageNow-1)*Pagesize+1;
18.v_end number:=pageNow*Pagesize;
19.begin
20.--執(zhí)行部分
21.v_sql:='select * from (select t1.*, rownum rn from (select * from'||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;
22.--把游標(biāo)和 sql關(guān)聯(lián)
23.open p_cursor for v_sql;
24.--計(jì)算 myrows和 myPageCount
25.--組織一個(gè) sql語句
26.v_sql:='select count(*) from '||tableName;
27.--執(zhí)行 sql,并把返回的值，賦給 myrows；
28.execute inmediate v_sql into myrows;
29.--計(jì)算 myPageCount
30.--if myrows%Pagesize=0 then這樣寫是錯(cuò)的
31.if mod(myrows,Pagesize)=0 then
32. myPageCount:=myrows/Pagesize;
33.else
34.myPageCount:=myrows/Pagesize+1
35.end if;
36.--關(guān)閉游標(biāo)
37.close p_cursor;
38.end;
39./
--使用 java測(cè)試
//測(cè)試分頁
Java代碼
import java.sql.*;
public class FenYe{
public static void main(String[] args){
try{
//1.加載驅(qū)動(dòng)
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到連接
Connection ct =DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
//3.創(chuàng)建 CallableStatement
CallableStatement cs = ct.prepareCall("{callfenye(?,?,?,?,?,?)}");
//4.給第？賦值
cs.seString(1,"emp");
cs.setInt(2,5);
cs.setInt(3,2);
//注冊(cè)總記錄數(shù)
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
//注冊(cè)總頁數(shù)
cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
//注冊(cè)返回的結(jié)果集
cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);
//5.執(zhí)行
cs.execute();
//取出總記錄數(shù) /這里要注意，getInt(4)中 4，是由該參數(shù)的位置決定的
int rowNum=cs.getInt(4);
int pageCount = cs.getInt(5);
ResultSet rs=(ResultSet)cs.getObject(6);
//顯示一下，看看對(duì)不對(duì)
System.out.println("rowNum="+rowNum);
System.out.println("總頁數(shù)="+pageCount);
while(rs.next()){
System.out.println("編號(hào)： "+rs.getInt(1)+"名字："+rs.getString(2)+"工資："+rs.getFloat(6));
}
} catch(Exception e){
e.printStackTrace();
} finally{
//6.關(guān)閉各個(gè)打開的資源
cs.close();
ct.close();
}
}
}
運(yùn)行，控制臺(tái)輸出：
rowNum=19
總頁數(shù)：4
編號(hào)：7369名字：SMITH工資：2850.0
編號(hào)：7499 名字：ALLEN 工資：2450.0
編號(hào)：7521 名字：WARD 工資：1562.0
編號(hào)：7566 名字：JONES 工資：7200.0
編號(hào)：7654 名字：MARTIN 工資：1500.0
--新的需要，要求按照薪水從低到高排序，然后取出 6-10
過程的執(zhí)行部分做下改動(dòng)，如下：
Sql 代碼
begin
--執(zhí)行部分
v_sql:='select * from (select t1.*, rownum rn from (select * from'||tableName||' order by sal) t1 where rownum<='||v_end||')
where rn>='||v_begin;
重新執(zhí)行一次 procedure,java不用改變，運(yùn)行，控制臺(tái)輸出：
rowNum=19
總頁數(shù)：4
編號(hào)：7900名字：JAMES工資：950.0
編號(hào)：7876 名字：ADAMS 工資：1100.0
編號(hào)：7521 名字：WARD 工資：1250.0
編號(hào)：7654 名字：MARTIN 工資：1250.0
編號(hào)：7934 名字：MILLER 工資：1300.0
2、㈠在oracle客戶端調(diào)用過程有兩種方法：
①exec 過程名（參數(shù)值...）
②call 過程名（參數(shù)值...）
實(shí)際begin
存儲(chǔ)過程名;
end;（或者右擊存儲(chǔ)過程名并點(diǎn)擊測(cè)試）
?、嬖贘AVA中調(diào)用存儲(chǔ)過程方法：
①加載驅(qū)動(dòng)：Class.forName("oracle.jdbc.driver.OracleDriver");
②得到鏈接：ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:數(shù)據(jù)庫名","用戶名","密碼")
③創(chuàng)建CallableStatement接口對(duì)象：cs=ct.prepareCall("{call過程名（？，？）}");
④給？賦值：cs.setString(1,"haoxiaoli");//？為你插入的字段個(gè)數(shù)，與SQL語句有關(guān)
⑤執(zhí)行我們的語句：cs.execute();
注：如果卡了記得在oracle中先提交commit
3、顯示錯(cuò)誤：
show error;
當(dāng)前標(biāo)題：oracle存儲(chǔ)過程
轉(zhuǎn)載來源：http://bm7419.com/article8/psscop.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供域名注冊(cè)、定制開發(fā)、企業(yè)網(wǎng)站制作、手機(jī)網(wǎng)站建設(shè)、商城網(wǎng)站、網(wǎng)頁設(shè)計(jì)公司
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)
- 手機(jī)APP設(shè)計(jì)制作定律（一） 2016-11-04
- 移動(dòng)APP設(shè)計(jì)需要遵循的五大基本原則 2022-08-11
- 移動(dòng)APP設(shè)計(jì)趨勢(shì)：神奇的微互動(dòng) 2015-12-03
- 電商APP設(shè)計(jì)時(shí)有哪些問題要注意 2016-11-05
- 移動(dòng)時(shí)代的APP設(shè)計(jì)怎樣做到極簡(jiǎn)？ 2017-01-24
- 移動(dòng)時(shí)代的APP設(shè)計(jì)應(yīng)注意哪些問題？ 2017-01-24
- 專業(yè)的APP設(shè)計(jì)師告訴你，怎么做可以讓APP更“快”！ 2016-11-11
- 移動(dòng)應(yīng)用APP設(shè)計(jì)的關(guān)鍵：原型設(shè)計(jì) 2016-08-24
- 小程序的設(shè)計(jì)，和App設(shè)計(jì)一樣嗎？ 2022-08-31
- 登錄框?qū)τ诨ヂ?lián)網(wǎng)APP設(shè)計(jì)而言是必不可少的 2017-01-23
- 成都網(wǎng)站建設(shè)情況下的APP設(shè)計(jì)3如何處理加載頁面？ 2013-11-28
- APP設(shè)計(jì)怎樣才能符合人體工程學(xué)？ 2016-08-09