【正文】 39。Microsoft39。Hewlett Packard39。IBM39。)。, 39。 , 39。This will return all records from the suppliers table where there are no records in the orders table for the given supplier_idInThe IN function helps reduce the need to use multiple OR conditionsThe IN function can be used in any valid SQL statement select, insert, update, or delete.Example 1 The following is an SQL statement that uses the IN function: SELECT *FROM supplierWHERE supplier_name in ( 39。根據(jù)實(shí)際的開發(fā)過程，本文檔從以下幾個(gè)方面來說明：一． 報(bào)表開發(fā)的總體步驟，及一些工具包的簡單介紹開發(fā)步驟1 分析客戶提供的樣表2 向客戶或者顧問征詢報(bào)表中每個(gè)值的意思，務(wù)必理解所需開發(fā)報(bào)表的意向3 需要客戶在ERP指明數(shù)據(jù)的取處4 自己動(dòng)手在測試環(huán)境中做一遍，梳理一下流程5 清楚報(bào)表的參數(shù)是什么6 以包的形式組織數(shù)據(jù)，在包里面實(shí)現(xiàn)各種功能7 在ERP中定義可執(zhí)行，從而使ERP和DB建立聯(lián)系8 在ERP中定義并發(fā)程序，定義了報(bào)表的輸出文件的類型和參數(shù)9 在相應(yīng)的請求組中加入已定義好的并發(fā)程序名稱工具包介紹1 FND_PROFILERetrieve user profile values for the current runtime environmentSet user profile values for the current runtime environment(‘ORG_ID’) 取配置文件的值2 DBMS_OUTPUTThis package enables you to send messages from stored procedures, packages, and triggers 在sql window中輸出結(jié)果3 FND_GLOBALFnd_global. APPS_INITIALIZE(user_id,resp_id,resp_appl_id) 模擬環(huán)境其中user_id,resp_id,resp_appl_id可以通過這種方法獲得：幫助診斷檢查，在塊中選擇$PROFILES，在字段選擇USER_ID可以獲得USER_ID,同樣方法可以獲得RESP_ID,RESP_APPL_ID（可能有時(shí)你需要在Oracle Applications環(huán)境外運(yùn)行一些PL/SQL語句，但是這些語句中需要訪問系統(tǒng)相關(guān)的環(huán)境變量，例如view，這樣你可以使用上述方法達(dá)到你的目的）Fnd_global. User_name 得到當(dāng)前用戶的名稱Fnd_global. User_id 得到當(dāng)前用戶的id4 UTL_FILEThe UTL_FILE package lets your PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file input/output (I/O). The file I/O capabilities are similar to those of the standard operating system stream file I/O (OPEN, GET, PUT, CLOSE), with some limitations.For example, call the FOPEN function to return a file handle, which you then use in subsequent calls to GET_LINE or PUT to perform stream I/O to a file. When you are done performing I/O on the file, call FCLOSE to plete any output and to free any resources associated with the file. 定義文件指針(dir,name,mode) Opens a file for input or output with the default line size ( , ) Writes a line to a file向文件中寫數(shù)據(jù) Closes all open file handles關(guān)閉所有已打開的文件指針5 FJ_FUNC 客戶化的函數(shù)包，定義了一些在實(shí)際開發(fā)過程中用到一些方法和變量 在實(shí)際開發(fā)過程中，如果需要一些方法時(shí)可以在這個(gè)包中查找，如果通用性比較強(qiáng)的過程也可以加到這個(gè)包里 詳細(xì)可以參閱這個(gè)包6 FJ_OUTPUT 客戶化的報(bào)表格式生成包，封裝了html語法 詳細(xì)可以參閱這個(gè)包二． Pl/sql語法及pl/sql函數(shù)、數(shù)組、記錄、游標(biāo)、視圖、基表、臨時(shí)表、異常、Pl/sql基本元素的使用Select The SELECT statement allows you to retrieve records from one or more tables in your database. The syntax for the SELECT statement is: SELECT columns FROM tables WHERE predicatesDistinct The DISTINCT clause allows you to remove duplicates from the result set. The DISTINCT clause can only be used with select statements The syntax for the DISTINCT clause is: SELECT DISTINCT columnsFROM tables WHERE predicates EXISTS The EXISTS condition is considered to be met if the subquery returns at least one row The EXISTS condition can be used in any valid SQL statement select, insert, update, or delete. Example 1 The following is an SQL statement that uses the EXISTS condition: SELECT * FROM suppliers WHERE EXISTS (select * from orders where = )。本文檔只關(guān)注運(yùn)用Oracle 在DB中提供的一些開發(fā)工具包，及WEB技術(shù)開發(fā)報(bào)表的過程和方法。南京多茂科技發(fā)展有限公司Oracle ERP報(bào)表開發(fā)培訓(xùn)教程報(bào)表開發(fā)的總體步驟，及一些工具包的簡單介紹 ………………………………………………………………………………………………. 1Pl/sql語法及pl/sql函數(shù)、數(shù)組、記錄、游標(biāo)、視圖、基表、臨時(shí)表、異?！?. 4HTML技術(shù)和CSS修飾 ……………………………………………………………………………………………………………………………………………….. 10查找數(shù)據(jù)的方法及Oracle Application表命名的規(guī)律…………………………………………………………………………….. 11可執(zhí)行、并發(fā)程序、請求組和值集的定義……………….……………………………………………………………………………………………. 12一個(gè)報(bào)表開發(fā)的實(shí)例……………………………………………………………………………………………………………………………………………..….. 15系統(tǒng)中部分表的介紹……………………………………………………………………………………………………………………………………………..….. 21開發(fā)分頁報(bào)表（套打）的步驟…………………………………………………………………………………………………………………………..….. 22開發(fā)規(guī)范………………………………………………………………………………………………………………………………………………………………...….. 24附:記錄和數(shù)組使用范例…………………………………………………………………………………………………………………………………...….. 28所謂報(bào)表開發(fā)就是按照用戶的需求，根據(jù)用戶提供的樣表，運(yùn)用編程的手段，從ERP系統(tǒng)中取出數(shù)據(jù)展現(xiàn)在頁面上的一個(gè)理解需求、查找數(shù)據(jù)、展現(xiàn)結(jié)果的過程。目前開發(fā)報(bào)表的方法及工具有很多種，有專門針對報(bào)表需求定制的報(bào)表系統(tǒng)，通過對sql語句的改寫能實(shí)現(xiàn)特別的報(bào)表需求，這樣的系統(tǒng)有很強(qiáng)的針對性和限制性；還有運(yùn)用報(bào)表開發(fā)工具根據(jù)用戶需求臨時(shí)開發(fā)報(bào)表，具有很強(qiáng)的靈活性和應(yīng)用性。Oracle博大精深，如果在實(shí)際開發(fā)過程中遇到困難可以通過網(wǎng)上論壇和oracle網(wǎng)站獲得技術(shù)幫助。This select statement will return all records from the suppliers table where there is at least one record in the orders tableith the same supplier_id.Example 2 NOT EXISTS The EXISTS condition can also be bined with the NOT operator. For example, SELECT * FROM suppliers WHERE not exists (select * from orders Where = )。IBM39。Hewlett Packard39。Microsoft39。 This would return all rows where the supplier_name is either IBM, Hewlett Packard, or Microsoft. Because the * is used in the select, all fields from the supplier table would appear in the result set.Example 2 NOT IN The IN function can also be bined with the NOT operator. For example, SELECT *FROM supplierWHERE supplier_name not in ( 39。 , 39。, 39。)。Hew%39。%bob%39。T%39。Sm_th39。 aggregate_function can be a function such as SUM, COUNT, MIN, or MAX. Example using the SUM function For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department). SELECT department, SUM (sales) as Total salesFROM order_detailsGROUP BY department。 aggregate_function can be a function such as SUM, COUNT, MIN, or MAX. Example using the SUM function For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department). The HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned. SELECT department, SUM (sales) as Total salesFROM order_details GROUP BY department HAVING SUM (sales) 1000 ORDER BY The ORDER BY clause allows you to sort the records in your result set. The ORDER BY claus