-- exemples5.sql

-------------------------------------------------------------------------------
-- exceptions et erreurs BD en PL/SQL

create or replace procedure ex4 (n integer) is
begin
  if n = 5
  then 
    raise_application_error(-20001, 'pb dans ex4 avec : ' || n);
  end if;
  dbms_output.put_line('fin normale');
end;
/

execute ex4(4);
execute ex4(5);

create or replace procedure ex5(t varchar2) is
  pas_de_table exception;
  pragma exception_init(pas_de_table, -942);
  texte varchar2(200);
begin
  texte := 'drop table '||t;
  dbms_output.put_line(texte);
  execute immediate texte;
  dbms_output.put_line('fin normale');
exception
  when pas_de_table then dbms_output.put_line('pb pas de table !');
end;
/

execute ex5('t');
create table t(a integer, b integer);
execute ex5('toto');

-------------------------------------------------------------------------------
-- encapsulation

drop table t;
create table t(nom varchar2(10) primary key, age integer);
insert into t values('toto', 19);
insert into t values('titi', 21);
insert into t values('tutu', 22);

-- but : permettre aux autres utilisateurs SGBD de mettre a jour t selon
-- un cahier des charges

-- retire tous les droits sur t
-- waller_a tape : 

grant select, insert, update, delete
on t
to waller2_a;

revoke select, insert, update, delete -- etc.
on t
from waller2_a;

create or replace procedure rajeunit(n t.nom%type) is
begin
  update t set age = age - 1 where nom = n;
end;
/

grant execute 
on rajeunit
to waller2_a;

-- waller2_a tape : 
execute waller_a.rajeunit('toto');

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