PL/SQL QUERIES FOR DEVELOPERS AND ADMINISTRATORS
INTRODUCTION TO PL/SQL:
Pl/SQL is procedural language or structured query language. PL/SQL
(Procedural
Language/SQL) is a procedural extension of Oracle-SQL that offers
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-PL) 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..
Advantages in pl/sql:
1 ) pl/sq l is development tool which is not only supports sq l
data manipulation ,but also provides facilities of conditional
checking,branching and looping.
2) pl/sq l sends the entire block of sq l statements to the oracle
engine all the same time
3) pl/sq l also permits dealing with errors as required and
facilities like displaying those errors in a user-friendly message whenever
errors are encountered.
4) It allows declaration and use of variable in block of
code.These variables can be used to store the results of the query for the
lateral processing and insert them into the oracle table int the lateral
storage
Disadvantages:
1) Sq l Statements are passed to oracle engine only one at a time.
2)Sq l doest not have any procedural capability does not provide
the programming technique of condition checking,looping .
3)while processing an sql statements if any occurs, the oracle
engine displays it own error message.Sq l has no facility for program handling
of errors which during the manipulation of data.PL/SQL is a block-structured
language.A PL/SQL block has an optional declare section, a part containing
PL/SQL statements, and an optional exception-handling part
< Block header>
declare
< Constants>
< Variables>
< Cursors>
< User defined exceptions>
begin
< SQL statements>
exception
< Exception handling>
end ;
The block header specifies whether the PL/SQL block is a procedure,
a function, or a packages.If no
header is specified, the block is said to be an anonymous PL/SQL
block.
The pl/sq l block consists of 3 sections:
---------------------------------------------------------
1)Deceleration Section
2)Begin Section
3)Exceptional Section
Declaration:
1)The deceleration section is optional
2)Constants, variables, cursors, and exceptions used in a PL/SQL
block must be declared in the declare section of that block.
3)It is used to list the variables used in the block along with
the type of data they support.
4)The block begin with DECLARE ,telling the pl/sq l complier the
type of code that comes next.
Execution:
The execution section supports all DML commands and sq l*plus
built-in
functions. It Supports DDL commands using Native Dynamic SQL and
the DBMS_SQL built-in package.
Exception Section:
The exception section is optional and traps errors generated
during program
execution.Errors can occur due to syntax (or) logic (or) Violating
the rule such as missing the semicolon,using an undeclared variable. Whenever a
pl/sq l block is begin and after completion of that block,we have to close that
block by using END keyword.
How To Execute a PL/SQL Block:
pl/sq l can be executed in 3 methods:
1)@filename
It is used for displaying the output
2)start filename
It is used for displaying the output
3)run filename
It is used for displaying the output as well as the block of code.
To View The Output of pl/sq l Block:
To see the output of pl/sq l block ,we have to start the server by
the command
set server output on
Sample Program:
begin
dbms_output.put_line('WELCOME TO PL/SQL PROGRAMMING ');
end;
/
Control Structures:
The flow of control statements can be classified into the
following categories:
● Conditional Control
● Iterative Control
● Sequential Control
Conditional Control :
pl/sq l allows the use of an IF statement to control the execution
of
a block of code .In PL/SQL ,the IF-THEN-ELSEIF-ELSE_END .IF
construct in code block allow specifying certain conditions under which
specific block of code should be executed.
1.
I
F-THEN:
The most basic conditional evaluation is an IF_THEN statement a
condition is met,THEN do something. If the condition is not met
,skip the code that follows and continue with rest of the program.
Syntax for IF-THEN statement:
if condition
then
action
end if;
Example for IF_THEN:-
declare
bal number(9):=&bal;
begin
if(bal>500) then
insert into ysl5 values(&sno,'&sname');
end if;
end;
/
2)IF-THEN-ELSE:
using IF-THEN logic,a condition that evaluates to FALSE or NULL
does nothing.The program is skipped.IF-THEN-ELSE adds another layer to the
logic ,allowing us yo specify what to do if the condition evaluates to FALSE or
NULL.
The Syntax for IF-THEN-ELSE:
if condition
then
action
else
action
end if;
Example for IF-THEN-ELSE:
declare
bal number(9):=&bal;
begin
if(bal>500) then
insert into ysl5 values(&sno,'&sname');
dbms_output.put_line('values are inserted');
else
dbms_output.put_line('values are not inserted');
end if;
end;
/
3)IF-THEN-ELSIF:
Oracle provides a way to chain IF condition together so that if
one is met,the rest are
assumed to be false and are skipped.
Syntax for elsif:
if <condition> then
<statement>;
elsif<condition> then
<statements>;
else
<statements>;
end if;
/
Example for els if:
declare
sno1 number(9):=&sno1;
baln number(9);
begin
select bal into baln from emp where sno=sno1;
if(baln>500 and baln<1000) then
update emp set bal=bal+500 where sno=sno1;
elsif(baln>1000) then
update emp set bal=bal+1000 where sno=sno1;
else
dbms_output.put_line('bal less then 500 cannot be updated');
end if;
end;
/
Iterative Control Statements:
Iterative control indicates the ability to repeat or skip sections
of a code block.A loop marks a sequence
of statements that has to be repeated.The keyword LOOP has to be
placed before the first statement int
the sequence of statements to be repeated,while the ENDLOOP is
placed immediately after th last
statement in the sequences.Once a loop begins to execute,it will
go on forever.Hence a conditional
statement that controls the number of times is executed always
accompanies loops.
Pl//sq l supports the following iterative control statements:
● Simple loop
● While loop
● for loop
Simple Loop:
A loop that keeps repeating until an exit or exit when statement
is reached within the loop
Syntax:
loop
<sequence of statements>
exit when <condition>;
end loop;
Example for Simple loop:-
write a pl/sq l block to display 1 to 10 numbers using simple loop
declare
i number:=0;
begin
loop
i:=i+2;
exit when i>10;
end loop;
end;
/
output:-
--------------
2468
10
while Loop:
A loop that repeats when a condition is met.Instead of the exit
condition begin
specified via an exit command within the loop,the exit condition
is specified in the while command that intaties the loop
Syntax:
while<condition>
loop
<set of statements>;
end loop;
Example :
write a pl/sq l block to display 1 to 10 numbers using while
declare
i number(10);
begin
i:=1;
while(i<10)
loop
i:=i+1;
dbms_output.put_line('the value of i is '||i);
end loop;
end;
/
output:-
the value of i is 2
the value of i is 3
the value of i is 4
the value of i is 5
the value of i is 6
the value of i is 7
the value of i is 8
the value of i is 9
the value of i is 10
For Loop:
A loop that repeated number of times.A simple loop executes until
an exit
condition is met,whereas a FOR loop executes a specified number of
times.The FOR loop's start is indicated by the keyword for,followed by the
criteria used to determine when the processing is complete and the loop can be
exited.Because the number of times the loop is executed is set when the loop is
begun,an exit command isn't needed within the loop.
Syntax:
for i in start..end
loop
<set of statements>;
end loop;
Example:
write a pl/sq l block to display 1 to 10 numbers using for
declare
i number(10);
begin
for i in 1..10
loop
dbms_output.put_line('the number are'||+i);
end loop;
end;
/
output:-
the number are1
the number are2
the number are3
the number are4
the number are5
the number are6
the number are7
the number are8
the number are9
the number are10
NOTE:
The Advantage of for loop is:
1)No need to declare a variable which is used in for loop.
2)No need to initialize a value to a variable.
3)No need to increment that variable.
Sequential Control Statements:
GOTO:
The goto statements changes the flow of control within a pl/sq l
block.This statements
allows execution of a particular block of code which is not in
normal flow of control.
The entry point into such a block of code is marked by using the
tag
<<user-defined name>>
The goto statement then makes use of this user-defined name to
jump into that block of
Code for execution.
Syntax:
goto<user-defined name>;
lines of code
<<user-defined name>>;
Example:
begin
dbms_output.put_line('beginning of block');
dbms_output.put_line('hai this is chaitanya');
goto emp;
dbms_output.put_line('goto didnt work');
return;
<<emp>>
dbms_output.put_line('ending of block');
end;
/
output:-
beginning of block
hai this is chaitanya
last line
\
PROCEDURES:
procedure:
A procedure is a logically group set of SQL and PL/SQL statements
that perform specific task.
➢ To make a procedure dynamic, Then the user has to pass the parameters
before the execution of procedure.
➢ A procedure can change the way it works depending upon the
parameters passed prior to its execution.
➢ A procedure fairly included in DML statements; they cannot be used
for DDL statements.
➢ If we want to use DDL statements we use EXECUTE IMMEDIATE command.
Procedures are made up of A declarative part Executable part An
Exception part
End part
Declarative part: A
declarative part contains the declarations of the cursors,variables,
constants and exceptions. These are local to the procedure. These
become invalid
whenever the procedure exists.
Executable part: The executable part is a PL/SQL block consisting of SQL and PL/SQL
statements that assign values,controls the execution and manipulation
of data. The action that the procedure is expected to perform is return here.
Exception Handling part: This part
contains the code that deals with Exceptions which
are raised during the execution of the code which is return in the
executable part.
End part: This marks the end of PL/SQL procedure.
Template of a Procedure:
create or replace procedure <procedure-name>(variable data
type)
is/as
<Declarative block>
begin
<Executable block>
exception
<Exception block>
end;
/
simple procedure:
create
or replace procedure chaitanya
as
begin
dbms_output.put_line('CHAITANYA
VIZAG');
end;
/
Execution process:
in SQL prompt
we have to type this syntaxes
For compilation purpose- @<filename.sql>
ex:-@chaitanya.sql
For Executing purpose -Execute <procedure-name>
ex:-exec chaitanya
output:-CHAITANYA VIZAG
Key words of procedures:
Replace: recreates the procedure if it already exists.
Procedure: it is the name of the procedure to be created.
Argument:it is the name of the argument to the procedure. Paranthesis can
be omitted if no arguments are present.
In: this indicates that the parameter will accept a value from the
user.
Out:this indicates that the parameter will return a value to the user.
In out:this indicates that the parameter will accept a value or will
return a value to the user.
procedures are two types
(1) named procedures
(2)Stored procedures
Stored procedures, or named procedures, are given unique name at creation
time. They are owned by the user that created them unless otherwise stated
stated in the creation script.
Namedprocedures:
procedures and functions should be named according to the business
function they perform or business rule they enforce. There should be no
ambiguity about their purpose.The new book procedure shown earlier should be
renamed. NEW_BOOK performs a business function-inserting recods into BOOKSHELF
and deleting them from BOOK_ORDER_
so its name should be reflect that function.A better choice for
the name would
be RECIVE_BOOK_ORDER. Since it performs a function, a verb (in the
case,”recive”) should describe what it does The name of the procedure should
also include the name of the major table(s) it impacts. But tabels or properly
named.
Storedprocedures:
stored procedure means one procedure call in another procedure.
in this if second procedure executes automatically the frist
procedure is executed and the result will be displayed.
Example:
q)write
a stored procedure to enter a n umber and display its square.
create
or replace procedure chaitanyavsp1(i number)
is
begin
dbms_output.put_line(i*i);
end;
/
create
or replace procedure chaitanyavsp2
is
i
number;
begin
chaitanyavsp1(&i);
dbms_output.put_line(i);
end;
/
Execution process:
Step1:- we are having two procedures 1)chaitanyavsp1.sql
2)chaitanyavsp2.sql
Step2:- compile the frist procedure like this @chaitanyavsp1.sql
Step3:-and then compile the second procedure like this
@chaitanyavsp2.sql and giving a valu for 'I';
EX:-Enter value for i: 5
Step4:- after that execute the second procedure only
'execute<procedurename>'
EX:-execute chaitanyavsp2;
output:-25
IN parameters using procedures:
The value of the actual parameter is passed into the procedure
when the procedure is
invoked. Inside the procedure, the formal parameter acts like a
PL/SQL constant-it is considered readonly and cannot be changed. When the
procedure finishes and control returns to the calling environment, the actual
parameter is not changed.
Example:
create
or replace procedure chaitanyavsp(i in number)
is
begin
dbms_output.put_line(i*i);
end;
/
Execution process:
Execution procedure:
in SQL prompt
we have to type this syntaxes
For compilation purpose- @<filename.sql>
ex:-@chaitanyavsp.sql
For Executing purpose -Execute <procedure-name>
ex:-exec chaitanyavsp(10);
output:-100
OUT parameters using procedures:
Any value the actual parameter has when the procedure is called is
ignored.inside the procedure, the formal parameter acts like an unintialized
PL/SQL variable and thus has a value of NULL. It can be read from and written
to. When the procedure finishes and control returns to the calling environment,
the contents of the formal parameter are assigned to the actual parameter.
Example:
create
or replace procedure chaitanyavsp(i out number) is
k
number=0;
begin
dbms_output.put_line('inside
out');
if(i
is NULL) then
dbms_output.put_line('==========I
variable having NULL values=========');
else
dbms_output.put_line('I
value is================'||i*i);
end
if;
i:=7;
k:=i;
dbms_output.put_line('==============ending
of out=====================');
if
(i is NULL) then
dbms_output.put_line('==========I
12 variable having NULL values=========');
else
dbms_output.put_line('I12
value is================'||i*i);
end
if;
end;
/
Execution process:
Step1:-frist compile the procedure by using filename
@<filename.sql>
ex:- @out.sql
Step2:-and then giving a 'i' value by using SQL prompt like this
ex:-SQL>declare(Press Enter on your key board)
SQL>variable y number;(Press Enter on your key
board)
SQL> begin
2 :y:=10;
3 end;
4 /
Step:3:- after assign a i value we have execute the procedure by using this
syntex execute<procedurename>(assigned variablename);
ex:-execute chaitanyavsp(:y);
Output:-49.
IN and OUT parameters using procedures:
This mode is a combination ot in and out. The value of the actual
parameter is passed into the procedure when the procedure is invoked. Inside
the procedure, the formal parameter acts like an initialized variable and can
be read from and written to. When the procedure finishes and control returns to
the calling environment,the contents of the formal parameter are assigned to the
actual parameter(subject to NOCOPY,as for OUT).
Example:
create
or replace procedure chaitanyavsp(i in out number) is
begin
dbms_output.put_line(i*i);
end;
/
Execution process:
Step1:-frist compile the procedure by using filename
@<filename.sql>
ex:-@storeproc1.sql
Step2:-and then giving a 'i' value by using SQL prompt like this
ex:-SQL>declare(Press Enter on your key board)
SQL>variable y number;(Press Enter on your key
board)
SQL> begin
2 :y:=6;
3 end;
4 /
Step:3:- after assign a i value we have execute the procedure by using this
syntex execute<procedurename>(assigned variablename);
ex:-execute chaitanyavsp(:y);
Output:-36.
Dynamic procedure calling using IN OUT
parameters:
This type of procedures are mostly used in live projects why
because no need execute the procedures but compilation is necessary in this
compilation procedure will be executed and display the result. In this the
procedure having one procedure and one block. The block is included into the
procedures.When The procedure is compiled automatically procedure and block
will be automatically executed.
Example:
create
or replace procedure chaitanyavsp3(a in number, b out number) as
begin
b:=a*a;
end;
/
declare
b
varchar2(20);
begin
chaitanyavsp3(&a,b);
dbms_output.put_line(b);
end;
/
Execution process:
Step1:-in this only compilation is neccassary @<filename.sql> and
giving a value of 'I';
EX:- @chaitanyavsp3.sql
Enter value for a: 4
output:-16
cursor using in procedures:
procedure means retring and maniplating a singlerow data from the
table.
cursor is used only retriving the multiple rows data from the
table.By using procedure with cursor we can retrive and maniplate the multiple
data at a time using single
procedure.
create
or replace procedure chaitanya is
eno
varchar2(20):='&eno';
cursor
c1 is select * from emp where empno=eno;
r1
c1%rowtype;
begin
open
c1;
loop
fetch
c1 into r1;
exit
when c1%notfound;
dbms_output.put_line('your
name is :'||r1.ename);
dbms_output.put_line('your
salary is :'||r1.sal);
dbms_output.put_line('your
job is:'||r1.job);
dbms_output.put_line('your
department number :'||r1.deptno);
end
loop;
close
c1;
exception
when no_data_found then
dbms_output.put_line('invalid
meter number you are entering');
end;
/
Execution process:-
in SQL prompt
we have to type this syntaxes
For compilation purpose- @<filename.sql>
ex:-@chaitanya.sql
For Executing purpose -Execute <procedure-name>
ex:-exec chaitanya
output:-your name is :FORD
your salary is :16500
your job is:ANALYST
your department number :20
Procedures using with Exceptionhandling:
Exceptions are used to process errors and warnings that occur
during the execution of PL/SQL statements in a controlled manner.
Each error or warning during the execution of a PL/SQL block
raises an exception
if an exception is raised with in the executable commands section
of your pl/sql block the flow of commands immediately leave's the executable
section and searches exception handling section
example:-create or replace procedure
Calling a procedure with ina trigger:
Rather then creating a large block of code with in a trigger
body,you can save the code as a strored procedure and call the procedure from
within the trigger, by using the CALL command. For example if you createan
text_book procedure that inserts rows into BOOKSHELL_table,as shown in the
example.
Create
or replace trigger text_book
after
insert on BOOKSHELL_table
for
each row
begin
call
text_book(:new.old_rating,:new.new_Rating,:new.Audit_date);
end;
/
Using EXECUTE IMMEDIATE in procedures:
A procedure fairly included in DML(select,insert,update,delte) statements;
they cannot be used for DDL statements.If we want to use DDL(create,alter...)
statements we use EXECUTE IMMEDIATE command.
Example:
begin
execute
immediate 'create table chaitanya03(trainee_id number)';
dbms_output.put_line('emp
table created');
end;
/
FUNCTIONS:
A function is very similar to procedure. Both take
parameters,which can be of any mode.
Both are different from PL/SQL blocks,with
declarative,executable,and exeception sections.both can be stored in the
database or declared within a block. However, a procediure call is a PL/SQL
statement by itself, while a function call is called as part of an expression.
Functions are mainly used to written a particular value and
display on the screen.
➢ A function can return only one value at a time.
➢ No DML operations or commands are supported in functions.
RETURN statement:inside the the body of the function, the RETURN
statement is used to return control to the calling environment with a value.
The general syntax of the RETURN statement is
Syntex: RETURN expression;
where expression is the value to be returned. When RETURN is
executed, expression will be converted to the type specified in the RETURN
clause of the function defination, if it is not already of that type. At this
point,control immeadiatly returns to the calling environment.
Keywords or Parameters:
replace:-this is used to recreate a function if it is already
exists
function :this is the name of the function to be created
Template of a Function:
create or replace function <Function-name> return
<data-type>
is/as
begin
<set of statements>
end;
/
simple Function:
create
or replace function display(a number,b number) return number
is
begin
return
a+b;
end;
/
Execution Function:
in SQL prompt
we have to type this syntaxes
For compilation purpose- @<filename.sql>
ex:-@chaitanya.sql
For Executing purpose- select <function-name> (parameters)
from
dual;
ex:-select display(10,20) from dual;
output:-30
Functions are Two types:
1)Recursive function.
2)Stored function.
Recursive function:
The function which calls itself until its value becomes '1'(one)
is called as recursive
function.
Example:
Q) Write recursive function to accept a number and display its
factorial value.
Function:
set
serveroutput on
create
or replace function chaitanya(i number) return number is
begin
if
i=1 then
return
1;
else
return
i * chaitanya(i-1);
end
if;
end
;
/
Execution of function:- Step1:@recursive.sql
Step2:-select chaitanya(5) from dual;
output:-chaitanya(5)
----------
120
StoredFunction:
we can use the function name in another PL/SQL block to display the
result of the function such a function are called as stored functions.
StoredFunction:
create
or replace function chaitanya(i number)return number
is
begin
return
i*i;
end;
/
PL/SQL block:
begin
dbms_output.put_line('the
stored function value is'||chaitanya(5));
end;
/
Execution of function:
Step1:frist we have to store the function and block stored in data base
with different file
Names that is 1) storedfunction.sql
2) storedblock.sql
Step2:-after we compile the function by using @storedfunction.sql
Step3:-compile and execute the block by using @storedblock.sql
output:-the stored function value is 25
cursors in functions:
declare
cursor
c1 is select * from emp;
dcc
varchar2(20);
function
fun1(empno number,ename varchar,sal number)return varchar
is
begin
return
(empno||ename||sal);
end
;
begin
for
a1 in c1
loop
dcc:=fun1(a1.empno,a1.ename,a1.sal);
dbms_output.put_line(dcc);
end
loop;
end;
/
Execution of function:
Step1:- in this we are doing only compilation that is @cuinfun.sql
Step2:-no need to use SELECT command.
output:-
7369SMITH9400
7499ALLEN1600
7521WARD1250
7566JONES4975
7654MARTIN1250
7698BLAKE2850
7782CLARK3450
7788SCOTT5500
7839KING6000
7844TURNER1500
7876ADAMS3600
7900JAMES
7902FORD1400
passing parameters & Exception handling
in function:
create
or replace function chaitanya0305(eno number)return varchar
is
name
varchar2(15);
begin
select
ename into name from emp where empno=eno;
return
name;
Exception
when
no_data_found then
dbms_output.put_line('record
is not in table');
return
'record not found';
end;
/
output:-
CHAITANYA0305(63454)
--------------------
record not found
record is not in table
How does the oracle Engine creates Stored
procedures & functions:-
The following steps are performed automatically by the oracle
engine while creating
procedure or a function.
Step 1: compiles the procedure or a function.
Step 2:Stores the procedure or a function in database.
Step 3:The oracle engine compiles the PL/SQL code block.
If any ERROR occurs during compilation of a procedure or a
function then an invalid
procedure or a function gets created. The oracle engine displays a
message after creation that the procedure or a function created with
compilation errors. the compilation process does not display the errors. These
errors are view by using the SELECT statement and show error.
1)select * from user_errors;
2)show error
Droping procedure and functions:
Just as a table can be dropped, procedure and functions can also
be dropped.this removes the procedure or function from the data dictionary.
The syntax for dropping a procedure is drop procedure
procedure_name;
the syntex for dropping the function is:
drop procedure function_name;
PROCEDURES VS FUNCTIONS:
procedures do not have to return the value to the caller. Functions
can return a value to the caller,and functions can be directly refrenced in Queries.
This value is returned through the use of the RETURN key wordwithin the
function.
Packages:
Packages are database objects used to develop complex database
applications that require the usage of control structures and procedural
elements such as procedures, functions and other modules.In pl/sql blocks that
specify procedures and functions are grouped together to form packages.A
package is similar to module and has an implementation part.Oracle offers
several predefined packages such as file handling,job scheduling etc.A package consists
of package specification and package body.The package specifications defines how
many procedures or functions are being used that are visible to application
programmers and a package body implements the package
specification.
Some of the advantages of using packages
are listed below.
1)we can combine one or more SQL commands.
2)The code reusability.
3)Reduce the cost of maintenance and changing applications.
4)Modularity can be achieved through packages.
5)Code complexity can be reduced.
Syntax:
create or replace package <package name>
is/as
--package specification
procedure <procedure name>(parameters);
function <function name>(paramaters) return <datatype>
;
end <package name>; --end of package specification
/
--package body
create or replace package body <package name>
is/as
procedure <procedure name>(parameters)--procedure
implementation
is/as
begin
<executable statement>
end <procedure name>;
function <function name>(parameters) return
<datatype>--function
implementation
is/as
begin
<executable statement>
end <function name>;
end <package name>; --end of package body
/
A simple example for a package is as
follows:-
This program just displays the value which
we are entering:-
vi pack1.sql --program name
create or replace package p111 --package name(p111)
as
--starting of package specification
procedure p1(a number); --a is a parameter which as to be passed
during execution time
function fun1(a number) return number;
end p111; --end of package specification
/
--starting of package body
create or replace package body p111
as
procedure p1(a number) --implementation of procedure p1
is
begin
dbms_output.put_line('a value is'||a);
end p1; --end of procedure p1
function fun1(a number) return number --implementation of function
fun1
is
begin
return a;
end fun1; --end of function fun1
end p111; --end of package body
/
How to compile and execute a procedure?
SQL> @pack1.sql-----------compilation of package and body is
done simultaneously
Package created.
Package body created.
After compilation of that package we need
to execute the procedure and function
SQL> exec p111.p1(12)----------execution of procedure
a value is12---------------------------output
PL/SQL procedure successfully completed.--message for confirmation
SQL> select p111.fun1(1222) from dual;-------------------execution
of function
P111.FUN1(1222) ------------output
---------------
1222
Note:
while compilation if we get any errors then sql-plus command
"show errors" is used to
display the compilation errors for the most recently compiled
function or procedure or package body or package or tigger
SQL>show errors [show err or sho err]
Then errors will be displayed with line numbers on the screen or
we can use
select * from user_errors; to view the errors.
A procedure or a function implemented in packages can be called
from another procedure and function using the statement
<package name>.<procedure name>(parameters).we need to
keep the key word "execute " or exec
before the statement
Syntax:- execute <package name>.<procedure
name>(parameters)
Oracle offers several pedefined packages and procedures that can
be user by application developers.The one we use if very often is
DBMS_OUTPUT.This package allows user to display the information on to the
screen.
Some of the procedures of this package are:
Procedure name
=============
DBMS_OUTPUT.DISABLE ----------------disables the output.
DBMS_OUTPUT.ENABLE ----------------enables the output.
DBMS_OUTPUT.PUT----------------appends string to output buffer.
DBMS_OUTPUT.PUT_LINE----------------appends string to output buffer
and
append a new line.
DBMS_OUTPUT.NEW_LINE----------------displays the new line.
UTIL_FILE ----------------for reading and writing files.
DBMS_JOB ----------------for job scheduling..
DBMS_SQL ----------------for generation sql statements
dynamically(ie create and delete tables from
pl/sql programs ).
More packages can be found in the directory $ORACLE
HOME/rdbms/admin.
In packages overloading concept is also
used for the users to design in a more friendly way.
In packages procedures and functions can be overloaded in the
sense that procedure name
or function name should be of the same but there should be a
difference in the passing of parameters.As
per the parameter passing a certain procedure or function is
called accordingly.
create
or replace package p112
as
--starting
of package specification
procedure
p1(a number); --single parameterised procedure
procedure
p1(a number,b number); --overloaded procedure
function
fun1(a number) return number; --single parameterised function
function
fun1(a number,b number) return number; --overloaded function
end
p112; --end of package specification
/
--starting
of package body
create
or replace package body p112
as
procedure
p1(a number) --implementation of single parameter
procedure
p1
is
begin
dbms_output.put_line('a
value is'||a);
end
p1; --end of single parameter procedure p1
procedure
p1(a number,b number) --implementation of overloaded procedure
is
begin
dbms_output.put_line('addition
of two numbers is '||(a+b));
end
p1; --end of overloaded procedure
function
fun1(a number) return number--implementation of single parameter
function
fun1
is
begin
return
a;
end
fun1; --end of single parameter function fun1
function
fun1(a number,b number) return number --implementation of overloaded function
fun1
is
begin
return
a+b;
end
fun1; --end of overloaded function fun1
end
p112; --end of package body
/
Cursors:
➢ Oracle engine uses a work area for its internal processing in order
to execute an SQL statement. This work area is private to SQL's operations and
is called a cursor.
➢ 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.
➢ The data that is stored in the cursor is called the Active Data
Set.
➢ The values retrieved from a table are held in a cursor opened in
memory by the Oracle Engine.
➢ Types of cursors:There are two types of cursors
implicit cursor
explicit cursor
Implicit Cursor:
If oracle engine opened a cursor for its internal processing it is
know as Implicit Cursor.
SQL is the name of the cursor opened implicitly anytime the SQL
statement being processed is not a
part of an explicitly named and opened cursor.
Implicit cursor attributes:
Attribute Name Description
%isopen The SQL%ISOPEN attribute of an implicit cursor
cannot be referenced outside of its SQL statement.
As a result SQL%ISOPEN always evaluates to
false.
%FOUND Evaluates true,affected a single-row select returned
one or more rows. Otherwise it evaluates false
.syntax is SQL%FOUND
%NOTFOUND Is the Logical opposite of %FOUND. It evaluates
true select returns no rows. Otherwise it evaluates
false. Syntax is SQL%NOTFOUND
%ROWCOUNT Returns number of rows affected by a select
statement. Syntax is SQL%ROWCOUNT
Example:
declare
emp_no
number;
row_count
number;
begin
emp_no:=&emp_no;
update
emp set sal=sal+1000 where empno=emp_no;
if
SQL%FOUND then
dbms_output.put_line('record
updated');
end
if;
if
SQL%NOTFOUND then
dbms_output.put_line('record
not found');
end
if;
row_count:=SQL%ROWCOUNT;
if
SQL%ROWCOUNT>0 then
dbms_output.put_line(row_count
||' row updated');
end
if;
end;
/
Explicit Cursor:
A cursor created and used is know as an explicit cursor.
This cursor will be declared and mapped to an SQL query in the
declare section of the pl/sql block and is used with in its executable section
.
Follow these steps to use a explicit cursor:
● Declare Cursor—A cursor declaration specifies the cursor name and
the SELECT
statement to generate the results.
Ex: DECLARE <cursor name> CURSOR is <SELECT statement>
● Open Cursor—Opening a cursor executes the associated SELECT
statement and
positions the cursor at the beginning of the result.
Ex: OPEN <cursor name> ;
● Fetch Rows—Each fetch retrieves the next row in the result. After
the last row has
been fetched, subsequent calls to FETCH signal that there are not
more data to fetch.
Ex: FETCH <cursor name> INTO [<target list>]
The <target list> specified the destination of the row
values. Usually, these values
are placed into host variables.
● Close Cursor—Closing a cursor deallocates associated system
resources.
Ex: CLOSE <cursor name>;
Explicit Cursor Attributes :
Attribute Name Description
%FOUND Evaluates to true,if the last fetch succeeded
because a row was available or false if the last
fetch failed . Syntax is Cursorname%FOUND.
%NOTFOUND Is the Logical opposite of %FOUND. It evaluates
true if the last fetch failed .
Otherwise it evaluates false if the last fetch
returns a row. Syntax is SQL%NOTFOUND
%ISOPEN Evaluates to true,If an explicit cursor is open or to
false if it is closed. Syntax is
cursorname%ISOPEN.
%ROWCOUNT Returns number of rows affected by a select
statement. Syntax is Cursorname%ROWCOUNT.
1)cursor using rowtype variable
Ex : cursor using row type
declare
cursor
c1 is select * from emp; --select all records into cursor
r1
c1%rowtype; --declare variable as row type
begin
open
c1; --open cursor
dbms_output.put_line('empno
ename salary deptno ');
dbms_output.put_line('-----------------------------------');
loop
fetch
c1 into r1; --fetch records into variable
exit
when c1%notfound; --exit when records not found
dbms_output.put_line(r1.empno||'
'||r1.ename||' '||r1.sal||' '||r1.deptno);
end
loop;
close
c1; --close cursor
end;
/
2)cursor using column type variables
declare
cursor
c1 is select empno,ename,sal,deptno from emp;
emp_no
emp.empno%type;
e_name
emp.ename%type;
salary
emp.sal%type;
dept_no
emp.deptno%type;
begin
open
c1; --open cursor
dbms_output.put_line('empno
ename salary deptno ');
dbms_output.put_line('--------------------------------------------');
loop
--to iterate all records
fetch
c1 into emp_no,e_name,salary,dept_no;
exit
when c1%notfound; --exit when no
records
dbms_output.put_line(emp_no||'
'||e_name||' '||salary||' '||
dept_no);
end
loop;
close
c1;
end;
/
3)cursor using for loop
In a cursor FOR loop ,the opening,fetching,and closing of cursors
is performed implicitly. You don't
need to explicitly code these actions.
Example:
declare
cursor
c1 is select * from emp;
begin
dbms_output.put_line('empno
ename salary deptno ');
dbms_output.put_line('--------------------------------------------');
for
i in c1
loop
exit
when c1%notfound;
dbms_output.put_line(i.empno||'
'||i.ename||' '||i.sal||' '||
i.deptno);
end
loop;
end;
/
Parameterized Cursor:
The cursor accepts user_defined values into
its parameters,thus changing the result set extracted,it is called as
parameterized cursor.
Declaring a parameterized cursor
Syntax:
declare
cursor <cursor-name>(variable datatype) is <select
statement..>
begin
open <cursor-name(variable/value/expression);
Example:
--parameterized cursor
declare
cursor
c1(emp_no number) is select * from emp where empno=emp_no;
r1
c1%rowtype; --declare variable as row type
begin
open
c1(&emp_no); --open cursor
loop
fetch
c1 into r1; --fetch records into variable
exit
when c1%notfound; --exit when records not found
dbms_output.put_line('empno
ename salary deptno ');
dbms_output.put_line('-----------------------------------');
dbms_output.put_line(r1.empno||'
'||r1.ename||' '||r1.sal||' '||r1.deptno);
end
loop;
close
c1; --close cursor
end;
/
Exception Handling:
➢ Exceptions are used to process errors and warnings that occur
during the execution of PL/SQL
statements in a controlled manner.
➢ Each error or warning during the execution of a PL/SQL block
raises an exception
➢ if an exception is raised with in the executable commands section
of your pl/sql block the flow of commands immediately leave's the executable
section and searches exception handling
section
➢ Some exceptions are internally defined, such as ZERO DIVIDE. Other
exceptions can be
specified by the user at the end of a PL/SQL block.
Syntax:
declare
<declarations section>
begin
<executable commands>
exception
<exception handling>
end;
there are two types of exceptions :
• system defined exceptions/oracle named exceptions
• user defined exceptions
System defined exceptions:
➢ System defined exceptions always automatically raised whenever
corresponding errors or warnings occur.
➢ The most common errors that can occur during the execution of
PL/SQL programs are handled by system defined exceptions
➢ The last when clause in the exception part may contain the
exception name others. This introduces the default exception handling.
pre-determined internal pl/sql exceptions
NO_DATA_FOUND A select . . . into or fetch statement returned no
tuple
TOO_MANY_ROWS A select . . . into statement returned more than one
tuple
ZERO_DIVIDE You have tried to divide a number by 0
DUP_VAL_ON_INDEX Raised when an insert or update attempts to
create
two rows with duplicate values in columns
constrained by a unique index.
LOGIN_DENIED Raised when an invalid username/password was
used to log onto oracle.
NOT_LOGGED_ON Raised when pl/sql issues an oracle call without
being logged onto oracle.
PROGRAM_ERROR Raised when pl/sql has internal problem.
VALUE_ERROR Raised when data type or data size is invalid
CURSOR_ALREADY_OPEN You have tried to open a cursor which is
already
open
INVALID_CURSOR Invalid cursor operation such as fetching from a
closed cursor
OTHERS stands for all other exceptions not explicitly
named.
Example:
declare
emp_no
number;
salary
number;
begin
emp_no:=&emp_no;
select
sal into salary from emp where empno=emp_no;
if(salary>2000)
then
update
emp set sal=salary+500 where empno=emp_no;
dbms_output.put_line('record
updated');
end
if;
exception
when no_data_found then --system defined excetion
dbms_output.put_line('record
not found');
when
others then --when other exception raised
dbms_output.put_line('record
not updated');
end;
/
User defined exceptions:
➢ which must be declared by the user in the declaration part of a
block where the exception isused/implemented
➢ User defined exceptions need to be declared using <name of
exception> exception.
must be raised explicitly in a sequence of statements using raise
<exception name>.
➢ After the keyword exception at the end of a block, we have to
write in exception block
exception when <exception name> then <sequence of
statements>;
Example:
declare
emp_no
number;
salary
number;
min_sal
exception; --exception name
begin
emp_no:=&emp_no;
select
sal into salary from emp where empno=emp_no;
if(salary<2000)
then
raise
min_sal; --raise exception
else
update
emp set sal=salary+500 where empno=emp_no;
dbms_output.put_line('record
updated');
end
if;
exception
when min_sal then
dbms_output.put_line('you
should have minimum 2000 sal to get
increment');
when
others then
dbms_output.put_line('record
not updated');
end;
/
pragma :
➢ pragma is used to bind a numbered exception handler to a name
using pragma
exception_init().this binds of a nubered exception handler,to a
name ,is done in the declare section of a pl/sql block.
➢ The function exception_init() takes two parameters the first is
user defined exception name the
second is the oracle engines exception number.
Syntax:
declare
<exception anme> Exception;
pragma Exception_init(<exception name>,<error code>)
begin
.......
exception when <exception name> then
<action>;
end;
example:
declare
emp_no
number;
salary
number;
min_sal
exception;
pragma
exception_init(min_sal,-00054);
begin
emp_no:=&emp_no;
select
sal into salary from emp where empno=emp_no;
if(salary<2000)
then
raise
min_sal;
else
update
emp set sal=salary+500 where empno=emp_no;
dbms_output.put_line('record
updated');
end
if;
exception
when min_sal then
dbms_output.put_line('you
should have minimum 2000 sal to get increment');
end;
/
Triggers:
Unlike integrity constraints such as primary keys ,foreign keys or
other
constraints.Complex integrity constraints that refer to several
tables and attributes cannot be specified within table definitions.triggers in
contrast,providea procedural technique to spefcify and mainatain
integrity constraints.A trigger is a pl/sql procedure it allows
users to specify more complex integrity constraints.This kind of procedures are
associated with the table and is automatically called by the database system
when ever a certain modification is done on that table ie modifications like insert,update
and delete operations.
Raise Application Error:
Inorder to display the error messages for each of the error
conditions you define,you may
select an error message that appears when an error occurs.the
error numbers and messages that are
dispalyed to the user are set thru the raise_application_error
procedure.
This procedure has two parameters <error number>and
<message text>.
<error number>: is a negative integer defined by the user
and must range
between -20000 and -20999.
<error message>: is a string with a length up to 2048
characters.
Syntax:-
create or replace trigger <trigger name>
before/after
insert or update [of <column(s)>]or delete on <table>
for each row
when(<condition>)
begin
end;
Explaination
before/after :=a trigger can be invoked before and after the
event.It tell us that if any operations that are performed on the table
accordingly the trigger is fired before or after that is specified in the
program.
insert or update [of <column(s)>]or delete on <table>:
These are DML statements which are to be used to perform operations on the
table .
for each row: There are two types of triggers they are statement
level trigger and row level trigger.A
level trigger is used when you specify for each row if this is not
mentioned then it is statement level tigger.
Example for restricting the balance from
entering or deducting below 500/- from a table.
create
or replace trigger bal1 before update or insert on bb1 for each row
begin
if(:new.bal<500)
then
raise_application_error(-20001,'balance
cannot be less than 500');
end
if;
end;
/
compilation:
SQL> @"trig1.sql"
Trigger created.
First create a table
create table bb1(actno number,bal number,name varchar2(15))
SQL> /
Table created.
SQL> desc bb1
Name Null? Type
-----------------------------------------
-------- ----------------------------
ACTNO NUMBER
BAL NUMBER
NAME VARCHAR2(15)
if you try to insert the table balance less
than 500 then an trigger fires:
SQL> insert into bb1 values(123,344,'aa');
insert into bb1 values(123,344,'aa')
*
ERROR at line 1:
ORA-20001: balance cannot be less than 500—these are userdefined
messages
ORA-06512: at "ORACLE03.BAL1", line 3
ORA-04088: error during execution of trigger 'ORACLE03.BAL1'
insert into bb1 values(123,500,'chaitanya');
1 row created.
SQL> select * from bb1;
ACTNO BAL NAME
---------- ---------- ---------------
123 500 chaitanya
if you try to update the table balance less
than 500 then an trigger fires:
SQL> update bb1 set bal=234 where ACTNO=123;
update bb1 set bal=234 where ACTNO=123
*
ERROR at line 1:
ORA-20001: balance cannot be less than 500—this is userdefined
message
ORA-06512: at "ORACLE03.BAL1", line 3
ORA-04088: error during execution of trigger 'ORACLE03.BAL1'
There are some keywords which are used in control statements from
restricting the table in
the forms of:
oracle engine directly recognises the keywords accordingly to
their name specifed
1)inserting
2)updating
3)deleting
Example for each of the above commands are
Inserting
create
or replace trigger indupdt before insert on stud for each row
begin
if
inserting then
raise_application_error(-20031,'cannot
insert records into this table');
end
if;
end;
/
Updating:
create
or replace trigger indupdt1 before update on stud for each row
begin
if
updating then
raise_application_error(-20031,'cannot
insert records into this table');
end
if;
end;
/
Deleting:
create
or replace trigger ind before delete on stud for each row
begin
if
deleting then
raise_application_error(-20033,'cannot
delete records from this table');
end
if;
end;
/
THANKS FOR VIEWING MY BLOG FOR MORE UPDATES VISIT MY BLOG
