Introducing PL/SQL

Learning objective

After completing this topic, you should be able to identify the basic elements of PL/SQL and recognize the uses of PL/SQL.

1. What is PL/SQL?

Procedural Language/Structured Query Language (PL/SQL) is a powerful language that extends SQL to provide procedural programming capabilities.

In addition to providing the querying capability of SQL, it allows you to

• declare variables, constants, and cursors
• use typical programming control structures such as WHILE iterations and IF conditions
• handle errors

You can describe PL/SQL as a block-structured language, because its code can be subdivided into logical units called blocks.

A PL/SQL block can contain subblocks. It may be helpful to view a block as a solution, and a subblock as a solution within the solution.

You can divide a PL/SQL block into four parts, which are ordered as follows:

• DECLARE
• BEGIN
• EXCEPTION
• END

DECLARE

The optional DECLARE part allows you to define variables, types, and constants. You must declare any variables or constants before using them in other statements or declarations.

Whatever you declare in this part, you can use in areas of code where PL/SQL expressions are permitted.

BEGIN

The mandatory BEGIN part marks the beginning of the block's code.

It can contain a combination of SQL statements and procedural statements.

In terms of languages, this code will consist of
SQL and PL/SQL commands.

EXCEPTION

The optional EXCEPTION part is where you handle errors.

Here, you can define local exceptions that will typically arise as a result of an error condition.

END

The mandatory END part ends a block of code.

For every BEGIN statement, you must include an END statement.

Here is an example of a PL/SQL block. It uses a variable called stmt to indicate which of two consecutive SELECT statements failed to retrieve data.

The DECLARE part declares variables stmt and name.

DECLARE
 stmt INTEGER;
 name VARCHAR2(100);
BEGIN
 stmt := 1;  -- designates 1st SELECT statement
 SELECT table_name INTO name FROM user_tables WHERE
 table_name LIKE 'ABC%';
 stmt := 2;  -- designates 2nd SELECT statement
 SELECT table_name INTO name FROM user_tables WHERE
 table_name LIKE 'XYZ%';
EXCEPTION
 WHEN NO_DATA_FOUND THEN
   dbms_output.put_line('Table name not found in query ' || stmt);
END;

The BEGIN statement invokes the queries.

And the EXCEPTION statement handles the error of a query retrieving no data.

Question

Match the parts of a PL/SQL block to what they do.

Options:

1. DECLARE
2. BEGIN
3. EXCEPTION
4. END

Targets:

1. This mandatory part can include SQL and procedural statements
2. This optional part handles errors
3. This mandatory part terminates the block
4. This optional part defines variables, types and constants

Answer

The optional DECLARE part defines variables, types, and constants, and the mandatory BEGIN part can include SQL and procedural statements. The optional EXCEPTION part handles errors, and the mandatory END part terminates the block.

Whatever you declare in the DECLARE part, you can use in areas of code where PL/SQL expressions are permitted. You must declare constants and variables before using them in statements or other declarations.

The code in the BEGIN statement marks the beginning of the PL/SQL block and consists of SQL and PL/SQL commands.

In the optional EXCEPTION part, you define local exceptions that will typically arise as a result of an error condition.

For every BEGIN statement, you must include an END statement.

2. PL/SQL structures

PL/SQL combines query capabilities with programming capabilities.

So, in addition to being able to query the database, you can use it to generate information using algorithms and to handle database events.

PL/SQL provides several programmatic structures:

• Anonymous blocks
  
• Functions
• Procedures
• Triggers
• Data types
• Packages

Anonymous blocks

An anonymous block is an unnamed block typically nesting within a named block.

Among other things, the named block could be a procedure or a function.

By nesting anonymous blocks within blocks, you can gain greater programmatic control. This anonymous block handles the purchase of tennis rackets.

The code for this block is as follows:

DECLARE
qty_on_hand NUMBER(5);
BEGIN
SELECT quantity INTO qty_on_hand FROM
inventory
WHERE product = 'TENNIS RACKET'
FOR UPDATE OF quantity;
IF qty_on_hand > 0 THEN -- check quantity
UPDATE inventory SET quantity = quantity - 1
WHERE product = 'TENNIS RACKET';
INSERT INTO purchase_record
VALUES ('Tennis racket purchased', SYSDATE);
ELSE
INSERT INTO purchase_record
VALUES ('Out of tennis rackets', SYSDATE);
END IF;
COMMIT;
END;

Functions

A function is a block that uses PL/SQL's RETURN command to return a single value.

It differs from a procedure in that it returns this value.

The onscreen code creates the function get_bal which returns an account balance


The code to do this is:
CREATE FUNCTION get_bal(acc_no IN NUMBER)
RETURN NUMBER
IS acc_bal NUMBER(11,2);
BEGIN
SELECT order_total
INTO acc_bal
FROM orders
WHERE customer_id = acc_no;
RETURN(acc_bal);
END;

Procedures

A procedure is a block that executes a specific action.

Unlike a function, a procedure does not use the RETURN command to return a value.

The onscreen code creates the procedure remove_emp to delete records from an employees table

The code to do this is:

CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
tot_emps NUMBER;
BEGIN
DELETE FROM employees
WHERE employees.employee_id = remove_emp.employee_id;
tot_emps := tot_emps - 1;
END;

Triggers

A trigger is a block that is executed when an event happens in the database.

An event can be a tabular change such as the insertion of a row into a table.

It can also be a database event, such as a user login. The onscreen syntax indicates how a trigger should display.

The syntax for the trigger statement is:
CREATE TRIGGER schema.trigger_name
BEFORE
DELETE OR INSERT OR UPDATE
ON schema.table_name
pl/sql_block

Data types

An object type is a kind of data type that can include functionality as well as actual values. You can define your own data types.

In the onscreen code the data_typ object type is created and given one member function prod. The prod function is implemented within the CREATE TYPE BODY statement:

The code to do this is as follows:

CREATE TYPE data_typ AS OBJECT
( year NUMBER,
MEMBER FUNCTION prod(invent NUMBER) RETURN NUMBER
);
/

CREATE TYPE BODY data_typ IS
MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS
BEGIN
RETURN (year + invent);
END;
END;

Packages

A package groups together logically connected functions, procedures, and anonymous blocks.

The following code creates the specification for the emp_mgmt package.

CREATE OR REPLACE PACKAGE emp_mgmt AS
FUNCTION hire (last_name VARCHAR2, job_id VARCHAR2,
manager_id NUMBER, salary NUMBER,
commission_pct NUMBER, department_id NUMBER)
RETURN NUMBER;
FUNCTION create_dept(department_id NUMBER, location_id NUMBER)
RETURN NUMBER;
PROCEDURE remove_emp(employee_id NUMBER);
PROCEDURE remove_dept(department_id NUMBER);
PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER);
PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER);
no_comm EXCEPTION;
no_sal EXCEPTION;
END emp_mgmt;

Question

In which of the following structures can you use PL/SQL?

Options:

1. Anonymous blocks
   
2. Functions
   
3. Packages
4. Procedures
   
5. SELECT statements
6. Triggers
   

Answer

Anonymous blocks, functions, packages, procedures, and triggers are all structures you can code using PL-SQL.

Option 1 is correct. An anonymous block is an unnamed block, generally found inside a named block. By nesting anonymous blocks within blocks, you can gain greater programmatic control.

Option 2 is correct. A function is a block that uses PL/SQL's RETURN command to return a single value.

Option 3 is correct. A package groups together logically connected functions, procedures, and anonymous blocks.

Option 4 is correct. A procedure is a block that executes a specific action but returns nothing.

Option 5 is incorrect. You cannot use PL/SQL in a SELECT statement, although you can include a SELECT statement within a PL/SQL block.

Option 6 is correct. A trigger is a block that is executed when an event happens in the database.

Question

Match the programmatic structures with their definitions.

Options:

1. Anonymous block
2. Function
3. Procedure
4. Trigger

Targets:

1. An unnamed block
2. A block that uses the RETURN command to return a single value
3. A block that executes a specific action but does not use a value
4. A block that is executed when an event happens in the database

Answer

An unnamed block is an anonymous block. A function is a block that uses the RETURN command to return a single value. A procedure is a block that executes a specific action but does not use the RETURN command. A trigger is a block that is executed when an event happens in the database. And a package is a grouping of logically connected functions, procedures, and anonymous blocks.

You typically find an anonymous block nested within a named block.

Unlike a procedure, which performs a specific action but does not return a value, a function returns a value.

Whereas a function uses the RETURN command to return a value, a procedure merely executes a specific action.

An event can be a tabular change, such as the insertion of a row into a table. It can also be a database event, such as a user login.

Summary

PL/SQL extends SQL to provide procedural programming capabilities. Because its code is made up of logical units or blocks, it is sometimes described as a block language. A PL/SQL block can be divided into DECLARE, BEGIN, EXCEPTION, and END parts. Only the BEGIN and END parts are mandatory.

PL/SQL combines query capabilities with programming capabilities. You can use blocks of PL/SQL in programmatic structures such as anonymous blocks, functions, procedures, triggers, packages, and object types.