:建立无参数的函数CREATE OR REPLACE FUNCTION cur_datetimeRETURN VARCHARISBEGINRETURN TO_CHAR(sysdateYYYY"年"MM"月"DD"日"HH″时"MI"分"SS"秒");END;/ :建立带有输入参数的函数CREATE OR REPLACE FUNCTION get_sal(name VARCHAR)RETURN NUMBERASv_sal empsal%TYPE;BEGINSELECT 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 VARCHARASname empename%TYPE;BEGINSELECT enamejob INTO nametitle FROM empWHERE empno=eno;RETURN name;END;/ :建立带有输入输出参数的函数CREATE OR REPLACE FUNCTION get_upd_info(eno NUMBERsal_chg IN OUT NUMBER) RETURN VARCHARASname empename%TYPE;BEGINUPDATE emp SET sal=sal+sal_chg WHERE empno=enoRETURNING enamesal INTO namesal_chg;RETURN name;END;/ :建立结果缓存函数CREATE OR REPLACE FUNCTION get_name(no VARCHAR)RETURN NUMBER RESULT_CACHE RELIES_ON(emp)ASv_name empename%TYPE;BEGINSELECT ename INTO v_name FROM emp WHERE empno=no;RETURN v_name;END;/ :调用无参数的函数BEGINdbms_outputput_line(cur_datetime);END;/ :调用带有输入参数的函数BEGINdbms_outputput_line(工资:||get_sal(&name));END;/ :调用带有输出参数的函数DECLAREv_name empename%TYPE;v_job empjob%TYPE;BEGINv_name:=get_info(&enov_job);dbms_outputput_line(姓名:||v_name||岗位:||v_job);END;/ :调用带有输入输出参数的函数DECLAREv_empno empempno%TYPE;v_name empename%TYPE;v_salchg empsal%TYPE;BEGINv_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 NUMBEREXEC :salary:=get_sal(name=>&name) :使用组合传递为参数传递变量和数据VAR name VARCHAR()VAR sal_chg NUMBEREXEC :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 NUMBERASv_sal empsal%TYPE;BEGINSELECT sal INTO v_sal FROM empWHERE upper(ename)=upper(name);RETURN v_sal;EXCEPTIONWHEN NO_DATA_FOUND THENRAISE_APPLICATION_ERROR(该雇员不存在);END;/ :使用纪录类型作为返回类型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(该雇员不存在);END;/DECLAREemp_record emp%ROWTYPE;BEGINemp_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 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(该部门不存在);END;/DECLAREename_table ename_table_type;BEGINename_table:=get_name(&dno);FOR i IN ename_tableCOUNT LOOPdbms_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;
