oracel 相关

原创文章:如有下载及转载请注明来源链接，否则视为侵权
--excel辅助列合同号、日期公式快速在oracel查询
="select'"&D27&"'"&"合同号,"&"to_date('"&YEAR(Q27)&"/"&MONTH(Q27)&"/"&DAY(Q27)&"','yyyy/mm/dd')"&" 日期 from dual union all"

="select '"&A6&"'"&" 合同号 ,"&"to_date('"&YEAR(D6)&"/"&MONTH(D6)&"/"&DAY(D6)&"','yyyy/mm/dd')"&"还款时间 ,"&"'"&E6&"'"&"还款金额 ,"&"'"&J6&"'"&"机构 from dual union all"

--方法1更新表数据
MERGE INTO 表名
USING(
)
WHEN MATCHED THEN UPDATE SET

--举例
MERGE INTO collection.xn_calls_info a
USING(SELECT *
FROM xn043830.collection_user_info p
) mix ON(mix.userid=a.collection_id AND a.all_day=TRUNC(SYSDATE)-1)
WHEN MATCHED THEN UPDATE SET
a.collector_name=mix.collector_name;
--COMMIT ;

--方法2更新表数据
UPDATE 表名
SET
WHERE
--举例
UPDATE collection.xn_calls_info a
SET a.collctor_name=
(SELECT mix.collector_name
FROM xn043830.collection_user_info mix)
WHERE (SELECT mix.userid
FROM xn043830.collection_user_info mix)=a.collection_id
AND a.all_day=TRUNC(SYSDATE)-1

-------------更新cpd!=0的退案时间
UPDATE XN044215.XN_AGENCY_CALLLIST C
SET C.ASSIGN_END_DAY = TRUNC(SYSDATE) ---改为当天，原为昨天
WHERE C.ASSIGN_END_DAY > TRUNC(SYSDATE)---去掉=号
AND C.CPD != '0'
AND EXISTS (SELECT 1
FROM COLLECTION.ACCT_LOAN_DAILY L
WHERE C.CONTRACT_NO = L.PUTOUTNO
AND L.CPDDAYS <= 0
AND L.INSERT_TIME = TRUNC(SYSDATE)
AND L.PUTOUTNO NOT IN
(SELECT D.CONTRACT_NO
FROM COLLECTION.XN_COLL_CONTRACTS_INFO D
WHERE D.PAYOFF_DATE IS NOT NULL));

----------------------------------------------------------oracel LOOP循环语法格式---------------------------------------
create or replace procedure p_contract_cpd_daily as

begin

declare

v_date date;

begin

select trunc(sysdate) --今天
  into v_date
  from dual;

LOOP

  IF v_date > trunc(sysdate) THEN
    EXIT;
  END IF;
  ........
  ........
  ........
  ........
  commit;

  v_date := trunc(v_date) + 1;

END LOOP;

end;

end p_contract_cpd_daily;

--生成累计和(当over()后面不加任何内容时，就是对所有的数据进行汇总)
SELECT hh.contract_no AS 合同号,
hh.total_o_d_amt AS 委案金额，
SUM(hh.total_o_d_amt) OVER(ORDER BY hh.contract_no) AS 累计委案
/* (SELECT listagg(total_o_d_amt,'+')WITHIN GROUP(ORDER BY contract_no )
FROM bi.report_receivalble_base pp
WHERE hh.processing_date=TRUNC(SYSDATE) -1
AND hh.contract_no LIKE'100%'
AND hh.total_o_d_days BETWEEN 1 AND 30
AND pp.contract_no<=hh.contract_no
) AS 计算公式**/
FROM bi.report_receivalble_base hh
WHERE hh.processing_date=TRUNC(SYSDATE) -1
AND hh.contract_no LIKE'100%'
AND hh.total_o_d_days BETWEEN 1 AND 30

--求百分比函数
SELECT round(100/470*100,2)||'%'
FROM dual

--加减年/月/日
SELECT TRUNC(SYSDATE)-1 AS 减去1天,
TRUNC(SYSDATE)+1 AS 加上1天,
TRUNC(SYSDATE)-5 AS 减去5天,
add_months(TRUNC(SYSDATE),1) AS 加一个月,
add_months(TRUNC(SYSDATE),-2) AS 减去一个月,
add_months(TRUNC(SYSDATE,'MM'),-1) AS 上个月1日,
last_day(add_months(TRUNC(SYSDATE,'MM'),-1)) AS 上个月最后1天,
--加减时/分/秒
SYSDATE-1/24/60/60 AS 减去1秒,
SYSDATE+1/24/60/60 AS 加上1秒,
TRUNC(SYSDATE,'mi')-5/24/60 AS 减去5分钟,
TRUNC(SYSDATE,'mi')+5/24/60 AS 加上5分钟,
TRUNC(SYSDATE,'hh') -10/24 AS 减去10小时,
TRUNC(SYSDATE,'hh') +10/24 AS 加上10小时,
--字符型求星期，月份及年
to_char(SYSDATE,'day') AS 星期几,
to_char(SYSDATE,'month') AS 月份,
to_char(SYSDATE,'year') AS 年，
to_char(SYSDATE,'dd') AS 日期,
to_char(SYSDATE,'mm') AS 数字月份,
to_char(SYSDATE,'yyyy')
FROM dual

--三种情况排序对比
--(1)row_number()排序---分组后，每一组排序值都是连续排下去的，1,2,3,4 ，这种方法方便去重，相同组的数据只取一条
SELECT hh.contract_no AS 合同号,
hh.currcollector,
hh.total_o_d_amt,
row_number()OVER(PARTITION BY hh.contract_no,hh.currcollector ORDER BY hh.total_o_d_amt DESC ) AS rn
FROM bi.report_receivalble_base hh
WHERE hh.processing_date BETWEEN TRUNC(SYSDATE,'MM')-15 AND TRUNC(SYSDATE) -1
AND hh.contract_no LIKE'100%';

--(2)RANK()排序---分组后，是跳跃排序，有两个第1名时接下来就是第3名（同样是在各个分组内）．
SELECT hh.contract_no AS 合同号,
hh.currcollector,
hh.total_o_d_amt,
RANK()OVER(PARTITION BY hh.contract_no,hh.currcollector ORDER BY hh.total_o_d_amt DESC ) AS rn
FROM bi.report_receivalble_base hh
WHERE hh.processing_date BETWEEN TRUNC(SYSDATE,'MM')-15 AND TRUNC(SYSDATE) -1
AND hh.contract_no LIKE'100%';

--(3)dense_rank()排序是连续排序，有两个第1名时仍然跟着第二名。有重复值的
SELECT hh.contract_no AS 合同号,
hh.currcollector,
hh.total_o_d_amt,
dense_rank()OVER(PARTITION BY hh.contract_no,hh.currcollector ORDER BY hh.total_o_d_amt DESC ) AS rn
FROM bi.report_receivalble_base hh
WHERE hh.processing_date BETWEEN TRUNC(SYSDATE,'MM')-15 AND TRUNC(SYSDATE) -1
AND hh.contract_no LIKE'100%';

--限制返回来行数

SELECT *
FROM
(SELECT hh.*,ROWNUM AS ll
FROM bi.report_receivalble_base hh
WHERE ROWNUM<=10
)kk
WHERE ll=10

--随机排序（先随机排序，再取数据）
SELECT *
FROM
(SELECT hh.*
FROM bi.report_receivalble_base hh
ORDER BY dbms_random.value()

)
WHERE ROWNUM<=10

SELECT LEVEL FROM dual CONNECT BY LEVEL <=4

oracel 相关

end p_contract_cpd_daily;

推荐阅读更多精彩内容