Transcript Explicit Cursors

PL/SQL Block Structure

DECLARE - Optional
Variables, cursors, user-defined exceptions

BEGIN - Mandatory
SQL Statements
PL/SQL Statements

EXCEPTIONS - Optional
Actions to perform when errors occur

END; - Mandatory
Block Types
Anonymous
Procedure
Function
[DECLARE]
PROCEDURE name FUNCTION name
IS
RETURN datatype
IS
BEGIN
BEGIN
BEGIN
--statements
--statements
--statements
[EXCEPTION] [EXCEPTION]
[EXCEPTION]
END;
END;
END;
Handling Variables




Declare and initialize variables in the
declaration section.
Assign new values to variables in the
executable section.
Pass values into PL/SQL blocks through
parameters.
View results through output variables.
Variable Declaration



Two variables can have the same name,
provided they are in different block.
identifier [CONSTANT] datatype [NOT NULL]
[:= | DEFAULT expr];
%TYPE Attribute
–
Declare a variable as a database table, column or
previously declared variable.
Variable Declaration

%ROWTYPE
–
–
Declare a variable according to a collection of
columns in a database table
Example

–
emp_record emp%ROWTYPE;
Assigning value

emp_record.emp_name = ‘DOUGLAS’;
Syntax

Delimiters
–

Identifiers
–

Can contain up to 30 characters.
Literals
–

+, -, *, /, =, @, ||, <>, !=, --, /*, */, :=, ;
Must be enclosed in single quotation marks.
Comments
–
–
Single-line comments: -Multi-line comments: /* … */
Nested Blocks and Variable Scope



Statements can be nested wherever an
executable statement is allowed.
An exception section can contain nested blocks.
Scope
–
–
A block can look up to the enclosing block.
A block cannot look down to enclosed blocks.
Programming Guidelines

Code Convention
–
–
–
–
–
SQL Statements: Uppercase
PL/SQL Keywords: Uppercase
Datatypes: Uppercase
Identifiers and Parameters: Lowercase
Database Tables and Columns: Lowercase
Programming Guidelines

Name Convention
–
–
–
–
–
–
–
–
Variable: v_name
Constant: c_name
Cursor: name_cursor
Exception: e_name
Table: name_table
Record: name_record
Parameter: p_name
Global Variable: g_name
Programming Guidelines

Indenting
–
Indent each level of code
DECLARE
v_dept_no
v_location
BEGIN
SELECT
INTO
FROM
WHERE
END;
NUMBER(2);
VARCHAR2(13);
dept_no,
loc
v_dept_no
v_location
dept
d_name = ‘SALES’
SQL Statements in PL/SQL

SELECT
Can return on and only one row
SELECT select_list
INTO variable
FROM table
WHERE condition;
– The variable can also be a record
–
SQL Statements in PL/SQL

SELECT
–
Exceptions


–
NO_DATA_FOUND
TOO_MANY_ROWS
Example
SELECT emp_id, name
INTO v_emp_id, v_name
FROM emp
WHERE dept_name = ‘SALES’;
SQL Statements in PL/SQL

INSERT
INSERT INTO table (columns)
VALUES (columns);
– The order of the columns in the INSERT INTO
statement must be the same as in the VALUES
statement.
– Example
INSERT INTO emp (emp_no, emp_name)
VALUES (emp_no_seq.NEXTVAL, ‘DOUGLAS’);
SQL Statements in PL/SQL

UPDATE
UPDATE table
SET sal = sal + v_sal_increase
WHERE job = ‘ANALYST’;
– Can effect zero or more rows in a single statement.
SQL Statements in PL/SQL

DELETE
DELETE FROM table
WHERE column = value;
– Can effect zero or more rows in a single statement.
– Example
DELETE FROM emp
WHERE emp_name = ‘DOUGLAS’;
Control Structures

IF Statement
IF
Statements
ELSIF
Statements
ELSE
Statements
END IF;
Basic Loop
LOOP
Statements;
EXIT [WHEN condition]
END LOOP;
 Example
LOOP
v_counter := v_counter + 1;
EXIT WHEN v_counter > 10;
END LOOP;
FOR Loop
FOR counter in lower_bound .. upper_bound LOOP
Statements;
END LOOP;
 Example
FOR i IN 1 .. 10 LOOP
v_counter := v_counter + 1;
END LOOP;
WHILE Loop
WHILE condition LOOP
Statements;
END LOOP;
 Example
WHILE v_counter <= 10 LOOP
v_counter := v_counter + 1;
END LOOP;
PL/SQL Record
TYPE type_name IS RECORD
(field_declaration, field_declaration);
identifier type_name;
 Example
TYPE emp_record_type IS RECORD
(emp_name
VARCHAR2(10),
sal
NUMBER(7,2));
emp_record emp_record_type;
Cursors


Private work area
Implicit
–

Used by the Oracle Server to parse and execute
SQL statements.
Explicit
–
–
Declared and named by the programmer.
Declared in the DECLARE section of the block
structure.
Explicit Cursors

Controlling Cursors
–
Declare

–
Open

–
Load the current row into variables
Empty

–
Identify the active set
Fetch

–
Create a named SQL area
Test for existing rows
Close

Release the active set
Explicit Cursors

Declaring the Cursor
CURSOR cursor_name IS
select_statement;
–
–
–
Do not include the INTO clause in the cursor
declaration
ORDER BY clause can be used if necessary
Example
CURSOR emp_cursor IS
SELECT emp_no, emp_name
FROM emp;
Explicit Cursors

Opening the Cursor
OPEN cursor_name;
–
–
–
executes the query and identify the active set
No rows, no exception
Always test if cursor is open before opening.
IF NOT cursor_name%ISOPEN THEN
OPEN cursor_name
END IF;
Explicit Cursors

Fetching Data from the Cursor
–
FETCH cursor_name INTO variables;

–
A record can replace the variables.
Testing for rows
IF cursor_name%NOTFOUND THEN
Statements
END IF;
Explicit Cursors

Closing the Cursor
–
–
CLOSE cursor_name;
Test for a closed cursor
IF cursor_name%ISOPEN THEN
CLOSE cursor_name;
END IF;
Explicit Cursors

Attributes
–
%ISOPEN

–
%NOTFOUND

–
Evaluates to TRUE if the most recent fetch does not return
a row.
%FOUND

–
Evaluates to TRUE if the cursor is open.
Evaluates to TRUE if the most recent retch does return a
row.
%ROWCOUND

Evaluates to the total number of records returned so far.
Explicit Cursors


Cursor FOR loops
FOR record_name IN cursor_name LOOP
Statements
END LOOP;
Example
FOR emp_record IN emp_cursor LOOP
Statements
END LOOP;
– Implicit open, fetch and close occur.
Explicit Cursors

Parameters
CURSOR cursor_name (parameter_name
datatype) IS
select_statement;
–
Example
CURSOR emp_cursor (p_dept_no NUMBER) IS
select_statement;
OPEN emp_cursor(10);
FOR emp_record IN emp_cursor(10) LOOP
Explicit Cursors

FOR UPDATE Clause
SELECT …
FROM
FOR UPDATE [OF column_reference] [NOWAIT]





Explicit locking lets you deny access for the duration of a
transaction
Locks the rows before the update or delete
Last clause in a select statement
Use only when necessary
NOWAIT will check if rows have already been locked
Explicit Cursors

WHERE CURRENT OF Clause
WHERE CURRENT OF cursor;
–
–
–
–
Use cursors to update or delete the current row.
Include the FOR UPDATE clause in the cursor in
the cursor query to lock the rows first
Use the WHERE CURRENT OF clause to reference
the current row from an explicit cursor.
Example
UPDATE emp SET sal = emp_record.sal *1.10
WHERE CURRENT OF sal_cursor;
Trapping Exceptions
Performed in the EXCEPTION section of the
Block Structure
EXCEPTION
WHEN exception1 [OR exception2] THEN
Statements
WHEN exception3 [OR exception4] THEN
Statements
WHEN OTHERS THEN
Statements

Trapping Exceptions

WHEN OTHERS is the last clause
–
–


Will trap unspecified exceptions
Only one WHEN OTHERS clause is allowed
Only one handler is processed before leaving
the block
Exceptions can be defined in the DECLARE
section and used in the EXCEPTION section
Predefined Exceptions

There are many predefined exceptions
–
Examples





NO_DATE_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
ZERO_DIVIDE
DUP_VAL_ON_INDEX
Functions for Trapping Exceptions

SQLCODE
–
–

Returns the numeric value for the error code.
Can be assigned to a NUMBER variable.
SQLERRM
–
–
Returns the message associated with the error
number.
Can be assigned to a VARCHAR2 variable.
PL/SQL

DBMS_OUTPUT.PUT_LINE
–
–

Other DBMS_OUTPUT procedures
–

Will display information to the screen.
Must SET SERVEROUTPUT ON in SQL*Plus
window.
Check the manual
Slash (/)
–
–
PL/SQL block is terminated by a slash on a line by
itself.
Used when creating blocks in SQL*Plus.
PL/SQL

COMMIT
–

ROLLBACK
–

Will save all changes to the database permanently.
Will remove all changes from the database back to
the last COMMIT.
DDL (Data Definition Language)
–
–
Cannot be used in a PL/SQL block
Example


CREAT TABLE
DROP TABLE
PL/SQL

IS NULL
–
Checks if a value is null.