PL/SQL Tutorial
Welcome to PLSQLTutorial.com website! If you are looking for a complete PL/SQL tutorial, you are at the right place. At PL/SQL Tutorial.com, we are working hard to make a comprehensive PL/SQL Tutorial to help you to learn PL/SQL quick and fun.
So What is PL/SQL?
PL/SQL stands for “Procedural Language extensions to the Structured Query Language or SQL.”. SQL is the powerful tool for both querying and update data in relational databases. Oracle introduced PL/SQL to extend some limitations of SQL to provide a more comprehensive solution for building mission-critical applications running on Oracle database.
We assume that you have fundamental knowledge of database and SQL to start our PL/SQL tutorial. If this is not the case, you can follow basic SQL tutorial on the website SQLTutorial.org to have a good start.
Basic PL/SQL Tutorial
This section is targeted as a good starting point for those who are new to PL/SQL. However, if you are very familiar with the language and also want to glance through these tutorials as a refresher, you may even find something useful that you haven’t seen before!
- PL/SQL Variables
- PL/SQL Block Structure
- PL/SQL Function
- PL/SQL Procedure
- PL/SQL Nested Block
- PL/SQL IF Statement
- PL/SQL CASE Statement
- PL/SQL LOOP Statement
- PL/SQL WHILE Loop Statement
- PL/SQL FOR Loop Statement
- PL/SQL Exception Handling
- PL/SQL Record
- PL/SQL Cursor
- PL/SQL Packages
PL/SQL Variables
In PL/SQL, a variable is a meaningful name of a temporary storage location that supports a particular data type in program. In order to use a variable, you need to declare it in declaration section of PL/SQL block.
PL/SQL Variable Naming Convention
Like other programming languages, a variable in PL/SQL must follow the naming rules as below:
- The variable name must be less than 31 characters. Try to make it as meaningful as possible within 31 characters.
- The starting of a variable must be an ASCII letter. It can be either lowercase or uppercase. Note that PL/SQL is not case-sensitive.
- A variable name can contain numbers, underscore, and dollar sign characters followed by the first character. Again, do not make your variables hard to read and understand to make it easier to maintain in the future.
It is recommended that you should follow the naming conventions in the table 1.1 below to name variables to make it obvious in PL/SQL programs:
Prefix
|
Data Type
|
v_
|
VARCHAR2
|
n_
|
NUMBER
|
t_
|
TABLE
|
r_
|
ROW
|
d_
|
DATE
|
b_
|
BOOLEAN
|
For example, if you want to declare a variable to hold the first name of employee with the data type VARCHAR2 the variable name should be v_first_name.
PL/SQL Variable Declaration
To declare a variable, you type a variable name followed by the data type and terminated by a semicolon (;). You can also explicitly add length constraint to the data type in a set of parentheses. Here is an example of declaring some variables in an anonymous block:
[codesyntax lang="plsql"]
DECLARE
v_first_name varchar2(20);
v_last_name varchar2(20);
n_employee_id number;
d_hire_date date;
BEGIN
NULL;
END;
[/codesyntax]
PL/SQL Variable Anchors
In PL/SQL program, you select different values from columns of a database table into a set of variables. There are new enhancements so the data type of column of the table changed therefore you have to change the PL/SQL program also to make the type of variable compatible with the new changes. PL/SQL provides you a very useful feature called variable anchors. It refers to the use of keyword %TYPE to declare a variable with the data type is the column data type in a table.
Let’s take a look at the employees table in HR sample database below:
Employees Table
[codesyntax lang="plsql"]
DECLARE
v_first_name EMPLOYEES.FIRST_NAME%TYPE;
v_last_name EMPLOYEES.LAST_NAME%TYPE;
n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
d_hire_date EMPLOYEES.HIRE_DATE%TYPE;
BEGIN
NULL;
END;
/
[/codesyntax]
PL/SQL Variable Assignment
To assign a value or a variable to a variable in PL/SQL, you use the assignment operator ( := ) which is a colon( : ) followed by an equal sign( = ). See the code listing below to have a better understanding:
[codesyntax lang="plsql" title="PL/SQL variables assignment" bookmarkname="PL/SQL variables assignment"]
DECLARE
v_first_name EMPLOYEES.FIRST_NAME%TYPE;
v_last_name EMPLOYEES.LAST_NAME%TYPE;
n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
d_hire_date EMPLOYEES.HIRE_DATE%TYPE;
BEGIN
v_first_name := 'Mary';
v_last_name := 'Dose';
d_hire_date := to_date('19700101','YYYYMMDD');
END;
/
[/codesyntax]
You can use INTO of SQL SELECT statement to assign a value to a variable. In this way, the INTO clause move the values from the SELECT query’s column list into corresponding PL/SQL variables.
[codesyntax lang="plsql" title="PL/SQL Initializing Variables" bookmarkname="PL/SQL Initializing Variables"]
You can use INTO of SQL SELECT statement to assign a value to a variable. In this way, the INTO clause move the values from the SELECT query’s column list into corresponding PL/SQL variables.
[codesyntax lang="plsql" title="PL/SQL Initializing Variables" bookmarkname="PL/SQL Initializing Variables"]
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
v_first_name EMPLOYEES.FIRST_NAME%TYPE;
v_last_name EMPLOYEES.LAST_NAME%TYPE;
n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
d_hire_date EMPLOYEES.HIRE_DATE%TYPE;
BEGIN
SELECT employee_id,
first_name,
last_name,
hire_date
INTO n_employee_id,
v_first_name,
v_last_name,
d_hire_date
FROM employees
WHERE employee_id = 200;
DBMS_OUTPUT.PUT_LINE(v_first_name);
DBMS_OUTPUT.PUT_LINE(v_last_name);
DBMS_OUTPUT.PUT_LINE(d_hire_date);
END;
/
[/codesyntax]
Initializing Variables
When you declare a variable, its value is uninitialized and hence is NULL. You can initialize variable a value by assigning it a value in declaration section.
[codesyntax lang="plsql" title="PL/SQL Initialize Variables" bookmarkname="PL/SQL Initialize Variables"]
DECLARE
n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE :=200;
d_hire_date EMPLOYEES.HIRE_DATE%TYPE:=to_date('19700101','YYYYMMDD');
BEGIN
NULL;
END;
/
[/codesyntax]
In PL/SQL, NULL means an unknown value so it has some different characteristic as follows:
- NULL is not equal to anything even itself NULL.
- NULL is not greater than or less than anything else, not even NULL.
- You cannot use logical operator equal (=) or (<>) with NULL. You must use “is NULL” or “is not NULL” to test for NULL values.
PL/SQL Block Structure
Introducing PL/SQL block structure and anonymous block
PL/SQL program units organize the code into blocks. A block without name is known as anonymous block. The anonymous block is the simplest unit in PL/SQL. It is called anonymous block because it is not saved in the database. The anonymous blocks are only one-time use and useful in certain situations such as creating test units. The following illustrates anonymous block syntax:
[codesyntax lang="plsql"]
[DECLARE]
Declaration statements;
BEGIN
Execution statements;
[EXCEPTION]
Exception handling statements;
END;
/
[/codesyntax]
Let’s examine the block structure in detail.
The anonymous block has three basic parts that are declaration, execution, and exception handling. Only execution part is required and the others are optional.
- The declaration part allows you to define data types, structures, and variables. You can also declare a variable in the declaration part by giving it a name, a data type and a initial value. You can both define and declare variables in the declaration part.
- The execution part is required in block structure and it must have at least one statement. The execution part is where you put the execution code or business logic. You can use both procedural and SQL code inside execution part.
- The exception handling part is starting with the keyword EXCEPTION. The exception part is where you put the code to manage exceptions. You can either catch or handle exceptions in this part.
Note that the single forward slash (/) is a signal to tell SQL*Plus to execute the PL/SQL block.
PL/SQL block structure examples
Let’s take a look at the simplest block that does nothing.
[codesyntax lang="plsql"]
BEGIN
NULL;
END;
[/codesyntax]
If you execute the above anonymous block in SQL*Plus you will it issues a message saying that “PL/SQL procedure successfully completed.”
Now if we want to output it on screen we execute the following block:
[codesyntax lang="plsql"]
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello PL/SQL');
END;
/
[/codesyntax]
The first command does not belong to the anonymous block. It is telling SQL*Plus to echo the database’s output to the screen after executing PL/SQL procedure.
In the above examples, you just uses the execution part to execute code. You will learn how to declare variables and handling exceptions in the next tutorials.
Exercise on anonymous block structure
Now it is your turn to create a block and execute it in SQL*Plus that print a greeting message “Hello Word” on screen.
First, you need to login to the SQL*Plus by the account by providing username and password as the figure 1 below.
Figure 1. SQL*Plus Login
Second, type the following code into the SQL*Plus and execute it as the figure 2 below:
Figure 2.PL/SQL Hello World
Congratulation, you’ve finished the first PL/SQL program!
PL/SQL Function
Introducing to PL/SQL function
PL/SQL function is a named block that returns a value. PL/SQL functions are also known as subroutines or subprograms. To create a PL/SQL function, you use the following syntax:
[codesyntax lang="plsql" title="PL/SQL Function Template" bookmarkname="plsqlfunction"]
CREATE [OR REPLACE] FUNCTION {function_name} [(
{parameter_1} [IN] [OUT] {parameter_data_type_1},
{parameter_2} [IN] [OUT] {parameter_data_type_2},...
{parameter_N} [IN] [OUT] {parameter_data_type_N} )]
RETURN {return_datatype} IS
--the declaration statements
BEGIN
-- the executable statements
return {return_data_type};
EXCEPTION
-- the exception-handling statements
END;
/
[/codesyntax]
The {function_name} is the name of the function. Function name should start with a verb for example function convert_to_number.
{parameter_name} is the name of parameter being passed to function along with parameter’s data type {parameter_data_type}. There are three modes for parameters: IN,OUT and IN OUT.
- The IN mode is the default mode. You use the IN mode when you want the formal parameter is read-only. It means you cannot alter its value in the function. The IN parameter behaves like a constant inside the function. You can assign default value to the IN parameter or make it optional.
- The OUT parameters return values to the caller of a subprogram. An OUT parameter cannot be assigned a default value therefore you cannot make it optional. You need to assign values to the OUT parameter before exiting the function or its value will be NULL. From the caller subprogram, you must pass a variable to the OUT parameter.
- In the IN OUT mode, the actual parameter is passed to the function with initial values. And then inside the function, the new value is set for the IN OUT parameter and returned to the caller. The actual parameter must be a variable.
The function must have at least one RETURN statement in the execution part. The RETURN clause in the function header specifies the data type of returned value.
The block structure of a function is the same as an anonymous block except for the addition CREATE [OR REPLACE] FUNCTION, the parameters section, and a RETURN clause.
Examples of PL/SQL Function
We are going to create a function that parses a string and returns a number if the string being passed is a number otherwise it returns NULL.
[codesyntax lang="plsql"]
CREATE OR REPLACE FUNCTION try_parse(
iv_number IN VARCHAR2)
RETURN NUMBER IS
BEGIN
RETURN to_number(iv_number);
EXCEPTION
WHEN others THEN
RETURN NULL;
END;
[/codesyntax]
The input parameter is iv_number that is a varchar2 type. We can pass any string to the function try_parse(). We use built-in function to_number to convert a string into a number. If any exception occurs, the function will return NULL in the exception section of the function block.
Calling PL/SQL Function
The PL/SQL function returns a value so you can use it on the right hand side of an assignment or in a SQL SELECT statement.
Let’s create an anonymous block to use the function try_parse() above.
[codesyntax lang="plsql"]
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
n_x number;
n_y number;
n_z number;
BEGIN
n_x := try_parse('574');
n_y := try_parse('12.21');
n_z := try_parse('abcd');
DBMS_OUTPUT.PUT_LINE(n_x);
DBMS_OUTPUT.PUT_LINE(n_y);
DBMS_OUTPUT.PUT_LINE(n_z);
END;
/
[/codesyntax]
Or you can also use the function try_parse in the SQL SELECT statement as follows:
[codesyntax lang="plsql"]
[codesyntax lang="plsql"]
SELECT try_parse('1234') FROM dual;
SELECT try_parse('Abc') FROM dual;
[/codesyntax]
Note that dual table a special one-row table that is used for selecting pseudocolumn like our example above.
In this tutorial, you’ve learned what PL/SQL function is and how to develop your own function. In addition, you’ve also learned how to call PL/SQL function in anonymous blocks or SQL SELECT statements.
PL/SQL Procedure
Introducing to PL/SQL Procedure
Like a PL/SQL function, a PL/SQL procedure is a named block that performs one or more actions. PL/SQL procedure allows you to wrap complex business logic and reuse it. The following illustrates the PL/SQL procedure’s syntax:
[codesyntax lang="plsql" title="PL/SQL Procedure Template" bookmarkname="plsqlprocedure"]
[codesyntax lang="plsql" title="PL/SQL Procedure Template" bookmarkname="plsqlprocedure"]
PROCEDURE [schema.]name[( parameter[, parameter...] ) ]
[AUTHID DEFINER | CURRENT_USER]
IS
[--declarations statements]
BEGIN
--executable statements
[ EXCEPTION
---exception handlers]
END [name];
[/codesyntax]
Let’s examine the PL/SQL syntax in details.
We can divide the PL/SQL procedure into two sections: header and body.
PL/SQL Procedure’s Header
The section before the keyword IS is called procedures’ header or procedure’s signature. The elements in the procedure’s header are listed as follows:
- Schema: The optional name of the schema that own this procedure. The default is the current user. If you specify a different user, the current user must have privileges to create a procedure in that schema.
- Name: The name of the procedure. The name of the procedure like a function should be always meaningful and starting by a verb.
- Parameters: The optional list of parameters. Please refer to the PL/SQL function for more information on parameter with different modes IN, OUT and IN OUT.
- AUTHID: The optional AUHTID determines whether the procedure will execute with the privileges of the owner (DEFINER) of the procedure or the current user (CURRENT_USER).
PL/SQL Procedure’s Body
Everything after the keyword IS is known as procedure’s body. The procedure’s body consists of declaration, execution and exception sections. The declaration and exception sections are optional. You must have at least one executable statement in the execution section. The execution section is where you put the code to implement a given business logic to perform one or more tasks.
In PL/SQL procedure you still have RETURN statement. However unlike the RETURN statement in function that returns a value to calling program, RETURN statement in procedure is used only to halt the execution of procedure and return control to the caller. RETURN statement in procedure does not take any expression or constant.
Example of PL/SQL Procedures
We’re going to develop a procedure in HR sample database of Oracle called adjust_salary(). We’ll update the salary information of employees in the table employees by using SQL UPDATE statement. Here is the PL/SQL procedure adjust_salary() code sample:
[codesyntax lang="plsql" title="PL/SQL Adjust Salary Procedure Code" bookmarkname="adjustsalaryprocedure"]
[codesyntax lang="plsql" title="PL/SQL Adjust Salary Procedure Code" bookmarkname="adjustsalaryprocedure"]
CREATE OR REPLACE PROCEDURE adjust_salary(
in_employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE,
in_percent IN NUMBER
) IS
BEGIN
-- update employee's salary
UPDATE employees
SET salary = salary + salary * in_percent / 100
WHERE employee_id = in_employee_id;
END;
[/codesyntax]
There are two parameters of the procedure IN_EMPLOYEE_ID and IN_PERCENT. This procedure will update salary information by a given percentage (IN_PERCENT) for a given employee specified by IN_EMPLOYEE_ID. In the procedure’s body, we use SQL UPDATE statement to update salary information. Let’s take a look how to call this procedure.
Calling PL/SQL Procedures
A procedure can call other procedures. A procedure without parameters can be called directly by using keyword EXEC or EXECUTE followed by procedure’s name as below:
[codesyntax lang="plsql" title="Execute PL/SQL Procedure" bookmarkname="Execute PL/SQL Procedure"]
EXEC procedure_name();
EXEC procedure_name;
[/codesyntax]
Procedure with parameters can be called by using keyword EXEC or EXECUTE followed by procedure’s name and parameter list in the order corresponding to the parameters list in procedure’s signature.
Procedure with parameters can be called by using keyword EXEC or EXECUTE followed by procedure’s name and parameter list in the order corresponding to the parameters list in procedure’s signature.
[codesyntax lang="plsql" title="Execute PL/SQL Procedure" bookmarkname="Execute PL/SQL Procedure"]
EXEC procedure_name(param1,param2…paramN);
[/codesyntax]
Now we can call our procedure as follows:
[codesyntax lang="plsql" title="Test PL/SQL Procedure" bookmarkname="Test PL/SQL Procedure"]
-- before adjustment
SELECT salary FROM employees WHERE employee_id = 200;
-- call procedure
exec adjust_salary(200,5);
-- after adjustment
SELECT salary FROM employees WHERE employee_id = 200;
[/codesyntax]
PL/SQL Nested Block
Summary: In this tutorial, you will learn how to work with PL/SQL nested block that is a PL/SQL block embedded inside another PL/SQL block.
Introducing PL/SQL Nested Block
To nest a block means to embed one ore more PL/SQL block inside another PL/SQL block to allow you have a better control over program’s execution and exception handling. Let’s take a look at an example:
[codesyntax lang="plsql" title="PL/SQL Nested Block Example" bookmarkname="PL/SQL Nested Block Example" highlight_lines="3,6"]
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
n_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := &emp_id1;
BEGIN
DECLARE
n_emp_id employees.employee_id%TYPE := &emp_id2;
v_name employees.first_name%TYPE;
BEGIN
SELECT first_name
INTO v_name
FROM employees
WHERE employee_id = n_emp_id;
DBMS_OUTPUT.PUT_LINE('First name of employee ' || n_emp_id ||
' is ' || v_name);
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('Employee ' || n_emp_id || ' not found');
END;
END;
/
[/codesyntax]
We have one PL/SQL block that is nested inside another PL/SQL block in the above example. The outer PL/SQL block is called parent block and the inner PL/SQL block is known as child block.
PL/SQL Nested Block
If you take a look carefully, you will see that we have two variables with the same name n_emp_id in the declaration section of both parent and child blocks. This is allowed in this scenario. So the question here is the which variable does the SQL SELECT statement accepts? If you execute the code in SQL*PLUS you will see that the SQL SELECT statement will accept the variable in the child block. Why? Because PL/SQL gives the first preference to the variable inside its own block. If the variable is not found, PL/SQL will search for the variable in the parent block and use it. If no variable, PL/SQL will issue an error. In this case, the variable v_emp_id in the child block overrides the variable in the parent block.
PL/SQL Block Label
So what if you want to refer to the variable in the parent block inside the child block in the above example? PL/SQL provides you a feature called block label so you can qualify all references to variables inside that block via a label. To label a block you just specify the label name before the declaration section as below:
[codesyntax lang="plsql" title="PL/SQL Block Label"]
<<block_label>>
DECLARE
...
BEGIN
...
END;
[/codesyntax]
You can refer to variable inside the block by using dot notation as below:
[codesyntax lang="plsql" title="PL/SQL Block Label" bookmarkname="PL/SQL Block Label"]
block_label.variable_name;
[/codesyntax]
Here is an example:
[codesyntax lang="plsql" title="PL/SQL Block Label Example" bookmarkname="PL/SQL Block Label Example" highlight_lines="2,6"]
SET SERVEROUTPUT ON SIZE 1000000;
<<label>>
DECLARE
v_name varchar2(25) := 'Maria';
BEGIN
DBMS_OUTPUT.PUT_LINE(label.v_name);
END;
/
[/codesyntax]
We can rewrite the above example as below:
[codesyntax lang="plsql" title="PL/SQL Block Label Example" bookmarkname="PL/SQL Block Label Example"]
SET SERVEROUTPUT ON SIZE 1000000;
<<parent>>
DECLARE
n_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := &emp_id1;
BEGIN
<<child>>
DECLARE
n_emp_id employees.employee_id%TYPE := &emp_id2;
v_name employees.first_name%TYPE;
BEGIN
SELECT first_name
INTO v_name
FROM employees
WHERE employee_id = parent.n_emp_id;
DBMS_OUTPUT.PUT_LINE('First name of employee ' || parent.n_emp_id ||
' is ' || child.v_name);
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('Employee ' || parent.n_emp_id || ' not found');
END;
END;
/
[/codesyntax]
There are several advantages of using PL/SQL block label:
- Improve the readability of the code.
- Gain better control of code execution because a block label can be a target for EXIT and CONTINUE statements.
- Allow you to qualify references to variables in parent block that has the same name with variable in child block by using dot notation.
In this tutorial, you’ve learned how to work with PL/SQL nested block and how to use block label to qualify references to variables from parent block that has the same name with variable in the current nested or child block.
PL/SQL IF Statement
Introduction to PL/SQL IF Statement
The PL/SQL IF statement allows you to execute a sequence of statements conditionally. The IF statements evaluate a condition. The condition can be anything that evaluates to a logical true or false such as comparison expression or combination of multiple comparison expressions. You can compare two variables of the same type or different types but they are convertible to each other. You can compare two literals. In addition, a Boolean variable can be used as a condition.
The PL/SQL IF statement has three forms: IF-THEN, IF-THEN-ELSE and IF-THEN-ELSIF.
PL/SQL IF-THEN Statement
The following illustrates the IF-THEN statement:
[codesyntax lang="plsql" title="PL/SQL IF-THEN Statement" bookmarkname="PL/SQL IF-THEN Statement"]
IF condition THEN
sequence_of_statements;
END IF;
[/codesyntax]
This is the simplest form of the IF statement. If the condition evaluates to true, the sequence of statements will execute. If the condition is false or NULL, the IF statement does nothing. Note that END IF is used to close the IF statement, not ENDIF.
The following code snippet demonstrates the PL/SQL IF statement. It updates employee’s salary to mid-range if employee’s salary is lower than higher than the mid-range. Otherwise it does nothing.
[codesyntax lang="plsql" title="PL/SQL IF Statement Example" bookmarkname="PL/SQL IF Statement Example" highlight_lines="29,33"]
DECLARE
n_min_salary NUMBER(6,0);
n_max_salary NUMBER(6,0);
n_mid_salary NUMBER(6,2);
n_salary EMPLOYEES.SALARY%TYPE;
n_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := 200;
BEGIN
-- get salary range of the employee
-- based on job
SELECT min_salary,
max_salary
INTO n_min_salary,
n_max_salary
FROM JOBS
WHERE JOB_ID = (SELECT JOB_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = n_emp_id);
-- calculate mid-range
n_mid_salary := (n_min_salary + n_max_salary) / 2;
-- get salary of the given employee
SELECT salary
INTO n_salary
FROM employees
WHERE employee_id = n_emp_id;
-- update employee's salary if it is lower than
-- the mid range
IF n_salary < n_mid_salary THEN
UPDATE employees
SET salary = n_mid_salary
WHERE employee_id = n_emp_id;
END IF;
END;
[/codesyntax]
PL/SQL IF-THEN-ELSE Statement
This is the second form of the the IF statement. The ELSE keyword is added with the alternative sequence of statements. Below is the syntax of the IF-ELSE statement.
[codesyntax lang="plsql" title="PL/SQL IF-THEN-ELSE Statement" bookmarkname="PL/SQL IF-THEN-ELSE Statement"]
IF condition THEN
sequence_of_if_statements;
ELSE
sequence_of_else_statements;
END IF;
[/codesyntax]
If the condition is NULL or false, the sequence of else statements will execute.
Suppose you want to increase salary for employee to mid-range if the current salary is lower than the mid-range of the job otherwise increase 5%. In this case you can change the code example above by using PL/SQL IF-THEN-ELSE statement as follows:
[codesyntax lang="plsql" title="PL/SQL IF-THEN-ELSE Statement Example" bookmarkname="PL/SQL IF-THEN-ELSE Statement Example" highlight_lines="3,7,11"]
-- update employee's salary if it is lower than
-- the mid range, otherwise increase 5%
IF n_salary < n_mid_salary THEN
UPDATE employees
SET salary = n_mid_salary
WHERE employee_id = n_emp_id;
ELSE
UPDATE employees
SET salary = salary + salary * 5 /100
WHERE employee_id = n_emp_id;
END IF;
[/codesyntax]
PL/SQL IF-THEN-ELSIF Statement
PL/SQL supports IF-THEN-ELSIF statement to allow you to execute a sequence of statements based on multiple conditions. The syntax of PL/SQL IF-THEN-ELSIF is as follows:
[codesyntax lang="plsql"]
IF condition1 THEN
sequence_of_statements1
ELSIF condition2 THEN
sequence_of_statements2
ELSE
sequence_of_statements3
END IF;
[/codesyntax]
Note that an IF statement can have any number of ELSIF clauses. IF the first condition is false or NULL, the ELSIF clause checks second condition and so on. If all conditions are NULL or false, the sequence of statements in the ELSE clause will execute. Note that the final ELSE clause is optional so if can omit it. If any condition from top to bottom is true, the corresponding sequence of statements will execute.
The following example illustrates the PL/SQL IF-THEN-ELSIF statement to print out a message if employee salary is higher than mid-range, lower than mid-range or equal to mid-range.
[codesyntax lang="plsql" title="PL/SQL IF-THEN-ELSIF Statement Example" bookmarkname="PL/SQL IF-THEN-ELSIF Statement Example" highlight_lines="29,33,38,42"]
DECLARE
n_min_salary NUMBER(6,0);
n_max_salary NUMBER(6,0);
n_mid_salary NUMBER(6,2);
n_salary EMPLOYEES.SALARY%TYPE;
n_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := 200;
BEGIN
-- get salary range of the employee
-- based on job
SELECT min_salary,
max_salary
INTO n_min_salary,
n_max_salary
FROM JOBS
WHERE JOB_ID = (SELECT JOB_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = n_emp_id);
-- calculate mid-range
n_mid_salary := (n_min_salary + n_max_salary) / 2;
-- get salary of the given employee
SELECT salary
INTO n_salary
FROM employees
WHERE employee_id = n_emp_id;
-- update employee's salary if it is lower than
-- the mid range, otherwise increase 5%
IF n_salary > n_mid_salary THEN
DBMS_OUTPUT.PUT_LINE('Employee ' || TO_CHAR(n_emp_id) ||
' has salary $' || TO_CHAR(n_salary) ||
' higher than mid-range $' || TO_CHAR(n_mid_salary));
ELSIF n_salary < n_mid_salary THEN
DBMS_OUTPUT.PUT_LINE('Employee ' || TO_CHAR(n_emp_id) ||
' has salary $' || TO_CHAR(n_salary) ||
' lower than mid-range $' || TO_CHAR(n_mid_salary));
ELSE
DBMS_OUTPUT.PUT_LINE('Employee ' || TO_CHAR(n_emp_id) ||
' has salary $' || TO_CHAR(n_salary) ||
' equal to mid-range $' || TO_CHAR(n_mid_salary));
END IF;
END;
[/codesyntax]
In this tutorial, you’ve learned how to use various forms of the PL/SQL IF statement including IF-THEN, IF-THEN-ELSE and IF-THEN-ELSIF statements.
PL/SQL CASE Statement
Summary: In this tutorial, you will learn how to use PL/SQL CASE statement to execute a sequence of statements based on a selector.
Introduction to PL/SQL CASE Statement
The PL/SQL CASE statement allows you to execute a sequence of statements based on a selector. A selector can be anything such as variable, function, or expression that the CASE statement evaluates to a Boolean value. You can use almost any PL/SQL data types as a selector except BLOB, BFILE and composite types. Unlike the PL/SQL IF statement, PL/SQL CASE statement uses a selector instead of combination of multiple Boolean expressions. The following illustrates the PL/SQL CASE statement syntax:
[codesyntax lang="plsql" title="PL/SQL CASE Statement Syntax" bookmarkname="PL/SQL CASE Statement Syntax"]
[<<label_name>>]
CASE [TRUE | selector]
WHEN expression1 THEN
sequence_of_statements1;
WHEN expression2 THEN
sequence_of_statements2;
...
WHEN expressionN THEN
sequence_of_statementsN;
[ELSE sequence_of_statementsN+1;]
END CASE [label_name];
[/codesyntax]
Followed by the keyword CASE is a selector. The PL/SQL CASE statement evaluates the selector only once to decide which sequence of statements to execute. Followed by the selector is any number of the WHEN clause. If the selector value is equal to expression in the WHEN clause, the corresponding sequence of statement after the THEN keyword will be executed. If the selector’s value is not one of the choices covered by WHEN
clause, the sequence of statements in the ELSE clause is executed. The ELSE clause is optional so if you omit the ESLE clause, PL/SQL will add the following implicit ELSE clause:
[codesyntax lang="plsql"]
ELSE RAISE CASE_NOT_FOUND;
[/codesyntax]
If you use implicit ELSE clause in the PL/SQL CASE statement, exception CASE_NOT_FOUND is raised and can be handled in the exception handling part of the PL/SQL block as usual.
The keywords END CASE are used to terminate the CASE statement.
Example of Using PL/SQL CASE Statement
The following code snippet demonstrates the PL/SQL CASE statement. We’ll use table employees in the sample data HR in Oracle for demonstration.
[codesyntax lang="plsql" title="PL/SQL CASE Statement Example" bookmarkname="PL/SQL CASE Statement Example" highlight_lines="14,15,17,19,21,23"]
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
n_pct employees.commission_pct%TYPE;
v_eval varchar2(10);
n_emp_id employees.employee_id%TYPE := 145;
BEGIN
-- get commission percentage
SELECT commission_pct
INTO n_pct
FROM employees
WHERE employee_id = n_emp_id;
-- evalutate commission percentage
CASE n_pct
WHEN 0 THEN
v_eval := 'N/A';
WHEN 0.1 THEN
v_eval := 'Low';
WHEN 0.4 THEN
v_eval := 'High';
ELSE
v_eval := 'Fair';
END CASE;
-- print commission evaluation
DBMS_OUTPUT.PUT_LINE('Employee ' || n_emp_id ||
' commission ' || TO_CHAR(n_pct) ||
' which is ' || v_eval);
END;
/
[/codesyntax]
PL/SQL Searched CASE Statement
PL/SQL provides a special CASE statement called searched CASE statement. The syntax of the PL/SQL searched CASE statement is as follows:
[codesyntax lang="plsql" title="PL/SQL Searched CASE Statement Syntax" bookmarkname="PL/SQL Searched CASE Statement Syntax"]
[<<label_name>>]
CASE
WHEN search_condition_1 THEN sequence_of_statements_1;
WHEN search_condition_2 THEN sequence_of_statements_2;
...
WHEN search_condition_N THEN sequence_of_statements_N;
[ELSE sequence_of_statements_N+1;]
END CASE [label_name];
[/codesyntax]
The searched CASE statement has no selector. Each WHEN clause in the searched CASE statement contains a search condition that returns a Boolean value. The search condition is evaluated sequentially from top to bottom. If a search condition return TRUE, the sequence of statements in the corresponding WHERE clause is executed and control passes to the next statement therefore the subsequent search conditions are ignored. If no search condition evaluates to TRUE, the sequence of statements in the ELSE clause will be executed.
Here is an example of using PL/SQL searched CASE statement:
[codesyntax lang="plsql" title="PL/SQL Searched CASSE Statement Example" bookmarkname="PL/SQL Searched CASSE Statement Example" highlight_lines="12,13,17,19"]
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
n_salary EMPLOYEES.SALARY%TYPE;
n_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := 200;
v_msg VARCHAR(20);
BEGIN
SELECT salary
INTO n_salary
FROM employees
WHERE employee_id = n_emp_id;
CASE
WHEN n_salary < 2000 THEN
v_msg := 'Low';
WHEN n_salary >= 2000 and n_salary <=3000 THEN
v_msg := 'Fair';
WHEN n_salary >= 3000 THEN
v_msg := 'High';
END CASE;
DBMS_OUTPUT.PUT_LINE(v_msg);
END;
/
[/codesyntax]
You’ve learned how to use PL/SQL CASE statement to execute a sequence of statements based on a selector. In addition, you learned how to use PL/SQL searched CASE statement.
PL/SQL LOOP Statement
Summary: In this tutorial you will learn PL/SQL LOOP statement to execute a sequence of statements repeatedly.
Introduction to PL/SQL LOOP Statement
PL/SQL LOOP is an iterative control structure that allows you to execute a sequence of statements repeatedly. The simplest of LOOP consists of the LOOP keyword, the sequence of statements and the END LOOP keywords, as shown below:
[codesyntax lang="plsql" title="PL/SQL LOOP Statement" bookmarkname="PL/SQL LOOP Statement"]
LOOP
sequence_of_statements;
END LOOP;
[/codesyntax]
Note that there must be at least one executable statement between LOOP and END LOOP keywords. The sequence of statements is executed repeatedly until it reaches a loop exits. PL/SQL provides you EXIT and EXIT-WHEN statements to allow you to terminate a loop.
- The EXIT forces the loop halt execution unconditionally and passes control to the next statement after keyword END LOOP.
- The EXIT-WHEN statement allows the loop complete conditionally. When the EXIT-WHEN statement is reached, the condition in the WHEN clause is checked. If the condition is true, the loop is terminated and pass control to the next statement after keyword END LOOP. If condition is false, the loop will continue repeatedly until the condition is evaluated to true. Therefore if you don’t want to have a infinite loop you must change variable’s value inside loop to make condition true.
The following illustrates PL/SQL LOOP with EXIT and EXIT-WHEN statements:
[codesyntax lang="plsql" title="PL/SQL LOOP with EXIT Statement" bookmarkname="PL/SQL LOOP with EXIT Statement"]
LOOP
...
EXIT;
END LOOP;
[/codesyntax]
[codesyntax lang="plsql" title="PL/SQL LOOP with EXIT-WHEN Statement" bookmarkname="PL/SQL LOOP with EXIT-WHEN Statement"]
LOOP
...
EXIT WHEN condition;
END LOOP;
[/codesyntax]
Examples of PL/SQL LOOP Statement
Example of PL/SQL LOOP with EXIT Statement
In this example, we declare a counter. Inside the loop we add 1 to the counter and print it out. If the counter is 5, we use EXIT statement to terminate the loop. Below is the code example of PL/SQL LOOP statement with EXIT:
[codesyntax lang="plsql" title="PL/SQL LOOP EXIT Example" bookmarkname="PL/SQL LOOP EXIT Example" highlight_lines="4,8,10"]
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE n_counter NUMBER := 0;
BEGIN
LOOP
n_counter := n_counter + 1;
DBMS_OUTPUT.PUT_LINE(n_counter);
IF n_counter = 5 THEN
EXIT;
END IF;
END LOOP;
END;
/
[/codesyntax]
Example of PL/SQL LOOP with EXIT-WHEN Statement
We’ll use the same counter example above. However instead of using the IF-THEN and EXIT statements, we use EXIT-WHEN to terminate the loop. The code example is as follows:
[codesyntax lang="plsql" title="PL/SQL LOOP EXT-WHEN Example" bookmarkname="PL/SQL LOOP EXT-WHEN Example" highlight_lines="4,7,8"]
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE n_counter NUMBER := 0;
BEGIN
LOOP
n_counter := n_counter + 1;
DBMS_OUTPUT.PUT_LINE(n_counter);
EXIT WHEN n_counter = 5;
END LOOP;
END;
/
[/codesyntax]
As you see in two examples above, the EXIT and EXIT-WHEN can be used interchangeably. The EXIT goes together with IF-THEN statement is equivalent to the EXIT-WHEN statement.
Loop Label
A loop can have an optional label that is an undeclared identifier enclosed by double angle brackets <<label>>. The loop label appears at the beginning and can also appear at the end of the PL/SQL LOOP statement. A loop label is used to qualify the name of the loop counter variable hence increase the code readability. The following illustrates the syntax of the PL/SQL loop statement with label:
[codesyntax lang="plsql" title="PL/SQL Loop Label" bookmarkname="PL/SQL Loop Label" highlight_lines="1,4"]
<<label>>
LOOP
sequence_of_statements;
END LOOP label;
[/codesyntax]
Below is an example of using loop label n nested loop:
[codesyntax lang="plsql" title="PL/SQL Loop Label Example" bookmarkname="PL/SQL Loop Label Example" highlight_lines="6,10,16,17"]
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
n_i NUMBER := 0;
n_j NUMBER := 0;
BEGIN
<< outer_loop >>
LOOP
n_i := n_i + 1;
EXIT WHEN n_i = 2;
<< inner_loop >>
LOOP
n_j := n_j + 1;
EXIT WHEN n_j = 5;
DBMS_OUTPUT.PUT_LINE('Outer loop counter ' || n_i);
DBMS_OUTPUT.PUT_LINE('Inner loop counter ' || n_j);
END LOOP inner_loop;
END LOOP outer_loop;
END;
/
[/codesyntax]
PL/SQL Loop Label Example Output SQL*PLUS
In this tutorial, you’ve learned how to use the PL/SQL LOOP together with EXIT and EXIT-WHEN statements to construct an iterative statement in PL/SQL to execute a sequence of statements repeatedly.
PL/SQL WHILE Loop
Summary: In this tutorial, you will learn how PL/SQL WHILE loop to execute a sequence of statements when you don’t know how many time the iteration will execute in advance.
Introducing to PL/SQL WHILE Loop
In some programming contexts, you don’t know in advance how many times to execute a sequence of statements because the execution depends on a condition that is not fixed. In such cases, you should you PL/SQL WHILE loop statement. The following illustrates the PL/SQL WHILE LOOP syntax:
[codesyntax lang="plsql" title="PL/SQL WHILE Loop Syntax" bookmarkname="PL/SQL WHILE Loop Syntax"]
WHILE condition
LOOP
sequence_of_statements;
END LOOP;
[/codesyntax]
a condition is a Boolean variable or expression that evalues to a Boolean values of TRUE, FALSE or NULL. Each time before executing the sequence of statements, the condition is checked. If the condition is evaluated to TRUE, then the sequence of statements is executed. If it evaluates to FALSE or NULL, the the loop terminates and control passes to the next executable statement following the END LOOP keywords. It is important to note that the sequence of statements may not execute even a single time based on condition. In addition, inside the loop you have to change values of variables to make the condition is FALSE or NULL to terminate the loop or you will have a endless loop. The PL/SQL WHILE loop is effective when you don’t know how many times the loop will execute. If the loop runs for a specific number of times, you should use the PL/SQL FOR loop statement instead.
Example of using PL/SQL WHILE LOOP
In this example, we will calculate factorial of 10 by using PL/SQL WHILE LOOP statement. Here is the code sample of the example:
[codesyntax lang="plsql" title="PL/SQL WHILE Loop Example" bookmarkname="PL/SQL WHILE Loop Example" highlight_lines="8,9,12"]
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
n_counter NUMBER := 10;
n_factorial NUMBER := 1;
n_temp NUMBER;
BEGIN
n_temp := n_counter;
WHILE n_counter > 0
LOOP
n_factorial := n_factorial * n_counter;
n_counter := n_counter - 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('factorial of ' || n_temp ||
' is ' || n_factorial);
END;
/
[/codesyntax]
In this tutorial, you’ve learned how to use another iterative statement called PL/SQL WHILE loop to execute a sequence of statements based on a condition that is checked before each iteration of the loop.
PL/SQL FOR Loop
Summary: In this tutorial, you will learn how to use PL/SQL FOR loop to execute a sequence of statements a fixed number times.
Introducing to PL/SQL FOR Loop
PL/SQL FOR loop is an iterative statement that allows you to execute a sequence of statements a fixed number of times. Unlike the PL/SQL WHILE loop, the number of iterations of the PL/SQL FOR loop is known before the loop starts. The following illustrates the PL/SQL FOR loop statement syntax:
[codesyntax lang="plsql" title="PL/SQL FOR Lopp Syntax" bookmarkname="PL/SQL FOR Loop Syntax"]
FOR loop_counter IN [REVERSE] lower_bound .. higher_bound
LOOP
sequence_of_statements;
END LOOP;
[/codesyntax]
Let’s examine the PL/SQL FOR loop syntax in details:
- loop_counter. The PL/SQL automatically creates a local variable loop_counter with data type INTEGER implicitly in the FOR loop so you don’t have to declare it. The scope of the loop_counter variable is within the loop itself so you cannot reference it outside the loop. After each iteration, PL/SQL increases loop_counter by 1 and checks if the loop_counter is still in the range of lower_bound and higher_bound to execute the next iteration. If the loop_counter is not in the range, the loop terminates.
- The lower_bound..higher_bound is the range of integers that FOR loop iterates over. This range is known as iteration scheme. The range operator is specified by a double dot (..) between lower_bound and higher_bound. The FOR loop evaluates the range when the loop first entered and never re-evaluated. The lower_bound has to be less than or equal to the higher_bound. If the lower_bound is equal to the higher_bound, the sequence of statements is executed once. If the lower_bound is larger than the higher_bound, the sequence within the loop will never execute. The lower_bound and higher_bound can be literals, variables, or expressions that evaluate to numbers. Otherwise, PL/SQL raises VALUE_ERROR exception.
- By default, the loop iterates in the upward fashion from the lower_bound to the higher_bound. However, if you want to force the loop to iterates in a downward way from the higher_bound to the lower_bound, you can use the REVERSE keyword after the IN keyword.
- You must have at least one executable statement between LOOP and END LOOPkeywords.
Examples of PL/SQL FOR LOOP
In the first example, we print integers from 1 to 10 by using PL/SQL FOR loop as the code below:
[codesyntax lang="plsql" title="PL/SQL FOR Loop Example" bookmarkname="PL/SQL FOR Loop Example" highlight_lines="5,7"]
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
n_times NUMBER := 10;
BEGIN
FOR n_i IN 1..n_times LOOP
DBMS_OUTPUT.PUT_LINE(n_i);
END LOOP;
END;
/
[/codesyntax]
PL/SQL FOR Loop Example 1
In the second example, we use the RESERVE keyword to print integers but in descending order.
[codesyntax lang="plsql" title="PL/SQL FOR Loop Example 2" bookmarkname="PL/SQL FOR Loop Example 2" highlight_lines="5,7"]
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
n_times NUMBER := 10;
BEGIN
FOR n_i IN REVERSE 1..n_times LOOP
DBMS_OUTPUT.PUT_LINE(n_i);
END LOOP;
END;
/
[/codesyntax]
PL/SQL FOR Loop Example 2
In this tutorial, you’ve learned how to use PL/SQL FOR loop to iterates over a specified range of integer which is known as iteration scheme.
PL/SQL Exception
Summary: In this tutorial, you will learn how to handle PL/SQL exception appropriately. In addition, you’ll also learn how to define your own exception and raise it in your code.
Introducing to PL/SQL Exception
In PL/SQL, any kind of errors are treated as exceptions. An exception is defined as a special condition that change the program execution flow. The PL/SQL provides you a flexible and powerful way to handle such exceptions.
PL/SQL catches and handles exceptions by using exception handler architecture. Whenever an exception occurs, it is raised. The current PL/SQL block execution halts and control is passed to a separated section called exception section. In the exception section, you can check what kind of exception has been occurred and handle it appropriately. This exception handler architecture enables separating the business logic and exception handling code hence make the program easier to read and maintain.
PL/SQL Exception
There are two types of exceptions:
- System exception: The system exception is raised by PL/SQL runtime when it detect an error. For example NO_DATA_FOUND exception is raised if you select a non-existing record from database.
- Programmer-defined exception: The programmer exception is defined by you in a specific application. You can map exception names with specific Oracle errors using the EXCEPTION_INIT pragma. You can also assign a number and description to the exception using RAISE_APPLICATION_ERROR.
Defining PL/SQL Exception
An exception must be defined before it can be raised. Oracle provides thousands of predefined exceptions in the STANDARD package. To define an exception you use EXCEPTION keyword as below:
[codesyntax lang="plsql" title="PL/SQL define a new Exception" bookmarkname="PL/SQL define a new Exception"]
EXCEPTION_NAME EXCEPTION;
[/codesyntax]
To raise exception that you’ve defined you use the RAISE statement as follows:
[codesyntax lang="plsql" title="PL/SQL raise exception" bookmarkname="PL/SQL raise exception"]
RAISE EXCEPTION_NAME;
[/codesyntax]
In the exception handler section you use can handle the exception as usual. The following example illustrates the programmer-defined exceptions. We get the salary of an employee and check it with the job’s salary range. If the salary is below the range, we raise exception BELOW_SALARY_RANGE. If the salary is above the range, we raise exception ABOVE_SALARY_RANGE.
[codesyntax lang="plsql" title="PL/SQL Exception Example" bookmarkname="PL/SQL Exception Example" highlight_lines="4,5,24,26,33,36"]
SET SERVEROUTPUT ON SIZE 100000;
DECLARE
-- define exceptions
BELOW_SALARY_RANGE EXCEPTION;
ABOVE_SALARY_RANGE EXCEPTION;
-- salary variables
n_salary employees.salary%TYPE;
n_min_salary employees.salary%TYPE;
n_max_salary employees.salary%TYPE;
-- input employee id
n_emp_id employees.employee_id%TYPE := &emp_id;
BEGIN
SELECT salary,
min_salary,
max_salary
INTO n_salary,
n_min_salary,
n_max_salary
FROM employees
INNER JOIN jobs ON jobs.job_id = employees.job_id
WHERE employee_id = n_emp_id;
IF n_salary < n_min_salary THEN
RAISE BELOW_SALARY_RANGE;
ELSIF n_salary > n_max_salary THEN
RAISE ABOVE_SALARY_RANGE;
END IF;
dbms_output.put_line('Employee ' || n_emp_id ||
' has salary $' || n_salary );
EXCEPTION
WHEN BELOW_SALARY_RANGE THEN
dbms_output.put_line('Employee ' || n_emp_id ||
' has salary below the salary range');
WHEN ABOVE_SALARY_RANGE THEN
dbms_output.put_line('Employee ' || n_emp_id ||
' has salary above the salary range');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee ' || n_emp_id || ' not found');
END;
/
[/codesyntax]
In this tutorial, you’ve learned how to define your own PL/SQL exception, raise and handle it in exception handler section of PL/SQL block.
PL/SQL Record
Summary: In this tutorial, you will learn about the PL/SQL record that is a composite data structure to allow you to write and manage your code more efficiently.
What is a PL/SQL Record
A PL/SQL record is a composite data structure that is a group of related data stored in fields. Each field in the Pl/SQL record has its own name and data type.
Declaring a PL/SQL Record
PL/SQL provides three ways to declare a record: table-based record, cursor-based record and programmer-defined records.
Declaring Table-based Record
To declare a table-based record you use a table name with %ROWTYPE attribute. The fields of the PL/SQL record has the same name and data type corresponding to the column of the table. The following illustrates table-based record declaration:
[codesyntax lang="plsql" title="Table-baed PL/SQL Record Syntax" bookmarkname="Table-baed PL/SQL Record Syntax" highlight_lines="2"]
DECLARE
table_based_record table_name%ROWTYPE;
[/codesyntax]
After having the table-based record, you can use it in various way for example in SQL SELECT statement as follows:
[codesyntax lang="plsql" title="PL/SQL Record Example 1" bookmarkname="PL/SQL Record Example 1" highlight_lines="3"]
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
r_emp employees%ROWTYPE;
n_emp_id employees.employee_id%TYPE := 200;
BEGIN
SELECT *
INTO r_emp
FROM employees
WHERE employee_id = n_emp_id;
-- print out the employee's first name
DBMS_OUTPUT.PUT_LINE(r_emp.first_name);
END;
/
[/codesyntax]
In the above example, first we defined a record based on employees table in HR sample database. Then we used SQL SELECT statement to retrieve the employee information of employee id 200 into the employee record r_emp. Finally we print out the first name of the selected employee from the employee record r_emp.
Declaring Programmer-defined Record
In order to declare programmer-defined record, first you use have to define record type by using TYPE statement with the fields of record explicitly. The you can declare a record based on record type you’ve defined. The following illustrates the syntax of the defining programmer-defined record with TYPE statement:
[codesyntax lang="plsql" title="PL/SQL Programmer-defined Record Syntax" bookmarkname="PL/SQL Programmer-defined Record Syntax"]
TYPE type_name IS RECORD
(field1 data_type1 [NOT NULL] := [DEFAULT VALUE],
field2 data_type2 [NOT NULL] := [DEFAULT VALUE],
...
fieldn data_type3 [NOT NULL] := [DEFAULT VALUE]
);
[/codesyntax]
The data type of field can be anything of the following:
- Scalar type (VARCHAR2,NUMBER…).
- Anchor declaration %TYPE.
- %ROW type, in this case we have a nested record.
- SUBTYPE
- PL/SQL collection types.
- Cursor variable REF CURSOR.
Once you define record type, you can declare a record based on this record type as follows:
[codesyntax lang="plsql" title="PL/SQL Record Declaration" bookmarkname="PL/SQL Record Declaration"]
record_name type_name;
[/codesyntax]
The following is an example of using programmer-defined record:
[codesyntax lang="plsql" title="PL/SQL Record Example 2" bookmarkname="PL/SQL Record Example 2" highlight_lines="3,4,5,6,7,12,16"]
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
TYPE t_name IS RECORD(
first_name employees.first_name%TYPE,
last_name employees.last_name%TYPE
);
r_name t_name; -- name record
n_emp_id employees.employee_id%TYPE := 200;
BEGIN
SELECT first_name,
last_name
INTO r_name
FROM employees
WHERE employee_id = n_emp_id;
-- print out the employee's name
DBMS_OUTPUT.PUT_LINE(r_name.first_name || ',' || r_name.last_name );
END;
/
[/codesyntax]
Declaring Cursor-based Record
You can define a record based on a cursor. A cursor has to be defined first. And then you use %ROWTYPE with the cursor variable to declare a record. The fields of the record correspond to the columns in the cursor SELECT statement. Here is an example of declaring a record based on a cursor.
[codesyntax lang="plsql" title="PL/SQL Cursor-based Record" bookmarkname="PL/SQL Cursor-based Record" highlight_lines="3,8"]
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
CURSOR cur_emp IS
SELECT *
FROM employees
WHERE employee_id = 200;
emp_rec cur_emp%ROWTYPE;
BEGIN
NULL;
END;
/
[/codesyntax]
Working with PL/SQL Record
After having a PL/SQL record, you can work with a record as a whole or you can work with individual field of the PL/SQL record.
Working with PL/SQL record at record level
At record level, you can do the following:
- You can assign a PL/SQL record to another PL/SQL record. The pair of PL/SQL records must have the same number of fields and the data type of each field has to be convertible.
- You can assign a PL/SQL record to NULL value by assign a record to an uninitialized record.
- A PL/SQL record can be used as an argument of parameter list in a function
- You can return a PL/SQL record from a function
- To check if the record is NULL, you have to check each individual field of the record.
- To compare record, you have to compare each individual field of record.
Here is an example of working with PL/SQL record at record level:
[codesyntax lang="plsql"]
SET serveroutput ON SIZE 1000000;
DECLARE
TYPE t_name IS RECORD(
first_name employees.first_name%TYPE,
last_name employees.last_name%TYPE
);
r_name t_name;
r_name2 t_name;
r_name_null t_name;
n_emp_id employees.employee_id%TYPE := 200;
BEGIN
-- assign employee's infomation to record
SELECT first_name,
last_name
INTO r_name
FROM employees
WHERE employee_id = n_emp_id;
-- assign record to another record
r_name2 := r_name;
-- print out the employee's name
DBMS_OUTPUT.PUT_LINE(r_name2.first_name || ',' || r_name2.last_name);
-- assign record to NULL
r_name2 := r_name_null;
-- check NULL for each individual field
IF r_name2.first_name IS NULL AND
r_name2.last_name IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Record r_name2 is NULL');
END IF;
END;
/
[/codesyntax]
Working with PL/SQL Record at Field Level
As you see in the above example, we can reference to a field of a record by using dot notation as follows:
[codesyntax lang="plsql" title="Reference to field of PL/SQL record" bookmarkname="Reference to field of PL/SQL record"]
record_name.field
[/codesyntax]
If you reference to a record variable in different package or schema you need to explicitly specify those information as below:
[codesyntax lang="plsql" title="PL/SQL Record Field Level" bookmarkname="PL/SQL Record Field Level"]
[schema_name.][package_name.]record_name.field
[/codesyntax]
You can use assignment operator (:=) to change the value of field of a record that you reference to.
For the nested record you need to use extra dot notation.
Below is an example of using PL/SQL Record a field level:
[codesyntax lang="plsql" title="PL/SQL Nested Record Example" bookmarkname="PL/SQL Nested Record Example"]
DECLARE
TYPE t_address IS RECORD(
house_number VARCHAR2(6),
street VARCHAR2(50),
phone VARCHAR2(15),
region VARCHAR2(10),
postal_code VARCHAR2(10),
country VARCHAR2(25)
);
TYPE t_contact IS RECORD(
home t_address,
business t_address
);
r_contact t_contact;
BEGIN
r_contact.business.house_number := '500';
r_contact.business.street := 'Oracle Parkway';
r_contact.business.region := 'CA';
r_contact.business.postal_code := '94065';
r_contact.business.country := 'USA';
r_contact.business.phone := '+1.800.223.1711';
END;
[/codesyntax]
In this tutorial, you’ve learned how to use PL/SQL record to manipulate data more efficiently, make your code cleaner and easier to maintain.
PL/SQL Cursor
Summary: In this tutorial you will explore PL/SQL cursor feature. You will learn step-by-step how to use a cursor to loop through multiple rows.
Introducing to PL/SQL Cursor
When you work with Oracle database, you work with a complete set of rows that is known as result set returned by SQL SELECT statement. However applications in some cases cannot work effectively with entire result set therefore database server needs to provide a mechanism for these applications to work with one row or a subset of result set at a time. As the result, Oracle introduced Cursor concept to provides these extensions.
PL/SQL cursor is a pointer that points to the result set of the SQL query against database tables.
Working with PL/SQL Cursor
There are several steps you need to do when yo work with PL/SQL cursor as describe in the image below:
PL/SQL Cursor
Let’s examine each steps in details.
Declaring PL/SQL Cursor
To use PL/SQL cursor, you must first declare it in the declaration section of PL/SQL block or in a package as follows:
[codesyntax lang="plsql" title="PL/SQL Declaration Syntax" bookmarkname="PL/SQL Declaration Syntax"]
CURSOR cursor_name [ ( [ parameter_1 [, parameter_2 ...] ) ]
[ RETURN return_specification ]
IS sql_select_statements
[FOR UPDATE [OF [column_list]];
[/codesyntax]
- First you declare the name of cursor cursor_name after the keyword CURSOR. The name of cursor can have up to 30 characters in length and follows the rules of identifiers in PL/SQL. It is important to note that cursor’s name is not a variable so you cannot use it as a variable such as assign it to other cursor or use it in an expression.
- parameter1 , parameter2… are optional section in cursor declaration. These parameter allows you to pass arguments into the cursor.
- RETURN return_specification is an optional part
- Next you specify the valid SQL statement which returns a result set where the cursor points to.
- Finally you can indicate a list of columns you want to update after the FOR UPDATE OF. This part is optional so you can omit it in the CURSOR declaration.
Here is an example of declaring a cursor:
[codesyntax lang="plsql" title="PL/SQL Cursor Declaration Example" bookmarkname="PL/SQL Cursor Declaration Example"]
CURSOR cur_chief IS
SELECT first_name,
last_name,
department_name
FROM employees e
INNER JOIN departments d ON d.manager_id = e.employee_id;
[/codesyntax]
Opening a PL/SQL Cursor
After declaring a cursor you can use open it by following the below syntax:
[codesyntax lang="plsql" title="Opening PL/SQL Cursor Syntax" bookmarkname="Opening PL/SQL Cursor Syntax"]
OPEN cursor_name [ ( argument_1 [, argument_2 ...] ) ];
[/codesyntax]
You have to specify the cursor’s name cursor_name after the keyword OPEN. If the cursor was defined with a parameter list, you need to pass corresponding arguments to the cursor also. When you OPEN the cursor, PL/SQL executes the SQL SELECT statement and identifies the active result set. Note that the OPEN action does not actually retrieve records from database. It happens in the FETCH step. If the cursor was declared with FOR UPDATE clause, PL/SQL locks all the records in the result set.
We can open our cursor cur_chief above as follows:
[codesyntax lang="plsql" title="Open PL/SQL Cursor Example" bookmarkname="Open PL/SQL Cursor Example"]
OPEN cur_chief;
[/codesyntax]
Fetching Records from PL/SQL Cursor
Once cursor is open, you can fetch data from the cursor into a record that has structure corresponding to the cursor. You can also fetch data from cursor to a list of variables. The fetch action retrieve data and fill the record or variable. You then can manipulate this data in memory. You can fetch the data until there is no record found in active result set. The syntax of FETCH is as follows:
[codesyntax lang="plsql" title="PL/SQL Cursor FETCH Syntax" bookmarkname="PL/SQL Cursor FETCH Syntax"]
FETCH cursor_name INTO record or variables
[/codesyntax]
You can test the cursor’s attribute %FOUND or %NOTFOUND to check if the fetch against the cursor succeeded. There are more cursor’s attributes which will cover in the next section.
We can use PL/SQL LOOP statement together with the FETCH to loop through all records in active result set as follows:
[codesyntax lang="plsql" title="PL/SQL Cursor Fetch Example" bookmarkname="PL/SQL Cursor Fetch Example" highlight_lines="3,5"]
LOOP
-- fetch information from cursor into record
FETCH cur_chief INTO r_chief;
EXIT WHEN cur_chief%NOTFOUND;
-- print department - chief
DBMS_OUTPUT.PUT_LINE(r_chief.department_name || ' - ' ||
r_chief.first_name || ',' ||
r_chief.last_name);
END LOOP;
[/codesyntax]
Closing PL/SQL Cursor
You should always close the cursor when you’re done with it. Otherwise you will have memory leak in your program which is not good. The close cursor syntax is very simple as follows:
[codesyntax lang="php"]
CLOSE cursor_name;
[/codesyntax]
And here is an example of closing cursor:
[codesyntax lang="plsql" title="PL/SQL Cursor CLOSE example" bookmarkname="PL/SQL Cursor CLOSE example"]
CLOSE cur_chief;
[/codesyntax]
A Complete PL/SQL Cursor Example
We can have a complete example of cursor for printing chief of department and department name as follows:
[codesyntax lang="plsql" title="PL/SQL Cursor Example" bookmarkname="PL/SQL Cursor Example" highlight_lines="4,13,14,18,26"]
[codesyntax lang="plsql" title="PL/SQL Cursor Example" bookmarkname="PL/SQL Cursor Example" highlight_lines="4,13,14,18,26"]
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
-- declare a cursor
CURSOR cur_chief IS
SELECT first_name,
last_name,
department_name
FROM employees e
INNER JOIN departments d ON d.manager_id = e.employee_id;
r_chief cur_chief%ROWTYPE;
BEGIN
OPEN cur_chief;
LOOP
-- fetch information from cursor into record
FETCH cur_chief INTO r_chief;
EXIT WHEN cur_chief%NOTFOUND;
-- print department - chief
DBMS_OUTPUT.PUT_LINE(r_chief.department_name || ' - ' ||
r_chief.first_name || ',' ||
r_chief.last_name);
END LOOP;
-- close cursor cur_chief
CLOSE cur_chief;
END;
/
[/codesyntax]
PL/SQL Cursor Attributes
These are the main attributes of a PL/SQL cursor and their explanations.
Attribute
|
Explanation
|
cursor_name%FOUND
|
returns TRUE if record was fetched successfully by cursor cursor_name
|
cursor_name%NOTFOUND
|
return TRUE if record was not fetched successfully by cursor cursor_name
|
cursor_name%ROWCOUNT
|
returns the number of records fetched from the cursor cursor_name at the time we test %ROWCOUNT attribute
|
cursor_name%ISOPEN
|
returns TRUE if the cursor cursor_name is open
|
In this tutorial, you’ve learned how to use PL/SQL Cursor to loop through multiple rows with every steps that need to be done including DECLARE, OPEN, FETCH and CLOSE.
PL/SQL Package
Summary: In this tutorial you will learn how to create a simple PL/SQL package that is a group of related functions, procedures, types and etc.
Introducing to PL/SQL Package
PL/SQL package is a group of related stored functions, procedures, types, cursors and etc. PL/SQL package is like a library once written stored in the Oracle database and can be used by many applications. A package has two parts:
- A package specification is the public interface of your applications. The public here means the stored function, procedures, type … are accessible by other applications.
- A package body contains the code that implements the package specification.
PL/SQL Package
Creating PL/SQL Package Specification
The package specification is required when you create a new package. The package specification lists all the objects which are publicly accessible from other applications. The package specification also provides the information that developers need to know in order to use the interface. In short, package specification is the package’s API.
If the package specification does not contains any stored functions, procedures and no private code is needed, you don’t need to have a package body. These packages may contain only type definition and variables declaration. Those variables are known as package data. The scope of package data is global to applications. It is recommended that you should hide as much as package data as possible and use get and set functions to read and write that data. By doing this, you can prevent your package data changed unintentionally.
It is important to note that you must compile the package specification before package body.
Here is the syntax of creating PL/SQL package specification:
[codesyntax lang="plsql" title="PL/SQL Package Specification Declaration" bookmarkname="PL/SQL Package Specification Declaration"]
CREATE [OR REPLACE] PACKAGE package_name
[ AUTHID { CURRENT_USER | DEFINER } ]
{ IS | AS }
[definitions of public TYPES
,declarations of public variables, types, and objects
,declarations of exceptions
,pragmas
,declarations of cursors, procedures, and functions
,headers of procedures and functions]
END [package_name];
[/codesyntax]
The CREATE PACKAGE package_name is used to define a new package specification. If you want to rebuild existing package you can use REPLACE keyword instead of CREATE keyword. In the package specification you can define a new types, declare global variables, types, objects, exceptions, cursors, procedures and functions.
Below is an example of creating a new package specification called personnel. The personnel package contains two functions: get_fullname () and get_salary() based on employee’s ID.
[codesyntax lang="plsql" title="PL/SQL Packag Specification" bookmarkname="PL/SQL Packag Specification"]
CREATE OR REPLACE PACKAGE personnel AS
-- get employee's fullname
FUNCTION get_fullname(n_emp_id NUMBER)
RETURN VARCHAR2;
-- get employee's salary
FUNCTION get_salary(n_emp_id NUMBER)
RETURN NUMBER;
END personnel;
[/codesyntax]
Creating PL/SQL Package Body
PL/SQL package body contains all the code that implements stored functions, procedures and cursors listed in the package specification. The following illustrates the syntax of creating package body:
[codesyntax lang="plsql" title="PL/SQL Package Body Syntax" bookmarkname="PL/SQL Package Body Syntax"]
CREATE [OR REPLACE] PACKAGE BODY package_name
{ IS | AS }
[definitions of private TYPEs
,declarations of private variables, types, and objects
,full definitions of cursors
,full definitions of procedures and functions]
[BEGIN
sequence_of_statements
[EXCEPTION
exception_handlers ] ]
END [package_name];
[/codesyntax]
The syntax is similar to the package specification except the keyword BODY and the implemented code of package specification. The package body can contain an optional package initialization section appearing in at the end of package body. The package initialization sections starts with the BEGIN keyword and end with the EXCEPTION section or END keyword of the package. The package initialization section is executed when the applications references to the package element at the first time.
The following illustrate package body of the personnel package which package specification is listed above:
[codesyntax lang="plsql" title="PL/SQL Package Body" bookmarkname="PL/SQL Package Body"]
/*
Package personnel body
*/
CREATE OR REPLACE PACKAGE BODY personnel AS
-- get employee's fullname
FUNCTION get_fullname(n_emp_id NUMBER) RETURN VARCHAR2 IS
v_fullname VARCHAR2(46);
BEGIN
SELECT first_name || ',' || last_name
INTO v_fullname
FROM employees
WHERE employee_id = n_emp_id;
RETURN v_fullname;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN TOO_MANY_ROWS THEN
RETURN NULL;
END; -- end get_fullname
-- get salary
FUNCTION get_salary(n_emp_id NUMBER) RETURN NUMBER IS
n_salary NUMBER(8,2);
BEGIN
SELECT salary
INTO n_salary
FROM employees
WHERE employee_id = n_emp_id;
RETURN n_salary;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN TOO_MANY_ROWS THEN
RETURN NULL;
END;
END personnel;
[/codesyntax]
Referencing PL/SQL Package Elements
You reference to package elements by using dot notation:
[codesyntax lang="plsql" title="Reference to PL/SQL Package Element" bookmarkname="Reference to PL/SQL Package Element"]
package_name.package_element
[/codesyntax]
The following code illustrates how to use personnel package by calling its functions:
[codesyntax lang="plsql" title="PL/SQL Package Example" bookmarkname="PL/SQL Package Example"]
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
n_salary NUMBER(8,2);
v_name VARCHAR2(46);
n_emp_id NUMBER := &emp_id;
BEGIN
v_name := personnel.get_fullname(n_emp_id);
n_salary := personnel.get_salary(n_emp_id);
IF v_name IS NOT NULL AND
n_salary IS NOT NULL
THEN
dbms_output.put_line('Employee: ' || v_name);
dbms_output.put_line('Salary:' || n_salary);
END IF;
END;
[/codesyntax]
In this tutorial, you’ve learned how to create a complete PL/SQL package and how to use its functions.
Tidak ada komentar:
Posting Komentar