侧边栏壁纸
  • 累计撰写 79 篇文章
  • 累计创建 7 个标签
  • 累计收到 0 条评论

Oracle函数

水龙吟
2021-06-18 / 0 评论 / 0 点赞 / 160 阅读 / 13,032 字
温馨提示:
本文最后更新于 2021-12-27,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

1、分组后取每组前N行数据

SELECT * FROM (
    SELECT M.ID,M.NUM,M.INST_NO,ROW_NUMBER() OVER(PARTITION BY M.INST_NO ORDER BY M.NUM desc) E
    FROM MK_MON_EN M)
T WHERE T.E <= 2

语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

​ 从1开始,为每一条分组记录返回一个数字,相当于ROW_NUM一样的东西,之后再取最小的几个,

​ ORDER BY是先把列进行排序,排序之后为每条记录返回一个序号。

(2)PERCENT_RANK

语法:PERCENT_RANK(expr1,expr2…)WITHIN GROUP (ORDER BY expr1,expr2…)

功能:该函数用于返回特定数值在统计级别中所占的比例。

(3)nvl2(A1, A2, A3):如果A1不为空,则取A2的值,如果为空,则取A3的值

(4)order by DYIQ.DJSJ desc nulls last
根据djsj倒序排序,并且将空值放在后面

(5)拼接后去重:regexp_replace((listagg ( CASE WHEN a.ryfl IN ( '1', '2' ) THEN a.rymc END, ',' ) within GROUP ( ORDER BY a.ywh ) ),'([^,]+)(,\1)*(,|$)', '\1\3')

2、自定义函数

(1)计算工作日(ESTATE_EXT_WORKDAY记录每一年工作日和非工作日的表)

CREATE OR REPLACE
function GET_WORK_DAY(
  i_startTime date, -- 起始时间:( 格式:'YYYY-MM-DD HH24:MI:SS' )
  i_endTime date    -- 结束时间:( 格式:'YYYY-MM-DD HH24:MI:SS' )
)
return number
as
  v_real_startTime date;--开始时间 变量
  v_real_endTime date;--结束时间 变量
  v_hours number(18,0);--计算结果
  v_number number(18,4);
begin
  v_real_startTime := i_startTime;
  v_real_endTime := i_endTime;
--   if v_real_startTime > v_real_endTime -- 如果起始时间大于结束时间,将起始时间与结束时间替换
--   then
--     select v_real_startTime, v_real_endTime into v_real_endTime, v_real_startTime from dual;
--   end if;

  if v_real_startTime < trunc(v_real_startTime,'dd')+9/24 -- 如果小于当天9点,将其置为当天9点(因为你是9点上班)
  then
    v_real_startTime:=trunc(v_real_startTime,'dd')+9/24;

-- 如果大于当天17点,将其置为第二天9点
  elsif v_real_startTime>trunc(v_real_startTime,'dd')+17/24
  then
    v_real_startTime:=trunc(v_real_startTime+1,'dd')+9/24;
  end if;

-- 如果小于当天9点,将其置为昨天17点
  if v_real_endTime<trunc(v_real_endTime,'dd')+9/24
  then
    v_real_endTime:=trunc(v_real_endTime-1,'dd')+17/24;

-- 如果大于当天17点,将其置为当天17点(因为你是17点下班)
  elsif v_real_endTime > trunc(v_real_endTime,'dd')+17/24
  then
    v_real_endTime:=trunc(v_real_endTime,'dd')+17/24;
  end if;
  with a as(select v_real_startTime+(level-1)/24 as cdate,
            trunc(v_real_startTime+(level-1)/24,'hh24') as tr_cdate from dual
            connect by level <= (v_real_endTime-v_real_startTime)*24+2 ),
       b as( select cdate, tr_cdate cdate2 from a where trunc(a.cdate) not in (select to_date(DATEYEAR, 'yyyy-MM-dd')
       from ESTATE_EXT_WORKDAY where ISREST = '1') ),
       -- 排除非工作日
       -- 对b中时间点进行转换  主要以工作时间09——17来进行
       c as( select (case when to_char(t1.cdate,'hh24') in ('17') then trunc(t1.cdate,'hh24') else t1.cdate end) as cdate1, -- 超过17点的按17点
                     (case when to_char(t1.cdate2,'hh24') in ('09') then trunc(t2.cdate+1/24,'hh24') else t2.cdate end) as cdate2 -- 超过9点的按9点
             from b t1
             left join b t2 on trunc(t1.cdate,'hh24')=trunc(t2.cdate+1/24,'hh24')
            order by (case when to_char(t1.cdate,'hh24') in ('09','17') then trunc(t1.cdate,'hh24') else t1.cdate end) ),
       d as ( select (case when c.cdate1>v_real_endTime then v_real_endTime
                           else c.cdate1 end) as cdate1,
                      c.cdate2
                from c
               where to_char(c.cdate1,'hh24') in ('09','10','11','12','13','14','15','16','17') )
      --*24为时间进度计算到小时
      select nvl(sum(d.cdate1-nvl(d.cdate2,d.cdate1))*24,0) into v_number from d;

  return v_number;
end GET_WORK_DAY;

(2)计算截止日期(ESTATE_EXT_WORKDAY记录每一年工作日和非工作日的表)

​ 不加班:

CREATE OR REPLACE
FUNCTION GET_DEADLINE(start_time date, process_time number)  -- 开始时间,办理时长 (计算截止日期)
RETURN date
AS
  v_start_time date;    -- 开始时间
  v_process_time number;  --处理时限
  deadline date;  -- 截止日期
  n_temp_day number;  -- 计算工作天数
  n_lastWorkDay number;   -- 开始日期到截止日期的天数
  v_isrest varchar2(5); -- 是否是休息日,1表示是,0表示否
  v_count number;
BEGIN
  v_start_time := start_time;
  v_process_time := process_time;
  n_temp_day := 0;
  n_lastWorkDay := 0;
  v_count := 0;

  select count(1) into v_count from ESTATE_EXT_WORKDAY;
  if (v_count=0)
  then
    deadline := v_start_time + v_process_time;
  else
    -- 判断当天是休息日还是工作日,如果是休息日,则置为下一个工作日(应该不需要这一步)
    select isrest into v_isrest from ESTATE_EXT_WORKDAY where DATEYEAR = to_char(v_start_time, 'yyyy-MM-dd');
    if (v_isrest = '1')
    then
      while (v_isrest = '1') loop
        select isrest into v_isrest from ESTATE_EXT_WORKDAY where DATEYEAR = to_char(v_start_time+1, 'yyyy-MM-dd');
        if (v_isrest ='0')  --如果是工作日。则置为当天早上9点
        then
            v_start_time := trunc(v_start_time+1,'dd')+9/24;
        else
          v_start_time := v_start_time + 1;
        end if;
      end loop;
    end if;

    if v_start_time >= trunc(v_start_time,'dd')+17/24   -- 如果大于等于当天晚上5点。则开始时间为第二天9点
    then
      v_start_time := trunc(v_start_time+1,'dd')+9/24;
    elsif (v_start_time < trunc(v_start_time,'dd')+9/24)  --如果小于当天9点,则置为当天早上9点
    then
      v_start_time := trunc(v_start_time,'dd')+9/24;
    end if;

    while (n_temp_day < v_process_time) loop    --循环判断中间有多少个休息日
      n_lastWorkDay := n_lastWorkDay + 1;
      select isrest into v_isrest from ESTATE_EXT_WORKDAY where DATEYEAR = to_char(v_start_time + n_lastWorkDay, 'yyyy-MM-dd');
      if (v_isrest = '0')
      then
        n_temp_day := n_temp_day + 1;
      end if;
    end loop;

    deadline := v_start_time + n_lastWorkDay;

    if deadline <= trunc(deadline,'dd')+9/24
    then
      v_isrest := '1';
      while (v_isrest = '1') loop
        deadline := deadline-1;
        select isrest into v_isrest from ESTATE_EXT_WORKDAY where DATEYEAR = to_char(deadline, 'yyyy-MM-dd');
      end loop;
      deadline := trunc(deadline,'dd')+17/24;
    end if;
  end if;

  RETURN deadline;
END GET_DEADLINE;

​ 截止日期为第二天早上九点(用于加班)

CREATE OR REPLACE
FUNCTION GET_DEADLINE(start_time date, process_time number)  -- 开始时间,办理时长 (计算截止日期)
RETURN date
AS
  v_start_time date;    -- 开始时间
  v_process_time number;  --处理时限
  deadline date;  -- 截止日期
  n_temp_day number;  -- 计算工作天数
  n_lastWorkDay number;   -- 开始日期到截止日期的天数
  v_isrest varchar2(5); -- 是否是休息日,1表示是,0表示否
  v_count number;
BEGIN
  v_start_time := start_time;
  v_process_time := process_time;
  n_temp_day := 0;
  n_lastWorkDay := 0;
  v_count := 0;

  select count(1) into v_count from ESTATE_EXT_WORKDAY;
  if (v_count=0)
  then
    deadline := v_start_time + v_process_time;
  else
    -- 判断当天是休息日还是工作日,如果是休息日,则置为下一个工作日(应该不需要这一步)
    select isrest into v_isrest from ESTATE_EXT_WORKDAY where DATEYEAR = to_char(v_start_time, 'yyyy-MM-dd');
    if (v_isrest = '1')
    then
      while (v_isrest = '1') loop
        select isrest into v_isrest from ESTATE_EXT_WORKDAY where DATEYEAR = to_char(v_start_time+1, 'yyyy-MM-dd');
        if (v_isrest ='0')  --如果是工作日。则置为当天早上9点
        then
            v_start_time := trunc(v_start_time+1,'dd')+9/24;
        else
          v_start_time := v_start_time + 1;
        end if;
      end loop;
    end if;

    if v_start_time >= trunc(v_start_time,'dd')+17/24   -- 如果大于等于当天晚上5点。则开始时间为第二天9点
    then
      v_start_time := trunc(v_start_time+1,'dd')+9/24;
    elsif (v_start_time < trunc(v_start_time,'dd')+9/24)  --如果小于当天9点,则置为当天早上9点
    then
      v_start_time := trunc(v_start_time,'dd')+9/24;
    end if;

    while (n_temp_day < v_process_time) loop    --循环判断中间有多少个休息日
      
      select isrest into v_isrest from ESTATE_EXT_WORKDAY where DATEYEAR = to_char(v_start_time + n_lastWorkDay, 'yyyy-MM-dd');
	  n_lastWorkDay := n_lastWorkDay + 1;	
      if (v_isrest = '0')
      then
        n_temp_day := n_temp_day + 1;
      end if;
    end loop;

    deadline := v_start_time + n_lastWorkDay;

		-- 如果截止日期小于等于早上9点
    if deadline <= trunc(deadline,'dd')+9/24
    then
      v_isrest := '1';
      while (v_isrest = '1') loop
        
        select isrest into v_isrest from ESTATE_EXT_WORKDAY where DATEYEAR = to_char(deadline, 'yyyy-MM-dd');
				if v_isrest = '1'
				then
					deadline := deadline+1;
				end if;
      end loop;
      deadline := trunc(deadline,'dd')+9/24;
    end if;
		
		-- 如果截止日期大于等于晚上5点
		if deadline >= trunc(deadline,'dd')+17/24
    then
      v_isrest := '1';
      while (v_isrest = '1') loop
        deadline := deadline+1;
        select isrest into v_isrest from ESTATE_EXT_WORKDAY where DATEYEAR = to_char(deadline, 'yyyy-MM-dd');
      end loop;
      deadline := trunc(deadline,'dd')+9/24;
    end if;
  end if;

  RETURN deadline;
END GET_DEADLINE;

(3)身份证号码15为转18位

CREATE OR REPLACE
FUNCTION transIDCard15to18 (IdCardNO  VARCHAR)		-- 15位身份证转化为18位标准证件号
RETURN VARCHAR
as 
type coe_arr is varray(17) of VARCHAR2(17);
var_array coe_arr := coe_arr('7','9','10','5','8','4','2','1','6','3','7','9','10','5','8','4','2'); -- 系数数组
type verCode_arr is varray(11) of varchar2(5);
var_verCode verCode_arr := verCode_arr('1','0','X','9','8','7','6','5','4','3','2');	-- 18位标准身份证最后一位

oldIdCardNo varchar(20);	-- 15位身份证号
newCardNo VARCHAR(20);  -- 18位标准身份证号
preIdCardNO varchar(20);		-- 15位身份证前6位
sufIdCardNO varchar(20);		-- 15位身份证后9位

total number;	-- 17位数字和系数相乘的结果相加的总和

BEGIN
	oldIdCardNo := IdCardNO;
	newCardNo := '';
	preIdCardNO := '';
	sufIdCardNO := '';
	total := 0;
	  
	if (oldIdCardNo is not null and length(trim(oldIdCardNo)) = 15)
	then
		oldIdCardNo := trim(oldIdCardNo);
		preIdCardNO := substr(oldIdCardNo, 1, 6);		-- 截取前6位
		
		sufIdCardNO := substr(oldIdCardNo, 7, 9);		-- 截取后9位
		
		newCardNo := preIdCardNO||'19'||sufIdCardNO;

		for i in 1..var_array.count loop
			total := total + to_number(substr(newCardNo, i, 1)) * to_number(var_array(i));
		end loop;
		newCardNo := CONCAT(newCardNo, var_verCode(mod(total, 11)+1));
		
	end if;
	
	RETURN newCardNo;
END;

(4)金额数字转大写

CREATE OR REPLACE
FUNCTION "TO_UPPER_JE" (p_amount IN NUMBER)
         RETURN VARCHAR2 
   IS 
    smallmoney VARCHAR2(25);
    bigwrite   VARCHAR2(200);
    bignum     VARCHAR2(4);
    rmb        VARCHAR2(4);
    moneyplace NUMBER;
    dotplace   NUMBER;
    moneynum   NUMBER;
    
  BEGIN
    IF p_amount < 0 OR p_amount > 999999999999.9999 then  -- 0 - 千亿
      bigwrite := '转换金额超出计算范围.';
      RETURN bigwrite;
    END IF;

    IF p_amount = 0 THEN

       bigwrite := '零圆.';
       RETURN bigwrite;

    END IF;
    
    smallmoney := p_amount;
    dotplace := instr(smallmoney,'.');--得出小数点的位置
    IF dotplace = 0 THEN
      moneyplace := length(smallmoney);--整数的位数
    ELSE
      moneyplace := dotplace - 1;
    END IF;
    
    FOR moneynum IN 1..length(smallmoney) LOOP
            
      IF moneynum < dotplace THEN             --有小数点的前半部分处理   
        CASE (substr(smallmoney,moneynum,1))  --从左往右,高位开始读,判断转换数字
          WHEN '1' THEN bignum := '壹';
          WHEN '2' THEN bignum := '贰';
          WHEN '3' THEN bignum := '叁';
          WHEN '4' THEN bignum := '肆';
          WHEN '5' THEN bignum := '伍';
          WHEN '6' THEN bignum := '陆';
          WHEN '7' THEN bignum := '柒';
          WHEN '8' THEN bignum := '捌';
          WHEN '9' THEN bignum := '玖';
          WHEN '0' THEN bignum := '零';    --‘零’
        END CASE;
        CASE moneyplace                  --高位开始读,判断位数
            WHEN 1  THEN rmb := '元';
            WHEN 2  THEN rmb := '拾';
            WHEN 3  THEN rmb := '佰';
            WHEN 4  THEN rmb := '仟';
            WHEN 5  THEN rmb := '萬';
            WHEN 6  THEN rmb := '拾';
            WHEN 7  THEN rmb := '佰';
            WHEN 8  THEN rmb := '仟';
            WHEN 9  THEN rmb := '亿';  --亿
            WHEN 10 THEN rmb := '拾';
            WHEN 11 THEN rmb := '佰';
            WHEN 12 THEN rmb := '仟';  --千亿
            ELSE null;
        END CASE;
        moneyplace := moneyplace - 1;           --位数递减
        bigwrite := bigwrite || bignum || rmb;  --组合读取
      ELSIF moneynum = dotplace THEN
        bigwrite := bigwrite || '';   --‘点’
      ELSE                                    --无小数点全整数处理 与 有小数点的后半部分处理         
        CASE (substr(smallmoney,moneynum,1))
          WHEN '1' THEN bignum := '壹';
          WHEN '2' THEN bignum := '贰';
          WHEN '3' THEN bignum := '叁';
          WHEN '4' THEN bignum := '肆';
          WHEN '5' THEN bignum := '伍';
          WHEN '6' THEN bignum := '陆';
          WHEN '7' THEN bignum := '柒';
          WHEN '8' THEN bignum := '捌';
          WHEN '9' THEN bignum := '玖';
          WHEN '0' THEN bignum := '零';  --‘零’
        END CASE;
        CASE moneyplace
          WHEN -2 THEN rmb := '厘';
          WHEN -1 THEN rmb := '分';
          WHEN 0  THEN rmb := '角'; 
          WHEN 1  THEN rmb := '元';
          WHEN 2  THEN rmb := '拾';
          WHEN 3  THEN rmb := '佰';
          WHEN 4  THEN rmb := '仟';
          WHEN 5  THEN rmb := '萬';
          WHEN 6  THEN rmb := '拾';
          WHEN 7  THEN rmb := '佰';
          WHEN 8  THEN rmb := '仟';
          WHEN 9  THEN rmb := '亿';  --亿
          WHEN 10 THEN rmb := '拾';
          WHEN 11 THEN rmb := '佰';
          WHEN 12 THEN rmb := '仟';  --千亿
          ELSE rmb := null;
        END CASE;
        moneyplace := moneyplace - 1;
        
        bigwrite := bigwrite || bignum||rmb;
        
      END IF;
    END LOOP;
    --大写显示处理
    IF bigwrite IS NOT NULL THEN
      bigwrite := REPLACE(bigwrite, '零拾', '零');
      bigwrite := REPLACE(bigwrite, '零佰', '零');
      bigwrite := REPLACE(bigwrite, '零仟', '零');
      bigwrite := REPLACE(bigwrite, '零零零', '零');
      bigwrite := REPLACE(bigwrite, '零零', '零');
    --  bigwrite := REPLACE(bigwrite, '零角零分零厘', '整');
    --  bigwrite := REPLACE(bigwrite, '零厘', '整');
      bigwrite := REPLACE(bigwrite, '零角', '零');
      bigwrite := REPLACE(bigwrite, '零亿零萬零圆', '亿圆');
      bigwrite := REPLACE(bigwrite, '亿零萬零圆', '亿圆');
      bigwrite := REPLACE(bigwrite, '零亿零萬', '亿');
      bigwrite := REPLACE(bigwrite, '零萬零圆', '萬圆');
      bigwrite := REPLACE(bigwrite, '萬零圆', '萬圆');
      bigwrite := REPLACE(bigwrite, '零亿', '亿');
      bigwrite := REPLACE(bigwrite, '零萬', '萬');
      bigwrite := REPLACE(bigwrite, '零圆', '圆');
      bigwrite := REPLACE(bigwrite, '零零', '零');
    END IF;
    --壹元以下处理
    bigwrite := LTRIM(LTRIM(LTRIM(LTRIM(LTRIM(bigwrite, '圆'), '零'), '角'), '分'), '厘'); 
    
    IF bigwrite = '圆' THEN
      bigwrite := '零圆';
    END IF;
    
    RETURN(bigwrite);
 END TO_UPPER_JE;

(5)日期转大写

CREATE OR REPLACE
FUNCTION "TO_UPPER_DATE"(DATE_IN IN VARCHAR2) RETURN VARCHAR2
AS
	TEMP VARCHAR2(32767);
	RESULT VARCHAR2(32767);
	STR VARCHAR2(32767) := '〇一二三四五六七八九';
BEGIN
	IF DATE_IN IS NULL THEN 
		RETURN NULL;
	END IF;
	
	FOR I IN 1 .. LENGTH(DATE_IN)
		LOOP
		 SELECT SUBSTR(STR, SUBSTR(DATE_IN, I, 1) + 1, 1) INTO TEMP FROM DUAL;
		 RESULT := RESULT || TEMP;
	 END LOOP;
	 RETURN RESULT;
 EXCEPTION 
	WHEN OTHERS THEN 
		DBMS_OUTPUT.PUT_LINE(SQLERRM);
		RETURN '';
	RETURN NULL;
END;

(6)根据文件相对路径获取绝对路径

CREATE OR REPLACE
FUNCTION GET_FILE_REALPATH(absolutePath in VARCHAR2, httpAntiStealToken in VARCHAR2) 
RETURN VARCHAR2    -- 定义返回类型
AS
	v_file_prefix VARCHAR2(32767);		-- 文件前缀
	v_http_secretKey VARCHAR2(32767);  -- 密钥
	v_file_abs_path VARCHAR2(32767);		-- 文件的相对路径 如:group1/M00/00/00/wKgzgFnkTPyAIAUGAAEoRmXZPp876.jpeg
	v_file_abs_path_1 VARCHAR2(32767);		-- FastDFS文件的名称,如:M00/00/00/wKgzgFnkTPyAIAUGAAEoRmXZPp876.jpeg
	v_file_real_path VARCHAR2(32767);		--文件的绝对路径
	v_is_enable_safe_chain VARCHAR2(32767);	--是否开启防盗链
	i_timestamp number(18,0);		-- 当前时间的时间戳
	token VARCHAR2(32767);  		
	v_suffix VARCHAR2(32767); 		-- 相对路径后缀
BEGIN
	v_file_prefix := 'http://17.16.38.50/';
	v_http_secretKey := 'NCSBDCDJ20191219';
	v_file_abs_path := absolutePath;		
	v_is_enable_safe_chain := httpAntiStealToken;	
	
	if (v_file_abs_path is null)
	then
		v_file_real_path := '';
	elsif (v_file_abs_path is not null and (v_is_enable_safe_chain = 'true' or v_is_enable_safe_chain is null))
	then
		select ceil((sysdate - to_date('1970-01-01 08:00:00','yyyy-MM-dd hh24:mi:ss'))*24*60*60)+99 into i_timestamp from dual;	-- 查询当前时间的时间戳
		v_file_abs_path_1 := substr(v_file_abs_path, instr(v_file_abs_path, '/')+1);
		
		v_suffix := v_file_abs_path_1||v_http_secretKey||to_char(i_timestamp);
		token := lower(RawToHex(UTL_RAW.Cast_To_Raw(DBMS_OBFUSCATION_TOOLKIT.MD5(input_string=>v_suffix))));
		
		v_file_real_path := v_file_prefix||v_file_abs_path||'?token='||token||'&ts='||i_timestamp;
	elsif (v_file_abs_path is not null and v_is_enable_safe_chain = 'false')
	then
		v_file_real_path := v_file_prefix||v_file_abs_path;
	end if;
	
	RETURN v_file_real_path;
END;

(7)查询是否缴费(注意:函数中使用select语句无法查询两个字段;如果查询数据结果为空,则无法进行判断)

CREATE OR REPLACE
FUNCTION GET_SFJF(ywh in varchar2) 
RETURN varchar2
AS
	v_ywh varchar2(64);
	v_sfjf varchar2(64);	-- 是否缴费
	v_zfzt varchar2(64);	-- 支付状态
	i_jfje number(18,0);	--缴费金额
	i_count number(18,0);

BEGIN
	v_ywh := ywh; 
	v_sfjf := '2';
	i_jfje := 0;
	if (v_ywh is null)
	then
		return '';
	end if;
	
	select count(0) into i_count from  estate_dj_sf where ywh = v_ywh;
	
	if (i_count = 0)
	then
		return '2';
	end if;
	
	//如果v_ywh在数据库中查不出来,则会提示没有数据返回,无法进行下面的判断
	select ysje into i_jfje from  estate_dj_sf where ywh = v_ywh;
	select count(0) into i_count from V_MX_JKM_SUCC where slbh = v_ywh;
	
	if (i_jfje = 0 or i_jfje is null)
	then 
		v_sfjf := '2';
	elsif (i_count = 1)
	then
		v_sfjf := '1';
	else
		v_sfjf := '0';
	end if;
	
	RETURN v_sfjf;
END;

获取时间戳:

select ceil((sysdate - to_date('1970-01-01 08:00:00','yyyy-MM-dd hh24:mi:ss'))2460*60)+99 as timestamp from dual

0

评论区