熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle中自定義函數 無參數 輸入參數 輸出參數

2013-11-13 22:25:12  來源: Oracle 

   :建立無參數的函數 CREATE OR REPLACE FUNCTION cur_datetime RETURN VARCHAR IS BEGIN RETURN TO_CHAR(sysdate YYYY"年"MM"月"DD"日"HH″時"MI"分"SS"秒"); END; / :建立帶有輸入參數的函數 CREATE OR REPLACE FUNCTION get_sal(name VARCHAR) RETURN NUMBER AS v_sal empsal%TYPE; BEGIN SELECT sal INTO v_sal FROM emp WHERE upper(ename)=upper(name); RETURN v_sal; END; / :建立帶有輸出參數的函數 CREATE OR REPLACE FUNCTION get_info (eno NUMBERtitle OUT VARCHAR) RETURN VARCHAR AS name empename%TYPE; BEGIN SELECT enamejob INTO nametitle FROM emp WHERE empno=eno; RETURN name; END; / :建立帶有輸入輸出參數的函數 CREATE OR REPLACE FUNCTION get_upd_info (eno NUMBERsal_chg IN OUT NUMBER) RETURN VARCHAR AS name empename%TYPE; BEGIN UPDATE emp SET sal=sal+sal_chg WHERE empno=eno RETURNING enamesal INTO namesal_chg; RETURN name; END; / :建立結果緩存函數 CREATE OR REPLACE FUNCTION get_name(no VARCHAR) RETURN NUMBER RESULT_CACHE RELIES_ON(emp) AS v_name empename%TYPE; BEGIN SELECT ename INTO v_name FROM emp WHERE empno=no; RETURN v_name; END; / :調用無參數的函數 BEGIN dbms_outputput_line(cur_datetime); END; / :調用帶有輸入參數的函數 BEGIN dbms_outputput_line(工資:||get_sal(&name)); END; / :調用帶有輸出參數的函數 DECLARE v_name empename%TYPE; v_job empjob%TYPE; BEGIN v_name:=get_info(&enov_job); dbms_outputput_line(姓名:||v_name||崗位:||v_job); END; / :調用帶有輸入輸出參數的函數 DECLARE v_empno empempno%TYPE; v_name empename%TYPE; v_salchg empsal%TYPE; BEGIN v_empno:=&eno; v_salchg:=&incre; v_name:=get_upd_info(v_empnov_salchg); dbms_outputput_line(姓名:||v_name||新工資:||v_salchg); END; / :使用位置傳遞為參數傳遞變量和數據 SELECT get_sal(&name) 工資 FROM dual; :使用名稱傳遞為參數傳遞變量和數據 VAR salary NUMBER EXEC :salary:=get_sal(name=>&name) :使用組合傳遞為參數傳遞變量和數據 VAR name VARCHAR() VAR sal_chg NUMBER EXEC :sal_chg:= EXEC :name:=get_upd_info(&eno:sal_chg) PRINT name sal_chg :在sql語句中調用pl/sql函數 SELECT get_sal(name=>scott) salary FROM dual; :使用異常處理 CREATE OR REPLACE FUNCTION get_sal(name VARCHAR) RETURN NUMBER AS v_sal empsal%TYPE; BEGIN SELECT sal INTO v_sal FROM emp WHERE upper(ename)=upper(name); RETURN v_sal; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(該雇員不存在); END; / :使用紀錄類型作為返回類型 CREATE OR REPLACE FUNCTION get_info (eno NUMBER) RETURN emp%ROWTYPE IS emp_record emp%ROWTYPE; BEGIN SELECT * INTO emp_record FROM emp WHERE empno=eno; RETURN emp_record; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(該雇員不存在); END; / DECLARE emp_record emp%ROWTYPE; BEGIN emp_record:=get_info(&eno); dbms_outputput_line(姓名:||emp_recordename||部門號:||emp_recorddeptno); END; / :使用集合類型作為返回類型 CREATE OR REPLACE TYPE ename_table_type IS TABLE OF VARCHAR(); / CREATE OR REPLACE FUNCTION get_name (dno NUMBER) RETURN ename_table_type IS ename_table ename_table_type; BEGIN SELECT ename BULK COLLECT INTO ename_table FROM emp WHERE deptno=dno; RETURN ename_table; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(該部門不存在); END; / DECLARE ename_table ename_table_type; BEGIN ename_table:=get_name(&dno); FOR i IN ename_tableCOUNT LOOP dbms_outputput_line(姓名||ename_table(i)); END LOOP; END; / :刪除函數 DROP FUNCTION get_name; :顯示編譯錯誤 SHOW ERRORS :確定函數狀態 SELECT object_name FROM user_objects WHERE status=INVALID AND object_type=FUNCTION; :編譯函數 ALTER FUNCTION get_info COMPILE; :查看函數代碼 SELECT text FROM user_source WHERE name=GET_INFO;


From:http://tw.wingwit.com/Article/program/Oracle/201311/19063.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.