- Procedures
- Functions
- Packages
Procedures: Procedure is a named block or stored program or sub program which will be stored in the database table.
Procedures are developed to build business logics
Advantages: Once the procedured is created it will be stored in the table (user_source)
Once the procedure is available in the data base we can re-use it / call from other block.
Syntax: create or replace procedure procedure_name is
variable data types;
begin
Example:
SQL> ed proc1.sql
connect
username:system
password:myfamily
Set serverout on :first time connect to sql
create or replace procedure supplier_proc is
cursor supplier_cur is select * from supplier;
begin
for supplier_rec in supplier_cur
loop
dbms_output.put_line (supplier_rec.vendor_id||’ ‘||supplier_rec.vendor_name);
end loop;
end;
SQL> @ proc1.sql
procedure created
SQL> execute supplier_proc;
101 dell
102 hcl
103 hp
104 lenovo
105 nokiya
106 IBM
107 Samsung
110 Htc
111 Philips
Source of the procedure is available in user_source
SQL> select text from user_source where name like ‘SUPPLIER_PROC’;
TEXT
procedure supplier1_proc is
cursor supplier_cur is select * from supplier;
begin
for supplier_rec in supplier_cur
loop
dbms_output.put_line (supplier_rec.vendor_id||’ ‘||supplier_rec.vendor_name);
end loop;
end;
Calling above procedure from a program
begin
dbms_output.put_line (‘software’);
— calling procedure
supplier_proc;
dbms_output.put_line (‘hARDWARE’);
end;
Output
SQL> @ xyz.sql
7 /
software
101 dell
102 hcl
103 hp
104 lenovo
105 nokiya
106 IBM
107 Samsung
110 Htc
111 Philips
hARDWARE
Procedure can created with 3 types of parameters
- IN
- OUT
- IN OUT
