# Cours bd

### Partie SQL simple
#### Question entre sequance, function/procedure(methodes) et triggers.

function p(val):
    val+=1;
    dbms_system.out_put(val);
    return val;

Create table tname
(nom varchar2(10) default ‘peter’, id int default 10);
Insert into tname (id, col2) values (s_name.nextval, ‘val2’);
Insert into tname values(vals_for_all_cols);
Select col from tname 
where col2 not in (select col from tname) 
order by col DESC;
Update tnane set col = col-10 where col1 = ‘val2’;
Delete from tname where col = ‘10’;
Drop table tname cascade constraints;
Drop sequence s_name; 
-create sequence s_name start with 10;
s_name.nextval;
s_name.currval;
Create table tname (
col integer, constraint pk_t primary key (col), 
b integer not null, 
c integer, 
constraint unique_t unique (b,c), 
constraint check_t check (a+b>4), 
constraint check_etat check (cond1 or/and cond2), 
foreign key (c) references tname(col));

### Partie pseudo sql constraints

Tab name:
    (col1, col2) unique 
    age not null check 0<age<99 
    check etat in (neuf, bon, vieux)   
    col1 pk, 
    col1 fk tname(col2) not null;

Contraintes non sql: Exactement une ligne dans tname, etc...

-- Cleanup
drop table eclient;
drop table eTypeVoiture;
drop table eOffre;
drop table eDemande;
drop table eCovoiturage;

create table eclient(
                        idc int primary key,
                        nom varchar2(255),
                        telephone varchar2(100)
);

create table eTypeVoiture(
                             idt int primary key,
                             marque varchar2(100),
                             nbSieges int
);
create table eOffre(
                       ido int primary key,
                       idc int,
                       idt int,
                       destination varchar2(100)
);
create table eDemande(
                         idd int primary key,
                         idc int,
                         destination varchar2(100),
                         marque varchar2(100),
                         statut varchar2(100)
);

create table eCovoiturage(
                             idco int primary key,
                             ido int,
                             idc int
);
-- insertions
insert into eclient values(1, 'nom cl1', 'telcl1');
insert into eclient values(2, 'nom cl2', 'telcl2');
insert into eclient values(3, 'nom cl3', 'telcl3');

insert into eDemande values (1, 1, 'DestCl1', 'MarqueCl1', 'ignores');
insert into eDemande values (2, 2, 'DestCl2', 'MarqueCl2', 'ignores');
insert into eDemande values (3, 3, 'DestCl3', 'MarqueCl3', 'ignores');

### Partie PL/sql

Set serveroutput on;
Show errors;
exec nomDeTaProcedure; 
-- dbms_output.put_line(val1||' chaine '||val2); 
-- dbms_output.put_line(fname(params)); 
Declare
Var1 eclient.idc%type; -- je souhaut que mon var1 soit de meme type que nomDeCoumn de la table tname
Var2 eclient.nom%type; 
Var3 eclient.telephone%type;
-- select * into var1, var2, var3 from eclient; 
-- ex retour de select 1, 'Pierre', '0321'
-- var1 aurais 1, var2 aurais 'Pierre', var3 aurais '0312'
-- var1 + 10;
Var4 tname%rowtype; -- Je souhaut que mon Var4 deviens un objet qui contient tout les columns de tname
-- ex : Var4 eCovoiturage%rowtype; -- 
-- Select * into var4 from eCovoiturage where idco = 1;
--  ex retour de select 1, 2, 3
-- pour acceder au result il faut utiliser 
-- var4.idco aurais 1
-- var4.
-- 
Var5 varchar2(10); -- varchar2(100)
Var6 int;
Begin

dbms_output.put_line(‘’||’’);
procedureNom(1,2,3,'toto');

End;
/

### En sql " " -> existe pas, pour des chaines de caracteres Strings on utilise que ' '

### Partie PL/sql avancé

create or replace procedure myProcedure is...
create or replace procedure myProcedure(
param1 int, param2 out varchar2) as...
-- 2 types de params
-- 'in' et 'out'
-- in => copie disparesse avec fin d'execution de procedure
-- out => original reviens avec fin d'execution de function
create or replace function myFunction return varchar2 as...
create or replace function myFunction(param1 int, param2 out eclient.idc%type) return varchar2 is...
-- dbms_output.put_line(param2); // retourne ce qu'est dedans de param2 apres execution de function

Create or replace procedure/function
p/fname(
Param1 in tname.col%type, p2 out integer)
return sys_refcursor is/as
Cursor nomCursor is
Select col, col1 from tname;
Cursor c1 is select * from tname2;
c2 sys_refcursor;
Var1 tname.col%type
Var2 tname.col1%type
Var3 tname2%rowtype

Begin
Open c2 for select * from where p < p2;
// open cursor passed in params ? (no)
Open nomCursor;
Fetch nomCursor into var1, var2;

// todo donner une exo sur fetch
| col | Column 2 | Column 3 | <- open nomCursor;
| -------- | -------- | -------- |
| 1 | Text | Text | <- fetch nomCursor into var1, var2,var3 alors var1 aurais 1, var2 aurais Text...
if nomCursor%found then fetch...
| 2 | Text | Text | <- fetch nomCursor into [..]
| 3 | Text | Text | <- fetch [...]
| | |

If nomCursor%found then -- Est ce que notre cursor point sur des donnees ?
-- traitement si trouvé
fetch nomCursor into ...
Var1 := s_name.nextval;
Else
-- traitement si non
End if;

open nomCursor;
fetch ...
_While_ nomCursor%found _loop_
Fetch nomCursor into var1, var2; -- <- oublie pas fetch car sinon tu vas boucler sur meme valeur a l'infini
-- traitement
_End loop;_ -- while(){}


Loop -- do {} while();
Fetch nomCursor into var1, var2;
var1 -- aurai-t-il valeur dedans ? pas sur
Exit when nomCursor%notfound;
-- Traitement
End loop;

Close c;

Select count(*) into p2 where...
select * from p; -- <- où p c'est une variable nom de la table ca passe pas.
-- p varchar2 contient 'tname3';
sqlText := 'select * from '|| p; -- <- cancatenation de caracters dans une string p une varchar2 passé en params et il contient nom de la table
execute immediate sqlText; -- <- equivalent au 
-- select * from tname3;
-- on souhait executer une function et recuperer son resultat et 1 parametre 
texte := 'begin :1 := '||f||'(:2, :3); end;';
execute immediate texte using out var1, out var2, var3;
-- ic = 1 , v = 'test', j = 'roro'
-- c##conn_a.traitement2(1, 'test', 'roro', :2,:3,:4)
texte :='begin :1 := c##'||p2||'.traitement2('||ic||','''||v||''','''||j||''',:2,:3,:4); end;';
-- sort begin :1 := c##nivaniu_a.traitement2(...)
-- j'ira chez compte de nivaniu_a et je executera son function traitement2.
dbms...('m''appelle') => mappelle
execute immediate texte using out var1, out var2, var3;
-- c3 sys_refcursor;
open c3 for sqlText; -- select * from tname3;
Return c3;
End
/

Jonathan PL/SQL prog

create or replace procedure etraitement(funcName varchar2) as
cursor c is (select * from edemande where statut = 'ignores');
rowDemande edemande%rowtype;
sqltxt varchar2(200);
telRet varchar2(100);
begin
open c;
-- We need to fetch at least 1 time before looking into %found %notfound
-- Another loop example :
-- while c%found loop
-- dbms_output.put_line('Demande found '||rowDemande.idd);
-- fetch c into rowDemande;
-- end loop;
loop
fetch c into rowDemande;
exit when c%notfound;
dbms_output.put_line('Demande found '||rowDemande.idd);
sqltxt := 'begin :1 := '||funcName||'('||rowDemande.idc||'); end;';
-- FunctionName(var out int) return varchar2 
-- sqltxt := 'Begin :1 := functionName(:2); end;'
-- 
-- 
trucDeclareMaisVide int;
execute immediate sqltxt using out telRet, out trucDeclareMaisVide;

dbms_output.put_line('Result '||telRet ||' '||trucDeclareMaisVide);
end loop;
close c;

end;
/
declare
-- tel varchar2(100);
begin
dbms_output.put_line('Executing traitement');
etraitement('etraitement3');
-- tel := etraitement3(1);
-- dbms_output.put_line(tel|| ' returned');
end;
/
devoir prends plsql des annales de bd et fais les toi meme
et envoi moi pour les verifier

### Java

import java.sql.*;
Class cname {
// Integer.parseInt(str);
static String input(String msg) throws InvalidInputException{
// try {
    int zero = 0;
    int var = ()->{
    if (True) throw new Exception();
    return 2;
    };
// } 
// catch (Exception e){ return “error”;}
}
Public static void main(String[] args) throws SQLException, ClassNotFoundException {
    try{
    Class.forName(“oracle.jdbc.driver.OracleDriver”);

    Connection c = DriverManager.getConnection(
    // utilisateur baba
    // machine betty
    // port 3333
    // nom de base mybase
    “jdbc:oracle:thin:c##baba/baba@betty:3333:mybase”
    );
    
    Statement s = c.createStatement(); // avec Statement on peut executer SQL
    String sql = “update/insert/delete ...”;
    
    int updatedLines = s.executeUpdate(texte); // return integer et peut executer uniquement des updates de BD, AKA Insertion, deletion, update lui meme.
    // .executeUpdate PEUT PAS faire (select * ...)
String sql = “select seq_sejour.currval from dual”;
ResultSet r = s.executeQuery(sql); // return ResultSet.class
// select col1, col2, var3 from tname;
if(r.next()) {
// "col1" <- juste une chaine de caracteres 
// col1 <- va chercher variable avec pour nom col1
int res = r.getInt(3);
String str = r.getString(2);
int res2 = r.getInt(1);
}
r.close();
//     CallableStatement sert à executer les functions et procedures
    CallableStatement cs = c.prepareCall("{? = call traitement3(?,?,?,?)}");
    cs.registerOutParameter(1, Types.INTEGER); // ecoute
    // le param 1 doit revenir et ca doit etre un integer.
// store le qqpart, je apres viendrais le chercher
    cs.registerOutParameter(2, Types.VARCHAR);
    cs.setString(3, ‘val’);
//    cursor sys_refcursor;
//    creer une function qui prends 2 parametres chaines de caracteres
//    et revoi leur concatenation. 
// p1 varchar2;
// ...testFunc(p1...)
// create or replace function testFUnct([...],[...],cur out sys_refcursor) return varchar2 as
//
// cs.registerOutParameter(3, OracleTypes.CURSOR);
// ResultSet r = (ResultSet) cs.getObject(3);
    // cs.execute();
    // int nb = cs.getInt(1);
    // problematique
    // fo inserer 10000 valeur dans bd
    // fo t il faire .Statement dans une boucle 10 000 fois ?
    // pq il faut pas le faire ?
    // s.executeUpdate(SQLVariable) // on va le compiler pour verifier s'il est correct, si table et cols existe.
    // si y a besoin d'executer meme sql plus d'une fois,
    // fo passer par prepareStatement
    // si il faut l'executer une SEULE fois, on UTILISE pas prepareStatement
    // sinon tu as 0
    PreparedStatement p = c.prepareStatement(SQL Simple où que les parametres changent);
    for (int i = 0; i < 10000; i++) {
        p.setInt(2, ‘val’+i); //throws SQLException;
        p.setString(3, ‘val’+i); //throws SQLException;
        p.setString(4, ‘val’+i); //throws SQLException;
        p.executeUpdate();
        // p.executeQuery();
    }
//p.executeQuery();//(ResultSet)
    p.close();
    cs.close();
    s.close();
    c.close();
    }
    catch(SQLException e){
        return ...;
    }
    catch(ClassNotFoundException nImporteQuelleNom){
        return "error";
    }
    }
}
Devoir Faire exo de Java celle d\'exam

Partie PHP

<?php
$vat = $_REQUEST[‘paramName’];

$c = ocilogon(‘c##nivaniu_a’, ‘nivaniu_a’, ‘dbinfo’);
if(!$c) {return;}
$sql = “select col from tname where col2 = ”.$vat;

$sqlDual = “select sName.nextVal from dual”;

$sqlAll = “select * from tname”;

$ordreAll = ociparse($c, $sqlAll); // present toujours

ociexecute($ordreAll); // execution de ta commande

if(ocifetchinto($ordreAll, $line))//
 echo $line; // contiendra tout les champs
$line[0], $line[1] // recuperation par ids et "Ids"

$sqlproced = “begin pname('“.$vat.”'); end;”;
// envoyer params out avec function  f

$sqlproced2 = “begin :1 := f('“.$vat.”', '“.$vat.”'); end;”; // mets ici en params valeur de $vat 

$ordre2 = ociparse($c, $sqlproced2); 

// dans java il fallu indiquer numero de valeur que tu bind
// en pl, tu fais using out/in tu liste en ordre :1, :2 etc
// en php c'est different, tu peux mettre n'importe quoi et le bind
ocibindbyname($ordre2, ‘:1’, $var, bufferSize); // envoi rien
// bufferSize est une param optionnel et c'est une integer
// resultat qui doit etre normalement en :1 se trouvera dans variable $var;
A           ->                  B // notre train a que 5 places donc il peut deplacer uniquement au max 5 personnes
- - - - - 
_________>
ociexecute($ordre2);


while(ocifetchinto($ordre2, $res)){
    var_dump($res);
}
// execute function fname a partir de php
// fname etais declare comme suite
// create or replace function fname(stuff in varchar2, stuffout out varchar2, stuffout2 out int) returns varchar2 as begin stuffout := 'stuffout1'; stuffout2 := 'stuffout2'; return stuff || ' it'; end;/
/// playground
$sqlproced3 = “begin :1 := fname('“.$stuff.”', :2, :3); end;”;
// $sqlproced3 contient : avec $stuff = 'toto'
// fname(stuff in varchar2, stuffout out varchar2, stuffout2 out int)
// begin var := fname('toto'); end;
$ordre3 = ociparse($c, $sqlproced3); 
ocibindbyname($ordre3, ‘:1’, $var1);
ocibindbyname($ordre3, ‘:2’, $var2);
ocibindbyname($ordre3, ‘:3’, $var3);

ociexecute($ordre3);

/// end playground
$vat // contient resultat de $ordre2
//if(ocifetchinto($ordre, $line))

$ordre = ociparse($c, $sql);
ociexecute($ordre);
while(ocifetchinto($ordre, $ligne)) 
ocilogoff($c);
?>

Devoir
1) PLSQL devoir prends plsql des annales de bd et fais les toi meme
et envoi moi pour les verifier // ici tu fais sur oracle // 10 points
2) JAVA Devoir Faire exo de Java celle d\'exam // ici tu fais sur machine en executant // 10 points
3) PHP Devoir Faire exo de Java celle d\'exam // ici tu fais sur un server php // 20 points
Commence maitenant:

import java.sql.*;
Class Examen{
    public void exercice(String u, Connection c) throws SQLException{
        Statement s = c.createStatement();
        String sql = "select c.idc from client c, demande d where c.idc = d.idc and d.statut = 'ignoré'";
        ResultSet r = s.executeQuery(sql);
        String res = "2";
        while(r.next()){
            CallableStatement cs = c.prepareCall("{? = call traitement3(?)}")
            cs.registerOutParameter(1, Types.VARCHAR);
            cs.setInt(2, r.getInt(1));
            cs.execute();
        }
        cs.close();
        r.close();
        s.close();
        c.close();
    }
}