pl-sql programs

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm

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
-----------------------

create package and execute packages

create or replace package pkg_test1
    is
        function getArea (i_rad NUMBER) return NUMBER;
        procedure p_print (a in number,b in number,c out number);
    end pkg_test1;

--------------------------------------
create or replace package body pkg_test1
    is
        function getArea (i_rad NUMBER)return NUMBER
        is
          v_pi NUMBER:=3.14;
       begin
          return v_pi * (i_rad ** 2);
       end;

       procedure p_print(a in number,b in number,c out number)
       is
       begin
           c:=a+b;
       end;
   end;
   ------------------------------------------
   \
   --execution of packages:
   declare
   a NUMBER; 
   i number;
   g number;
   h number;
   begin
   a:=pkg_test1.getarea(&num);
   pkg_test1.p_print(&g,&h,i);
   dbms_output.put_line('the area value is='||a||'********'||i||g);
   end;

auditing by using of triggers

CREATE TABLE DEPT12
  ( DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13)
  );

 --------------------------------------------

 INSERT INTO DEPT12 VALUES
  (20, 'RESEARCH', 'DALLAS' );
 INSERT INTO DEPT12 VALUES
  (30, 'SALES', 'CHICAGO' );
 INSERT INTO DEPT12 VALUES
  (40, 'OPERATIONS', 'BOSTON' );
 CREATE TABLE DEPT$AUDIT
  (
    DEPTNO      NUMBER           ,
    DNAME       VARCHAR2(14 byte),
    LOC         VARCHAR2(13 byte),
    CHANGE_TYPE VARCHAR2(1 byte) ,
    CHANGED_BY  VARCHAR2(30 byte),
    CHANGED_TIME DATE
  );
 
select *From dept$audit



 ---------------------------------------------------
 CREATE OR REPLACE TRIGGER auditDEPTAR AFTER
 INSERT OR
 UPDATE OR
 DELETE ON DEPT12 FOR EACH ROW DECLARE my DEPT$audit%ROWTYPE;
BEGIN
  IF inserting THEN
    my.change_type := 'I';
  elsif updating THEN
    my.change_type :='U';
  ELSE
    my.change_type := 'D';
  END IF;
  my.changed_by   := USER;
  my.changed_time := sysdate;
  CASE my.change_type
  WHEN 'I' THEN
    my.DEPTNO := :new.DEPTNO;
    my.DNAME  := :new.DNAME;
    my.LOC    := :new.LOC;
  ELSE
    my.DEPTNO := :old.DEPTNO;
    my.DNAME  := :old.DNAME;
    my.LOC    := :old.LOC;
  END CASE;
   INSERT INTO DEPT$AUDIT VALUES my ;
END;
-------------------------------------
INSERT INTO DEPT12 VALUES
  (70, 'ACCOUNTING', 'NEW YORK'
  );
  -------------------------
  TRIGGER auditDEPTAR Compiled.
1 rows inserted
DEPTNO                 DNAME          LOC           CHANGE_TYPE CHANGED_BY                     CHANGED_TIME             
---------------------- -------------- ------------- ----------- ------------------------------ -------------------------
70                     ACCOUNTING     NEW YORK      I           APPS                           01-OCT-11                

before insert or delete trigger

create table Employee(
     ID                 VARCHAR2(4 BYTE)         NOT NULL primary key,
     First_Name         VARCHAR2(10 BYTE),
    Last_Name          VARCHAR2(10 BYTE),
    Start_Date         DATE,
     End_Date           DATE,
     Salary             Number(8,2),
     City               VARCHAR2(10 BYTE),
    Description        VARCHAR2(15 BYTE)
  )
 
  insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
  /
   insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
  /
   insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
  /
   insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
  /
   insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
  /
   insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values('06','Linda',    'Green',   to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York',  'Tester')
  /
 
  /
 
 create or replace trigger emp_bid
     before insert or delete
     on employee
     referencing new as new old as old
    begin
       if to_char(sysdate,'Dy') in ('Sat','Sun') then
            raise_application_error(-20999,'No create/delete employees on weekend!');
        end if;
    end;
   /
  --After creating trigger when you are inserting into the tables means that
 
   insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values('07','David',    'Larry',   to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York',  'Manager')
  /
  Error report:
SQL Error: ORA-20999: No create/delete employees on weekend!
ORA-06512: at "APPS.EMP_BID", line 3
ORA-04088: error during execution of trigger 'APPS.EMP_BID'

implicit cursor and using of sql%rowcount

declare
no NUMBER;
begin
delete from studs;
no:=sql%rowcount;
end;

answers:
It returns no of row in the studs table

procedure by using in parameter

CREATE OR REPLACE PROCEDURE SP_STUDENTS_UPDATE_BYPK
       (
          p_STUDENT_ID  IN stude1.STUDENT_ID%TYPE   , 
          p_ACTIVE_FLG  IN stude1.ACTIVE_FLG%TYPE                  
       )
AS
BEGIN

      UPDATE stude1
      SET
           ACTIVE_FLG     = p_ACTIVE_FLG                        
      WHERE  STUDENT_ID  = p_STUDENT_ID  ;

      COMMIT ;

EXCEPTION
        WHEN OTHERS THEN
             RAISE_APPLICATION_ERROR (-20001,
              p_STUDENT_ID    || ':$:' ||
              p_ACTIVE_FLG    || ':$:' ||
              SQLERRM, TRUE) ;

END SP_STUDENTS_UPDATE_BYPK ;


EXECUTE SP_STUDENTS_UPDATE_BYPK(1,1);