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
评论区