想到哪写到哪。
1. 典型的SQL结构
SELECT
t1.f1,
t1.f,
t2.f3
FROM
table1 t1,
table2 t2
WHERE t1.f = t2.f -- 此为两表关联条件
AND t1.f4 > sysdate - 1 --此为数据筛选条件
ORDER BY
t1.f1,
t2.f3
-- 建议关键字大写，其它小写，每个表都定义别名，同时通过换行、空白等格式让代码易读。
2. 分组查询
以下分组查询关键字必须在SQL中包含GROUP BY，WHERE作为非分组字段的筛选条件写在GROUP BY之前，HAVING作为分组字段的筛选条件写在GROUP BY之后：
- COUNT()
- MIN()
- MAX()
- AVG()
- SUM()
3. 关联表的方式
- WHERE关联，这种写法方便理解完整的代码。
- JOIN关联，表之间用ON描述关系。
- INNER JOIN，强约束关联，返回值是两个表的交集。
- LEFT OUTER JOIN，左侧表所有的记录都返回，右侧匹配的记录返回，没有匹配的返回NULL，等同于WHERE t1.f = t2.f(+)。
- RIGHT OUTER JOIN, 右侧表的记录返回，左侧返回匹配的记录，没有匹配返回NULL，等同于WHERE t1.f(+) = t2.f。
- FULL OUTER JOIN，返回值是两个表的合集。
4. 合并查询结果
- 用UNION ALL合并多个查询结果集。
5. 限制查询结果集
- 用DISTINCT过滤重复记录。
- 用ROWNUM < N设定记录数。
6. 日期转换
- TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')，将日期转换成字符串，注意HH24表示24小时格式，MI表示分钟格式。
- TO_DATE('2010-04-14 10:14:23', 'YYYY-MM-DD HH24:MI:SS')，将字符串转换成日期。
7. DUAL
DUAL是系统内置的一个特殊表，此表有N多字段，但是只有一行记录，可以理解成一个特殊函数构造，如：
SELECT SYSDATE FROM DUAL; -- 查询系统日期
SELECT 1/3 FROM DUAL; -- 数值运算
SELECT USER FROM DUAL; -- 查询当前线程登录帐号
8. DECODE
使用此函数可以将字段直接进行处理，用法：DECODE(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)。
如DECODE(LISTSUBASSEMBLY, NULL, OPTIONPARTQUANTITY, LISTSUBASSEMBLY)，表示对于LISTSUBASSEMBLY字段，如果其值为NULL，则取OPTIONPARTQUANTITY值，否则取LISTSUBASSEMBLY值。
9. 代码注释
- 用--单行注释
- 用/* ...*/进行多行注释
10. 在查询SQL中可调用用户创造的函数
11. 在查询SQL中可调用的数据集：
- 表
- 视图
- 临时表
- 物化视图
- 远程数据库对象，用@连接远程数据库。
12. 物化视图
- 建议先创建表，包含所有的查询SQL，然而用物化视图引用此表。
- 建议用数据库JOB定期刷新表，对于重要数据，也可以用TRIGGER更新到物化视图。
- 物化视图是一个物理对象，有存储空间，可创建索引。
13. 表数据的备份
CREATE TABLE T2
AS
SELECT * FROM T1
注意创建的新表只包括结构和数据，但是索引和约束会丢失。
14. 用旧表创建新的空表结构
CREATE TABLE T2
AS
SELECT * FROM T1 WHERE 1 = 0 -- 此条件将返回0条记录
15. 用DBMS调试存储过程
- 在存储过程中包含dbms_output.put_line('P1='||P1); 其中P1是存储过程参数。
- 打开客户端的COMMAND WINDOW，SET SERVEROUTPUT ON;
- EXECUTE IMMEDIATE存储过程，DBMS的消息将显示到窗口中。
16. 最后，围观一坨300多行的单句查询SQL:
/*----------------------------------------------------------------------------------
Modified by : Tripti Mandal
Date : 31st Mar 09
Organisation : TCS
Purpose : To get validation information for workorders on a particular the production line no
e.g
Option Part Mismatch( Item assembly configuration checks)
BOM Not Download
Modified by : Tripti Mandal
Date : 23-Apr-09
Organisation : TCS
Purpose : To Fix PRDB_G00138326(Unable to download
workorders from multiple lines at the same time)
Modified by : Rahul Sapar
Date : 15-June-09
Organisation : TCS
Purpose : SubAssembly changes
Modified By : Amitava ( TCS )
Modified On : 9Jul07
PR : DB_G00142020
Modified By : Rahul Sapar ( TCS )
Modified On : 10Jul09
Purpose : Used Materialized View (COB_MV_DAILY_SCH_BOM) rather then mormal view (COB_V_DAILY_SCH_BOM_28001) to improve performance of SQL - PR : DB_G00142563
Modified By : Amitava ( TCS )
Modified On : 14Jul07
PR : DB_G00142563
Modified By : Rahul Sapar ( TCS )
Modified On : 26Jul09
PR : Changed MV from WO specific to WO and Date Specific
Modified By : Rahul Sapar ( TCS )
Modified On : 23Aug09
PR : To Catch Multiple SubAssemblies
Modified By : Rahul Sapar ( TCS )
Modified On : 23Sept09
PR : DB_G00146371
Modified By : Rahul Sapar ( TCS )
Modified On : 27Sept09
PR : DB_G00146418
Modified By : Rahul Sapar ( TCS )
Modified On : 12Oct09
PR : DB_G00146418
Modified By : Taoyun Zhang (BFCEC)
Modified On : 06 Oct 09
Purpose: Check items discontinued in BOM but effective in IAC,
Change BOM LEFT OUTER JOIN IAC, INTO FULL OUTER JOIN
DECODE PART FROM BOM OR IAC
--------------------------------------------------------------------------------------*/
SELECT DSSG.WIPJOBNO AS LISTWORKORDERNO,
DSSG.SONUMBER AS LISTSHOPORDER,
TO_DATE (DSSG.BUILDSCHEDULEDATE, 'YYMMDD')
AS LISTWORKORDERBUILDSCHDATE,
TO_DATE (DSSG.CUSTOMERREQUESTDATE, 'YYMMDD')
AS LISTWORKORDERCUSTOMERREQDATE,
DSSG.CUSTOMERSPECIFICATION AS LISTWORKORDERCUSTOMERSPEC,
DSSG.CUSTOMERNAME AS LISTWORKORDERCUSTOMERNAME,
DSSG.STATUS AS LISTWORKORDERSTATUS,
TO_DATE (DP.DP_BUILD_DATE, 'YYDDD') AS LISTDATAPLATEBUILDSCHDATE,
OPTIONPARTQUANTITY AS LISTPARTMISSINGINFO,
DSSG.HOTBUILDINDICATOR AS LISTHOTBUILDINDICATOR
FROM (
SELECT *
FROM COB_T_DAILY_SO_SCHEDULE
WHERE WIPJOBNO IN (@ListWorkOrderNo)) DSSG
LEFT OUTER JOIN
COB_T_DATAPLATE DP
ON DP.DP_SO_NO = DSSG.SONUMBER
AND TO_DATE (DP.DP_BUILD_DATE, 'YYDDD') =
TO_DATE (DSSG.BUILDSCHEDULEDATE, 'YYMMDD') --DSSG.BUILDSCHEDULEDATE
LEFT OUTER JOIN
(
SELECT NVL (WORKORDER, '') AS WORKORDER, -- ADDED BY RAHUL SAPAR
DECODE (
LISTSUBASSEMBLY,
NULL,
OPTIONPARTQUANTITY,
DECODE (OPTIONPARTQUANTITY,
'0', LISTSUBASSEMBLY,
NULL, LISTSUBASSEMBLY,
OPTIONPARTQUANTITY)
)
AS OPTIONPARTQUANTITY
FROM
-- A3 begin
(
--SELECT * FROM
-- A3 begin
--(
SELECT
A2.WORKORDER WORKORDER, --A2.P1, A2.P2, A2.ND,
COB_F_TAB_TO_STRING (
CAST (
COLLECT(DECODE (
TO_CHAR (A2.P1),
NULL,
'BOM Not downloaded',
TO_CHAR (A2.P1) || '-->'
|| TO_CHAR(A2.P2 || '-->'
|| TO_CHAR (
A2.ND
))
)) AS COB_T_VARCHAR2_TAB
),
','
)
AS OPTIONPARTQUANTITY,
a2.listsubassembly
FROM
-- A2 begin
(
SELECT
*
FROM
(
SELECT * FROM
-- A begin
(
SELECT *
--WORKORDER,
FROM
-- X begin
(
SELECT BOMIAC.WORKORDER,
PARENTPART.PRODUCTNO P1,
CHILDPART.PRODUCTNO P2,
BOMIAC.quantity ND
FROM
--(
--(
--SELECT * FROM
-- BOMIAC begin
(
SELECT
DECODE(workorder, null, workorder2, workorder) workorder,
DECODE(parentproductid, null, parentproductid2, parentproductid) parentproductid,
DECODE(parentproductno, null, parentproductno2, parentproductno) parentproductno,
DECODE(productid, null, productid2, productid) productid,
--DECODE(quantity, null, quantity2, quantity) quantity
(NVL(quantity,0) - NVL(quantity2,0)) quantity
FROM
(
SELECT
--WORKORDER,
--PARENTPRODUCTID,
--PARENTPRODUCTNO,
--PRODUCTID,
--QUANTITY
bom.WORKORDER,
bom.PARENTPRODUCTID,
bom.PARENTPRODUCTNO,
bom.PRODUCTID,
bom.QUANTITY,
ctis.WORKORDER workorder2,
ctis.PARENTPRODUCTID parentproductid2,
ctis.PARENTPRODUCTNO parentproductno2,
ctis.PRODUCTID productid2,
ctis.QUANTITY quantity2
FROM
-- BOM begin
(
SELECT
DSS.WIPJOBNO AS WORKORDER,
VBOM.PARENTPRODUCTID AS PARENTPRODUCTID,
VBOM.PARENTPRODUCTNO -- Added by Rahul Sapar 23 Sept 2009
AS PARENTPRODUCTNO,
VBOM.PRODUCTID AS PRODUCTID,
VBOM.QUANTITY AS QUANTITY
FROM
COB_MV_DAILY_SCH_BOM VBOM -- Modified by Rahul Sapar 10 July 2009
RIGHT OUTER JOIN
-- DSS begin
(
SELECT
WIPJOBNO,
SONUMBER,
STRUCTUREDATE
FROM
COB_T_DAILY_SO_SCHEDULE
WHERE WIPJOBNO IN (@ListWorkOrderNo)
) DSS --ON DSS.WIPJOBNO = VBOM.WORKORDER
-- DSS end
ON DSS.SONUMBER = VBOM.SONUMBER
AND DSS.STRUCTUREDATE = VBOM.STRUCTUREDATE -- Added By Rahul Sapar 26 July - Made View Shop Order and Date specific rather than WO specific
)
--)
BOM
-- BOM end
-- LEFT OUTER JOIN -- change to full outer join, taoyun, 06 Oct 09
FULL OUTER JOIN
-- CTIS begin
(
SELECT
WIPJOBNO AS WORKORDER,
PARENTPRODUCTID,
PARENTPRODUCTNO,
PRODUCTID,
QUANTITY AS QUANTITY -- Tripti 23-Apr-09 , Changed the sum
FROM
(
SELECT *
FROM
COB_V_DAILY_SCH_CTIS_28002
WHERE WIPJOBNO IN (@ListWorkOrderNo)
AND ASSMPRODUCTIONLINENO IN (@ListProductionLineNo)
)
GROUP BY
WIPJOBNO,
PARENTPRODUCTID,
PARENTPRODUCTNO,
PRODUCTID,
QUANTITY -- Tripti 23-Apr-09
)
CTIS
-- CTIS end
ON CTIS.WORKORDER = BOM.WORKORDER
AND CTIS.PARENTPRODUCTID = BOM.PARENTPRODUCTID
AND CTIS.PARENTPRODUCTNO = BOM.PARENTPRODUCTNO
AND CTIS.PRODUCTID = BOM.PRODUCTID
)
WHERE (NVL(QUANTITY,0) - NVL(QUANTITY2,0) <> 0)
) BOMIAC
--) BOMIAC end
LEFT JOIN PRODUCT PARENTPART
ON PARENTPART.ID = BOMIAC.PARENTPRODUCTID
LEFT JOIN PRODUCT CHILDPART
ON CHILDPART.ID = BOMIAC.PRODUCTID
--)
--)
--WHERE (NVL (BOM.QUANTITY, 0) - NVL (CTIS.QUANTITY, 0) <> 0) OR (BOM.PARENTPRODUCTID IS NULL))
GROUP BY
BOMIAC.WORKORDER,
PARENTPART.PRODUCTNO,
CHILDPART.PRODUCTNO,
BOMIAC.quantity
) X
-- X end
--FULL OUTER JOIN
LEFT OUTER JOIN
-- Y begin
(
SELECT NVL (LISTSUBASSEMBLY, '') LISTSUBASSEMBLY, WIPJOBNO
FROM -- Add NVL here
(SELECT
'SUBASSMFAIL' AS LISTSUBASSEMBLY,DSS.WIPJOBNO as WIPJOBNO
FROM
cob_t_daily_so_schedule dss
INNER JOIN product p ON P.PRODUCTNO = DSS.SONUMBER
INNER JOIN cob_t_shop_order_detail sod ON p.productno = sod.productno
INNER JOIN product_component pc ON pc.productid = p.id
INNER JOIN component c
ON ( c.ID = pc.componentid
AND c.effectivedate <= TO_DATE (DSS.STRUCTUREDATE , 'YYMMDD')
AND ( c.discontinuedate > TO_DATE (DSS.STRUCTUREDATE, 'YYMMDD')
OR c.discontinuedate IS NULL
)
)
INNER JOIN product p1 ON p1.ID = c.productid
INNER JOIN product_component pc2 ON pc2.productid = p1.ID
INNER JOIN component c2
ON ( c2.ID = pc2.componentid
AND c2.effectivedate <= TO_DATE (DSS.STRUCTUREDATE, 'YYMMDD')
AND ( c2.discontinuedate > TO_DATE (DSS.STRUCTUREDATE, 'YYMMDD')
OR c2.discontinuedate IS NULL
)
)
INNER JOIN product p2
ON p2.ID = c2.productid AND p2.productinventorytype = 102
left outer JOIN cob_t_engine_build_var ebv
ON ((
(ebv.level1option = p1.productno) AND
(ebv.customer = sod.customer OR ebv.customer = 'ALL')
AND ( ebv.enginefamily = sod.enginefamily
OR ebv.enginefamily = 'ALL'
)
AND (ebv.enginetype = sod.enginetype OR ebv.enginetype = 'ALL'
)
AND ( ebv.configuration = sod.configuration
OR ebv.configuration = 'ALL'
)
AND (ebv.application = sod.application
OR ebv.application = 'ALL'
)
AND (ebv.active = 1)
)
)
INNER JOIN cob_t_item_assembly ctis
ON ( ctis.parentproductid = p1.ID
and CTIS.PRODUCTID = p2.id
AND ctis.variationcode = NVL (ebv.variationcode, 0)
AND ctis.effectivedate <= TO_DATE (DSS.STRUCTUREDATE, 'YYMMDD')
AND ( ctis.discontinuedate >=
TO_DATE (DSS.STRUCTUREDATE, 'YYMMDD')
OR ctis.discontinuedate IS NULL
)
AND ctis.active = 1
AND ( ctis.routingnumber IS NULL
OR ctis.routingnumber = 0
OR ctis.routingnumber = 1
)
AND (ctis.deviatedpart = 0) --Compare non deviated part only
AND (ctis.assmworkstation IS NOT NULL)
AND ctis.assmproductionlineno = DSS.PRODUCTIONLINENO
)
WHERE DSS.WIPJOBNO IN (@ListWorkOrderNo)
GROUP BY DSS.WIPJOBNO, ctis.assmworkstation,p2.PRODUCTNO,p2.productinventorytype
HAVING sum (c2.quantity) > 1
)
--)
) Y
-- Y end
ON X.WORKORDER = Y.WIPJOBNO
) A
-- A end
--GROUP BY WORKORDER, P1, P2, ND
)
) A2
-- A2 end
GROUP BY WORKORDER,listsubassembly
) A3
-- A3 end
--)
)
ON WORKORDER = DSSG.WIPJOBNO
ORDER BY DSSG.WIPJOBNO
博文
