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('