📄 introduction.htm

📁 Oracle PL/SQL procedure generator (second generator type)
💻 HTM
字号:
<HTML>
<HEAD>
<TITLE>PL/Generator Introduction</TITLE>
</HEAD>
<BODY bgcolor="white" background="bar_products.gif">

<table border=0 cellspacing=0 cellpadding=0 valign=top  width=750>
<tr><td width="51" valign=top>

<br>

<td valign="top" width="699">

<IMG SRC="generator.gif" WIDTH=285 HEIGHT=48>

<br>
<hr>
<P>

<B><FONT SIZE=5>Part 1: Introduction</B></FONT>
<P>
Thank you for choosing Quest Software's PL/Generator. In this manual you'll find information designed to help you understand the capabilities of PL/Generator, as well as a step-by-step explanation of how to install the software.
<P>
<A HREF="#What"><FONT   SIZE=2>What is PL/Generator?</FONT></A><BR>
<A HREF="#Encapsulation"><FONT SIZE=2>The Power of Encapsulation</FONT></A><BR>
<A HREF="#Purpose"><FONT   SIZE=2>Purpose of this Document</FONT></A><BR>
<A HREF="#When"><FONT   SIZE=2>When the PL/Generator API is Needed</FONT></A>
<P>
<hr>
<P>

<A NAME="What">
<FONT   SIZE=5><B>What is PL/Generator?</B></FONT>
<P>
PL/Generator generates production-ready code written to your coding standards. It combines best-practice coding and sophisticated "database-aware" technology to generate table packages written specifically for your Oracle tables and to your specifications.  
<P>
PL/Generator employs plug-in drivers, each of which generate different source code as output. The PL/SQL Table Encapsulation Driver, currently available for license, generates PL/SQL encapsulation packages for tables that can be compiled and deployed in production applications without any changes. In a similar manner, future PL/Generator drivers will generate Java components that interact with the Oracle database via your choice of JDBC, JSQL and SQLJ. <P>
Besides saving you significant development time, PL/Generator improves application performance. It eliminates the performance drain caused by unmanageable variations of code, each of which performs the same table access.  
<P>
PL/Generator抯 standardized table API results in optimal re-use of pre-parsed cursors in Oracle抯 System Global Area (SGA). It keeps network round-trips to a minimum by moving SQL code to the server and providing high-level lookup programs on the client. And it offers numerous performance options, such as caching frequently used data into user memory, which can improve dramatically lookup performance. 
<P>
Multiple versions of code doing the same thing doesn't just hurt performance, it makes it quite hard to control the way developers manipulate table contents. PL/Generator抯 APIs, however, restrict table access to a consistent, optimized and centrally controlled standard, which improves data integrity and streamlines maintenance when changes occur to database structures. 
<P>
To test the behavior of its generated packages, PL/Generator also builds a comprehensive test application that fully exercises the packaged programs against your tables. Finally, PL/Generator also generates detailed, table-specific HTML documentation for each table encapsulation package. 
<P>
See the on-line help for more details on the architecture of PL/Generator.  
<P>
</FONT>
<FONT SIZE=5><B><A NAME="Encapsulation">Power of Encapsulation</A></B></FONT>
<P>
The immediate objective of PL/Generator is to provide PL/SQL developers with an efficient, practical means of creating a programmatic interface to their tables. Why do this? There are a number of reasons, including:
<P>
<LI><U><FONT SIZE="+1">Improved productivity:</FONT></U> individual developers do need to write SQL; they just call pre-built programs which know all about the underlying data structures, "do the right thing" in terms of error handling and execution tracing, and otherwise ease the burden of the programmer.
<P>
<LI><U><FONT SIZE="+1">Better performance:</FONT></U> since you don't have multiple, most likely different versions of the same SQL scattered throughout your code, you are much more likely to take advantage of shared cursors in the Oracle shared memory. In addition, if you are writing and executing PL/SQL code in a client-server configuration (such as with Oracle Developer/2000), then encapsulating your SQL access will improve performance by moving all SQL activity to the server.
<P>
<LI><U><FONT SIZE="+1">Higher levels of data integrity:</FONT></U> since all attempts to access the data must pass through the programmatic gatekeeper you build, you can apply the full power of PL/QL to enforce complex business rules across many tables.
<P>
Building packages around tables is, however, a daunting task. How can you write such a large volume of code and ensure that it is all done properly, sensitive to performance issues, and employing a consistent API across all the packages? The only feasible approach is to generate the code and that is what PL/Generator will do for you.
<P>
Before going any further, let's take a look at how your programming life might change if you took advantage of table encapsulation. Suppose you were building a human resources application based on the standard Oracle demonstration tables (in their latest incarnation): Employee and Department.
<P>
<B>BEFORE:   Retrieving Data</B>
<P>
I need to get the name of the department for its ID number and then display that name. Here is the kind of code I might write in an Oracle Forms trigger:
<P>
<pre>
BEGIN
   select name into :empblk.dept_name
   from department where department_id = :empblk.department_id;
END;
</pre>
<P>
Of course, this is a very common query and in an Oracle Reports module, I also perform the same lookup as part of a pre-report validation, and it looks like this:
<P>
<pre>
BEGIN
   SELECT name 
     INTO v_dept_name
     FROM department 
    WHERE department_id = v_dept_id;

    ... more logic here ...

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      ... error handling logic ...
END;
</pre>
<P>
Notice that since I am performing an implicit query (the usual approach taken in these situations), I must also add some code to handle the situation where no match was found.
<P>
Besides the volume of code required, we have a basic tuning problem: since a different format was used for these two logically-equivalent queries, the pre-parsed cursor in the Oracle SGA will not be reused, resulting in sub-optimal performance.
<P>
<B>AFTER:   Retrieving Data</B>
<P>
With PL/Generator, we can take a very different approach. The code to perform the lookup has already been created and stored in the database. So I can just make the appropriate call in my Oracle Forms trigger. I can call the function that retrieves an entire row, as follows:
<P>
<pre>
DECLARE
   deptrec department%ROWTYPE;
BEGIN
   deptrec := te_department.onerow (:empblk.department_id);
   :empblk.dept_name := deptrec.name;
END;
</pre>
<P>
Or, if the PL/Generator designer had requested creation of a function to return the name for a department specifically, I could write this code:
<P>
<pre>
BEGIN
   :empblk.dept_name := te_department.name$val (:empblk.department_id);
END;
</pre>
<P>
I have the same options for my Oracle Reports module and thus end up with:
<P>
<pre>
BEGIN
   v_dept_name := te_department.name$val (v_dept_id);

   IF v_dept_name IS NULL
   THEN
      ... error handling logic ...
   ELSE
      ... more logic here ...
   END IF;
END;
</pre>
<P>
I no longer need the error handling logic; I can check the status of the retrieval with a standard IF statement. Most importantly, I execute precisely the same SQL query in all cases, guaranteeing the best possible performance. Finally, I was able to write this code without knowing about the names of columns in the table, and even without knowledge of SQL itself! All I needed to know was the API to the table. 
<P>
<B>BEFORE:  Inserting Data</B>
<P>
The same advantages accrue to DML statements: updates, inserts and deletes. Suppose that I need to insert a new row into the employee table. To do that in the traditional way within PL/SQL, I must research the names of the columns, determine the method of primary key generation (we are using sequences, so I have to know about the NEXTVAL operator of the sequence, and how it must be called in SQL). The specifications, in this care, also stipulate that if I do encounter a duplicate of the primary key (in other words, that employee ID number is already in use), I should perform an update of the name and department information. 
<P>
Here we go:
<P>
<pre>
PROCEDURE ins_employee (
   last_in IN employee.last_name%TYPE,
   first_in IN employee.firsst_name%TYPE,
   dept_in IN employee.department_id%TYPE
   )
IS
   v_key employee.employee_id%TYPE
      := employee_seq.NEXTVAL;
BEGIN
   INSERT INTO employee
      (employee_id, last_name, first_name, department_id)
   VALUES
     (v_key, last_in, first_in, dept_in);
EXEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
      UPDATE employee
         SET last_name = last_in,

             first_name = first_in,
             department_id = dept_in
       WHERE employee_id = v_key;
END;
</pre>
<P>
Of course, you don't see all the mistakes I just made writing this code and getting it to compile. Initially, my sequence name was incorrect; I wrote "dup_val_in_index" instead of "on_index" (I always get that wrong the first time; it just seems like "in" makes more sense); I realized when I got to the exception section that I could not call NEXTVAL inside the INSERT statement, but instead needed to declare a variable for it, since I would also need the value in the exception section for the update.
<P>
<B>AFTER:  Inserting Data</B>
<P>
Here is what was done once the generated package in place:
<P>
<pre>
PROCEDURE ins_employee (
   last_in IN employee.last_name%TYPE,
   first_in IN employee.firsst_name%TYPE,
   dept_in IN employee.department_id%TYPE
   )
IS
   v_key employee.employee_id%TYPE
BEGIN
   te_employee.ins (
      last_name_in => last_in,
      first_name_in => first_in,
      department_id_in => dept_in,
      employee_id_out => v_key,
      upd_on_dup => TRUE
      );
END;
</pre>
<P>
Of course, there is also a really good chance that you would not bother creating such an ins_employee procedure; you would just call te_employee.ins (or whatever you decided to name your insert procedure) from your host programs.
<P>
Hopefully this gives you a sense of the power of pre-built, well-structured interfaces to your tables. Sure, you still have to learn the API, but PL/Generator creates HTML documentation automatically to help you come up to speed quickly.
<P>
<P>
<FONT SIZE=5><B><A NAME="Purpose">Purpose of this Document</A></B> </FONT>

<P>
Follow the instructions in this manual to install PL/Generator. From that point on, you should make use primarily of the PL/Generator Online Help System to answer your questions about the product and help you use the Graphical User Interface (GUI) for PL/Generator.  
<P>
In the first release of PL/Generator, however, not all of the PL/Generator functionality is available through the GUI interface. In some instances (described below), you will need to make calls directly to the PL/Generator API. This manual documents the complete functionality of that programmatic interface (implemented by the PLGdoir, PLGpriv and PLGval packages). 
<P>
The PL/Generator API, for the most part, manipulates the contents of the Object Information Repository, which is explained later in this document. 
<B><FONT SIZE=5>
<P>

<A NAME="When">When the PL/Generator API is Needed</A> </B></FONT>

<P>
As of this release of PL/Generator, you will need to use the PL/Generator API if you want to perform any of the following actions: 

<UL>
<LI>Emulate a primary key for a table which has no primary key defined for it. </LI>
<LI>Define additional cursors for the generated package. </LI>
<LI>Set the descriptor index for a table. </LI>
<LI>Perform export/import activities. </LI>
<LI>Validate the naming standards you have chosen for the code you will generate. </LI>
<LI>Regenerate code from data structures that have changed </LI>
<LI>Turn on and define privileged access for configuring code to be generated by PL/Generator.</LI>
<LI>Clear your settings from the Object Information Repository.
<LI>To turn on use of the RESTRICT_REFERENCES pragma, allowing packaged functions to be called from within an SQL statement.

</UL>

<P>You will, in addition, need to edit directly the customization driver files you create for incorporation into the generated code. 
<P>&nbsp; 
<P>
<HR> 

<font size="2">
<p align="center">
<a href="User_Guide.htm"> Main </a>  |  
Introduction  | 
<a href="Installation.htm"> Installation </a>  |  
<a href="Get_Started.htm"> Getting Started </a>  |  
<a href="Generator_API.htm"> PL/Generator API </a>  
</font>
<p>

<hr>
<p align="center"><font size="1">Copyright
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -