ORACLE FUNCTION 方法
1、/**
DECLARE
2 STR VARCHAR2(10);
3 BEGIN
4 STR:=SF_TSS_CN0001('8000');
5 DBMS_OUTPUT.put_line(STR);
6 END;
7 /
**/
CREATE OR REPLACE FUNCTION SF_TSS_CN0001
(ENO NUMBER)
RETURN VARCHAR2 AS
V_NAME EMP.ENAME%TYPE; /**定義變量**/
BEGIN
SELECT ENAME INTO V_NAME FROM EMP WHERE EMPNO=ENO; /**賦值給變量**/
RETURN V_NAME; /**返回變量值**/
EXCEPTION WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000,'該僱員不存在');
END;
2、DROP FUNCTION SF_TSS_CN0001;
3、/**out參數
DECLARE
MJOB VARCHAR2(10);
str VARCHAR2(10);
BEGIN
MJOB:=:MJOB;
str:=SF_TSS_CN0003('WHPR',:MJOB);
DMS_OUTPUT.PUT_LINE(str);
END;**/
CREATE OR REPLACE FUNCTION SF_TSS_CN0003
(NAME VARCHAR2,MJOB OUT VARCHAR2)
RETURN VARCHAR2 AS
V_DEPTNAME DEPT.DNAME%TYPE;
BEGIN
SELECT B.DNAME,A.JOB INTO MYJOB,V_DEPTNAME FROM EMP A JOIN DEPT B ON A.DEPTNO=B.DEPTNO WHERE UPPER(A.ENAME)=UPPER(NAME);
RETURN V_DEPTNAME;
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line(-20000,'無');
END;
4、/** DECLARE
RESULT NUMBER;
RESULT2 NUMBER;
BEGIN
RESULT2:=30;
RESULT:=SF_TSS_CN0004(100,:RESULT2);
DBMS_OUTPUT.PUT_LINE(RESULT);
DBMS_OUTPUT.PUT_LIN3(RESULT2);
END;**/
CREATE OR REPLACE FUNCTION SF_TSS_CN0004
(NUM1 NUMBER,NUM2 IN OUT NUMBER)
RETURN NUMBER AS
V1 NUMBER;
V2 NUMBER;
BEGIN
V1:=NUM1/NUM2;
V2:=NUM1-NUM2;
NUM2:=V2;
RETURN V1
END;