Oracle上機指南文檔

數據庫應用技術

Oracle上機指南目錄軟件與使用說明輸入sql的注意事項練習一練習二練習三練習四軟件使用通常，在oracle客戶端使用sqlplus作為標準的命令行工具需要安裝oracle的客戶端驅動以前經常被使用，基于命令行，但使用不方便在無圖形模式下是第一選擇sqldeveloper一個基于java的圖形化界面工具無需驅動安裝SQLDeveloper我們要使用的SQLDeveloper是一個基于Java的圖形界面的SQL執(zhí)行工具，使用比較方便。雙擊d:\oracli\sqldeveloper目錄下面的sqldeveloper.exe即可執(zhí)行第一次執(zhí)行時可能會詢問java目錄，請輸入C:\ProgramFiles\Java\jdk\bin（依賴于機房環(huán)境，可能略有不同）SQLDeveloper的界面SQLDeveloper的界面組成左上方是連接設置，以及連接后數據庫對象瀏覽樹。右面上方的頁簽說明可以有多個待編輯的SQL腳本。右上方是輸入SQL的部分。右面中下是SQL語句執(zhí)行結果的顯示區(qū)域右下方顯示其他信息。SQLDeveloper－建立連接右擊左上角的Connections，選擇new在彈出的如右圖所示對話框中輸入服務器信息。包括用戶名和密碼服務器名根據機房情況而定SID部分必須輸入ORCL可以使用test按鈕進行測試。使用connect按鈕連接注意，連接時間可能比較長連接成功以后可以直接點擊相應的連接name登錄數據庫服務器IP:192.168.8.240必須是ORCLSQLDeveloper－連接后左上角是這個數據庫的各個數據庫對象（當前用戶可見）可以點擊各個對象，直接查看對象內容常用操作第一次連接后右側為輸入SQL命令的子窗口如不慎關閉右擊左側連接，OpenSQLWorksheet直接在Worksheet上面輸入，每個SQL語句以分號結尾點擊工具欄上第一個按鈕（或F9），執(zhí)行當前語句點擊工具欄上第二個按鈕（或F5），批量執(zhí)行輸入框中所有語句查詢數據查詢的結果是本用戶權限下可見的數據；其他會話修改但未提交的數據不能看到（讀提交）。如果有授權，可以查詢其他用戶的表(和視圖)teacher用戶下，有我們上課的示例表和數據。所有用戶可以查詢，但不能修改SELECT*FROMTEACHER.SALES;按照范例建自己的表CREATETABLECUSTOMERSASSELECT*FROMTEACHER.CUSTOMERSSQL注意事項修改密碼alteruser用戶名identifiedby新密碼利用SQL進行純粹的計算，可以使用DUAL表SELECT1+2FROMDUAL查看當前用戶下有哪些表SELECT*FROMTAB;SELECTOWNER,TABLE_NAMEFROMALL_TABLES;復雜的PL/SQL命令中可能需要使用/表示整個語句結束。日期格式缺省日期格式為簡單格式，如例所示可以使用TO_CHAR函數進行調整可以使用ALTERSESSION命令改變當前會話的缺省格式輸入日期時要按照缺省格式或者使用TO_DATE函數SELECTSYSDATEFROMDUAL;SYSDATE----------09-5月-05SELECTTO_CHAR(SYSDATE,'YYYY/MM/DDHH24:MI:SS')FROMDUAL;ALTERSESSIONSETNLS_DATE_FORMAT='YYYY/MM/DDHH24:MI:SS';SELECTSYSDATEFROMDUAL;SYSDATE-------------------2005/05/0901:52:10練習一各組分別領取數據庫用戶名和密碼，組內修改密碼登錄SQLDeveloper，掌握基本使用使用SELECT語句完成后面列出的例題和習題各個數據來源均可以使用teacher用戶下面的示例表也可以在自己創(chuàng)建的同樣表格上執(zhí)行標有*號的題目較難例題及習題－基本查詢例1：查詢所有產品信息例2：查詢ID為‘A01’的銷售員例3：查詢名字以’S’開頭的銷售員例4：查詢購買總金額在500和1000之間的不重復的商品ID例4：列出每個銷售員名字的大寫和薪水＋100的值例5：列出當前時間和1＋2的值例6：列出地址為空的顧客信息，按姓名升序排列。例7：按照“MaryInNewYork”的樣式用一個表達式列出所有顧客名字和城市信息。城市為空用’(N/A)’表示，按名字降序排列。(需使用NVL函數)例題及習題－連接例1：找出每個顧客購買的商品名稱和數量；例2：找出住在’Dallas’或’Duluth’的顧客購買的產品號；例3：列出每個銷售員及其經理的姓名；例4：找出在同一城市居住的顧客對；例5：找出比’Smith’工資高的銷售員；例6：寫出購買商品的顧客姓名和產品名稱；例7：寫出購買單價1元的產品的顧客名單；例8：找出與經理在同一城市的銷售員；例9(*)：列出7天內至少購買過兩次產品的CID例題及習題－聚組函數例1：當前有多少訂單；例2：當前LOCATIONS內有多少個郵編（可以重復）？例3：有多少個顧客購買過產品？例4：列出每一個顧客ID，總購買額。例5：列出每一個顧客ID，姓名及總購買額。例6：單筆金額超過500的訂單被稱為大訂單，只考慮大的訂單，按照產品計算平均銷售額。例7：計算平均銷售額大于700的各種產品及總銷售額。例8：求至少有兩次大訂單的顧客ID及大訂單總金額。（能否求訂單總額？）例9：按產品分類，總銷售額最高值是多少？例題及習題－子查詢練習二復雜的SQL語句TOP-N問題DML語句DML與事務2.1復雜SELECT2.2DML語句和事務DML語句執(zhí)行后，本次會話數據暫時修改其他會話仍使用修改之前數據使用COMMIT命令提交修改使用ROLLBACK命令撤銷修改習題過程－I自己建立一個例子表向表中插入數據，練習insert語句如果插入語句沒有錯誤，使用select查詢此表，檢查數據。注意省略字段的值。請同組其他同學查詢自己的例子表，檢查有無第二步增加數據？輸入COMMIT命令，然后重復第4步。重復第2步，增加新的數據，然后執(zhí)行ROLLBACK命令。使用select檢查表中數據。習題過程－II在自己建立的表中修改某行的值，注意UPDATE語法請同組另外同學修改同一個表的同一行數據，觀察現象。在自己機器上COMMIT或者ROLLBACK，同時觀察第2步同學機器練習DELETE語句仿照課堂例子，完成從AGENTS表到ACOPY表的同步練習三建立各種數據庫對象設計一套學生借書數據庫應用系統表格（帶有約束），并實現試驗各種約束的作用。當修改后的數據學習使用視圖來簡化查詢試驗索引的創(chuàng)建習題過程－III設計一套學生借書數據庫應用系統表格（帶有約束）。一種方案如下：學生信息表、書籍信息表（包括總冊數、已借出數）、學生借還書日志（包括學號、書號、借/還標識、時間等）。使用CREATETABLE等語句實現上面的設計在這個設計上，建立適當的視圖（如學生借書總數、書被借閱次數）。練習通過視圖查詢（如查詢最常被借閱的書籍）考慮這個系統中索引的設計練習四PL/SQL的使用練習PL/SQL的使用練習創(chuàng)建存儲過程和函數執(zhí)行存儲過程和函數建立觸發(fā)器初步學習如何調試存儲過程。建立存儲過程舉例將存儲過程代碼直接在PLSQL-Developer的SQL窗口中加入，并執(zhí)行(F5)如果沒有拼寫錯誤，輸出欄會提示（proceduresp1Compiled.）否則提示（Warning:執(zhí)行完畢,但帶有警告）下面先建立一個表spt1，如右側例子CREATETABLESPT1(C1DATE,C2INT,C3CHAR(10));建立存儲過程舉例按照右側代碼輸入，注意各行的;和最后的/輸入完成后執(zhí)行。如無錯誤繼續(xù)輸入（將所有輸入刪除后）

executesp1;select*fromspt1;批量執(zhí)行（F5）。會在輸出中看到spt1表中增加了一行記錄createorreplaceproceduresp1asBEGINDECLAREv1integer;BEGINselectcount(*)intov1fromorders;

insertintospt1(c1,c2,c3)values(sysdate,v1,'test');

END;END;/存儲過程調試如果輸入有錯誤（包括語法錯誤等），該存儲過程依然被建立，但處于不可用的狀態(tài)。提示

proceduresp1Compiled.

Warning:執(zhí)行完畢,但帶有警告錯誤信息存儲在user_errors表中，用戶可以通過select*fromuser_errors來查詢該表只保存最后一次的錯誤信息Oracle的錯誤信息可讀性不強，需要仔細檢查執(zhí)行存儲過程在SQL輸入窗口使用EXECUTE過程名可以執(zhí)行，但必須使用（RunScriptF5）來執(zhí)行可以使用類似下面語法，作為一條命令執(zhí)行（F9），隨后一行一定要加一個／，作為PL/SQL的結束標志beginsp1;end;／使用PL/SQLDeveloper管理存儲過程在左側的目錄中找到Procedures，按右鍵選擇新建經過一個向導對話框之后，會有一個編輯窗口，并且直接有PL/SQL的基本框架代碼，用戶可直接在此基礎上修改修改完成后，首先選擇工具欄中的“Compile”，如有錯誤，會直接出現在下方編譯成功后，可以按Run來運行編譯好的存儲過程，可以在左側目錄中通過“右鍵-Edit”來修改在存儲過程中輸出調試信息通常，存儲過程的結果會體現在數據庫的表中。在復雜程序中，我們可能需要知道運行過程中變量值等，來方便調試可以在存儲過程中使用Dbms_output.put_line函數來產生調試信息?？梢栽贒BMSOutput窗口查看調試信息在存儲過程中輸出調試信息修改過程sp1，加入紅色的行。在SQL輸入窗口中，輸入setserveroutputon;并執(zhí)行此命令的作用是開啟調試輸出，并且執(zhí)行后對該次會話均有效輸入Executesp1，并執(zhí)行（F5)找到下方的DBMSOutput子窗口，在里面可以看到v1的值已經輸出。createorreplaceproceduresp1asBEGINDECLAREv1integer;BEGINselectcount(*)intov1fromorders;

dbms_output.put_line(‘v1=’||v1);insertintospt1(c1,c2,c3)values(sysdate,v1,'test');

END;END;/演講完畢，謝謝觀看！OracleERP主要模塊InvoicesARENGBOMWIPBillsand

RoutingsShipmentsECOSWork

OrdersGLAPPlanning(MRP)POMaterialRequirementsEnterprise

DemandVendorPOInterplantPOReceiptsWorkOrdersCustomersJournalEntriesJournalEntriesJournalEntriesVendorInvoicesVendorPaymentsTransportation

InformationBillsItemsOEINVInternalPaymentsCorporateJournalEntriesShipmentsOrdersFAMassAdditionJournalEntriesCOSTCOSTCOSTReceipts財務配銷HRPayroll制造人力資源財務系統模塊:Oracle總帳管理（GL）Oracle應付帳管理（AP）Oracle固定資產管理（FA）Oracle應收帳管理（AR）Oracle現金管理（CE）Oracle項目會計（PA）Oracle財務分析（OFA）分銷系統模塊:Oracle庫存管理（INV）Oracle采購管理（PUR）Oracle銷售定單管理（OE）Oracle銷售&市場管理（SM）Oracle銷售補償管理（SC）Oracle售后服務管理（SR）Oracle銷售分析（OSA）OracleERP模塊清單制造系統模塊:Oracle計劃管理（MPS/MRP）Oracle能力計劃管理（CAP）Oracle制造數據管理（BOM）Oracle車間生產管理（WIP）Oracle成本管理（CST）Oracle項目制造（PJM)Oracle流程制造(OPM)Oracle質量管理（QM)其他系統模塊:Oracle設備管理（EM）Oracle人事管理（HR）Oracle薪金管理（PAYROLL）Oracle系統管理（SYSTEMADMIN）Oracle預警（ALT）Oracle多維數據分析/商業(yè)智能系統 （OLAP/BIS）Oracle桌面集成（ADI）OracleERP模塊清單Engineering工程資料管理系統BillofMaterials物料清單及制程管理系統CostManagement成本管理系統OrderEntry訂單管理系統MasterScheduling/MRP生產排程管理系統與物料需求計劃系統WorkInProcess生產管理系統Inventory庫存管理系統Purchasing采購管理系統Personnel人事管理系統Receivable應收賬款管理系統Payable應付賬款管理系統Assets固定資產管理系統GeneralLedger

總帳會計系統Capacity產能規(guī)劃系統OracleApplications39Oracle行業(yè)模型:離散制造供應鏈企業(yè)后臺管理客戶關系管理計劃尋源

制造

市場銷售支持企業(yè)管理返回項目到盈利財務管理到財務報告概念到發(fā)布聯系到解決合同到續(xù)簽預測到計劃采購到付款需求到制造(離散)訂單到收款庫存到履約問題到預防機會到訂單Oracle主要業(yè)務流程DesigntoReleaseForecasttoPlanPlantoScheduleScheduletobuildProcuretoPayOrdertoCashInventorytoFulfillmentCostQuality41概念到發(fā)布返回設計到發(fā)布預測到計劃計劃到排程排程到生產(離散)概念到獲利一級業(yè)務流程42預測到計劃返回供應商協同到供應商承諾客戶協同到達成一致預測預測到計劃計劃到排程排程到生產(離散)請求到接收–直接設計到發(fā)布一級業(yè)務流程43采購到支付返回分析到協議預測到計劃申請到接收–直接申請到接收–間接供應商退貨到貸項通知單供應商退貨到補貨驗收到接收接收到資產供應商發(fā)票到付款補貨信號費用報告到入帳一級業(yè)務流程44需求到制造(離散)返回一級業(yè)務流程返回計劃到排程排程到生產(離散)檢驗到處理設計到發(fā)布按訂單配置到發(fā)運按訂單裝配到發(fā)運一級業(yè)務流程45訂單到收款返回訂單到直發(fā)信貸審批到客戶揀貨到訂單到發(fā)運客戶退貨到退款客戶退貨到退換客戶發(fā)票-現金定制到訂單到發(fā)運裝配到訂單到發(fā)運

銷售線索到訂單訂單到發(fā)運訂單到直接采購發(fā)運收入到過帳訂單到客戶發(fā)票一級業(yè)務流程46庫存到履約返回申請到接收-直接排程到生產(離散)檢驗到處理產品標準成本到庫存再估價訂單到收款接收到庫存準確度補貨信號到補貨子分類帳到過帳倉庫布局到庫存估價計劃到排程一級業(yè)務流程客戶訂單CustomerPO銷售預測Forecast主需求計劃MDS主生產計劃MPS物料需求

計劃MRP請購作業(yè)PR采購作業(yè)PO

收料作業(yè)

Receiving工單開立WIP生產發(fā)料WIPIssue生產控制生產入庫WIPComplete出貨作業(yè)Shipping應付賬款應收賬款發(fā)票作業(yè)成本作業(yè)Oracle整合企業(yè)日常作業(yè)流程可承諾量查核ATP內部領用撿貨單雜項發(fā)料/退貨MiscIssue/Return發(fā)料/出貨Issue入庫異動ReceiptTransaction實際盤點PhysicalCounting標簽與序列號列印TagGeneration盤點過賬交貨預測與再訂購管理ForecastandReorder存貨管制規(guī)則-Min-max-ReorderPoint-Sub-inventoryreplenishment交貨/預定安排庫存評估/報表定義作業(yè)規(guī)定總帳更新定義作業(yè)流程組織/倉庫定義SetupOrganization/Subinventory檢驗Inspect登帳運送Deliver退貨Return供應商Supplier內部Internal成本Cost狀態(tài)Status目錄Catalog分類Category模板Template供應商Supplier內部Internal訂單處理自動請購余額更新調整庫存平衡表循環(huán)盤點CycleCounting-自動排程-手動排程OracleInventory(庫存管理系統)庫存控制InventoryControl物料管理MaterialManagement收料Receipt聯接操作制程料品定義Item定義資源Resource選用零配件/料品成本累計成品FinishGood定義資源使用量ResourceRate新產品流程建立替代制程AlternateRouting定義制程Routing材料需求ItemRequirement新產品開發(fā)建立替代性物料清單AlternateBOM工單排程量產日排程實施設計變更單OracleBillofMaterials(物料清單及制程管理系統)設計變更單ECO半成品Assembly建立物料清單BOM產品規(guī)格-型號、選項產生工單(或量產計劃)完成品入庫原物料耗用報表-工單成本-期間成本-成本差異-成本衡量-物料耗用-資源耗用組立裝配Assembly(選項)外包反溯扣帳Backflash工單發(fā)放Release發(fā)料Issue裝配完成工單完成WIPComplete制造資源耗用Resource-人工-機器設備-制造費用送貨/交貨外包交貨外包OutsideProcessing安排生產制程物料清單展開計劃工單手開工單銷售訂單OracleWorkInProcess(生產管理系統)預測資料ForecastOracleSO客戶訂單其它系統客戶訂單需求報表&查詢主需求計劃MDS需求排程更新MDSUpdate詳細產能計劃物料需求計劃MRP工單WIP請購PRBOM計劃工單計劃采購單現有庫存量OnhandOraclePlanning(MPS/MRP管理系統)主生產計劃MPS交易資訊Transaction定義配件成本報告采購收料POReceive定義成本計算規(guī)則成本累計CostRollup成本更新CostUpdate庫存值計算采購料品材料材料管理費用用料清單制程

成本元件-工作項目/活動Project/Active-料品Item-批次Lot-資源單位Uom-總價半成品庫存資源成本采購退貨POReturn公司間移轉OrganizationTransfer移動平均成本更新雜項收發(fā)MiscellaneousReceipt/Issue選擇模擬規(guī)則模擬凍結成本Frozen凍結成本OracleCostManagement(成本管理系統)請購單處理收貨

(料)Receipt檢驗Inspect異動處理-收貨-運送-調撥退貨Return應付賬款對帳應計作業(yè)-暫記項目-憑證-月結報價單收集請求報價RFQ(選擇性項目)自動產生訂購單發(fā)放Release定義企業(yè)流程收料控制-核準及安全管制-交貨來源-采購預算管制-異動處理方法內部轉移InterOrgTransfer發(fā)票處理應付賬款成立APInvoice總帳科目更新核準Approve供應商挑選供應商交貨允收AcceptOraclePurchasing(采購管理系統)MRP需求存貨狀況OnhandQty在制品(委外)OutProcess手開請購ManualPR開放界面Interface列印/EDI核準Approve各種采購單-標準式Standard-計劃式Planned-合并式Blanket-合約式Contract判退Reject選配件產品訂單輸入OrderEnter發(fā)票開立Invoice出貨管理Shipping退貨管理RMA退貨審核Approve貨品退回Return訂單完成重排程Re-schedule保留Reservation庫存余額更新未交訂單管理定義各項流程次序定義流程元件定義企業(yè)運作流程Workflow-彈定規(guī)則訂單處理流程-授信額度檢查-退貨管理-訂單排程訂單處理流程定義授信額度檢查CreditCheck例外管理Exception訂單保留Hold-自動Auto-手動Manual訂單解除管制HoldRelease訂價Price訂單審核確認客戶基本資料Customer線上查詢庫存余量-允交量ATP-保留量Reservation授信額度檢查CreditCheckOracleOrderEntry(訂單管理系統)允交量查詢ATP訂單排程OrderSchedule出貨確認ShipConfirm檢貨Pick銷貨折讓Credit分派/收集預算資料建立預算預算作業(yè)Budget查詢報表產生器

(FSG)定義企業(yè)作業(yè)模式/輸出分析會計科目結構AccountingStructure分錄/憑證登錄日歷Calendar多公司合并作業(yè)處理Consolidation多幣別作業(yè)Multi-Currency應計分錄/回轉分錄開放介面轉入Interface科目調整作業(yè)結帳開啟會計期間OpenAccountingPeriod匯兌重整與實現Revaluation(多公司)合并分錄作業(yè)Consolidation報表需求分析-財務性-管理性-例行性(年/月報)修訂.整理預算資料明細賬反查稽核人工輸入ManualOracle