@H_403_1@注意：终端使用dbms_output输出执行结果，首先PL/sql开启服务器输出
@H_403_1@set serveroutput on;
@H_403_1@-- 查看函数、触发器
@H_403_1@select * from user_source;
@H_403_1@-- 删除函数
@H_403_1@drop function fun_getCurDate;
@H_403_1@create or replace function fun_getCurDate
@H_403_1@return varchar2
@H_403_1@is
@H_403_1@begin
@H_403_1@ return to_char(sysdate,'yyyy-mm-dd');
@H_403_1@end;
@H_403_1@select fun_getCurDate() from dual;
@H_403_1@BEGIN
@H_403_1@ dbms_output.put_line(fun_getCurDate);
@H_403_1@END;
@H_403_1@2.建立带有输入参数的函数:根据姓名获取工资
@H_403_1@CREATE OR REPLACE FUNCTION fun_getSal(name VARCHAR2)
@H_403_1@RETURN NUMBER
@H_403_1@AS v_sal emp.sal%TYPE;
@H_403_1@BEGIN
@H_403_1@ SELECT sal INTO v_sal FROM emp WHERE UPPER(ename)=UPPER(name);
@H_403_1@ RETURN v_sal;
@H_403_1@END;
@H_403_1@select fun_getSal(name=>'libing') salary from dual;
@H_403_1@3. 建立带有输出参数的函数：
@H_403_1@CREATE OR REPLACE FUNCTION fun_getInfo(eno NUMBER,title OUT VARCHAR2)
@H_403_1@RETURN VARCHAR2
@H_403_1@AS NAME emp.ename%TYPE;
@H_403_1@BEGIN
@H_403_1@ SELECT ename,job INTO name,title FROM emp WHERE empno=eno;
@H_403_1@ RETURN name;
@H_403_1@END;
@H_403_1@4. 建立带有输入输出参数的函数：
@H_403_1@CREATE OR REPLACE FUNCTION fun_getUpdInfo(eno NUMBER,sal_chg IN OUT NUMBER) RETURN VARCHAR2
@H_403_1@AS
@H_403_1@name emp.ename%TYPE;
@H_403_1@BEGIN
@H_403_1@ UPDATE emp SET sal=sal+sal_chg WHERE empno=eno
@H_403_1@ RETURNING ename,sal INTO name,sal_chg;
@H_403_1@ RETURN name;
@H_403_1@END;
@H_403_1@DROP FUNCTION get_upd_info;
@H_403_1@5.建立结果缓存函数
@H_403_1@CREATE OR REPLACE FUNCTION fun_getName(eno VARCHAR2)
@H_403_1@RETURN NUMBER result_cache relies_on(emp)
@H_403_1@AS v_name emp.ename%TYPE;
@H_403_1@BEGIN
@H_403_1@ SELECT ename INTO v_name FROM emp WHERE empno = eno;
@H_403_1@ RETURN v_name;
@H_403_1@END
@H_403_1@6.使用异常处理
@H_403_1@CREATE OR REPLACE FUNCTION get_sal(NAME VARCHAR2)
@H_403_1@RETURN NUMBER
@H_403_1@AS
@H_403_1@v_sal emp.sal%TYPE;
@H_403_1@BEGIN
@H_403_1@ SELECT sal INTO v_sal FROM emp WHERE UPPER(ename)=UPPER(NAME);
@H_403_1@ RETURN v_sal;
@H_403_1@ EXCEPTION
@H_403_1@ WHEN no_data_found THEN
@H_403_1@ raise_application_error(-20005,'该雇员不存在');
@H_403_1@END;
@H_403_1@SELECT get_sal('lily')salary FROM dual;
@H_403_1@7.使用记录类型作为返回类型
@H_403_1@CREATE OR REPLACE FUNCTION get_info(eno NUMBER)
@H_403_1@RETURN emp%ROWTYPE
@H_403_1@IS
@H_403_1@emp_record emp%ROWTYPE;
@H_403_1@BEGIN
@H_403_1@ SELECT * INTO emp_record FROM emp WHERE empno=eno;
@H_403_1@ RETURN emp_record;
@H_403_1@ EXCEPTION
@H_403_1@ WHEN no_data_found THEN
@H_403_1@ raise_application_error(-20006,'该雇员不存在');
@H_403_1@END;
@H_403_1@
@H_403_1@
@H_403_1@8.使用集合类型作为返回类型
@H_403_1@CREATE OR REPLACE TYPE ename_table_type IS TABLE OF VARCHAR2(10);
@H_403_1@CREATE OR REPLACE FUNCTION get_name(dno NUMBER)
@H_403_1@RETURN ename_table_type
@H_403_1@IS ename_table ename_table_type;
@H_403_1@BEGIN
@H_403_1@ SELECT ename BULK COLLECT INTO ename_table FROM emp WHERE deptno = dno;
@H_403_1@ RETURN ename_table;
@H_403_1@ EXCEPTION
@H_403_1@ WHEN no_data_found THEN
@H_403_1@ raise_application_error(-20006,'该部门不存在');
@H_403_1@END;
