Online Oracle PL SQL Tutorials

PL/SQL Identifiers

2011-08-29T11:24:00.000+01:00

Identifiers are names for PL/SQL objects such as constants,variables, exceptions, procedures, cursors, and reserved words.

Identifiers have the following characteristics:
• Can be up to 30 characters in length
• Cannot include whitespace (space, tab, carriage return)
• Must start with a letter
• Can include a dollar sign ($), an underscore (_), and a
pound sign (#)
• Are not case-sensitive

You shouln't use the PL/SQL’s reserved words as identifiers in your programs as this can result in compilation or runtime errors that are difficult to troubleshoot.

Predefined Exceptions

2011-08-28T15:15:00.000+01:00

We discussed in the previous article (Expection Handling) that the exceptions can be either System pre-defined exceptions or User defined excpetions. Lets have a look on commonly used pre-defined exceptions with there error numbers:

Named Exception	Error Number
DUP_VAL_ON_INDEX	ORA-00001
TIMEOUT_ON_RESOURCE	ORA-00051
TRANSACTION_BACKED_OUT	ORA-00061
INVALID_CURSOR	ORA-01001
NOT_LOGGED_ON	ORA-01012
LOGIN_DENIED	ORA-01017
NO_DATA_FOUND	ORA-01403
SYS_INVALID_ROWID	ORA-01410
TOO_MANY_ROWS	ORA-01422
ZERO_DIVIDE	ORA-01476
USERENV_COMMMITSCN_ERROR	ORA-01725
INVALID_NUMBER	ORA-01722
STORAGE_ERROR	ORA-06500
PROGRAM_ERROR	ORA-06501
VALUE_ERROR	ORA-06502
ROWTYPE_MISMATCH	ORA-06504
CURSOR_ALREADY_OPEN	ORA-06511
ACCESS_INTO_NULL	ORA-06530
COLLECTION_IS_NULL	ORA-06531
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532
SUBSCRIPT_BEYOND_COUNT	ORA-06533
NO_DATA_NEEDED	ORA-06548
CASE_NOT_FOUND	ORA-06592
SELF_IS_NULL	ORA-30625

One type of exception can be defined once in a block but different type of exceptions can be raised in the same block. And nested blocks can be used to specify same exception to handle in different ways.
For Ex:
Syntax:
DECLARE
        Declaration section;
BEGIN
       Executable statements ;
EXCEPTION
    WHEN ex_name1 THEN
        Error handling statements;
   WHEN ex_name2 THEN
       Error handling statements ;
   WHEN Others THEN
       Error handling statements ;
END;

Exception handling

2011-08-25T20:12:00.001+01:00

An Exception is an error or warning situation, which occurs during program Execution. Ideally these situations should not occur in program.

The exceptions can be defined as follows:
•System-defined or pre-defined exception
•User-defined exception

Syntax:
DECLARE
Declaration section;
BEGIN
Executable statements ;
EXCEPTION
WHEN ex_name1 THEN
Error handling statements;
WHEN ex_name2 THEN
Error handling statements ;
WHEN Others THEN
Error handling statements ;
END;

•System-defined or pre-defined exception raised automatically by the oracle runtime engine whenever it detects an error condition , also known as NAMED SYSTEM EXCEPTION
•Unexpected Oracle errors can be Handled using OTHERS handler,also known as UNNAMED SYSTEM EXCEPTION
•Predefined exception handlers are declared globally in package STANDARD.
•Some of the pre-defined Exceptions are as follows:
1.CURSOR_ALREADY_OPEN
2.INVALID_CURSOR
3.TOO_MANY_ROWS
4.DUP_VAL_ON_INDEX

Handling Un-named Exception
•There are two ways to handle unnamed system exceptions:
1.WHEN OTHERS clause
2.Associate the System exception code to a name and use it as a named exception
•We can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT.
•EXCEPTION_INIT will associate a predefined Oracle error number to a programmer defined exception name

Steps to be followed to use unnamed system exceptions are
1.They are raised implicitly.
2.If it is not handled in WHEN Others, it must be handled explicitly.
3.To handle the exception explicitly, Pragma EXCEPTION_INIT must be used.

Uses of EXCEPTION_INIT

•Giving Names to otherwise system anonymous exceptions.
•Assigning names to the application specific errors you raise using RAISE_APPLICATION_ERROR, this allows you to handle such errors by name, rather than simply by number.

Raising Exceptions:

There are 3 ways that an Exception may be raised in Your Application:
1.Implicitly by Oracle
2.By Using RAISE Statement
3.Using RAISE_APPLICATION_ERROR built-in procedure

RAISE statement

Following are the forms of RAISE Statement:
1.RAISE exception_name;
2.RAISE package_name.exception_name;
3.RAISE;

Scope of Exception Handling
–Once an exception is raised in a block, that block's executable section closes. But you get to decide what constitutes a block
- If an exception propagates out of the outermost block, then that exception goes un handled

PL/SQL Introduction

2011-08-24T22:36:00.000+01:00

PL/SQL- PLSQL stands for "Procedural Language/Structured Query Language". PL SQL is closely integrated into the SQL language, yet it adds programming constructs that are not native to SQL. PL/SQL also implements basic exception handling. It supports variables, conditions, loops and arrays.Each PL/SQL program like procedures,functions, packages consists of SQL and PL/SQL statements which form a PL/SQL block.

A PL/SQL Block consists of three sections:
The Declaration section .
The Execution section .
The Exception Handling section .

These blocks are mainly bounded by 4 keyword:

DECLARE: DECLARE is the keyword that will specify declaration section.This is section where we will declare the data type definitions, variables, embedded functions, and procedures.

BEGIN: BEGIN is the keyword that marks the start of executable section. We must have at least one line of executable code, even if it’s the keyword NULL, which means no operation.

EXCEPTION:is the keyword where exception-handling section starts.This is where we will catch any database or PL/SQL errors orexceptions

END: is the keyword to mark the end of the PL/SQL block.

Sequences (Identity)

2011-08-21T20:41:00.000+01:00

Most of the tables consist of an ID or identity column to identify the rows into the table. Microsoft SQL has an defined property IDENTITY column that increment the value automatically when the data is inserted into the table.
But Oracle don't have any similar property defined but not to worry this can be achived by the help of SEQUENCES.

Lets consider the table Employee (
ID,
Name,
Department)

Now this table cointains ID and if want to always insert the next value after the maximum value present in table we can achieve this with the help of sequences.

Syntax:
CREATE SEQUENCE <sequence_name>
    MINVALUE <value>
    MAXVALUE <value>
    START WITH <value>
    INCREMENT BY <value>
    CACHE <value> ;

Here:
<sequence_name> : is name for the sequence
MINVALUE : is the minimum value for the column
MAXVALUE: is the maximum value for the column by default it will be 999999999999999999999999999
START WITH: is the starting value
INCREMENT BY: is the value by the which the current value will be inceremented to get the next value
CACHE : is number of records that should stay in cache for perfomance

For ex:
CREATE SEQUENCE Employee_seq_id
    MINVALUE 1
    MAXVALUE 99999999
    START WITH 1
    INCREMENT BY 1
    CACHE 100 ;

INSERT INTO EMPLOYEE
VALUES(Employee_seq_id.nextval, 'John','Sales')

Here:
nextval will return the next value that should be inserted into the ID column

Comments

2011-08-21T17:56:00.001+01:00

Comments can be inserted in the plsql scripts in three ways:

1. REM[ARK] command: REM Command can be ued in the PL*SQL to comment a single line
For ex:
REM This is PL*SQL comment for reports

2. -- : The symbol -- can also be used to comment a single line
For ex:
-- This is PL*SQL comment for reports

3. /* */ : For mulitple line comments /* <multiple line comments> */ can be used
For ex:
/* This is PL*SQL comment for reports
We can put multiple lines in it */

Views

2011-08-21T00:16:00.000+01:00

View is a representation of collection of data from one or more tables. It usually consist of SQL select statement to select data from combination of related tables but can also be used to update , insert and delete data from the tables.
Now the question is when we are using the SQL statements only why we need Views? Answer is:

Security : With the help of views you can restrict and manage what data and fields from a single or multiple tables the users should be able to see.
Repetitive code: If some code needs to be used mutiple times its better to save as views so that every time the same need not to written again.
Code Manageability : Views help in managing the code in structured and consistent way.

Syntax:
CREATE [OR REPLACE] VIEW <view_name> AS
<sql_select_statement>;

Here:
<view_name>: is the name of the view
<sql_select_statement>: SQL SELECT statement against one or more tables in the database.

For Ex:

Create or replace view employee_department
AS
SELECT emp.name,
emp.empID,
dept.name
FROM EMPLOYEE emp,
DEPARTMENT dept
Where emp.deptid = dept.id

Delete & Rollback

2011-08-14T21:53:00.000+01:00

Lets delete for some unwanted data from the table

Syntax:
DELETE FROM <table_name>
WHERE <column_name_N> = <column_value_N>...;

Here:
<table name> is the table from where the date needs to be deleted
<column_name_N> is the condition for which the data needs to be deleted

Now what if you delete data by mistake. Don't worry until you do the COMMIT we can still undo the delte by the ROLLBACK command.

Syntax:

ROLLBACK;

Update

2011-08-14T20:40:00.002+01:00

We learned how to insert and select data, now lets discuss how to update the existing data in the table. Having learned the select statement it easy top use the Update statement as there is just a slight change in the syntax.

Syntax:

UPDATE <table_name>
SET <column_name_1> = <column_value_1>,
<column_name_2> = <column_value_2>,...
<column_name_N> = <column_value_N>
WHERE <column_name_M> = <value> ;
COMMIT;

Here:
UPDATE: is commend for updating the data
SET: is the keyword for setting the value for the column

Update is used quite similar to the Select statement and joins (discussed in joins) can also be used with the update statement as with select.

Joins

2011-08-14T13:31:00.000+01:00

We discussed earlier how to select data from a single table. Now what if we want to select data from multiple related tables. For ex lets say we have below 2 tables:

Table 1: Employee Table 2 : Department

Columns Columns

EmpID DepartmentID

EmpName DepartmentName

DepartmentId Function

Now if we want to see the Employee's name the department name which he belongs to, this can be accomplished by using the joins with the select statement.

INNER or Simple joins

Syntax:

SELECT <column_name_1>,
<column_name_2>,
<column_name_N>
FROM <table_name_1> ,<table_name_2>
WHERE <table_name_1>.<column_name> = <table_name_2>.<column_name>

[ORDER BY <order_by_column_name_N>];

SELECT <column_name_1>,
<column_name_2>,
<column_name_N>
FROM <table_name_1> JOIN <table_name_2>
ON <table_name_1>.<column_name> = <table_name_2>.<column_name>

[ORDER BY <order_by_column_name_N>];

Both the above queries will return the similar results

OUTER JOINS:

Now if we want all the records from one of the tables in join and only matching values from another table this can be accomplished by Outer joins.

Syntax:

SELECT <column_name_1>,
<column_name_2>,
<column_name_N>
FROM <table_name_1> ,<table_name_2> (+)
WHERE <table_name_1>.<column_name> = <table_name_2>.<column_name>

[ORDER BY <order_by_column_name_N>];

SELECT <column_name_1>,
<column_name_2>,
<column_name_N>
FROM <table_name_1> [Left , Right] JOIN <table_name_2>
ON <table_name_1>.<column_name> = <table_name_2>.<column_name>

[ORDER BY <order_by_column_name_N>];

Here:
We have introduced 2 new commands:
(+) for tradional joins : represents all the values will be selected from the table
[Left , Right] : Use Left if you want to select all the value from the left hand side table
and use RIGHT if you want to select all the value from the right hand side table.
Only one of them can be used in a single join

Select

2011-08-13T20:55:00.002+01:00

We have inserted data into the table, now the question is how to read this data. Select statement is the answer. Select is one of the most usefull and powerfull command in SQL and a whole book can be written about it, lets discuss few commonly used features of Select:

Syntax:

SELECT <column_name_1>,
<column_name_2>,
<column_name_N>
FROM <table_name>
[WHERE <select_condition >]
[ORDER BY <order_by_column_name_N>];

Here:
SELECT: Command for selecting the data
<column_name> : table column name to be fetched
FROM : Command for specifying from which table data is required
<table_name>: Table name from which data is required
WHERE : Optional Command for specfying the filter criteria
Order BY : Optional command to sort the data
<order_by_column_name_N>: column names on which needs to be sorted

For ex:

SELECT EmpNo,Name , department
FROM Employee
WHERE department = 'sales'
ORDER BY EmpNo ;

Insert

2011-08-10T22:49:00.000+01:00

Now as we have create the table lets insert some data into it, thats whats tables are for - storing data.
There are several methods by which data can be inserted into the table:

1. By Values
2. By Select (from another table)

Insertion by Value:

INSERT INTO <table_name> (
<column_name_1>,
<column_name_2>, …
<column_name_N> )
VALUES (
<column_value_1>,
<column_value_2>,…
<column_value_N> );
Commit;

Here:
<table_name> : is the table name where data needs to be inserted
<column_name> : is the column names in the table
<column_value>: is the corresponding column value that needs to be inserted

Insertion by Select:

INSERT INTO <table_name> (
<column_name_1>,
<column_name_2>, …
<column_name_N> )
SELECT <column_value_1>,
<column_value_2>,…
<column_value_N>
FROM <from_table_name> ...;
Commit;

Here:
<table_name> : is the table name where data needs to be inserted
<column_name> : is the column names in the table
<column_value>: is the corresponding column value that needs to be inserted
<from_table_name>: is the table from where the date needs to be selected

We will discuss the select statement in detail soon in the coming notes.

Triggers

2011-08-09T23:55:00.000+01:00

Triggers are procedures that are stored in the database that gets executed implictly on operations like INSERT, UPDATE and DELETE. Triggers can be DML triggers, INSTEAD OF triggers and Database and schema triggers. The events can take place BEFORE, AFTER and INSTEAD OF the INSERT, UPDATE and DELETE.

Events:
BEFORE triggers: Executes the specified events before writting the data onto the disk.
AFTER trigger: Executes the specified events after writting the data onto the disk.
INSTEAD OF triggers: Executes the specified events instead of the original operation.

Syntax:

CREATE [OR REPLACE] TRIGGER <trigger_name>
BEFORE INSERT ON <table_name>
FOR EACH ROW
BEGIN
<pl/sql statement>
END;

Here:
<trigger_name> is the trigger name,
BEFORE is the keyword as discussed earlier and can be interchenged with AFTER and INSTEAD OF according to requirement,
INSERT is the event and can be replaced with DELTE and UPDATE ,
<table_name> is the table name on which trigger is defined,
FOR EACH ROW is an optional keyword that is used when trigger operation is required for each row in the table,
<pl/sql statement> is the pl/sql queries required as trigger operation

Constraints

2011-08-04T23:02:00.000+01:00

Constraints as name suggest are rules for a table and columns that define what kind of data can be stored in the table.

Primay Key Constraint: Primary key is the most widely used constriant that is usually required in every table. A primary key consists of one or more columns of a table, combination of which will be unique in the table.

Syntax:

ALTER TABLE <table_name> ADD

CONSTRAINT <constraint_name>

PRIMARY KEY (

<column_name_1>,

<column_name_2>,...

<column_name_N> );

For ex:

ALTER TABLE Employee ADD

CONSTRAINT Employee_pk

primary key (

Emp_id );

Here:

<table_name>

<constraint_name> is the name of the primary key constraint, and

<column_name> is a column to use in the constraint.

is the name of the table.

Foreign Key Constraint: Foreign key constraints is the SQL way to maintain referential integrity. It helps in defining the parent- child relationship between the tables. Once a parent table column value is used in the child table it can't be deleted fron the parent table without deleting from the child table.

Syntax:

ALTER TABLE <table_name> ADD
CONSTRAINT <constraint_name>
FOREIGN KEY (
<column_name_1>,
<column_name_2>,…
<column_name_N> )
REFERENCES <referenced_table_name> (
<column_name_1>,
<column_name_2>,…
<column_name_N> );

For ex:

ALTER TABLE Employee ADD
CONSTRAINT employee_fk1
FOREIGN KEY (branch)
REFERENCES Department(branch);

Here: <table_name> is the name of the table to be constrained,
<constraint_name> is the name of the foreign key constraint,
<referenced_table_name> is the name of the table to be referenced,
<column_name> is a column that is being referenced

Indexes

2011-08-04T21:13:00.000+01:00

Indexes in the database has the same function as of index page in the directory means its makes searching easy and fast. But in the database indexes should be implement carefully as bad indexes may increase the insertion, updation and deletion time.

Indexes: Syntax

CREATE [UNIQUE] INDEX <index_name>

on <table_name> (

<column_name_1>,

<column_name_2>,

<column_name_N> );

For ex:

CREATE UNIQUE INDEX Employee_uk1

on Employee (

name,

birth_date,

gender );

here

<table_name> is the name of the table, and<column_name>
UNIQUE is an optional keyword that is used for ensuring unique values in the table.
is the name of a column.

<index_name> is the name of the index,

Getting started - Table Creation

2011-08-02T22:45:00.001+01:00

"Table" is basic structure that is used by SQL to store and represent data. It is similar to 2- dimensional array and consists of rows and columns.

Table: Structure
           Column1 Column2 Column3.................................
Row1    Data         Data        Data
Row2    Data         Data        Data
Row3    Data         Data        Data
.
.
.

How to create table: Syntax

CREATE TABLE <table_name> (

<column_name_1> <data_type_1>,

<column_name_2> <data_type_2>,

<column_name_N> <data_type_N> );

For Ex:

CREATE TABLE employee(

Empid number,

Name varchar2(100),

Birth_date date,

Gender varchar2(15) );