plsql document

(1)

PL/SQL

Introduction

The development of database applications typically requires language constructs similar to those that can be found in programming languages such as C, C++, or Pascal. These constructs are necessary in order to implement complex data structures and algorithms. A major restriction of the database language SQL, however, is that many tasks cannot be accomplished by using only the provided language elements.

PL/SQL (Procedural Language/SQL) is a procedural extension of Oracle-SQL that o_ers language constructs similar to those in imperative programming languages. PL/ SQL allows users and designers to develop complex database applications that require the usage of control structures and procedural elements such as procedures, functions, and modules.

The basic construct in PL/SQL is a block. Blocks allow designers to combine logically related (SQL-) statements into units. In a block, constants and variables can be declared, and variables can be used to store query results. Statements in a PL/SQL block include SQL statements, control structures (loops), condition statements (if-then-else), exception handling, and calls of other PL/SQL blocks.

PL/SQL blocks that specify procedures and functions can be grouped into packages. A package is similar to a module and has an interface and an implementation part. Oracle o_ers several predefined packages, for example, input/output routines, file handling, job scheduling etc.

Another important feature of PL/SQL is that it o_ers a mechanism to process query results in a tuple-oriented way, that is, one tuple at a time. For this, cursors are used. A cursor basically is a pointer to a query result and is used to read attribute values of selected tuples into variables. A cursor typically is used in combination with a loop construct such that each tuple read by the cursor can be processed individually.

In summary, the major goals of PL/SQL are to • increase the expressiveness of SQL,

• process query results in a tuple-oriented way, • optimize combined SQL statements,

• develop modular database application programs, • reuse program code, and

• reduce the cost for maintaining and changing applications.

Structure of PL/SQL-Blocks

PL/SQL is a block-structured language. Each block builds a (named) program unit, and blocks can be nested. Blocks that build a procedure, a function, or a package must be named.

(2)

[<Block header>] [declare

<Constants> <Variables> <Cursors>

<User defined exceptions>] begin

<PL/SQL statements> [exception

<Exception handling>] end;

The block header specifies whether the PL/SQL block is a procedure, a function, or a package. If no header is specified, the block is said to be an anonymous PL/SQL block. Each PL/SQL block again builds a PL/SQL statement. Thus blocks can be nested like blocks in conventional programming languages. The scope of declared variables (i.e., the part of the program in which one can refer to the variable) is analogous to the scope of variables in programming languages

such as C or Pascal.

Declarations

Constants, variables, cursors, and exceptions used in a PL/SQL block must be declared in the declare section of that block. Variables and constants can be declared as follows:

<variable name> [constant] <data type> [not null] [:= <expression>];

Valid data types are SQL data types (see Section 1.1) and the data type boolean. Boolean data may only be true, false, or null. The not null clause requires that the declared variable must always have a value di_erent from null. <expression> is used to initialize a variable.

If no expression is specified, the value null is assigned to the variable. The clause constant states that once a value has been assigned to the variable, the value cannot be changed (thus the variable becomes a constant).

Example:

Declare hire date date; /* implicit initialization with null */ job title varchar2(80) := ’Salesman’;

emp found boolean; /* implicit initialization with null */ salary incr constant number(3,2) := 1.5; /* constant */ . . .

begin . . . end;

(3)

type of the column Empno in the relation EMP. Instead of a single variable, a record can be declared that can store a complete tuple from a given table (or query result). For example, the data type DEPT%ROWTYPE specifies a record suitable to store all attribute values of a complete row from the table DEPT. Such records are typically used in combination with a cursor. A field in a record can be accessed using <record name>.<column name>, for example, DEPT.Deptno.

Exception Handling

A PL/SQL block may contain statements that specify exception handling routines. Each error or warning during the execution of a PL/SQL block raises an exception. One can distinguish between two types of exceptions:

• system defined exceptions

• user defined exceptions (which must be declared by the user in the declaration part of a

block where the exception is used/implemented)

System defined exceptions are always automatically raised whenever corresponding errors or warnings occur. User defined exceptions, in contrast, must be raised explicitly in a sequence of statements using raise <exception name>. After the

keyword exception at the end of a block, user defined exception handling routines are implemented. An implementation has the pattern when

<exception name> then <sequence of statements>;

The most common errors that can occur during the execution of PL/SQL programs are handled by system defined exceptions. The table below lists some of these exceptions with their names and a short description.

Exception name Number Remark

CURSOR

ALREADY OPEN

ORA-06511 You have tried to open a cursor which is already open

INVALID CURSOR

ORA-01001 Invalid cursor operation such as fetching from a closed cursor

NO DATA FOUND ORA-01403 A select . . . into or fetch statement returned no tuple

TOO MANY

ROWS ORA-01422 A select . . . into statement returned morethan one tuple

(4)

Examples

Q1) Write a PL/SQL programme to add two numbers ?

SQL> declare

a number(10) := 20; b number(10) := 30; c number(10) ; begin

c := a + b;

dbms_output.put_line('a value'||a||'b value' ||b||'c value'||c);

end; /

PL/SQL procedure successfully completed

We can not see the ouput until we set the server output on

SQL> set serveroutput on SQL> /

A value 20 b value 30 c value 50

Q2) Write a PL/SQL programme to add two numbers by accepting the values from keyboard?

SQL> declare

a number(10) := &a; b number(10) := &b; c number(10) ; begin

c := a + b;

dbms_output.put_line('a value'||a||'b value' ||b||'c value'||c);

end; /

Enter value for a: 10

old 2: a number(10) := &a; new 2: a number(10) := 10; Enter value for b: 20

old 3: b number(10) := &b; new 3: b number(10) := 20; a value10b value20c value30

(5)

Q3) Write a PL/SQL programe to extract sailors sid and sname for a given sid?

SQL> declare a number(10); b varchar2(10); c number(10) := &c; begin

select sid,sname into a,b from sailors where sid = c; dbms_output.put_line('sailors id = '||a||'sailors Name= '||b); exception

when no_data_found then

dbms_output.put_line('no such sailor found with sid = '||c); end;

/

Enter value for c: 22

old 4: c number(10) := &c; new 4: c number(10) := 22;

sailors id = 22 sailors name = Dustin

PL/SQL procedure successfully completed.

SQL>/

old 4: c number(10) := &c; new 4: c number(10) := 20; no such sailor found with sid = 20

Initially any application will have to connect to the database in order to access the data. It is important to point out that when a user is issuing DML statements in an application the changes are not visible to the user until a COMMIT or ROLLBACK has been issued.

COMMIT- makes events within a transaction permanent ROLLBACK – Erases events within a transaction

SAVEPOINT – We can control transactions by using save points. These break transactions into pieces so that we can roll back to a specified save point with out roll backing the total transaction.

SYNTAX: savepoint name;

(6)

SQL> create table empl(eid number(10),ename varchar(10),desg archar(10),salary number(5));

Table created

SQL> begin savepoint a;

insert into empl values (101,'abc','acc',2000); savepoint b;

insert into empl values (102,'bcd','clk',3000); savepoint c;

insert into empl values (103,'sde','gar',1200); rollback to c;

end; /

PL/SQL procedure successfully completed

SQL> select * from empl;

EID ENAME DSEG SALARY

101 abc acc 2000

102 Bcd clk 3000

Here only two records are inserted because we are giving savepoints in above

(7)

CONDITIONAL CONTROL STATEMNETS

In PL/SQL there are three types of conditional control : IF, ELSIF and CASE

IF – THEN Statement

This is the most basic kind of a conditional control and has the following structure

If Condition Then Statement 1; ….

Statement 2; End If;

The reserved word IF marks the beginning of the IF statement.

Example:

Q4) Write a PL/SQL block to swap two numbers when the first number is greater than second number ?

SQL> declare

a number(10) := &a; b number(10) := &b; c number(10); begin

dbms_output.put_line('a value ='||a||' b value ='||b); if a>b then

c := a; a := b; b := c; end if;

dbms_output.put_line('a value ='||a||' b value ='||b); end;

/

old 2: a number(10) := &a; new 2: a number(10) := 20; Enter value for b: 10

old 3: b number(10) := &b; new 3: b number(10) := 10; a value =20 b value =10 a value =10 b value =20

(8)

IF – THEN – ELSE

This statement enables you to specify two groups of statements One group of statements is executed when the condition evaluates to TRUE and the other group of statements is executed when the condition evaluates to FALSE.

If Condition Then Statement 1; ELSE

Statement 2; End If;

Statement 3; Example:

Q5) Write a PL/SQL block to test whether the given number is odd or even

SQL> declare

2 a number(10) := &a; 3 begin

4 if mod(a,2)=0 then

5 dbms_output.put_line('a value is even'); 6 else

7 dbms_output.put_line('a value is odd'); 8 end if;

9 end; 10 /

old 2: a number(10) := &a; new 2: a number(10) := 44; a value is even

PL/SQL procedure successfully completed. SQL> /

old 2: a number(10) := &a; new 2: a number(10) := 33; a value is odd

(9)

ELSIF Statement:

This statement has the following structure

If Condition 1 Then Statement 1; ELSIF Condition 2 Then

Statement 2; ELSIF Condition 3 Then

Statement 3; …

ELSE

Statement 4; END IF;

Example:

Q6) Write a PL/SQL block to find the grade of sailor for a given sid 10, 9, 8 – Grade A

7, 6, 5 – Grade B other – Grade C

SQL> declare

2 a number(10) := &a; 3 c number(10);

4 begin

5 select rating into c from sailors where sid = a; 6 if c in (10,9,8) then

7 dbms_output.put_line('sailor '||a||' has grade A'); 8 elsif c in (7,6,5) then

9 dbms_output.put_line('sailor '||a||' has grade B'); 10 else

11 dbms_output.put_line('sailor '||a||' has grade C'); 12 end if;

13 end; 14 15 /

old 2: a number(10) := &a; new 2: a number(10) := 22; sailor 22 has grade B

(10)

CASE:

A case statement has the following structure:

CASE SELECTOR

WHEN EXPRESSION 1 STATEMENT 1; WHEN EXPRESSION 1 STATEMENT 1; …..

WHEN EXPRESSION 1 STATEMENT 1; ELSE STATEMENT N+1;

END CASE;

The reserved word CASE marks the beginning of the case statement. A selector is a value that determines which WHEN clause should be executed.

Example:

Q7) Write a PL/SQL block to print the day name for a given date?

SQL> declare

2 a date := '&a'; 3 b char(10); 4 begin

5 b := to_char(a,'D'); 6 case b

7 when '1' then

8 dbms_output.put_line('today is sunday'); 9 when '2' then

10 dbms_output.put_line('today is monday'); 11 when '3' then

12 dbms_output.put_line('today is thuesday'); 13 when '4' then

14 dbms_output.put_line('today is wednesday'); 15 when '5' then

16 dbms_output.put_line('today is thrusday'); 17 when '6' then

18 dbms_output.put_line('today is friday'); 19 when '7' then

20 dbms_output.put_line('today is saturday'); 21 end case;

22 end; 23 /

Enter value for a: 10-mar-09 old 2: a date := '&a';

new 2: a date := '10-mar-09'; today is thuesday

(11)

ITERATIVE CONTROL

In PL/SQL there are three types of loops : Simple LOOP, WHILE loops and Numeric FOR loop

A simple loop, as you can see from its name, is the most basic kind of loop and has the following structure:

LOOP

STATEMENT 1; STATEMENT 2; …….

STATEMENT N; END LOOP;

The reserved word LOOP marks the beginning of the simple loop. Statement 1 through N are a sequence of statements that is executed repeatedly.

EXIT statement causes a loop to terminate when exit condition evaluates to TRUE.

LOOP

STATEMENT 1; STATEMENT 2; IF CONDITION THEN

EXITL; END IF; …….

Example:

Q8) Write a PL/SQL block to print number from 1 to 5 using loop statements

SQL> declare

2 a number :=0; 3 begin

4 loop 5 a := a+1;

6 dbms_output.put_line('a value'||a); 7 if a>5 then

8 exit; 9 end if; 10 end loop; 11 end; 12 /

(12)

a value4 a value5 a value6

Note: here numbers are printed 1 to 6 because this loop acts as do-while so it executes the statements and then check the condition next.

LOOP

STATEMENT 1; STATEMENT 2;

EXIT WHEN CONDITION; …….

Q8) Write a PL/SQL block to print number from 1 to 5 using loop statements

SQL> declare

2 a number :=0; 3 begin

4 loop 5 a := a+1;

6 dbms_output.put_line('a value'||a); 7 exit when a>5 ;

10 end loop; 11 end; 12 /

a value1 a value2 a value3 a value4 a value5 a value6

(13)

WHILE LOOPS:

A while loop has the following structure

WHILE CONDITION LOOP STATEMNET 1; STATEMNET 2; ……

STATEMNET N; End loop;

The reserved word WHILE marks the beginning of a loop construct. The word CONDITION is the test condition of the loop that evaluates to TRUE or FALSE.

Example:

Q9) Write a PL/SQL block to print number from 1 to 5 using while loop statements

SQL> declare

2 a number:=1; 3 begin

4 while a<5 loop

5 dbms_output.put_line('a value'||a); 6 a := a+1;

7 end loop; 8* end; SQL> / a value1 a value2 a value3 a value4

NUMERIC FOR LOOP:

A numeric FOR loop is called numeric because it requires an integer as its terminating value. Its structure is as follows.

FOR loop_counter IN[REVERSE] Lower_limit..upper_limit LOOP STATEMENT 1;

STATEMENT 2; ……

(14)

Example:

Q10) Write a PL/SQL block to print number from 1 to 5 using for loop statements

SQL> begin

2 for a in reverse 1..5 loop

3 dbms_output.put_line('a value'||a); 4 end loop;

5 end; 6 /

a value5 a value4 a value3 a value2 a value1

Examples:

Q11) To write a PL/SQL block to find Sum of Digits of a given Number.

SQL> DECLARE num number(5); rem number(5); sm number(5):=0; num1 number(5); BEGIN

num:=&num; num1:=num; while(num>0) loop rem:=mod(num,10); sm:=sm+rem;

num:=trunc(num/10); end loop;

dbms_RESULT.put_line('SUM OF DIGITS OF '||num1||' IS: '||sm); end;

/ RESULT:

SQL> /

Enter value for num: 123 old 7: num:=&num; new 7: num:=123;

SUM OF DIGITS OF 123 IS: 6

SQL> /

(15)

old 7: num:=&num; new 7: num:=456;

SUM OF DIGITS OF 456 IS: 15

Q12) To write a PL/SQL block to Generate Fibonacci Series

SQL> DECLARE num number(5); f1 number(5):=0; f2 number(5):=1; f3 number(5); i number(5):=3; BEGIN

num:=&num;

dbms_RESULT.put_line('THE FIBONACCI SERIES IS:'); dbms_RESULT.put_line(f1);

dbms_RESULT.put_line(f2); while(i<=num) loop

f3:=f1+f2;

dbms_RESULT.put_line(f3); f1:=f2;

f2:=f3; i:=i+1; end loop; END; / RESULT:

SQL> /

Enter value for num: 10 old 8: num:=&num; new 8: num:=10;

THE FIBONACCI SERIES IS: 0

1 1 2 3 5 8 13 21 34

(16)

Q13) To write a PL/SQL block to Check the Given String is Palindrome or Not.

SQL> DECLARE

name1 varchar2(20); name2 varchar2(20); l number(5);

BEGIN

name1:='&name1'; l:=length(name1); while l>0 loop

name2:=name2||substr(name1,l,1); l:=l-1;

end loop;

dbms_RESULT.put_line('REVERSE OF STRING IS:'||NAME2); if(name1=name2) then

dbms_RESULT.put_line(name1||' IS PALINDROME '); else

dbms_RESULT.put_line(name1||' IS NOT PALINDROME '); end if;

END; / RESULT

Enter value for name1: LIRIL old 6: name1:='&name1'; new 6: name1:='LIRIL';

REVERSE OF STRING IS:LIRIL LIRIL IS PALINDROME

SQL> /

Enter value for name1: MADAM old 6: name1:='&name1';

new 6: name1:='MADAM';

REVERSE OF STRING IS:MADAM MADAM IS PALINDROME

(17)

USER DEFINED EXCEPTIONS

Some type of errors can not be identified by in built exception handlers so we can design our own exceptions to handle situations

To use a exception first we must declare it in the declaration section of PL/SQL block.

DECLARE

Exception_name EXCEPTION;

A user defined exception must be raised explicitly i.e., you need to specify in your program under which circumstances an exception must be raised.

DECLARE

Exception_name EXCEPTION; BEGIN

….

IF CONDITION THEN

RAISE exception_name; ELSE

…. END IF;

The exception statements associated with this exception are specified in the exception handling section of the block.

DECLARE

Exception_name EXCEPTION; BEGIN

….

IF CONDITION THEN

RAISE exception_name; ELSE

…. END IF; EXCEPTION

WHEN exception_name THEN

ERROR-PROCESSING STATEMENT; END;

Example

Q14) Write a PL/SQL block to handle exception when the given sid is negative using user defined exception?

SQL> declare

2 a number(10); 3 b varchar2(10); 4 c number(10) := &c;

5 d exception; --exception variable declaration 6 begin

(18)

8 raise d; --raising an exception 9 else

10 select sid,sname into a,b from sailors where sid = c; 11 dbms_output.put_line('sailors id = '||a||'sailors Name= '||b); 12 end if;

13 exception

14 when d then --action to be taken

15 dbms_output.put_line('sailor id can not be negative'); 16 when no_data_found then

17 dbms_output.put_line('no such sailor found with sid = '||c); 18 end;

19 /

old 4: c number(10) := &c; new 4: c number(10) := 22; sailors id = 22sailors Name= dustin

SQL> /

Enter value for c: -22 old 4: c number(10) := &c; new 4: c number(10) := -22; sailor id can not be negative

RAISE_APPLICATION_ERROR

It is a special built in procedure provided by oracle. This procedure allows programmers to create meaningful error messages for a specific application. This works with user defined exceptions

RAISE_APPLICATION_ERROR(error_number, error_message);

Q15) Write a PL/SQL block to handle exception when the given sid is negative using RAISE_APPLICATION_ERROR?

SQL> declare

2 a number(10); 3 b varchar2(10); 4 c number(10) := &c; 5 begin

6 if c<0 then

7 raise_application_error(-20000,'sailor id can not be negative'); 8 else

9 select sid,sname into a,b from sailors where sid = c;

10 dbms_output.put_line('sailors id = '||a||'sailors Name= '||b); 11 end if;

(19)

13 when no_data_found then

14 dbms_output.put_line('no such sailor found with sid = '||c); 15 end;

16 /

old 4: c number(10) := &c; new 4: c number(10) := 22; sailors id = 22sailors Name= dustin

SQL> /

Enter value for c: -22 old 4: c number(10) := &c; new 4: c number(10) := -22; declare

*

ERROR at line 1:

(20)

PROCEDUREDS:

Modular code :

A PL/SQL module is any complete logical unit of work. There are four types of PL/SQL modules: 1) anonymous blocks that are run with a text script( you have used until now), 2) Procedures, 3) Functions, and 4) Packages.

There are two main benefits to using modular code: 1) it is more reusable and 2) it is more manageable.

A procedure is a module performing one or more actions: it does not need to return any value. The syntax for creating a procedure is

CREATE OR REPLACE PROCEDURE name

[(PARAMETER 1 {IN,OUT,INOUT} DATATYPE(SIZE), PARAMETER 2 {IN,OUT,INOUT} DATATYPE(SIZE),…. PARAMETER N {IN,OUT,INOUT} DATATYPE(SIZE))] AS

[local declaration] BEGIN

Executable statements [EXCEPTION

exception handler] END [name];

Example

Q16) create a procedure to add two number and call the block with a PL/SQL block?

SQL> create or replace procedure sum(a in number,b in number) 2 is

3 c number := 1; 4 begin

5 c := a+b;

6 dbms_output.put_line('c value '||c); 7 end;

8 /

Procedure created.

SQL> declare

2 a number := &a; 3 b number := &b; 4 begin

(21)

Enter value for a: 10 old 2: a number := &a; new 2: a number := 10; Enter value for b: 20 old 3: b number := &b; new 3: b number := 20; c value 30

Q17) Create a procedure to add two number and return the value to a PL/SQL block?

SQL> create or replace procedure sum1(a in number,b in number,d out number)

2 is

3 c number := 1; 4 begin

5 c := a+b; 6 d:=c; 7 end; 8 /

Procedure created.

SQL> declare

2 a number := &a; 3 b number := &b; 4 d number; 5 begin

6 sum1(a,b,d);

7 dbms_output.put_line('d value '||d); 8 end;

9 /

Enter value for a: 10 old 2: a number := &a; new 2: a number := 10; Enter value for b: 30 old 3: b number := &b; new 3: b number := 30; d value 40

Q18) Create a procedure to accept sailors sid and return age of sailor to a PL/SQL block?

SQL> create or replace procedure sail(a in number,b out number) 2 is

(22)

5 select age into c from sailors where sid = a; 6 b := c;

7 exception

9 dbms_output.put_line('no such sailors'); 10 end;

11 /

Procedure created.

SQL> declare

2 a number := &a; 3 b number; 4 begin 5 sail(a,b);

6 dbms_output.put_line('sailors with '||a||'has age '||b); 7 end;

8 /

Enter value for a: 64 old 2: a number := &a; new 2: a number := 64; sailors with 64has age 35

(23)

FUNCTION:

The syntax for creating a function is as follows:

CREATE OR REPLACE FUNCTION name

[(PARAMETER 1 {IN,OUT,INOUT} DATATYPE(SIZE), PARAMETER 2 {IN,OUT,INOUT} DATATYPE(SIZE),…. PARAMETER N {IN,OUT,INOUT} DATATYPE(SIZE))] RETURN datatype

IS

[local declaration] BEGIN

Executable statements [EXCEPTION

exception handler] END [name];

The function does not necessarily have any parameters, but it must have a RETURN value declared in the header, and it must return values for all the varying possible execution streams.

Q19) Create a function to add two number and return the value to a PL/SQL block?

SQL> create or replace function f(a in number) 2 return number

3 as

4 b number; 5 begin 6 b:=10; 7 b:=a+b; 8 return b; 9 end; 10 /

Function created.

SQL> declare

2 a number:=10; 3 c number; 4 begin 5 c := f(a);

6 dbms_output.put_line('c value'||c); 7 end;

8 / c value20

(24)

Q20) Create a function to accept sailors sid and return age of sailor to a PL/SQL block?

SQL> create or replace function sailf(a in number) 2 return number

3 as

4 b number; 5 begin

6 select age into b from sailors where sid=a; 7 return b;

8 exception

10 dbms_output.put_line('no such sailors'); 11 end;

12 /

Function created.

SQL> declare

2 a number:= &a; 3 c number; 4 begin 5 c := sailf(a);

6 dbms_output.put_line('sailor with sid '||a||'has age '||c); 7 end;

8 /

Enter value for a: 58 old 2: a number:= &a; new 2: a number:= 58; sailor with sid 58has age 35

(25)

CURSOR:

A cursor is a handle or pointer to the context area. Through the cursor a PL/SQL program can control the context area and what happens to it as the statements is processed.

1) Cursor allow you to fetch and process rows returned by a SELECT statement, one row at a time.

2) A cursor is named so that it can be referenced.

Types of cursors:

1. An implicit cursor is automatically declared by oracle every time an SQL statement is executed. The user will not be aware of this happening and will not be able to control or process the information in an implicit cursor. 2. An explicit cursor is defined by the program for any query that returns

more than one row of data. That means the programmer has declared the cursor within the PL/SQL code block. This declaration allows for the application to sequentially process each row of data as it is returned by the cursor.

IMPLICIT CURSOR

In order to better understand the capabilities of an explicit cursor, you first need to run through the process of an implicit cursor.

1) Any given PL/SQL block issues an implicit cursor when ever an SQL statement is executed, as long as an explicit cursor does not exist for that SQL statement.

2) A cursor is automatically associated with every DML statement.

3) All UPDATE and DELETE statements have cursors that identify the set od rows that will be affected by the operations.

4) An INSERT statement needs a place to receive the data that is to be inserted in the database; the implicit cursor fulfills this need.

5) The most recently opened cursor is called the ‘SQL%’ cursor.

EXPLICIT CURSOR

The only means of generating an explicit cursor is for the cursor to be named in the DECLARE section of the PL/SQL block.

1) Declaring the cursor defines the name of the cursor and associatedit with a SELECT statements CURSOR cursor_name IS SELECT statement 2) Opening the cursor is to process the select statement and set a active

pointer to the first row OPEN cursor_name;

3) Fetching cursor into PL/SQL local variables, these variables are declared as ROWTYPE in declare section FETCH cursor_name INTO PL/SQL variables

(26)

Explicit cursor attributes

Cursor Attribute Syntax Explanation

%NOTFOUND Cursor_name%NOTFOUND A Boolean attribute that returns TRUE if the previous FETCH did not return a row and FALSE if it did

%FOUND Cursor_name%FOUND A Boolean attribute that returns TRUE if the previous FETCH did return a row and FALSE if it did not

%ROWCOUNT Cursor_name%ROWCOUNT # of records fetched from a cursor at that point in time

%ISOPEN Cursor_name%ISOPEN A Boolean attribute that returns TRUE if cursor is open, FALSE if it is not.

Q22) Create a cursor which fetches data from sailors table whose rating is greater than 5?

SQL> declare

2 cursor sail is select sid,sname,age from sailors where rating>5; 3 s sail%rowtype;

4 begin 5 open sail; 6 loop

7 fetch sail into s;

8 exit when sail%notfound;

9 dbms_output.put_line('sailor sid = '||s.sid||' name = '||s.sname||' age = '

||s.age); 10 end loop; 11 close sail; 12 end; 13 /

sailor sid = 64 name = horatio age = 35 sailor sid = 22 name = dustin age = 45 sailor sid = 31 name = lubber age = 55.5 sailor sid = 32 name = andy age = 25.5 sailor sid = 58 name = rusty age = 35 sailor sid = 71 name = zobra age = 16 sailor sid = 74 name = horatio age = 35

(27)

Q23) Create a cursor which fetches data from boats table? SQL> declare

2 cursor bt is select * from boats; 3 b bt%rowtype;

4 begin 5 open bt; 6 loop

7 fetch bt into b;

8 exit when bt%notfound;

9 dbms_output.put_line('boat bid = '||b.bid||' name = '||b.bname||' color = ‘||b.color);

10 end loop; 11 close bt; 12* end; SQL> /

boat bid = 101 name = interlake color = blue boat bid = 102 name = interlake color = red boat bid = 103 name = clipper color = green boat bid = 104 name = marine color = red

Q24) Create cursor which fetches data from reserves table where sid is22?

SQL> declare

2 cursor reser is select * from reserves where sid=22; 3 r reser%rowtype;

4 begin 5 open reser; 6 loop

7 fetch reser into r;

8 exit when reser%notfound;

9 dbms_output.put_line('reserve sid = '||r.sid||' bid = '||r.bid||' day = '|| r.day);

10 end loop; 11 close reser; 12* end; SQL> /

reserve sid = 22 bid = 101 day = 10-OCT-98 reserve sid = 22 bid = 102 day = 10-OCT-98 reserve sid = 22 bid = 103 day = 10-SEP-98 reserve sid = 22 bid = 104 day = 10-JUL-98

(28)

Triggers

The Oracle RDBMS has the ability to store procedures within the data dictionary and execute procedures in the RDBMS. Procedures (Program Units) are written in the PL/ SQL language (Procedural Language), which is proprietary to Oracle. PL/SQL runs in both the database engine as well as in many of Oracle's development tools such as Oracle Developer.

It is common practice to store general business rule checking in procedures. This allows applications to check data validity before a transaction is submitted to the database. Triggers can also call the procedures to check data at the database level. Since the business rules are coded in a single set of procedures, maintenance of this code is simplified. In this section, we will introduce the syntax for creating triggers and demonstrate the use of a trigger to enforce a business rule.

Trigger Syntax

Creating a trigger is accomplished with the CREATE TRIGGER statement. There are numerous options for a trigger that specify when the trigger should fire. These options include:

 The SQL statement (INSERT, UPDATE, DELETE, SELECT) that causes the event. An event can include more than one SQL statement per trigger.

 The timing when the trigger code is executed. Options here include

o BEFORE - The trigger code is executed before the effects of the SQL

statement are put into place.

o INSTEAD OF - The trigger code is executed instead of the normal

SQL statement.

o AFTER - The trigger code is executed after the normal SQL statement

is processed.

 Some SQL statements such as UPDATE, DELETE and SELECT may affect more than one row. Triggers may be specified to fire once for the SQL statement or once for each row affected by the SQL statement.

Example:

To write a TRIGGER to ensure that SAILORS TABLE does not contain duplicate of null values in SID column.

CREATE OR RELPLACE TRIGGER trig1 before insert on sailors

for each row DECLARE

a number; BEGIN

if(:new.sid is Null) then

(29)

else

select count(*) into a from sailors where sid=:new.sid; if(a=1) then

raise_application_error(-20002,'error:: cannot have duplicate sid'); end if;

end if; END;

Trigger created.

SQL>select * from sailors;

SID SNAME RATING AGE

22 Dustin 7 45.0

29 Brutus 1 33.0

31 Lubber 8 55.5

32 Andy 8 25.5

58 Rusty 10 35.0

64 Horatio 7 35.0

71 Zobra 10 16.0

74 Horatio 9 35.0

85 Art 3 25.5

95 Bob 3 63.5

SQL> insert into sailors values(&sid,'&sname',&rating,&age); Enter value for sid: null

Enter value for sname: mark Enter value for rating: 7 Enter value for age: 35.5

old 1: insert into sailors values((&sid,'&sname',&rating,&age) new 1: insert into sailors values(null,’mark',7,35.5)

insert into sailors values(null,’mark',7,35.5) *

ERROR at line 1:

ORA-20001: error::sid cannot be null ORA-06512: at "SCOTT.TRIG1", line 5

ORA-04088: error during execution of trigger 'SCOTT.TRIG1' SQL> /

Enter value for deptnp: 22 Enter value for dname: mana Enter value for rating: 9 Enter value for age:25.5

old 1: insert into sailors values((&sid,'&sname',&rating,&age) new 1: insert into sailors values(22,’mana’,9,25.5)

insert into sailors values(22,’mana’,9,25.5)) *

ERROR at line 1:

(30)

ORA-04088: error during execution of trigger 'SCOTT.TRIG1' SQL> /

Enter value for deptnp: 30 Enter value for dname: mana Enter value for rating: 9 Enter value for age:25.5

old 1: insert into sailors values((&sid,'&sname',&rating,&age) new 1: insert into sailors values(30,’mana’,9,25.5)

I row created

SQL>select * from sailors;

SID SNAME RATING AGE

22 Dustin 7 45.0

29 Brutus 1 33.0

31 Lubber 8 55.5

32 Andy 8 25.5

58 Rusty 10 35.0

64 Horatio 7 35.0

71 Zobra 10 16.0

74 Horatio 9 35.0

85 Art 3 25.5

95 Bob 3 63.5

30 mana 9 25.5

A sample PL/SQL programs to practice.

Q.25. Write a PL/SQL program to find the sum of 2 numbers? To start PL/SQL Program as follows:

1. ed <file name>.extension (sql)

2. After wrote a program to execute as “ start <file name>.extention(sql)” at ‘sql’ promt in sql editor.

SQL> ed sum.sql

Declare a number(2); b number(2); c number(2); Begin

(31)

c:=a+b;

Dbms_Output.Put_Line('The Total sum is :'||c); End;

/

SQL> start sum.sql

Input truncated to 1 characters Enter value for a: 2

old 6: a:=&a; new 6: a:=2; Enter value for b: 3 old 7: b:=&b; new 7: b:=3; The Total sum is :5

Q. 26. Write a PL/SQL program to insert a row in dept table? SQL> ed Insertrow.sql

declare

n number(2):=&n; mname varchar(20); mloc varchar(20); Begin

insert into dept values(n,'&mname','&mloc'); commit;

End; / Output:

SQL> start s2.sql

Input truncated to 1 characters Enter value for n: 12

old 2: n number(2):=&n; new 2: n number(2):=12; Enter value for mname: kill Enter value for mloc: hyd

old 6: insert into dept values(n,'&mname','&mloc'); new 6: insert into dept values(n,'kill','hyd');

(32)

SQL> select *from dept;

DEPTNO DNAME LOC --- - 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 12 kill hyd

Q.27. Write a PL/Sql program to the given number is -ve or +ve or Zero ? SQL> ed positive .sql

declare

a number(3):=&a; begin

if a<0 then

dbms_output.put_line('-ve'||a); elsif a>0 then

dbms_output.put_line('+ve'||a); else

dbms_output.put_line('zero'||a); end if;

end; /

Output:

SQL> start positive.sql;

old 2: a number(3):=&a; new 2: a number(3):=2; +ve2

PL/SQL procedure successfully completed.

SQL> /

Enter value for a: -2

(33)

SQL> /

old 2: a number(3):=&a; new 2: a number(3):=0; zero0

Q.28. Write a PL/Sql program to find the Biggest number between two numbers ?

SQL> ed Biggest.sql declare

a number(3):=&a; b number(3):=&b; begin

if a>b then

dbms_output.put_line(' "B" is Big:'||' '||b); else

dbms_output.put_line('"a" is Small:'||' '||a); end if;

end; /

Output:

SQL> start Biggest

old 2: a number(3):=&a; new 2: a number(3):=2; Enter value for b: 5

old 3: b number(3):=&b; new 3: b number(3):=5; "a" is Small: 2

(34)

declare

a number(3):=1; begin

loop

dbms_output.put_line(a); a:=a+1;

exit when a>10; end loop;

end; /

Output:

SQL> start one_to_ten

Input truncated to 1 characters 1

2 3 4 5 6 7 8 9 10

Q.30. Write a PL/SQL Program to print a word as character by character ? SQL> ed character.sql

declare

string varchar2(20):=&string; n number(2):=1;

begin

while n<=length(string) loop

dbms_output.put_line( substr(string,n,1) ); n:=n+1;

end loop; end;

/

Output:

SQL> start character

(35)

Enter value for string: 'naga kishore' old 2: string varchar2(20):=&string; new 2: string varchar2(20):='naga kishore'; n

a g a k i s h o r e

Q.31. Write a PL/SQL Program to display name of the given employee number?

SQL> ed employee.sql declare

name varchar2(20); n number(4):=&n; begin

select ename into name from emp where empno=n; dbms_output.put_line(name );

end; /

Output:

SQL> start employee

old 3: n number(4):=&n; new 3: n number(4):=7900; JAMES

SQL> /

Enter value for n: 7369

old 3: n number(4):=&n; new 3: n number(4):=7369; SMITH

(36)

Q .32. Write a PL/SQL Program to find whether the given number is ODD or EVEN ?

SQL> ed ODD_EVEN.sql

declare

n number(4):=&n; begin

if mod(n,2)=0 then

dbms_output.put_line(' The Given number is : EVEN '); else

dbms_output.put_line(' The Given number is : ODD '); end if;

end; /

Output:

SQL> start ODD_EVEN

old 3: n number(4):=&n; new 3: n number(4):=5; The Given number is : ODD

SQL> /

old 3: n number(4):=&n; new 3: n number(4):=2; The

Given number is : EVEN

SQL> /

old 3: n number(4):=&n; new 3: n number(4):=7; The Given number is : ODD

(37)

declare

time1 varchar2(10):=to_char(sysdate,'hh:mm:ss'); date1 varchar2(10):=to_char(sysdate,'dd-mm-yyyy'); begin

dbms_output.put_line('date:'||date1||'and'||' '|| time:'|| time1); end;

/

Output:

SQL> start date

Input truncated to 1 characters date:31-12-2010and\n time:07:12:44