1、/**DECLARE 2 STR VARCHAR2(10); 3 BEGIN 4 STR:=SF娄多骋能_TSS_CN0001('8000'); 5 DBMS_OUTPUT.put_l足毂忍珩ine(STR); 6 END; 7 /**/CREATE OR REPLACE FUNCTION SF_TSS_CN0001(ENO NUMBER)RETURN VARCHAR2 ASV_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 ASV_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 ASV1 NUMBER;V2 NUMBER;BEGIN V1:=NUM1/NUM2; V2:=NUM1-NUM2; NUM2:=V2; RETURN V1END;