--let us know why to create a package
--1 create a function to calc square area
create or replace function square_area
( p_side number )
return number
is
begin
return p_side*p_side;
end;
select square_area(4) from dual;
--------------------------------------------
----1 create a function to calc rectangle area
create or replace function rectangle_area
( p_l number,p_w number )
return number
is
begin
return p_l*p_w;
end;
select rectangle_area(4,5) from dual;
----------------------------------------------------------
--Now because theses 2 functions are logically grouped,
--it is better to use package
--The code will be more organized
create or replace package area
is
function square_area( p_side number )
return number;
function rectangle_area( p_l number,p_w number )
return number;
--we dont have begin in package specification
end;
create or replace package body area
is
function square_area( p_side number )
return number
is
begin
return p_side*p_side;
end;
function rectangle_area( p_l number,p_w number )
return number
is
begin
return p_l*p_w;
end;
--the begin is optional--we use it for initilization
begin
DBMS_OUTPUT.PUT_LINE('welcome ');
end;
select area.square_area(4) from dual;
select area.rectangle_area(4,10) from dual;
begin
DBMS_OUTPUT.PUT_LINE(area.square_area(4));
end;
--so now no need for square_area, rectangle_area which created in step 1,2
drop function square_area;
drop function rectangle_area;
-------------------------------------------------------------------------------------------------------
drop table student;
create table student
( student_id number,
first_name varchar2(100),
birthday date,
constraint student_pk primary key (student_id)
);
drop sequence student_seq;
create sequence student_seq;
--we need to create a package for insert, delete, query a student
--1 we create the package spec.
create or replace package General_student
is
procedure insert_student
(p_first_name varchar2, p_birthday date);
procedure delete_student
(p_student_id number);
function get_name
(p_student_id number )
return varchar2;
end;
-------
--2 we create the package body
create or replace package body General_student
is
procedure insert_student
(p_first_name varchar2, p_birthday date)
is
begin
insert into student
values (student_seq.nextval,p_first_name,p_birthday);
commit;
exception
when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;
procedure delete_student
(p_student_id number)
is
begin
delete from student
where student_id =p_student_id ;
commit;
exception
when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;
function get_name
(p_student_id number )
return varchar2
is
v_name student.first_name%type;
begin
select first_name into v_name
from student
where student_id=p_student_id;
return v_name;
exception
when others then
return null;
end;
end;
-------------------------
execute general_student.insert_student ('khaled ahmed','10-may-81');
execute general_student.insert_student ('samer ahmed','10-may-82');
select * from student;
execute general_student.delete_student(1);
select * from student;
select general_student.get_name (2) from dual;
--------------------------------------------------------------------------------------------
--we can create package specification without body
--this used when we want to define global variable
create or replace package global_Measurement
is
c_mile_to_km constant number:=1.6093;
c_kilo_to_mile constant number:=0.6214;
end;
execute dbms_output.put_line('60 mile:='||60* global_Measurement.c_mile_to_km||' KM' );
execute dbms_output.put_line('100 KM:='||100* global_Measurement.c_kilo_to_mile||' Mile' );
-- now i can create function that read form this package
create or replace function get_mile_to_km
( p_value number )
return number
is
begin
return p_value* global_Measurement.c_mile_to_km;
end;
select get_mile_to_km(100) from dual;
-----------------------------------------------------
--very good thing you should know
--you can define a procedure or function inside pl/sql block
--but this will be used only in this block
declare
function get_sysdate
return date
is
begin
return sysdate;
end;
begin
dbms_output.put_line(get_sysdate);
end;
-----------------------------------------------------------------------------------
create or replace package p_test
is
c_var1 constant number:=10;
c_var2 varchar2(100):='welcome';
procedure print;
end;
------------
create or replace package body p_test
is
c_var3 varchar2(100):='hi there';
procedure print
is
c_var4 varchar2(100):='hi';
begin
dbms_output.put_line('this variable came from package spec. '||c_var1);
dbms_output.put_line('this variable came from package spec. '||c_var2);
dbms_output.put_line('this variable came from package body. '||c_var3);
dbms_output.put_line('this variable came from print Proc. '||c_var4);
end;
end;
execute p_test.print;
---------------------------------------------------------------
--note that we can update the package body without compile the spec.
create or replace package body p_test
is
c_var3 varchar2(100):='hi there';
procedure print
is
c_var4 varchar2(100):='hi';
begin
dbms_output.put_line('this variable came from package spec. '||c_var1);
dbms_output.put_line('this variable came from package spec. '||c_var2);
dbms_output.put_line('this variable came from package body. '||c_var3);
dbms_output.put_line('this variable came from print Proc. '||c_var4);
end;
begin
dbms_output.put_line('this is optional');
end;
execute p_test.print;
----------------------------------------------------
--now let us try to change the package spec
--not a major change
create or replace package p_test
is
c_var1 constant number:=10;
c_var2 varchar2(100):='welcome';
p_n number;
procedure print;
end;
execute p_test.print;
--now let us try to change the package spec again
--a major change/ add new subprogram
create or replace package p_test
is
c_var1 constant number:=10;
c_var2 varchar2(100):='welcome';
p_n number;
procedure print;
function get_name (p number) return varchar2;
end;
execute p_test.print;
-----------------------------------------
select * from user_objects
where oBject_name='P_TEST'
SELECT * FROM USER_SOURCE
WHERE NAME='P_TEST'
AND TYPE='PACKAGE';
SELECT * FROM USER_SOURCE
WHERE NAME='P_TEST'
AND TYPE='PACKAGE BODY';
--to drop package specification and body
drop package p_test;
--to drop only package body
drop package body p_test;
-------------------------------------------------------------------------------------
No comments:
Post a Comment