PL/SQL Tutorials

PL/SQL Introduction

2006-10-26T13:14:00.000-07:00

This tutorial covers PL/SQL datatypes, PL/SQL variables, PL/SQL constants, PL/SQL collections, PL/SQL cursors, procedures, functions and packages and much more in best of its form. You will love to read the content of this tutorial. It is an ever increasing tutorial. I will keep on adding more PL/SQL content from time to time.

PL/SQL Block Structure

Below is the basic structure of the PL/SQL program:

Set serveroutput on

Declare

Var1 varchar2(20);

Begin

Var1 := "Hello World";

Dbms_output.put_line(var1);

Exception

When others then

Dbms_output.put_line(‘It is an exception’);

End;

In the declaration section all the variables and constants are defined.

In PL/SQL all the errors are handled in the Exception block.

Begin and End are mandatory statements indicating the begin and end of the PL/SQL Block.

DECLARING VARIABLES AND CONSTANTS:

Example of declaraing variables:

Var1 varchar2(100);

Hire_date Date;

Var2 number default 5;

Var3 number not null := 2;

Not null means a value may change but it can never be assigned Null.

Var4 varchar2(20) := Null;

Var5 varchar2(20) default Null;

Constants:

Var_constant constant number := 100;

Constants cannot be changed.

You must initialize constants at the time of declaration..

%TYPE and %ROWTYPE

%TYPE is used to declare a variable that is of the same type as a specified table’s column.

Emp_number emp.empno%type;

%ROWTYPE is used to declare a record(variable that represents the entire row of a table).

Emp_record emp%rowtype;

Another example of declaraing variable:

Declare

Name varchar2(30);

Select ename into name from emp where empno = 20;

Begin

Null;

End;

Any DML statements should be after Begin statement;

Begin

Delete from emp where empno = 29;

Commit;

End;

PL/SQL Collections

Records

PL/SQL tables

Varrays

Nested Tables

Records:

It is a collection of variables:

Type my_first_record is record (

Name varchar2(20);

Age number;

Salary number;

);

Var_of_myfirstrecord my_first_record;

PL/SQL Tables

These are one dimensional arrays and are indexed by integers.

Example:

Type my_first_table is table of varchar2(10)

Index by binary_integer;

Var_of_table my_first_table;

Var_of_table(1) := ‘hello world’;

Var_of_table(2) := ‘bye’;

Example:

Type my_emp_table is table of emp%rowtype

Index by binary_integer:

Var_of_emp my_emp_table;

Var1_of_emp my_emp_table;

Var_of_emp(1).ename := ‘sachin’;

Var_of_emp(1).empno := 20;

Var_of_emp(1).sal := 3000;

To delete individual records:

Var_of_emp .delete(1);

To remove all entries from a table:

Var_of_emp.delete;

Or

Var_of_emp := var1_of_emp

Where var1_of_emp does not contain any value, it is empty.

COUNT method can be used to return number of records in a PL/SQL Table.

Var_of_table.count

First, Next and Last methods of PL/SQL Tables.

First is for first index in the PL/SQL Tables.

Last is for last index in the PL/SQL Tables.

Next is for next index in the PL/SQL Tables.

Example:

SQL> set serveroutput on

SQL> Declare

2

3 Type my_dept_table is table of varchar2(20)

4 Index by binary_integer;

5

6 Var_of_dept my_dept_table;

7 Index_value number;

8

9 Begin

10

11 For dept_rec in (select * from dept) loop

12 Var_of_dept(dept_rec.deptno) := dept_rec.dname;

13 End loop;

14

15 Index_value := var_of_dept.first;

16 Loop

17 Exit when index_value is null;

18 Dbms_output.put_line (index_value || ' ' ||var_of_dept(index_value));

19 Index_value := var_of_dept.next(index_value);

20 End loop;

21 End;

22 /

10 ACCOUNTING

20 RESEARCH

30 SALES

40 OPERATIONS

PL/SQL procedure successfully completed.

Varrays:

These can be stored in the columns of your tables. When you create them you must provide the maximum size for them. These are dense and Not sparse, which means there is no way to delete individual elements of a varrays.

Example:

Create type project_work_type as varray(20) of varchar2(30);

Create table student_projects(

Name varchar2(20),

Id number,

--each student can have upto 20 projects

Projects project_work_type,

);

Example:

Create type emp_type as object(

Id number,

Name varchar2(20)

);

Create type emp_varray_type as varray(5) of emp_type;

Create table t1(

T1_id number,

T1_name varchar2(10),

Employee emp_varray_type);

Insert into t1(t1_id, t1_name, employee)

Values (1, ’one’,emp_varray_type( emp_type(1,’a’),

Emp_type(2,’b’))

)

Nested Tables

These are similar to index by table but these can be stored in database columns but index by tables cannot be stored in database columns.

PL/SQL Sitemap PL/SQL Resources

PL/SQL Interview Questions

2006-10-25T23:47:00.000-07:00

SQl PL/SQL Question 1

How to display row number with records?

Select rownum, ename from emp;

SQl PL/SQL Question 2

How to view version information in Oracle?

Select banner from v$version;

SQl PL/SQL Question 3

How to find the second highest salary in emp table?

select min(sal) from emp a

where
1 =
(select count(*) from emp b where a.sal < b.sal) ;

SQl PL/SQL Question 4

How to delete the duplicate rows from a table?

create table t1 ( col1 int, col2 int, col3 char(1) );

insert into t1 values(1,50, ‘a’);

insert into t1 values(1,50, ‘b’);

insert into t1 values(1,89, ‘x’);

insert into t1 values(1,89, ‘y’);

insert into t1 values(1,89, ‘z’);

select * from t1;

Col1	Col2	Col2
1	50	a
1	50	b
2	89	x
2	89	y
2	89	z

delete from T1

where rowid <> ( select max(rowid)

     from t1 b

     where b.col1 = t1.col1

     and b.col2 = t1.col2 )

3 rows deleted.

select * from t1;

Col1	Col2	Col2
1	50	a
2	89	z

will do it.

SQl PL/SQL Question 5

How to select a row using indexes?

You have to specify the indexed columns in the WHERE clause of query.

SQl PL/SQL Question 6

How to select the first 5 characters of FIRSTNAME column of EMP table?

select substr(firstname,1,5) from emp

SQl PL/SQL Question 7

How to concatenate the firstname and lastname from emp table?

select firstname || ‘ ‘ || lastname from emp

SQl PL/SQL Question 8

What's the difference between a primary key and a unique key?

Primary key does not allow nulls,

Unique key allow nulls.

SQl PL/SQL Question 9

What is a self join?

A self join joins a table to itself.

Example

SELECT a.last_name Employee, b.last_name Manager

FROM employees a, employees b

WHERE b.employee_id = a.manager_id;

SQl PL/SQL Question 10

What is a transaction and ACID?

Transaction - A transaction is a logical unit of work. It must be commited or rolled back.

ACID - Atomicity, Consistency, Isolation and Duralbility, these are properties of a transaction.

SQl PL/SQL Question 11

How to add a column to a table?

alter table t1 add sal number;

alter table t1 add middle_name varchar(20);

SQl PL/SQL Question 12

Is it possible for a table to have more than one foreign key ?

A table can have any number of foreign keys. It can have only one primary key .

SQl PL/SQL Question 13

How to display number value in words?

SQL> select sal, (to_char(to_date(sal,'j'), 'jsp')) from emp;

SQl PL/SQL Question 14

What is candidate key, alternate key, composite key.

Candidate Key A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table.

Alternate KeyIf the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

Composite Key: - A key formed by combining at least two or more columns is called composite key.

SQl PL/SQL Question 15

What's the difference between DELETE TABLE and TRUNCATE TABLE commands? Explain drop command.

Both Delete and Truncate will leave the structure of
the table. Drop will remove the structure also.

Example

If tablename is T1.

To remove all the rows from a table t1.

Delete t1

Truncate table t1

Drop table t1.
Truncate is fast as compared to Delete. DELETE will
generate undo information, in case of rollback, but TRUNCATE will not.
Full Table scan and index fast scan read data blocks
up to high water mark and truncate resets high water mark but delete does
not.So full table scan after Delete will not improve but after truncate it
will be fast.
Delete is DML. Because truncate is a DDL, it performs
implicit commit. You cannot rollback a truncate. Any uncommitted DML changes
will also be committed with the TRUNCATE.
You cannot specify a WHERE clause in the TRUNCATE
statement, but you can specify that in Delete.
When you truncate a table the storage for the table
and all the indexes can be reset back to its initial size,but a Delete will
never shrink the size of the a table or its indexes.

About Dropping

Dropping a table removes the data and definition of the table. The indexes, constraints, triggers, and privileges on the table are also dropped. The action of dropping a table cannot be undone. The views, materialized views or other stored programs that reference the table are not dropped but they are marked as invalid.

SQl PL/SQL Question 16

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.

Procedures and functions are stored in compiled form in database.

Functions take zero or more parameters and return a value. Procedures take zero or more parameters and return no values.

Both functions and procedures can take or return zero or more values through their parameter lists.

Another difference between procedures and functions, other than the return value, is how they are called. Procedures
are called as stand-alone executable statements:

my_procedure(parameter1,parameter2...);

Functions can be called anywhere in an valid expression :

e.g

1)

IF
(tell_salary(empno) < 500 )
THEN …

2)
var1 := tell_salary(empno);

3)
DECLARE
var1 NUMBER DEFAULT tell_salary(empno);
BEGIN …

Packages contain function , procedures and other data structures.

There are a number of differences between packaged and non-packaged PL/SQL programs.

Package The data in package is persistent for the duration of the user’s
session.The data in package thus exists across commits in the session.

If you grant execute privilege on a package, it is for all functions and procedures and data structures
in the package specification. You cannot grant privileges on only one
procedure or function within a package.

You can overload procedures and functions within a package, declaring multiple programs with the same name. The correct
program to be called is decided at runtime, based on the number or datatypes
of the parameters.

SQl PL/SQL Question 17

Describe the use of %ROWTYPE and %TYPE in PL/SQL

%ROWTYPE associates a variable to an entire table row.

The %TYPE associates a variable with a single column type.

SQl PL/SQL Question 18

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE returns the current database error number. These error numbers are all negative, except NO_DATA_FOUND, which returns +100.

SQLERRM returns the textual error message.. These are used in exception handling.

SQl PL/SQL Question 19

How can you find within a PL/SQL block, if a cursor is open?

By the Use of %ISOPEN cursor variable.

SQl PL/SQL Question 20

How do you debug output from PL/SQL?

By the use the DBMS_OUTPUT package.

By the use of SHOW ERROR command, but this only shows errors.

The package UTL_FILE can also be used.

SQl PL/SQL Question 21

What are the types of triggers?

Use Row and Statement Triggers

Use INSTEAD OF Triggers

SQl PL/SQL Question 22

Explain the usage of WHERE CURRENT OF clause in cursors ?

It refers to the latest row fetched from a cursor in an update and delete statement.

SQl PL/SQL Question 23

Name the tables where characteristics of Package, procedure and functions are stored ?

User_objects, User_Source and User_error.

SQl PL/SQL Question 24

What are two parts of package ?

They consist of package specification, which contains the function headers, procedure headers, and externally visible data structures.
The package also contains a package body, which contains the declaration, executable, and exception handling sections of all the bundled
procedures and functions.

SQl PL/SQL Question 25

What are two virtual tables available during database trigger execution ?

The table columns are referred as OLD.column_name and NEW.column_name.

For INSERT only TRIGGERS NEW.column_name values ARE only available.

For UPDATE only TRIGERS OLD.column_name NEW.column_name values ARE only available.

For DELETE only TRIGGERS OLD.column_name values ARE only available.v

SQl PL/SQL Question 26

What is Overloading of procedures ?

REPEATING OF SAME PROCEDURE NAME WITH DIFERENT PARAMETER LIST.

SQl PL/SQL Question 27

What are the return values of functions SQLCODE and SQLERRM ?

SQLCODE returns the latest code of the error that has occurred.

SQLERRM returns the relevant error message of the SQLCODE.

SQl PL/SQL Question 28

Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?

It is not possible.,because of the side effect to transactions. You can use them indirectly by calling procedures or functions .

SQl PL/SQL Question 29

What are the modes of parameters that can be passed to a procedure ?

IN, OUT, IN-OUT parameters.

PL/SQL Sitemap PL/SQL Resources

PL/SQL Triggers

2006-10-25T23:39:00.000-07:00

Database Trigger

A database trigger is a block of code that is automatically executed in response to certain events. Triggers are executed implicitly whenever the triggering event happens.

The triggering event is either a INSERT, DELETE, or UPDATE command.

The timing can be either BEFORE or AFTER, INSTEAD OF trigger

The trigger can be either row-level or statement-level, where the former fires once for each row affected by the triggering statement and the latter fires once for the whole statement.

Example

Example of creating a trigger based on the following two tables:

CREATE TABLE T1 (a INTEGER);

CREATE TABLE T2 (b INTEGER);

We will create a trigger that may insert a tuple into T2 when a tuple is inserted into T1. The trigger checks if the inserted row in T1 is has a value less than 5 only then a tuple is inserted in T2.

CREATE TRIGGER tr1

AFTER INSERT ON T1

REFERENCING NEW AS newRow

FOR EACH ROW

WHEN (newRow.a <= 5)

BEGIN

INSERT INTO T2 VALUES(:newRow.a);

END tr1;

run;

Displaying Trigger Errors

If we get a message

Warning: trigger created with compilation errors.

you can check the error messages with:

show errors trigger ;

You can also type, SHO ERR (SHOW ERRORS) to see the most recent compilation error.

Viewing Defined Triggers

To view all the defined triggers, use:

select name_of_trigger from user_triggers;

For more details on a particular trigger:

select trigger_type, triggering_event, table_name, referencing_names, trigger_body

from user_triggers

where trigger_name = '< name_of_trigger >';

Disabling Triggerss

To disable or enable a trigger:

alter trigger < name_of_trigger > {disable|enable};

Mutating Table Errors

Permissions

For creating triggers you should have create trigger privilege :

Example

Grant create trigger to John;

Sample table:

create table t1 (

id int,

name varchar(10),

primary key(id)

);

Before insert trigger:

Create or replace

Trigger tr1

Before

Insert

On t1

For each row

Begin

Dbms_output.put_line(’before insert of ’ || :new.name);

End;

We will insert some data to see if our triggers work or not:

insert into t1(id,name) values (1,’sam’);

Result:

before insert of sam

After insert trigger:

Create or replace

Trigger tr1

After

Insert

On t1

For each row

Begin

Dbms_output.put_line(’After insert of ’ || :new.name);

End;

We will insert some data to see if our triggers work or not:

insert into t1(id,name) values (1,’sam’);

Result:

After insert of sam

Before Update Statement Trigger:

create or replace

trigger tr1

before update

on t1

begin

dbms_output.put_line(’before updating some names(s)’);

end;

create or replace

trigger tr1

before update

on t1

for each row

begin

dbms_output.put_line(’before updating ’ ||

(:old.name) || ’ to ’ ||

to_char(:new.name));

end;

IF statements

create or replace

trigger tr1

before insert or update or delete on t1

for each row

begin

if inserting then

dbms_output.put_line(’inserting : ’ || :new.name);

elsif updating then

dbms_output.put_line(’updating : ’ ||

:old.name || ’ to ’ || :new.name);

elsif deleting then

dbms_output.put_line(’deleting : ’ || :old.name);

end if;

end;

We will insert some data to see if our triggers work or not:

insert into t1(id,name) values (1,’sam’);

Result:

inserting : sam

update t1 set name = ’g’ where name = ’a’;

updating: g to s

Working with Views

we will create a view (of t1 table):

CREATE OR REPLACE

VIEW t1_VIEW AS

SELECT NAME FROM t1;

Now, we know that updating (or inserting) into a view is kind of pointless; however, we can
provide this functionality using a trigger!

Example:

CREATE OR REPLACE

TRIGGER t1_VIEW_INSERT

INSTEAD OF INSERT ON t1_VIEW

FOR EACH ROW

BEGIN

DBMS_OUTPUT.PUT_LINE(’INSERTING: ’ || :NEW.NAME);

-- we can also do

-- INSERT INTO t1(ID,NAME) VALUES (N,:NEW.NAME);

END;

When we do an insert statement on t1_VIEW:

INSERT INTO t1_VIEW(NAME) VALUES (’ poly’);

Which produces the result:

INSERTING: poly

The trigger will be fired when someone will try to insert a value into a VIEW.

PL/SQL Sitemap PL/SQL Resources

PL/SQL Cursors

2006-10-25T22:19:00.000-07:00

Implicit Cursors

Implicit cursors are automatically created and used by Oracle every time you issue a select statement in PL/SQL. If you use an Implicit cursors, Oracle will perform the open, fetches, and close for you automatically. Implicit cursors are used in statements that return only one row. If the SQL statement returns more than one row, an error will occur.

In the following PL/SQL code block, the select statement makes use of an implicit cursor:

Begin

Update emp

Set empno=empno

Where 1=2;

Dbms_output.put_line(sql%rowcount ||' '|| ' rows are affected by the update statement');

End;

SELECT SUM (sal) INTO TOTAL

FROM emp

WHERE depno = 10;

Cursor For loops

Normally when you use explicit cursors, cursors have to be opened, the data has to be fetched, and finally the cursor needs to be closed. A cursor for loop automatically performs the open, fetch, and close procedures, which simplifies your job.

Declare

Cursor emp_cursor is

Select empno, ename,sal

From emp;

V_emp emp%RowType;

Begin

For emp_info IN emp_cursor LOOP

Dbms_output.put_line ('Employee id : '||emp_info.empno||'Employee

name : '|| emp_info.ename||'Employee salary :'||emp_info.sal);

End LOOP;

End;

Explicit Cursors

Explicit cursors are created by the programmer, and with these you can do operations on a set of rows, which can be processed one by one. You use explicit cursors when you are sure that the SQL statement will return more than one row. You have to declare an explicit cursors in the declare section at the beginning of the PL/SQL block. Once you declare your cursor, the explicit cursors will go through these steps:

Declare. This clause initializes the cursor into memory.

Open The previously declared cursor is now open and memory is allotted.

Fetch The previously declared and opened cursor can now access data;

Close The previously declared, opened, and fetched cursor is closed , which also releases memory allocation.

SQL> set serveroutput on;

SQL> Declare

2 Cursor empcursor(empn in number)

3 Is select * from emp where empno=empn;

4

5 Empvar emp%rowtype;

6 Begin

7 Dbms_output.put_line('Getting records for employee no. 7521');

8 Open empcursor(7521);

9 Loop

10 Fetch empcursor into empvar;

11 Exit when empcursor%notfound;

12 Dbms_output.put_line('emp name : ' || empvar.ename);

13 Dbms_output.put_line('emp salary : ' || empvar.sal);

14 End loop;

15 Close empcursor;

16 End;

17 /

Getting records for employee no. 7521

emp name : WARD

emp salary : 1250

PL/SQL procedure successfully completed.

Cursor Attributes

Here are the main cursor attributes:

%ISOPEN It returns TRUE if cursor is open, and FALSE if it is not.

%FOUND It returns TRUE if the previous FETCH returned a row and FALSE if it did not

%NOTFOUND It returns TRUE if the previous FETCH did not return a row and FALSE if it did.

%ROWCOUNT It gives you the number of rows the cursor fetched so far.

PL/SQL Sitemap PL/SQL Resources

PL/SQL Resources

2006-09-19T23:48:00.000-07:00

http://www.sqlzoo.net

http://www.w3schools.com

www.1keydata.com

http://sql-tutorial.net

www.db.cs.ucdavis.edu/teaching/sqltutorial

http://asktom.oracle.com

http://metalink.oracle.com/

http://www.ioug.org/

123 Exchange Links

Pl SQL Tutorials Blog

PL/SQL Sitemap

2006-01-09T00:08:00.000-08:00

PL/SQL Tutorials

1. PL/SQL Introduction
2. PL/SQL Cursors
3. PL/SQL Triggers
4. PL/SQL Interview Questions
5. PL/SQL Resources