首页 > 代码库 > oracle函数
oracle函数
函数（存储函数）也是一种较为方便的存储结构，用户定义函数可以被SQL语句或者PL/SQL直接调，函数和过程最大的区别在于，函数可以有返回值，
而过程只能依靠OUT 或者IN OUT返回数据
定义函数语法：
CREATE [OR REPLACE] FUNCTION 函数([参数，...]])
RETURN 返回值类型
[AUTHID [DEFINER | CURRENT_USER]]
AS || IS
[PRAGMA AUTONOMOUS_TRANSACTION;]
声明部分;
BEGIN
程序部分;
[RETURN 返回值;]
EXCEPTION
导常处理;
END [函数名];
参数中定义参数模式表示过程的数据的接收操作，一般分为IN,OUT,IN OUT 3类
CREATE [OR REPLACE]：表示创建或者替换过程，如果过程存在则替换，如果不存在就创建一个新的
AUTHID子句定义了一个过程的所有者权限，DEFINER（默认）表示定义者权限执行，或者用CURRENT_USER覆盖程序的默认行为，变为使用者权限
PRAGMA AUTONOMOUS_TRANSACTION:表示过程启动一个自治事务，自治事务可以让主事挂起，在过程中执行完SQL后，由用户处理提交或者回滚自治事务，
然后恢复主事务
和过程的语法基本相似，唯一不同的是在定义函数时候需要有返回值类型(RETURN 返回值类型)声明
定义一个函数返回系统时间
CREATE OR REPLACE FUNCTION datetime_funRETURN VARCHAR2ASBEGIN RETURN to_char(SYSDATE,‘yyyy-mm-dd hh24:mi:ss‘);END;--调用DECLAREBEGIN dbms_output.put_line(datetime_fun);END;
带出输入和输出的函数create or replace function getinfo_fun(eno emp.empno%type, job out emp.job%type) return varchar2 is v_name emp.ename%TYPE; v_count NUMBER;BEGIN SELECT COUNT(eno) INTO v_count FROM emp WHERE empno=eno; IF v_count>0 THEN SELECT ename,job INTO v_name,job FROM emp WHERE empno=eno; END IF; RETURN v_name;end getinfo_fun;--调用DECLARE v_id emp.empno%TYPE:=&empno; v_name emp.ename%TYPE; v_job emp.job%TYPE;BEGIN v_name:=getinfo_fun(v_id,v_job); dbms_output.put_line(‘员工编号是:‘||v_id||‘ 姓名:‘||v_name||‘ 职位:‘||v_job);END;
示例一、定义函数通过员工编号来查询员工的工资
CREATE OR REPLACE FUNCTION GET_SAL_FUN(F_NO EMP.EMPNO%TYPE) RETURN NUMBER AS V_SAL EMP.SAL%TYPE;BEGIN SELECT SAL INTO V_SAL FROM EMP WHERE EMPNO = F_NO; RETURN V_SAL;END;--调用DECLARE v_id emp.empno%TYPE:=&empno; v_sal emp.sal%TYPE; BEGIN v_sal:=get_sal_fun(v_id); dbms_output.put_line(‘员工编号：‘||v_id||‘ 的工资为: ‘||v_sal); END;
第二种
-使用过程来调用CREATE OR REPLACE PROCEDURE invoke_procASv_id emp.empno%TYPE:=&empno;v_sal emp.sal%TYPE;BEGIN v_sal:=get_sal_fun(v_id); dbms_output.put_line(‘员工编号：‘||v_id||‘ 的工资为: ‘||v_sal);END;EXEC invoke_proc ;
第三种
使用sql语句来调用 SELECT get_sal_fun(&v_id) FROM dual;
参数模式
IN模式
示例一、定义函数使用IN
CREATE OR REPLACE FUNCTION in_fun( f_b VARCHAR2 DEFAULT ‘Java开发实战经典‘, --默认的参数模式为in f_a IN VARCHAR2 DEFAULT ‘好好学习‘ --明确定义IN参数模式) RETURN VARCHAR2ASBEGIN RETURN f_a;END;执行DECLARE v_a VARCHAR2(50); v_b VARCHAR2(50);BEGIN v_b:=in_fun(v_a); dbms_output.put_line(v_b); dbms_output.put_line(SQLERRM);END;结果:好好学习ORA-0000: normal, successful completion
OUT模式
示例二、定义函数使用OUT
CREATE OR REPLACE FUNCTION out_fun( f_a OUT Varchar2, f_b OUT VARCHAR2)RETURN VARCHAR2ASBEGIN f_a:=‘Java开发实战经典‘; f_b:=‘Oracle开发实战经典‘; RETURN f_b;END;--调用DECLARE v_a VARCHAR2(100); v_b VARCHAR2(100); v_result VARCHAR2(100);BEGIN v_result:=out_fun(v_a,v_b); dbms_output.put_line(v_result); dbms_output.put_line(SQLERRM);END;
示例三、通过函数完成部门增加
CREATE OR REPLACE FUNCTION dept_inser_fun( f_dno dept.deptno%TYPE, f_dname dept.dname%TYPE, f_loc dept.loc%TYPE)RETURN NUMBERAS v_count NUMBER;BEGIN SELECT COUNT(deptno) INTO v_count FROM dept WHERE deptno=f_dno; IF v_count>0 THEN RETURN -1; --返回失败 ELSE INSERT INTO dept(deptno,dname,loc)VALUES(f_dno,f_dname,f_loc); COMMIT; RETURN 0; END IF;END;--调用DECLARE v_result NUMBER;BEGIN v_result:=dept_inser_fun(&deptno,‘&dname‘,‘&loc‘); IF V_RESULT = 0 THEN DBMS_OUTPUT.PUT_LINE(‘新部门增加成功‘); ELSE DBMS_OUTPUT.PUT_LINE(‘新部门增加失败‘); END IF;END; VAR v_sal NUMBER; CALL get_sal_fun(&v_id) INTO v-sal; print v_sal;
示例四、函数根所员工编号，输出姓名，返回工资
--定义函数根所员工编号，输出姓名，返回工资CREATE OR REPLACE FUNCTION get_sal(eno NUMBER,eme OUT VARCHAR2) RETURN NUMBERISv_sal emp.sal%TYPE;BEGIN SELECT sal,ename INTO v_sal,eme FROM emp WHERE empno=eno; RETURN v_sal; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(‘没有这个员工‘);END;--调用DECLARE v_eno emp.empno%TYPE:=&empno; v_ename emp.ename%TYPE; v_sal emp.sal%TYPE;BEGIN v_sal:=get_sal(v_eno,v_ename); dbms_output.put_line(‘ 编号:‘||v_eno||‘ 姓名:‘||v_ename||‘ 工资:‘||v_sal); END;
oracle函数
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。