📄 examples.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Examples</TITLE>
<META NAME="GENERATOR" CONTENT="Arachnophilia 3.9">
<META NAME="FORMATTER" CONTENT="Arachnophilia 3.9">
</HEAD>
<BODY BGCOLOR="white" BACKGROUND="bar_products.gif">
<DIR>
<DIR>
<IMG SRC="examples.GIF" WIDTH="431" HEIGHT="92"> <HR>
<P><FONT SIZE="5">Table of Contents</FONT></P>
<P> </P>
<UL>
<LI><A HREF="#1"><B>How to use the examples</B></A> </LI>
<LI><A HREF="#2"><B>Create a header for an insert procedure that accepts as IN
arguments every column in a table</B></A> </LI>
<LI><A HREF="#3"><B>Create a function to check for equality between two records
based on the same table %ROWTYPE</B></A> </LI>
<LI><A HREF="#4"><B>Create cursors to query from a table by primary key and
foreign keys</B></A> </LI>
<LI><A HREF="#5"><B>Generate a procedure to write the contents of a particular
table to a file, with column values separated by a delimiter you pass as an
argument</B></A> </LI>
<LI><A HREF="#6"><B>Build a package that transfers the contents of a particular
table-based record through database pipes</B></A> </LI>
<LI><A HREF="#7"><B>Create DROP statements for all the tables and views in your
schema</B></A> </LI>
<LI><A HREF="#8"><B>Defining an Array of Files in a Directory</B></A> </LI>
<LI><A HREF="#9"><B>Create an audit database trigger for updates to tables in
your schema</B></A> </LI>
<LI><A HREF="#10"><B>Create an entire insert procedure that accepts as IN
arguments every column in a table</B></A> </LI>
<LI><A HREF="#11"><B>Customize the PL/Generator Table Encapsulation driver to
generate customized code specific to each table in your schema</B></A> </LI>
<LI><A HREF="#12"><B>Construct procedures to manage your packaged cursors more
easily</B></A> </LI>
<LI><A HREF="#13"><B>Provide a general solution for mutating table problems
with index</B></A> </LI>
<LI><A HREF="#14"><B>Generate HTML documentation for a table</B></A> </LI>
<LI><A HREF="#15"><B>Generate a PL/SQL program that wraps a Java Stored
Procedure </B></A></LI>
</UL>
<HR>
<P><A NAME="1"><B><FONT FACE="Arial" SIZE="5">INTRODUCTION</FONT></B></A></P>
<P>The best way to learn to use CGML is to examine and play around with
examples. This document offers many different "templates" or CGML
scripts that provide a set of instructions to the PL/Generator engine. We call
them templates because they represent in a generic way structures of code that
you want to generate for a specific database object or other data structure you
have identified to PL/Generator. </P>
<P>You can run these templates yourself by taking these steps:</P>
<OL>
<LI>Follow the directions in the section titled "Working with CGML"
to set up your test environment.</LI>
</OL>
<OL>
<LI>For each challenge, copy your CGML file to testCGML.gdr in your default
directory.</LI>
<LI>Run the test.sql script in SQL*Plus. It will display your results on the
screen and also write them to the CGML.tst file.</LI>
</OL>
<P>When you run test.sql, you will be prompted for the name of the table
against which you want to generate your text. You can easily make copies of
this file to run for specific tables or even to avoid the specification of a
database object all together.</P>
<P>Each example proposes a challenge and then offers the CGML Solution (the
CGML commands needed to achieve the goal), a description of the CGML Highlights
(CGML features of note used in the solution) and a Sample of Generated Text (so
you can get a feel for what you should see yourself when you run the script).
</P>
<HR>
<P><A NAME="2"><B><I><FONT FACE="Arial" SIZE="5">Create a header for an insert
procedure that accepts as IN arguments every column in a
table.</FONT></I></B></A></P>
<P>This procedure would be just one element of a more comprehensive
encapsulation of a table behind a layer of PL/SQL code. This layer gives you
better control over data integrity and improved (consistent) error handling.
</P>
<P><B><FONT FACE="Arial" SIZE="4">CGML Solution </FONT></B></P>
<P>You will want to obtain the column information from two arrays: pkycol and
nonpkycol (there is not single array for all columns). The following text uses
a loop to scan through the array and construct IN argument syntax. </P>
<PRE>
# inshdr.gdr
[STOREIN]CGML.tst
PROCEDURE insert_row (
[FOREACH]col[BETWEEN],
[colname]_in IN [data_type]
[ENDFOREACH]
);
</FONT></PRE>
<P>Notice that I construct an IN parameter based on the column name and its
datatype. An even better approach would be to avoid hard-coded datatype
declarations and instead rely on anchored datatypes as follows:</P>
<P></P>
<PRE> #inshdr2.gdr
[STOREIN]CGML.tst
PROCEDURE insert_row (
[FOREACH]col[BETWEEN],
[colname]_in IN [objname].[colname]%TYPE
[ENDFOREACH]
);</FONT></PRE>
<P><FONT FACE="Arial" SIZE="4"><B>CGML Highlights </B></FONT></P>
<UL>
<LI>The STOREIN command directs output to the desired file.</LI>
<LI>The FOREACH loop goes through each column in the table, using BETWEEN to
add a column between each parameter, except for the last.</LI>
</UL>
<P><B><FONT FACE="Arial" SIZE="4">Sample of Generated Text </FONT></B></P>
<P>Here is the output based on the department table, for both approaches:</P>
<PRE>
PROCEDURE insert_row (
department_id_in IN NUMBER,
name_in IN VARCHAR2,
loc_id_in IN NUMBER
);
PROCEDURE insert_row (
department_id_in IN department.department_id%TYPE,
name_in IN department.name%TYPE,
loc_id_in IN department.loc_id%TYPE
);</FONT></PRE>
<HR>
<P><A NAME="3"><I><FONT FACE="Arial" SIZE="5"><B>Create a function to check for
equality between two records based on the same table
%ROWTYPE.</B></FONT></I></A></P>
<P>This is a handy example, since PL/SQL does not let you do boolean checks
between records. The following logic is, for example, highly desirable but not
compilable:</P>
<P></P>
<PRE> DECLARE
rec1 employee%ROWTYPE;
rec2 employee%ROWTYPE;
BEGIN
IF rec1 = rec2 /* Only in your dreams! */</FONT></PRE>
<P>Instead, you must compare the values in corresponding fields of each record.
And then, of course, you have to worry about NULL values. It can get
complicated and, for large records, it can get downright tedious. </P>
<P><B><FONT FACE="Arial" SIZE="4">CGML Solution </FONT></B></P>
<P>Here's some CGML that will take care of this problem (for non-object
database tables, anyway!):</P>
<P></P>
<PRE> #recseq.gdr
[STOREIN][objname].req
CREATE OR REPLACE FUNCTION [objname]_recseq (
rec1 IN [objname]%ROWTYPE,
rec2 IN [objname]%ROWTYPE
)
RETURN BOOLEAN
IS
unequal_records EXCEPTION;
retval BOOLEAN;
BEGIN
[FOREACH]col
retval := rec1.[colname] = rec2.[colname] OR
(rec1.[colname] IS NULL AND rec2.[colname] IS NULL);
IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;
[ENDFOREACH]
RETURN TRUE;
EXCEPTION
WHEN unequal_records THEN RETURN FALSE;
END;
/</FONT></PRE>
<P><FONT FACE="Arial" SIZE="4">CGML Highlights </FONT></P>
<UL>
<LI>In this STOREIN command, I have even made the name of the output file
change with the name of the table. This comes in handy when you want to avoid
overwriting an existing file and also just generate a whole lot of text for a
variety of tables.</LI>
</UL>
<P><B><FONT FACE="Arial" SIZE="4">Sample of Generated Text </FONT></B></P>
<P>Here is an example of the output from this CGML for the location or loc
table:</P>
<P></P>
<PRE> CREATE OR REPLACE FUNCTION loc_recseq (
rec1 IN loc%ROWTYPE,
rec2 IN loc%ROWTYPE
)
RETURN BOOLEAN
IS
unequal_records EXCEPTION;
retval BOOLEAN;
BEGIN
retval := rec1.loc_id = rec2.loc_id OR
(rec1.loc_id IS NULL AND rec2.loc_id IS NULL);
IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;
retval := rec1.regional_group = rec2.regional_group OR
(rec1.regional_group IS NULL AND rec2.regional_group IS NULL);
IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;
RETURN TRUE;
EXCEPTION
WHEN unequal_records THEN RETURN FALSE;
END;
/</FONT></PRE>
<P>As you can see, the only way I can get to the RETURN TRUE statement is if
the corresponding fields have matching values or both are NULL.</P>
<P>And I am sure that it is easy for you to see how to extend this to compare
three or even four different records, as you may need.</P>
<HR>
<P><A NAME="4"><B><I><FONT FACE="Arial" SIZE="5">Create cursors to query from a
table by primary key and foreign keys.</FONT></I></B></A></P>
<P>We usually write the same kind of cursors over and over again in our
programs. Get me the row for a given primary key, fetch through all the rows
for a particular foreign key value set, etc. Why not just generate it instead?
</P>
<P><B><FONT FACE="Arial" SIZE="4">CGML Solution </FONT></B></P>
<P>This solution is a bit long, but it sure is better than writing those
queries again and again!</P>
<P></P>
<PRE> #cursors.gdr
[STOREIN][objname].crs
CURSOR [objname]_bypky
IS
SELECT
[FOREACH]col[between],
[colname]
[ENDFOREACH]
FROM [objname]
ORDER BY
[FOREACH]pkycol[between],
[colname]
[ENDFOREACH]
;
CURSOR [objname]_forpky (
[FOREACH]pkycol[between],
[colname]_in IN [coldatatype]
[ENDFOREACH]
)
IS
SELECT
[FOREACH]col[between],
[colname]
[ENDFOREACH]
FROM [objname]
WHERE
[FOREACH]pkycol[between] AND
[colname] = [colname]_in
[ENDFOREACH]
;
[FOREACH]fky
CURSOR row_for_[fkyname] (
[FOREACH]fkycol[between],
[colname]_in IN [coldatatype]
[ENDFOREACH]
)
IS
SELECT
[FOREACH]col[between],
[colname]
[ENDFOREACH]
FROM [objname]
WHERE
[FOREACH]fkycol[between] AND
[colname] = [colname]_in
[ENDFOREACH]
;
[ENDFOREACH]fky</FONT></PRE>
<P><FONT FACE="Arial" SIZE="4">CGML Highlights </FONT></P>
<UL>
<LI>As you can see, I can loop through all the columns in the table, just the
columns in the primary key or just the columns in a particular foreign key. No
matter which loop, I can reference the same database tags [colname] and
[coldatatype].</LI>
<LI>I am taking advantage of nested loops in this example: For each foreign
key, for each column in each foreign key. There is no limitation to the
nestings of loops in CGML.</LI>
<LI>I can place the name of the foreign key directly inside the cursor name to
make sure the resulting cursor is unique. </LI>
</UL>
<P><B><FONT FACE="Arial" SIZE="4">Sample of Generated Text </FONT></B></P>
<P>Here is part of the output from this CGML text for the employee table:</P>
<P></P>
<PRE> CURSOR employee_bypky
IS
SELECT
employee_id,
last_name,
...
department_id
FROM employee
ORDER BY
employee_id
;
CURSOR employee_forpky (
employee_id_in IN number
)
IS
SELECT
employee_id,
last_name,
...
FROM employee
WHERE
employee_id = employee_id_in
;
CURSOR row_for_EMP_DEPT_LOOKUP (
department_id_in IN number
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -