Microsoft.NET

……………………………………………….Expertise in .NET Technologies

Basics of PL/SQL – Part 1

Posted by Ravi Varma Thumati on November 10, 2009

About PL/SQL      

PL/SQL is an Oracle’s Procedural Language (PL) extension to Structured Query Language (SQL), that Oracle developed as an extension to standard SQL in order to provide a way to execute procedural logic on the database. PL/SQL provides a mechanism for developers to add a procedural component at the server level. It has been enhanced to the point where developers now have access to all the features of a full-featured procedural language at the server level. It also forms the basis for programming in Oracle’s continually evolving set of client/server development tools, most notably Developer/2000.

It, too, usually runs on the database server, but some Oracle products such as Developer 2000 also contain a PL/SQL engine that resides on the client. Thus, you can run PL/SQL code on the either the client or the server depending on which is more appropriate for the task at hand. Unlike SQL, PL/SQL is procedural, not declarative. This means that your code specifies exactly how things get done. As in SQL, however, you need some way to send your PL/SQL code up to the server for execution. PL/SQL also enables you to embed SQL statements within its procedural code.

Use of PL/SQL

You can use PL/SQL to perform processing on the server rather than the client. You can use PL/SQL to encapsulate business rules and other complicated logic. It provides for modularity and abstraction.

Finally, it provides you with a level of platform independence. Oracle is implemented on many platforms, but PL/SQL is the same on all of them. It makes no difference whether you are running Personal Oracle on a laptop or Oracle 8 Server on Windows NT In a nutshell, With PL/SQL you have the power to make your applications more robust, more efficient, and most secure

Advantages of PL/SQL

PL/SQL is a block-structured language offered by Oracle to facilitate the use of the Oracle RDBMS.

It has  the following properties and features that can be used to aid in application development: 

PL/SQL is completely portable, high performance transaction processing, which offers the following advantages.

  • Without PL/SQL, Oracle must process SQL statements one at a time. With PL/SQL, an entire block of statements can process in a single command line statement. This reduces the time taken to communicate the application and the Oracle server. PL/SQL blocks are portable to any operating system or platform. 
  • PL/SQL allows us to use of all SQL data manipulation commands, transactions control commands, SQL functions (except group functions), operators and pseudo columns.
  • Any DDL Statements are not allowed in PL/SQL Block.
  • PL/SQL supports all the SQL data types and as well as it has its own.

These features make PL/SQL a powerful SQL processing language. Using PL/SQL has several major advantages over using standard SQL statements (in addition to allowing the use of stored procedures and functions). Among these are ease of use, portability, and higher performance.

PL/SQL Block

PL/SQL code is grouped into structures called blocks. A Block contains three sections, as described below.

declare

<declaration of variables, constants, function, procedure,

cursor etc.>;

begin

<executable statement(s)>;

exception

<exception handling>;

end;

/

Within a PL/SQL block, the first section is the Declaration section. Using Declaration section, you can define variables and cursors that the block will use. The declaration section starts with the keyword declare and ends when the Executable commands section starts. (As indicated by the keywords begin). The executable commands section is followed by exception handling section; the exception keyword signals the start of the exception handling section. The PL/SQL block is terminated by the end keyword.

In PL/SQL Block only the executable section is required, the declarative and exception handling sections are optional.

A Simple Block

Example

Begin

Insert into emp(empno,ename) values(100,’Shruti’);

Insert into emp(empno,ename) values(101,’Yesha’);

End;

/

/ forward slash executes the PL/SQL block.

When the PL/SQL block is executed it will inserted two rows in emp table.

A Block without Declaration and Exception part is known as anonyms block.  

PL/SQL Architecture

PL/SQL blocks are executed by PL/SQL engine. The PL/SQL engine executes only the procedural statements and sends the SQL statements to the SQL statement executor in the Oracle Server. The PL/SQL engine can either reside in the Oracle Server or another Oracle tools like SQL* Forms, Oracle Forms, Reports etc.

plsql archi

dbms_output.put_line()

dbms_output.put_line() is used to displays the value of variable or any message on the next line of the console.

You might wonder, that, though the block is successfully completed with dbms_output.put_line() it is not showing any output. For that we have to check out that the serveroutput is on or not? If not, write the following line on the SQL prompt, and execute the block again, now you get the output on the console.

SQL>Set Serveroutput On

  • It is not necessary to write the above statement for the execution of each and every block. This statement is written only once per session

You can omit line word from dbms_output.put_line().

dbms_output.put()is used to displays the value of variable or any message on the same line on the console.

Example

Begin

dbms_output.put_line(‘Starting of PL/SQL’);

dbms_output.put_line(‘Welcome to’)

dbms_output.put(‘AZURE’);

End;

/

When the above PL/SQL block is executed, you will receive the following response from Oracle.

SQL> PL/SQL Procedure successfully completed.

Starting of PL/SQL

Welcome to AZURE

  • Before executing above block make sure that Serveroutput must be on.

Datatypes

PL/SQL datatypes can be classified into two types.

  • Scalar datatypes
  • Composite datatypes

All SQL data types like number, char, varchar2, raw, long raw, lob, date and ANSI Standard data type such as boolean, binary_integer and number are categorized as a scalar datatype.

Boolean

Boolean data types can be used to store the values TRUE, FALSE or NULL.

Binary_Integer

Binary_integer is used to store signed integers. The range of binary_integer value is ±231 i.e. the range of binary_integer is –2147483647 to 21474483647

Number

It is same as SQL number data types. In addition to this it includes ANSI standard types which includes following datatypes

Dec / Decimal

Int / Integer

Real

Identifiers

Identifiers are names, which the programmer used within the program. They can refer to one of the following.

  • Variable
  • Constant

Some data are predetermined before a block is used, and its value remains unchanged during the execution of block, these are constant. Other data may change or be assigned values, as the block executed is known as variable.

Variables

Communication with the database takes place via variables in the PL/SQL block. Variables are memory locations, which can store data values. As the program runs, the contents of variables can and do change. Information from the database can be assigned to a variable, or the contents of a variable can be inserted into the database. These variables are declared in the declarative sections of the block. Every variable has a specific type as well, which describes what kind of information can be stored in it.  

Variables are declared in the declarative section of the block.

The general syntax for declaring a variable is

variable_name type [:= value/expression];

where variable_name is the name of the identifier, type is the any valid data type and value is the value of the variable.

For example, the following are the legal variable declarations:

salary number(5);

Declares variable called salary to store maximum 5-digit number.

Assigning values to a variable

We can assign a value to the variable using followings.

  • Assignment operator (:=)
  • Default keyword
  • By fetching method

Declare variable name to store maximum 10 characters and assign AZURE value through assignment operator

name varchar2(10):=‘AZURE’;

Declared variable called salary to store maximum 5-digit number and assign value 3999 to it using default keyword.

Salary number(5) default 3999;

Store an employee name in nm variable whose employee number is 2 using fetching method.

Select ename into nm from emp where empno = 2;

Constants

We can declare variable as a constant and use them in executable part. One cannot change the value of constant throughout the program.

variable_name CONSTANT type := value/expression;

Where variable_name is the name of the identifier, type is the any valid data type and value is the value of the variable.

For example,

pi constant number(9,7):=3.1415926;

Here value of variable pi can’t be change throughout the program execution.

Example

Insert value into dept table using variables.

Declare

v_deptno number := 10;

v_dname varchar2(10) := ‘sales’;

v_loc varchar2(10) := ‘bombay’;

Begin

insert into dept values(v_deptno, v_dname, v_loc);

End;

/

Example

To get the area of the circle provided the radius is given.

Declare

pi constant NUMBER(9,7) := 3.1415926;

radius INTEGER(5);

area NUMBER(13,2);

Begin

radius := 3;

area := pi * power(radius,2);

dbms_output.put_line(‘Area of the circle is ’,area);

End;

/

In the above example, Declaration section having constant identifier (the value of which can not be reinitialized) named pi, which stores a constant value 3.1415926 and another two variables of type Integer and Number respectively. In Executable Section, radius is initialize with value 3 and then area is initialize with the calculated result according to the mathematical formula for finding area of the Circle.

Example

To get the name and salary of specified employee.

Declare

nm varchar2(20);

sal number(9,2);

Begin

Select ename,salary into nm, sal from emp where empno=2;

dbms_output.put_line(‘Name : ’ || nm);

dbms_output.put_line(‘Salary : ’ || sal);

End;

/

In the above example we have use concatenation operator (||). Concatenation operator attaches two

or more strings together.

‘hello ’||‘world’ gives you ‘hello world’ output.

Comments in PL/SQL Block

As we know from very beginning of programming, Comment improves readability and makes your program more understandable. The PL/SQL engine ignores them at the compilation and execution time.

There are two kinds of comments:

Single-line comments

It is starts with two dashes and continues until the end of the line.

Example

Begin — — declaration section is ignored

Insert into emp(ename, empno) values(‘Tejas’,1234);

End;

/

Multiline comments

It is starts with /* delimiter and end with the */ delimiter. This is the same style of comments used in the C language.

Example

Begin /* declaration section and exception

section is also neglected*/

Insert into emp(ename, empno) values(‘Tanmay’,1234);

End;

/

One can utilize multiline comments in Single line.

Example

To get the name and salary of specified employee.

Declare

nm varchar2(20);

sal number(9,2);

Begin

/* Get employee name and Salary */

Select ename,salary into nm, sal from emp where empno=2;

/* Display Employee name and Salry */

dbms_output.put_line(‘Name : ’ || nm);

dbms_output.put_line(‘Salary : ’ || sal);

End;

/

%type Attribute

In many cases, variable will be used to manipulate data stored in a database table. In such case, the variable should have the same type as the table column. For example, the ename column of emp table has type varchar2 (20). Based on this, we have to declare a variable as follows as discussed in above program.

nm varchar2(20);

This is fine, but what happens if the definition of ename column is changed? Say the table is altered and ename now has type varchar2(25). Any PL/SQL code that uses this column would have to be changed, as shown here:

nm varchar2(25);

If you have a large amount of PL/SQL code based on ename column of emp table, this can be a time consuming and error prone process. Rather than hardcode the type of a variable in this way, you can use the %type attribute.

 

This attribute is used to declare a variable’s data type as being equivalent to the specified column’s datatype. So, you need not know the exact data type and size of a database column.

For example:

nm emp.ename%type;

By using %type, nm will have whatever type and size the ename column of the emp table has. If the database definition changes, the datatype in PL/SQL block is also changed according to changes made in database.

Example

To get the name and salary of specified employee using %type attribute.

Declare

nm emp.ename%type;

sal emp.salary%type;

Begin

Select ename,salary into nm, sal from emp where empno=2;

dbms_output.put_line(‘Name : ’ || nm);

dbms_output.put_line(‘Salary : ’ || sal);

End;

/

%rowtype Attribute

%type attribute is used to declare a variable’s data type as being equivalent to the specified column’s datatype , while %rowtype attribute is used to declare composite variable that is equivalent to a row in the specified table. The composite variable is consist of the column names and datatypes in the referenced table i.e. in the declaration of %rowtype attribute with variable, variable inherits column and its datatype information for all the columns of a table.

For example:

erec emp%rowtype;

By using %rowtype, erec will have all the columns with it of emp table.

To, access value of the particular column is done as follows.

erec.ename;

Where, erec is a composite variable, while ename is a column of emp table.

Example

To get the name and salary of specified employee using %type attribute.

Declare

emprec emp%rowtype;

Begin

Select * into emprec from emp where empno=10;

dbms_output.put_line(‘Name : ’ || emprec.ename);

dbms_output.put_line(‘Salary : ’ || emprec.salary);

End;

/

%rowtype attribute specially used with Cursor, which will be discussed later.

PL/SQL Control Structures

Control structure is the most important in PL/SQL to change the logical flow of statements within PL/SQL Block. PL/SQL has a variety of control structures that allow you to control the behavior of the block as it runs. These structures include conditional statements and iterative controls i.e. PL/SQL supports basic programming control structures.

  • Sequence
  • Selection / Condition
  • Iteration

These structures combined with variables, gives PL/SQL its power and flexibility.

  • dbms_standard package provides a language facility to interact with Oracle.

Selection Control

Within PL/SQL block, Selection control is used when the execution of a particular set of statement is based on a specific condition. Sequence of statements can be executed based on some condition using if statement. There are various form of if statement.

If-then form

The simple form of the if statement is the if-then statement as follows.

IF <boolean_expression> THEN

statements;

END IF;

Where boolean_expression is any expression that evaluates to a Boolean value.

Example

Accept Number from a User and display Hello message if the entered number is Positive.

Declare

num number;

Begin

num := &num;

if num > 0 then

dbms_output.put_line(‘Hello’);

end if;

end;

/

Example

Display Salary of a specified employee increasing by 500 if its salary is more than 3000.

Declare

sal number(9,2);

num emp.empno%type;

Begin

num := &num;

Select salary into sal from emp where empno=num;

If sal > 3000 then

sal := sal + 500;

end if;

dbms_output.put_line(‘Salary : ’ || sal);

End;

/

Above block will display salary of specific employee (as per entered employee number) by increasing

500 if its salary is greater than 3000 otherwise it will display salary as it is.

If-then-else form

So far we have discussed the simplest form of if statement, which gives us a choice of executing a statement of block or skipping them. If-then-else form allows us to execute either or blocks depend on condition using if-then-else form as follows.

IF <boolean_expression> THEN

True block statements;

ELSE

False block statements;

END IF;

True block statements are executed only if the condition is satisfied otherwise the else portion will be executed.

Example

Accept number from a user and find out whether it is Odd or Even.

Declare

num number;

Begin

num := &num;

if mod(num,2) = 0 then

dbms_output.put_line(no,’is even’);

else

dbms_output.put_line(no,’is Odd’);

end if;

End;

/

Example

Accept employee number from a user and increase its salary depends on the current salary as follows.

Salary Increment

>= 5000 12.5%;

<5000 11%

Declare

sal number(9,2);

num emp.empno%type;

pf number(9,2);

Begin

num := &num;

Select salary into sal from emp where empno=num;

If sal >= 5000 then

update emp set salary = salary+(salary*0.125)where empno=num;

else

update emp set salary = salary + (salary*0.11) where empno=num;

end if;

End;

/

If-then-elsif form

This form is used to select one of multiple alternatives.

IF <boolean_expression1> THEN

statements;

ELSIf <boolean_expression2> THEN

statements;

ELSIf <boolean_expression2> THEN

statements;

——————————

——————————

END IF;

Example

Declare

sal emp.sal%type;

eno emp.empno%type;

Begin

Eno := &eno;

Select salary into sal from emp where empno=eno;

if sal > 10000 then

dbms_output.put_line(‘Salary is more than 10000’);

elsif sal >= 7000 then

dbms_output.put_line(‘salary is between 7000 to 10000’);

else

dbms_output.put_line(‘Salary is less than 7000’);

end if;

End;

/

Iterative Control / Loops

PL/SQL provides a facility for executing statements repeatedly, via loops. In PL/SQL we have three loops as follows to execute statements repetitively.

  • Simple loop
  • While loop
  • For loop

Simple loop

The most basic kind of loops, simple loop have this Syntax:

LOOP

Sequence_of_statements;

END LOOP;

Sequence_of_statements will be executed infinitely, since this loop has no stopping condition. We can exit this loop via EXIT statement. General form of exit statement is as follows.

EXIT [WHEN condition];

Example

Declare

I number(2):=0;

Begin

Loop

dbms_output.put_line(I);

I:=I+1;

Exit when (I>10);

End loop;

End;

/

WHILE Loop

WHILE <condition>

LOOP

Sequence_of_statements;

END LOOP;

Before each iteration of the loop, condition is evaluated. If it evaluates to TRUE, sequence_of_statements is executed. If condition evaluates to FALSE or NULL, the loop is finished and control resumes after the END LOOP statement.

The only difference between simple loop and while loop is simple execute first and then it will check condition, so simple loop execute at least once and in while loop first it will check condition and then execute.

Example

Declare

I number(2):=0;

Begin

While I > 50

loop

dbms_output.put_line(i);

I:=I+5;

End Loop;

End;

/

FOR Loop

The number of iterations for simple loops and while loops is not known in advance; it depends on the condition. FOR loops, on the other hand, have a defined number of iterations.

FOR loop_counter IN [REVERSE] LowerBound..UpperBound

LOOP

Sequence_of_statements;

End LOOP;

Where loop_counter is the implicitly declared index variable, lowerbound and upperbound specify the number of iterations, and sequence_of_Statements is the contents of the loop.

Example

Declare

no number := 5;

Begin

For I in 1..10 loop

dbms_output.put_line(no||‘ * ‘||I||‘ = ‘||no*I);

End loop;

End;

/

Reverse keyword

If the REVERSE keyword is present in the FOR loop, then the loop index will iterate from the high

value to the low value. Notice that the syntax is the same; the low value is still referenced first.

100

Example

Begin

For I in REVERSE 1..5 LOOP

dbms_output.put_line(I);

End LOOP;

End;

/

Goto Statment

The goto statement allows us to branch to a label unconditionally. The label, which is enclosed within double angular brackets, must precede an executable SQL or a PL/SQL block. When executed, the goto statement transfers control to the labeled statement or a block.

Example

Declare

no number:=1;

Begin

While no<=10 loop

dbms_output.put_line(no);

no := no+1;

If no = 5 then

goto lbl;

End if;

End loop;

<<lbl>>

dbms_output.put_line(‘Number Printing from lable ‘||no);

End;

/

In above example, when no is equal to 5 control transfer to label lbl and execute whatever mentioned after label and stop execution of for loop.

Exception Handling

Trappings Errors and Exceptions

An Exception is raised when an error occurs. In case of error, normal execution stops and the control is transferred to the exception handling part of PL/SQL block. Sometimes the Oracle server or the user’s application causes an error to occur during runtime processing. Such errors can arise from hardware or network failures, application errors, logical errors, data integrity errors, and many other sources. These errors are known as exceptions; that is, these unwanted events are the exceptions to the normal processing that is expected.  

Typically, when an error occurs, processing of the PL/SQL block terminates immediately. Hence your application stops processing and the task at hand goes unfinished. Oracle enables you to be prepared for these errors and write logic in your programs to handle them gracefully and allow processing to carry on as you have designed it. This logic written to manage errors is known as exception-handling code. With Oracle exception handling, when an error is detected, control is passed to the exception-handling portion of your program, and then processing completes normally. Handling errors also provides valuable information for debugging applications and for better “proofing” the application against future errors.

Any well-written program must have the ability to handle errors intelligently and recover from them if possible. PL/SQL implements error handling via exceptions and exception-handlers. Exceptions can be associated with Oracle errors or with your own user-defined errors.

Exception

One of the features of PL/SQL is the exception handling mechanism. By using exceptions and exception handlers, you can make your PL/SQL programs robust and able to deal with both unexpected and expected errors during execution. What kind of errors can occur in a PL/SQL program? Errors can be classified into run-time error and compile-time error.

Exceptions are designed for run-time error handling. Errors that occur during the compilation time are detected by PL/SQL engine and reported back to the user. The program cannot handle this, since this had yet to run. Exceptions and exception handlers are how the program responds to runtime errors.  

When an error occurs, an exception is raised. When this happens, control is passed to the exception handler, which is the separate section of the PL/SQL Block. This separates the error handling from the rest of the block, which makes the logic of the program easier to understand.

In PL/SQL, the user can anticipate and trap for certain runtime errors.  

There are two types of exceptions:

  • Predefined Exceptions
  • User-defined Exceptions

Exceptions can be internally defined by Oracle or by the user.

Pre-defined Exceptions

The Pre-defined exception is raised implicitly (automatically) when PL/SQL block or any of its statement violets Oracle rule. Those errors, which are frequently occur, are assign as a pre-defined exception by Oracle.

Then General form Exception Handling is :

Declare

Begin

Exception

When Exception_1 then

<Statement(s)>;

When Exception_2 then

<Statement(s)>;

End;

/

In this syntax, Exception_1 and Exception_2 are the names of the predefined exceptions. Statements are the valid code that will be executed if the exception name is satisfied.

The Oracle server defines several errors with standard names. Although every Oracle error has a number, the errors must be referenced by name. PL/SQL has predefined some common Oracle errors and exceptions. Some of these predefined exception names are

Sr. Exception Description

  1. No_Data_Found SELECT returned no rows
  2. Too_many_rows SELECT into statement returned more than one row.
  3. Invalid_cursor This exception is raised when we violet cursor operation. For example, when we try to close a cursor, which is not opened.
  4. Value_error Arithmetic, Conversion, Truncation or Constraint Error occurred. i.e. Attempt to insert a value with larger precision.
  5. Invalid_Number Conversion of a character to a number is failed.
  6. Zero_divide Attempted to divide by zero.
  7. Dup_val_on_Index Attempted to insert a duplicate value into a column that has a unique index.  
  8. Cursor_already_open Attempted to open a cursor that was previously opened.
  9. Not_logged_on A database call was made without being logged into Oracle.

10.  Transaction_backed_out Usually raised when a remote portion of a transaction is rolled back

11.  Login_denied Login to Oracle failed because of invalid username and password.

12.  Program_error Raised if PL/SQL encounters an internal problem.

13.  Storage_error Raised if PL/SQL runs out of memory or if memory is corrupted.

14.  Timeout_on_resource Timeout occurred while Oracle was waiting for a resource.

15.  Others This is a catchall. If the error was not trapped in the previous exception traps, this statement will trap the error.

Oracle declares predefined exceptions globally in the package standard. Therefore, you do not need to declare them yourself.

Example

Write a PL/SQL Block to accept employee name from a user if it is exist display its salary otherwise display appropriate message using exception handling.

Declare

erec emp%rowtype;

nm emp.ename%type;

Begin

nm:=&nm;

SELECT * into erec from emp where ename=nm;

dbms_output.put_line(‘Employee Salary : ‘ || erec.salary);

Exception

When No_Data_Found then

dbms_output.put_line(‘Entred name is not found’);

When Others then

Null;

End;

/

Example

Write a PL/SQL Block to display the salary of that employee whose age is 45 year otherwise display appropriate message using exception handling.

 

Declare

erec emp%rowtype;

yr number;

Begin

yr := &yr;

SELECT *into erec from emp Where round((SYSDATE-BDATE)/365),0)= 45;

dbms_output.put_line(‘Employee Salary : ‘ || erec.salary);

Exception

When No_Data_Found then

dbms_output.put_line(‘No Employee with 45 years age’);

When Too_many_rows then

dbms_output.put_line(‘More than one Employee with 45 years age’);

When Others then

Null;

End;

/

Example

Write a PL/SQL Block to insert add one row in employee table with employee number and name. Display appropriate message using exception handling on duplication entry of employee number.

Declare

eno emp.empno%type;

nm emp.ename%type;

Begin

eno := &eno;

nm := ‘&nm’;

insert into emp(empno,ename) values(eno,nm);

Exception

When Dup_val_on_index then

dbms_output.put_line(‘Employee Number already Exist’);

When Others then

Null;

End;

/

As you saw in the earlier examples of exception-handling blocks, the other exception was used as a catchall exception handler. Others is normally used when the exact nature of the exception isn’t important, when the exception is unnamed, or even when it’s unpredictable.

User-Defined Exception

Unlike internal exceptions, user-defined exceptions should be explicitly specified. The user-defined exception must be declared in the declaration part of the PL/SQL Block and it can explicitly rise with the RAISE Statement. Declaration of user-defined cursor declares a name for user_defined error that the PL/SQL code block recognizes. The raise exceptions procedure should only be used when Oracle does not raise its own exception or when processing is undesirable or impossible to complete

Steps for trapping a user-defined error include the following:

1)      Declare the name for the user-defined exception within the declaration section of the block.

2)      Raise the exception explicitly within the executable portion of the block using the RAISE Statement.

3)      Reference the declared exception with an error-handling routine.

Example

Declare

Invalid_Pay Exception;

Pay Number := &Pay;

Begin

If Pay Not Between 2000 and 5000 Then

RAISE Invalid_Pay;

End If;

Exception

When Invalid_Pay Then

dbms_Output.Put_Line(‘Salary should be between 2000 and 5000’);

End;

/

Example

Accept employee number and salary from a user and store it into the table if salary is greater than zero otherwise display appropriate message using user-defined exception.

Declare

sal emp.salary%type;

eno emp.empno%type;

sal_error exception;

Begin

eno := &eno;

sal := &sal;

if sal=0 then

raise sal_error;

106

else

update emp set salary = sal where empno=eno;

end if;

Exception

when sal_error then

dbms_output.put_line(‘Salary must be >0’);

End;

/

Example

Accept employee number and salary from a user and update employee salary as per following rules if employee exist other wise display appropriate message using exception handling

a)      If job is Manager then Salary not more then 7000

b)      If job is Clerk then salary not more then 4000

c)      If job is Analyst then salary not more then 24000

Declare

sal emp.salary%type;

eno emp.empno%type;

vjob emp.job%type;

upd_error exception;

Begin

eno := &eno;

sal := &sal;

select job into vjob from emp where empno = eno;

if upper(vjob) = ‘MANAGER’ AND sal > 7000 then

raise upd_error;

elsif upper(vjob) = ‘CLERK’ AND sal > 4000 then

raise upd_error;

elsif upper(vjob) = ‘ANALYST’ AND sal > 24000 then

raise upd_error;

else

update emp set salary = sal where empno=eno;

dbms_output.put_line(‘salary of employee ’|| eno ||’is

Updated to’|| sal);

end if;

Exception

when no_data_found then

dbms_output.put_line(‘Employee numbe not exist’);

when upd_error then

dbms_output.put_line(‘Salary is not been changed as per

the Rule’);

end;

/

Advertisements

One Response to “Basics of PL/SQL – Part 1”

  1. Pretty good submit. I just came across your blog and wanted to state that I have got really appreciated reading your website posts.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: