Oracle CTS

(1)

Oracle 9i

Version: ORACLE/HANDOUT/0704/1.0

Date: 27-07-04

Cognizant Technology Solutions 500 Glen Pointe Center West

Teaneck, NJ 07666 Ph: 201-801-0233 www.cognizant.com

(2)

2

3

Understand Read Consistency... 22

SUMMARY ... 22

Test Your Understanding ... 23

Chapter 2: PL/SQL Fundamentals... 24 Learning Objectives ... 24 PL/SQL Features ... 24 PL/SQL Block Structure... 24 Identifiers ... 25 Variables ... 25 Data Types ... 25 Composite Types ... 25 PL/SQL Statements ... 26

Oracle Supplied Packages... 26

Expressions and Operators ... 28

Conditional and Loop Constructs ... 29

SUMMARY ... 31

Test your Understanding ... 31

Chapter 3: Cursors and Exceptions... 32

Learning Objectives ... 32 Understand Cursors ... 32 Fetch Cursor... 33 Close Cursor ... 34 Understanding Exceptions ... 38 Predefined Exceptions... 38

User Defined Exception ... 39

PRAGMA EXCEPTION_INIT ... 40

RAISE_APPLICATION_ERROR ... 40

SUMMARY ... 40

Chapter 4: Procedures, Functions and Packages... 42

Learning Objectives ... 42

Understand Procedures ... 42

(4)

4

Passing Parameters ... 43

Creating Functions ... 44

Implementing Packages ... 45

SUMMARY ... 46

Chapter 5: Triggers... 47

Appreciate Database Triggers... 47

Components of Triggers ... 47

Types of Triggers ... 48

Create Triggers ... 48

Order of Trigger Firing ... 50

SUMMARY ... 50

Chapter 6: PL/SQL Collection ... 51

Understand Collections ... 51

Types of Collection... 51

Define and Declare Collection ... 52

Use Collection Methods ... 53

Learn Multi Level Collection ... 54

SUMMARY ... 54

Chapter 7: Dynamic SQL... 55

Understand Dynamic SQL ... 55

Use of Dynamic SQL... 55

Execute Dynamic SQL ... 55

Understand Bulk Operations ... 57

Understand FOR ALL ... 58

Understand BULK COLLECT... 59

Appreciate the difference between Soft Parse and Hard Parse ... 59

(5)

5

REFERENCES ... 61

WEBSITES... 61

BOOKS ... 61

(6)

6 Introduction

About this Module

The module tells the student about the basics of PL/SQL statements in Oracle.

Target Audience

Entry Level Trainees

Module Objectives

Upon completion of this module, the student would be able to:

??Understand and write PL/SQL statements ??Use DDL, DML and TCL statements

??Write queries using all clauses and fetch data from one or more tables or views ??Write queries using all kinds of Joins

??Write simple and complex sub queries ??Write queries in ANSI syntax

??Write Static and Dynamic SQL statements

??Create procedures, functions, packages and triggers ??Use static and dynamic cursors in PL/SQL blocks

Pre-requisite

The student needs to have an idea of the following: ??General Programming and Logic

??Structured Query Language ??Relational DBMS

(7)

7

Chapter 1 :

ORACLE SQL

Learning Objectives

Upon Completion of this chapter the student shall be able to: ??Understand & write SQL statements

Understand the features of SQL

What is SQL?

The Sequential Query Language (SQL) is a set of statements with which all programs and users access data in an Oracle Database.

Who Developed SQL and When?

IBM developed the SQL, popularly pronounced as “SEQUEL” and implemented it in 1979. Actually, in doing so, IBM implemented the model on Relational Database Management Systems (RDBMS) developed by Dr. E.F. Codd. Today, SQL is the most popular RDBMS language.

What can SQL do?

With SQL*Plus, you can execute SQL commands and PL/SQL blocks, additionally you can perform the following tasks:

Enter, edit, store, retrieve and run SQL commands and PL/SQL blocks

Format, perform calculations on, store, print and create web output of query results List column definitions for any table

Access and copy data between SQL databases

Send messages to and accept responses from an end user Perform database administration

**Learn SQL*Plus**

Logging in:

??Ensure that Oracle is installed in your computer

??Type SQLPLUS at the command prompt and press Enter ??SQL Plus asks for your username and password

??Enter your Username and Password

??SQL prompt appears indicating that it is prepared to work

Exiting SQL Plus

To exit SQL Plus, simply type Exit at the command Prompt

(8)

8

To tell SQL Plus what to do, simply enter the command you wish to use. Commands can either be in Lower or Upper case, for the sake of clarity, all table names, column names, and commands in this Guide appear in capital letters.

You can enter three kinds of commands at the command prompt:

??SQL commands, for working with information in the database ??PL/SQL blocks, also for working with information in the database

??SQL*Plus commands, for formatting query results, setting options, and editing and storing SQL commands and PL/SQL blocks

Getting Help

Type HELP at the command prompt followed by the name of the command to get online help for SQL*Plus commands. For example:

SQL>HELP ACCEPT Executing Commands

After you enter the command and direct SQL*Plus to execute it, SQL*Plus processes the command and re-displays the command prompt, indicating that you can enter another command.

Running SQL Commands

Entering a SQL Command

In this example, you will enter and execute a SQL command to display the

employee number, name, job, and salary of each employee in the sample table EMP.

1. At the command prompt, enter the first line of the command:

SQL> SELECT EMPNO, ENAME, JOB, SAL

2. SQL*Plus will display a "2", the prompt for the second line. Enter the second line of the command:

2 FROM EMP WHERE SAL < 2500;

The above command shall display the salaries below Rs. 2500 for all employees

Dividing a SQL Command into Separate Lines

You can divide your SQL command into separate lines at any points you wish, as long as individual words are not split between lines. The point has been shown in the following example:

(9)

9

SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL < 2500;

Can also be entered in the following form:

SQL> SELECT

2 EMPNO, ENAME, JOB, SAL 3 FROM EMP

4 WHERE SAL < 2500;

Ending an SQL Command

You can end an SQL command in one of three ways: With a semicolon (;)

With a slash (/) on a line by itself With a blank line

Creating Stored Procedures

Stored procedures are PL/SQL functions, packages, or procedures. You use SQL CREATE commands to create stored procedures; The following SQL CREATE commands are used to create stored procedures:

CREATE FUNCTION CREATE LIBRARY CREATE PACKAGE CREATE PACKAGE BODY CREATE PROCEDURE CREATE TRIGGER CREATE TYPE

Learn the rules for writing SQL statements

SQL statements may be broken down into multiple lines.

A SQL statement may be terminated with a semi-colon (;) or a forward slash (/). The forward slash must be given in a new line after the SQL statement.

For re-executing the last SQL statement in the buffer use a forward slash instead of rewriting it. SQL commands may be on one or many lines

Clauses are usually placed on separate lines Tabulation can be used

Command words cannot be split across lines

(10)

10

An SQL command is entered at the SQL prompt and the subsequent lines are numbered. This is called SQL buffer

Use Select Statements

What is the SELECT statement?

The SELECT statement is an SQL statement that specifies which rows and columns to fetch from one or more tables or views.

What does the Select statement do?

The SELECT statement is very helpful in finding filtered records from a database. If the database happens to be very large, the SELECT statement, used with the right parameters, can work wonders in finding the right information.

An example of the usage of SELECT statement

The following SELECT statement returns an employee’s name, job title, and salary from the emp database table:

SELECT ename, job, sal INTO my_ename, my_job, my_sal FROM emp WHERE empno = my_empno;

In the following example, the SQL engine loads the entire empno and ename database columns into nested tables before returning the tables to the PL/SQL:

DECLARE

TYPE NumTab IS TABLE OF emp.empno%TYPE; TYPE NameTab IS TABLE OF emp.ename%TYPE; enums NumTab; -- no need to initialize

names NameTab; BEGIN

SELECT empno, ename BULK COLLECT INTO enums, names FROM emp; ...

END;

Handle NULL Values

What is a Null value?

The NULL value means inaction or nothing. It just passes control on to the next statement.

What does the NULL value do?

Although NULL value means inaction, 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.

An example of the usage of NULL value

In the following example, the NULL statement shows that no action is taken for unnamed exceptions:

EXCEPTION

WHEN ZERO_DIVIDE THEN ROLLBACK;

(11)

11

WHEN VALUE_ERROR THEN

INSERT INTO errors VALUES... COMMIT;

WHEN OTHERS THEN NULL;

END;

Use Column Alias

What is a Column Alias?

In SQL, alias is a temporary name assigned to a table, view, column, or value within a SQL statement used to refer to that item later in the same statement or in associated

SQL*Plus commands.

What does the Column Alias do?

Column Alias is usually used for distinguishing two column names. If two or more tables have some column names in common, you must qualify column names with names of tables. It is always a good idea to qualify table and column references explicitly.

Eliminate Duplicate Rows

Duplicate Rows can be removed using the ROWID field. The ROWID is guaranteed unique. There are many variations on this theme, but the logic is to delete all but one record for each key value. The following example shows the deletion of Duplicate Rows:

delete from EMP E where not E.ROWID = (

select min(F.ROWID)

from EMP F

where F.EMP_ID = E.EMP_ID );

Use Where Clause

Following are the features of a Where Clause:

??Used for applying conditions to filter rows.

??Appears immediately after the SELECT and FROM clause. ??Alias names are not allowed.

??Works on row levels.

Syntax:

SELECT <COLUMN NAME (S)/* FROM EMP

WHERE <CONDITION>

The following four elements can be used with the WHERE clause: ??Column name

??Comparison Operators ??Constants

(12)

12

??List of values

Example:

SELECT EMPNO, ENAME, SAL FROM EMP

WHERE SAL > 2500;

Use Operators

What is an Operator?

An operator manipulates individual data items and returns a result. The data items are called operands or arguments. Operators are represented by special characters or by keywords. For example, an asterisk represents the multiplication operator

(*) and the operator that tests for nulls is represented by the keywords IS NULL.

There are various kinds of Operators in PL/SQL:

Unary:

A unary operator operates on only one operand. A unary operator typically appears with its operand in this format:

operator operand Binary:

A binary operator operates on two operands. A binary operator appears with its operands in this format:

operand1 operator operand2 Arithmetic Operators

You can use an arithmetic operator in an expression to negate, add, subtract, multiply, and divide numeric values. The result of the operation is also a numeric value. Some of these operators are also used in date arithmetic. An example of arithmetic operators is given in the following:

SELECT * FROM orders WHERE qtysold = -1; SELECT * FROM emp WHERE -sal < 0;

(13)

13

The concatenation operator manipulates character strings. The following example shows the use of Concatenation operators:

SELECT ’Name is ’ || ename FROM emp;

This selects character strings with ‘Name is’ from the database emp.

Comparison Operators

Comparison operators compare one expression with another. The result of such a comparison can be TRUE, FALSE, or UNKNOWN. Example of a Comparison Operator is given below:

SELECT * FROM emp WHERE sal > 1500;

The Comparison operator above shall select all salaries that are higher than 1500 in the emp database.

Logical Operators

A logical operator combines the results of two component conditions to produce a

single result based on them or to invert the result of a single condition. An example of a logical operator is given below:

SELECT * FROM emp

WHERE job = ’CLERK’ AND deptno = 10;

Set Operators

Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries.

Sort Rows

The ORDER BY Clause

ORDER BY clause is used to arrange the output Default order is ascending (ASC)

For arranging in descending order, DESC is used

ORDER BY should be the last clause in a SELECT statement Can be arranged in multiple columns

(14)

14

SELECT EMPNO, ENAME, SAL, DEPTNO

FROM EMP

ORDER BY DEPTNO, SAL DESC; DESC specifies a descending order

ASC specifies an ascending order. This is also the default order –Numeric Values Lowest First

–Date Values earliest first –Character Values alphabetically

Example

select ename, job, hiredate from emp

order by hiredate desc;

Ordering By Many Columns

In the order by clause, specify the columns to order by, separated by commas. If any or all are to be reversed, specify DESC after any or each column

Null values are displayed last for ascending sequences, and are reported first when rows are sorted in descending order.

Example

select ename, job, deptno from emp

order by deptno, sal desc;

Understand Functions

Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format of their arguments. This format allows them to operate on zero, one, two, or more arguments. Basically, there are two kinds of Functions:

SQL functions

SQL functions are built into Oracle and are available for use in various appropriate SQL statements.

User Defined Functions

You can write user-defined functions in PL/SQL or Java to provide functionality that is not available in SQL or SQL functions. User functions can appear in a SQL statement anywhere SQL functions can appear, that is, wherever an expression can occur.

For example, user functions can be used in the following:

(15)

15

The condition of a WHERE clause

CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses The VALUES clause of an INSERT statement

The SET clause of an UPDATE statement

Use Single Row Functions

Single Row Functions are of the following types: ??Character ??Number ??Date ??Conversion ??List ??General Functions

Can be used with the following clauses: ??SELECT

??WHERE ??ORDER BY

The DUAL Table features:

??System generated table.

??Contains one row and one column only. ??Used as a dummy table to display information.

Example:

SELECT LOWER (‘ORACLE’) FROM DUAL;

Single row functions and data type conversions are an area where the SQL used be different DBMS vendors varies greatly.

Thus, the operations described here will work only for ORACLE databases.

We will describe some basic ORACLE functions to:

Perform specific computational operations on numeric, character, and/or data fields, or to Convert data from one type to another

(16)

16 Use Aggregate Functions

Operates on sets of rows to give one result per group ??SUM

??AVG ??MAX ??MIN ??COUNT (*)

??COUNT (column name)

All group functions except COUNT (*) ignore NULL MAX and MIN functions can be used for any datatype

GROUP BY Clause

??Query results can be summarized using the GROUP BY clause. ??Multiple columns can be used in GROUP BY clause.

Following can be used with GROUP BY clause: ??Constant

??Function without parameters (SYSDATE, USER) ??Group functions

Example

SELECT DEPTNO, SUM (SAL) FROM EMP

GROUP BY DEPTNO;

The HAVING Clause

??Used for restricting groups

Sequence:

??Rows are first grouped ??The group function is applied

??Groups matching the HAVING clause is then displayed

Example:

SELECT DEPTNO, SUM (SAL) FROM EMP

GROUP BY DEPTNO

(17)

17 Understand Joins

A join is a query that combines rows from two or more tables, views, or materialized views ("snapshots"). Oracle performs a join whenever multiple tables appear in the query’s FROM clause. The query’s select list can select any columns from any of these tables. If any two of these tables have a column name in common, you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

Understand Equi-Joins

What is an Equi-join?

??Is defined as a join in which more than one tables are joined together with the help of a common column that exists in both the tables

??In this type of join the relationship between the tables are specified in the where clause, by using an equal (=) symbol

??EQUI Joins are also called simple joins and inner joins

Example

SELECT EMPNO, ENAME, DNAME FROM EMP, DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO

Removing Ambiguity

??When more than one column joined, and if one column exists in both the tables and that column needs to be displayed in the output, it must be preceded by the table name followed by a period (.)

??Otherwise an ambiguity will occur because SQL will not understand from which table the said column to display

Example

SELECT DEPT.DEPTNO, EMPNO FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO

Table Aliases

??Should not be more than 30 characters long, but the shorter it is the better.

??The table alias should be substituted for the table name throughout the SELECT statement.

??Valid only for the current SELECT statement. ??Should be meaningful.

Example:

(18)

18

FROM EMP E, DEPT D

WHERE D.DEPTNO = E.DEPTNO;

Understand Outer-Joins

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join condition. A simple join does not return such rows. To write a query that performs an outer join of tables A and B and returns all rows from A, apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, Oracle returns NULL for any select list expressions containing columns of B.

Outer join queries are subject to the following rules and restrictions:

??The (+) operator can appear only in the WHERE clause or, in the context of left correlation (that is, when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view.

??If A and B are joined by multiple join conditions, you must use the (+) operator in all of these conditions. If you do not, Oracle will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.

?? The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain a column marked with the (+) operator.

Understand Self Joins

??When one row of one table is to be compared with another row of the same table, then self-join is used.

??Logical tables need to be created from the same table temporarily.

Example:

SELECT E.ENAME, E.SAL, F.ENAME, F.SAL FROM EMP E, EMP F

WHERE E.SAL = F.SAL AND E.EMPNO < F.EMPNO;

Learn Sub Query

??A sub query is a select statement that is nested within another select statement ??The result of the inner one is passed as an argument to the outer one

Sub queries may be:

??Single Row Sub query (single row comparison operators can be used e.g. =, <, >, <=, >= etc.)

(19)

19

??Multiple row sub query (Multi row comparison operator e.g. IN,SOME/ANY or ALL

operators)

Example

SELECT EMPNO, ENAME, SAL FROM EMP

WHERE SAL=(SELECT MAX (SAL) FROM EMP);

EXISTS Operator

??Exists returns a value TRUE if the sub-query that follows it returns at least one row

Example

SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE EXISTS (SELECT COUNT (*) FROM EMP WHERE DEPTNO=30

HAVING COUNT (*) > 5) ORDER BY DEPTNO;

ANY Operator

??Compares with any of the values returned by the inner query

Example

SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL > ANY

(SELECT SAL FROM EMP WHERE DEPTNO = 10);

ALL Operators

??Compares with all the values returned by the inner query

Example

SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL > ALL

(SELECT SAL FROM EMP WHERE DEPTNO = 10);

Learn Co Related Sub Query

??Each execution of the outer query will ensure the inner query to be executed for all of its values

Example

Select ename, deptno from dept

(20)

20

from emp

where dept.deptno= emp.deptno);

Steps of execution of Correlated Sub queries ??Get candidate row (fetched by outer query) ?? Execute Inner query using candidate row’s value.

?? Use Value(s) resulting from inner query to qualify or disqualify candidate. ?? Repeat until no candidate row remains

Understand DDL Statements

??Data Definition Language is a set of SQL commands used to create, modify and delete database structures

??The CREATE TABLE is used to create and define a new relational table

??The DROP TABLE is used to delete a table and all data within the specified table ??The ALTER TABLE is used to change an existing table definition

??Cannot delete an existing column

??Cannot change an existing table constraint definition

??The TRUNCATE TABLE is used to delete all the rows within the specified table

Create Tables

Rules for naming a table

??The name must begin with a letter, A-Z or a-z.

??May contain letters, numeric and special character _(underscore). The $ and # are also legal but discouraged;

??The name is the same whether upper or lower case letters are used ??It may be up to 30 char length

??The name must not duplicate the name of any other object in your account. ??The name must not be a sequel reserved word

Data type Description

VARCHAR2(W) Variable length char value up to width w CHAR(W) Fixed length char values

NUMBER Floating point number value NUMBER(W) Integer numbers of precision w

NUMBER(W,S) Numbers with a precision w and scale s

DATE date values

LONG store variable-length character data with a maximum length of 2^31 - 1

(2,147,483,647) bytes

RAW Equivalent to varchar2 but stores binary data LONG RAW Equivalent to Long but stores binary data

(21)

21

Example

CREATE TABLE EMP

(EMPNO NUMBER (4) CONSTTRAINT EMP_PRIM PRIMARY KEY, ENAME VARCHAR2 (10) CONSTRAINT ENAME_CONS

CHECK(ENAME=UPPER(ENAME)), JOB VARCHAR2 (10),

MGR NUMBER (4) CONSTRAINT EMP_MGR

REFERENCES EMP(EMPNO), HIREDATE DATE DEFAULT SYSDATE

SAL NUMVER(7,2) CONSTRAINT SAL_CONS NOT NULL, COMM NUMBER(7,2),

DEPTNO NUMBER(2) CONSTRAINT DEPTNO_CONS NOT NULL, CONSTRAINT EMP_DEPT FOREIGN KEY(DEPTNO)

REFERENCES DEPT(DEPTNO))

Understand DML statements

INSERT

Use INSERT INTO to insert a new row in table

UPDATE

Use UPDATE to update a single row/multiple rows in table

DELETE

Use DELETE to delete a single row/multiple rows from table

Understand Implicit Transaction Processing

An automatic COMMIT occurs when: ??DDL statement is issued ??DCL statement is issued

??Normal exit from SQL*PLUS without issuing explicit COMMIT or ROLLBACK

An automatic ROLLBACK occurs under an abnormal termination of SQL *PLUS or due to system failure

(22)

22

??The previous state of data can be recovered.

??The current user can review the results of the DML operations by using the SELECT statement.

??Other users cannot view the results of the DML statements by the current user.

??The affected rows are locked; other users cannot change the data within the affected row.

??Data changes are made permanent in the database. ??The previous state of data is permanently lost. ??All users can view the results.

??All save points are erased.

State of data after ROLLBACK

??Discard all pending changes by suing the ROLLBACK statement. ??Data changes are undone

??Previous state of the data is released.

Understand Read Consistency

??Read Consistency guarantees a consistent view of the data at all times

??Changes made by one user do not conflict with changes made by another user. ??Ensures that:

??Readers do not wait for readers. ??Write do not wait for writers.

??Each users sees the data as it existed at the last COMMIT, before DML operation is started

SUMMARY

??SQL is an English-like, non-procedural language

??SQL is used in creating, modifying and deleting database objects ??SQL is used in inserting, updating and deleting rows in a table

??SQL is used in controlling access to the database and database objects

??SQL *PLUS is an Oracle tool. It recognizes and submits SQL statements residing in the SQL buffer (memory area) for execution to the Oracle server

??SQL *PLUS has its own sets of commands

??SELECT Statement is used to retrieve data from the database either selectively or collectively

??The WHERE clause is used to apply conditions to filter rows

??SQL provides a set of built-in functions, namely Single Row Functions and Aggregate functions

??Data from multiple tables can be queried using SQL Join. They are of three types, Equi Join, Outer Join and Self Join

??Sub-queries is a select statement that is nested within another select statement ??Oracle provides DDL statements for Creating, Altering, Dropping and Truncating

Tables

(23)

23

??COMMIT makes permanent any database changes you made during the current

transaction. Until you commit your changes, other users cannot see them.

??ROLLBACK ends the current transaction and undoes any changes made since the transaction began

Test Your Understanding

??Display the number, name and job of those employees who are hired between February 1, 1981 and May 1, 1981.

??Find out the names of the employees who has worked for more than 20 years ??Find out the day of your date of birth

??Display the total salary earned by the employees for each job type. Display only those job types for which the total salary exceeds 5000

(24)

24

Chapter 2 :

PL/SQL Fundamentals

Learning Objectives

Upon completion of this chapter, you will be able to ??Understand and write PL/SQL Statements

PL/SQL Features

PL/SQL stands for Procedural Language/SQL

PL/SQL extends SQL by adding constructs found in other procedural languages ??Variables and types

??Control Structures (IF-THEN-ELSE) ??Procedures and Functions

??Object types and methods

PL/SQL Block Structure

??PL/SQL is a block-structured language

??PL/SQL program is made up of a series of statements. A statement is terminated with a semicolon (;), not with the physical end of a line

??It has three parts, Declarative part, an Executable part and an Exception Handling part

Syntax DECLARE

/*Declarative Section - PL/SQL variables, cursors and types */

BEGIN

/* Executable section - procedural and SQL statements */ EXCEPTION

/* Exception handling section - error handling statements */ END; Anonymous Blocks Declare /* variables*/ Begin /*SQL statements */ Exception /* Error handling */ End;

(25)

25 Identifiers

??Must start with a letter ??Up to 30 characters in length

??May include a $ (dollar sign), _ (underscore), and # (pound sign) ??Cannot contain embedded spaces

Variables

??Variables are memory locations, which can store data values

??Information from database can be assigned to a variable, or the contents of a variable can be inserted into the database

??The variables are declared in the declarative section of the block

??Every variable has a specific type which describes what kind of information it can store

Variable Declarations Declaration Syntax

variable_name type [CONSTANT][NOT NULL][:=value] Example

DECLARE

v_Description VARCHAR2(50); v_NumberSeats NUMBER := 45;

v_Counter BINARY_INTEGER DEFAULT 0;

Data Types

??BINARY_INTEGER ??DEC ??DECIMAL ??DOUBLE PRECISION ??FLOAT ??INT ??INTEGER ??NATURAL ??NUMBER ??NUMERIC ??POSITIVE ??REAL ??SMALLINT

Composite Types

PL/SQL Record

??A record is similar to “C” structure

??Record provide a way to deal with separate but related variables as Unit ??To use, developer must define the type, then declare variables of that type ??To refer to a field within a record , dot notation is used

(26)

26

PL/SQL Record

Following declarative section can be replaced by PL/SQL Record Example DECLARE v_StudentID NUMBER(5); v_FirstName VARCHAR2(20); v_LastName VARCHAR2(20); DECLARE

TYPE t_studentRec IS RECORD ( StudentID NUMBER(5), FirstName VARCHAR2(20), LastName VARCHAR2(20)); v_studentInfo t_StudentRec;

PL/SQL Record

??Use the %ROWTYPE attribute to declare a record based upon a collection of database columns from a table or view

??The fields within the record take their names and data types from the columns of the table or view

??Declare the record in the DECLARE section along with any other required variables and constants Example DECLARE REC1 EMP%ROWTYPE; REC2 EMP%ROWTYPE

PL/SQL Statements

Statements in PL/SQL

??The INTO clause must be used to store a table column value into a variable declared in the DECLARATION section of PL/SQL block

SELECT ENAME INTO MEMNAME FROM EMP WHERE EMPNO=101176;

??Multiple column values can be assigned to equal number of memory variables using single INTO

SELECT ENAME, SAL INTO MEMNAME, MEMSAL FROM EMP WHERE EMPNO=101176;

Oracle Supplied Packages

What is a Package?

A package is an encapsulated collection of related program objects stored together in the database. Program objects are procedures, functions, variables, constants, cursors, and exceptions.

(27)

27

??Let you organize your application development more efficiently.

??Let you grant privileges more efficiently.

??Let you modify package objects without recompiling dependent schema objects. ??Enable Oracle to read multiple package objects into memory at once.

??Let you overload procedures or functions. Overloading means creating multiple procedures with the same name in the same package, each taking arguments of different number or datatype.

??Can contain global variables and cursors that are available to all procedures and functions in the package

What are the packages supplied by Oracle?

Presented below are some important packages provided by Oracle and their Description:

Package Description

Calendar Provides calendar maintenance functions

DBMS_ALERT

Provides support for the asynchronous notification of database events.

DBMS_APPLICATION_INFO

Lets you register an application name with the database for auditing or performance tracking purposes.

DBMS_AQ Lets you add a message (of a predefined object type) onto a queue or to dequeue a message

DBMS_AQADM

Lets you perform administrative functions on a queue or queue table for messages of a predefined object type.

DBMS_DDL

Provides access to some SQL DDL statements from stored procedures, and provides special administration operations not available as DDLs.

DBMS_DEBUG A PL/SQL API to the PL/SQL debugger layer, Probe, in the Oracle server.

DBMS_DEFER

Provides the user interface to a replicated transactional deferred remote procedure call facility. Requires the Distributed Option.

DBMS_DEFER_QUERY Permits querying the deferred remote procedure calls (RPC) queue data that is not exposed through views. Requires the Distributed Option

DMBS_DEFER_SYS

Provides the system administrator interface to a replicated transactional deferred remote procedure call facility. Requires the Distributed Option.

(28)

28 Expressions and Operators

Operator:

An operator is used to manipulate individual data items and return a result. These items are called operands or arguments. Operators are represented by special characters or by keywords. For example, the multiplication operator is represented by an asterisk (*) and the operator that tests for nulls is represented by the keywords IS NULL. The main SQL operators are described below:

Unary and Binary Operators:

A unary operator operates on only one operand. A unary operator typically appears with its operand in this format: operator operand

A binary operator operates on two operands. A binary operator appears with its operands in this format: operand1 operator operand2

Precedence:

An important property of an operator is its precedence. Precedence is the order in which Oracle evaluates different operators in the same expression. When evaluating an expression containing multiple operators, Oracle evaluates operators with higher precedence before evaluating those with lower precedence. Oracle evaluates operators with equal precedence from left to right within an expression.

Arithmetic Operators:

You can use an arithmetic operator in an expression to negate, add, subtract, multiply, and divide numeric values. The result of the operation is also a numeric value. Some of these operators are also used in date arithmetic. Some examples of arithmetic operators are:

+, -, *, / etc.

Character Operators:

Character operators are used in expressions to manipulate character strings. Following is an example of a character operator:

Operator Purpose Example

II Concatenates Character Strings SELECT ‘Name is’ II ename FROM emp

Comparison Operators:

Comparison operators are used in conditions that compare one expression to another. The result of comparing one expression to another can be TRUE, FALSE, or UNKNOWN. Below is given an example of a comparison Operator:

Operator Purpose Example

+ Equality Test SELECT * FROM emp WHERE sal=1500

Logical Operators:

A logical operator combines the results of two component conditions to produce a single result based on them or to invert the result of a single condition. The main comparison operators are: AND, Not and OR Below is given an example of a comparison Operator:

Operator Function Example

(29)

29

FALSE if it is TRUE. If it is UNKNOWN, it remains

UNKNOWN FROM emp WHERE NOT (sal BETWEEN 1000 AND 2000) Set Operators:

Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries. The main set operators are: UNION, UNION ALL, INTERSECT and MINUS

Expression:

An expression is a sequence of variables and literals, separated by operators. The most basic operator is assignment.

Syntax

Variable: = expression

Variable is PL/SQL variable and expression is PL/SQL expression

PL/SQL expression is: 3 + 5 * 7

Concatenation operator (||) attaches two or more strings together. For example, the expression ‘Hello ’||’World’ evaluates to ‘Hello World’.

Conditional and Loop Constructs

Conditionals:

A conditional statement executes a code segment based on a condition, such as an equality test (a = b), a comparison test (a > b), or a Boolean test. PL/SQL has three conditional structures: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF-THEN-...-ELSE.

The IF-THEN format executes a code block if the condition is TRUE. For example: IF line_count > LINES_PER_PAGE

THEN

line_count: = 0;

DBMS_SQL.PUT_LINE ('---'); END IF;

The IF-THEN-ELSE format has two code blocks. If the condition is TRUE, the first block is executed; otherwise, the second block is executed. For example:

IF items_sold > get_employee_target (emp_id) THEN over_quota_count: = over_quota_count + 1; give_raise (emp_id); ELSE give_talking_to (emp_id); END IF;

(30)

30

The IF-THEN-ELSIF-THEN-...-ELSE, PL/SQL's equivalent of the CASE or SWITCH statement, can contain multiple conditions. The statement executes the code block associated with the first TRUE condition. Here's an example:

IF is_number (current_char) OR is_letter (current_char) THEN new_char: = current_char; ELSIF current_char = ' ' THEN new_char: = '+'; ELSE

new_char := convert_to_hex (current_char); END IF;

Loops:

Looping, or iteration, causes the block between the keywords LOOP and END LOOP to be repeatedly executed. The loop ends, or terminates, when an exit condition is met. Once a loop terminates, program control is returned to the first line after the END LOOP keyword. There are three looping structures: simple, WHILE, and FOR.

In the simple loop, the exit condition is embedded inside the loop body. The EXIT command terminates the loop immediately, and is usually embedded inside an IF...THEN statement. EXIT WHEN combines EXIT with a conditional to form a more compact syntax. Here are two constructions of a simple loop. The first example uses EXIT:

LOOP COUNT: = COUNT + 1; IF COUNT > 10 THEN EXIT; END IF; END LOOP;

The second example uses EXIT WHEN: LOOP

COUNT: = COUNT + 1; EXIT WHEN COUNT > 10; END LOOP;

In the second kind of loop, the WHILE loop, the exit condition is outside the body of the loop. The code within the body of the loop iterates while the loop condition is true. The loop terminates when the condition is false, for example:

(31)

31

LOOP

COUNT: = COUNT + 1; END LOOP;

The last kind of loop, the FOR loop, iterates a predetermined number of times. For example, the number of loops needed to process each month in the year does not depend on a complex condition; it always requires 12 passes through the loop. A FOR loop is controlled by an index variable that ranges from a lower bound to an upper bound. The index variable begins at the lower bound. Each pass through the loop increments it. The loop terminates when the index reaches the upper bound, for example:

FOR month_index IN 1.. 12 LOOP

process_month_sales (month_index); END LOOP;

SUMMARY

??PL/SQL extends SQL by adding constructs found in other procedural languages like conditional constructs, looping, variables data types, procedures, functions etc.

??Data types can be Scalar types and Composite types. ??Composite Types can be PL/SQL Record and PL/SQL Tables

??A Record is like a ‘C’ structure. The %ROWTYPE attribute is used to declare a record based upon a collection of database columns from a table or view

??A table is like a ‘C’ array. A Table can have only one primary key (BINARY_INTEGER) and a column

??

Test your Understanding

Write a PL/SQL block to print the Department wise Employee Details, with Total for each department as well as Grand Total. The report should look like this,

DEPT No: 10 dname: Accounts

Deptno Emono Ename Job Sal

--- --- --- --- --

(32)

32

Chapter 3 :

Cursors and Exceptions

Learning Objectives

Upon completion of this Chapter, you will be able to: ??Understand & write cursors

Understand Cursors

A cursor is a mechanism by which you can assign a name to a "select statement" and manipulate the information within that SQL statement. In other words, A cursor is a SELECT statement that is defined within the declaration section of your PLSQL code. We'll take a look at three different syntaxes for cursors. There are two types of cursors: Implicit and Explicit. An Implicit cursor is used for all other SQL statements. Implicit Cursors gives less programmatic control. In explicit cursor the cursor name is explicitly attached to a select statement

The four PL/SQL steps necessary for explicit cursor processing are as follows: ??Declare the cursor

??Open the cursor

??Fetch the results into PL/SQL variables ??Close the cursor

??To use a cursor, it must be declared first

Syntax

CURSOR cursor_name IS SELECT_statement;

A cursor without parameters

CURSOR comp IS SELECT compid FROM company;

A cursor with parameters

CURSOR comp (mcompid IN NUMBER) IS SELECT name FROM company WHERE compid = mcomid;

Open Cursors:

Once you've declared your cursor, the next step is to open the cursor The basic syntax to OPEN the cursor is:

OPEN cursor_name;

For example, you could open a cursor called c1 with the following command: OPEN c1;

(33)

33

Below is a function that demonstrates how to use the OPEN statement:

CREATE OR REPLACE Function FindCourse ( name_in IN varchar2 ) RETURN number IS cnumber number; CURSOR c1 IS SELECT course_number from courses_tbl

where course_name = name_in; BEGIN

open c1;

fetch c1 into cnumber; if c1%notfound then cnumber := 9999; end if; close c1; RETURN cnumber; END;

Fetch Cursor

The purpose of using a cursor, in most cases, is to retrieve the rows from your cursor so that some type of operation can be performed on the data. After declaring and opening your cursor, the next step is to FETCH the rows from your cursor.

The basic syntax for a FETCH statement is:

FETCH cursor_name INTO <list of variables>;

For example, you could have a cursor defined as:

CURSOR c1 IS

SELECT course_number from courses_tbl

where course_name = name_in;

The command that would be used to fetch the data from this cursor is: FETCH c1 into cnumber;

(34)

34 Close Cursor

The final step of working with cursors is to close the cursor once you have finished using it. The basic syntax to CLOSE the cursor is:

CLOSE cursor_name;

For example, you could close a cursor called c1 with the following command: CLOSE c1;

Below is a function that demonstrates how to use the CLOSE statement:

open c1;

fetch c1 into cnumber; if c1%notfound then cnumber := 9999; end if; close c1; RETURN cnumber; END; Cursor Attributes:

While dealing with cursors, you may need to determine the status of your cursor. The following is a list of the cursor attributes that you can use:

Attributes Explanation

%ISOPEN Returns TRUE if the cursor is open, FALSE if the cursor is closed

%FOUND

Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.

Returns NULL if cursor is open, but fetch has not been executed. Returns TRUE if a successful fetch has been executed.

Returns FALSE if no row was returned.

%NOTFOUND Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.

(35)

35

Return NULL if cursor is open, but fetch has not been executed.

Returns FALSE if a successful fetch has been executed. Returns TRUE if no row was returned.

%ROWCOUNT

Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.

Returns the number of rows fetched.

Below is an example of how you might use the %NOTFOUND attribute.

open c1;

fetch c1 into cnumber; if c1%notfound then cnumber := 9999; end if; close c1; RETURN cnumber; END; Cursor Examples:

The Following example shows a procedure that outputs a dynamic PLSQL cursor. The example states a problem and shows how to solve it.

Question: In Oracle, I have a table called "wine" and a stored procedure that outputs a cursor based on the "wine" table.

I've created an HTML Form where the user can enter any combination of three values to retrieve results from the "wine" table. My problem is that I need a general "select" statement that will work no matter what value(s), the user enters.

Example:

parameter_1= "Chianti" parameter_2= "10"

(36)

36

parameter_3= wasn't entered by the user but I have to use in the select statement. And this is my problem. How to initialize this parameter to get all rows for column3?

SELECT * FROM wine WHERE column1 = parameter_1 AND column2 = parameter_2 AND column3 = parameter_3;.

The output of my stored procedure must be a cursor.

Answer: To solve your problem, you will need to output a dynamic PLSQL cursor in Oracle. Let's take a look at how we can do this. We've divided this process into 3 steps.

Step 1 - Table Definition

First, we need a table created in Oracle called "wine". Below is the create statement for the wine table.

create table wine ( col1 varchar2(40), col2 varchar2(40), col3 varchar2(40) );

We've made this table definition very simple, for demonstration purposes.

Step 2 - Create package

Next, we've created a package called "winepkg" that contains our cursor definition. This needs to be done so that we can use a cursor as an output parameter in our stored procedure.

create or replace PACKAGE winepkg IS

/* Define the REF CURSOR type. */

TYPE wine_type IS REF CURSOR RETURN wine%ROWTYPE; END winepkg;

This cursor will accept all fields from the "wine" table.

Step 3 - Create stored procedure

Our final step is to create a stored procedure to return the cursor. It accepts three parameters (entered by the user on the HTML Form) and returns a cursor (c1) of type "wine_type" which was declared in Step 2.

The procedure will determine the appropriate cursor to return, based on the value(s) that have been entered by the user (input parameters).

create or replace procedure find_wine2 (col1_in in varchar2, col2_in in varchar2, col3_in in varchar2, c1 out winepkg.wine_type) as BEGIN

/* all columns were entered */

IF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) > 0) THEN

(37)

37

select * from wine

where wine.col1 = col1_in and wine.col2 = col2_in and wine.col3 = col3_in; /* col1 and col2 were entered */

ELSIF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) = 0) THEN

OPEN c1 FOR select * from wine

where wine.col1 = col1_in and wine.col2 = col2_in; /* col1 and col3 were entered */

ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) > 0)

THEN

where wine.col1 = col1_in and wine.col3 = col3_in; /* col2 and col3 where entered */

ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) > 0) THEN

where wine.col2 = col2_in and wine.col3 = col3_in; /* col1 was entered */

ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) = 0) THEN

where wine.col1 = col1_in; /* col2 was entered */

ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) = 0) THEN

where wine.col2 = col2_in; /* col3 was entered */

ELSIF (length(col1_in) = 0) and (length(col2_in) = 0) and (length(col3_in) > 0) THEN

where wine.col3 = col3_in; END IF;

(38)

38 Understanding Exceptions

What is an Exception?

Exceptions are errors raised whenever there is any in a particular PL/SQL block. This causes a termination in the program by Oracle. Control then is transferred to the separate exception section of the program, if one exists, to handle the exception.

Types of Exceptions:

??Predefined Exception ??User Defined Exception

Predefined Exceptions

Oracle has a list of Predefined exceptions. They are generated mostly with the SELECT statement. They are generated implicitly at runtime. Following is a list of the predefined exceptions that Oracle recognises:

Oracle Exception Name Oracle Error

Explanation

DUP_VAL_ON_INDEX

ORA-00001

You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.

TIMEOUT_ON_RESOURCE

ORA-00051

You were waiting for a resource and you timed out.

TRANSACTION_BACKED_OUT ORA-00061

The remote portion of a transaction has rolled back.

INVALID_CURSOR

ORA-01001

You tried to reference a cursor that does not yet exist. This may have happened because you’ve executed a FETCH cursor or CLOSE cursor before opening the cursor.

NOT_LOGGED_ON

ORA-01012

You tried to execute a call to Oracle before logging in.

LOGIN_DENIED

ORA-01017

You tried to log into Oracle with an invalid username/password combination.

NO_DATA_FOUND

ORA-01403

You tried one of the following: 1. You executed a SELECT INTO statement and no rows were returned. 2. You referenced an uninitialized row in a table. 3. You read past the end of file with the UTL_FILE package.

TOO_MANY_ROWS

ORA-01422

You tried to execute a SELECT INTO statement and more than one row was returned.

(39)

39

ZERO_DIVIDE

ORA-01476

You tried to divide a number by zero.

User Defined Exception

Sometimes, it is necessary for programmers to name and trap their own exceptions - ones that aren't defined already by PL/SQL. These are called Named Programmer or User-Defined Exceptions.

The syntax for the Named Programmer-Defined Exception in a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ] IS [declaration_section] exception_name EXCEPTION; BEGIN executable_section RAISE exception_name ; EXCEPTION

WHEN exception_name THEN [statements]

WHEN OTHERS THEN [statements]

END [procedure_name];

The syntax for the Named Programmer-Defined Exception in a function is:

CREATE [OR REPLACE] FUNCTION function_name [(parameter [, parameter])] RETURN return_datatype IS | AS [declaration_section] exception_name EXCEPTION; BEGIN executable_section RAISE exception_name ; EXCEPTION

WHEN exception_name THEN [statements]

WHEN OTHERS THEN [statements]

END [function_name];

Here is an example of a procedure that uses a Named Programmer-Defined Exception:

CREATE OR REPLACE PROCEDURE add_new_order (order_id_in IN NUMBER, sales_in IN NUMBER) IS no_sales EXCEPTION; BEGIN IF sales_in = 0 THEN RAISE no_sales; ELSE

INSERT INTO orders (order_id, total_sales ) VALUES ( order_id_in, sales_in );

(40)

40

EXCEPTION

WHEN no_sales THEN

raise_application_error (-20001,'You must have sales in order to submit the order.');

WHEN OTHERS THEN

raise_application_error (-20002,'An error has occurred inserting an order.'); END;

PRAGMA EXCEPTION_INIT

The pragma EXCEPTION_INIT associates an exception name with an Oracle error number. That lets you refer to any internal exception by name and to write a specific handler for it instead of using the OTHERS handler.

Syntax

PRAGMA EXCEPTION_INIT (EXCEPTION, ERROR_NUMBER);

Example DECLARE

MYEXP EXCEPTION;

PRAGMA EXCEPTION_INIT (MYEXP, -1422);

RAISE_APPLICATION_ERROR

What is RAISE_APPLICATION_ERROR?

The RAISE_APPLICATION_ERROR is a procedure that communicates application-specific errors from the server side (usually a database trigger) to the client-side application. This built-in procedure is the only mechanism available for communicating a server-side, programmer-defined exception to the client side in such a way that the client process can handle the exception. Error number should be of the range between –20000 and –20999. Error messages should be less than 512 characters.

Syntax

RAISE_APPLICATION_ERROR (error_number in NUMBER, error_msg in VARCHAR2); Example

IF age < 18 THEN

RAISE_APPLICATION_ERROR (-20001, ‘Must be eighteen years of age.'); END IF;

SUMMARY

??Explicit cursor handling requires Declaring, Opening, Fetching and Closing cursor ??Oracle raises ERRORS whenever any abnormal situation arises in a PL/SQL block

and performs an illegal termination of the execution of the program

??PL/SQL traps and responds to errors using an architecture of EXCEPTION handler ??Pre-defined exceptions are already defined in the STANDARD package

(41)

41 Test your Understanding

1. Create a cursor to print empno, ename, job, sal, hiredate and the increment amount for all employees. Increment amount depends on the day of joining

Day of Joining Increment in %

Friday 20

Wednesday 18

Thursday 17

(42)

42

Chapter 4 :

Procedures, Functions and Packages

Learning Objectives

On completion of this Chapter, you will be able to: ??Understand & Write Procedures

Understand Procedures

What is a Procedure?

It is a sub program in the PL/SQL block, which can perform a specific task when invoked explicitly with or without parameter. A Procedure has two parts: Specification and Body

Specification: This section begins with the keyword Procedure followed by its name and Parameter list (optional)

Body: Procedure Body begins with the keyword IS/AS and ends with the keyword END followed by the procedure name (optional)

Syntax

CREATE OR REPLACE PROCEDURE <PROCEDURE_NAME> (<PARAMETER> [MODE] <DATA TYPE>,)

IS/AS

[LOCAL VARIABLE DECLARATION] BEGIN

PL/SQL EXECUTABLE STATEMENT

[EXCEPTION]

[EXCEPTION HANDLERS] END [PROGRAM UNIT NAME];

Creating Procedures

In Oracle, you can create your own procedures. The syntax for a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter [, parameter])] IS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [procedure_name];

(43)

43 Passing Parameters

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

IN

The parameter can be referenced by the procedure or function. The value of the parameter cannot be overwritten by the procedure or function.

OUT

The parameter cannot be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.

Example of Procedure with Parameter

CREATE OR REPLACE PROCEDURE CALC_BONUS ( EMP_ID IN INTEGER,

BONUS OUT NUMBER) IS MJOB EMP.JOB%TYPE;

BEGIN

SELECT SAL*0.10 INTO BONUS FROM EMP WHERE EMPNO = EMP_ID; IF MJOB = ‘MANAGER’ THEN

BONUS: = 0; END IF;

END CALC_BONUS;

Executing The Procedure DECLARE BON NUMBER; ECODE NUMBER; BEGIN ECODE: = &EMPLOYEE_NO; CALC_BONUS(ECODE, BON);

DBMS_OUTPUT.PUT_LINE(‘The Bonus for the employee’ || ECODE || ‘ is’ || BON); END;

Example of Procedure with INOUT Parameter

CREATE OR REPLACE PROCEDURE FORMAT_PHONE_NO (V_PHONE IN OUT VARCHAR2)

IS BEGIN

V_PHONE: = ‘(‘ || SUBSTR (V_PHONE, 1,3) || ‘)’ || SUBSTR (V_PHONE, 4,3) || ‘-’ || SUBSTR (V_PHONE, 7);

END FORMAT_PHONE_NO; /

(44)

44 Creating Functions

In Oracle, you can create your own functions. The syntax for a function is:

CREATE [OR REPLACE] FUNCTION function_name [ (parameter [,parameter]) ] RETURN return_datatype IS | AS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [function_name];

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

1. IN

The parameter can be referenced by the procedure or function. The value of the parameter cannot be overwritten by the procedure or function.

2. OUT

The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.

3. IN OUT

The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

The following is a simple example of a function:

CREATE OR REPLACE Function FindCourse ( name_in IN varchar2 ) RETURN number IS cnumber number; cursor c1 is select course_number from courses_tbl

open c1;

fetch c1 into cnumber; if c1%notfound then cnumber := 9999; end if; close c1; RETURN cnumber; EXCEPTION

WHEN OTHERS THEN

(45)

45

ERROR- '||SQLERRM); END;

This function is called FindCourse. It has one parameter called name_in and it returns a number. The function will return the course number if it finds a match based on course name. Otherwise, it returns a 99999.

You could then reference your new function in an SQL statement as follows:

select course_name, FindCourse(course_name) as course_id from courses

where subject = 'Mathematics';

Implementing Packages

What is a Package?

A package is a collection of Functions, Procedures, Global variables and cursors stored in server in compiled form. A package consists of 2 parts:

Package Specification: This acts as an interface to the user applications. This part declares the, PL/SQL types, variables, constants, exception, cursor & sub-programs (Functions and procedures). This part is created using CREATE PACKAGE command

Package Body: It implements the specifications by defining the cursors and sub-programs. This part is created using CREATE PACKAGE BODY command.

Oracle stores package specification and body separately in the data dictionary. A package specification can exists without a package body but not vice versa.

An element of a package, whether it is a variable or a module, can either be Public

When defined in the specification a public element can be referenced from other programs and PL/SQL blocks

Private

When defined only in the body of the package, but does not appear in the specification. A private element cannot be referenced outside of the package. It can only be referenced by other elements within the package

The sub-programs that are present inside a package cannot exist separately as database objects. A package cannot be called by itself. Only the procedures and functions from within the package can be called with reference to the package using the dot (.) operator. When one sub-program is called, then all other sub-programs are also loaded into the memory, hence the subsequent call for any other modules becomes fast.

(46)

46 SUMMARY

??A package is a compiled database objects logically groups PL/SQL types, sub-programs etc. It consists of two parts: specification and body

??The specification is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the specification

Test Your Understanding

1. Create a procedure that will accept a percentage and JOB, and will show the reflection on the total salary. Calculate the total salary before the increment for the particular JOB and show the difference.

2. Create a procedure that will accept the LOAN amount and EMPNO as input and display the installment amount and number of installments to be paid. (Max loan amount will be his current salary * total number of years of service. Loan will be paid in equal monthly installment. Installment amount will be 1/10th_{of monthly salary)}

(47)

47

Chapter 5 :

Triggers

Learning Objectives

Upon Completion of this chapter you will be able to: ??Understand & write Database Triggers

Appreciate Database Triggers

What is a Database Trigger?

A database trigger is a stored program unit associated with a database table. Triggers are used to overcome the limitations of constraints and to supplement the declarative referential integrity while implementing complex business rules or to audit changes to data.

Features of Database Triggers:

??These are stored procedures that gets implicitly executed when some database-related event occurs

??Can be applied on any Table/View

??Can be applied before the instruction is executed as well as after the execution

??Can work for any DML statements like INSERT, UPDATE and DELETE but not for SELECT

??Can be used to overcome the limitation of CHECK constraints Syntax

CREATE OR REPLACE TRIGGER <TRIGGER_NAME> [BEFORE/AFTER] [INSERT/UPDATE/DELETE]

ON <TABLE_NAME>

[FOR EACH ROW [WHEN triggering_condition]] trigger_body;

Components of Triggers

The main components of Database Triggers are the following: ??Trigger Timing

It means when a trigger should fire. The possible trigger timing are BEFORE, AFTER ??Trigger Event

The trigger event can be INSERT, UPDATE or DELETE ??Trigger Type

The type of trigger can be Statement or Row ??Trigger Body

Oracle CTS

Oracle 9i

Version: ORACLE/HANDOUT/0704/1.0

Date: 27-07-04

2

TABLE OF CONTENTS

3

4

5

6

Introduction

About this Module

Target Audience

Module Objectives

Pre-requisite

7

Chapter 1

:

ORACLE SQL

Learning Objectives

Understand the features of SQL

What can SQL do?

Learn SQL*Plus

8

9

10

Use Select Statements

Handle NULL Values

11

Use Column Alias

Eliminate Duplicate Rows

Use Where Clause

12

Use Operators

13

Sort Rows

14

15

Use Single Row Functions

16

Use Aggregate Functions

17

Understand Joins

Understand Equi-Joins

18

Understand Outer-Joins

Understand Self Joins

Learn Sub Query

19

Learn Co Related Sub Query

20

Understand DDL Statements

Create Tables

21

Understand DML statements

Understand Implicit Transaction Processing

22

State of data after ROLLBACK

Understand Read Consistency

SUMMARY

23

Test Your Understanding

24

Chapter 2

:

PL/SQL Fundamentals

Learning Objectives

PL/SQL Features

PL/SQL Block Structure

25

Identifiers

Variables

Data Types

Composite Types

26

PL/SQL Statements

Oracle Supplied Packages

27

28

Expressions and Operators

**Learn SQL*Plus**