Oracle进阶(八)自定义函数(Function)

1、函数语法
1.1 创建语法
--创建函数
CREATE OR REPLACE FUNCTION 函数名(参数1 模式 参数类型)
  RETURN 返回值类型
AS
  变量1 变量类型;
  变量2 变量类型;
BEGIN
    函数体;
END 函数名;
1.2 其它语法
--删除函数
DROP FUNCTION 函数名;
 
--确定函数状态
SELECT OBJECT_NAME FROM USER_OBJECTS WHERE STATUS='INVALID' AND OBJECT_TYPE='FUNCTION';
 
--编译函数
ALTER FUNCTION 函数名 COMPILE;
 
--查看函数代码
SELECT TEXT FROM USER_SOURCE WHERE NAME='函数名';
2、定义函数
2.1 无参数的函数
--创建函数
CREATE OR REPLACE FUNCTION F_CUR_DATETIME
RETURN VARCHAR2
IS
BEGIN
  RETURN TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日"HH24"时"MI"分"SS"秒"');
END;
 
--调用函数方式1
SELECT F_CUR_DATETIME() FROM DUAL;
 
--调用函数方式2
BEGIN
  DBMS_OUTPUT.PUT_LINE(F_CUR_DATETIME);
END;
2.2 输入参数的函数
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;
END;
 
--调用函数
BEGIN
  DBMS_OUTPUT.PUT_LINE(GET_SAL(NAME => 'SMITH'));
END;
2.3 输出参数的函数
CREATE OR REPLACE FUNCTION GET_NAME(ENO NUMBER,OJOB OUT VARCHAR2) 
RETURN VARCHAR2
AS
  NAME EMP.ENAME%TYPE;
BEGIN
  SELECT ENAME,JOB INTO NAME,OJOB FROM EMP WHERE EMPNO=ENO;
  RETURN NAME;
END;
 
--调用函数
DECLARE
  V_NAME EMP.ENAME%TYPE;
  V_JOB EMP.JOB%TYPE;
BEGIN
  V_NAME:=GET_NAME(ENO=>7900,OJOB => V_JOB);
  DBMS_OUTPUT.PUT_LINE('姓名:'||V_NAME||',工作:'||V_JOB);
END;
2.4 输入输出参数的函数
CREATE OR REPLACE FUNCTION GET_UPD_SAL(ENO NUMBER,UPD_SAL IN OUT NUMBER) 
RETURN VARCHAR2
AS
  NAME EMP.ENAME%TYPE;
BEGIN
  UPDATE EMP SET SAL=SAL+UPD_SAL WHERE EMPNO=ENO
    RETURNING ENAME,SAL INTO NAME,UPD_SAL;
  RETURN NAME;
END;
 
--调用函数
DECLARE
  V_EMPNO EMP.EMPNO%TYPE;
  V_NAME EMP.ENAME%TYPE;
  V_SALUPD EMP.SAL%TYPE;
BEGIN
  V_EMPNO:=7900;
  V_SALUPD:=2021;
  V_NAME:=GET_UPD_SAL(V_EMPNO,V_SALUPD);
  DBMS_OUTPUT.PUT_LINE('姓名:'||V_NAME||',修改后工资:'||V_SALUPD);
END;
2.5 结果缓存的函数
CREATE OR REPLACE FUNCTION GET_NAME_CACHE(ENO VARCHAR2)
RETURN VARCHAR2 
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;
 
--调用函数
BEGIN
  DBMS_OUTPUT.PUT_LINE(GET_NAME_CACHE(ENO =>7900));
END;
2.6 使用异常处理的函数
CREATE OR REPLACE FUNCTION GET_SAL_EXCEPTION(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(-20000,NAME ||'不存在');
END;
 
--调用函数
BEGIN
  DBMS_OUTPUT.PUT_LINE(GET_SAL_EXCEPTION(NAME =>'一二山人'));
END;
2.7 返回行数据类型的函数
CREATE OR REPLACE FUNCTION GET_EMP_ROWTYPE(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(-20000,'编号'||ENO ||'的员工不存在');
END;
 
--调用函数
DECLARE
  RESULT EMP%ROWTYPE;
BEGIN
  RESULT := GET_EMP_ROWTYPE(ENO => 7900);
    DBMS_OUTPUT.PUT_LINE(RESULT.ENAME);
END;
2.8 返回集合类型的函数
CREATE OR REPLACE TYPE ENAME_TABLE_TYPE IS TABLE OF VARCHAR2(10);
/
CREATE OR REPLACE FUNCTION GET_NAME_TABLE(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(-20099,'编号'||DNO ||'的部门不存在');
END;
 
--调用函数方式1
SELECT * FROM  TABLE(GET_NAME_TABLE(DNO => 30));
 
--调用函数方式2
DECLARE
  ENAME_TABLE ENAME_TABLE_TYPE;
BEGIN
  ENAME_TABLE:=GET_NAME_TABLE(DNO => 20);
  FOR I IN 1..ENAME_TABLE.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('姓名:'||ENAME_TABLE(I));
  END LOOP;
END;
3、返回结果集的方式
3.1 以游标形式返回结果集
--创建函数:通过员工姓名获取记录行
CREATE OR REPLACE FUNCTION F_GETEMPNO(PNAME IN VARCHAR2)
  RETURN SYS_REFCURSOR 
  IS
    EMPNO SYS_REFCURSOR;
  BEGIN
    OPEN EMPNO FOR
      SELECT EMPNO,ENAME FROM EMP WHERE ENAME=PNAME;
    RETURN(EMPNO);
END F_GETEMPNO;
 
--调用函数
SELECT F_GETEMPNO (PNAME => 'SMITH') FROM DUAL;
3.2 以Table形式返回结果集
--返回table类型的结果集:
 
--定义一个行类型
CREATE OR REPLACE TYPE "EMP_ROW_TYPE"  AS OBJECT(EMP_NO NUMBER(4),E_NAME varchar2(10))
 
--定义一个表类型
CREATE OR REPLACE TYPE "EMP_TAB_TYPE" AS TABLE of EMP_ROW_TYPE
 
--创建函数
CREATE OR REPLACE FUNCTION F_GETTAB_TAB(PNAME IN VARCHAR2)
  RETURN EMP_TAB_TYPE 
  IS
    EMP_ROW     EMP_ROW_TYPE;                       -- 定义单行
    EMP_TAB   EMP_TAB_TYPE := EMP_TAB_TYPE();   -- 定义返回结果,并初始化
  BEGIN
    FOR CURROW IN (SELECT EMPNO AS EMP_NO,ENAME AS E_NAME FROM EMP WHERE ENAME=PNAME) 
    LOOP
      EMP_ROW := EMP_ROW_TYPE(CURROW.EMP_NO, CURROW.E_NAME);
      EMP_TAB.EXTEND;
      EMP_TAB(EMP_TAB.COUNT) := EMP_ROW;
    END LOOP;
  RETURN(EMP_TAB);
END F_GETTAB_TAB;
 
--调用函数
SELECT * FROM  TABLE(F_GETTAB_TAB(PNAME => 'SMITH'));
3.3 以管道形式返回结果集
--定义一个行类型
CREATE OR REPLACE TYPE "DATA_ROW"  AS OBJECT(EMP_NO NUMBER(4),E_NAME varchar2(10))
 
--定义一个表类型
CREATE OR REPLACE TYPE "DATA_TABLE" AS TABLE of DATA_ROW
 
--创建函数
CREATE OR REPLACE FUNCTION F_GETEMP_PIPE(PNAME IN VARCHAR2)
  RETURN DATA_TABLE PIPELINED
  IS
    EMP_ROW DATA_ROW;         --定义DATA_ROW为行对象类型
  BEGIN
    FOR CURROW IN (SELECT EMPNO AS EMP_NO,ENAME AS E_NAME FROM EMP WHERE ENAME=PNAME)
    LOOP
      EMP_ROW:= DATA_ROW(CURROW.EMP_NO, CURROW.E_NAME);
      PIPE ROW (EMP_ROW);
    END LOOP;
  RETURN;
END F_GETEMP_PIPE;
 
--调用函数
SELECT * FROM  TABLE(F_GETEMP_PIPE(PNAME => 'SMITH'));
4、常见案例
4.1 入门案例
--1 创建函数,从EMP表中查询指定员工编号的职工的工资
 
 CREATE OR REPLACE FUNCTION F_CHECK_SAL(F_EMPNO IN EMP.EMPNO%TYPE) RETURN NUMBER IS
 V_SAL VARCHAR(50);
 
 BEGIN
     SELECT SAL INTO V_SAL FROM EMP WHERE EMPNO = F_EMPNO;  --- 直接调用隐式游标
     RETURN V_SAL;
 
    EXCEPTION WHEN NO_DATA_FOUND THEN --- 异常值处理
    V_SAL:='没找到任何数据';
    RETURN V_SAL;
 END;
 
--调用函数
SELECT F_CHECK_SAL(7900) AS 工资 FROM DUAL;
 
--2 创建函数,返回EMP表中指定职工的工资和姓名
 
CREATE OR REPLACE FUNCTION F_EMP_SALENAME(FEMPNO IN EMP.EMPNO%TYPE,V_ENAME OUT EMP.ENAME%TYPE)RETURN NUMBER IS
V_SAL EMP.SAL%TYPE;
BEGIN
   SELECT SAL,ENAME INTO V_SAL,V_ENAME
   FROM EMP
   WHERE EMPNO = FEMPNO;
   RETURN V_SAL;
END;
 
--调用函数
 DECLARE
 P_ENAME EMP.ENAME%TYPE;
 p_SAL EMP.SAL%TYPE;
 BEGIN
    P_SAL:=F_EMP_SALENAME(7369,P_ENAME); 
    DBMS_OUTPUT.put_line('员工'||P_ENAME||'工资'||P_SAL);
 END;
 
--3 创建函数,根据给定的部门编号(提示: 利用&)计算该部门所有职工的平均工资
 
CREATE OR REPLACE FUNCTION F_INPUTEMPNO(F_DEPTNO IN DEPT.DEPTNO%TYPE)
RETURN NUMBER IS
V_SAL NUMBER;
BEGIN 
   SELECT AVG(SAL) INTO V_SAL FROM EMP WHERE DEPTNO = F_DEPTNO; 
   RETURN V_SAL;
END;
 
--调用时候,在输入名称前加入&,类似于VBA里的Input函数
 SELECT  F_INPUTEMPNO(&部门编号) AS 平均工资 FROM DUAL;
 
--4 创建一个函数,仅有一个形参,它接收调用函数中传递过来的实参:部门号,函数的返回值为该部门的一整条记录信息
 
CREATE OR REPLACE FUNCTION F_EMPINFO(F_DEPTNO IN DEPT.DEPTNO%TYPE)RETURN DEPT%ROWTYPE IS
    V_DEPT DEPT%ROWTYPE;
  BEGIN
    SELECT * INTO V_DEPT FROM DEPT WHERE DEPTNO = F_DEPTNO;
    RETURN V_DEPT;
  END;
  
--调用
  DECLARE 
  V_DEPT DEPT%ROWTYPE;
  BEGIN
  V_DEPT := F_EMPINFO(20);
  DBMS_OUTPUT.PUT_LINE('部门名称: '||V_DEPT.DNAME || '  部门位置: ' || V_DEPT.LOC);
  END;
 
--5 创建函数,将emp表中工资低于平均工资的职工工资加上200,并返回修改了工资的总人数
 
CREATE OR REPLACE FUNCTION F_MODIFY 
RETURN NUMBER 
IS
BEGIN
 UPDATE EMP SET SAL=SAL+200 WHERE SAL<(SELECT AVG(SAL) FROM EMP);
 RETURN SQL%ROWCOUNT; 
 
/* sql%rowcount用于记录修改的条数,必须放在一个更新或者删除等修改类语句后面执行,
   select语句用于查询的话无法使用,
   当你执行多条修改语句时,按照sql%rowcount 之前执行的最后一条语句修改数为准。*/
END;
/
--调用
BEGIN
  DBMS_OUTPUT.PUT_LINE(F_MODIFY);
END; 
/
4.2 中级案例
--1、输入2个整数,返回最小到最大数之间的连乘的结果(两个整数在1到20之间)
 
CREATE OR REPLACE FUNCTION F_B(P1 IN INT, P2 IN INT) RETURN INTEGER IS 
  V_RESULT INTEGER := 1;
    
BEGIN
 
 IF (P1 > 1 AND P1 < 20) AND (P2> 1 AND P2 < 20) THEN
    IF P2 > P1 THEN
      FOR I IN P1 .. P2 LOOP
        V_RESULT := V_RESULT * I;
      END LOOP;
    ELSIF P2 < P1 THEN
      FOR I IN P2 .. P1 LOOP
        V_RESULT := V_RESULT * I;
      END LOOP;
    ELSE
      V_RESULT := P1 * P2;
    END IF;
  ELSE 
  DBMS_OUTPUT.put_line('请输入正确的范围值1-20');
END IF; 
  RETURN V_RESULT;
END;
 
--调用
SELECT F_B(3,7) FROM DUAL;
 
--2 输入3个整数,取中位数
 
CREATE OR REPLACE FUNCTION F_A(P1 IN INT,P2 IN INT,P3 IN INT)
  RETURN INTEGER IS  
  V_RESULT INTEGER;
  
  BEGIN    
  SELECT CASE WHEN P1 >= P2 AND P1 <= P3 THEN  P1
              WHEN P2 >= P1 AND P2 <= P3 THEN  P2
              WHEN P3 >= P1 AND P3 <= P2 THEN  P3 END
  INTO V_RESULT FROM DUAL;
  RETURN V_RESULT;
  
  END;
    
--调用
SELECT F_A(3,7,5) FROM DUAL;
        
--方法二  
CREATE OR REPLACE FUNCTION F_MIDDLE(X1 IN NUMBER,X2 IN NUMBER,X3 IN NUMBER)
     RETURN NUMBER
    IS 
    BEGIN
    IF (X1-X2)*(X1-X3)<=0   THEN RETURN X1;
    ELSIF (X2-X1)*(X2-X3)<=0 THEN RETURN X2;
    ELSIF(X3-X1)*(X3-X2)<=0 THEN RETURN X3;
    END IF;
    END;
        
--调用
SELECT F_MIDDLE(3,7,5) FROM DUAL;
 
--3 对比两个数,返回一个最大值。可以在设置参数时,顺带设置默认值
 
CREATE OR REPLACE FUNCTION FUN_MAX (P_NUM1 IN NUMBER, P_NUM2 IN NUMBER DEFAULT 99)
  RETURN NUMBER
  IS
  BEGIN
    IF P_NUM1>P_NUM2 THEN 
      RETURN P_NUM1;  
    ELSE 
      RETURN P_NUM2; 
    END IF;
  END;
 
--调用
SELECT FUN_MAX(5,4) FROM DUAL;
SELECT FUN_MAX(5) FROM DUAL;
4.3 进阶案例
--传入部门编号,返回整个部门的员工信息
 
CREATE OR REPLACE FUNCTION FUN_REF(P_DEPTNO EMP.DEPTNO%TYPE)
      RETURN SYS_REFCURSOR IS
      C_EMP SYS_REFCURSOR;
    BEGIN
      OPEN C_EMP FOR
        SELECT * FROM EMP WHERE DEPTNO = P_DEPTNO;
      RETURN C_EMP;
    END FUN_REF;
 
 --调用1
SELECT FUN_REF(30) FROM DUAL;
 
 --调用2
DECLARE
V_ROWS SYS_REFCURSOR;
V_ROW EMP%ROWTYPE;
BEGIN
V_ROWS:=FUN_REF(30);
LOOP
FETCH V_ROWS INTO V_ROW;
EXIT WHEN V_ROWS%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_ROW.EMPNO||''||V_ROW.ENAME||''||V_ROW.JOB||''||V_ROW.SAL);
END LOOP;
CLOSE V_ROWS;
END;
附表
文中所涉及的表,请查看Oracle进阶(三)常用函数的附表。
————————————————
版权声明:本文为CSDN博主「一二山人」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq_31652795/article/details/116381604

  • 1
    点赞
  • 25
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值