1、無返回值的存儲過程
例如 1、可以向book表添加書,
---建表
Create table book(bookId number,bookName varchar2(50),publishHouse varchar2(50));
--編寫過程 in 表示變量為輸入值,如果不寫默認為輸入值,而不是輸出變量,out為輸出值
Create or replace procedure sp_pro7(spBookId in number,spbookName in varchar2,sppublishHouse in varchar2)is
Begin
Insert into book valuse(spBookId ,spbookName,sppublishHouse );
End
---java 中如何調用
Package com.sp; Importjava.sql.*; Publicclass Test1{ public static void main(string[] args){ try{ //1加載驅動 Class.forName("Oracle.jdbc.driver.OracleDriver"); Connetionct=DriverManger.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1","scott","123"); //2創建callablestatement Callablestatement cs=ct.preparecall(“{call sp_pro7(?,?,?)}”); //給?賦值 Cs.setInt(1,10); Cs.setstring(2,“笑傲江湖”); Cs.setstring(3,"人民出版社"); //執行 Cs.execute();; } Catch(EXCEPTION E){ e.printstacktrace(); } Finally{ 關閉各個鏈接 } } }
2、有返回值的存儲過程
例如輸入書編號返回書姓名
Create or replace procedure sp_pro8(spno in number,spkName out varchar2,spsal out varchar2) is
Begin
select ename ,spsal,into spName, spsal form emp where empno=spno;
End
在java中如何調用
Package com.sp; Importjava.sql.*; Publicclass Test1{ public static void main(string[] args){ try{ //1加載驅動 Class.forName("Oracle.jdbc.driver.OracleDriver"); Connetionct=DriverManger.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1","scott","123"); //2創建callablestatement Callablestatement cs=ct.preparecall(“{call sp_pro8(?,?)}”); //給?賦值 Cs.setInt(1,7788); Cs.registerOutParemeter(2,oracle.jdbc.oracleTyps.varchar)//執行 Cs.registerOutParemeter(3,oracle.jdbc.oracleTyps.double)//執行 //oracle.jdbc.oracleTyps.varchar 表示執行的類型 Cs.execute(); //取出返回值,要注意?順序 String name=sc.getstring(2); String job=cs.getstring(3); System.out.println(“7788的名字”+name +“7788的工資”+sal); } Catch(EXCEPTION E){ e.printstacktrace(); } Finally{ 關閉各個鏈接 } } }
oracle存儲過程本身沒有返回時用out參數替代的,集合需要用package
--建立包,定義類型test_cursor
Create or replace packagetestpackage as
Typetest_cursur is ref cursor;
End testpackage
建立存儲過程
Create or repalce procedure sp_pro9(spNo in number,P_cursor out tespackage,test_cursor) is
begin
Open p_cursor for select *from emp where depto-spNo;
End;
--如何在java中調用
Package com.sp; Importjava.sql.*; Publicclass Test1{ public static void main(string[] args){ try{ //1加載驅動 Class.forName("Oracle.jdbc.driver.OracleDriver"); Connetionct=DriverManger.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1","scott","123"); //2創建callablestatement Callablestatement cs=ct.preparecall(“{call sp_pro9(?,?)}”); //給?賦值 Cs.setInt(1,10); Cs.registerOutParemeter(2,oracle.jdbc.oracleTypes.cursor)
//執行 Cs.execute(); //取出返回值,要注意?順序 ResultSet rs=(ResultSet)cs.getObject(2); While (rs.next()){ system.out.println(rs.getInt(1)+""+rs.getString(2)); } } Catch(EXCEPTION E){ e.printstacktrace(); } Finally{ 關閉各個鏈接 } } }
Sql>select t1.*, rownum rn from(select * from emp) t1;
Sql>select t1.*, rownum rn from(select * from emp) t1 where rownum<=10;
--在分頁的時候,可以
Select *from(
Sql>select t1.*, rownum rn from(select * from emp) t1where rownum<=10;) where rn>=6;
--開發一個包
Create or replace package testpackage as
Typetest_cursur is ref cursor;
End testpackage
5、--編寫分寫的過程 分頁並按工資排序
Create or replace procedure fenye
(tableName in varchar2,
Pagesize in number,
Pagenow in number,
Myrows out number,--總記錄數
mypageCount out number,--總頁數
p_cursor out testpackage.test_cursor --返回的記錄集
) is
--定義部分
--定義sql語句 字符串
v_sql varchar2(1000);
- 定義兩個整數
v_begin number:= (Pagenow-1)*Pagesize +1;
v_endnumber:=Pagenow *Pagesize ;
Begin
--執行部分
v_sql:='Select * from(
Sql>select t1.*, rownum rn from(select * from '||tableName||' order by sal) t1 where rownum<='||v- end||';) where rn>='||v_begin||';'
- -打開游標和sql關聯
Open p_cursor for v_sql;
--計算Myrows 和 mypageCount
--組織一個sql
v_sql:='select count(*) from '|tablename|;
--執行sql,並把返回的值,賦給myrows;
Executeimmediate v_sql into myrows;
-計算mypagecount
If mod(myrows,pagesize)=0 then
Mypagecount:=myrows/pagesize;
Else
Mypagecount:=myrows/pagesize+1;
End if;
--關閉游標
Close p_cursor;
End;
--使用java測試
Package com.sp; Importjava.sql.*; Public class Test1{ public static void main(string[] args){ try{ //1加載驅動 Class.forName("Oracle.jdbc.driver.OracleDriver"); Connetionct=DriverManger.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1","scott","123"); //2創建callablestatement Callablestatement cs=ct.preparecall(“{call fenye(?,?,?,?,?,?)}”); //給?賦值 Cs.setString(1,"emp"); 表 Cs.setInt(2,5);每頁大小 Cs.setInt(3,2);第2頁 //接受總記錄數 Cs.registerOutParemeter(4,oracle.jdbc.oracleTypes.integer)//執行 //注冊總頁數 Cs.registerOutParemeter(5,oracle.jdbc.oracleTypes.integer)//執行 //注冊返回的結果集 Cs.registerOutParemeter(6,oracle.jdbc.oracleTypes.cursor)//執行 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(20)+"薪水"; } } } Catch(EXCEPTION E){ e.printstacktrace(); } Finally{ 關閉各個鏈接 } } }看似很復雜一個分頁過程,通過一步步由簡到繁的分解,由無返回值的存儲過程,到有返回值,進而到以集合形式作為返回值的存儲過程,加上簡單的分頁算法,基於pl/sql中包,游標,mod語句,if分支語句,變量的定義,oder by子句組成。學習的過程就是這樣,一口一個胖子是不可能的,同樣這也是人類認知的一個規律,由簡到繁,在遇到復雜的問題要考慮如何將它拆分成簡單的,自己熟知的問題,一步步了解。