oracleerp报表开发开发培训教程(doc 28页).doc

内部资料NO.ERP0002南京多茂科技发展有限公司Oracle ERP报表开发培训教程报表开发的总体步骤，及一些工具包的简单介绍 . 1Pl/sql语法及pl/sql函数、数组、记录、游标、视图、基表、临时表、异常. 4HTML技术和CSS修饰 . 10查找数据的方法及Oracle Application表命名的规律. 11可执行、并发程序、请求组和值集的定义. 12一个报表开发的实例. 15系统中部分表的介绍. 21开发分页报表（套打）的步骤. 22开发规范. 24附:记录和数组使用范例. 28所谓报表开发就是按照用户的需求，根据用户提供的样表，运用编程的手段，从ERP系统中取出数据展现在页面上的一个理解需求、查找数据、展现结果的过程。目前开发报表的方法及工具有很多种，有专门针对报表需求定制的报表系统，通过对sql语句的改写能实现特别的报表需求，这样的系统有很强的针对性和限制性；还有运用报表开发工具根据用户需求临时开发报表，具有很强的灵活性和应用性。本文档只关注运用Oracle 在DB中提供的一些开发工具包，及WEB技术开发报表的过程和方法。Oracle博大精深，如果在实际开发过程中遇到困难可以通过网上论坛和oracle网站获得技术帮助。根据实际的开发过程，本文档从以下几个方面来说明：一 报表开发的总体步骤，及一些工具包的简单介绍开发步骤1 分析客户提供的样表2 向客户或者顾问征询报表中每个值的意思，务必理解所需开发报表的意向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 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可以通过这种方法获得：帮助-诊断-检查，在块中选择$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 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 complete any output and to free any resources associated with the file.UTL_FILE.FILE_TYPE 定义文件指针utl_file.fopen(dir,name,mode) 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 客户化的报表格式生成包，封装了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 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 suppliers.supplier_id = orders.supplier_id);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 combined with the NOT operator. For example, SELECT * FROM suppliers WHERE not exists (select * from orders Where suppliers.supplier_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, update, 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, 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 neither 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 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%; 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 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, 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 the 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 HAVING 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, 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 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 ORDER 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 supplier_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 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, field2, 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 query 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 in 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(value-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 stringFor 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 Example: 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 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 return 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 Tech 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 string 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 The 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 function.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 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 value 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 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_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 TABLE MyTempTable 所建的临时表虽然是存在的，但是insert 一条记录然后用别的连接登上去select，记录是空的-ON COMMIT DELETE ROWS 说明临时表是事务指定，每次提交后ORACLE将截断表（删除全部行） -ON COMMIT PRESERVE ROWS 说明临时表是会话指定，当中断会话时ORACLE将截断表 异常 PL/SQL处理异常不同于其他程序语言的错误管理方法，PL/SQL的异常处理机制与ADA很相似，有一个处理错误的全包含方法。当发生错误时，程序无条件转到异常处理部分，这就要求代码要非常干净并把错误处理部分和程序的其它部分分开。oracle允许声明其他异常条件类型以扩展错误/异常处理。这种扩展使PL/SQL的异常处理非常灵活。当一个运行时错误发生时，称为一个异常被抛出。PL/SQL程序编译时的错误不是能被处理得异常，只有在运行时的异常能被处理。在PL/SQL程序设计中异常的抛出和处理是非常重要的内容。由三种方式抛出异常. 通过PL/SQL运行时引擎. 使用RAISE语句. 调用RAISE_APPLICATION_ERROR存储过程当数据库或PL/SQL在运行时发生错误时，一个异常被PL/SQL运行时引擎自动抛出。异常也可以通过RAISE语句抛出RAISE exception_name;三 HTML技术和CSS修饰 HTML技术 HTML英语意思是：Hypertext Marked Language，即超文本标记语言,是一种用来制作超文本文档的简单标记语言。用HTML编写的超文本文档称为HTML文档，它能独立于各种操作系统平台（如UNIX，WINDOWS等）。 1 HTML的基本结构 超文本文档分文档头和文档体两部分，在文档头里，对这个文档进行了一些必要的定义，文档体中才是要显示的各种文档信息。 头 部 信 息 文 档 主 体， 正 文 部 分 其中在最外层， 表示这对标记间的内容是HTML文 档。我们还会看到一些Hompage省略标记，因为.html 或.htm 文件被Web浏览器默认为是HTML文档。 之间包括文档的头部信息，如文档总标题等，若不需头部信息则可省略此标记。 标记一般不省略， 表示正文内容的开始。 2 在报表的实际过程中，最重要的是针对标记table的应用， 表格的基本结构 . 定义表格.定义标题 定义表行 定义表头 定义表元(表格的具体数据) 表中数据左右排列方式通过align属性来定义left、right、center 表中数据上下排列方式通过valign属性来定义top、middle、bottom 3 跨多行、多列的表元 要创建跨多行、多列的表元，只需在或中加入ROWSPAN或COLSPAN属性，这两个属性的值，表明了表元中要跨越的行或列的个数。 跨多列的表元 colspan表示跨越的列数，例如colspan=2表示这一格的宽度为两个列的宽度。跨多行的表元 rowspan所要表示的意义是指跨越的行数，例如rowspan=2就表示这一格跨越表格两个行的高度 4 插入图象的标签是，其格式为： SRC属性指明了所要链接的图象文件地址，这个图形文件可以是本地机器上的图形，也可以是位于远端主机上的图形。地址的表示方法可以沿用上一篇内容“文件的链接”中URL地址表示方法。例：IMG还有两个属性是HEIGHT和WIDTH，分别表示图形的高和宽。通过这两个属性，可以改变图形的大小，如果没有设置，图形按原大显示 CSS修饰 CSS是Cascading Style Sheets（层叠样式表单）的简称。更多的人把它称作样式表。顾名思义，它是一种设计网页样式的工具实际上CSS的代码都是由一些最基本的语句构成的。它的基本语句的结构是这样的： 选择符属性：属性值一般说来，下面的CSS语句是以注释语句的形式书写的。 在报表中预置了以下css（具体请参阅包FJ_OUTPUT）： bodymargin-top:0;td font-family:宋体;font-size:9pt;font-weight:normal;color:black;text-align:center;BACKGROUND-COLOR:white;height:27;vertical-align:middle;.td_cssborder:solid windowtext .5pt;.tb_cssborder-collapse:collapse;border:solid windowtext .5pt;四 查找数据的方法及Oracle Application表命名的规律 查找数据的方法请参阅在ERP系统中查找数据的方法 Oracle Application表命名的规律 一般来说，在Applications中所有的表的命名都是相当规范，通过名字，一般都可以知道这个表是做什么用，而且还可以通过查看FND_TABLES和FND_COLUMNS来获得表的详细信息。除此之外，还有一些规则，例如以TL结尾表示带有语言信息的表，V结尾表示一般的视图，VL表示带有语言信息的视图，以V$开头代表动态性能试图，以FND开头是属于Application Object Library模块的，以AR,RA开头是属于Oracle Receivables模块的，以MTL开头是属于库存模块的，以AP开头是属于应收模块的，以GL开头是属于总帐模块的，以FA开头是属于资产模块的，以OE开头的是属于订单模块的，以WSH开头是属于发运模块的，以WIP开头是属于在制车间模块的，以IBE开头是属于网上商店的，etc； 而且根据主从表的关系，Application表之间还有一定的对