PL/SQL Some Advanced Fundamental

1.Running Your First PL/SQL Program

This is called anonymous block,a block with no name.Its only executable statement is a call to the procedure PUT_LINE.supplied in Oracle's built in package named DBMS_OUTPUT.

SQL> set serveroutput on
SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE('hello, world');
  3  END;
  4  /
hello, world

PL/SQL procedure successfully completed.

SQL>

2.Saving Scripts to Use Again Later.

SQL> EDIT hello.sql
SET SERVEROUTPUT ON

BEGIN
  DBMS_OUTPUT.PUT_LINE('hello, world');
END;
/
~
~
~
"hello.sql" [New] 6L, 74C written

SQL> @hello.sql
hello, world

PL/SQL procedure successfully completed.

SQL> @hello
hello, world

PL/SQL procedure successfully completed.

SQL>

3. Introduction to Program Structure

In PL/SQL, there are only three types of blocks:

• Anonymous blocks

• Procedures

• Functions

 Anonymous blocks

BEGIN
statements
END;

The second major form of anonymous block includes a declaration section. In this form, the initial keyword is DECLARE rather than BEGIN:

DECLARE
declarations
BEGIN
statements
END;

the most common forms of an anonymous block: 

[ DECLARE
declarations ]
BEGIN
statements
[ EXCEPTION
exception handlers ]
END;

Named blocks: procedures and functions

• Procedure

A named program that executes some predefined statements and then returns control to whatever called it. After creating a procedure,you can invoke it by name from other programs.

• Function

Similar to a procedure,except that it returns a value to the program that calls it. The data returned by a function is always of a specific, predefined datatype

SQL> 
SQL> CREATE FUNCTION message_for_the_world
  2  RETURN VARCHAR2
  3  AS
  4  BEGIN
  5    RETURN 'hello, world';
  6  END;
  7  /

Function created.

SQL>

.One way we could execute the function would be to declare a temporary variable and assign the output from the function to it, as shown here:

SQL> 
SQL> DECLARE
  2    msg VARCHAR2(30);
  3  BEGIN
  4    msg := message_for_the_world;
  5    DBMS_OUTPUT.PUT_LINE(msg);
  6  END;
  7  /
hello, world

PL/SQL procedure successfully completed.

SQL>

since the function returns a VARCHAR2,the function can “stand in” wherever you could use a string. You don’t really need a temporary variable in the program.

SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE(message_for_the_world);
  3  END;
  4  /
hello, world

PL/SQL procedure successfully completed.

SQL> 

Variables

In PL/SQL,before you can work with any kind of variable,you must first declare it; that is,you must give it a name and specify its datatype.

Datatypes

The most common datatypes in PL/SQL are in four families: string,number,date, and logical (Boolean).

Strings

Strings are “free form” data. A string can contain any valid character in the character set of a language. While there are several variations of strings,the datatype you will almost always use is VARCHAR2.

The general format for a VARCHAR2 declaration is:

variable_name VARCHAR2(n);

SQL> 
SQL> DECLARE
  2    small_string VARCHAR2(4);
  3    line_of_text VARCHAR2(2000);
  4    biggest_string_allowed VARCHAR2(32767);
  5  BEGIN
  6    biggest_string_allowed := 'Tiny';
  7    line_of_text := 'Tiny';
  8    IF biggest_string_allowed = line_of_text
  9    THEN
 10      DBMS_OUTPUT.PUT_LINE('They match!');
 11    END IF;
 12  END;
 13  /
They match!

PL/SQL procedure successfully completed.

SQL> 

Numbers

PL/SQL,just like the Oracle database server,offers a variety of numeric datatypes to suit different purposes. There are generally two types of numeric data: whole number and decimal (in which digits to the right of the decimal point are allowed)

The declaration of a NUMBER looks like this:

variable_name NUMBER [ ( precision [, scale ] ) ]; 

Precision and scale,if present,must be literal values (and integers at that); you cannot use variables or constants in the declaration.

The following examples demonstrate the different ways you can declare variables of type NUMBER:

The bean_counter variable can hold values with up to ten digits of precision, three of which are to the right of the decimal point. If you assign 12345.6784 to bean_counter,it is rounded to 12345.678. If you assign 1234567891.23 to the variable,the operation will return an error because there are more digits than allowed for in the precision:

bean_counter NUMBER (10,3);

• The any_number variable can span the full range of supported values,because the default precision and scale are unspecified:

any_number NUMBER;

Dates

the only true date
datatype you could use was DATE:

DECLARE
 l_birth_date DATE;

Handy built-in functions for dates include the following:

SYSDATE

Returns the current system date and time down to the nearest second

SYSTIMESTAMP (supported in Oracle9i only)

Returns the current system date and time down to the nearest .000001 seconds

TO_CHAR

Converts a date to a human-readable string

TO_DATE

Converts a human-readable string to a date

Booleans

Oracle’s SQL does not support this datatype; you cannot create a table with a column of datatype BOOLEAN. You can create a table with a column of datatype VARCHAR2(1) and store the text “T” or “F”,representing TRUE or FALSE,in that column.

Here is another example of a Boolean declaration:

DECLARE
 too_young_to_vote BOOLEAN;

Boolean values and variables are very useful in PL/SQL. Because a Boolean variable can only be TRUE,FALSE,or NULL,you can use that variable to explain what is happening in your code. With Booleans you can write code that is easily readable, because it is more English-like. You can replace a complicated Boolean expression involving many different variables and tests with a single Boolean variable that directly expresses the intention and meaning of the text.

Declaring Variables

Variables to which you do not assign an initial value will default to NULL. In other
words:
number_of_teeth NUMBER;
is equivalent to:
number_of_teeth NUMBER := NULL;
is equivalent to:
number_of_teeth NUMBER DEFAULT NULL;

Common Operators

 Assignment Operator

Given two variables a and b, an assignment statement is of the following form:

a := b;

You can store a string in a VARCHAR2,but you can’t store it in a NUMBER. The statement:

first_name := 'Steven';

Assuming that pi is declared as NUMBER, here is the assignment:

pi := 3.141592654;

If is_lukewarm is a BOOLEAN, an assignment might look like:

is_lukewarm := FALSE;

Arithmetic Operators

a + b
Result of adding a and b
a - b
Result of subtracting b from a
a * b
Result of multiplying a and b
a / b
Result of dividing a by b
a**b
Result of raising a to the bth power

Logical Operators

This class of operators act on Boolean values and return Boolean values. In the following, a and b are declared to be of datatype BOOLEAN.

a AND b

Logical conjunction operator. If both operands a and b are true then the result is TRUE. Otherwise, if at least one expression is FALSE, then the result is FALSE

a OR b

Logical disjunction operator. If at least one expression is TRUE,then the result is TRUE. It doesn’t matter what the other operand is—it can be null or true or false.

NOT b

Logical negation operator. The result is the logical “opposite” of a b,as long as b is not null. If b is null,though,it’s kind of a strange case,because NOT b is then also null!

Non-Null Comparison Operators

a = b

Evaluates to TRUE if a and b are non-null and contain the same value. Evaluates to FALSE if a and b are non-null and contain different values. Evaluates to NULL if one or both of the operands is null. This works for non-null numbers,strings,dates,Booleans,and,under certain conditions, other datatypes.

a != b

The logical inverse of =,evaluates to TRUE if a and b are non-null and contain different values. If a or b is null, the result is null. The following statement fragments illustrate the four variants of the inequality operator; all of these are equivalent:

IF favorite_flavor != 'ROCKY ROAD' THEN ... 
IF favorite_flavor <> 'ROCKY ROAD' THEN ... 
IF favorite_flavor ~= 'ROCKY ROAD' THEN ... 
IF favorite_flavor ^= 'ROCKY ROAD' THEN ... 

These various forms of the operator can make life easier for programmers who already use them in other languages.

 In all of the following illustrative cases, a, b, c, and d are assumed non-null:
a > b
Evaluates to TRUE if a is greater than b.
a < b
Evaluates to TRUE if a is less than b.
a >= b
Evaluates to TRUE if a is greater than b,or if a is equal to b. This is just a shortcut for the compound expression a > b OR a = b.
a <= b
Evaluates to TRUE if a is less than b,or if a is equal to b. Same as a < b OR a =
b.
a IN (b, c [, d, ... ] )
Convenience equality operator,short for the compound expression (a = b) OR
(a = c) [ OR (a = d) .... ]
a BETWEEN b AND c
Inclusive range checking operator,short for the compound expression a >= b
AND a <= c.
Note that with these comparison operators,if any of the operands are null,the result
will be null.

Test for Nullity

To test correctly whether a particular variable or expression is null,you must use the IS NULL operator. When testing for the presence of a non-null value,use IS NOT NULL.

a IS NULL
Evaluates to TRUE if a is null.
a IS NOT NULL
Evaluates to TRUE if a is not null.

IF number_of_pages IS NULL
THEN
 DBMS_OUTPUT.PUT_LINE('Warning: number of pages is unknown.');
END IF;

String Patterns and Wildcards: LIKE, %, _

expression

The string you want to examine to see if the pattern is present.

pattern

Another string that includes one or more of the wildcard characters,% or _. The percent wildcard matches any number (zero or more) of characters,and the underscore matches any one single character.

String Concatenation: ||

PL/SQL uses two vertical bars || as an operator that will connect two strings. Consider the expression:

a || b

If a and b are strings,this evaluates to a string consisting of the “joining” of them. Null operands are treated as strings with zero length. So for example, the following:

full_name := 'Steven ' || 'Feuerstein';

stores in the full_name variable the same result as the following:

full_name := 'Steven Feuerstein';

You can also concatenate a series of strings:

my_family := 'Steven ' || 'Veva ' || NULL || 'Chris ' || 'Eli';

which stores in my_family the string:

Steven Veva Chris Eli

IF Statements

IF condition1
THEN
   statements
[ ELSIF condition2
THEN
   statements ] ...
[ ELSIF conditionn
THEN
   statements ]
[ ELSE
   last_statements ]
END IF;

IF book_count > 10000
THEN
 ready := TRUE;
 DBMS_OUTPUT.PUT_LINE ('We''re ready to open the library!');
END IF;


IF hourly_wage < 10
THEN
 hourly_wage := hourly_wage * 1.5;
ELSE
 hourly_wage := hourly_wage * 1.1;
END IF;

IF salary BETWEEN 10000 AND 40000
THEN
 bonus := 1500;
ELSIF salary > 40000 AND salary <= 100000
THEN
 bonus := 1000;
ELSE
 bonus := 0;
END IF;

Simple CASE statement

The general syntax of the so-called simple CASE statement is:

CASE selector
 WHEN expression1 THEN statements
 [ WHEN expression2 THEN statements ]
 ...
 [ ELSE statements ]
END CASE;

Searched CASE statement

This alternate form is more flexible; it omits the selector and supports individual conditions instead of testing for equality with a selector. In other words:

CASE
 WHEN condition1 THEN statements
 [ WHEN condition2 THEN statements ]
 ...
 [ ELSE statements ]
END CASE;

CASE
 WHEN salary BETWEEN 10000 AND 40000 THEN
 bonus := 1500;
 WHEN salary > 40000 AND salary <= 100000 THEN
 bonus := 10000;
 ELSE
 bonus := 0;
END CASE;

CASE expressions

In PL/SQL,the CASE keyword can serve more than one purpose. We’ve just seen an example of CASE serving as a statement,but it can also serve as an expression—that is, it can return a value.

gender_name :=
 CASE gender_code
 WHEN 'M' THEN 'MALE'
 WHEN 'F' THEN 'FEMALE'
 ELSE 'UNKNOWN'
 END;

Executing in Circles: Loop Statements

A very common requirement in programming is to execute the same functionality repetitively—in a loop. Programmers call this iteration,and it is a mainstay of virtually all procedural languages.

FOR loop (numeric and cursor)
This loop executes its body of code for a specific, limited number of iterations.
Simple or infinite loop
This loop executes its body of code until it encounters an EXIT statement.
WHILE loop
This loop executes its body of code until the WHILE condition evaluates to
FALSE.

FOR Loop

Use the FOR loop when you know in advance how many times you want the loop to execute (its number of iterations).

BEGIN
 show_books_borrowed (1);
 show_books_borrowed (2);
 show_books_borrowed (3);
 show_books_borrowed (4);
 show_books_borrowed (5);
 show_books_borrowed (6);
END;
/

Alternatively, we could just use a FOR loop to achieve the same result:
BEGIN
 FOR month_num IN 1 .. 6
 LOOP
 show_books_borrowed (month_num);
 END LOOP;
END;
/
I’d rather use the loop, wouldn’t you? The exact syntax is:
FOR loop_counter IN [ REVERSE ] lower_bound .. upper_bound
LOOP
statements
END LOOP;

Where:
loop_counter
An identifier that has not been declared in the program,this variable gives you a
way of detecting the “trip number” through the loop.
lower_bound
A numeric expression that Oracle uses to compute the smallest value assigned to
loop_counter. Often,this will just be the number 1. You should make this an
integer,but if you don’t,PL/SQL automatically rounds it to an integer. If the
lower bound is greater than the upper bound,the loop will not execute; if it is
null, your program will end in a runtime error.
REVERSE
Without this keyword,the loop counter increases by one with every trip through
the loop,from the lower to the upper bound. With REVERSE,though,the loop
will decrease by one instead, going from the upper to the lower bound.
.. (yes, that really is two consecutive dots)
This is a special operator that means “visit all the integers between lower_bound
and upper_bound.”
upper_bound
Numeric expression that provides the highest number the counter will be. This
must be equal to or greater than the lower bound in order for the loop to execute.

Simple (Infinite) Loop

LOOP
statements
END LOOP;

This is also called an infinite loop*,because the LOOP syntax itself does not offer any way to stop the loop. Here, for example, is an infinite loop:

LOOP
 l_date_published := SYSDATE;
END LOOP;

You can usually tell when you have written an infinite loop: your SQL*Plus session seems to go into a coma. Now,there are actually some situations in which an “infinite” loop is desirable (such as a program that wakes up every ten minutes to check for a message). In general,though,you want to avoid infinite loops,and PL/SQL gives you an easy way to do that: the EXIT statement: EXIT;

This means simply “stop looping now and proceed to the next executable statement in the program.” To make things simpler for you,Oracle provides the EXIT WHEN feature in PL/SQL:

LOOP
statements
 EXIT WHEN condition;
END LOOP;

counter := 0;
LOOP
 counter := counter + 1;
 prior_approx := approx;
 approx := new_approx(approx);
 EXIT WHEN counter = 1000 OR prior_approx - approx = 0.0;
END LOOP;

WHILE Loop

The WHILE loop executes as long as (“while”) the specified Boolean condition evaluates to TRUE. It looks like this:

WHILE condition
LOOP
statements
END LOOP;

This loop is the equivalent of: 

LOOP
 EXIT WHEN NOT condition;
statements
END LOOP;

prior_approx := approx;
approx := new_approx(approx);
counter := 0;
WHILE counter <= 1000 AND prior_approx - approx != 0.0
LOOP
 counter := counter + 1;
 prior_approx := approx;
 approx := new_approx(approx);
END LOOP;

Simple or WHILE Loop?

 Here,then,is my extrapolation: when you write longer,complicated loops,the END LOOP statement may be 50,100,or even 200 lines away from the WHILE LOOP statement. Further,the WHILE condition could also involve 2,5,or 10 different variables and complex formulas. In this case,you will find yourself repeating perhaps 5 lines of code before the WHILE statement and at the end of the loop. How can you maintain that code effectively,so that any change in one set of assignments occurs in the other as well? Here’s the bottom line: if as you write a WHILE loop you find yourself repeating the setup and next-iteration code,try switching to a simple loop. You will very likely be able to write and maintain just one version of the code.

Code Formatting: Requirements and Guidelines

As you begin to write your own code,you will have many questions about the best
approach to capitalization,indentation,spacing,and other aspects of programming
style. This section lists some of the features of the language in this area,and should
help you get started with good habits

Upper- or Lowercase?

PL/SQL is case-insensitive (except for the values of literal strings). That means you can type keywords and identifiers in uppercase or lowercase or mixed-case—it doesn’t make any difference. So all of these statements are identical:

favorite_flavor VARCHAR2(20); 
Favorite_Flavor varchar2(20); 
fAvOrItE_flaVOR vArCHAr(20);

Spacing and Line Breaks

You’ll sometimes hear programmers talk of whitespace in their programs.
Whitespace consists of spaces,tabs,and/or line breaks. PL/SQL allows any amount
of whitespace to separate keywords and identifiers. The declaration:
favorite_flavor VARCHAR2(20);
is completely equivalent to:
 favorite_flavor VARCHAR2(20);
and also to the ludicrous:
 favorite_flavor
VARCHAR2
 ( 20 ) ;