OracleERP应用开发培训教材

Introduction to PL/SQL Programs
Introduction to PL/SQL Programs
1
Packages and Package Bodies
2
Triggers
3
Functions and Procedures
4
Tables, Views, Synonyms, Sequence & other database objects
• Reusable objects should be employed wherever possible,
especially using server or client side package to share programs
Refers to Page 1-2 of 115devg.rdf
Tip
select empno,ename,mgr,xyo_package_test.mgr_name(mgr) from xyo_emp;
Introduction to PL/SQL Programs
2
Triggers
Triggers are programs that are executed automatically in response to a change in the database. It fires either before or after the triggering event. Trigger events: • DML events: insert/delete/update • DDL events: create/alter/drop (type:statement/row)
Refers to Chapter 27 in 115devg.rdf
Development Overview
Overview of Application Development Steps
1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. Register your application. Set up your application directory structures. Modify the appropriate environment files. Register your custom Oracle schema. Include your custom application and Oracle schema in data groups. Create your application tables and views. Integrate your tables and views with the Oracle Applications APPS schema. Register your flexfields tables. Build your application libraries and forms. Build your application functions and menus. Build your application responsibilities. Build concurrent programs and reports. Customize Oracle Applications forms if necessary using the CUSTOM library.
Continuous
Syntax:
CREATE [OR REPLACE] PACKAGE [schema.]package_name {AS | IS}
public_declarations function_specifications procedure_specifications END [package_name]; CREATE [OR REPLACE] PACKAGE BODY [schema.]package_name {AS | IS} private_declarations pl/sql_package_body END [package_name]; | | |
languages)
• Fast performance over the World Wide Web (the web) is critical • Platform–specific code should be avoided except where absolutely necessary
Objectives
• Introduction of Oracle Application Customization • Developer/2000 Form Overview • Developer/2000 Report Overview • Using Named PL/SQL Programs in Oracle Application
Refers to Page 1-16 of 115devg.rdf
Development Overview
Summary
• • • • Following Oracle Coding Standards when combining customizations into Oracle Application; Using Forms, Reports, PL/SQL to manipulate Oracle objects when possible Concentrating Customization Objects under user defined Schema Using Extension other than Modification
• Compiling programs in group, fail or success as a whole; • Can’t grant privileges on the individual programs.
Introduction to PL/SQL Programs
1
Packages and Package Bodies
Introduction to PL/SQL Programs
1
Packages and Package Bodies
Packages are containers that bundle together procedures, functions and data structures. Using Package instead of non-packaged PL/SQL programs. Benefits: • Grouping programs for specific functionality;
Development Overview
Development Overview
Major Development Tasks
1
Interactive Data Query and Manipulation
Developer/2000 Forms
2
Concurrent Data Query
Developer/2000 Reports; SQL*Plus
3
Concurrent Data Manipulation PL/SQL Programs; C or Pro*C; Java
Development Overview
Coding Standards
Oracle Applications Developer's Guide : 115devg.rdf Oracle Applications User Interface Standards for Forms-Based Products : 115uistan.rdf
Development Overview
Customization Standards
• Determining Requirements, eliminating with altering application configuration is appropriate; • Documenting and Prioritizing the requirements, arranging resources; • Manipulating Oracle Application Data through Oracle Application is strongly recommended; • Using Customization by Extension instead of by Modification when possible; • Using table’s flexible columns instead of altering tables • Building custom schema to concentrate management, combining into Oracle Application Avoid overwritten by patches Easy to maintain Seamless behavior in Application
• Persisting package data in the duration of the session;
• Overloading procedures and functions based on parameters; • Managing privileges in group.
Disadvantage:
Contents of Day One (July 15)
1DevBiblioteka lopment Overview2
Introduction to PL/SQL Programs
3
Oracle Standard Request Submission (SRS)
4
Running PL/SQL Programs with SRS
• Code must be readable to be maintained • Tools such as Oracle Forms and PL/SQL are used whenever possible (avoid complex user exits using other coding
Introduction to PL/SQL Programs
1
Packages and Package Bodies
Continuous
Sample: create or replace package xyo_package_test is function mgr_name(p_mgr number) return varchar2; PRAGMA RESTRICT_REFERENCES(mgr_name, WNDS, WNPS, RNPS); end xyo_package_test; create or replace package body xyo_package_test is function mgr_name(p_mgr number) return varchar2 is v_mgr_name varchar2(30); begin select ename into v_mgr_name from xyo_emp where empno = p_mgr; return v_mgr_name; exception when others then return null; end; end xyo_package_test;
Design Test Form and Run through Oracle Application Design Test Report and Run through SRS Design Test PL/SQL Program and Run through SRS Trace and Tune Test programs