《Oracle 特色函数及复杂更新应用.pptx》由会员分享，可在线阅读，更多相关《Oracle 特色函数及复杂更新应用.pptx（47页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Oracle Oracle Oracle Oracle 特色函数及复杂特色函数及复杂特色函数及复杂特色函数及复杂更新应用更新应用更新应用更新应用目标目标今天主要介绍一下日常操作中可能会用上Oracle的特色函数:转换函数：to_char通用函数：nvl、nullif、nvl2、coalesce、decode日期函数：months_between、add_months、next_day、last_day通过Round、Trunc函数 操作日期复杂的Update Table应用显式数据类型转换显式数据类型转换NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_
2、DATETO_CHAR 函数对日期的转换函数对日期的转换TO_CHAR(date,format_model)格式:v必须包含在单引号中而且大小写敏感。v可以包含任意的有效的日期格式。v可以使用 fm 去掉多余的空格或者前导零。v与日期指用逗号隔开。YYYY日期格式的元素日期格式的元素YEARMMMONTHDYDAY2004TWO THOUSAND AND FOUR02MONMONDAYJULYMONJULDD02练习v 如何用SQL语句返回以下格式（假设今天是2010-9-13）2010年9月13日13/09/102010/9/132010年的第37周2010年的第256天2010年的第37周
3、星期一TO_CHAR 函数对数字的转换函数对数字的转换下面是在TO_CHAR 函数中经常使用的几种格式:TO_CHAR(number,format_model)90$L.,数字数字零零美元符美元符本地货币符号本地货币符号小数点小数点千位符千位符练习练习v怎样把一个随意的数字转换成以下格式（小于一千万）：235678.9851-235,678.99235678.9851-￥235,678.998-通用函数通用函数这些函数适用于任何数据类型，同时也适用于空值：vNVL(expr1,expr2)vNVL2(expr1,expr2,expr3)vNULLIF(expr1,expr2)vCOALESCE
4、(expr1,expr2,.,exprn)NVL 函数函数将空值转换成一个已知的值：v可以使用的数据类型有日期、字符、数字。v函数的一般形式:NVL(commission_pct,0)NVL(hire_date,01-JAN-97)NVL(job_id,No Job Yet)NVL2 函数函数判断值是否为空，如果值不为空则返回第一个表达式，否则返回第二个表达式：v可以使用的数据类型有日期、字符、数字。v函数的一般形式:NVL2(value,exp1,exp2)NULLIF NULLIF 函数函数vNULLIF(exp1,exp2)vCASE WHEN exp1=exp2 THEN NULL E
5、LSE exp1 ENDv比较两个表达式，如果它们相等则返回空值，否则返回表达式一v注意：表达式一不能为空COALESCE 函数函数vCOALESCE(exp1,exp2,expN)vCOALESCE返回表达式列表中第一个不为空的表达式vCOALESCE(expr1,expr2)vCASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 ENDvCOALESCE(expr1,expr2,.,exprn),for n=3vCASE WHEN expr1 IS NOT NULL THEN expr1 ELSE COALESCE(expr2,.,exprn)
6、ENDCASE 表达式表达式在需要使用 IF-THEN-ELSE 逻辑时:CASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_exprENDDECODE(expr,comparison_expr1,return_expr1,comparison_expr2,return_expr2,comparison_exprn,return_exprn,else_expr)日期函数
7、日期函数两日期相差多少月两日期相差多少月MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC 加月份以日期加月份以日期指定日期的下一天指定日期的下一天一个月中的最后一天一个月中的最后一天Round 日期日期Truncate 日期日期函数函数描述描述练习练习-日期运算日期运算v得到2010-5-10至2010-9-13经过了多少天v得到2010-5-10至2010-9-13经过了多少个月v得到某个日期两个月后的日期v得到某个日期这天后100天的日期v得到某个日期两个月后的那个月的最后一天高级更新操作高级更新操作v提出疑问如何实现以下SQL Serv
8、er中的Update操作举例高级更新操作高级更新操作v提出疑问有以下表结构，如何通过一个语句达到以下功能：把A表的数据同步到B表，以ID字段为主键，如果B表中没有A表的记录，请插入合并语句合并语句v按照指定的条件执行插入或删除操作v如果满足条件的行存在，执行更新操作；否则执行插入操作:避免多次重复执行插入和删除操作提高效率而且使用方便在数据仓库应用中经常使用合并语句的语法合并语句的语法可以使用merge语句，根据指定的条件进行插入或更新操作MERGE INTO table_name table_alias USING(table|view|sub_query)alias ON(join con
9、dition)WHEN MATCHED THEN UPDATE SET col1=col_val1,col2=col2_val WHEN NOT MATCHED THEN INSERT(column_list)VALUES(column_values);MERGE INTO copy_emp c USING employees e ON(c.employee_id=e.employee_id)WHEN MATCHED THEN UPDATE SET c.first_name =e.first_name,c.last_name =e.last_name,.c.department_id =e.d
10、epartment_idWHEN NOT MATCHED THEN INSERT VALUES(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);合并语句举例合并语句举例在对表COPY_EMP使用merge语句，根据指定的条件从表 EMPLOYEES中插入或更新数据。合并语句举例合并语句举例MERGE INTO copy_emp c USING employees e ON(c.
11、employee_id=e.employee_id)WHEN MATCHED THEN UPDATE SET .WHEN NOT MATCHED THEN INSERT VALUES.;SELECT*FROM COPY_EMP;no rows selectedSELECT*FROM COPY_EMP;20 rows selected.演讲完毕，谢谢观看！Oracle ERPOracle ERP主要模主要模块块 InvoicesARENGBOMWIPBills andRoutingsShipmentsECOSWorkOrdersGLAPPlanning(MRP)POMaterialRequire
12、mentsEnterpriseDemandVendor POInterplant POReceiptsWorkOrders CustomersJournal EntriesJournal EntriesJournal EntriesVendor InvoicesVendor PaymentsTransportationInformationBillsItemsOEINVINVInternalPaymentsCorporateJournal EntriesShipments OrdersFAMass AdditionJournal EntriesCOSTCOSTCOSTReceipts财务配销H
13、RPayroll制造人力资源财务系统模块财务系统模块财务系统模块财务系统模块:Oracle Oracle 总帐管理（总帐管理（总帐管理（总帐管理（GLGL）Oracle Oracle 应付帐管理（应付帐管理（应付帐管理（应付帐管理（APAP）Oracle Oracle 固定资产管理（固定资产管理（固定资产管理（固定资产管理（FAFA）Oracle Oracle 应收帐管理（应收帐管理（应收帐管理（应收帐管理（ARAR）Oracle Oracle 现金管理（现金管理（现金管理（现金管理（CECE）Oracle Oracle 项目会计（项目会计（项目会计（项目会计（PAPA）Oracle Orac
14、le 财务分析（财务分析（财务分析（财务分析（OFAOFA）分销系统模块分销系统模块分销系统模块分销系统模块:Oracle Oracle 库存管理（库存管理（库存管理（库存管理（INVINV）Oracle Oracle 采购管理（采购管理（采购管理（采购管理（PURPUR）Oracle Oracle 销售定单管理（销售定单管理（销售定单管理（销售定单管理（OEOE）Oracle Oracle 销售销售销售销售&市场管理（市场管理（市场管理（市场管理（SMSM）Oracle Oracle 销售补偿管理（销售补偿管理（销售补偿管理（销售补偿管理（SCSC）Oracle Oracle 售后服务管理（
15、售后服务管理（售后服务管理（售后服务管理（SRSR）Oracle Oracle 销售分析（销售分析（销售分析（销售分析（OSAOSA）Oracle ERP Oracle ERP 模块清单模块清单制造系统模块制造系统模块制造系统模块制造系统模块:Oracle Oracle 计划管理（计划管理（计划管理（计划管理（MPS/MRPMPS/MRP）Oracle Oracle 能力计划管理（能力计划管理（能力计划管理（能力计划管理（CAPCAP）Oracle Oracle 制造数据管理（制造数据管理（制造数据管理（制造数据管理（BOMBOM）Oracle Oracle 车间生产管理（车间生产管理（车间生
16、产管理（车间生产管理（WIPWIP）Oracle Oracle 成本管理（成本管理（成本管理（成本管理（CSTCST）Oracle Oracle 项目制造（项目制造（项目制造（项目制造（PJM)PJM)Oracle Oracle 流程制造流程制造流程制造流程制造 (OPM)(OPM)Oracle Oracle 质量管理（质量管理（质量管理（质量管理（QM)QM)其他系统模块其他系统模块其他系统模块其他系统模块:Oracle Oracle 设备管理（设备管理（设备管理（设备管理（EMEM）Oracle Oracle 人事管理（人事管理（人事管理（人事管理（HRHR）Oracle Oracle 薪
17、金管理（薪金管理（薪金管理（薪金管理（PAYROLLPAYROLL）Oracle Oracle 系统管理（系统管理（系统管理（系统管理（SYSTEM ADMINSYSTEM ADMIN）Oracle Oracle 预警（预警（预警（预警（ALTALT）Oracle Oracle 多维数据分析多维数据分析多维数据分析多维数据分析/商业智能系统商业智能系统商业智能系统商业智能系统 （OLAP/BISOLAP/BIS）OOracle racle 桌面集成（桌面集成（桌面集成（桌面集成（ADIADI）OOracle ERP racle ERP 模块清单模块清单Oracle ApplicationsOr
18、acle Applications2828Oracle Oracle 行业模型行业模型行业模型行业模型:离散制造离散制造离散制造离散制造供应链供应链供应链供应链企业后台管理企业后台管理企业后台管理企业后台管理客户关系管理客户关系管理客户关系管理客户关系管理计划计划计划计划 寻源寻源寻源寻源 制造制造制造制造 市场市场市场市场 销售销售销售销售 支持支持支持支持企业管理企业管理企业管理企业管理返回返回返回返回项目到盈利项目到盈利项目到盈利项目到盈利财务管理到财财务管理到财财务管理到财财务管理到财务报告务报告务报告务报告概念到发布概念到发布概念到发布概念到发布联系到解决联系到解决联系到解决联系到解
19、决合同到续签合同到续签合同到续签合同到续签预测到计划预测到计划预测到计划预测到计划采购到付款采购到付款采购到付款采购到付款需求到制造需求到制造需求到制造需求到制造 (离散离散离散离散)订单到收款订单到收款订单到收款订单到收款库存到履约库存到履约库存到履约库存到履约问题到预防问题到预防问题到预防问题到预防机会到订单机会到订单机会到订单机会到订单Oracle Oracle 主要业务流程主要业务流程Design to ReleaseForecast to PlanPlan to ScheduleSchedule to buildProcure to PayOrder to CashInventory
20、 to FulfillmentCostQuality3030概念到发布概念到发布概念到发布概念到发布返回返回返回返回设计到发布设计到发布设计到发布设计到发布预测到计划预测到计划预测到计划预测到计划计划到排程计划到排程计划到排程计划到排程排程到生产排程到生产排程到生产排程到生产 (离散离散离散离散)概念到获利概念到获利概念到获利概念到获利一级业务流程一级业务流程一级业务流程一级业务流程3131预测到计划预测到计划预测到计划预测到计划返回返回返回返回供应商协同供应商协同供应商协同供应商协同到供应商承诺到供应商承诺到供应商承诺到供应商承诺客户协同客户协同客户协同客户协同到达成一致预测到达成一致预测到
21、达成一致预测到达成一致预测预测到计划预测到计划预测到计划预测到计划计划到排程计划到排程计划到排程计划到排程排程到生产排程到生产排程到生产排程到生产 (离散离散离散离散)请求到接收请求到接收请求到接收请求到接收 直接直接直接直接设计到发布设计到发布设计到发布设计到发布一级业务流程一级业务流程一级业务流程一级业务流程3232采购到支付采购到支付采购到支付采购到支付 返回返回返回返回分析到协议分析到协议分析到协议分析到协议预测到计划预测到计划预测到计划预测到计划申请到接收申请到接收申请到接收申请到接收 直接直接直接直接申请到接收申请到接收申请到接收申请到接收 间接间接间接间接供应商退货到贷项通供应商
22、退货到贷项通供应商退货到贷项通供应商退货到贷项通知单知单知单知单供应商退货到补货供应商退货到补货供应商退货到补货供应商退货到补货验收到接收验收到接收验收到接收验收到接收接收到资产接收到资产接收到资产接收到资产供应商发票到付款供应商发票到付款供应商发票到付款供应商发票到付款补货信号补货信号补货信号补货信号费用报告到入帐费用报告到入帐费用报告到入帐费用报告到入帐一级业务流程一级业务流程一级业务流程一级业务流程3333需求到制造需求到制造需求到制造需求到制造(离散离散离散离散)返回返回返回返回一级业务流程一级业务流程一级业务流程一级业务流程返回返回返回返回计划到排程计划到排程计划到排程计划到排程排程
23、到生产排程到生产排程到生产排程到生产 (离散离散离散离散)检验到处理检验到处理检验到处理检验到处理设计到发布设计到发布设计到发布设计到发布按订单配置到发运按订单配置到发运按订单配置到发运按订单配置到发运按订单装配到发运按订单装配到发运按订单装配到发运按订单装配到发运一级业务流程一级业务流程一级业务流程一级业务流程3434订单到收款订单到收款订单到收款订单到收款 返回返回返回返回订单到直发订单到直发订单到直发订单到直发信贷审批到客户信贷审批到客户信贷审批到客户信贷审批到客户拣货到订单到发运拣货到订单到发运拣货到订单到发运拣货到订单到发运客户退货到退款客户退货到退款客户退货到退款客户退货到退款客户
24、退货到退换客户退货到退换客户退货到退换客户退货到退换客户发票客户发票客户发票客户发票-现金现金现金现金定制到订单到发运定制到订单到发运定制到订单到发运定制到订单到发运装配到订单到发运装配到订单到发运装配到订单到发运装配到订单到发运 销售线索到订单销售线索到订单销售线索到订单销售线索到订单订单到发运订单到发运订单到发运订单到发运订单到直接采购发运订单到直接采购发运订单到直接采购发运订单到直接采购发运收入到过帐收入到过帐收入到过帐收入到过帐订单到客户发票订单到客户发票订单到客户发票订单到客户发票一级业务流程一级业务流程一级业务流程一级业务流程3535库存到履约库存到履约库存到履约库存到履约返回返回
25、返回返回申请到接收申请到接收申请到接收申请到接收-直接直接直接直接排程到生产排程到生产排程到生产排程到生产(离散离散离散离散)检验到处理检验到处理检验到处理检验到处理产品标准成本产品标准成本产品标准成本产品标准成本到库存再估价到库存再估价到库存再估价到库存再估价订单到收款订单到收款订单到收款订单到收款接收到库存准确度接收到库存准确度接收到库存准确度接收到库存准确度补货信号到补货补货信号到补货补货信号到补货补货信号到补货子分类帐到过帐子分类帐到过帐子分类帐到过帐子分类帐到过帐仓库布局到库存估价仓库布局到库存估价仓库布局到库存估价仓库布局到库存估价计划到排程计划到排程计划到排程计划到排程一级业务流
26、程一级业务流程一级业务流程一级业务流程客户订单Customer PO销售预测Forecast主需求计划MDS主生产计划MPS物料需求计划MRP请购作业PR 采购作业PO 收料作业 Receiving工单开立WIP生产发料WIP Issue生产控制生产入库WIP Complete出货作业Shipping应付账款应收账款发票作业成本作业Oracle Oracle 整合整合企业日常作业流程企业日常作业流程可承诺量查核ATP内部领用捡货单杂项发料/退货Misc Issue/Return发料/出货Issue入库异动Receipt Transaction实际盘点Physical Counting标签与序列
27、号列印Tag Generation盘点过账交货预测与再订购管理Forecast and Reorder存货管制规则-Min-max-Reorder Point-Sub-inventory replenishment交货/预定安排库存评估/报表定义作业规定总帐更新定义作业流程组织/仓库定义Setup Organization/Subinventory检验Inspect登帐运送Deliver退货Return供应商Supplier内部Internal成本Cost状态Status目录Catalog分类Category模板Template供应商Supplier内部Internal订单处理自动请购余额更新
28、调整库存平衡表循环盘点Cycle Counting-自动排程-手动排程Oracle Inventory(Oracle Inventory(库存管理系统库存管理系统)库存控制InventoryControl物料管理Material Management收料Receipt联接操作制程料品定义Item定义资源Resource选用零配件/料品成本累计成品Finish Good定义资源使用量Resource Rate新产品流程建立替代制程Alternate Routing定义制程Routing材料需求Item Requirement新产品开发建立替代性物料清单Alternate BOM工单排程量产日排程
29、实施设计变更单Oracle Bill of Materials(Oracle Bill of Materials(Oracle Bill of Materials(Oracle Bill of Materials(物料清单及制程管理系统物料清单及制程管理系统物料清单及制程管理系统物料清单及制程管理系统)设计变更单ECO半成品Assembly建立物料清单BOM产品规格-型号、选项产生工单(或量产计划)完成品入库原物料耗用报表-工单成本-期间成本-成本差异-成本衡量-物料耗用-资源耗用组立装配Assembly(选项)外包反溯扣帐Backflash工单发放Release发料Issue装配完成工单完成
30、WIP Complete制造资源耗用Resource-人工-机器设备-制造费用送货/交货外包交货外包OutsideProcessing安排生产制程物料清单展开计划工单手开工单销售订单Oracle Work In Process(Oracle Work In Process(生产管理系生产管理系统统)预测资料ForecastOracle SO客户订单其它系统客户订单需求报表&查询主需求计划MDS需求排程更新MDS Update详细产能计划物料需求计划MRP工单WIP请购PRBOM计划工单计划采购单现有库存量OnhandOracle Planning(MPS/MRPOracle Planning(
31、MPS/MRP管理系统管理系统)主生产计划MPS交易资讯Transaction定义配件成本报告采购收料 PO Receive定义成本计算规则成本累计Cost Rollup成本更新Cost Update库存值计算采购料品材料材料管理费用用料清单制程 成本元件-工作项目/活动 Project/Active-料品Item-批次Lot-资源单位Uom-总价半成品库存资源成本采购退货PO Return公司间移转Organization Transfer移动平均成本更新杂项收发Miscellaneous Receipt/Issue选择模拟规则模拟冻结成本Frozen冻结成本Oracle Cost Mana
32、gement(Oracle Cost Management(Oracle Cost Management(Oracle Cost Management(成本管理系统成本管理系统成本管理系统成本管理系统)请购单处理收货(料)Receipt检验Inspect异动处理-收货-运送-调拨退货Return应付账款对帐应计作业-暂记项目-凭证-月结报价单收集请求报价RFQ(选择性项目)自动产生订购单发放Release定义企业流程-收料控制-核准及安全管制-交货来源-采购预算管制-异动处理方法内部转移InterOrg Transfer发票处理应付账款成立AP Invoice总帐科目更新核准Approve供应
33、商挑选供应商交货允收AcceptOracle Purchasing(Oracle Purchasing(采购管理系采购管理系统统)MRP需求存货状况Onhand Qty在制品(委外)OutProcess手开请购Manual PR开放界面 Interface列印/EDI核准Approve各种采购单-标准式Standard-计划式Planned-合并式Blanket-合约式Contract判退Reject选配件产品订单输入Order Enter发票开立Invoice出货管理Shipping退货管理RMA退货审核Approve货品退回Return订单完成重排程Re-schedule保留Reserva
34、tion库存余额更新未交订单管理定义各项流程次序定义流程元件定义企业运作流程Workflow-弹定规则订单处理流程-授信额度检查-退货管理-订单排程订单处理流程定义授信額度檢查Credit Check例外管理Exception订单保留Hold-自动Auto-手动Manual订单解除管制Hold Release订价Price订单审核确认客户基本资料Customer线上查询库存余量-允交量ATP-保留量Reservation授信额度检查Credit CheckOracle Order Entry(Oracle Order Entry(订单管理系统订单管理系统)允交量查询ATP订单排程Order S
35、chedule出货确认Ship Confirm检货Pick销货折让Credit分派/收集预算资料建立预算预算作业Budget查询报表产生器(FSG)定义企业作业模式/输出分析会计科目结构Accounting Structure分录/凭证登录日历Calendar多公司合并作业处理Consolidation多币别作业Multi-Currency应计分录/回转分录开放介面转入Interface科目调整作业結帳开启会计期间Open Accounting Period汇兑重整与实现Revaluation(多公司)合并分录作业Consolidation报表需求分析-财务性-管理性-例行性(年/月报)修订.
36、整理预算资料明细账反查稽核人工输入ManualOracle 其它系统转入Journal Import成本分配核准Approval过账PostOracle General Ledger(Oracle General Ledger(总帐管理系统总帐管理系统)付款优先顺序Priority员工资料查核作业流程资料输入会计总帐更改调整自动付款方式选择确认产生校对更正审阅期间关闭报表&查询 -查核勾稽 -税赋 -会计总帐科目采用采购对帐税别/币别管理数量/价格差异分析放行暂停支票资产界面Oracle Payables(Oracle Payables(应付帐款管理系统应付帐款管理系统)运费成本 应付账款立帐
37、作业Invoice供应商管理Supplier输入发票Invoice退货/折让证明Credit输入预付费用Prepayment输入员工报支Employee Expense 应付期间开启Payable Period Open核准文件Approve付款处理Payment杂项支出处理,报表及查询催收款Dunning Letter报表输出GL总帐更新过帐人工登录Oracle OE订单系统转入对帐单/催款单外接开放介面转入帐款比对-自动规则收款冲账作业Apply查询研究/重订作业流程定义企业作业流程运用流程规定信限与调整处理维护信限管理资料临时管理性报表税务与稽核明细客户收款日程设定线上即时查询银行对帐单
38、外币处理税务作业客户确认发票销货退回折让证明保证金/预收款项杂收Misc Receipts人工冲账印表/EDI 电子资料交换客户管理Customer自动冲账-邸箱(Lockbox)-汇票、L/C.等作业Oracle Receivables(Oracle Receivables(应收帐款管理系统应收帐款管理系统)应收帐款成立Transaction现金管理Cash Management资产增加折旧租赁追踪GL 總帳更新记录资产实体资料资本预算及预测价值估算会计科目确认所有权/管理/使用指派AP 应付系统介面转入人工输入开放介面转入资讯拮取模式资产变更作业运用一般折旧法则运用比例分摊运用比率CIP 在建工程资产作业费用性资产税务账册处理会计财务账册处理-ACE-Bonuses-Ceilings-ITC-International rates-Life-based rates-NBV Rates-Custom rules报销/复原作业重估资产变更大批变更责任变更处所变更大批转移税务报表线上查询财务报表Oracle Assets(Oracle Assets(固定资产管理系统固定资产管理系统)