Oracle中自訂函數 無參數 輸入參數 輸出參數等各種用法

-- 19-1:建立無參數的函數CREATE OR REPLACE FUNCTION cur_datetimeRETURN VARCHAR2ISBEGINRETURN TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"HH24″時"MI"分"SS"秒"');END;/-- 19-2:建立帶有輸入參數的函數CREATE OR REPLACE FUNCTION get_sal(name VARCHAR2)RETURN NUMBERASv_sal emp.sal%TYPE;BEGINSELECT sal INTO v_sal FROM emp WHERE upper(ename)=upper(name);RETURN v_sal;END;/-- 19-3:建立帶有輸出參數的函數CREATE OR REPLACE FUNCTION get_info(eno NUMBER,title OUT VARCHAR2) RETURN VARCHAR2ASname emp.ename%TYPE;BEGINSELECT ename,job INTO name,title FROM empWHERE empno=eno;RETURN name;END;/-- 19-4:建立帶有輸入輸出參數的函數CREATE OR REPLACE FUNCTION get_upd_info(eno NUMBER,sal_chg IN OUT NUMBER) RETURN VARCHAR2ASname emp.ename%TYPE;BEGINUPDATE emp SET sal=sal+sal_chg WHERE empno=enoRETURNING ename,sal INTO name,sal_chg;RETURN name;END;/-- 19-5:建立結果緩衝函數CREATE OR REPLACE FUNCTION get_name(no VARCHAR2)RETURN NUMBER RESULT_CACHE RELIES_ON(emp)ASv_name emp.ename%TYPE;BEGINSELECT ename INTO v_name FROM emp WHERE empno=no;RETURN v_name;END;/-- 19-6:調用無參數的函數BEGINdbms_output.put_line(cur_datetime);END;/-- 19-7:調用帶有輸入參數的函數BEGINdbms_output.put_line('工資:'||get_sal('&name'));END;/-- 19-8:調用帶有輸出參數的函數DECLAREv_name emp.ename%TYPE;v_job emp.job%TYPE;BEGINv_name:=get_info(&eno,v_job);dbms_output.put_line('姓名:'||v_name||',崗位:'||v_job);END;/-- 19-9:調用帶有輸入輸出參數的函數DECLAREv_empno emp.empno%TYPE;v_name emp.ename%TYPE;v_salchg emp.sal%TYPE;BEGINv_empno:=&eno;v_salchg:=&incre;v_name:=get_upd_info(v_empno,v_salchg);dbms_output.put_line('姓名:'||v_name||',新工資:'||v_salchg);END;/-- 19-10:使用位置傳遞為參數傳遞變數和資料SELECT get_sal('&name') 工資 FROM dual;-- 19-11:使用名稱傳遞為參數傳遞變數和資料VAR salary NUMBEREXEC :salary:=get_sal(name=>'&name')-- 19-12:使用組合傳遞為參數傳遞變數和資料VAR name VARCHAR2(10)VAR sal_chg NUMBEREXEC :sal_chg:=200EXEC :name:=get_upd_info(&eno,:sal_chg)PRINT name sal_chg-- 19-13:在sql語句中調用pl/sql函數SELECT get_sal(name=>'scott') salary FROM dual;-- 19-14:使用異常處理CREATE OR REPLACE FUNCTION get_sal(name VARCHAR2)RETURN NUMBERASv_sal emp.sal%TYPE;BEGINSELECT sal INTO v_sal FROM empWHERE upper(ename)=upper(name);RETURN v_sal;EXCEPTIONWHEN NO_DATA_FOUND THENRAISE_APPLICATION_ERROR(-20000,'該僱員不存在');END;/-- 19-15:使用紀錄類型作為傳回型別CREATE OR REPLACE FUNCTION get_info(eno NUMBER) RETURN emp%ROWTYPEISemp_record emp%ROWTYPE;BEGINSELECT * INTO emp_record FROM emp WHERE empno=eno;RETURN emp_record;EXCEPTIONWHEN NO_DATA_FOUND THENRAISE_APPLICATION_ERROR(-20000,'該僱員不存在');END;/DECLAREemp_record emp%ROWTYPE;BEGINemp_record:=get_info(&eno);dbms_output.put_line('姓名:'||emp_record.ename||',部門號:'||emp_record.deptno);END;/-- 19-16:使用集合類型作為傳回型別CREATE OR REPLACE TYPE ename_table_type IS TABLE OF VARCHAR2(10);/CREATE OR REPLACE FUNCTION get_name(dno NUMBER) RETURN ename_table_type ISename_table ename_table_type;BEGINSELECT ename BULK COLLECT INTO ename_table FROM emp WHERE deptno=dno;RETURN ename_table;EXCEPTIONWHEN NO_DATA_FOUND THENRAISE_APPLICATION_ERROR(-20099,'該部門不存在');END;/DECLAREename_table ename_table_type;BEGINename_table:=get_name(&dno);FOR i IN 1..ename_table.COUNT LOOPdbms_output.put_line('姓名：'||ename_table(i));END LOOP;END;/-- 19-17:刪除函數DROP FUNCTION get_name;-- 19-18:顯示編譯錯誤SHOW ERRORS-- 19-19:確定函數狀態SELECT object_name FROM user_objects WHERE status='INVALID' AND object_type='FUNCTION';-- 19-20:編譯函數ALTER FUNCTION get_info COMPILE;-- 19-21:查看函數代碼SELECT text FROM user_source WHERE name='GET_INFO';