Procedure/function are created to perform subtask
Create or replace procedure procedure_name(parameter [IN|OUT|IN OUT] type,…………….) is
Begin
<procedure body>
end;
create PROCEDURE findMin11(x IN number, y IN number, Z out number) IS
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END;
DECLARE
a number;
b number;
c number;
BEGIN
a:= 23;
b:= 45;
findMin11(a, b,c);
dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
create procedure sqr1(a in out number)
is
begin
a:=a*a;
end;
declare
a integer:=4;
begin
sqr1(a);
dbms_output.put_line('doubled value is'||a);
end;
ASSIGNMENT 6
Write a stored procedure to retrieve the salary and fname of employee whose ssn is 333445555 and works for
department number 5
create procedure emp_info11(eno in [Link]%type,b in [Link]%type, s out [Link]%type, d out
[Link]%type)
is
begin
select salary ,name into s,d from emp1 where ssn=eno and dno=b;
end;
declare
b [Link]%type;
c [Link]%type;
begin
emp_info11(777,1,b,c);
dbms_output.put_line('Salary of the employee is'||b);
dbms_output.put_line('Name of the employee is'||c);
end;
drop procedure emp_info11;
Functions
The PL/SQL Function is very similar to PL/SQL Procedure. The main difference between
procedure and a function is, a function must always return a value, and on the other
hand a procedure may or may not return a value.
Syntax
CREATE [OR REPLACE] FUNCTION function_name [parameters]
1. [(parameter_name [IN | OUT | IN OUT] type [, ...])]
2. RETURN return_datatype
3. {IS | AS}
4. BEGIN
5. < function_body >
6. END [function_name];
Create or replace function function_name (parametername [in|out|IN OUT] type….) return return type
Is
Begin
<function body>
End;
create or replace function adder(n1 in number, n2 in number)
return number
is
n3 number(8);
begin
n3 :=n1+n2;
return n3;
end;
DECLARE
n3 number(2);
BEGIN
n3 := adder(11,22);
dbms_output.put_line('Addition is: ' || n3);
END;
ASSIGNMENT 7
Write a stored function to retrieve the total number of dependents of employee with ssn 333445555
create function dept_info(s in [Link]%type)
return number
is
c integer;
begin
select count(*) into c from dependent where ssn=333445555;
return c;
end;
declare
r integer;
begin
r:=dept_info(333445555);
dbms_output.put_line('Total number of dependents are:'||r);
end;
drop function dept_info;
A package is a way of logically storing the subprograms
like procedures, functions, exception or cursor into a single common unit.
syntax
create or replace package packagename is/as
function functionname(parameter in/out/in out datatype….) return datatype;
procedure procedurename(parameter in/out/in out datatype…);
end packagename;
/
CREATE OR REPLACE PACKAGE BODY <package_name> IS/AS
FUNCTION <function_name> (<list of arguments>) RETURN <datatype>IS/AS
-- local variable declaration;
BEGIN
-- executable statements;
END <function_name>;
PROCEDURE <procedure_name> (<list of arguments>)IS/AS
-- local variable declaration;
BEGIN
-- executable statements;
END <procedure_name>;
END <package_name>;
Create a package which contains a procedure largest to find largest of two numbers and function to find sum of
two numbers.
create or replace package lar_sum is
function sum(a in number,b in number) return number;
procedure large(a in out number, b in number,z out number);
end lar_sum;
create or replace package body lar_sum is
function sum(a in number,b in number) return number is
h number;
begin
h:=a+b;
return h;
end sum;
procedure large(a in out number, b in number,z out number) is
begin
if a>b then
z:=a;
else
z:=b;
end if;
end large;
end lar_sum;
declare
a integer;
b integer;
c integer;
begin
a:=10;
b:=20;
c:=lar_sum.sum(a,b);
dbms_output.put_line('sum is'||c);
lar_sum.large(a,b,c);
dbms_output.put_line('largest is'||c);
end;
ASSIGNMENT 10:
Create a package prj_info which include one procedure named prj_insert to insert a new project details
controlled by department 1. Package also includes one function prj_count to find the total number of project
controlled by department 1.
create or replace package prj_info is
function prj_count(a in number) return number;
procedure prj_insert(p in [Link]%type, pn1 in [Link]%type,pl [Link]%type,dn
[Link]%type);
end prj_info;
create or replace package body prj_info is
procedure prj_insert(p in [Link]%type, pn1 in [Link]%type,pl [Link]%type,dn
[Link]%type)
is
begin
insert into project values(p,pn1,pl,dn);
if(sql%rowcount=1) then
dbms_output.put_line('A new row is inserted into project');
else
dbms_output.put_line('A no row is inserted into project');
end if;
end prj_insert;
function prj_count(a in number) return number is
h number;
begin
select count(*) into h from project where dnumber=a;
return h;
end prj_count;
end prj_info;
declare
s integer;
begin
prj_info.prj_insert(999,'ppp','Bombay',1);
s:=prj_info.prj_count(1);
dbms_output.put_line('Total no of project controlled by dept number one is:'||s);
end;