1 / 34

LDD et PL/SQL Patrice Saintenoy Université Paris X - Nanterre UFR SEGMI Licence MIAGE

LDD et PL/SQL Patrice Saintenoy Université Paris X - Nanterre UFR SEGMI Licence MIAGE. LDD ( Langage de description des Données). SEQUENCES. CREATE SEQUENCE [user.]nomSequence [INCREMENT BY n] [START WITH n] CREATE SEQUENCE eseq;

jeroen
Download Presentation

LDD et PL/SQL Patrice Saintenoy Université Paris X - Nanterre UFR SEGMI Licence MIAGE

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. LDD et PL/SQL Patrice Saintenoy Université Paris X - Nanterre UFR SEGMI Licence MIAGE

  2. LDD ( Langage de description des Données)

  3. SEQUENCES CREATE SEQUENCE [user.]nomSequence [INCREMENT BY n] [START WITH n] CREATE SEQUENCE eseq; INSERT INTO EMP VALUES(eseq.nextval, 'TOTO', 10000.00); DROP SEQUENCE eseq;

  4. Synonymes CREATE SYNONYM [user.]nomSym FOR [user.]nomTable; CREATE SYNONYM piece FOR TP.PIECE; DROP SYNONYM piece;

  5. INDEX CREATE [UNIQUE] INDEX nomIndex ON table(attribut [ASC | DESC], ...); CREATE INDEX indemp ON emp(numDept);

  6. Langage de contrôle et de sécurité

  7. Utilisateurs • Droits généraux GRANT privilèges TO utilisateurId IDENTIFIED BY motDePasse; CONNECT, RESOURCE, DBA • Droits sur les objets GRANT droits ON table | vue TO utilisateur [WITH GRANT OPTION]; SELECT , INSERT, UPDATE, DELETE, ALTER, INDEX, CLUSTER, ALL

  8. Utilisateurs (suite) REVOKE droits ON table TO nom;

  9. Rôles et privilèges grant create table to toto; grant alter any table to toto; create role lambda; grant select, insert on tab to lambda; grant create any view to lambda; grant lambda to toto; grant lambda to machin;

  10. Programmation et BD

  11. Différentes approches • Intégration dans un langage procédural par utilisation de pré-compilateurs (embedded SQL): ORACLE : PRO*C, PRO*ADA, PRO*COBOL • Langages adaptés : Access Basic (Visual Basic), PL/SQL (Ada+SQL)

  12. Différentes approches (suite) • API : ODBC (tous langages), JDBC (Java), SERVLETS (Java) • Langages de script - accès par internet (pages JSP (Java - SUN), ASP (MicroSoft), PHP, PERL, )

  13. PL/SQL

  14. PL/SQL (suite) Structure d’un bloc DECLARE -- déclarations de variables BEGIN -- instructions EXCEPTION -- traitements d'exception END

  15. PL/SQL (suite) Declare numdeptv Dept.numdept%type = 10; nbenr number(5); minimum number; valeur number;

  16. PL/SQL (suite) begin SELECT count(*) INTO nbenr FROM emp WHERE dept=numdeptv; if nbenr > 0 then UPDATE emp SET dept = null WHERE dept=numdeptv; end if; DELETE FROM dept WHERE numdept=numdeptv;

  17. PL/SQL (suite) SELECT min(salaire) INTO minimum FROM emp WHERE dept=5; if valeur < minimum then augmentationSalaire; else message(' valeur superieure a: '|| to_char(minimum)||'.'); end if; COMMIT;

  18. PL/SQL (suite) exception when no_data_found then INSERT INTO erreur_log VALUES(sysdate, numdeptv); when others then err_code:=sqlcod; err_txt := sqlerrm; INSERT INTO errlog2 VALUES(sysdate, err_code,err_txt); end;

  19. Déclarations PL/SQL • Types SQL • Référence au type d'un attribut de table : T.attribut%type

  20. Déclarations PL/SQL declare x varchar2(50); OK boolean; v constant number(8) default 500; t number(5) not null :=0; cursor c1 is select numEmp from emp where nom like '%x% order by nom; empRec emp%rowtype; buffer c1%rowtype; trop exception; pas_assez exception;

  21. Déclarations PL/SQL Création de types (limitée) type txt_tab_type is table of varchar2(80) index by binary_integer; txt_lines txt_tab_type; err_msg txt_tab_type; type emp_rec_type is record (nom varchar2(30), datenaiss date, num number(8)); emp_rec emp_rec_type;

  22. Instructions PL/SQL Affectation Entier := 10; sousChaine := substr(v5,1,10); condition := salaire > 2000; datenaiss := to_date('19-05-47', 'DD-MM-YYYY');

  23. Instructions PL/SQL while salaire < 2000 loop ... end loop; for i in reverse 1 .. 10 loop ... end loop; if nom is null or numemp=1000 then fetch c1 into emp_rec; else close c1; end if;

  24. Exceptions en PL/SQL • Nombreuses exceptions prédéfinies • Possibilité d'en définir de nouvelles declare trop exception; begin ... raise trop; ... exception when trop then ...

  25. Exceptions pré-définies • Program_error • Storage_error • No_data_founds • Logon_denied • Not_logged_on • Too_many_rows • Time_out_on_ressource

  26. Curseur Outil destiné à conserver la mémoire de l'exploration d'une table. for(i in 1..10) loop select nom, prenom from employe; end loop; Cette boucle retourne 10 fois les mêmes valeurs!

  27. Curseur (utilisation) Cursor c1 is select * from emp order by nom; c1_rec c1%rowtype; begin open c1; loop fetch c1 into c1_rec; exit when c1%notfound end loop; nb := c1%rowcount; close c1; end;

  28. Boucle « Pour_curseur » Cursor cur_for is select * from emp order by nom; for c1_rec in cur_for loop fetch cur_for into c1_rec; total := cur_for%rowcount; ... end loop;

  29. Procédures et fonctions PL/SQL create procedure p(...) is … create or replace procedure p(...) is … create or replace function f(...) return <type> is …

  30. Procédures et fonctions PL/SQL create or replace function f(num number, txt varchar2(10)) return number is total number; begin select count(*) into total from emp where emp.num=num; if total>20 then return total else return 0; end if; end;

  31. Paquetages • Spécification create or replace package test as function fgetval(num number) return number; end test; • Corps (implémentation) create or replace package body test as function fgetval(num number) return number is .... (code pl/sql) end; end test;

  32. Utilisation d’un paquetage Utiliser le nom complet : [proprietaire].[paquetage].procedure[(parametres)] Exemple : create synonym ouv for tp.bibli.lister; ouv('Moliere','seuil');

  33. Triggers Automatisation d'un traitement sur la prise en compte d'un évènement :

  34. Create or replace trigger commande after insert or update on commande_usine for each row begin if inserting then insert into commande@magasin values( :new.numcom, :new.numclient, :new.numarticle, :new.montant); elsif updating then update commande@magasin set numcom :=new numcom, numcli:=new.numcli, numarticle=new.numarticle, montant=new.montant where numcom=:old.numcom; end if; end;

More Related