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.
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 := #
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 := #
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 := #
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 := #
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
- No_Data_Found SELECT returned no rows
- Too_many_rows SELECT into statement returned more than one row.
- Invalid_cursor This exception is raised when we violet cursor operation. For example, when we try to close a cursor, which is not opened.
- Value_error Arithmetic, Conversion, Truncation or Constraint Error occurred. i.e. Attempt to insert a value with larger precision.
- Invalid_Number Conversion of a character to a number is failed.
- Zero_divide Attempted to divide by zero.
- Dup_val_on_Index Attempted to insert a duplicate value into a column that has a unique index.
- Cursor_already_open Attempted to open a cursor that was previously opened.
- 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;
/
