Comments
Comments are used in the PL/SQL program to improve the readability and
understandability of a program. A comment can appear anywhere in the program code. The compiler ignores comments. Generally, comments are used to
describe the purpose and use of each code segment. A PL/SQL comment may
be a single-line or multiline.
Single-Line Comments
Single-line comments begin with a double hyphen (–) anywhere on a line and
extend to the end of the line.
Example
– start calculations
Multiline Comments
Multiline comments begin with a slash-asterisk (/*) and end with an asteriskslash (*/), and can span multiple lines.
Example
/* Hello World! This is an example of multiline comments in PL/SQL */
Variables and Constants
Variables and constants can be used within PL/SQL block, in procedural
statements and in SQL statements. These are used to store the values. As
the program executes, the values of variables can change, but the values of
constants cannot. However, it is must to declare the variables and constants,
before using these in executable portion of PL/SQL. Let us see how to declare
variables and constants in PL/SQL.
Declaration
1Variables and constants are declared in the Declaration section of PL/SQL
block. These can be any of the SQL data type like CHAR, NUMBER,
DATE, etc.
220 5 PL/SQL
I. Variables Declaration
The syntax for declaring a variable is as follows:
identifier datatype;
Example
To declare the variable name, age, and joining date as datatype
VARCHAR2(10), NUMBER(2), DATE, respectively; declaration statement
is as follows:
DECLARE
Name VARCHAR2(10);
Age NUMBER(2);
Joining date DATE;
Initializing the Variable
By default variables are initialized to NULL at the time of declaration. If we
want to initialize the variable by some other value, syntax would be as follows:
Identifier datatype := value;
Or,
Identifier datatype DEFAULT value;
Example
If a number of employees have same joining date, say 01-JULY-99. It is better
to initialize the joining date rather than entering the same value individually,
any of the following declaration can be used:
Joining date DATE := 01-JULY-99; (or)
Joining date DATE DEFAULT 01-JULY-99;
Constraining a Variable
Variables can be NOT NULL constrained at the time of declaring these, for
example to constrain the joining date NOT NULL, the declaration statement
would be as follows:
Joining date DATE NOT NULL: = 01-JULY-99;
(NOT NULL constraint must be followed by an initialization clause)
thus following declaration will give an error:
Joining date DATE NOT NULL; – illegal
5.4 PL/SQL Language Elements 221
Declaring Constants
Declaration of constant is similar to declaration of variable, except the keyword CONSTANT precedes the datatype and it must be initialized by some
value. The syntax for declaring a constant is as follows:
identifier CONSTANT datatype := value;
Example
To define the age limit as a constant, having value 30; the declaration statement would be as follows: Age limit CONSTANT NUMBER := 30;
Restrictions
PL/SQL imposes some restrictions on declaration as follows:
(a) A list of variables that have the same datatype cannot be declared in the
same row
Example
A, B, C NUMBER (4,2); – illegal
It should be declared in separate lines as follows:
A NUMBER (4,2);
B NUMBER (4,2);
C NUMBER (4,2);
(b) A variable can reference to other variable if and only if that variable is
declared before that variable. The following declaration is illegal:
A NUMBER(2) := B;
B NUMBER(2) := 4;
Correct declaration would be as follows:
B NUMBER(2) := 4;
A NUMBER(2) := B;
(c) In a block same identifier cannot be declared by different datatype. The
following declaration is illegal:
DECLARE
X NUMBER(4,2);
X CHAR(4); – illegal
222 5 PL/SQL
5.5 Data Types
Every constant and variable has a datatype. A datatype specifies the space
to be reserved in the memory, type of operations that can be performed,
and valid range of values. PL/SQL supports all the built-in SQL datatypes.
Apart from those datatypes, PL/SQL provides some other datatypes. Some
commonly used PL/SQL datatypes are as follows:
BOOLEAN
One of the mostly used datatype is BOOLEAN. A BOOLEAN datatype
is assigned to those variables, which are required for logical operations.
A BOOLEAN datatype variable can store only logical values, i.e., TRUE,
FALSE, or NULL. A BOOLEAN variable value cannot be inserted in a table;
also, a table data cannot be selected or fetched into a BOOLEAN variable.
%Type
The %TYPE attribute provides the datatype of a variable or database column.
In the following example, %TYPE provides the datatype of a variable:
balance NUMBER(8,2);
minimum balance balance%TYPE;
In the above example PL/SQL will treat the minimum balance of the same
datatype as that of balance, i.e., NUMBER(8,2). The next example shows
that a %TYPE declaration can include an initialization clause:
balance NUMBER(7,2);
minimum balance balance%TYPE := 500.00;
The %TYPE attribute is particularly useful when declaring variables that
refer to database columns. Column in a table can be referenced by %TYPE
attribute.
Example
To declare a column my empno of the same datatype as that of empno column
of emp table in scott/tiger user, the declaration statement would be as follows:
my empno scott.emp.empno%TYPE;
Using %TYPE to declare my empno has two advantages. First, the knowledge
of exact datatype of empno is not required. Second, if the database definition
of empno changes, the datatype of my empno changes accordingly at run time.
But %TYPE variables do not inherit the NOT NULL column constraint, even
though the database column empno is defined as NOT NULL, one can assign
a null to the variable my empno.
5.6 Operators Precedence 223
%Rowtype
The %ROWTYPE attribute provides a record type that represents a row in a
table (or view). The record can store an entire row of data selected from the
table.
Example
emp rec is declared as a record datatype of emp table. emp rec can store a
row selected from the emp table.
emp rec emp%ROWTYPE;
Expressions
Expressions are constructed using operands and operators. PL/SQL supports
all the SQL operators; in addition to those operators it has one more operator,
named exponentiation (symbol is **). An operand is a variable, constant,
literal, or function call that contributes a value to an expression. An example
of simple expression follows:
A=B ∗ ∗3
where A, B, and 3 are operand; = and ** are operators. B**3 is equivalent to
value of thrice multiplying the B, i.e., B*B*B.
Operators may be unary or binary. Unary operators such as the negation
operator (−) operate on one operand; binary operators such as the division
operator (/) operate on two operands. PL/SQL evaluates (finds the current
value of) an expression by combining the values of operands in ways specified
by the operators. This always yields a single value and datatype. PL/SQL
determines the datatype by examining the expression and the context in which
it appears.
5.6 Operators Precedence
The operations within an expression are done in a particular order depending
on their precedence (priority). Table 5.1 lists the operator’s level of precedence from top to bottom. Operators listed in the same row have equal
precedence.
Operators with higher precedence are applied first, but if parentheses
are used, expression within innermost parenthesis is evaluated first. For
example the expression 8 + 4/2 ∗ ∗2 results in a value 9, because exponentiation has the highest priority followed by division and addition. Now in the
same expression if we put parentheses, the expression 8+((4/2)∗∗2) results in
a value 12 not 9, because now first it will solve the expression within innermost
parentheses.
224 5 PL/SQL
Table 5.1. Order of operations
operator operation
**, NOT exponentiation, logical negation
+, − identity, negation
*, / multiplication, division
+, −, || addition, subtraction, concatenation
=, !=, <, >, <=, >=, IS NULL, comparison
LIKE, BETWEEN, IN
AND conjunction
OR disjunction
5.7 Control Structure
Control structure is an essential part of any programming language. It controls
the flow of process. Control structure is broadly divided into three categories:
– Conditional control,
– Iterative control, and
– Sequential control
In the following sections we will discuss about each of these control structures
in detail.
Conditional Control
A conditional control structure tests a condition to find out whether it is
true or false and accordingly executes the different blocks of SQL statements.
Conditional control is generally performed by IF statement. There are three
forms of IF statement. IF-THEN, IF-THEN-ELSE, IF-THEN-ELSEIF.
IF-THEN
It is the simplest form of IF condition. The syntax for this statement is as
follows:
IF condition THEN
Sequence of statements
END IF;
Example
To compare the values of two variables A and B and to assign the value of A
to HIGH if A is greater than B. The IF construct for this is as follows:
IF A > B THEN
HIGH := A;
ENDIF;
5.7 Control Structure 225
The sequence of statements is executed only if the condition is true. If
the condition is FALSE or NULL, the sequence of statements is skipped and
processing continues from statements following END IF statements.
IF-THEN-ELSE
As it is clear with the IF-THEN construct, if condition is FALSE the control
exits to next statement out of IF-THEN clause. To execute some other set of
statements in case condition evaluates to FALSE, the second form of IF statement is used, it adds the keyword ELSE followed by an alternative sequence
of statements, as follows:
IF condition THEN
sequence of statements1
ELSE
sequence of statements2
END IF;
Example
To become clear about it, take the previous example, to compare the value of
A and B and assign the value of greater number to HIGH. The IF construct
for this is as follows:
IF A > B THEN
HIGH := A;
ELSE
HIGH := B;
ENDIF;
The sequence of statements in the ELSE clause is executed only if the
condition is FALSE or NULL.
IF-THEN-ELSIF
In the previous constructs of IF, we can check only one condition, whether it is
true or false. There is no provision if we want to check some other conditions if
first condition evaluates to FALSE; for this purpose third form of IF statement
is used. It selects an action from several mutually exclusive alternatives. The
third form of IF statement uses the keyword ELSIF (not ELSEIF) to introduce
additional conditions, as follows:
226 5 PL/SQL
IF condition1 THEN
sequence of statements1
ELSIF condition2 THEN
sequence of statements2
ELSE
sequence of statements3
END IF;
5.8 Steps to Create a PL/SQL Program
1. First a notepad file can be created as typing in the Oracle SQL editor.
Figure 5.2 shows the command to create a file,
2. Then a Notepad file will appear and at the same time background Oracle
will be disabled. It is shown in Fig. 5.3
3. We can write our PL/SQL program in that file, save that file, and we can
execute that program in the Oracle editor as in Fig. 5.4. In this program
Cursor (Cur rent Set of Records) concept is used which we will see in
the following pages. Here content of EMP table is opened by the cursor
and they are displayed by the DBMS OUTPUT package. Command IF is
used to check whether the cursor has been opened successfully by using
%Found attribute.
4. Then we can execute that file as follows in Fig. 5.5
Fig. 5.2. Creating a file
5.8 Steps to Create a PL/SQL Program 227
Fig. 5.3. Confirmation for the file created
Fig. 5.4. Program writing to the notepad
228 5 PL/SQL
Fig. 5.5. Program execution
5.9 Iterative Control
In iterative control a group of statements are executed repeatedly till certain
condition is true, and control exits from loop to next statement when the
condition becomes false. There are mainly three types of loop statements:
LOOP, WHILE-LOOP, FOR-LOOP.
LOOP
LOOP is the simplest form of iterative control. It encloses a sequence of statements between the keywords LOOP and END LOOP. The general syntax for
LOOP control is as follows:
LOOP
sequence of statements
END LOOP;
With each iteration of the loop, the sequence of statements gets executed,
then control reaches at the top of the loop. But a control structure like this
gets entrapped into infinite loop. To avoid this it is must to use the key word
EXIT and EXIT-WHEN.
5.9 Iterative Control 229
LOOP – EXIT
An EXIT statement within LOOP forces the loop to terminate unconditionally
and passes the control to next statements. The general syntax for this is as
follows:
LOOP
IF condition1 THEN
Sequence of statements1
EXIT;
ELSIF condition2 THEN
Sequence of statements2
EXIT
ELSE
Sequence of statements3
EXIT;
END IF;
END LOOP;
LOOP – EXIT WHEN
The EXIT-WHEN statement terminates a loop conditionally. When the EXIT
statement is encountered, the condition in the WHEN clause is evaluated.
If the condition is true, the loop terminates and control passes to the next
statement after the loop. The syntax for this is as follows:
LOOP
EXIT WHEN condition
Sequence of statements
END LOOP
Example
Figures 5.4 and 5.5 are also the example of LOOP – EXIT WHEN. Condition
used here is that the cursor does not return anything by using %NOTFOUND
attribute.
WHILE-LOOP
The WHILE statement with LOOP checks the condition. If it is true then
only the sequence of statements enclosed within the loop gets executed. Then
control resumes at the top of the loop and checks the condition again; if it is
true the sequence of statements enclosed within the loop gets executed. The
process is repeated till the condition is true. The control passes to the next
statement outside the loop for FALSE or NULL condition.
230 5 PL/SQL
Fig. 5.6. Example for FOR Loop
WHILE condition LOOP
Sequence of statements
END LOOP;
FOR-LOOP
FOR loops iterate over a specified range of integers. The range is part of
iteration scheme, which is enclosed by the keywords FOR and LOOP. A double
dot (..) serves as the range operator. The syntax is as follows:
FOR counter IN lower limit .. higher limit LOOP
sequence of statements
END LOOP;
The range is evaluated when the FOR loop is first entered and is never
re-evaluated. The sequence of statements is executed once for each integer
in the range. After every iteration, the loop counter is incremented.
Example
To find the sum of natural numbers up to 10, the following program can be
used as in Fig. 5.6.
5.10 Cursors 231
Sequential Control
The sequential control unconditionally passes the control to specified unique
label; it can be in the forward direction or in the backward direction. For
sequential control GOTO statement is used. Overuse of GOTO statement
may increase the complexity, thus as far as possible avoid the use of GOTO
statement.
The syntax is as follows:
GOTO label;
....... .
....... .
<a PL/SQL program opens a cursor to process rows returned by a query, and
then closes the cursor. Just as a file pointer marks the current position in an
open file, a cursor marks the current position in an active set.
After a cursor is declared and opened, the user can FETCH, UPDATE,
or DELETE the current row in the active set. The cursor can be CLOSED
to disable it and free up any allocated system resources. Three commands are
used to control the cursor – OPEN, FETCH, and CLOSE. First the cursor
is initialized with an OPEN statement, which identifies the active set. Then,
the FETCH statement is used to retrieve the first row. FETCH statement can
be executed repeatedly until all rows have been retrieved. When the last row
has been processed, the cursor can be released with the CLOSE statement.
Figure 5.10 shows the memory utilization by a cursor when each of these
statements is given.
5.11 Steps to Create a Cursor
Following are the steps to create a cursor:
5.11.1 Declare the Cursor
In PL/SQL a cursor, like a variable, is declared in the DECLARE section of
a PL/SQL block or subprogram. A cursor must be declared before it can be
236 5 PL/SQL
referenced in other statements. A cursor is defined in the declarative part by
naming it and specifying a SELECT query to define the active set.
CURSOR IS
SELECT...
The SELECT statement associated with a cursor declaration can reference
previously declared variables.
Declaring Parameterized Cursors
PL/SQL allows declaration of cursors that can accept input parameters which
can be used in the SELECT statement with WHERE clause to select specified
rows. Syntax to declare a parameterized cursor:
CURSOR [(parameter......)] IS
SELECT......
WHERE = parameter;
Parameter is an input parameter defined with the syntax:
[IN] [{:= | DEFAULT} value]
The formal parameters of a cursor must be IN parameters. As in the
example above, cursor parameters can be initialized to default values. That
way, different numbers of actual parameters can be passed to a cursor,
accepting or overriding the default values.
Moreover, new formal parameters can be added without having to change
every reference to the cursor. The scope of a cursor parameter is local only
to the cursor. A cursor parameter can be referenced only within the SELECT
statement associated with the cursor declaration. The values passed to the
cursor parameters are used by the SELECT statement when the cursor is
opened.
5.11.2 Open the Cursor
After declaration, the cursor is opened with an OPEN statement for processing
rows in the cursor. The SELECT statement associated with the cursor is
executed when the cursor is opened, and the active set associated with the
cursor is created.
The active set is defined when the cursor is declared, and is created when
cursor is opened.
The active set consists of all rows that meet the SELECT statement
criteria. Syntax of OPEN statement is as follows.
OPEN ;
5.11 Steps to Create a Cursor 237
5.11.3 Passing Parameters to Cursor
Parameters to a parameterized cursor can be passed when the cursor is opened.
For example, given the cursor declaration
CURSOR Mem detail (MType VARCHAR2) IS SELECT. . .
Any of the following statements opens the cursor.
OPEN Mem detail(‘L’);
OPEN Mem detail(Mem); where Mem is another variable.
Unless default values are to be accepted, each formal parameter in the
cursor declaration must have a corresponding actual parameter in the OPEN
statement. Formal parameters declared with a default value need not have
a corresponding actual parameter. They can simply assume their default
values when the OPEN statement is executed. The formal parameters of a
cursor must be IN parameters. Therefore, they cannot return values to actual
parameters. Each actual parameter must belong to a datatype compatible
with the datatype of its corresponding formal parameter.
5.11.4 Fetch Data from the Cursor
After a cursor has been opened, the SELECT statement associated with
the cursor is executed and the active set is created. To retrieve the rows
in the active set one row at a time, the rows must be fetched individually
from the cursor. After each FETCH statement, the cursor advances to the
next row in the active set and retrieves it. Syntax of FETCH is:
FETCH INTO , ....
where variable name is the name of a variable to which a column value is
assigned. For each column value returned by the query associated with the
cursor, there must be a corresponding variable in the INTO list. This variable
datatype must be compatible with the corresponding database column.
5.11.5 Close the Cursor
After processing the rows in the cursor, it is released with the CLOSE statement. To change the active set in a cursor or the values of the variables
referenced in the cursor SELECT statement, the cursor must be released with
CLOSE statement. Once a cursor is CLOSEd, it can be reOPENed. The
CLOSE statement disables the cursor, and the active set becomes undefined.
For example, to CLOSE Mem detail close statement will be:
CLOSE ;
238 5 PL/SQL
Example
Figures 5.4 and 5.5 show the example of declaring, opening, and fetching the
cursor called SALCUR.
Explicit Cursor Attributes
It is used to access useful information about the status of an explicit cursor.
Explicit cursors have the same set of cursor attributes %NOTFOUND,
%FOUND, %ROWCOUNT, and %ISOPEN. These attributes can be accessed
in PL/SQL statements only, not in SQL statements. Syntax to access an
explicit cursor attributes:
%
%Notfound
When a cursor is OPENed, the rows that satisfy the associated query are
identified and form the active set. Before the first fetch, %NOTFOUND
evaluates to NULL. Rows are FETCHed from the active set one at a time. If
the last fetch returned a row, %NOTFOUND evaluates to FALSE. If the last
fetch failed to return a row because the active set was empty, %NOTFOUND
evaluates to TRUE. FETCH is expected to fail eventually, so when that
happens, no exception is raised.
Example
Figures 5.4 and 5.5 show the example for this attribute. In this example, it is
used for checking whether all the rows have been fetched or not.
%Found
%FOUND is the logical opposite of %NOTFOUND. After an explicit cursor
is open but before the first fetch, %FOUND evaluates to NULL. Thereafter,
it evaluates to TRUE if the last fetch returned a row or to FALSE if no row
was returned. If a cursor is not open, referencing it with %FOUND raises
INVALID CURSOR exception.
Example
Figures 5.4 and 5.5 show the example for this attribute. In this example, it is
used for checking whether the cursor has been opened successfully or not.
%Rowcount
When you open a cursor, %ROWCOUNT is initialized to zero. Before the
first fetch, %ROWCOUNT returns a zero. Thereafter, it returns the number
of rows fetched so far. The number is incremented if the latest fetch returned
a row.
5.11 Steps to Create a Cursor 239
Example
Figures 5.8 and 5.9 show the example of this attribute where cursor updatcur
is used.
%Isopen
%ISOPEN evaluates to TRUE if the cursor is open; otherwise, %ISOPEN
evaluates to FALSE.
Example
Figures 5.11 and 5.12 show the example of this attribute where cursor updatcur is used.
Fig. 5.11. Example of FOR UPDATE clause
240 5 PL/SQL
Fig. 5.12. FOR UPDATE clause execution
Using FOR UPDATE and CURRENT
The FOR UPDATE clause is used to specify that the rows in the active set of a
cursor are to be locked for modification. Locking allows the rows in the active
set to be modified exclusively by your program. This protects simultaneous
modifications until update by one transaction is complete.
CURSOR IS SELECT [.....] FROM.....
FOR UPDATE [OF ......];
FOR UPDATE specifies that the rows of the active set are to be exclusively
locked when the cursor is opened and specifies the column names that can be
updated. The FOR UPDATE clause must be used in the cursor declaration
statement whenever UPDATE or DELETE are to be used after the rows are
FETCHed from a cursor.
Syntax of CURRENT clause with UPDATE statement is:
UPDATE SET = expression [.....]
WHERE CURRENT OF ;
Syntax of CURRENT OF Clause with DELETE Statement is:
DELETE table name WHERE CURRENT OF cursor name;
5.11 Steps to Create a Cursor 241
Example
Figures 5.11 and 5.12 show this example where a row of id E101 is locked for
updation and its name of the Employee is changed to Karthikeyan.
Cursor FOR Loop
PL/SQL provides FOR loop to manage cursors effectively in situations where
the rows in the active set of cursor are to be repeatedly processed in a looping
manner. A cursor FOR loop simplifies all aspects of processing a cursor. Cursor
FOR loop can be used 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 active
set into fields in the record, and closes the cursor when all rows have been
processed. Syntax to declare and process a cursor in a cursor FOR loop is:
FOR IN LOOP
.........
END LOOP;
where record name is the cursor FOR loop index implicitly declared as
a record of type %ROWTYPE. Cursor is assumed to be declared in the
DECLARE section. In the FOR loop declaration, the FOR loop index is
uniquely named and implicitly declared as a record of type %ROWTYPE.
This RECORD variable consists of columns referenced in the cursor SELECT
statement.
In the FOR loop, the cursor is implicitly opened for processing. No explicit
OPEN statement is required. Inside the FOR loop, the column values for each
row in the active set can be referenced by the FOR loop index with dot notation in any PL/SQL or SQL statement. Before any iteration of the FOR loop,
PL/SQL fetches into the implicitly declared record, which is equivalent to a
record declared explicitly. At the end of the active set, the FOR loop implicitly
closes the cursor and exits the FOR loop. No explicit CLOSE statement is
required. A COMMIT statement is still required to complete the operation.
We can pass parameters to a cursor used in a cursor FOR loop. The record
is defined only inside the loop. We cannot refer to its fields outside the loop.
The sequence of statements inside the loop is executed once for each row that
satisfies the query associated with the cursor. On leaving the loop, the cursor
is closed automatically. This is true even if an EXIT or GOTO statement is
used to leave the loop prematurely or if an exception is raised inside the loop.
Example
Figures 5.13 and 5.14 show the example of cursor execution using FOR loop.
242 5 PL/SQL
Fig. 5.13. Cursor using FOR loop
Fig. 5.14. Cursor using FOR loop execution
5.12 Procedure 243
5.12 Procedure
A procedure is a subprogram that performs some specific task, and stored
in the data dictionary. A procedure must have a name, so that it can be
invoked or called by any PL/SQL program that appears within an application.
Procedures can take parameters from the calling program and perform the
specific task. Before the procedure or function is stored, the Oracle engine
parses and compiles the procedure or function. When a procedure is created,
the Oracle automatically performs the following steps:
1. Compiles the procedure
2. Stores the procedure in the data dictionary
If an error occurs during creation of procedure, Oracle displays a message
that procedure is created with compilation errors, but it does not display the
errors. To see the errors following statement is used:
SELECT * FROM user errors;
When the function is invoked, the Oracle loads the compiled procedure in
the memory area called system global area (SGA). Once loaded in the SGA
other users can also access the same procedure provided they have granted
permission for this.
Benefits of Procedures and Functions
Stored procedures and functions have many benefits in addition to modularizing application development.
1. It modifies one routine to affect multiple applications.
2. It modifies one routine to eliminate duplicate testing.
3. It ensures that related actions are performed together, or not at all, by
doing the activity through a single path.
4. It avoids PL/SQL parsing at runtime by parsing at compile time.
5. It reduces the number of calls to the database and database network traffic
by bundling the commands.
Defining and Creating Procedures
A procedure consists of two parts: specification and body. The specification
starts with keyword PROCEDURE and ends with parameter list or procedure
name. The procedures may accept parameters or may not. Procedures that
do not accept parameters are written parentheses.
The procedure body starts with the keyword IS and ends with keyword
END. The procedure body is further subdivided into three parts:
1. Declarative part which consists of local declarations placed between keywords IS and BEGIN.
244 5 PL/SQL
2. Executable part, which consists of actual logic of the procedure, included
between keywords BEGIN and EXCEPTION. At least one executable
statement is a must in the executable portion of a procedure. Even a
single NULL statement will do the job.
3. Error/Exception handling part, an optional part placed between EXCEPTION and END.
The syntax for creating a procedure is follows:
CREATE OR REPLACE PROCEDURE [schema.] package name
[(argument {IN, OUT, IN OUT} data type,.........)] {IS, AS}
[local variable declarations]
BEGIN
executable statements
EXCEPTION
exception handlers
END [procedure name];
Create: Creates a new procedure, if a procedure of same name already
exists, it gives an error.
Replace: Creates a procedure, if a procedure of same name already exists,
it replace the older one by the new procedure definition.
Schema: If the schema is not specified then procedure is created in user’s
current schema.
Figure5.15 shows the procedure to raise the salary of the employee. The
name of the procedure is raise sal.
Fig. 5.15. Procedure creation
5.12 Procedure 245
Argument: It is the name of the argument to the procedure.
IN: Specifies that a value for the argument must be specified when calling
the procedure.
OUT: Specifies that the procedure pass a value for this argument back to
its calling environment after execution.
IN OUT: Specifies that a value for the argument must be specified when
calling the procedure and that the procedure passes a value for this argument
back to its calling environment after execution. If no value is specified then it
takes the default value IN.
Datatype: It is the unconstrained datatype of an argument. It supports
any data type supported by PL/SQL. No constraints like size constraints or
NOT NULL constraints can be imposed on the data type. However, you can
put on the size constraint indirectly.
Example
To raise the salary of an employee, we can write a procedure as follows.
Declaring Subprograms
Subprograms can be declared inside any valid PL/SQL block. The only thing
to be kept in mind is the declaration of programs must be the last part of
declarative section of any PL/SQL block; all other declarations should precede
the subprogram declarations.
Like any other programming language, PL/SQL also requires that any
identifier that is used in PL/SQL program should be declared first before its
use. To avoid problems arising due to such malpractices, forward declarations
are used.
System and Object Privileges for Procedures
The creator of a procedure must have CREATE PROCEDURE system privilege in his own schema, if the procedure being created refers to his own schema.
To create a procedure in other’s schema, the creator must have CREATE ANY
PROCEDURE system privilege.
To create a procedure without errors (compiling it without errors), the
creator of procedure must have required privileges to all the objects he refer
to from his procedure. It must be noted that the owner will not get the required
privileges through roles, he must be granted those privileges explicitly.
As soon as the privileges granted to the owner of procedure change, the
procedure must be reauthenticated in order to bring into picture the new
privileges of the owner. If a necessary privilege to an object referenced by a
procedure is revoked/withdrawn from the owner of the procedure, the procedure cannot be run.
246 5 PL/SQL
To EXECUTE any procedure a user must have EXECUTE ANY PROCEDURE privilege. With this privilege he can execute a procedure which belong
to some other user.
Executing/Invoking a Procedure
The syntax used to execute a procedure depends on the environment from
which the procedure is being called. From within SQLPLUS, a procedure can
be executed by using the EXECUTE command, followed by the procedure
name. Any arguments to be passed to the procedure must be enclosed in
parentheses following the procedure name.
Example
Figure 5.16 shows the execution of procedure raise sal.
Removing a Procedure
To remove a procedure completely from the database, following command is
used:
DROP PROCEDURE ;
Fig. 5.16. Procedure execution
5.13 Function 247
Fig. 5.17. Dropping of a procedure
To remove a procedure, one must own the procedure he is dropping or he
must have DROP ANY PROCEDURE privilege.
Example
To drop a procedure raise sal. Figure 5.17 indicate the dropping of the procedure raise sal.
5.13 Function
A Function is similar to procedure except that it must return one and only
one value to the calling program. Besides this, a function can be used as part
of SQL expression, whereas the procedure cannot.
Difference Between Function and Procedure
Before we look at functions in deep, let us first discuss the major differences
between a function and a procedure.
1. A procedure never returns a value to the calling portion of code, whereas
a function returns exactly one value to the calling program.
2. As functions are capable of returning a value, they can be used as elements
of SQL expressions, whereas the procedures cannot. However, user-defined
functions cannot be used in CHECK or DEFAULT constraints and cannot
manipulate database values, to obey function purity rules.
3. It is mandatory for a function to have at least one RETURN statement,
whereas for procedures there is no restriction. A procedure may have a
RETURN statement or may not. In case of procedures with RETURN
statement, simply the control of execution is transferred back to the
portion of code that called the procedure.
248 5 PL/SQL
The exact syntax for defining a function is given below:
CREATE OR REPLACE FUNCTION [schema.] functionname
[(argument IN datatype, . . . .)] RETURN datatype {IS,AS}
[local variable declarations];
BEGIN
executable statements;
EXCEPTION
exception handlers;
END [functionname];
where RETURN datatype is the datatype of the function’s return value. It
can be any PL/SQL datatype.
Thus a function has two parts: function specification and function body.
The function specification begins with keyword FUNCTION and ends with
RETURN clause which indicates the datatype of the value returned by the
function. Function body is enclosed between the keywords IS and END. Sometimes END is followed by function name, but this is optional. Like procedure,
a function body also is composed of three parts: declarative part, executable
part, and an optional error/exception handling part.
At least one return statement is a must in a function; otherwise PL/SQL
raises PROGRAM ERROR exception at the run time. A function can have
multiple return statements, but can return only one value. In procedures,
return statement cannot contain any expression, it simply returns control
back to the calling code. However in functions, return statement must contain
an expression, which is evaluated and sent to the calling code.
Example
To get a salary of an employee, Fig. 5.18 shows a function.
Figure5.19 shows that how the calling of a function is different from
procedure calling.
Purity of a Function
For a function to be eligible for being called in SQL statements, it must satisfy
the following requirements, which are known as Purity Rules.
1. When called from a SELECT statement or a parallelized INSERT,
UPDATE, or DELETE statement, the function cannot modify any
database tables.
2. When called from an INSERT, UPDATE, or DELETE statement, the
function cannot query or modify any database tables modified by that
statement.
5.13 Function 249
Fig. 5.18. Function creation
Fig. 5.19. Function execution
3. When called from a SELECT, INSERT, UPDATE, or DELETE statement, the function cannot execute SQL transaction control statements
(such as COMMIT), session control statements (such as SET ROLE), or
system control statements (such as ALTER SYSTEM). Also, it cannot
250 5 PL/SQL
execute DDL statements (such as CREATE) because they are followed by
an automatic commit.
If any of the above rules is violated, the function is said to be not following
the Purity Rules and the program using such functions receives run time error.
Removing a Function
To remove a function, use following command:
DROP FUNCTION ;
Example
Figure 5.20 illustrates the dropping of a function.
To remove a function, one must own the function to be dropped or he
must have DROP ANY FUNCTION privilege.
Parameters
Parameters are the link between a subprogram code and the code calling the
subprogram. Lot depends on how the parameters are passed to a subprogram.
Hence it is absolutely necessary to know more about parameters, their modes,
their default values, and how subprograms can be called without passing all
the parameters.
Parameter Modes
Parameter modes define the behavior of formal parameters of subprograms.
There are three types of parameter modes: IN, OUT, IN/OUT.
There are many advantages of database management system. Some of the advantages are listed later: 1. Centralized data management. 2. Data Independence. 3. System Integration. Centralized Data Management In DBMS all files are integrated into one system thus reducing redundancies and making data management more efficient. 1.11.2 Data Independence Data independence means that programs are isolated from changes in the way the data are structured and stored. In a database system, the database management system provides the interface between the application programs and the data. Physical data independence means the applications need not worry about how the data are physically structured and stored. Applications should work with a logical data model and declarative query language. If major changes were to be made to the data, the application programs may need to be rewritten. When changes are made to the data representation, the data maintained by the DBMS is changed but the DBMS contin...
2.1 Introduction Peter Chen first proposed modeling databases using a graphical technique that humans can relate to easily. Humans can easily perceive entities and their characteristics in the real world and represent any relationship with one another. The objective of modeling graphically is even more profound than simply representing these entities and relationship. The database designer can use tools to model these entities and their relationships and then generate database vendor-specific schema automatically. Entity–Relationship (ER) model gives the conceptual model of the world to be represented in the database. ER Model is based on a perception of a real world that consists of collection of basic objects called entities and relationships among these objects. The main motivation for defining the ER model is to provide a high level model for conceptual database design, which acts as an intermediate stage prior to mapping the enterprise being modeled onto a conceptual level. T...
Structured Query Language SQL stands for “Structured Query Language.” The Structured Query Language is a relational database language. By itself, SQL does not make a DBMS. SQL is a medium which is used to communicate to the DBMS. SQL commands consist of English-like statements which are used to query, insert, update, and delete data. English-like statements mean that SQL commands resemble English language sentences in their construction and use and therefore are easy to learn and understand. SQL is referred to as nonprocedural database language. Here nonprocedural means that, when we want to retrieve data from the database it is enough to tell SQL what data to be retrieved, rather than how to retrieve it. The DBMS will take care of locating the information in the database. Commercial database management systems allow SQL to be used in two distinct ways. First, SQL commands can be typed at the command line directly. The DBMS interprets and processes the SQL commands immediately, an...
Comments
Post a Comment