Oracle學習(十二)：預存程序/儲存函數

標籤：員工   on()   art   cep   stp   沒有   儲存   子程式   tno   

1.知識點

--第一個預存程序/*列印Hello Worldcreate [or replace] PROCEDURE 過程名(參數列表)  AS         PLSQL子程式體；調用預存程序:1. exec sayHelloWorld();2. begin      sayHelloWorld();      sayHelloWorld();   end;   /*/create or replace procedure sayHelloWorld<span style="white-space:pre"></span>--sayHelloWorld為過程名as--declare--變數說明begin  dbms_output.put_line(‘Hello World‘);end;/----------------------------------------------/*給指定的員工漲100的工資，並列印漲前和漲後的薪水create [or replace] PROCEDURE 過程名(參數列表)  --in為輸入參數，out為輸出參數AS         PLSQL子程式體；        SQL> begin  2     raiseSalary(7839);  3     raiseSalary(7566);  4     commit;  5  end;  6  /漲前:7986  漲後:8086漲前:5024.53  漲後:5124.53PL/SQL 過程已成功完畢。        */create or replace procedure raiseSalary(eno in number)<span style="white-space:pre"></span>--帶輸入參數的預存程序as  --變數  psal emp.sal%type;begin  --得到漲前薪水  select sal into psal from emp where empno=eno;    --漲工資  update emp set sal=sal+100 where empno=eno;  --問題：要不要commit？？答：不須要，由於整個事務還沒有結束。等調用此預存程序後，再commit  --列印  dbms_output.put_line(‘漲前:‘||psal||‘  漲後:‘||(psal+100));  end;/-------------------------------------------------------------------/*查詢某個員工的年度營收CREATE [OR REPLACE] FUNCTION 函數名(參數列表)  RETURN  函數實值型別ASPLSQL子程式體；*/create or replace function queryEmpIncome(eno in number)return number<span style="white-space:pre"></span>as  --變數  psal emp.sal%type;  pcomm emp.comm%type;begin  select sal,comm into psal,pcomm from emp where empno=eno;  return psal*12+nvl(pcomm,0);end;/-----------------------------------------------------------------------OUT參數/*查詢某個員工的姓名 月薪 職位*/create or replace procedure queryEmpInfo(eno in number,                                         pename out varchar2,                                         psal   out number,                                         pjob   out varchar2)asbegin  select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;end;/----------------------------------------------------------------------查詢某個部門中全部員工的全部資訊--1.建立一個包：MYPACKAGE --2.在該包中定義一個自己定義類型：empcursor 類型為遊標，一個預存程序：queryempCREATE OR REPLACE PACKAGE MYPACKAGE AS     type empcursor is ref cursor;--empcursor是一個自己定義類型：引用cursor的類型作為empcursor的類型  procedure queryEmpList(dno in number,empList out empcursor);END MYPACKAGE;==============================================CREATE OR REPLACEPACKAGE BODY MYPACKAGE AS  procedure queryEmpList(dno in number,empList out empcursor) AS  BEGIN            open empList for select * from emp where deptno=dno;      END queryEmpList;END MYPACKAGE;------------------------------------------------------------------

2.JAVA程式中測試預存程序/儲存函數

package demo.util;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import oracle.jdbc.OracleCallableStatement;import oracle.jdbc.OracleTypes;import org.junit.Test;/* *  * 效能: * Statement < PreparedStatement  < CallableStatement * PreparedStatement：先行編譯SQL語句。啟動並執行時候告訴參數。至少編輯一次 * CallableStatement：完畢對預存程序/儲存函數的調用，沒有編譯過程，直接調用 */public class TestOracle {/* * create or replaceprocedure queryEmpInfo(eno in number,                                         pename out varchar2,                                         psal   out number,                                         pjob   out varchar2) */@Testpublic void testProcedure(){//調用預存程序//{call <procedure-name>[(<arg1>,<arg2>, ...)]}String sql = "{call queryEmpInfo(?,?,?
,?)}";Connection conn=null;CallableStatement call = null;try{conn = JDBCUtils.getConnection();call = conn.prepareCall(sql);//賦值call.setInt(1, 7839);//對於out參數。申明call.registerOutParameter(2, OracleTypes.VARCHAR);call.registerOutParameter(3, OracleTypes.NUMBER);call.registerOutParameter(4, OracleTypes.VARCHAR);//調用call.execute();//取出結果String name = call.getString(2);double sal  = call.getDouble(3);String job = call.getString(4);System.out.println(name);System.out.println(sal);System.out.println(job);}catch (Exception e) {e.printStackTrace();}finally{JDBCUtils.release(conn, call, null);}}/* * create or replacefunction queryEmpIncome(eno in number)return number */@Testpublic void testFunction(){//儲存函數範例//{?
= call <procedure-name>[(<arg1>,<arg2>, ...)]}String sql = "{?
=call queryEmpIncome(?
)}";Connection conn = null;CallableStatement call = null;try{conn = JDBCUtils.getConnection();call = conn.prepareCall(sql);call.registerOutParameter(1, OracleTypes.NUMBER);call.setInt(2, 7839);//運行call.execute();//取出年度營收double income = call.getDouble(1);System.out.println(income);}catch (Exception e) {e.printStackTrace();}finally{JDBCUtils.release(conn, call, null);}}/* * 問題： * 1. 游標是否被關？:是。結構集關掉後游標就關掉了。
 * 2. 能否在MYSQL上運行？：不能 */@Testpublic void testCursor(){String sql = "{call MYPACKAGE.queryEmpList(?,?
)}";Connection conn = null;CallableStatement call = null;ResultSet rs  =null;try {conn = JDBCUtils.getConnection();call = conn.prepareCall(sql);call.setInt(1, 10);call.registerOutParameter(2, OracleTypes.CURSOR);//運行call.execute();//取出集合rs = ((OracleCallableStatement)call).getCursor(2);while(rs.next()){String name = rs.getString("ename");String job = rs.getString("job");System.out.println(name+"的職位是"+job);}} catch (Exception e) {e.printStackTrace();}finally{JDBCUtils.release(conn, call, rs);}}}

3.用到的JDBC類

package demo.util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class JDBCUtils {private static String driver="oracle.jdbc.OracleDriver";private static String url="jdbc:oracle:thin:@localhost:1521:orcl";private static String user="scott";private static String password="tiger";static{try {Class.forName(driver);} catch (ClassNotFoundException e) {throw new ExceptionInInitializerError(e);}}public static Connection getConnection(){try {return DriverManager.getConnection(url, user, password);} catch (SQLException e) {e.printStackTrace();}return null;}/* * 運行java程式 * java -Xms100M -Xmx200M HelloWorld */public static void release(Connection conn,Statement stmt,ResultSet rs){if(rs!=null){try {rs.close();} catch (SQLException e) {e.printStackTrace();}finally{rs=null;//記憶體回收：能否夠通過代碼幹預記憶體回收？}}if(stmt!=null){try {stmt.close();} catch (SQLException e) {e.printStackTrace();}finally{rs=null;//記憶體回收：能否夠通過代碼幹預記憶體回收？}}if(conn!=null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}finally{rs=null;//記憶體回收：能否夠通過代碼幹預記憶體回收？}}}}

Oracle學習(十二)：預存程序/儲存函數