PL/SQL NULL statement is a statement that does nothing. It serves as a placeholder statement when you need a syntactical construct in your code but don’t want to perform any actual action.
The PL/SQL NULL statement has the following format:
NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
The NULL statement is a NULL keyword followed by a semicolon ( ;). The NULL statement does nothing except that it passes control to the next statement.
The NULL statement is useful to:
Improve code readability
Provide a target for a GOTO statement
Create placeholders for subprograms
Using PL/SQL NULL statement to improve code readability
The following code sends an email to employees whose job titles are Sales Representative.
IF job_title = 'Sales Representative' THEN
send_email;
END IF;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
What should the program do for employees whose job titles are not Sales Representative? You might assume that it should do nothing. Because this logic is not explicitly mentioned in the code, you may wonder if it misses something else.
To make it more clear, you can add a comment. For example:
-- Send email to only Sales Representative, -- for other employees, do nothing
IF job_title = 'Sales Representative' THEN
send_email;
END IF;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Or you can add an ELSE clause that consists of a NULL statement to clearly state that no action is needed for other employees.
IF job_title = 'Sales Representative' THEN
send_email;
ELSE
NULL;
END IF;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Similarly, you can use a NULL statement in the ELSE clause of a simple CASE statement as shown in the following example:
DECLARE
n_credit_status VARCHAR2( 50 );
BEGIN
n_credit_status := 'GOOD';
CASE n_credit_status
WHEN 'BLOCK' THEN
request_for_aproval;
WHEN 'WARNING' THEN
send_email_to_accountant;
ELSE
NULL;
END CASE;
END;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
In this example, if the credit status is not blocked or warning, the program does nothing.
Using PL/SQL NULL statement to provide a target for a GOTO statement
When using a GOTO statement, you need to specify a label followed by at least one executable statement.
The following example uses a GOTO statement to quickly move to the end of the program if no further processing is required:
DECLARE
b_status BOOLEAN
BEGIN
IF b_status THEN
GOTO end_of_program;
END IF;
-- further processing here-- ...
<<end_of_program>>
NULL;
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Note that an error will occur if you don’t have the NULL statement after the end_of_program label.
Creating placeholders for subprograms
The following example creates a procedure named request_for_approval that doesn’t have the code in the body yet. PL/SQL requires at least one executable statement in the body of the procedure in order to compile successfully, therefore, we add a NULL statement to the body as a placeholder. Later you can fill in the real code.
CREATE PROCEDURE request_for_aproval(
customer_id NUMBER
)
AS
BEGIN
NULL;
END;
The label_name is the name of a label that identifies the target statement. In the program, you surround the label name with double enclosing angle brackets as shown below:
PL/SQL GOTO Demo
Hello
and good Bye...
Code language: SQL (Structured Query Language) (sql)
The following explains the sequence of the block in detail:
First, the GOTO second_message statement is encountered, therefore, the control is passed to the statement after the second_message label.
Second, the GOTO first_message is encountered, so the control is transferred to the statement after the first_message label.
Third, the GOTO the_end is reached, the control is passed to the statement after the the_end label.
The picture below illustrates the sequence:
GOTO statement restrictions
The GOTO statement is subject to the following restrictions.
First, you cannot use a GOTO statement to transfer control into an IF, CASE or LOOP statement, the same for the sub-block.
The following example attempts to transfer control into an IF statement using a GOTO statement:
DECLARE
n_sales NUMBER;
n_tax NUMBER;
BEGIN
GOTO inside_if_statement;
IF n_sales > 0 THEN
<<inside_if_statement>>
n_tax := n_sales * 0.1;
END IF;
END;
Code language: SQL (Structured Query Language) (sql)
Oracle issued the following error:
PLS-00375: illegal GOTO statement; this GOTO cannot branch to label 'INSIDE_IF_STATEMENT'
Code language: SQL (Structured Query Language) (sql)
Second, you cannot use a GOTO statement to transfer control from one clause to another in the IF statement e.g., from IF clause to ELSIF or ELSE clause, or from one WHEN clause to another in the CASE statement.
The following example attempts to transfer control to a clause in the IF statement:
DECLARE
n_sales NUMBER;
n_commission NUMBER;
BEGIN
n_sales := 120000;
IF n_sales > 100000 THEN
n_commission := 0.2;
GOTO zero_commission;
elsif n_sales > 50000 THEN
n_commission := 0.15;
elsif n_sales > 20000 THEN
n_commission := 0.1;
ELSE
<<zero_commission>>
n_commission := 0;
END IF;
END;
Code language: SQL (Structured Query Language) (sql)
Oracle issued the following error.
PLS-00375: illegal GOTO statement; this GOTO cannot branch to label 'ZERO_COMMISSION'
Code language: SQL (Structured Query Language) (sql)
Third, you cannot use a GOTO statement to transfer control out of a subprogram or into an exception handler.
Fourth, you cannot use a GOTO statement to transfer control from an exception handler back into the current block.
The CASE statement chooses one sequence of statements to execute out of many possible sequences.
The CASE statement has two types: simple CASE statement and searched CASE statement. Both types of CASE statements support an optional ELSE clause.
Simple PL/SQL CASE statement
A simple CASE statement evaluates a single expression and compares the result with some values.
The simple CASE statement has the following structure:
CASE selector
WHEN selector_value_1 THEN
statements_1
WHEN selector_value_1 THEN
statement_2
...
ELSE
else_statements
END CASE;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Let’s examine the syntax of the simple CASE statement in detail:
1) selector
The selector is an expression that is evaluated once. The result of the selector is used to select one of the several alternatives e.g., selector_value_1 and selector_value_2.
2) WHEN selector_value THEN statements
The selector values i.e., selector_value_1, selector_value_2, etc., are evaluated sequentially. If the result of a selector value equals the result of the selector, then the associated sequence of statements executes and the CASE statement ends. In addition, the subsequent selector values are not evaluated.
3) ELSE else_statements
If no values in WHERE clauses match the result of the selector in the CASE clause, the sequence of statements in the ELSE clause executes.
Because the ELSE clause is optional, you can skip it. However, if you do so, PL/SQL will implicitly use the following:
In other words, PL/SQL raises a CASE_NOT_FOUND error if you don’t specify an ELSE clause and the result of the CASE expression does not match any value in the WHEN clauses.
Note that this behavior of the CASE statement is different from the IF THEN statement. When the IF THEN statement has no ELSE clause and the condition is not met, PL/SQL does nothing instead raising an error.
Simple CASE statement example
The following example compares single value (c_grade) with many possible values ‘A’, ‘B’,’C’,’D’, and ‘F’:
DECLARE
c_grade CHAR( 1 );
c_rank VARCHAR2( 20 );
BEGIN
c_grade := 'B';
CASE c_grade
WHEN 'A' THEN
c_rank := 'Excellent' ;
WHEN 'B' THEN
c_rank := 'Very Good' ;
WHEN 'C' THEN
c_rank := 'Good' ;
WHEN 'D' THEN
c_rank := 'Fair' ;
WHEN 'F' THEN
c_rank := 'Poor' ;
ELSE
c_rank := 'No such grade' ;
END CASE;
DBMS_OUTPUT.PUT_LINE( c_rank );
END;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Searched CASE statement
The searched CASE statement evaluates multiple Boolean expressions and executes the sequence of statements associated with the first condition that evaluates to TRUE.
The searched CASE statement has the following structure:
CASE
WHEN condition_1 THEN statements_1
WHEN condition_2 THEN statements_2
...
WHEN condition_n THEN statements_n
[ ELSE
else_statements ]
END CASE;]
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
The searched CASE statement follows the rules below:
The conditions in the WHEN clauses are evaluated in order, from top to bottom.
The sequence of statements associated with the WHEN clause whose condition evaluates to TRUE is executed. If more than one condition evaluates to TRUE, only the first one executes.
If no condition evaluates to TRUE, the else_statements in the ELSE clause executes. If you skip the ELSE clause and no expressions are TRUE, a CASE_NOT_FOUND exception is raised.
Searched CASE statement example
The following example illustrates how to use the searched CASE statement to calculate sales commission based on sales revenue.
DECLARE
n_sales NUMBER;
n_commission NUMBER;
BEGIN
n_sales := 150000;
CASE
WHEN n_sales > 200000 THEN
n_commission := 0.2;
WHEN n_sales >= 100000 AND n_sales < 200000 THEN
n_commission := 0.15;
WHEN n_sales >= 50000 AND n_sales < 100000 THEN
n_commission := 0.1;
WHEN n_sales > 30000 THEN
n_commission := 0.05;
ELSE
n_commission := 0;
END CASE;
DBMS_OUTPUT.PUT_LINE( 'Commission is ' || n_commission * 100 || '%'
);
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
In this example, the sales revenue was set to 150,000. The first expression evaluated to FALSE:
PL/SQL stops evaluating the subsequent condition once it finds the first condition that evaluates to TRUE. Therefore, in this example, PL/SQL will never evaluate the last two conditions in the CASE statement. The ELSE statement clause will also never execute.
Simple or searched CASE statement
As a rule of thumb, use a searched CASE statement when you want to execute a sequence of statements based on the results of multiple Boolean expressions and use a simple CASE statement when you want to execute a sequence of statements based on the result of a single expression.
PL/SQL CASE statement vs. CASE expression
PL/SQL also has CASE expression which is similar to the CASE statement.
A CASE expression evaluates a list of conditions and returns one of multiple possible result expressions.
The result of a CASE expression is a single value whereas the result of a CASE statement is the execution of a sequence of statements.
The IF statement allows you to either execute or skip a sequence of statements, depending on a condition. The IF statement has three forms:
IF THEN IF THEN ELSE IF THEN ELSIF
PL/SQL IF THEN statement
The following illustrates the structure of the IF THEN statement:
IF condition THEN
statements;
END IF;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
The condition is a Boolean expression that always evaluates to TRUE, FALSE, or NULL.
If the condition evaluates to TRUE, the statements after the THEN execute. Otherwise, the IF statement does nothing.
PL/SQL IF THEN statement example
In the following example, the statements between THEN and END IF execute because the sales revenue is greater than 100,000.
DECLARE n_sales NUMBER := 2000000;
BEGIN
IF n_sales > 100000 THEN
DBMS_OUTPUT.PUT_LINE( 'Sales revenue is greater than 100K ' );
END IF;
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Tip # 1: Avoid clumsy IF statement
Consider the following example:
DECLARE
b_profitable BOOLEAN;
n_sales NUMBER;
n_costs NUMBER;
BEGIN
b_profitable := false;
IF n_sales > n_costs THEN
b_profitable := true;
END IF;
END;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
In this example, the IF statement determines whether the sales revenue is higher than the cost and updates the b_profitable variable accordingly.
This IF statement called a clumsy IF statement because you can assign the result of a Boolean expression directly to a Boolean variable as follows:
A Boolean variable is always TRUE, FALSE, or NULL. Therefore the following comparison is unnecessary:
IF b_profitable = TRUE THEN
DBMS_OUTPUT.PUT_LINE( 'This sales deal is profitable' );
END IF;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Instead, use:
IF b_profitable THEN
DBMS_OUTPUT.PUT_LINE( 'This sales deal is profitable' );
END IF;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
PL/SQL IF THEN ELSE statement
The IF THEN ELSE statement has the following structure:
IF condition THEN
statements;
ELSE
else_statements;
END IF;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
If the condition evaluates to TRUE, then the statements between THEN and ELSE execute. In case the condition evaluates to FALSE or NULL, the else_statements between ELSE and END IF executes.
IF THEN ELSE statement example
The following example sets the sales commission to 10% if the sales revenue is greater than 200,000. Otherwise, the sales commission is set to 5%.
DECLARE
n_sales NUMBER := 300000;
n_commission NUMBER( 10, 2 ) := 0;
BEGIN
IF n_sales > 200000 THEN
n_commission := n_sales * 0.1;
ELSE
n_commission := n_sales * 0.05;
END IF;
END;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
PL/SQL IF THEN ELSIF statement
The following illustrates the structure of the IF THEN ELSIF statement:
IF condition_1 THEN
statements_1
ELSIF condition_2 THEN
statements_2
[ ELSIF condition_3 THEN
statements_3
]
...
[ ELSE
else_statements
]
END IF;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
In this structure, the condition between IF and THEN, which is the first condition, is always evaluated. Each other condition between ELSEIF and THEN is evaluated only if the preceding condition is FALSE. For example, the condition_2 is evaluated only if the condition_1 is false, the condition_3 is evaluated only if the condition_2 is false, and so on.
If a condition is true, other subsequent conditions are not evaluated. If no condition is true, the else_statements between the ELSE and ENDIF execute. In case you skip the ELSE clause and no condition is TRUE, then the IF THEN ELSIF does nothing
IF THEN ELSIF statement example
The following example uses the IF THEN ELSIF statement to set the sales commission based on the sales revenue.
DECLARE
n_sales NUMBER := 300000;
n_commission NUMBER( 10, 2 ) := 0;
BEGIN
IF n_sales > 200000 THEN
n_commission := n_sales * 0.1;
ELSIF n_sales <= 200000 AND n_sales > 100000 THEN
n_commission := n_sales * 0.05;
ELSIF n_sales <= 100000 AND n_sales > 50000 THEN
n_commission := n_sales * 0.03;
ELSE
n_commission := n_sales * 0.02;
END IF;
END;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Nested IF statement
You can nest an IF statement within another IF statement as shown below:
IF condition_1 THEN
IF condition_2 THEN
nested_if_statements;
END IF;
ELSE
else_statements;
END IF;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
However, if you have too many levels of nesting, the code will be hard to read and maintain, so you should avoid nesting the IF statements.
A PL/SQL procedure is a reusable unit that encapsulates the specific business logic of the application. Technically speaking, a PL/SQL procedure is a named block stored as a schema object in the Oracle Database.
The following illustrates the basic syntax of creating a procedure in PL/SQL:
CREATE [OR REPLACE ] PROCEDURE procedure_name (parameter_list)
IS
A procedure begins with a header that specifies its name and an optional parameter list.
Each parameter can be in either IN, OUT, or INOUT mode. The parameter mode specifies whether a parameter can be read from or written to.
IN
An IN parameter is read-only. You can reference an IN parameter inside a procedure, but you cannot change its value. Oracle uses IN as the default mode. It means that if you don’t specify the mode for a parameter explicitly, Oracle will use the IN mode.
OUT
An OUT parameter is writable. Typically, you set a returned value for the OUT parameter and return it to the calling program. Note that a procedure ignores the value that you supply for an OUT parameter.
INOUT
An INOUT parameter is both readable and writable. The procedure can be read and modified.
Note that OR REPLACE option allows you to overwrite the current procedure with the new code.
PL/SQL procedure body
Similar to an anonymous block, the procedure body has three parts. The executable part is mandatory whereas the declarative and exception-handling parts are optional. The executable part must contain at least one executable statement.
1) Declarative part
In this part, you can declare variables, constants, cursors, etc. Unlike an anonymous block, a declaration part of a procedure does not start with the DECLARE keyword.
2) Executable part
This part contains one or more statements that implement specific business logic. It might contain only a NULL statement.
3) Exception-handling part
This part contains the code that handles exceptions.
Creating a PL/SQL procedure example
The following procedure accepts a customer id and prints out the customer’s contact information including first name, last name, and email:
CREATE OR REPLACE PROCEDURE print_contact(
in_customer_id NUMBER
)
IS
r_contact contacts%ROWTYPE;
BEGIN
-- get contact based on customer id
SELECT *
INTO r_contact
FROM contacts
WHERE customer_id = p_customer_id;
-- print out contact's information
dbms_output.put_line( r_contact.first_name || ' ' ||
r_contact.last_name || '<' || r_contact.email ||'>' );
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line( SQLERRM );
END;
Code language: SQL (Structured Query Language) (sql)
To compile the procedure, you click the Run Statement button as shown in the following picture:
If the procedure is compiled successfully, you will find the new procedure under the Procedures node as shown below:
Executing a PL/SQL procedure
The following shows the syntax for executing a procedure:
Sometimes, you want to lock a set of rows before you can update them in your program. Oracle provides the FOR UPDATE clause of the SELECT statement in an updatable cursor to perform this kind of locking mechanism.
Here is the syntax for declaring an updatable cursor:
CURSOR cursor_name IS
SELECT select_clause
FROM from_clause
WHERE where_clause
FOR UPDATE;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
The new syntax here is the FOR UPDATE keywords.
Once you open the cursor, Oracle will lock all rows selected by the SELECT ... FOR UPDATE statement in the tables specified in the FROM clause. And these rows will remain locked until the cursor is closed or the transaction is completed with either COMMIT or ROLLBACK.
Note that Oracle locks all rows returned by the SELECT ... FOR UPDATE during the update, therefore, you should have a WHERE clause to select only necessary rows to be locked.
If you have a specific column that you want to update, you can list it in the FOR UPDATE clause as follows:
CURSOR cursor_name IS
SELECT select_clause
FROM from_clause
WHERE where_clause
FOR UPDATE OF column_name;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
In this case, Oracle only locks rows of the table that has the column name listed in the FOR UPDATE OF clause.
Note that if you use only FOR UPDATE clause and do not include one or more column after the OF keyword, Oracle will then lock all selected rows across all tables listed in the FROM clause.
Oracle Cursor FOR UPDATE example
Consider the following example.
DECLARE
-- customer cursor
CURSOR c_customers IS
SELECT
customer_id,
name,
credit_limit
FROM
customers
WHERE
credit_limit > 0
FOR UPDATE OF credit_limit;
-- local variables
l_order_count PLS_INTEGER := 0;
l_increment PLS_INTEGER := 0;
BEGIN
FOR r_customer IN c_customers
LOOP
-- get the number of orders of the customer
SELECT COUNT(*)
INTO l_order_count
FROM orders
WHERE customer_id = r_customer.customer_id;
--
IF l_order_count >= 5 THEN
l_increment := 5;
ELSIF l_order_count < 5 AND l_order_count >=2 THEN
l_increment := 2;
ELSIF l_increment = 1 THEN
l_increment := 1;
ELSE
l_increment := 0;
END IF;
IF l_increment > 0 THEN
-- update the credit limit
UPDATE
customers
SET
credit_limit = credit_limit * ( 1 + l_increment/ 100)
WHERE
customer_id = r_customer.customer_id;
-- show the customers whose credits are increased
dbms_output.put_line('Increase credit for customer '
|| r_customer.NAME || ' by '
|| l_increment || '%' );
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error code:' || SQLCODE);
dbms_output.put_line('Error message:' || sqlerrm);
RAISE;
END;
/
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
How it works.
First, declare an updatable cursor that updates credits of the customers whose credits are greater than zero.
Next, loop over the rows in the cursors.
Then, get the number of orders for each customer.
After that, assign the credit increment based on the order count.
A cursor is a pointer that points to a result of a query. PL/SQL has two types of cursors: implicit cursors and explicit cursors.
Implicit cursors
Whenever Oracle executes an SQL statement such as SELECT INTO, INSERT, UPDATE, and DELETE, it automatically creates an implicit cursor.
Oracle internally manages the whole execution cycle of implicit cursors and reveals only the cursor’s information and statuses such as SQL%ROWCOUNT, SQL%ISOPEN, SQL%FOUND, and SQL%NOTFOUND.
The implicit cursor is not elegant when the query returns zero or multiple rows which cause NO_DATA_FOUND or TOO_MANY_ROWS exception respectively.
Explicit cursors
An explicit cursor is a SELECT statement declared explicitly in the declaration section of the current block or a package specification.
For an explicit cursor, you have control over its execution cycle from OPEN, FETCH, and CLOSE.
Oracle defines an execution cycle that executes an SQL statement and associates a cursor with it.
The following illustration shows the execution cycle of an explicit cursor:
Let’s examine each step in detail.
Declare a cursor
Before using an explicit cursor, you must declare it in the declaration section of a block or package as follows:
CURSOR cursor_name IS query;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
In this syntax:
First, specify the name of the cursor after the CURSOR keyword.
Second, define a query to fetch data after the IS keyword.
Open a cursor
Before start fetching rows from the cursor, you must open it. To open a cursor, you use the following syntax:
OPEN cursor_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
In this syntax, the cursor_name is the name of the cursor declared in the declaration section.
When you open a cursor, Oracle parses the query, binds variables, and executes the associated SQL statement.
Oracle also determines an execution plan, associates host variables and cursor parameters with the placeholders in the SQL statement, determines the result set, and sets the cursor to the first row in the result set.
More about parameterized cursor in the next tutorial.
Fetch from a cursor
The FETCH statement places the contents of the current row into variables. The syntax of FETCH statement is as follows:
FETCH cursor_name INTO variable_list;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
To retrieve all rows in a result set, you need to fetch each row till the last one.
Closing a cursor
After fetching all rows, you need to close the cursor with the CLOSE statement:
CLOSE cursor_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Closing a cursor instructs Oracle to release allocated memory at an appropriate time.
If you declare a cursor in an anonymous block, procedure, or function, the cursor will automatically be closed when the execution of these objects ends.
However, you must explicitly close package-based cursors. Note that if you close a cursor that has not opened yet, Oracle will raise an INVALID_CURSOR exception.
Explicit Cursor Attributes
A cursor has four attributes which you can reference in the following format:
cursor_name%attributeCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
where cursor_name is the name of the explicit cursor.
1) %ISOPEN
This attribute is TRUE if the cursor is open or FALSE if it is not.
2) %FOUND
This attribute has four values:
NULL before the first fetch
TRUE if a record was fetched successfully
FALSE if no row is returned
INVALID_CURSOR if the cursor is not opened
3) %NOTFOUND
This attribute has four values:
NULL before the first fetch
FALSE if a record was fetched successfully
TRUE if no row is returned
INVALID_CURSOR if the cursor is not opened
3) %ROWCOUNT
The %ROWCOUNT attribute returns the number of rows fetched from the cursor. If the cursor is not opened, this attribute returns INVALID_CURSOR.
PL/SQL cursor example
We will use the orders and order_items tables from the sample database for the demonstration.
The following statement creates a view that returns the sales revenues by customers:
CREATE VIEW sales AS
SELECT customer_id,
SUM(unit_price * quantity) total,
ROUND(SUM(unit_price * quantity) * 0.05) credit
FROM order_items
INNER JOIN orders USING (order_id)
WHERE status = 'Shipped'
GROUP BY customer_id;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
The values of the credit column are 5% of the total sales revenues.
Suppose you need to develop an anonymous block that:
Reset the credit limits of all customers to zero.
Fetch customers sorted by sales in descending order and give them new credit limits from a budget of 1 million.
The following anonymous block illustrates the logic:
DECLARE
l_budget NUMBER := 1000000;
-- cursor
CURSOR c_sales IS
SELECT * FROM sales
ORDER BY total DESC;
-- record
r_sales c_sales%ROWTYPE;
BEGIN
-- reset credit limit of all customers
UPDATE customers SET credit_limit = 0;
OPEN c_sales;
LOOP
FETCH c_sales INTO r_sales;
EXIT WHEN c_sales%NOTFOUND;
-- update credit for the current customer
UPDATE
customers
SET
credit_limit =
CASE WHEN l_budget > r_sales.credit
THEN r_sales.credit
ELSE l_budget
END
WHERE
customer_id = r_sales.customer_id;
-- reduce the budget for credit limit
l_budget := l_budget - r_sales.credit;
DBMS_OUTPUT.PUT_LINE( 'Customer id: ' ||r_sales.customer_id ||
' Credit: ' || r_sales.credit || ' Remaining Budget: ' || l_budget );
-- check the budget
EXIT WHEN l_budget <= 0;
END LOOP;
CLOSE c_sales;
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
In the declaration section, we declare three variables.
The first one is l_budget whose initial value is 1,000,000.
The second variable is an explicit cursor variable named c_sales whose SELECT statement retrieves data from the sales view:
CURSOR c_sales IS
SELECT * FROM sales
ORDER BY total DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
The third variable is a cursor-based record named c_sales.
In the execution section, we perform the following:
First, reset the credit limits of all customers to zero using an UPDATE statement.
Second, open the c_sales cursor.
Third, fetch each row from the cursor. In each loop iteration, we updated the credit limit and reduced the budget. The loop terminates when there is no row to fetch or the budget is exhausted.
Finally, close the cursor.
The following query retrieves data from the customers table to verify the update:
SELECT customer_id,
name,
credit_limit
FROM customers
ORDER BY credit_limit DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Result:
As you can see clearly from the result, only the first few customers have credit limits. If you sum up all credit limits, the total should be 1 million as shown follows:
SELECT
SUM( credit_limit )
FROM
customers;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
PL/SQL SELECT INTO statement is the simplest and fastest way to fetch a single row from a table into variables. The following illustrates the syntax of the PL/SQL SELECT INTO statement:
SELECT
select_list
INTO
variable_list
FROM
table_name
WHERE
condition; Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
In this syntax, the number of columns in the variable_list must be the same as the number of variables (or the number of components of a record) in the select_list. In addition, their corresponding data type must be compatible.
Besides the WHERE clause, you can use other clauses in the SELECT statement such as INNER JOIN, GROUP BY, HAVING, and UNION.
If the SELECT statement returns more than one row, Oracle will raise the TOO_MANY_ROWS exception. If the SELECT statement does not return any row, Oracle will raise the NO_DATA_FOUND exception.
PL/SQL SELECT INTO examples
Let’s use the customers and contacts tables in the sample database for demonstration.
1) Selecting one column example
The following example uses a SELECT INTO statement to get the name of a customer based on the customer id, which is the primary key of the customers table.
DECLARE
l_customer_name customers.name%TYPE;
BEGIN
-- get name of the customer 100 and assign it to l_customer_name
SELECT name INTO l_customer_name
FROM customers
WHERE customer_id = 100;
-- show the customer name
dbms_output.put_line( l_customer_name );
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
In this example:
First, declare a variable l_customer_name whose data type anchors to the name columns of the customers table. This variable will hold the customer’s name.
Second, use the SELECT INTO statement to select a value from the name column and assign it to the l_customer_name variable.
Third, show the customer’s name using the dbms_output.put_line procedure.
Because the customers table has only one row with customer ID 100, the code block displays the customer name.
VerizonCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
If there were no such row, the code block would fail with an unhandled NO_DATA_FOUND exception.
2) Selecting a complete row example
The following example fetches the entire row from the customers table for a specific customer ID:
DECLARE
r_customer customers%ROWTYPE;
BEGIN
-- get the information of the customer 100
SELECT * INTO r_customer
FROM customers
WHERE customer_id = 100;
-- show the customer info
dbms_output.put_line( r_customer.name || ', website: ' || r_customer.website );
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
First, declare three variables l_customer_name, l_contact_first_name, l_contact_last_name to hold the customer and contact’s name.
Second, use the SELECT INTO statement to fetch the customer and contact names of the customer id 100 from the customers and contacts tables into the corresponding variables
If the number of columns and expressions in the SELECT clause is greater than the number of variables in the INTO clause, Oracle issues this error:
ORA-00947: not enough values The INTO list contains fewer variables than the SELECT list.Code language: PHP (php)
Oracle issues the following error if the number of columns and expressions in the SELECT clause is less than the number of variables in the INTO clause:
ORA-00913: too many values The INTO list contains more variables than the SELECT list.Code language: PHP (php)
If the number of variables and elements in the select list are the same, but their corresponding datatypes are not compatible, Oracle cannot implicitly convert from one type to the other. It will issue the following error:
ORA-06502: PL/SQL: numeric or value errorCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Now, you should know how to use the PL/SQL SELECT INTO statement to fetch a single row from a table into variables.
Typically, the CONTINUE statement is used within an IF THEN statement to exit the current loop iteration based on a specified condition as shown below:
IF condition THEN
CONTINUE;
END IF;Code language: SQL (Structured Query Language) (sql)
The CONTINUE can be used in all loop constructs including LOOP, FOR LOOP and WHILE LOOP.
PL/SQL CONTINUE statement example
The following is a simple example of using the CONTINUE statement to skip over loop body execution for odd numbers:
BEGIN
FOR n_index IN 1 .. 10
LOOP
-- skip odd numbers
IF MOD( n_index, 2 ) = 1 THEN
CONTINUE;
END IF;
DBMS_OUTPUT.PUT_LINE( n_index );
END LOOP;
END;Code language: SQL (Structured Query Language) (sql)
The CONTINUE WHEN statement exits the current loop iteration based on a condition and immediately continues to the next iteration of that loop.
The syntax of CONTINUE WHEN statement is:
CONTINUE WHEN condition;Code language: SQL (Structured Query Language) (sql)
The condition of the WHEN clause is evaluated each time the CONTINUE WHEN statement is reached. If the condition is TRUE, the current loop is skipped, and control is transferred to the next iteration of the loop. If the condition is not TRUE, either FALSE or NULL, the CONTINUE WHEN statement does nothing.
Essentially, the CONTINUE WHEN statement is the combination of an IF THEN statement and CONTINUE statement:
IF condition THEN
CONTINUE;
END IF;Code language: SQL (Structured Query Language) (sql)
Similar to the CONTINUE statement, you can use the CONTINUE WHEN statement in LOOP, FOR LOOP and WHILE LOOP.
PL/SQL CONTINUE statement example
The following example illustrates how to use the CONTINUE WHEN statement to skip over loop body execution for even numbers:
BEGIN
FOR n_index IN 1 .. 10
LOOP
-- skip even numbers
CONTINUE
WHEN MOD( n_index, 2 ) = 0;
DBMS_OUTPUT.PUT_LINE( n_index );
END LOOP;
END;Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to exit the current loop iteration and continue to the next one using the PL/SQL CONTINUE or CONTINUE WHEN statement.
PL/SQL WHILE loop is a control structure that repeatedly executes a code block as long as a specific condition remains true.
Here’s the syntax for the WHILE loop statement:
WHILE condition
LOOP
statements;
END LOOP;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
In this syntax, the condition is a boolean expression that evaluates to TRUE, FALSE or NULL.
The WHILE loop statement continues to execute the statements between the LOOP and ENDLOOP as long as the condition evaluates to TRUE.
PL/SQL evaluates the condition in the WHILE clause before each loop iteration. If the condition is TRUE, then the loop body executes. If the condition is FALSE or NULL, the loop terminates.
If the condition is FALSE before entering the loop, the WHILE loop does not execute at all. This behavior is different from the LOOP statement whose loop body always executes once.
To terminate the loop prematurely, you use an EXIT or EXIT WHEN statement.
PL/SQL WHILE loop examples
Let’s take some examples of using the WHILE loop statement to see how it works.
1) Simple PL/SQL WHILE loop example
The following example illustrates how to use the WHILE loop statement:
DECLARE
n_counter NUMBER := 1;
BEGIN
WHILE n_counter <= 5
LOOP
DBMS_OUTPUT.PUT_LINE( 'Counter : ' || n_counter );
n_counter := n_counter + 1;
END LOOP;
END;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Second, the condition in the WHILE clause was evaluated before each loop iteration.
Third, inside the loop body, the counter was increased by one in each loop iteration. After five iterations, the condition was FALSE that caused the loop to terminate.
2) WHILE loop example terminated by EXIT WHEN statement
The following example is the same as the one above except that it has an additional EXITWHEN statement.
DECLARE
n_counter NUMBER := 1;
BEGIN
WHILE n_counter <= 5
LOOP
DBMS_OUTPUT.PUT_LINE( 'Counter : ' || n_counter );
n_counter := n_counter + 1;
EXIT WHEN n_counter = 3;
END LOOP;
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
The condition of the EXIT WHEN clause evaluated to true when the counter is three. Therefore, the loop body was only executed two times before termination.
In this tutorial, you have learned how to use the PL/SQL WHILE loop statement to execute a sequence of statements as long as a specified condition is TRUE.
