Sample PL/SQL Code For Basics- 2

Instead of  Trigger:

----create table 1


create table new_emps as select employee_id,last_name,salary,department_id,email,job_id,hire_date from employees;

-----create table 2

create table new_depts as select d.department_id,d.department_name,d.location_id,sum(e.salary) tot_dept_sal from departments d,employees e where d.department_id=e.employee_id group by d.department_id,d.department_name,d.location_id

-----create view

create view emp_detail as select e.employee_id,e.last_name,e.salary,e.department_id,e.email,e.j
d.department_name,d.location_id from employees e,departments d where e.department_id=d.department_id

-----create trigger

create or replace trigger new_emp_dept
instead of insert or update or delete on emp_detail for each row

begin

if inserting then insert into new_emps values(:new.employee_id,:new.last_name,:new.salary,:new.department_id,:new.email,:new.job_id,sy update new_depts set tot_dept_sal=tot_dept_sal+:new.salary where department_id=:new.department_id;
elsif deleting then delete from new_emps where employee_id=:old.employee_id;
update new_depts set tot_dept_sal=tot_dept_sal-:old.salary
where department_id=:old.department_id;
elsif updating ('salary') then
update new_emps set salary=:new.salary where department_id=:old.department_id;
update new_depts set tot_dept_sal=tot_dept_sal+(:new.salary-:old.salary) where department_id=:old.department_id;

elsif updating ('department_id') then
update new_emps set department_id=:new.department_id where employee_id=:old.employee_id;
end if;
end new_emp_dept;

/
Trigger Statement Level

create or replace trigger trigsar

before insert on employees
begin
if (to_char(sysdate,'DY') in ('SAT','SUN')) or(to_char(sysdate,'HH24:MI') not between '09:00' and '18:00') then raise_application_error (-20500,'You Can''t Insert in EMP during work Hrs...');
end if;
end trigsar;

Loop

declare

v_value number;
v_counter number:=1;
begin
loop
if v_counter=6 or v_counter=8 then null;
else
insert into messages(result)
values(v_counter);
v_counter:=v_counter+1;
end if;
exit when v_counter>10;
end loop;
end;

/
Variable Scope

<>

declare
v_sal number(7,2) :=60000;
v_comm number(7,2) :=v_sal*.20;
v_message varchar(250) := ' eligible for commission';

begin
<>
declare
v_sal number(7,2) :=50000;
v_comm number(7,2) :=0;
v_total number(7,2) :=v_sal+v_comm;
begin
v_message := 'Cleark Not'
v_message;
v_comm :=outer.v_sal*.30;
dbms_output.put_line (V_message);
dbms_output.put_line (v_comm);
end;
end;

/
UTL File:
 
create or replace procedure sal_status (p_filedir in varchar2,p_filename in varchar2)


is
v_filehandle utl_file.file_type;
cursor emp_info is
select last_name,salary,department_id from employees order by department_id;
v_newdept employees.department%type;
v_olddept employees.department%type :=0;

begin

v_filehandle :=utl_file.fopen(p_filedir,p_filename,'w');
utl_file.putf(v_filehandle,'SALARY REPORT: GENERATED ON %S\N',SYSDATE);
utl_file.put_line(v_filehandle);
for v_emp_rec in emp_info loop
v_newdept :=v_emp_rec.department_id;
if v_newdept<>v_olddept then
utl_file.putf(v_filehandle,'Department: %s\n',v_emp_rec.department_id);
end if;
utl_file.putf(v_filehandle,'Employee:%s earns:%s\n',v_emp_rec.last_name,v_emp_rec.salary);
v_olddept:=v_newdept;
end loop;
utl_file.put_line(v_filehandle,'****** End of Report******');
utl_file.fclose(v_filehandle);
exception
when utl_file.invalid_filehandle then
raise_application_error (-20001,'Invalid File');
when utl_file.write_error then
raise_application_error (-20002,'Unable to write');
end sal_status;
/