Before reading about the various aspects of the PL/SQL language, examine the syntax for creating a procedure and building a script for ease of maintenance as changes occur. This code is the first step in developing a sample package for calculating dollar totals in a merchandise order—an example that is completed within this chapter. The following example illustrates some commands for creating this PL/SQL packaged procedure and script.
set echo on
spool order_total
CREATE OR REPLACE PACKAGE order_total
AS
(package specifications)
END order_total
CREATE OR REPLACE PACKAGE BODY order_total
AS
(package body specifications)
END order_total;
DROP PUBLIC SYNONYM order_total;
CREATE PUBLIC SYNONYM order_total for order_total;
GRANT EXECUTE ON order_total TO PUBLIC;
spool off
SELECT
*
FROM
user_errors
WHERE
name='ORDER_TOTAL'
;
The first command in this script, SET ECHO ON, displays a listing of the package to the screen as it is being compiled. ECHO combined with the SPOOL name command creates a list file (order_total.lst) for debug purposes. This file will contain the compilation of the procedure, including errors, complete with line numbers.
CREATE OR REPLACE PACKAGE name is the command that starts the procedure build in the database. Declarations of objects and subroutines within the package area are visible to your applications. Think of this area as the application interface to your PL/SQL code; at the very least, you must define the procedure entry routine here. Modifications to any specifications in this area require rebuilding your applications. The END statement signifies the end of the package specification area.
set echo on
spool order_total
CREATE OR REPLACE PACKAGE order_total
AS
(package specifications)
END order_total
CREATE OR REPLACE PACKAGE BODY order_total
AS
(package body specifications)
END order_total;
DROP PUBLIC SYNONYM order_total;
CREATE PUBLIC SYNONYM order_total for order_total;
GRANT EXECUTE ON order_total TO PUBLIC;
spool off
SELECT
*
FROM
user_errors
WHERE
name='ORDER_TOTAL'
;
The first command in this script, SET ECHO ON, displays a listing of the package to the screen as it is being compiled. ECHO combined with the SPOOL name command creates a list file (order_total.lst) for debug purposes. This file will contain the compilation of the procedure, including errors, complete with line numbers.
CREATE OR REPLACE PACKAGE name is the command that starts the procedure build in the database. Declarations of objects and subroutines within the package area are visible to your applications. Think of this area as the application interface to your PL/SQL code; at the very least, you must define the procedure entry routine here. Modifications to any specifications in this area require rebuilding your applications. The END statement signifies the end of the package specification area.