Questão 715843

cursor c_empregado (pdepnum number, pcargo varchar2) is

select empnum, enome

from emp

where depnum = pdepnum

and cargo > pcargo;

create view emp_dep_v as

select e.empnum, e.enome, e.cargo, e.sal, d.dnome

from emp e, dep d

where e.depnum = d.depnum;

create function row_count_emp return integer as rows integer;

begin

execute immediate 'select count(*) from emp' into rows;

return rows;

end;

procedure lista_gerente_salario is

begin

for r1 in c1 loop

tgerente(r1.dep_id) := r1;

end loop;

n := tgerente.first;

while n <= tgerente.last loop

dbms_output.put_line('Gerente: '||tgerente(n).nome||' '||

'Sal.: '||to_char(tgerente(n).sal, 'fm\$999g999g990d00'));

n := tgerente.next(n);

end loop;

end;

create trigger tig_emp after insert or delete or update of sal, comm on emp

declare

wnr_registros number default 0;

wvl_total_salario number default 0;

begin

select count(*) into wnr_registros

from emp;

select sum(sal) into wvl_total_salario

from emp;

insert into tab_audit_emp (nr_registros, vl_total_salario)

values (wnr_registros, wvl_total_salario);

end;