Notes

------------------------PACKAGE WITH FUNCTION & PROCEDURES--------------------------------------------
------------------------------------------------------------------------------------------------------
----Creating a package named my_pack which consists of one Public function get_nm() and a procedure save_jobtype()---
--get_nm() returns the last name of an Employee when entered the Staff_id------
--save_jobtype() asks user to enter the staff id and the name of the employee
---and inserts the data in staff_stage table witht the job_type of the employee---------
--------------------------------------------------------------------------------------------------------
--Defining the header of the package-----
==========================================
create or replace package my_pack
is
procedure save_jobtype (pdno in integer, staff_name in varchar2);
function get_nm(st_id in integer)
return varchar2;
end;
/
=============================================

----Defining the body of the package----
=============================================
create or replace package body my_pack
is
----this is a function having a private scope which is not accesible outside the package----
----the get_dn is responsible to get the job_type and stored in dnm of the staff from the staff id entered--

function get_dn (pdno in integer)
return varchar2
is
dnm varchar2(25);
begin
select job_type into dnm from staff where staff_id = pdno;
return dnm;
end;

---- inserts into staff_stage table where pdno is staff_id, vt is job_type and staff_name is the name of the employe----
procedure save_jobtype (pdno in integer, staff_name in varchar2)
is
vt varchar2(25);
begin
vt:=get_dn(pdno);
insert into staff_stage values(pdno, vt, staff_name);
end;
-----public scope function which returns the last name of the employee----
function get_nm (st_id in integer)
return varchar2
is
snm varchar2(25);
begin
select staff_lname into snm from staff where staff_id = st_id;
return snm;
end;
end my_pack;
/
========================================================================================================
----create table staff_stage for the package to store the data---------
CREATE TABLE STAFF_STAGE(Staff_id INTEGER PRIMARY KEY, Job_type VARCHAR2(25), Name VARCHAR2(25));
---executing the procedure-----
execute my_pack.save_jobtype(102, 'THAPA');
---using the function inside the package my_pack-----
select my_pack.get_nm(102) from dual;



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

================TRIGGER 4=================================================================
create or replace TRIGGER BAND_LIMIT
AFTER
INSERT OR UPDATE OF STAGE_ID ON BAND
FOR EACH ROW
DECLARE
STAGE_SIZE NUMBER;
BEGIN
SELECT STAGE_SIZE INTO STAGE_SIZE FROM STAGE
WHERE
STAGE_ID = :NEW.STAGE_ID;
IF(STAGE_SIZE < 800)
THEN RAISE_APPLICATION_ERROR(-20007, 'THE STAGE ID '|| :NEW.STAGE_ID|| ' HAS SPACE BELOW 800');
END IF;
END;
================================================================================================
INSERT INTO BAND (BAND_ID, BAND_NAME, GENRE, BAND_CONTACT, AGENT_ID, STAGE_ID)
VALUES (107, 'THE LIFE', 'POP', '9123446278', 2, 105);