Oracle 特色函數(shù)及復(fù)雜更新應(yīng)用課件

Oracle特色函數(shù)及復(fù)雜更新應(yīng)用Oracle特色函數(shù)及復(fù)雜更新應(yīng)用目標(biāo)今天主要介紹一下日常操作中可能會(huì)用上Oracle的特色函數(shù):轉(zhuǎn)換函數(shù)：to_char通用函數(shù)：nvl、nullif、nvl2、coalesce、decode日期函數(shù)：months_between、add_months、next_day、last_day通過Round、Trunc函數(shù)

操作日期復(fù)雜的UpdateTable應(yīng)用目標(biāo)今天主要介紹一下日常操作中可能會(huì)用上Oracle的特色函顯式數(shù)據(jù)類型轉(zhuǎn)換NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATE顯式數(shù)據(jù)類型轉(zhuǎn)換NUMBERCHARACTERTO_CHARTO_CHAR

函數(shù)對(duì)日期的轉(zhuǎn)換TO_CHAR(date,'format_model')格式:必須包含在單引號(hào)中而且大小寫敏感?？梢园我獾挠行У娜掌诟袷??？梢允褂?/p>

fm

去掉多余的空格或者前導(dǎo)零。與日期指用逗號(hào)隔開。TO_CHAR函數(shù)對(duì)日期的轉(zhuǎn)換TO_CHAR(date,YYYY日期格式的元素YEARMMMONTHDYDAY2004TWOTHOUSANDANDFOUR02MONMONDAYJULYMONJULDD02YYYY日期格式的元素YEARMMMONTHDYDAY200練習(xí)

如何用SQL語句返回以下格式（假設(shè)今天是2010-9-13）2010年9月13日13/09/102010/9/132010年的第37周2010年的第256天2010年的第37周星期一練習(xí)如何用SQL語句返回以下格式（假設(shè)今天是2010-9-TO_CHAR

函數(shù)對(duì)數(shù)字的轉(zhuǎn)換下面是在TO_CHAR函數(shù)中經(jīng)常使用的幾種格式:TO_CHAR(number,'format_model')90$L.,數(shù)字零美元符本地貨幣符號(hào)小數(shù)點(diǎn)千位符TO_CHAR函數(shù)對(duì)數(shù)字的轉(zhuǎn)換下面是在TO_CHAR函數(shù)練習(xí)怎樣把一個(gè)隨意的數(shù)字轉(zhuǎn)換成以下格式（小于一千萬）：235678.9851->235,678.99235678.9851->￥235,678.998->Ⅷ練習(xí)怎樣把一個(gè)隨意的數(shù)字轉(zhuǎn)換成以下格式（小于一千萬）：通用函數(shù)這些函數(shù)適用于任何數(shù)據(jù)類型，同時(shí)也適用于空值：NVL(expr1,expr2)NVL2(expr1,expr2,expr3)NULLIF(expr1,expr2)COALESCE(expr1,expr2,...,exprn)通用函數(shù)這些函數(shù)適用于任何數(shù)據(jù)類型，同時(shí)也適用于空值：NVL

函數(shù)將空值轉(zhuǎn)換成一個(gè)已知的值：可以使用的數(shù)據(jù)類型有日期、字符、數(shù)字。函數(shù)的一般形式:NVL(commission_pct,0)NVL(hire_date,'01-JAN-97')NVL(job_id,'NoJobYet')NVL函數(shù)將空值轉(zhuǎn)換成一個(gè)已知的值：NVL2

函數(shù)判斷值是否為空，如果值不為空則返回第一個(gè)表達(dá)式，否則返回第二個(gè)表達(dá)式：可以使用的數(shù)據(jù)類型有日期、字符、數(shù)字。函數(shù)的一般形式:NVL2(value,exp1,exp2)NVL2函數(shù)判斷值是否為空，如果值不為空則返回第一個(gè)表達(dá)式NULLIF函數(shù)NULLIF(exp1,exp2)CASEWHENexp1=exp2THENNULLELSEexp1END比較兩個(gè)表達(dá)式，如果它們相等則返回空值，否則返回表達(dá)式一注意：表達(dá)式一不能為空NULLIF函數(shù)NULLIF(exp1,exp2)COALESCE

函數(shù)COALESCE(exp1,exp2,…,expN)COALESCE返回表達(dá)式列表中第一個(gè)不為空的表達(dá)式COALESCE(expr1,expr2)CASEWHENexpr1ISNOTNULLTHENexpr1ELSEexpr2ENDCOALESCE(expr1,expr2,...,exprn),forn>=3CASEWHENexpr1ISNOTNULLTHENexpr1ELSECOALESCE(expr2,...,exprn)ENDCOALESCE函數(shù)COALESCE(exp1,exp2CASE

表達(dá)式在需要使用

IF-THEN-ELSE邏輯時(shí):CASEexprWHENcomparison_expr1THENreturn_expr1

[WHENcomparison_expr2THENreturn_expr2WHENcomparison_exprnTHENreturn_exprnELSEelse_expr]ENDDECODE(expr,comparison_expr1,return_expr1,comparison_expr2,return_expr2,comparison_exprn,return_exprn,else_expr)CASE表達(dá)式在需要使用IF-THEN-ELSE邏輯時(shí)日期函數(shù)兩日期相差多少月MONTHS_BETWEENADD_MONTHSNEXT_DAY LAST_DAYROUND TRUNC 加月份以日期指定日期的下一天一個(gè)月中的最后一天Round日期Truncate日期函數(shù)描述日期函數(shù)兩日期相差多少月MONTHS_BETWEENADD_練習(xí)-日期運(yùn)算得到2010-5-10至2010-9-13經(jīng)過了多少天得到2010-5-10至2010-9-13經(jīng)過了多少個(gè)月得到某個(gè)日期兩個(gè)月后的日期得到某個(gè)日期這天后100天的日期得到某個(gè)日期兩個(gè)月后的那個(gè)月的最后一天練習(xí)-日期運(yùn)算得到2010-5-10至2010-9-13經(jīng)過高級(jí)更新操作提出疑問如何實(shí)現(xiàn)以下SQLServer中的Update操作舉例高級(jí)更新操作提出疑問高級(jí)更新操作提出疑問有以下表結(jié)構(gòu)，如何通過一個(gè)語句達(dá)到以下功能：把A表的數(shù)據(jù)同步到B表，以ID字段為主鍵，如果B表中沒有A表的記錄，請(qǐng)插入高級(jí)更新操作提出疑問合并語句按照指定的條件執(zhí)行插入或刪除操作如果滿足條件的行存在，執(zhí)行更新操作；否則執(zhí)行插入操作:避免多次重復(fù)執(zhí)行插入和刪除操作提高效率而且使用方便在數(shù)據(jù)倉(cāng)庫(kù)應(yīng)用中經(jīng)常使用合并語句按照指定的條件執(zhí)行插入或刪除操作合并語句的語法可以使用merge語句，根據(jù)指定的條件進(jìn)行插入或更新操作MERGEINTOtable_name

table_aliasUSING(table|view|sub_query)aliasON(joincondition)WHENMATCHEDTHENUPDATESET

col1=col_val1,col2=col2_valWHENNOTMATCHEDTHENINSERT(column_list)VALUES(column_values);合并語句的語法可以使用merge語句，根據(jù)指定的條件進(jìn)行插入MERGEINTOcopy_empcUSINGemployeeseON(c.employee_id=e.employee_id)WHENMATCHEDTHENUPDATESETc.first_name=e.first_name,c.last_name=e.last_name,...c.department_id=e.department_idWHENNOTMATCHEDTHENINSERTVALUES(e.employee_id,e.first_name,e.last_name,e.email,e.phone_number,e.hire_date,e.job_id,e.salary,mission_pct,e.manager_id,e.department_id);合并語句舉例在對(duì)表COPY_EMP使用merge語句，根據(jù)指定的條件從表

EMPLOYEES中插入或更新數(shù)據(jù)。MERGEINTOcopy_empc合并語句舉例在對(duì)合并語句舉例MERGEINTOcopy_empcUSINGemployeeseON(c.employee_id=e.employee_id)WHENMATCHEDTHENUPDATESET...WHENNOTMATCHEDTHENINSERTVALUES...;SELECT*FROMCOPY_EMP;norowsselectedSELECT*FROMCOPY_EMP;20rowsselected.合并語句舉例MERGEINTOcopy_empcSELOracle特色函數(shù)及復(fù)雜更新應(yīng)用Oracle特色函數(shù)及復(fù)雜更新應(yīng)用目標(biāo)今天主要介紹一下日常操作中可能會(huì)用上Oracle的特色函數(shù):轉(zhuǎn)換函數(shù)：to_char通用函數(shù)：nvl、nullif、nvl2、coalesce、decode日期函數(shù)：months_between、add_months、next_day、last_day通過Round、Trunc函數(shù)

操作日期復(fù)雜的UpdateTable應(yīng)用目標(biāo)今天主要介紹一下日常操作中可能會(huì)用上Oracle的特色函顯式數(shù)據(jù)類型轉(zhuǎn)換NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATE顯式數(shù)據(jù)類型轉(zhuǎn)換NUMBERCHARACTERTO_CHARTO_CHAR

函數(shù)對(duì)日期的轉(zhuǎn)換TO_CHAR(date,'format_model')格式:必須包含在單引號(hào)中而且大小寫敏感?？梢园我獾挠行У娜掌诟袷??？梢允褂?/p>

fm

去掉多余的空格或者前導(dǎo)零。與日期指用逗號(hào)隔開。TO_CHAR函數(shù)對(duì)日期的轉(zhuǎn)換TO_CHAR(date,YYYY日期格式的元素YEARMMMONTHDYDAY2004TWOTHOUSANDANDFOUR02MONMONDAYJULYMONJULDD02YYYY日期格式的元素YEARMMMONTHDYDAY200練習(xí)

如何用SQL語句返回以下格式（假設(shè)今天是2010-9-13）2010年9月13日13/09/102010/9/132010年的第37周2010年的第256天2010年的第37周星期一練習(xí)如何用SQL語句返回以下格式（假設(shè)今天是2010-9-TO_CHAR

函數(shù)對(duì)數(shù)字的轉(zhuǎn)換下面是在TO_CHAR函數(shù)中經(jīng)常使用的幾種格式:TO_CHAR(number,'format_model')90$L.,數(shù)字零美元符本地貨幣符號(hào)小數(shù)點(diǎn)千位符TO_CHAR函數(shù)對(duì)數(shù)字的轉(zhuǎn)換下面是在TO_CHAR函數(shù)練習(xí)怎樣把一個(gè)隨意的數(shù)字轉(zhuǎn)換成以下格式（小于一千萬）：235678.9851->235,678.99235678.9851->￥235,678.998->Ⅷ練習(xí)怎樣把一個(gè)隨意的數(shù)字轉(zhuǎn)換成以下格式（小于一千萬）：通用函數(shù)這些函數(shù)適用于任何數(shù)據(jù)類型，同時(shí)也適用于空值：NVL(expr1,expr2)NVL2(expr1,expr2,expr3)NULLIF(expr1,expr2)COALESCE(expr1,expr2,...,exprn)通用函數(shù)這些函數(shù)適用于任何數(shù)據(jù)類型，同時(shí)也適用于空值：NVL

函數(shù)將空值轉(zhuǎn)換成一個(gè)已知的值：可以使用的數(shù)據(jù)類型有日期、字符、數(shù)字。函數(shù)的一般形式:NVL(commission_pct,0)NVL(hire_date,'01-JAN-97')NVL(job_id,'NoJobYet')NVL函數(shù)將空值轉(zhuǎn)換成一個(gè)已知的值：NVL2

函數(shù)判斷值是否為空，如果值不為空則返回第一個(gè)表達(dá)式，否則返回第二個(gè)表達(dá)式：可以使用的數(shù)據(jù)類型有日期、字符、數(shù)字。函數(shù)的一般形式:NVL2(value,exp1,exp2)NVL2函數(shù)判斷值是否為空，如果值不為空則返回第一個(gè)表達(dá)式NULLIF函數(shù)NULLIF(exp1,exp2)CASEWHENexp1=exp2THENNULLELSEexp1END比較兩個(gè)表達(dá)式，如果它們相等則返回空值，否則返回表達(dá)式一注意：表達(dá)式一不能為空NULLIF函數(shù)NULLIF(exp1,exp2)COALESCE

函數(shù)COALESCE(exp1,exp2,…,expN)COALESCE返回表達(dá)式列表中第一個(gè)不為空的表達(dá)式COALESCE(expr1,expr2)CASEWHENexpr1ISNOTNULLTHENexpr1ELSEexpr2ENDCOALESCE(expr1,expr2,...,exprn),forn>=3CASEWHENexpr1ISNOTNULLTHENexpr1ELSECOALESCE(expr2,...,exprn)ENDCOALESCE函數(shù)COALESCE(exp1,exp2CASE

表達(dá)式在需要使用

IF-THEN-ELSE邏輯時(shí):CASEexprWHENcomparison_expr1THENreturn_expr1

[WHENcomparison_expr2THENreturn_expr2WHENcomparison_exprnTHENreturn_exprnELSEelse_expr]ENDDECODE(expr,comparison_expr1,return_expr1,comparison_expr2,return_expr2,comparison_exprn,return_exprn,else_expr)CASE表達(dá)式在需要使用IF-THEN-ELSE邏輯時(shí)日期函數(shù)兩日期相差多少月MONTHS_BETWEENADD_MONTHSNEXT_DAY LAST_DAYROUND TRUNC 加月份以日期指定日期的下一天一個(gè)月中的最后一天Round日期Truncate日期函數(shù)描述日期函數(shù)兩日期相差多少月MONTHS_BETWEENADD_練習(xí)-日期運(yùn)算得到2010-5-10至2010-9-13經(jīng)過了多少天得到2010-5-10至2010-9-13經(jīng)過了多少個(gè)月得到某個(gè)日期兩個(gè)月后的日期得到某個(gè)日期這天后100天的日期得到某個(gè)日期兩個(gè)月后的那個(gè)月的最后一天練習(xí)-日期運(yùn)算得到2010-5-10至2010-9-13經(jīng)過高級(jí)更新操作提出疑問如何實(shí)現(xiàn)以下SQLServer中的Update操作舉例高級(jí)更新操作提出疑問高級(jí)更新操作提出疑問有以下表結(jié)構(gòu)，如何通過一個(gè)語句達(dá)到以下功能：把A表的數(shù)據(jù)同步到B表，以ID字段為主鍵，如果B表中沒有A表的記錄，請(qǐng)插入高級(jí)更新操作提出疑問合并語句按照指定的條件執(zhí)行插入或刪除操作如果滿足條件的行存在，執(zhí)行更新操作；否則執(zhí)行插入操作:避免多次重復(fù)執(zhí)行插入和刪除操作提高效率而且使用方便在數(shù)據(jù)倉(cāng)庫(kù)應(yīng)用中經(jīng)常使用合并語句按照指定的條件執(zhí)行插入或刪除操作合并語句的語法可以使用merge語句，根據(jù)指定的條件進(jìn)行插入或更新操作MERGEINTOtable_name

table_aliasUSING(table|view|sub_query)aliasON(joincondition)WHENMATCHEDTHENUPDATESET

col1=col_val1,col2=col2_valWHENNOTMATCHEDTHENINSERT(column_list)VALUES(column_values);合并語句的語法可以使用merge語句，根據(jù)指定