Oracle DECODE 和 CASE 用法

发布时间：2020-12-12 15:06:55 所属栏目：百科来源：网络整理

导读：链接 Oracle DECODE CASE Functions Version 11.1 Note: Decode and Case are very similar in their appearance but can produce very different results. Demo Tables Data Airplanes Table Locations Table All other tables DECODE (overload 1) standar

Oracle DECODE & CASE FunctionsVersion 11.1Note:Decode and Case are very similar in their appearance but can produce very different results.Demo Tables & DataAirplanes TableLocations TableAll other tablesDECODE (overload 1)standard.DECODE(exprNUMBER,patNUMBER,resNUMBER)RETURNNUMBER;DECODE (overload 2)standard.DECODE(
exprNUMBER,
patNUMBER,
resVARCHAR2CHARACTER SET ANY_CS)
returnVARCHAR2CHARACTER SET res%CHARSET;DECODE (overload 3)standard.DECODE(exprNUMBER,resDATE)RETURNDATE;DECODE (overload 4)standard.DECODE(
exprVARCHAR2CHARACTER SET ANY_CS,
patVARCHAR2CHARACTER SET expr%CHARSET,
resNUMBER)
RETURNNUMBER;DECODE (overload 5)standard.DECODE(
exprVARCHAR2CHARACTER SET ANY_CS,
resVARCHAR2CHARACTER SET ANY_CS)
RETURNVARCHAR2CHARACTER SET res%CHARSET;DECODE (overload 6)standard.DECODE(
exprVARCHAR2CHARACTER SET ANY_CS,
resDATE)
RETURNDATE;DECODE (overload 7)standard.DECODE(exprDATE,patDATE,resNUMBER)RETURNNUMBER;DECODE (overload 8)standard.DECODE(
exprDATE,
patDATE,
resVARCHAR2CHARACTER SET ANY_CS)
RETURNVARCHAR2CHARACTER SET res%CHARSET;DECODE (overload 9)standard.DECODE( exprDATE,patDATE,resDATE)RETURNDATE;DECODE (overload 10)standard.DECODE(exprOBJECT,patOBJECT,resOBJECT)RETURNOBJECT;DECODE (overload 11)standard.DECODE(exprUNDEFINED,patUNDEFINED,resUNDEFINED)
RETURNUNDEFINED;Decode Built-in Function
Simple DECODESELECTDECODE(value,<if this value>,<return this value>)
FROMDUAL;SELECTprogram_id,
DECODE(customer_id,'AAL','American Airlines') AIRLINE,
delivered_date
FROMairplanes
WHEREROWNUM< 11;
More Complex DECODESELECTDECODE(value,<if this value>,<return this value>,
<if this value>,<return this value>,
....)
FROMDUAL;SELECTprogram_id,
DECODE(customer_id,
'AAL','American Airlines',
'ILC','Intl. Leasing Corp.',
'NWO','Northwest Orient',
'SAL','Southwest Airlines',
'SWA','Sweptwing Airlines',
'USAF','U.S. Air Force') AIRLINE,
delivered_date
FROMairplanes
WHEREROWNUM< 11;
DECODE with DEFAULTSELECTDECODE(value,<return this value>,
<if this value>,
....
<otherwise this value>)
FROMDUAL;SELECTprogram_id,
'AAL','American Airlines',
'ILC','Intl. Leasing Corp.',
'NWO','Northwest Orient',
'SAL','Southwest Airlines',
'SWA','Sweptwing Airlines',
'USAF','United States Air Force',
'Not Known') AIRLINE,
delivered_date
FROMairplanes
WHEREROWNUM< 11;Note:The following crosstabulation is the standard for 10g or earlier. In 11g use the PIVOT and UNPIVOT operatorsSimple DECODE Crosstab

Note how each decode only looks at a single possible value and turns it into a new columnSELECTprogram_id,
DECODE(customer_id,'AAL')AMERICAN,'DAL','DAL')DELTA,'NWO','NWO')NORTHWEST,'ILC','ILC')INTL_LEASING
FROMairplanes
WHEREROWNUM< 20;
DECODE as an in-line view with crosstab summationThe above DECODE,in blue,used as an in-line viewSELECTprogram_id,
COUNT(AMERICAN) AAL,
COUNT(DELTA) DAL,
COUNT(NORTHWEST) NWO,
COUNT(INTL_LEASING) ILC
FROM(
SELECTprogram_id,'ILC')INTL_LEASING
FROMairplanes)
GROUP BYprogram_id;
Query for DECODE demoCREATETABLEstores (
store_name VARCHAR2(20),
region_dir NUMBER(5),
region_mgrNUMBER(5),
store_mgr1NUMBER(5),
store_mgr2NUMBER(5),
asst_storemgr1NUMBER(5),
asst_storemgr2NUMBER(5),
asst_storemgr3NUMBER(5));

INSERTINTOstores
VALUES('San Francisco',100,200,301,302,401,403);

INSERTINTOstores
VALUES('Oakland',404,0);

INSERTINTOstores
VALUES('Palo Alto',305,405,406);

INSERTINTOstores
VALUES('Santa Clara',250,306,407);
COMMIT;

SELECTDECODE(asst_storemgr1,
DECODE(asst_storemgr2,
DECODE(asst_storemgr3,asst_storemgr3),
asst_storemgr2),asst_storemgr1)ASST_MANAGER,
DECODE(store_mgr1,DECODE(store_mgr2,store_mgr2),
store_mgr1)STORE_MANAGER,
REGION_MGR,
REGION_DIR
FROMstores;
DECODE with Summary FunctionSELECTSUM(CA_COUNT) CA,SUM(TX_COUNT) TX
FROM(
SELECTstate,
DECODE(state,'CA',COUNT(*),0) CA_COUNT,'TX',0) TX_COUNT
FROMlocations
GROUP BYstate);
DECODE in theWHEREclauseset serveroutput on

DECLARE
posnPLS_INTEGER:= 0;
empidPLS_INTEGER:= 178;
xNUMBER;
BEGIN
SELECTNVL(SUM(ah.quantity * ah.saleprice * ap.payoutpct),0)
INTOx
FROMaccessoryhistory ah,payoutpercentage ap,
sku s,store st
WHEREempid =DECODE(posn,
0,st.areadir,
1,st.areamgr,
2,NVL(st.storemgr1,st.storemgr2),
3,NVL(st.asstmgr1,NVL(st.asstmgr2,
st.asstmgr3)))
ANDah.statustypeIN('ACT','DEA')
ANDah.store = st.store
ANDs.dbid = ah.dbid
ANDs.sku = ah.sku
ANDap.productgroup = s.productgroup
ANDap.position = posn;

dbms_output.put_line(x);
END;
/
DECODE Altered WHERE Clause

Thanks to HJLCREATETABLEtest (
pubdateDATE,
compdateDATE,
valuecolNUMBER(5));

INSERTINTOtestVALUES(TRUNC(SYSDATE),TRUNC(SYSDATE+300),1);
INSERTINTOtestVALUES(TRUNC(SYSDATE-300),TRUNC(SYSDATE),9);
COMMIT;

SELECT*FROMtest;

CREATEORREPLACEPROCEDUREtestproc (
StartDateDATE,EndDateDATE,DateTypeINVARCHAR2) IS

iPLS_INTEGER;
BEGIN
SELECTvaluecol
INTOi
FROMtest
WHEREDECODE(DateType,'AA',compdate,'BB',pubdate,compdate) <= EndDate
ANDDECODE(DateType,compdate) >= StartDate;

dbms_output.put_line(TO_CHAR(i));
ENDtestproc;
/

set serveroutput on

exec testproc(TRUNC(SYSDATE),'BB');CASE
Simple CASE DemoSELECTCASEWHEN(<column_value>=<value>)THEN
WHEN(<column_value>=<value>)THEN
ELSE<value>
FROM<table_name>;SELECTline_number,
CASEWHEN(line_number = 1)THEN'One'
WHEN(line_number = 2)THEN'Two'
ELSE'More Than Two'
ENDASRESULTSET
FROMairplanes;
More Complex CASE Demo With BetweenSELECTCASEWHEN(<column_value>BETWEEN<value>
AND<value>)THEN
WHEN(<column_value>
BETWEEN<value>AND<value>)THEN
ELSE<value>
FROM<table_name>;SELECTline_number,
CASEWHEN(line_numberBETWEEN1AND10)THEN'One'
WHEN(line_numberBETWEEN11AND100)THEN'Big'
ELSE'Bigger'
END
FROMairplanes;
More Complex CASE Demo With BooleansSELECTCASEWHEN(<column_value><=<value>)THEN
WHEN(<column_value><=<value>)THEN
ELSE<value>
FROM<table_name>;SELECTline_number,
CASEWHEN(line_number < 10)THEN'Ones'
WHEN(line_number < 100)THEN'Tens'
WHEN(line_number < 1000)THEN'Hundreds'
ELSE'Thousands'
ENDRESULT_SET
FROMairplanes;
The above demo turned into a viewCREATEORREPLACEVIEW line_number_view AS
SELECTline_number,
CASEWHEN(line_number < 10)THEN'Ones'
WHEN(line_number < 100)THEN'Tens'
WHEN(line_number < 1000)THEN'Hundreds'
ELSE'Thousands'
ENDRESULT_SET
FROMairplanes;
CASE with BOOLEANSset serveroutput on

DECLARE
boolvarBOOLEAN:=TRUE;
BEGIN
dbms_output.put_line(CASEboolvarWHENTRUETHEN'TRUE'WHENFALSETHEN
'FALSE'END);
END;
/CASE - DECODE Comparison
The same functionality written using both functionsSELECTparameter,DECODE(SIGN(parameter-1000),-1,'C','P')ASBAND
FROMparameter_table;

SELECTparameter,
CASEWHENparameter < 1000THEN'C'ELSE'P'ENDASBAND
FROMparameter_table;
Another example using SIGNSELECTmin_cached,COUNT(*),ROUND(AVG(executions),2)
FROM(
SELECTDECODE(min_cached,'1) 00-01 min',
1,'2) 01-02min',
2,
DECODE(SIGN(min_cached -6),'3) 03-05min',
DECODE(SIGN(min_cached -16),'4) 06-15min',
DECODE(SIGN(min_cached -31),'5) 16-30min',
DECODE(SIGN(min_cached -61),'6) 31-60min',
DECODE(SIGN(min_cached-121),'7) 1-2hrs',
'8) 2 hrs+ ')))))) min_cached,
executions
FROM(
SELECTROUND((SYSDATE-
TO_DATE(first_load_time,'YYYY-MM-DD/HH24:MI:SS'))*24*60) min_cached,
executions
FROMgv$sqlarea
WHEREparsing_user_id != 0)
)
GROUP BYmin_cached

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

链接