, , ,

PL SQL Cursors

A cursor is the Private Memory area which is created by an Oracle server for manipulating the data.
Two Types of CURSORS
    1.  EXPLICIT : Multiple row SELECT  STATEMENTS
    2.  IMPLICIT
        All INSERT statements
        All UPDATE statements
        All DELETE statements
        Single row SELECT….INTO Statements
Using Explicit Cursors
The set of rows returned by a query can consist of zero, one, or multiple rows, depending on how many rows meet your search criteria. When a query returns multiple rows, you can explicitly declare a cursor to process the rows. Moreover,
you can declare a cursor in the declarative part of any PL/SQL block, subprogram, or package.
You use three commands to control a cursor: OPEN, FETCH, and CLOSE. First, you initialize the cursor with the OPEN statement, which identifies the result set. Then, you use the FETCH statement to retrieve the first row. You can execute FETCH repeatedly until all rows have been retrieved. When the last row has been processed, you release the cursor with the CLOSE statement. You can process several queries in parallel by declaring and opening multiple cursors.

Using Cursor FOR Loops
In most situations that require an explicit cursor, you can simplify coding by using a cursor FOR loop instead of the OPEN, FETCH, and CLOSE statements. A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor,
repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed.
Consider the PL/SQL block below, which computes results from an experiment, then stores the results in a temporary table. The FOR loop index c1_rec is implicitly declared as a record. Its fields store all the column values fetched from the cursor c1. Dot notation is used to reference individual fields.
DECLARE
result temp.col1%TYPE;
CURSOR c1 IS
SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1;
BEGIN
FOR c1_rec IN c1 LOOP
/* calculate and store the results */
result := c1_rec.n2 / (c1_rec.n1 + c1_rec.n3);
INSERT INTO temp VALUES (result, NULL, NULL);
END LOOP;
COMMIT;
END; Passing Parameters
You can pass parameters to the cursor in a cursor FOR loop. In the following example, you pass a department number. Then, you compute the total wages paid to employees in that department. Also, you determine how many employees have
salaries higher than $2000 and/or commissions larger than their salaries.
— available online in file ’examp8’
DECLARE
CURSOR emp_cursor(dnum NUMBER) IS
SELECT sal, comm FROM emp WHERE deptno = dnum;
total_wages NUMBER(11,2) := 0;
high_paid NUMBER(4) := 0;
higher_comm NUMBER(4) := 0;
BEGIN
/* The number of iterations will equal the number of rows
returned by emp_cursor. */
FOR emp_record IN emp_cursor(20) LOOP
emp_record.comm := NVL(emp_record.comm, 0);
total_wages := total_wages + emp_record.sal +
emp_record.comm;
IF emp_record.sal > 2000.00 THEN
high_paid := high_paid + 1;
END IF;
IF emp_record.comm > emp_record.sal THEN
higher_comm := higher_comm + 1;
END IF;
END LOOP;
INSERT INTO temp VALUES (high_paid, higher_comm,
’Total Wages: ’ || TO_CHAR(total_wages));
COMMIT;
END;
Implicit Cursors – FOR Loops
An Implicit Cursor is automatically associated with any SQL DML statement that does not have an explicit cursor associated with it.
This includes :
     1. ALL INSERT      statements 
     2. ALL UPDATE      statements
     3. ALL DELETE      statements
     4. ALL SELECT…INTO statements
QuickNotes – Implicit Cursors
1. Implicit cursor is called the “SQL” cursor –it stores information concerning the processing of the last  SQL  statement not associated with an explicit cursor.
2.OPEN, FETCH, AND CLOSE don’t apply.
3. All cursor attributes apply.

FOR UPDATE Clause
  • Use explicit locking to deny access for the duration of a transaction
  • Locks the rows before update or delete .

Syntax:  Select …..
     from
     FOR UPDATE  [ OF column reference ] [NOWAIT];e.g.
Declare
    Cursor EmpCursor is
    select emp_id, last_name, dept_name
    from employees , department
    where employees.dept_id=department.dept_id
    and employees.dept_id=80
    FOR UPDATE OF salary NOWAIT;

/by
, , ,

Control Structures in PL SQL

This chapter shows you how to structure the flow of control through a PL/SQL program. You learn how statements are connected by simple but powerful control structures that have a single entry and exit point. Collectively, these structures can handle any situation. Their proper use leads naturally to a well-structured program.
IF Statements
Often, it is necessary to take alternative actions depending on circumstances. The IF statement lets you execute a sequence of statements conditionally. That is, whether the sequence is executed or not depends on the value of a condition. There are three forms of IF statements: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF.


Iterative Control: LOOP and EXIT Statements
LOOP statements let you execute a sequence of statements multiple times. There are three forms of LOOP statements: LOOP, WHILE-LOOP, and FOR-LOOP.
Examples:
1. LOOP
…
IF credit_rating < 3 THEN
EXIT; — exit loop immediately
END IF;
END LOOP;— control resumes here

2. LOOP
FETCH c1 INTO …
EXIT WHEN c1%NOTFOUND; — exit loop if condition is true
…
END LOOP;
CLOSE c1;
Loop Labels
Like PL/SQL blocks, loops can be labeled. The label, an undeclared identifier enclosed by double angle brackets, must appear at the beginning of the LOOP statement, as follows:
<<label_name>>
LOOP
sequence_of_statements
END LOOP;
Optionally, the label name can also appear at the end of the LOOP statement, as the
following example shows:
<<my_loop>>
LOOP
…
END LOOP my_loop;
When you nest labeled loops, you can use ending label names to improve readability.
With either form of EXIT statement, you can complete not only the current loop, but any enclosing loop. Simply label the enclosing loop that you want to complete. Then, use the label in an EXIT statement, as follows:
<<outer>>
LOOP
…
LOOP
…
EXIT outer WHEN … — exit both loops
END LOOP;
…
END LOOP outer;
Every enclosing loop up to and including the labeled loop is exited.

WHILE-LOOP
The WHILE-LOOP statement associates a condition with a sequence of statements enclosed by the keywords LOOP and END LOOP, as follows:

Before each iteration of the loop, the condition is evaluated. If the condition is true, the sequence of statements is executed, then control resumes at the top of the loop. If the condition is false or null, the loop is bypassed and control passes to the next statement.

The number of iterations depends on the condition and is unknown until the loop completes. The condition is tested at the top of the loop, so the sequence might execute zero times. In the last example, if the initial value of total is larger than
25000, the condition is false and the loop is bypassed.
FOR-LOOP
Whereas the number of iterations through a WHILE loop is unknown until the loop completes, the number of iterations through a FOR loop is known before the loop is entered. FOR loops iterate over a specified range of integers. (Cursor FOR loops iterate over the result set of a cursor, are discussed in later section) The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP. A double dot (..) serves as the range operator. The syntax follows:

 
The range is evaluated when the FOR loop is first entered and is never re-evaluated.
As the next example shows, the sequence of statements is executed once for each integer in the range. After each iteration, the loop counter is incremented.
FOR i IN 1..3 LOOP — assign the values 1,2,3 to i
sequence_of_statements — executes three times
END LOOP;
The following example shows that if the lower bound equals the higher bound, the sequence of statements is executed once:
FOR i IN 3..3 LOOP — assign the value 3 to i
sequence_of_statements — executes one time
END LOOP;By default, iteration proceeds upward from the lower bound to the higher bound. However, as the example below shows, if you use the keyword REVERSE, iteration proceeds downward from the higher bound to the lower bound. After each
iteration, the loop counter is decremented.
FOR i IN REVERSE 1..3 LOOP — assign the values 3,2,1 to i
sequence_of_statements — executes three times
END LOOP;

Dynamic Ranges
PL/SQL lets you determine the loop range dynamically at run time, as the following example shows:
SELECT COUNT(empno) INTO emp_count FROM emp;
FOR i IN 1..emp_count LOOP
…
END LOOP;
The value of emp_count is unknown at compile time; the SELECT statement returns the value at run time.
Using the EXIT Statement
The EXIT statement allows a FOR loop to complete prematurely. For example, the following loop normally executes ten times, but as soon as the FETCH statement fails to return a row, the loop completes no matter how many times it has executed:
FOR j IN 1..10 LOOP
FETCH c1 INTO emp_rec;
EXIT WHEN c1%NOTFOUND;
…
END LOOP;
Suppose you must exit from a nested FOR loop prematurely. You can complete not only the current loop, but any enclosing loop. Simply label the enclosing loop that you want to complete. Then, use the label in an EXIT statement to specify which
FOR loop to exit, as follows:
<<outer>>
FOR i IN 1..5 LOOP
…
FOR j IN 1..10 LOOP
FETCH c1 INTO emp_rec;
EXIT outer WHEN c1%NOTFOUND; — exit both FOR loops
…
END LOOP;
END LOOP outer;
— control passes here

NULL Statement
The NULL statement explicitly specifies inaction; it does nothing other than pass control to the next statement. It can, however, improve readability. In a construct allowing alternative actions, the NULL statement serves as a placeholder. It tells
readers that the associated alternative has not been overlooked, but that indeed no action is necessary. In the following example, the NULL statement shows that no action is taken for unnamed exceptions:
EXCEPTION
WHEN ZERO_DIVIDE THEN
ROLLBACK;
WHEN VALUE_ERROR THEN
INSERT INTO errors VALUES …
COMMIT;
WHEN OTHERS THEN
NULL;
END;


Each clause in an IF statement must contain at least one executable statement. The NULL statement is executable, so you can use it in clauses that correspond to circumstances in which no action is taken. In the following example, the NULL
statement emphasizes that only top-rated employees get bonuses:
IF rating > 90 THEN
compute_bonus(emp_id);
ELSE
NULL;
END IF;

Also, the NULL statement is a handy way to create stubs when designing applications from the top down. A stub is dummy subprogram that allows you to defer the definition of a procedure or function until you test and debug the main program. In the following example, the NULL statement meets the requirement that at least one statement must appear in the executable part of a subprogram:
PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
BEGIN
NULL;
END debit_account;

/by
, , ,

Scope and Visibility in PL SQL

References to an identifier are resolved according to its scope and visibility. The scope of an identifier is that region of a program unit (block, subprogram, or package) from which you can reference the identifier. An identifier is visible only in
the regions from which you can reference the identifier using an unqualified name. Below Figure shows the scope and visibility of a variable named x, which is declared in an enclosing block, then redeclared in a sub-block.
Identifiers declared in a PL/SQL block are considered local to that block and global to all its sub-blocks. If a global identifier is redeclared in a sub-block, both identifiers remain in scope. Within the sub-block, however, only the local identifier is visible because you must use a qualified name to reference the global identifier.
Although you cannot declare an identifier twice in the same block, you can declare the same identifier in two different blocks. The two items represented by the identifier are distinct, and any change in one does not affect the other. However, a block cannot reference identifiers declared in other blocks at the same level because those identifiers are neither local nor global to the block.

/by
, , ,

Assignments in PL SQL

Variables and constants are initialized every time a block or subprogram is entered.
By default, variables are initialized to NULL. So, unless you expressly initialize a variable, its value is undefined, as the following example shows:
DECLARE
count INTEGER;
…
BEGIN
count := count + 1; — assigns a null to count
The expression on the right of the assignment operator yields NULL because count is null. To avoid unexpected results, never reference a variable before you assign it a value.
You can use assignment statements to assign values to a variable. For example, the following statement assigns a new value to the variable bonus, overwriting its old value:
bonus := salary * 0.15;
The expression following the assignment operator can be arbitrarily complex, but it must yield a datatype that is the same as or convertible to the datatype of the variable.
Boolean Values
Only the values TRUE, FALSE, and NULL can be assigned to a Boolean variable. For example, given the declaration
DECLARE
done BOOLEAN;the following statements are legal:
BEGIN
done := FALSE;
WHILE NOT done LOOP
…
END LOOP;
When applied to an expression, the relational operators return a Boolean value. So, the following assignment is legal:
done := (count > 500);
Expressions and Comparisons
Database Values
You can use the SELECT statement to have Oracle assign values to a variable. For each item in the select list, there must be a corresponding, type-compatible variable in the INTO list. An example follows:
DECLARE
my_empno emp.empno%TYPE;
my_ename emp.ename%TYPE;
wages NUMBER(7,2);
BEGIN
…
SELECT ename, sal + comm
INTO last_name, wages FROM emp
WHERE empno = emp_id;
However, you cannot select column values into a Boolean variable.
Quick notes -Assignment
1. := (ASSIGNMENT ) whereas = (VALUE EQUALITY)
2. The datatype of the left and right hand side of an assignment must be the same or implicitly convertible to each other.                    
For ex. , N:=‘7’ is legal because number may be implicitly converted to char.
3. Column or table reference are not allowed on either side of an assignment operator( : = ).
                SCOTT.EMP.EMPNO := 1234;
                  location := dept.loc.;    
Above two are incorrect.
/by
, , ,

PL/SQL Placeholders

Placeholders are temporary storage area. Placeholders can be any of Variables, Constants and Records. Oracle defines placeholders to store data temporarily, which are used to manipulate data during the execution of a PL SQL block.
Depending on the kind of data you want to store, you can define placeholders with a name and a datatype. Few of the datatypes used to define placeholders are as given below. Number (n,m) , Char (n) , Varchar2 (n) , Date , Long , Long raw, Raw, Blob, Clob, Nclob, Bfile
Place holders are used for
• Temporary storage of data, • Manipulation of stored values, • Reusability, • Ease of maintenance
Declaring PL/SQL Variable


Handling Variables in PL/SQL

  • 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.

Types of PL/SQL Variables
All PL/SQL variables have a data type, which specifies a storage format, constraints, and valid range of values. PL/SQL supports four data type categories—scalar, composite, reference, and LOB (large object)—that you can use for declaring variables, constants, and pointers.

  1. Scalar data types hold a single value. The main data types are those that correspond to column types in Oracle server tables; PL/SQL also supports Boolean variables.
  2. Composite data types, such as records, allow groups of fields to be defined and manipulated in PL/SQL blocks.
  3. Reference data types hold values, called pointers, that designate other program items. Reference data types are not covered in this course.
  4. LOB data types hold values, called locators, that specify the location of large objects (such as graphic images) that are stored out of line. LOB data types are discussed in detail later in this course.

Non-PL/SQL variables include host language variables declared in precompiler programs, screen fields in Forms applications, and iSQL*Plus host variables.
 
Quick Notes – Variable  Declaration
  1. The rules for identifiers are same as for SQL objects.
  2. NOT NULL/CONSTANT may be optionally used
  3. Only one identifier per line is allowed .  
     DECLARE 
        firstname    lastname  CHAR(20)  ; – illegal 
    DECLARE 
        firstname    CHAR(20)  ; -legal 
        lastname    CHAR(20)  ; – legal 

Attribute Declaration
PL/SQL objects (such as variables and constants) and database objects (such as columns and tables ) are associated with certain attributes.
%TYPE attribute
DECLARE
  books_printed        NUMBER (6);
  books_sold            books.sold%TYPE ;
  maiden_name        emp.ename%TYPE ;
%ROWTYPE attribute
DECLARE
  dept_row        dept%ROWTYPE ;

/by