• Identify various types of PL/SQL objects.
• Describe triggers and triggering events.
• Identify initialization parameters that affect PL/SQL performance.
Focused Explanation
Identifying PL/SQL Objects
Oracle Procedural Language Extension to SQL, also referred to as PL/SQL, is derived from the Ada programming language. PL/SQL has features, such as sequential and conditional controls, looping constructs, exception handling constructs, records, and collections. PL/SQL also has object-oriented features, such as method overloading and inheritance. The various types of PL/SQL objects are:
functions, procedures, packages, and triggers.
Working with Functions
Functions are PL/SQL programs that execute the SQL statements and return a value through a RETURN statement. Oracle also provides built-in functions, such as SYSDATE, COUNT, and SUBSTR. A return data type is also associated with each function because the function returns value. Functions can be invoked within an expression of the compatible data type. The following are examples of declaring and using functions:
Function declared as a default value:
DECLARE
today DATE DEFAULT SYSDATE;
Function called in an assignment statement:
Today := SYSDATE;
Function called in a Boolean expression:
IF TO_CHAR(SYSDATE,’DAY’) = ‘Monday’
You can also create a function using the CREATE FUNCTION statement. The following is an example of statements that create a function called is_weekend, which accepts a DATE parameter, check_date, returns ‘YES’ if the date is a weekend day, and returns ‘NO’ if the date is a weekday:
CREATE OR REPLACE FUNCTION is_weekend (check_date IN DATE DEFAULT SYSDATE) RETURN VARCHAR2 AS
BEGIN
CASE TO_CHAR(check_date, ‘DY’) WHEN ‘SAT’ THEN
Functions can include the OR REPLACE clause in the CREATE FUNCTION statement. The OR REPLACE clause specifies that if the existing function already exists in the database, its definition will be overwritten with the new function definition. If you do not include the OR REPLACE clause with the CREATE
FUNCTION statement and a function with the same name already exists in the database, an exception will be raised.
Working with Procedures
Procedures are PL/SQL programs that execute one or more SQL and PL/SQL statements. Procedures receive and return values only by using their parameter list. Oracle provides fewer built-in procedures than built-in functions. You can create a procedure using the CREATE PROCEDURE statement. The following is an example of the statements to create a procedure named demo_proc, which executes a SELECT statement against the emp table to retrieve an employee’s name and displays the employee name:
CREATE OR REPLACE PROCEDURE demo_proc IS
ename1 varchar2(20);
BEGIN
SELECT ename INTO ename1 FROM emp WHERE empno=7369;
DBMS_OUTPUT.PUT_LINE(‘employee name=‘||ename1);
END;
The IS clause in the procedure is the synonym of AS in the function description. You can invoke a procedure as a stand-alone statement within a PL/SQL program by using the CALL or EXEC statements.
Working with Packages
A package is a container for procedures, functions, and other data structures, such as records, cursors, variables, and constants. A package is divided into two sections, the public section is called the package specification, and the private section is called the package body. The package specification includes
declarations for the procedures, functions, and data structures that can be accessed from other programs. The package body contains programming statements for procedure and function
implementations. You can use the CREATE PACKAGE statement to create a package specification.
The following example creates a package specification for the package named table_util, which has a version1 function and a truncate1 procedure that can be accessed by other programs:
CREATE OR REPLACE PACKAGE table_util IS FUNCTION version1 RETURN VARCHAR2;
PROCEDURE truncate1 (table_name IN VARCHAR2);
END table_util;
You can grant privileges on a package at the package specification level. The EXECUTE privilege allows you to execute any program or use any data structure declared in the package specification.
To fully implement a package, you must declare a package body with the same name as the package specification. You cannot declare a package body without first declaring the package specification. The package body contains programming statements that implement the procedures, functions, and data structures that are declared in the package specification. You can use the CREATE PACKAGE BODY statement to create a package body. The following is an example of a package body, which is named table_util and implements a function named version1, and a procedure named truncate1:
CREATE OR REPLACE PACKAGE BODY table_util IS version_string VARCHAR2(8) :='1.0.0';
FUNCTION version1 RETURN VARCHAR2 IS BEGIN
RETURN version_string;
END;
PROCEDURE truncate1 (table_name IN VARCHAR2) IS BEGIN
IF UPPER(table_name) = 'ORDER_STAGE' THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE' || UPPER(table_name);
ELSE
RAISE_APPLICATION_ERROR(-20010, 'Invalid table for truncate');
END IF;
END;
END table_util;
In this package body, the version1 function returns the version_string string. The truncate1 procedure in the package body deletes all the rows from a table using the TRUNCATE TABLE statement if the name of the table is order_stage or ORDER_STAGE otherwise an error, Invalid table for truncate, is raised.
Triggers and Triggering Events
PL/SQL programs that are invoked in response to an event in the database are called triggers. You can create triggers on DML events, DDL events, and database events.
DML Event Triggers
DML triggers fire when the DML event specified by the trigger definition occurs. When you include the FOR EACH ROW clause in the trigger definition, the trigger is fired once for each row that is affected by the DML statement. If the DML event list includes the UPDATE event, the trigger can be further restricted to fire only when updates of specific columns occur. The following example creates a trigger that fires before any insert or any update of the ename column takes place on the empl table:
CREATE OR REPLACE TRIGGER emp_trig1 BEFORE INSERT OR UPDATE OF ename ON empl
FOR EACH ROW BEGIN
IF :new.name not like '%SMITH%' THEN
RAISE_APPLICATION_ERROR(-20000, 'No People Named Smith Allowed!');
END IF;
END;
Table 4-4 shows the DML triggering events and their descriptions.
DML Triggering Event Description
INSERT Is fired when a row is added to a table or a view.
UPDATE Is fired when an UPDATE statement changes a row in a table or a view.
DELETE Is fired when a row is removed from a table or a view.
Table 4-4: DML Triggering Events
If you define multiple triggers on a table, then these triggers fire in the following sequence:
1. Before statement triggers 2. Before row triggers 3. After row triggers 4. After statement triggers DDL Event Triggers
DDL triggers fire when DDL changes occur for a specific schema or all the schemas in a database. When you specify the ON DATABASE clause, the trigger is fired for the specific event on any schema in the database.
Table 4-5 lists the DDL triggering events and their descriptions.
DDL Triggering Event Description
ALTER Is fired when an ALTER statement changes a database object.
ANALYZE Is fired when a database validates the structure and gathers or deletes the statistics.
ASSOCIATE STATISTICS Is fired when the ASSOCIATE STATSTICS statement is used to associate a statistic type with a database object.
AUDIT Is fired when a database records an audit action.
COMMENT Is fired when a comment on a table or a column is modified.
CREATE Is fired when a database object is created.
DISASSOCIATE STATISTICS Is fired when a DISASSOCIATE STATISTICS statement is used to disassociate a statistic type from a database object.
DROP Is fired when a DROP statement removes an object from a database.
GRANT Is fired when a GRANT statement assigns a privilege.
NOAUDIT Is fired when a NOAUDIT statement changes database auditing.
RENAME Is fired when a RENAME statement changes an object name.
REVOKE Is fired when a REVOKE statement revokes a privilege.
TRUNCATE Is fired when a TRUNCATE statement purges a table.
Table 4-5: DDL Triggering Events Database Event Triggers
Database event triggers are fired when the specified database events occur.
Table 4-6 lists the database triggering events and their descriptions.
Database Triggering Event Description
LOGON Is fired after a client application successfully logs on to the database.
LOGOFF Is fired before a client application logs off the database.
STARTUP Is fired immediately after a database is opened.
SHUTDOWN Is fired immediately before a database shuts down.
SERVERERROR Is fired after a server error message is logged.
SUSPEND Is fired when a server error causes a transaction to be suspended.
Table 4-6: Database Triggering Events
Initialization Parameters for PL/SQL Performance
The initialization parameters that affect the performance of PL/SQL are:
• PLSQL_WARNING – Enables you to use compiler warnings to identify potential problems that can generate run-time errors.
• PLSQL_DEBUG – Interrupts all subsequent PL/SQL compilations and includes additional debugging information.
• PLSQL_OPTIMIZE_MODE – Optimizes the compiler to improve PL/SQL performance.
• PLSQL_CODE_TYPE – Specifies whether to compile the PL/SQL code into the default-interpreted byte code or native machine code.