oracle高级用法之自定义函数

发布时间：2020-12-12 14:35:33 所属栏目：百科来源：网络整理

导读：注意：终端使用 dbms_output 输出执行结果，首先 PL/SQL 开启服务器输出 set serveroutput on; /* 用户自定义函数 */ -- 查看函数、触发器 select * from user_source; -- 删除函数 drop function fun_getCurDate; -- 创建函数 1. 建立无参数的函数:获取当前

注意：终端使用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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!