oracleerp报表开发开发培训教程.docx

《oracleerp报表开发开发培训教程.docx》由会员分享，可在线阅读，更多相关《oracleerp报表开发开发培训教程.docx（29页珍藏版）》请在三一办公上搜索。

1、内部资料NO.ERP0002南京多茂科技发展有限公司Oracle ERP报表开发培训教程报表开发的总体步骤，及一些工具包的简单介绍 . 1Pl/sql语法及pl/sql函数、数组、记录、游标、视图、基表、临时表、异常. 4HTML技术和CSS修饰 . 10查找数据的方法及Oracle Application表命名的规律. 11可执行、并发程序、请求组和值集的定义. 12一个报表开发的实例. 15系统中部分表的介绍. 21开发分页报表（套打）的步骤. 22开发规范. 24附:记录和数组使用范例. 28所谓报表开发就是按照用户的需求，根据用户提供的样表，运用编程的手段，从ERP系统中取出数据展现在

2、页面上的一个理解需求、查找数据、展现结果的过程。目前开发报表的方法及工具有很多种，有专门针对报表需求定制的报表系统，通过对sql语句的改写能实现特别的报表需求，这样的系统有很强的针对性和限制性；还有运用报表开发工具根据用户需求临时开发报表，具有很强的灵活性和应用性。本文档只关注运用Oracle 在DB中提供的一些开发工具包，及WEB技术开发报表的过程和方法。Oracle博大精深，如果在实际开发过程中遇到困难可以通过网上论坛和oracle网站获得技术帮助。根据实际的开发过程，本文档从以下几个方面来说明：一 报表开发的总体步骤，及一些工具包的简单介绍开发步骤1 分析客户提供的样表2 向客户或者顾问

3、征询报表中每个值的意思，务必理解所需开发报表的意向3 需要客户在ERP指明数据的取处4 自己动手在测试环境中做一遍，梳理一下流程5 清楚报表的参数是什么6 以包的形式组织数据，在包里面实现各种功能7 在ERP中定义可执行，从而使ERP和DB建立联系8 在ERP中定义并发程序，定义了报表的输出文件的类型和参数9 在相应的请求组中加入已定义好的并发程序名称工具包介绍1 FND_PROFILERetrieve user profile values for the current run-time environmentSet user profile values for the current

4、run-time environmentFnd_profile.value(ORG_ID) 取配置文件的值2 DBMS_OUTPUTThis package enables you to send messages from stored procedures, packages, and triggersDbms_output.put_line 在sql window中输出结果3 FND_GLOBALFnd_global. APPS_INITIALIZE(user_id,resp_id,resp_appl_id) 模拟环境其中user_id,resp_id,resp_appl_id可以通过这

5、种方法获得：帮助-诊断-检查，在块中选择$PROFILES，在字段选择USER_ID可以获得USER_ID,同样方法可以获得RESP_ID,RESP_APPL_ID（可能有时你需要在Oracle Applications环境外运行一些PL/SQL语句，但是这些语句中需要访问系统相关的环境变量，例如view，这样你可以使用上述方法达到你的目的）Fnd_global. User_name 得到当前用户的名称Fnd_global. User_id 得到当前用户的id4 UTL_FILEThe UTL_FILE package lets your PL/SQL programs read and wr

6、ite 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

7、 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 complete any output and to free any resources associated with the file.UTL_FILE.FILE_TYPE 定义文件指针utl_file.fopen(dir,name,mode)

8、 Opens a file for input or output with the default line size utl_file.put_line( , ) Writes a line to a file向文件中写数据utl_file.fclose_all Closes all open file handles关闭所有已打开的文件指针5 FJ_FUNC 客户化的函数包，定义了一些在实际开发过程中用到一些方法和变量 在实际开发过程中，如果需要一些方法时可以在这个包中查找，如果通用性比较强的过程也可以加到这个包里 详细可以参阅这个包6 FJ_OUTPUT 客户化的报表格式生成包，封装了

9、html语法 详细可以参阅这个包二 Pl/sql语法及pl/sql函数、数组、记录、游标、视图、基表、临时表、异常、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 re

10、move 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 conditio

11、n 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 suppliers.supplier_id = orders.supplier_id);This select statement will return al

12、l 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 combined with the NOT operator. For example, SELECT * FROM suppliers WHERE not exists (select * from orders Where suppliers.suppl

13、ier_id = orders.supplier_id);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, upd

14、ate, or delete.Example #1 The following is an SQL statement that uses the IN function: SELECT *FROM supplierWHERE supplier_name in ( IBM , Hewlett Packard, Microsoft); This would return all rows where the supplier_name is either IBM, Hewlett Packard, or Microsoft. Because the * is used in the select

15、, all fields from the supplier table would appear in the result set.Example #2 NOT IN The IN function can also be combined with the NOT operator. For example, SELECT *FROM supplierWHERE supplier_name not in ( IBM , Hewlett Packard, Microsoft); This would return all rows where the supplier_name is ne

16、ither IBM, Hewlett Packard, or Microsoft. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want. Like The LIKE condition allows you to use wildcards in the where clause of an SQL statement. This allows you to perform pattern matching. The

17、LIKE condition can be used in any valid SQL statement - select, insert, update, or delete. The patterns that you can choose from are: % allows you to match any string of any length (including zero length) _ allows you to match on a single characterSELECT * FROM supplier WHERE supplier_name like Hew%

18、; SELECT * FROM supplier WHERE supplier_name like %bob%; SELECT * FROM supplier WHERE supplier_name not like T%; SELECT * FROM supplier WHERE supplier_name like Sm_th GROUP BYThe GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or

19、 more columns. The syntax for the GROUP BY clause is: SELECT column1, column2, . column_n, aggregate_function (expression)FROM tablesWHERE predicatesGROUP BY column1, column2, . column_n; aggregate_function can be a function such as SUM, COUNT, MIN, or MAX. Example using the SUM function For example

20、, 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; Because you have listed one column in your SELECT statement that is not encapsulated in t

21、he SUM function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section. Having The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns. The syntax for the H

22、AVING clause is: SELECT column1, column2, . column_n, aggregate_function (expression)FROM tables WHERE predicates GROUP BY column1, column2, . column_nHAVING condition1 . condition_n; aggregate_function can be a function such as SUM, COUNT, MIN, or MAX. Example using the SUM function For example, yo

23、u 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_detail

24、s 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 clause can only be used in SELECT statements. The syntax for the ORDER BY clause is: SELECT columns FROM tablesWHERE predicates ORDER BY column ASC/DESC; The ORD

25、ER BY clause sorts the result set based on the columns specified. If the ASC or DESC value is omitted, the system assumed ascending order. ASC indicates ascending order. (default)DESC indicates descending order. Example #1 SELECT supplier_city FROM supplier WHERE supplier_name = IBM ORDER BY supplie

26、r_city; This would return all records sorted by the supplier_city field in ascending order. Example #2 SELECT supplier_city FROM supplier WHERE supplier_name = IBM ORDER BY supplier_city DESC; This would return all records sorted by the supplier_city field in descending order. UNION The UNION query

27、allows you to combine the result sets of 2 or more select queries. It removes duplicate rows between the various select statements.Each SQL statement within the UNION query must have the same number of fields in the result sets with similar data types.The syntax for a UNION query is:select field1, f

28、ield2, field_n from tablesUNIONselect field1, field2, field_n from tables UNION ALL The UNION ALL query allows you to combine the result sets of 2 or more select queries. It returns all rows (even if the row exists in more than one of the select statements).Each SQL statement within the UNION ALL qu

29、ery must have the same number of fields in the result sets with similar data types.The syntax for a UNION ALL query is:select field1, field2, field_n from tablesUNION ALLselect field1, field2, field_n from tables; UPDATE The UPDATE statement allows you to update a single record or multiple records i

30、n a table. The syntax the UPDATE statement is: UPDATE table SET column = expression WHERE predicates INSERT The INSERT statement allows you to insert a single record or multiple records into a table. The syntax for the INSERT statement is: INSERT INTO table(column-1, column-2, . column-n)VALUES(valu

31、e-1, value-2, . value-n) DELETE The DELETE statement allows you to delete a single record or multiple records from a tableThe syntax for the DELETE statement is: DELETE FROM table WHERE predicatespl/sql函数substr (string, start_position, length)This function allows you to extract a substring from a st

32、ringFor Example: substr (This is a test, 6, 2) would return is substr (This is a test, 6) would return is a test substr (Tech on the Net, 1, 4) would return Techdecode( expression , search , result , search , result. , default ) This function has the functionality of an IF-THEN-ELSE statement For Ex

33、ample: SELECT supplier_name,decode (supplier_id, 10000,IBM, 10001, Microsoft,10002, Hewlett Packard, Gateway) result FROM suppliersinstr(string1, string2, start_position, nth_appearance) This function returns the location of a substring in a string For example:instr (Tech on the net, e) would return

34、 2; the first occurrence of e instr (Tech on the net, e, 1, 1) would return 2; the first occurrence of e instr (Tech on the net, e, 1, 2) would return 11; the second occurrence of e instr (Tech on the net, e, 1, 3) would return 14; the third occurrence of e instr (Tech on the net, e, -3, 2) would re

35、turn 2Trim(text) This function removes leading and trailing spaces from a string For exampleTrim ( Tech on the Net) would return Tech on the Net Trim ( Alphabet ) would return AlphabetRTrim (text)This function removes trailing spaces from a stringFor example:RTrim (Tech on the Net ) would return Tec

36、h on the Net RTrim ( Alphabet ) would return Alphabet LTrim (text) This function removes leading spaces from a string For example:LTrim ( Tech on the Net) would return Tech on the Net LTrim ( Alphabet ) would return Alphabet to_number (string1, format_mask, nls_language ) This function converts a st

37、ring to a number For example:to_number (1210.73, 9999.99) would return the number 1210.73 to_number (546, 999) would return the number 546 to_number (23, 99) would return the number 23to_char (value, format_mask, nls_language ) This function converts a number or date to a string Examples - Numbers T

38、he following are number examples for the to_char function.to_char (1210.73, 9999.9) would return 1210.7 to_char (1210.73, 9,999.99) would return 1,210.73 to_char (1210.73, $9,999.00) would return $1,210.73 to_char (21, 000099) would return 000021The following are date examples for the to_char functi

39、on.to_char (sysdate, yyyy/mm/dd); would return 2003/07/09 to_char (sysdate, Month DD, YYYY); would return July 09, 2003 to_char (sysdate, FMMonth DD, YYYY); would return July 9, 2003 to_char (sysdate, MON DDth, YYYY); would return JUL 09TH, 2003 to_char (sysdate, FMMON DDth, YYYY); would return JUL

40、9TH, 2003 to_char (sysdate, FMMon ddth, YYYY); would return Jul 9th, 2003to_date (string1, format_mask, nls_language ) This function converts a string to a date、nvlFor example:to_date (2003/07/09, yyyy/mm/dd); would return a date value of July 9, 2003. to_date (070903, MMDDYY); would return a date v

41、alue of July 9, 2003. to_date (20020315, yyyymmdd); would return a date value of Mar 15, 2002 nvl (string1, replace_with ) This function lets you substitutes a value when a null value is encountered Example #1:select NVL (supplier_city, n/a) from suppliers*数组的定义Type NumArray Is Table Of Number Index

42、 By Binary_IntegerType StrArray Is Table Of Varchar2(500) Index By Binary_Integer记录的定义Type rp_tb_type Is Record (TbBorder Varchar2(10), TbCss Boolean, TdCss Boolean )游标的定义Cursor cur_line Is Select tb.Month, tb.loct_onhand From fj_rp_opm003_tmp_tb tb Where tb.item_no = row_head.item_no; row_line cur_

43、line%Rowtype;视图的定义视图是一个虚拟的、不是物理存在的表，他是通过sql语句把一个或多个表连接在一起形成的.Create or replace view ic_item_v As Select * from ic_item_mst_b 基表 是一个物理存在的表，能以表格的形式存储数据，是数据的载体 Create table table_name (col1 varchar2(100),col2 varchar2(10) 临时表 1 会话特有的临时表 CREATE GLOBAL TEMPORARY ( ) ON COMMIT PRESERVE ROWS； 2 事务特有的临时表 CREATE GLOBAL TEMPORARY ( ) ON COMMIT DELETE ROWS； CREATE GLOBAL TEMPORARY