oracle高级用法之自定义函数

前端之家收集整理的这篇文章主要介绍了oracle高级用法之自定义函数前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

@H_403_1@注意:终端使用dbms_output输出执行结果,首先PL/sql开启服务器输出

@H_403_1@set serveroutput on;


/* 用户@R_502_204@ */



@H_403_1@-- 查看函数、触发器
@H_403_1@select * from user_source;


@H_403_1@-- 删除函数
@H_403_1@drop function fun_getCurDate;


@H_403_1@-- 创建函数

@H_403_1@1. 建立无参数的函数:获取当前日期
@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;

猜你在找的Oracle相关文章