oracle儲存函數

oracle儲存函數

SET SERVEROUTPUT ON;
--//可以沒有參數 ， 必須有return
--//參數和傳回型別不用指明精度
CREATE OR REPLACE FUNCTION hello
(p_name VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
 RETURN p_name || '你好！';
END;
--//作為運算式調用
SELECT hello('ACCP') FROM dual;
--//作為運算式調用
SET SERVEROUTPUT ON;

DECLARE
  l_Str VARCHAR2(100);
BEGIN
 l_Str := hello('ACCP');
 DBMS_OUTPUT.PUT_LINE(l_Str);
END;

--//運輸單據確認。只有已經有名細資訊，和非航空主單費用資訊的單據才可以確認
CREATE OR REPLACE  FUNCTION OT_LB_CONFIRM
  (PV_LBNO OT_LBHEAD.V_LBNO%TYPE,
  PV_LOGINNAME OT_LBHEAD.V_LBNO%TYPE
  )
RETURN INTEGER AS

ln_count integer;

BEGIN
   select count(*) into ln_count from ot_lbdetail where v_lbno=pv_lbno;      --no detail
   if ln_count=0 THEN
   RETURN 0;
   END IF;
   select count(*) into ln_count from ot_lbcost where v_lbno=pv_lbno;        --no cost
   if ln_count=0 and substr(PV_LBNO,0,2)<>'MA' THEN
   RETURN 0;
   END IF;

   UPDATE OT_LBHEAD
      SET V_DESIGNATION=PV_LOGINNAME,
         D_DESIGNATIONDATE=SYSDATE,
        V_STEP=4
  WHERE v_lbno=PV_LBNO;
   COMMIT;
   RETURN 1;
EXCEPTION
 WHEN others THEN
   ROLLBACK;
      sp_ExceptionLog('OT_LB_CONFIRM');
   RETURN -1;
END;
--//==========返回遊標的函數
Create or REPLACE FUNCTION getAge (
    stu_Age IN  VARCHAR2             --接收輸入參數
 )
RETURN   SYS_REFCURSOR
AS
    P_RESULT_SET_O         SYS_REFCURSOR; --返回遊標
    X_SQL VARCHAR2(200);
BEGIN
     X_SQL :='select * FROM stuInfo WHERE stuAge = '''||stu_Age||'''';
     OPEN P_RESULT_SET_O FOR X_SQL;
     RETURN P_RESULT_SET_O;   --返回遊標
END getAge;

--//JDBC調用
CallableStatement cstmt = null;
ResultSet rs = null;
try {
String callSql = "{? = call getAge(?)}";
cstmt = conn.prepareCall(callSql);
   
cstmt.setString(2, "userName");

cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);

cstmt.execute();

rs = (ResultSet) cstmt.getObject(1);
if (rs != null) {
       System.out.print("usercd");
       System.out.print("userName");               
       System.out.println("EMAIL");
       while (rs.next()) {       
    System.out.print(rs.getString(1)+"   ");
    System.out.print(rs.getString(2)+"   ");               
    System.out.println(rs.getString(3));
       }   
}

http://shuanhe1999.blog.163.com/blog/#m=0&t=1&c=fks_095068080084088067080084074071084085087066093081094