-- exemples4.sql

drop table t;
create table t(a integer, b integer);
insert into t values(1, 2);
insert into t values(3, 2);
insert into t values(4, 5);
insert into t values(4, 6);

set serveroutput on

-- curseur -------------------------------------------------

create or replace procedure affiche(v t.b%type) is
  cursor c is
    select a from t where b = v;
  x c%rowtype;
begin
  for x in c loop
    dbms_output.put_line(x.a);
  end loop;
end;
/

execute affiche(2);
execute affiche(5);
execute affiche(7);

-- SQL dynamique -------------------------------------------

create or replace procedure ex3 (t varchar2) 
-- authid current_user : pour create (droits executant)
is
  texte varchar2(200) := 'drop table';
begin
  texte := texte || ' ' || t;
  dbms_output.put_line(texte);
  execute immediate texte;
end;
/

create table s(a integer);
insert into s values(7);
select * from s;
execute ex3('s');
select * from s;

-- trigger -------------------------------------------------

create or replace trigger ex
  before delete 
  on t
  begin
    dbms_output.put_line('un delete sur t a eu lieu');
  end;
/

delete t where a = 4;

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