Procedures: A stored procedure is a subprogram unit that consists of a group of PL/SQL statements, stored in the database with a name. Each procedure in Oracle has its own unique name by which it can be referred. This subprogram unit is stored as a database object. A stored procedure can be called from client programs such as PL/SQL, Java programs, PHP programs etc.
Reduced network traffic : As only name is passed from client to server.
Better performance : As procedure is loaded into memory it stays in memory so that subsequent invocations can make use of procedure in memory. Procedures are stored in compiled format, which make them run faster.
Easy maintenance : As a change of business logic needs only change to procedure in the database. All client applications can immediately use new business logic.
Security : As it is possible to grant privilege to users to execute procedure even though no privilege is granted on table, there is no need to grant privilege on the table to others.
Parameter Modes: When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:
IN - An IN parameter is read-only. You can reference an IN parameter inside a procedure(or function), but you cannot change its value. Oracle uses IN as the default mode. It means that if you don’t specify the mode for a parameter explicitly, Oracle will use the IN mode.
OUT - An OUT parameter is writable. Typically, you set a returned value for the OUT parameter and return it to the calling program. Note that a procedure ignores the value that you supply for an OUT parameter.
IN OUT - An INOUT parameter is both readable and writable. The procedure can read and modify it.
Syntax:
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
Example:
SQL> select sysdate from dual;
SYSDATE
---------
27-OCT-21
SQL> select to_char(sysdate,'DAY') from dual;
TO_CHAR(S
---------
WEDNESDAY
Program on Procedure with no arguments :
Write PL/SQL block to demonstrate procedure with no arguments.
set serveroutput on
create or replace procedure rkdispdate
is
tempvar date;
cursor c1 is
select sysdate from dual;
begin
open c1;
fetch c1 into tempvar;
if c1%found then
dbms_output.put_line
('Details Of Todays Date Is :');
dbms_output.put_line
('Week Name :'||to_char(tempvar,'Day'));
dbms_output.put_line
('Day Number Is :'||to_char(tempvar,'DD'));
dbms_output.put_line
('Month Name Is :'||to_char(tempvar,'Month'));
dbms_output.put_line
('Year Is :'||to_char(tempvar,'YYYY'));
dbms_output.put_line
('Year In Words Is :'||to_char(tempvar,'Year'));
else
dbms_output.put_line('No Records Found');
end if;
close c1;
end;
/
Output:
Step1:
SQL> @C:\Users\student\Desktop\rk.sql
Procedure created.
Step 2:
Now type the exec <procedure name>
SQL> exec rkdispdate
Details Of Todays Date Is :
Week Name :Wednesday
Day Number Is :27
Month Name Is :October
Year Is :2021
Year In Words Is :Twenty Twenty-One
PL/SQL procedure successfully completed.
Program on Procedure with arguments :
Write PL/SQL block to demonstrate procedure with arguments.
create the following table in the database to proceed for procedure execution.
SQL> desc departments;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
LOCATION_ID NUMBER(4)
set serveroutput on
create or replace procedure depart_dtls(d_id number)
is
ddetails departments%ROWTYPE;
cursor c
is
select * into ddetails from departments where department_id>d_id;
begin
open c;
loop
fetch c into ddetails;
dbms_output.put_line(ddetails.department_name|| ' '||ddetails.location_id);
exit when c%notfound;
end loop;
end;
/
Output:
Step1:
SQL> @d:\abc.sql
Procedure created.
Step 2:
SQL> exec depart_dtls(200)
IT Support 1700
NOC 1700
IT Helpdesk 1700
Government Sales 1700
Retail Sales 1700
Recruiting 1700
Payroll 1700
Payroll 1700
PL/SQL procedure successfully completed.
Functions:
1) Write a PL/SQL script to create a function.
clear screen
set serveroutput on
create or replace function getsal(eid number)
return number
is
sal employees.salary%TYPE;
begin
select salary into sal from employees where employee_id=eid;
return sal;
end;
/
output:
Step1:
SQL> @d:\abc.sql
Function created.
Step 2:
begin
dbms_output.put_line('employee salary is :'||getsal(222));
end;
/
2)Write a PL/SQL script to create a function.
clear screen
set serveroutput on
create or replace function getage(dob IN date)
return number is
age number;
begin
age:=TRUNC((SYSDATE - dob)/365.25);
return age;
end;
/
output:
Step1:
SQL> @d:\abc7.sql
Function created.
Step 2:
SQL> begin
2 dbms_output.put_line('std age is :'||getage('28-feb-1979'));
3 end;
4 /
std age is :42
PL/SQL procedure successfully completed.
No comments:
Post a Comment