Monday, 3 October 2011

create package by using functions and procedures and execute packages

 CREATE OR REPLACE PACKAGE inv_pck_spec as
        FUNCTION inv_count(qty integer) RETURN integer;
        PROCEDURE inv_adjust(qty integer);
   END inv_pck_spec;
   

desc employee
------------------------------------------------------

 CREATE OR REPLACE PACKAGE BODY inv_pck_spec is
       FUNCTION inv_count(qty integer)RETURN integer is
           new_qty integer;
       BEGIN
           new_qty:= qty*6;
            INSERT into employee (id,salary) values ('01',new_qty);
           RETURN(new_qty);
       END inv_count;

      PROCEDURE inv_adjust(qty integer) is
     BEGIN
        DELETE from employee WHERE salary<qty;
      END;
     BEGIN  -- package initialization begins here
         INSERT into employee (id, first_name)values('01', 'new');

   END inv_pck_spec;
   /
   show error
----------call function
declare
a integer;
begin
a:=inv_pck_spec.inv_count(2);
dbms_output.put_line(a);
end;

-------------call procedures
begin
inv_pck_spec.inv_adjust(2000);
end;
-----------
select *From employee
-----------------------

No comments:

Post a Comment