oracle笔记

最新推荐文章于 2023-11-30 10:58:25 发布

chaoxiLi

最新推荐文章于 2023-11-30 10:58:25 发布

阅读量416

点赞数

分类专栏： oracle

oracle 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

distinct 去重复记录

union 并集，将两个sql连接在一起，具有distinct功能

union all 并集,将两个sql连接在一起，不具有distinct功能，所以效率要比union高

intersect:得到两个查询结果的交集，并且按照结果集的第一个列进行排序

minus:得到两个查询结果的减集，以第一列进行排序

Oracle函数to_char转化数字型指定小数点位数的用法
to_char，函数功能，就是将数值型或者日期型转化为字符型。
日期转为英语表达 to_char(to_date(pr.r_year||pr.r_month,'yyyymm'),'MON-yy','nls_date_language=american')

百分比表达 trim(to_char(round(pr.rebate_pst,4)*100,'990.99')||'%')

以car_name作为分组统计version_sale总和 sum(sa.version_sale) over (partition by sa.car_name) brand_sale

截取拼写 substr(to_char(ppp.Price_Time,'yyyymm'),0,4) ||to_number(substr(to_char(ppp.Price_Time,'yyyymm'),5,2))

如果LowestPrice字段为null 则返回第二个参数注: 前和后格式要一样 nvl(LowestPrice,0)

INITCAP(A.typeideng) 首字母大写其他小写

extract(截取时间函数)
select extract(year from systimestamp) year
      ,extract(month from systimestamp) month
      ,extract(day from systimestamp) day
      ,extract(minute from systimestamp) minute
      ,extract(second from systimestamp) second
      ,extract(timezone_hour from systimestamp) th
      ,extract(timezone_minute from systimestamp) tm
      ,extract(timezone_region from systimestamp) tr
      ,extract(timezone_abbr from systimestamp) ta
from dual

sign
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
select sign( 100 ),sign(- 100 ),sign( 0 ) from dual;
SIGN(123 ) SIGN(- 100 ) SIGN( 0 )

取同样条件下第一条记录
SELECT * FROM (SELECT r.*,Row_Number() OVER (partition by pl,MODEL_KW,paidfs,fzdy4 ORDER BY direction_price ASC) rank FROM res r) WHERE rank =1

DECODE函数相当于一条件语句(IF).它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。
当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。区别于SQL的其它函数，DECODE函数还能识别和操作空值.其具体的语法格式如下：
DECODE(input_value,value,result[,value,result…][,default_result]);其中：input_value 试图处理的数值。DECODE函数将该数值与一系列的序偶相比较，
以决定最后的返回结果value 是一组成序偶的数值。如果输入数值与之匹配成功，则相应的结果将被返回。对应一个空的返回值，可以使用关键字NULL于之对应result
是一组成序偶的结果值default_result 未能与任何一序偶匹配成功时，函数返回的默认值下面的例子说明了，如何读取用户CHECKUP表SEAPARK中的BLOOD_TEST_FLAG列下的项目，
作为DECODE函数的实参支持值。SELECT checkup_type,DECODE(blood_test_flag,’Y’,’Yes’,’N’,’No’,NULL,’None’,’Invalid’)FROM checkup;

前面补0 to_number(substr(lpad(ta.AREA_NAME, 4, 0), 0, 2)) as px

//分组函数适用于查询某型号一年每月的数据
GROUP(ORDER BY DISTRICT_ID, YEAR, MONTH_ID, WEEK_ID) AS DATA

//分组排序 (字段)

ROW_NUMBER() OVER(PARTITION BY MODELYEAR, MSRP,MODELID ORDER BY VERSIONID DESC) AS RN

--查数据库版本
select * from product_component_version

--小数点左边没有0
select to_char(0.1,'fm9999990.9999') from dual;

--优化sql
WITH TIME AS(
SELECT YEAR, MONTH_ID
FROM V_MIDS_VER_REBATE_ANALYSIS
WHERE TO_CHAR(TO_DATE(YEAR || MONTH_ID, 'yyyymm'), 'yyyymm') BETWEEN
       201305 AND 201307
GROUP BY MONTH_ID, YEAR
)
, MODEL AS (
SELECT MVRA.VERSION_ID
   FROM V_MIDS_VER_REBATE_ANALYSIS MVRA,DM_MODEL DM,DM_VERSION DV
WHERE MVRA.MODEL_ID IN (888, 517, 685)
    AND TO_CHAR(TO_DATE(YEAR || MONTH_ID, 'yyyymm'), 'yyyymm') BETWEEN
        201305 AND 201307 AND DM.MODEL_ID = MVRA.MODEL_ID AND DV.VERSION_ID = MVRA.VERSION_ID
GROUP BY MVRA.VERSION_ID
)

,COMPLEMENT AS(
SELECT * FROM TIME, MODEL
)
SELECT c.YEAR,
       c.MONTH_ID,
       c.VERSION_ID,
       d.REBATE_RATE,
       d.REBATE_AMOUNT,
       d.BONUS_RATE,
       d.BONUS,
       d.TACTICAL_PROMOTION,
       d.SALES_CONSULTANT_BONUS,
       d.DEALER_COST
FROM COMPLEMENT C
LEFT JOIN(
       SELECT * from V_MIDS_VER_REBATE_ANALYSIS MVRA WHERE TO_CHAR(TO_DATE(YEAR || MONTH_ID, 'yyyymm'), 'yyyymm') BETWEEN 201305 AND 201307
)D ON d.VERSION_ID = c.VERSION_ID AND D.YEAR = c.YEAR AND D.MONTH_ID = c.MONTH_ID

排名
RANK() OVER(ORDER BY BQ_SALES desc nulls last ) as bqnum
当2个对象的销量相同时，用“1 2 2 4”的方式排名。即第二名有2个，则无第三名，第二名之后为第四名

rownum
当2个对象的销量相同时，用“1 2 3 4”的方式排名

补数
select a.*,b.*,nvl(to_char(v.BQ_SALES),'0') sales ,sum(v.BQ_SALES) totalsales
       from(
          select d.year,d.month from dm_period d where d.ym between '201407' and '201409'
       ) a
       left join (
            select m.model_id,m.model_name from dm_model m where m.model_id in(1606,1882,1787,229,552)
       ) b on 1 = 1
       left join V_STATE_SALES_SUB_MODEL v on a.year = v.YEAR and v.MONTH_ID = a.month and v.MODEL_ID = b.model_id and v.DATA_TYPE_ID = 1+

//函数
select d.year,d.QUARTER_ID,sum(d.sales) sales,sum(d.tqsales) tqsales,sum(d.sqsales) sqsales
       from (
            select v.YEAR,
                   v.MONTH_ID,
                   v.QUARTER_ID,
                   sum(v.BQ_SALES) sales,
                   sum(v.TQ_SALES) tqsales,
                   --此分析函数是找当前记录的前第三条
                   lag(sum(v.BQ_SALES), 3) over(order by v.YEAR, v.MONTH_ID) sqsales
              from V_STATE_SALES_SUB_MODEL v
              where v.DATA_TYPE_ID = 1
              group by v.YEAR, v.MONTH_ID, v.QUARTER_ID
       )d group by d.year,d.QUARTER_ID
       order by d.year,d.QUARTER_ID

游标
声明游标
CURSOR CUR_CITY IS
SELECT DISTINCT L.CITY_ID FROM TB_PRICE_CITY_PRICE L;
循环游标
-- 二级细分市场 + city
FOR CMSG IN CUR_2ND_GRADE LOOP
-- 细分市场循环
FOR CC IN CUR_CITY LOOP
-- 城市循环
SELECT COUNT(*)
INTO COUNTNUM
FROM (SELECT *
FROM TB_SGM_PI_GRADE_PI_CITY A
WHERE A.SUB_GRADE_ID = CMSG.SEGMENT_ID
AND A.CITY_ID = CC.CITY_ID
ORDER BY A.YEAR DESC, A.MONTH_ID DESC, A.WEEK_ID DESC) A
WHERE ROWNUM = 1;

IF COUNTNUM > 0 THEN
SELECT A.YEAR, A.MONTH_ID, A.WEEK_ID, A.MSRP_INDEX, A.TP_INDEX
INTO B_P_YEAR,
B_P_MONTH,
B_P_WEEK,
B_P_MSRP_INDEX,
B_P_TP_INDEX
FROM (SELECT *
FROM TB_SGM_PI_GRADE_PI_CITY A
WHERE A.SUB_GRADE_ID = CMSG.SEGMENT_ID
AND A.CITY_ID = CC.CITY_ID
ORDER BY A.YEAR DESC, A.MONTH_ID DESC, A.WEEK_ID DESC) A
WHERE ROWNUM = 1;
ELSE
B_P_YEAR := BASE_YEAR;
B_P_MONTH := BASE_MONTH;
B_P_WEEK := BASE_WEEK;
B_P_MSRP_INDEX := 1;
B_P_TP_INDEX := 1;
END IF;
COUNT_FDM_2ND_GRADE_PRICEINDEX(CMSG.SEGMENT_ID,
CC.CITY_ID,
YEARNUM,
MONTHNUM,
WEEKNUM,
BASE_YEAR,
BASE_MONTH,
BASE_WEEK);
B_P_YEAR := 0;
B_P_MONTH := 0;
B_P_WEEK := 0;
B_P_MSRP_INDEX := 1;
B_P_TP_INDEX := 1;
END LOOP; -- end cur_city
END LOOP; -- end cur_2nd_grade

存储过程定义
PROCEDURE INIT_VERSION_DISTRICT_PRICE AS
--游标定义
CURSOR CUR_DISTRICT IS
SELECT DISTRICT_ID, DISTRICT_NAME, BRAND_ID
FROM TB_PRODUCT_DISTRICT
ORDER BY DISTRICT_ID;

BEGIN
EXECUTE IMMEDIATE 'truncate TABLE TB_SGM_PI_VERSION_PRICE_DIST';
EXECUTE IMMEDIATE 'ALTER INDEX IDX_SGM_PI_VERSION_PRICE_DIST UNUSABLE';
--循环游标
FOR CD IN CUR_DISTRICT LOOP
INSERT INTO TB_SGM_PI_VERSION_PRICE_DIST
(VERSION_ID, YEARNUM, MONTHNUM, WEEKNUM, DISTRICT_ID, PRICE, MSRP) /*nologging*/
SELECT F.VERSION_ID,
SUBSTR(F.STATISTIC_DATE, 0, 4),
SUBSTR(F.STATISTIC_DATE, 5),
F.PRICE_BATCH_ID,
CD.DISTRICT_ID,
AVG(L.PRICE),
AVG(F.MSRP)
FROM TB_PRICE_RSCH F
INNER JOIN TB_PRICE_CITY_PRICE L ON F.RSCH_PRICE_ID =
L.PRICE_RSCH_ID
INNER JOIN TB_PRODUCT_DISTRICT_PROVINCE A ON A.DISTRICT_ID =
CD.DISTRICT_ID
INNER JOIN TB_PRODUCT_CITY B ON A.PROVINCE_ID = B.PROVINCE_ID
AND B.CITY_ID = L.CITY_ID
WHERE F.PRICE_BATCH_ID IN (1, 2, 3, 4, 5)
AND L.PRICE >= 0
AND TO_NUMBER(F.STATISTIC_DATE) >= 201312
--AND l.city_id NOT IN (69,81,95)
GROUP BY F.VERSION_ID, F.STATISTIC_DATE, F.PRICE_BATCH_ID;
END LOOP;

COMMIT;
EXECUTE IMMEDIATE 'ALTER INDEX IDX_SGM_PI_VERSION_PRICE_DIST REBUILD';
END INIT_VERSION_DISTRICT_PRICE;

行转列例子
产品名称销售额季度
奶酪 50 第一季度
奶酪 60 第二季度
啤酒 50 第二季度
啤酒 80 第四季度

产品名称第一季度销售额第二季度销售额第三季度销售额第四季度销售额
奶酪 50 60 0 0
啤酒 0 50 0 80

oracle下可以用函数decode处理:
select 产品名称,sum(decode(季度,'第一季度',销售额,0)) 第一季度销售额,
sum(decode(季度,'第二季度',销售额,0)) 第二季度销售额,
sum(decode(季度,'第三季度',销售额,0)) 第三季度销售额,
sum(decode(季度,'第四季度',销售额,0)) 第四季度销售额,
from 表名 group by 产品名称;

查询锁表
SELECT object_name, machine, s.sid, s.serial#
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id　= o.object_id
AND l.session_id = s.sid;

--MERGE 用两个表相同的字段更新其中一个表的某1字段

MERGE INTO products t1
USING newproducts t2
ON (t1.product_id = t2.product_id)
WHEN MATCHED THEN
UPDATE
SET t1.product_name = t2.product_name,
t1.category = t2.category;

--Update
UPDATE TB_MARKET_ADVERTISE P
SET P.SEGMENT_LEVEL1_ID = (SELECT NP.SEGMENT_LEVEL1_NEW
FROM TB_MODEL_SEGMENT_UPDATE NP
WHERE P.MANF_ID = NP.MANF_ID
AND P.BRAND_ID = NP.BRAND_ID
AND P.MODEL_ID = NP.MODEL_ID
AND P.DATA_SUPPLIER = NP.DATA_SUPPLIER
AND P.SEGMENT_LEVEL1_ID = NP.SEGMENT_LEVEL1_OLD)
WHERE EXISTS (
SELECT * from TB_MODEL_SEGMENT_UPDATE NP
WHERE P.MANF_ID = NP.MANF_ID
AND P.BRAND_ID = NP.BRAND_ID
AND P.MODEL_ID = NP.MODEL_ID
AND P.DATA_SUPPLIER = NP.DATA_SUPPLIER
AND P.SEGMENT_LEVEL1_ID = NP.SEGMENT_LEVEL1_OLD
)

--失误删除数据 commit掉时间戳
delete from sys_resource;
insert into sys_resource
select *
from sys_resource as of timestamp to_timestamp('20150713 18:00:00','yyyymmdd hh24:mi:ss');
commit;

-- 一行分成多列
--1
MODEL_ID SUB_MODEL_ID
130 491,2910
132 230,2916
133 3710,3793
138 3997

--2
WITH T2 AS (
SELECT MODEL_ID,SUB_MODEL_ID from TB_PRODUCT_CAR_MODEL
)
select regexp_substr(T2.SUB_MODEL_ID, '[^,]+', 1, l) SUB_MODEL_ID,T2.MODEL_ID from T2,
(select level l from dual connect by level<= 100) b
where l <=length(T2.SUB_MODEL_ID) - length(replace(T2.SUB_MODEL_ID,','))+1;
--3
MODEL_ID SUB_MODEL_Id
130 491
130 2910
132 230
132 2916
133 3710
133 3793
138 3997

-- 更新或增加重要表时注意
删除与更新或增加要在一个事务以防止同步出问题

--like 用法
1.LIKE '%'||SUBSTR(TET.ENGINE_TYPE_NAME,0,length(TET.ENGINE_TYPE_NAME)-1)||'%'