HomeOracle Page 2 - Database Interaction with PL/SQL, User-defined Packages
Database interaction using a PACKAGE - Oracle
This article is part of a series focusing on database interactions with Oracle PL/SQL. In my previous article, we examined named notation, default values of parameters, stored procedures, stored functions and finally took our first look at package and package body. In this article, we will focus completely on package and package body. Before reading this article I suggest you to go through my last three articles in this series thoroughly.
There would be nothing new in this section apart from the syntax of package. All the procedures and functions being implemented inside the package have been thoroughly discussed in my previous articles. So, let us have a good example of using a package interacting with a database.
create or replace package SamplePkg as procedure dispEmp; procedure dispDept; end SamplePkg; /
create or replace package body SamplePkg as
procedure dispEmp as cursor c_emp is select ename, sal from emp; begin for r_emp in c_emp loop dbms_output.put_line (r_emp.ename || ',' || r_emp.sal); end loop; end;
procedure dispDept as cursor c_dept is select deptno,dname from dept; begin for r_dept in c_dept loop dbms_output.put_line (r_dept.deptno || ',' || r_dept.dname); end loop; end;
end SamplePkg; /
To execute each of those procedures separately, you can use the following commands: