前提

在项目中经常会使用到存储过程,来优化sql语句,比如获取两个时间差的日期等,减少sql的复杂性

创建语法

sql

--创建函数
CREATE OR REPLACE FUNCTION 函数名(参数1 模式 参数类型)
  RETURN 返回值类型
AS
  变量1 变量类型;
  变量2 变量类型;
BEGIN
    函数体;
END 函数名;

其他语法

sql
--删除函数
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='函数名';

无参

sql
--创建函数
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;

有入参

sql
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;

有出参

sql
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;

引用自

实际使用

sql

create or replace function GET_OVERDUE_DAYS(patientId in varchar2,
                                            visitNo   in varchar2)
  return number is
  VD_OVERDUE_DAYS number;
  VD_DISCHARGE_TIME DATE;
  VD_RECEIVE_TIME DATE;
  VD_EXSIT_REC number;
begin
  --获取逾期天数
  select t.discharge_date_time INTO VD_DISCHARGE_TIME from pat_visit t where t.patient_id= patientId and t.visit_id = visitNo;
  select count(1) into VD_EXSIT_REC from rec_mr_index t where t.patient_id=patientId and t.visit_no = visitNo;
  if VD_EXSIT_REC >0 then
    select t.receive_time into VD_RECEIVE_TIME from rec_mr_index t where t.patient_id=patientId and t.visit_no = visitNo;
    if VD_RECEIVE_TIME is null then
      VD_RECEIVE_TIME:=sysdate;
    end if;
  else
    VD_RECEIVE_TIME:=sysdate;
  end if;
  

  SELECT FLOOR(VD_RECEIVE_TIME - VD_DISCHARGE_TIME) - 7 INTO VD_OVERDUE_DAYS  FROM DUAL;
  if VD_OVERDUE_DAYS < 0 then
    VD_OVERDUE_DAYS:= 0;
  end if;
  return(VD_OVERDUE_DAYS);
end GET_OVERDUE_DAYS;