PL/SQLProcedural SQL
SQL does not support the conditional execution of procedures as well as looping operations.
To overcome the lack of procedural functionality in SQL and to provide standardization, SQL-99 standard defines the use of persistent stored modules (PSM).
What is PSM?
A PSM is a block of code containing standard SQL statements and procedural extensions that is stored and executed at the DBMS server.
The PSM represents business logic that can be encapsulated, stored and shared among multiple database users.
Oracle implements PSMs through its procedural SQL language.
What is PL/SQL and where can PL/SQL be used?
Procedural SQL (PL/SQL) is a language that makes it possible to use and store procedural code and SQL statements within the database and merge SQL and traditional programming constructs. The procedural code is executed as a unit by the DBMS when it is invoked b the end user.
PL/SQL is used to create
Anonymous PL/SQL blocks (without name)
Stored Procedures
PL/SQL functions
PL/SQL block
The PL/SQL block starts with the DECLARE section in which the variables, the data types or the initial values are declared.
The PL/SQL block ends with the word END
The PL/SQL block can be executed by typing / and press enter key.
Each statement inside the PL/SQL code must end with a semicolon “;”.
To display the output use the command dbms_output.put_line(‘any string’);.
Conditional statement or Looping statements can be used after declare statements;
The SELECT statement uses the INTO keyword inside the PL/SQL block of code to assign the output of the query to a PL/SQL variable. If the SELECT statement returns more than one value, you will get an error.
7
Commands included in PL/SQL
Comments: PL/SQL program includes commands such as Comments statements that are lines of text that explain or document a program step or series of steps.
Not executed by interpreter
To create a block of comments enclosed between /* and */
To create one line comment type two hyphens -- at the beginning of the line.
PL/SQL programs have
Assignment statements that assign values to variables
Conditional structures to perform decisions
Looping structures to repeat instructions multiple times
SQL commands that retrieve data.
Procedural Statements
An assignment statement has the following syntax
variable_name := value;
current_s_first_name := 'John';
current_s_first_name := s_first_name;
PL/SQL output buffer
Memory area on database server
Stores program’s output values before they are displayed to user
Should increase the size of the buffer as
SET SERVEROUTPUT ON SIZE buffer_size
Default buffer size
2000 bytes
To display more than just a few lines of output, it is better to make the output buffer larger than the default size.
Maximum is 1000000 bytes
Display program output
The display_text value can
contain literal character strings such as ‘ My name is Roohi’ or
variable names such as current_s_first.
Display maximum of 255 characters of text data
13
Create program in text editor
Copy and paste program commands into SQL*Plus window
Press Enter after last program command
Type front slash ( / )
DECLARE
DBMS_OUTPUT.PUT_LINE(todays_date);
DBMS_OUTPUT.PUT_LINE(x);
END;
Write a PL/SQL program to display the current date as shown below
Today’s date is 27-Feb-2012
A Guide to Oracle10g
PL/SQL Data Conversion Functions
Sometimes the PL/SQL interpreter is unable to implicitly convert value and an error occurs.
Other times, PL/SQL interpreter performs an implicit conversion and produce unexpected/invalid data
Explicit data conversions
Convert variables to different data types using data conversion functions that are built into PL/SQL.
16
Display today’s date using concatenated character string
To concatenate two strings in PL/SQL, you use the double bar (||) operator:
new_string := string1 || string2;
6 END;
PL/SQL Decision Control Structures
Use IF/THEN/ELSE to execute code if condition is true or FALSE
IF condition THEN
ELSE
END IF;
18
PL/SQL Decision Control Structures
Use IF/ELSIF decision control structure to test for many different conditions.:
IF condition1 THEN
ELSIF condition2 THEN
ELSIF condition3 THEN
...
conditions are TRUE;
LOOP…EXIT WHEN
The LOOP...EXIT WHEN Loop
The LOOP…EXITWHEN loop can also be either a pretest or a post test loop.
LOOP
The WHILE...LOOP
The WHILE….LOOP is a pretest loop that evaluates the exit condition before it executes any program statement.
WHILE condition LOOP
Does not require explicit counter increment
The loop counter variable and it start and end values are declared in the loop’s FOR statement.
Automatically increments counter variable until it reaches the end value.
FOR counter_variable IN start_value .. end_value
LOOP
Action queries can be used as in SQL*Plus
May use variables in action queries
DDL commands may not be used in PL/SQL
24
Using SQL Commands in PL/SQL Programs
Author (A) - Okay that slide title is figure caption and not heading?
PL/SQL block to insert a new row in the VENDOR table
SET SERVEROUTPUT ON
VALUES (25772,’Clue Store’, ‘Issac Hayes’, ‘456’, ‘323-2009’, ‘VA’, ‘N’);
DBMS.OUTPUT.PUT_LINE(‘New Vendor Added’);
/ -- to execute An anonymous PL/SQL Program
PL/SQL block to count the number of products having different price ranges
DECLARE
SELECT COUNT(P_CODE) INTO W_NUM FROM PRODUCT
WHERE P_PRICE BETWEEN W_P1 AND W_P2;
DBMS_OUTPUT.PUT_LINE (‘The number of products between’ ||W_P1||’and’ || W_P2 || ‘is ’ || W_NUM);
W_P1 := W_P2+1;
W_P2 := W_P2+ 50 ;
STORED PROCEDURES
A stored procedures is a named collection of procedural and SQL statements.
Stored procedures are stored in the database.
They can be used to encapsulate and represent business transactions.
Syntax:
[variable_name data_type[:=initial_value] ]
Refer to the handouts for more explanation on IN/OUT arguments
STORED PROCEDURES
Advantages:
Stored procedures substantially reduce traffic as there is no transmission of individual SQL statements over the network.
Stored procedures increase performance because all the transactions are executed locally on the RDBMS.
Create a procedure to assign an additional 5percent discount for all products when the quantity on Hand is more than or equal to twice the minimum quantity.
CREATE OR REPLACE PROCEDURE PRC_PROD_DISCOUNT
AS BEGIN
UPDATE PRODUCT
EXEC procedure_name[(paramenter_list)]
Create a procedure to assign an additional percent discount (an input variable) for all products when the quantity on Hand is more than or equal to twice the minimum quantity.
CREATE OR REPLACE PROCEDURE PRC_PROD_DISCOUNT(WPI IN NUMBER) AS
BEGIN
IF ((WPI <= 0) OR (WPI >= 1)) THEN -- validate WPI parameter
DBMS_OUTPUT.PUT_LINE('Error: Value must be greater than 0 and less than 1');
ELSE -- if value is greater than 0 and less than 1
UPDATE PRODUCT
Oracle Sequences
MS Access AutoNumber data type fills a column with unique numeric values
Oracle sequences
Named, used anywhere a value expected
Not tied to a table or column
Generate numeric values that can be assigned to any column in any table
Created and deleted at any time
34
Create a procedure which adds a new invoice.
CREATE OR REPLACE PROCEDURE PRC_INV_ADD (W_CUS_CODE IN VARCHAR2, W_DATE IN DATE)
AS BEGIN
CREATE OR REPLACE PROCEDURE PRC_CUS_ADD
(W_LN IN VARCHAR, W_FN IN VARCHAR, W_INIT IN VARCHAR, W_AC IN VARCHAR, W_PH IN VARCHAR)
AS
BEGIN
-- note that the procedure uses the CUS_CODE_SEQ sequence created earlier. Attribute names are required when not giving values for all table attributes
INSERT INTO CUSTOMER(CUS_CODE,CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE)
VALUES (CUS_CODE_SEQ.NEXTVAL, W_LN, W_FN, W_INIT, W_AC, W_PH);
DBMS_OUTPUT.PUT_LINE ('Customer ' || W_LN || ', ' || W_FN || ' added.');
END;
The result is
Customer Walker, James added.
PL/SQL procedure successfully completed.
Create a procedure which adds a new product line row for a given invoice.
CREATE OR REPLACE PROCEDURE PRC_LINE_ADD (W_LN IN NUMBER, W_P_CODE IN VARCHAR2, W_LU NUMBER)
AS
INSERT INTO LINE
DBMS_OUTPUT.PUT_LINE('Invoice line ' || W_LN || ' added');
END;
In order to insert a new record in the LINE table, we need to know the Invoice Number, Product Code, Line Units (the values are obtained from the input parameters), Line Price ( the value is obtained from the Product Price i.e. p_price) in a PRODUCT Table
Cursor
A cursor is a special construct in procedural SQL to hold the data rows returned by an SQL query.
A cursor is a reserved area of memory in which the output of the query is stored like an array holding columns and rows.
Cursors are held in a reserved memory area in the DBMS server, not in the client computer.
Cursors
A cursor is a pointer to a memory location on database server that the DBMS uses to process a SQL query
Cursors are used to retrieve and manipulate database data in PL/SQL programs
Types:
Implicit
Explicit
A memory location on the database server
Contains information about query e.g. no. of rows that the query processes, and a machine language representation of the query etc.
Created by INSERT, UPDATE, DELETE, or SELECT
Active set
For SELECT queries, the context area also stores the active set, which is the set of data rows that query retrieves
Implicit cursor
is a pointer to the context area
For Implicit cursor, there is no need to write commands explicitly to create it or retrieve its value.
Implicit Cursors
Executing a SELECT query creates an implicit cursor i.e. Implicit cursor can be used to assign the output of a SELECT query to PL/SQL program variables when we are sure that the query will return one and only one record.
If the query returns more than one record, or does not return any record, an error occurs.
Using an Implicit Cursor
To retrieve data values from a query’s implicit cursor into PL/SQL program variables add INTO clause :
SELECT field1, field2, ...
INTO variable1, variable2, ...
FROM table1, table2, ...
WHERE join_ conditions
AND search_condition_to_retrieve_1_record;
The variables must have been declared in the program’s declaration section
The variables must have the same data types as the associated database fields.
Reference Variables
Reference variables directly reference specific database field or record and assume data type of the associated field or record
%TYPE: is a reference data type which assumes the data type of a database field
data declaration syntax:
variable_name tablename.fieldname%TYPE;
e.g. current_c_last Customer.Cus_Lname%TYPE;
The current_c_last variable would assume a data type of VARCHAR2(15), because this is the data type of Cus_Lname field in the CUSTOMER table
Implicit Cursors (continued)
Useful to use %TYPE reference data type to declare variables used with implicit cursors
variable_name tablename.fieldname%TYPE
Reference Variables
%ROWTYPE: is a reference data type which assumes the data type of a database record
data declaration syntax:
row_variable_name tablename%ROWTYPE;
e.g. customer_row Customer%ROWTYPE;
The CUSTOMER_ROW references all of the fields in the CUSTOMER TABLE, and each field has the same data type as its associated database fields.
Reference Variables
How do you refer to individual data field within a %ROWTYPE variable?
Use the syntax row_variable_name.fieldname
If u have to refer to f_first name, you can write
customer_row.cus_fname
customer_row.cus_balance
Reference Data Type used with Cursors
%TYPE: is a reference data type which assumes the data type of a database field
data declaration syntax:
variable_name Cursor_Name.fieldname%TYPE;
e.g. current_c_last cus_cursor.cus_fname%TYPE;
%ROWTYPE: is a reference data type which assumes the data type of a database record
data declaration syntax:
variable_name Cursor_Name%ROWTYPE;
10 END;
PL/SQL procedure successfully completed.
PL/SQL program that displays last name and first name from the CUSTROMER table where cus_code =10010 using an implicit cursor
SQL> DECLARE
9 DBMS_OUTPUT.PUT_LINE(' The customer ''s name is ' || current_f_first || ' ' || current_f_last);
10 END;
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 8
Error “ORA-01422: exact fetch returns more than requested number of rows”
Implicit cursor query tried to retrieve multiple records
When implicit cursor query returns no records
SQL> DECLARE
10 END;
Explicit Cursors
Explicit cursors are created to retrieve and display data in PL/SQL programs for query that might
Retrieve multiple records
Return no records at all
Explicit cursors must be explicitly declared in the program’s declaration section
Explicit commands are written to process the cursor.
Steps for creating and using explicit cursor
Declare cursor
Open cursor
Using an Explicit Cursor
Declaring an Explicit Cursor
By declaring an EC, a memory location is created on the database server that processes query and stores the records that the query retrieves
CURSOR cursor_name IS select_query;
Cursor_name can be any valid PL/SQL variable name.
Select_query is the query that retrieves the desired data value ( simple, complex , nested query etc.)
The query’s search condition can contain PL/SQL variables as long as the variables are declared before the cursor is declared and are assigned values before the program opens and processes the cursor.
Using an Explicit Cursor
OPEN cursor_name;
When the open command is executed, the PL/SQL interpreter examines or parses the cursor’s SQL query, confirms that the query contains no syntax errors and translates the query into a machine language format.
The system stores the parsed query in the cursor’s context area and creates the memory structure that will store the active set.
The cursor does not retrieve the data values at this stage.
Using an Explicit Cursor
Fetching the data rows
FETCH cursor_name INTO variable_name(s);
EXIT WHEN cursor_name%NOTFOUND;
It retrieves the query data from the database into the active set, one row at a time.
The FETCH command associates each field value with a program variable.
The FETCH command is executed within a loop in order for the query to return several records.
The cursor variable or variables are declared using either the %TYPE or %ROWTYPE reference data type.
Using an Explicit Cursor
Active set pointer
It is a pointer that indicates the memory location of next record that is retrieved from database
When the FETCH command executes past the last record of the query, the active set pointer points an empty record.
The exit condition cursor_name%NOTFOUND determines whether the last cursor record has been fetched by checking if the active pointer is pointing to the last record and then the loop exits.
Closing the cursor
Close the cursor after it processes all of the records in the active set so that its memory area and resources are available to the system for other tasks.
CLOSE cursor_name;
The system automatically closes the cursor when the program that processes the cursor ends.
Explicit Cursor with %TYPE
It is used when a cursor retrieves one data field.
Using cursors create a procedure that lists all products that have a quantity on hand greater than the average quantity on hand for all products.
CREATE OR REPLACE PROCEDURE PRC_CURSOR_EXAMPLE IS
W_P_CODE PRODUCT.P_CODE%TYPE;
W_P_DESCRIPT PRODUCT.P_DESCRIPT%TYPE;
BEGIN
EXIT WHEN PROD_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('--- END OF REPORT ----');
/
Write a program that uses an Explicit Cursor to retrieve and display the invoice number value (using %TYPE variable) for every record in the INVOICE table for the customer code 10014.
DECLARE
LOOP
It is used when a cursor retrieves multiple data fields.
Use a single variable that has %ROWTYPE reference data type
Assume the same data type as the row that the cursor retrieves.
How to declare a cursor %ROWTYPE variable
row_variable_name cursor_name%ROWTYPE
To reference individual data fields within a %ROWTYPE variable, use
row_variable_name.fieldname
e.g. customer_row.room to reference the ROOM field in the location_row variable.
Write a program that uses an Explicit Cursor to retrieve and display the invoice number value (using %TYPE variable) for every record in the INVOICE table in which the customer code is 10014.
DECLARE
END LOOP;
CLOSE invoice_cursor;
Triggers
A Trigger is procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a given data manipulation event.
A trigger is invoked before or after a data row is actually inserted, updated or deleted.
A trigger is associated within a database table.
Each database table may have one or more triggers.
A trigger is executed as part of the transaction that triggered it.
Uses of Triggers
Triggers can be used to enforce constraints that cannot be enforced at the DBMS design and implementation levels.
Triggers add functionality by automating critical actions and providing appropriate warnings and suggestions for remedial action.
Triggers can be used to update table values, insert records in tables and call other stored procedures.
Syntax to create a Trigger in Oracle
CREATE OR REPLACE TRIGGER trigger_name
[BEFORE/AFTER] [DELETE/ INSERT/ UPDATE OF column_name] ON table_name
[FOR EACH ROW]
Syntax to create a Trigger in Oracle
The Triggering timing: BEFORE or AFTER. This timing indicates when the trigger’s PL/SQL code executes; in this case, before or after the triggering statement is completed. (BEFORE means before the changes are permanently saved to disk but after the changes are made in memory.)
The triggering event: the statement that causes the trigger to execute (INSERT, UPDATE or DELETE).
The triggering level:
A statement-level trigger is assumed if we omit the FOR EACH ROW keywords. This executes only once before or after the triggering statement is completed.
A row-level trigger requires use of the FOR EACH ROW keywords. This is executed once for reach row affected by the triggering statement.
The Triggering action: The PL/SQL code enclosed between the BEGIN and END keywords. Each statement inside the PL/SQL code must end with a semicolon.
Example
Create a trigger which should be activated when a product’s quantity on hand is updated when the product is sold, the system should automatically check whether the quantity on hand falls below its minimum allowable quantity.
CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER
AFTER INSERT OR UPDATE OF P_QOH ON PRODUCT
BEGIN
--------
SELECT * FROM PRODUCT WHERE P_CODE =‘11QER/31’;
SECOND version of the TRG_PRODUCT_REORDER TRIGGER
What happens if we reduce the minimum quantity of product ‘2232/QWE’?
When we update the P_MIN column, the trigger is never executed. TRG_PRODUCT_REORDER executes only after an update of the P_QOH column.
To avoid that inconsistency we need to modify the trigger event.
CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER
AFTER INSERT OR UPDATE OF P_QOH, P_MIN ON PRODUCT
BEGIN
/
What happens if we change the P_QOH value for product ‘11QER/31 from 4 to 29 when the P_MIN is set to 5 and P_REORDER is 1’
The P_REORDER flag is still set to 1 because the trigger sets the P_REORDER value only to1;it does not reset the value to 0, even if such an action is clearly required when the inventory level is back to a value greater than the minimum value.
Why did not the trigger change the reorder flag to 0?
Third version of the TRG_PRODUCT_REORDER TRIGGER
To avoid that inconsistency we need to modify the trigger event.
CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER
BEFORE INSERT OR UPDATE OF P_QOH, P_MIN ON PRODUCT
FOR EACH ROW
Third version of the TRG_PRODUCT_REORDER TRIGGER
The computer cannot change anything directly in permanent storage (disk).It must read the data from permanent storage to primary memory; then it makes the change in primary memory and finally, it writes the changed data back to permanent memory (disk).
All changes are done first in primary memory, then are transferred to permanent memory.
Third version of the TRG_PRODUCT_REORDER TRIGGER
 
Create a Trigger to update an attribute in a table other than the one being modified
 
AFTER INSERT ON LINE
Example:
Create a trigger to update the customer balance(CUS_BALANCE) in the CUSTOMER table after inserting every new LINE row. The trigger code is
CREATE OR REPLACE TRIGGER TRG_LINE_CUS
AFTER INSERT ON LINE
BEGIN
-- this trigger fires up after an INSERT of a LINE
-- it will update the CUS_BALANCE in CUSTOMER
 
-- 1) get the CUS_CODE
SELECT CUS_CODE INTO W_CUS
W_TOT := :NEW.LINE_PRICE * :NEW.LINE_UNITS;
UPDATE CUSTOMER
 
Although triggers are independent objects, they are associated with database tables. When you delete a table, all its trigger objects are deleted with it.
To delete a trigger
Trigger Action Based On Conditional DML Predicates
Triggers are created depending on the type of DML statements such as INSERT or UPDATE or DELETE that fires the trigger.
But how would we know which one of the three statements caused the trigger to execute? In those cases, we could use the following syntax:
IF UPDATTING THEN ….ENDIF;
IF INSERTING THEN… ENDIF;
IF DELETING THEN … ENDIF;
We can create our own stored functions.
Stored procedures and functions are very similar.
A stored function is basically a named group of procedural and SQL statements that returns a value indicated by a RETURN in its program code.
We can use function in the right side of the assignment statement.
PL/SQL Stored Functions
BEGIN