Scribd
Upload
9 views12 pages

PL/SQL Procedures and Execution Methods

The document provides an overview of PL/SQL subprograms, specifically procedures and functions, detailing their structure, syntax, and execution methods. It explains the components of a procedure, including specification and body, and illustrates with examples how to create and execute procedures for various tasks, such as displaying customer and employee details. Additionally, it covers properties of procedures and includes sample code for dynamic procedures and handling input parameters.

Uploaded by

Carl Cj
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
9 views12 pages

PL/SQL Procedures and Execution Methods

The document provides an overview of PL/SQL subprograms, specifically procedures and functions, detailing their structure, syntax, and execution methods. It explains the components of a procedure, including specification and body, and illustrates with examples how to create and execute procedures for various tasks, such as displaying customer and employee details. Additionally, it covers properties of procedures and includes sample code for dynamic procedures and handling input parameters.

Uploaded by

Carl Cj
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

SUB PROGRAMS

************
Sub program is a database object.
It will be created in the database server permenantly.
Sub program is created with CREATE command.

In pl/sql, sub programs are 2 types.

1) PROCEDURES
2) FUNCTIONS

STORED PROCEDURES or PL/SQL PROCEDURES:


***************************************
A pl/sql program which is stored / created under
the data base server is known stored procedure.
It is also known as a subprogram to perform a task or set of
tasks.

PROCEDURE HAS 2 PARTS

--> Specification
--> Body

i) Specification
----------------
It contains object Type, object name and arguments.

Argument is a variable, that is declared in the specification part.


By default argument is useful to accept input value at runtime.

ii) Body
--------
It contains declarations, begin block, exception block and
End of procedure.

syntax:
/*Procedure specification*/

create [or replace ] PROCEDURE <proc_name>


[( arg1 MODE datatype, arg2 MODE datatype , . . . . )]

IS / AS
------
/* Procedure body */
------
<declaration stmts>;
BEGIN
<assignment stmts>;
<data processing stmts>;
<output stmts>;
EXCEPTION
<error handling stmts>;
END <proc_name>;
/ (Hit Enter) ( to compile the procedure in SQL * PLUS window )

Procedure created.

HOW TO EXECUTE A PROCEDURE?


By using 2 methods

1) Making a call:

That is by writing " procedure calling stmt ".


A procedure can be called from other Program, procedure,
function, package and triggers.

SYN:
Proc_Name[(Arg_val1, Arg_val2, ....)];

2) With EXECUTE statement

EXECUTE <proc_name>[(arg_val1, arg_val2,.....)];

or

EXEC <proc_name>[(arg_val1, arg_val2,.....)];

Properties of a Procedure:-
***************************
-->Procedure can be stored as a precompiled object.
-->Means it will be compiled once and executed any number
of times.
-->Procedures can be easily enhansible for future requirements.
-->Procedures may take or may not take arguments.
-->Procedure never called by SELECT query
-->By Default,Procedure cannot return any value to the calling
object ( Program or procedure or function).

-----------------------------------------------------------------------------------
------

Ex:
write a procedure to display account details of customer id "11110" ?

CREATE or replace PROCEDURE PROC_CUSTactinfo


IS
vact_rec cust_act_dtls%rowtype;
begin
select * into vact_rec
from cust_act_dtls
where cno='11110';
dbms_output.put_line
(chr(10)||' customer id: 11110'||chr(10)||
'********************************'||chr(10)||
'Actno: '||vact_rec.actno||chr(10)||
'Act_type: '||vact_rec.act_type||chr(10)||
'Open Dt: '||vact_rec.act_open_date||chr(10)||
'Balance: '||vact_rec.act_bal||chr(10)||
'********************************'
);
END proc_custactinfo;

Ex:
Write a procedure to find number of customers from the city
"Delhi"?

create or replace procedure proc_cust_cnt_delhi


is
vcnt int;
begin
select count(*) into vcnt
from cust_dtls where city='Delhi';
dbms_output.put_line
(chr(10)||
'City: Delhi'||chr(10)||
'Customer Count: '||vcnt
);
end;

Ex:
write a procedure which is finding number of emps working
under dept 30?

create procedure proc_empcount_30


is
e_count int;
begin
select count(*) into e_count
from emp
where deptno=30;
dbms_output.put_line
(' Number of emps under deptno 30 are: '||e_count);
end proc_empcount_30;

Ex: execute proc_ecount_30; /* Executing procedure explicitly */

How do i execute a procedure implicitly?


By making a call to the procedure we can implicitly execute the procedure.

--Calling a procedure from any program

BEGIN
proc_ecount_30; --Procedure calling stmt
dbms_output.put_line(' calling procedure is finished');
dbms_output.put_line(' Program Execution is finished');
end;

--WRITING THE PROCEDURE TO DISPLAY THE NUMBER


OF EMPS IN THE GIVEN DEPTNO?

CREATE OR REPLACE PROCEDURE PROC_empcnt_did


(VDNO [Link]%TYPE)
IS
CNT INT;
BEGIN
SELECT COUNT(*) INTO CNT
FROM EMP
WHERE DEPTNO=VDNO;
DBMS_OUTPUT.PUT_LINE
(chr(10)||
' NUMBER OF EMPS IN DEPT : '||VDNO||': ARE : '||CNT);
END PROC_empcnt_did;

SAMPLE EXECUTIONS:

EXEC PROC_2(10);
EXEC PROC_2(20);
EXEC PROC_2(30);

anonymous block completed


NUMBER OF EMPS IN DEPT 10 ARE 2

anonymous block completed


NUMBER OF EMPS IN DEPT 20 ARE 5

anonymous block completed


NUMBER OF EMPS IN DEPT 30 ARE 6

ex:

declare
vdno int;
begin
vdno:=&vdno;
proc_ecount_did(vdno); --Procedure calling statement
dbms_output.put_line(' Exec Finished');
end;

WRITING PROCEDURE CALLING STATEMENTS IN A PROGRAM:-

For the following program we need to create


2 procedures called p_add and p_multi.

declare
x int;
y int;
begin
x:=&x;
y:=&y;
dbms_output.put_line(' adding '||x ||' and '||y);
Dbms_output.put_line(' Making a call to P_ADD procedure');
p_add(x,y);
dbms_output.put_line(' Multiplication of '||x||' and '||y);
dbms_output.put_line(' make a call to P_MULTI procedure');
p_multi(x,y);
dbms_output.put_line(' 2 tasks successfully completed');
end;

Ex:
wap to display customer name,city,account name,actno and bal
from given customer id?

create or replace procedure proc_cust_info


(vcid cust_dtls.cno%type)
as
type cust_type IS RECORD
(
cname cust_dtls.cname%type,
city cust_dtls.city%type,
act_name act_types.act_name%type,
actno cust_act_dtls.actno%type,
actbal cust_act_dtls.act_bal%type
);
Vcustrec cust_type;
begin
select [Link],[Link],at.act_name,[Link],cad.act_bal
into
vcustrec
from cust_dtls cd, cust_act_dtls cad, act_types at
where [Link]=vcid
and
(
[Link]=[Link]
and
cad.act_type=at.act_type
);
dbms_output.put_line
(chr(10)||' Given customer Id: '||vcid||chr(10)||
'-----------------------------------------------'||chr(10)||
'Cust name: '||[Link]||chr(10)||
'City: '||[Link]||chr(10)||
'Account Name: '||vcustrec.act_name||chr(10)||
'Account Number: '||[Link]||chr(10)||
'Balance: '||[Link]
);
end proc_cust_info;
/

Ex:
write a procedure to display the customer details and
his account details for the custid "cust-5"?

create or replace procedure proc_CUST_ACT_DTLS


is
v_cname cust_dtls.cname%type;
v_gender cust_dtls.gender%type;
v_city cust_dtls.city%type;
v_actno cust_act_dtls.actno%type;
v_act_type cust_act_dtls.act_type%type;
v_act_bal cust_act_dtls.act_bal%type;
begin
select [Link],[Link],[Link],[Link],ca.act_type,ca.act_bal INTO
V_CNAME,v_gender,v_city,v_actno,v_act_type,v_act_bal
from cust_dtls c , cust_act_dtls ca
where [Link]='cust-5'
and
[Link]=ca.cust_code;
dbms_output.put_line(' Details of customer 5');
dbms_output.put_line(v_cname||' '||v_gender||' '||v_city||' '||v_actno||' '||
v_act_type||' '||v_act_bal);
end proc_CUST_ACT_DTLS;

Ex:
write a procedure to display the customer details and his account
details for the GIVEN custid ?

create or replace procedure proc_3


(VCID cust_dtls.cno%type)
is
v_cname cust_dtls.cname%type;
v_gender cust_dtls.gender%type;
v_city cust_dtls.city%type;
v_actno cust_act_dtls.actno%type;
v_act_type cust_act_dtls.act_type%type;
v_act_bal cust_act_dtls.act_bal%type;
begin
select [Link],[Link],[Link],[Link],ca.act_type,ca.act_bal
INTO
V_CNAME,v_gender,v_city,v_actno,v_act_type,v_act_bal
from cust_dtls c , cust_act_dtls ca
where [Link]=VCID
and
[Link]=ca.cust_code;
dbms_output.put_line(' Details of customer '||VCID);
dbms_output.put_line
(v_cname||' '||v_gender||' '||v_city||' '||v_actno||' '||v_act_type||
' '||v_act_bal);
end proc_3;

Ex:
Ex:
write a procedure to display the customer details and his account
details for the GIVEN custid ?

create or replace procedure proc_3


(VCID cust_dtls.cno%type)
is
type cust_type is record
(
custname cust_dtls.cname%type,
custcity cust_dtls.city%type,
gender cust_dtls.gender%type,
actno cust_act_dtls.actno%type,
act_type cust_act_dtls.act_type%type,
act_bal cust_act_dtls.act_bal%type
);
vactdtls cust_type;
begin
select [Link],[Link],[Link],
[Link],cad.act_type,cad.act_bal
INTO
vactdtls
from cust_dtls c , cust_act_dtls cad
where [Link]=VCID
and
[Link]=[Link];
dbms_output.put_line
(' Details of customer: '||VCID||CHR(10)||
'************************************');
dbms_output.put_line
(
chr(10)||
'Name: '||[Link]||chr(10)||
'City: '||[Link]||chr(10)||
'gender: '||[Link]||chr(10)||
'A/c No: '||[Link]||chr(10)||
'A/c Type: '||vactdtls.act_type||chr(10)||
'Balance: '||vactdtls.act_bal
);
end proc_3;

Ex: write a procedure to display the employee details and dept details for the
empno 7902?

create or replace procedure proc_4


is
v_ename [Link]%type;
v_job [Link]%type;
v_sal [Link]%type;
v_hiredate [Link]%type;
v_dno [Link]%type;
v_dname [Link]%type;
v_loc [Link]%type;
begin
select [Link],[Link],[Link],[Link],[Link],[Link],[Link] INTO
v_ename,v_sal,v_job,v_hiredate,v_dno,v_dname,v_loc
from emp e , dept d
where [Link]=7902 and [Link]=[Link];
dbms_output.put_line('Details of empno= 7902');
dbms_output.put_line(v_ename||' '||v_job||' '||v_sal||' '||v_hiredate||' '||
v_dno||' '||v_dname||' '||v_loc);
end proc_4;

DYNAMIC PROCEDURES:

EX: write a procedure to display the details of employee for the given employee id?

create or replace procedure proc_5 ( vempid [Link]%type)


is
emp_rec emp%rowtype;
begin
select * into emp_rec from emp where empno=vempid;
dbms_output.put_line(' employee details of '||vempid);
dbms_output.put_line('
***********************************************************************');
dbms_output.put_line(emp_rec.ename||','||emp_rec.sal||','||emp_rec.job||','||
emp_rec.hiredate);
end proc_5;

sample output:

employee details of 7902


FORD,3000,ANALYST,03-DEC-81

anonymous block completed


employee details of 7788
SCOTT,3000,ANALYST,19-APR-87

anonymous block completed


employee details of 7654
MARTIN,1250,SALESMAN,28-SEP-81

Ex: write a procedure to display the customer mobile number and name of the given
custid if he is male and from the city delhi?

desc cust_dtls
Name Null Type
---------- ---- ------------
CUSTID CHAR(4)
CUSTNAME VARCHAR2(20)
CUSTCITY VARCHAR2(10)
CUSTGENDER CHAR(1)
CUSTMOBILE NUMBER(10)

create or replace procedure proc_5(vcid cust_dtls.cno%type) is


--vmobile cust_dtls.mobile%type;
vcustname cust_dtls.cname%type;
begin
select cname into vcustname
from cust_dtls
where cno=vcid and gender='M' and city='Delhi';
dbms_output.put_line(' give customer id='||vcid);
dbms_output.put_line(' he is male and he is from delhi');
--dbms_output.put_line('mobile number is -'||vmobile);
dbms_output.put_line(' His name is '||vcustname);
end proc_5;

Ex: write a procedure to display number of emps working under given department
name?

Ex: write a procedure to display the number of customers from the given city with
given gender?
Ex: write a procedure to display the number of accounts of given account type?

1 create or replace procedure proc_insert_accounts


2 (argactno cust_act_dtls.actno%type,
3 argacttype cust_act_dtls.act_type%type,
4 argopendt cust_act_dtls.act_open_date%type,
5 argactbal cust_act_dtls.act_bal%type,
6 argcno cust_act_dtls.cno%type
7 )
8 is
9 reccnt int;
10 begin
11 select count(*) into reccnt
12 from cust_act_dtls;
13 dbms_output.put_line
14 (chr(10)||' old Record count : '||reccnt);
15 insert into cust_act_dtls values
16 (argactno,argacttype,argopendt,argactbal,argcno);
17 dbms_output.put_line
18 (chr(10)||' record inserted successfully');
19 select count(*) into reccnt
20 from cust_act_dtls;
21 dbms_output.put_line
22 (chr(10)||' New Record count : '||reccnt);
23* end proc_insert_accounts;
SQL> /

Procedure created.

SQL> declare
2 vactno cust_act_dtls.actno%type;
3 vacttype cust_act_dtls.act_type%type;
4 vactopendt date;
5 vactbal number;
6 vcno cust_act_dtls.cno%type;
7 begin
8 vactno:='&vactno';
9 vacttype:='&vacttype';
10 vactopendt:=sysdate;
11 vactbal:='&vactbal';
12 vcno:='&vcno';
13 proc_insert_accounts
14 (vactno,vacttype,vactopendt,vactbal,vcno);
15 end;
16 /
Enter value for vactno: 20035201489
old 8: vactno:='&vactno';
new 8: vactno:='20035201489';
Enter value for vacttype: CA
old 9: vacttype:='&vacttype';
new 9: vacttype:='CA';
Enter value for vactbal: 35000
old 11: vactbal:='&vactbal';
new 11: vactbal:='35000';
Enter value for vcno: cust-1
old 12: vcno:='&vcno';
new 12: vcno:='cust-1';
old Record count : 7

record inserted successfully

New Record count : 8

PL/SQL procedure successfully completed.

SQL>

Ex:
write a procedure to display number of customers from given
account type with min bal 20000?

SQL> create or replace procedure proc_cust_cnt


2 (vact_type cust_act_dtls.act_type%type)
3 is
4 vcnt int;
5 begin
6 select count(*) into vcnt
7 from cust_act_dtls
8 where act_type=vact_type
9 and
10 act_bal>=20000;
11 dbms_output.put_line
12 (chr(10)||
13 'Given account type: '||vact_type||chr(10)||
14 'Number of customers: '||vcnt
15 );
16 end;
17 /

Ex: Write a procedure to display the name,sal,job,


hiredate,deptno for the given employee id?

create or replace procedure proc_emp( veno [Link]%type)


is
type erec is record(
name [Link]%type,
esal [Link]%type,
desg [Link]%type,
jdate [Link]%type,
dno [Link]%type
);

rec erec;
begin
select ename,sal,job,hiredate,deptno into rec
from emp where empno=veno;
dbms_output.put_line(' Ename='||[Link]);
dbms_output.put_line(' EmpSal='||[Link]);
dbms_output.put_line(' Emp Job Title='||[Link]);
dbms_output.put_line(' Emp join date='||[Link]);
dbms_output.put_line(' Emp Dept No='||[Link]);
end proc_emp;
Advantages:

i) It is reducing the number of declarations of variables.


ii) It is reducing the amount of memory reserved for Record based variable.

Ex:
Write a procedure to display the name,cost and warrenty of the
product for the given product id?

Ex:
Write a procedure to display pname,cost,mfg,exp,companyName,Dealer
address,phonenumber for the given product id?

EX:
write procedure to display customer name,city,gender,account
name, balance from given account number?

create or replace procedure proc_cust


(vactno IN cust_act_dtls.actno%type)
is
TYPE custtype IS RECORD
(
cname cust_dtls.cname%type,
city cust_dtls.city%type,
gender cust_dtls.gender%type,
actname act_types.act_name%type,
actbal cust_act_dtls.act_bal%type
);
vrec custtype;
begin
select [Link],[Link],[Link],b.act_name,
c.act_bal
into vrec
from cust_dtls a inner join cust_act_dtls c
on
[Link]=vactno
and
([Link]=[Link]
inner join act_types b
on c.act_type=b.act_type);
dbms_output.put_line
(chr(10)||
'Account number: '||vactno||chr(10)||
'Customer name: '||[Link]||chr(10)||
'City: '||[Link]||chr(10)||
'Gender: '||[Link]||chr(10)||
'Account Name: '||[Link]||chr(10)||
'Account Bal: '||[Link]
);
end;
-----------------------------------------------------------------------------------
------

You might also like