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