-- corr_pl_sqldyn.sql
-- corrige partiel
-------------------------------------------------------------------------------
-- Jesus Camacho, 27 septembre 2012

-- 12. SQL dynamique
-- 12.1. Familiarisation.
-- 12.2.
-- Table loisir...
DROP TABLE loisir;
CREATE TABLE loisir(nom varchar2(10), age integer, activite varchar2(10));
INSERT INTO loisir VALUES('Riton', 22, 'SGBD');
INSERT INTO loisir VALUES('Rita', 21, 'tango');
INSERT INTO loisir VALUES('Jeanne', 20, 'sieste');
SELECT * FROM loisir;

-- Procedure pour detruire les lignes avec valeur superior ou egal a 22...
CREATE OR REPLACE PROCEDURE deletesupoueg22 (
	nomtable IN VARCHAR2,
	nomcolonne IN VARCHAR2)
AS
	BEGIN
		EXECUTE IMMEDIATE 'delete ' || nomtable || ' where ' || nomcolonne || '>=22';
	END;
/

-- Test procedure
EXECUTE deletesupoueg22('loisir','age');
SELECT * FROM loisir;

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

-- 5 octobre 2012
-- 14. SQL dynamique (2/2)
-- 14.1. 
CREATE OR REPLACE PROCEDURE duplique (
	nomtable IN VARCHAR2) AUTHID CURRENT_USER
AS
	requete VARCHAR2(100);
	BEGIN
		requete := 'CREATE TABLE ' || nomtable || '2 AS SELECT * FROM ' || nomtable;
		-- 'CREATE TABLE client2 AS SELECT * FROM client'
		EXECUTE IMMEDIATE requete;
	END;
/
-- On n'a pas permis pour creer la table depuis la procedure, donc on utilise AUTHID CURRENT_USER.
-- AUTHID CURRENT_USER: Permet d'executer avec les droits de celui qui execute le code (definer rights) par opposition au mode par defaut ou le code PL/SQL s'execute avec les droits celui qui a compile le code (invoker rights).

EXECUTE duplique('client');
SELECT * FROM client2;

-- 14.2.
DROP TABLE tabledeuxcolonnes;
CREATE TABLE tabledeuxcolonnes(col1 INT, col2 varchar2(10));
INSERT INTO tabledeuxcolonnes VALUES(22, 'SGBD');
INSERT INTO tabledeuxcolonnes VALUES(21, 'tango');
INSERT INTO tabledeuxcolonnes VALUES(20, 'sieste');
SELECT * FROM tabledeuxcolonnes;

CREATE OR REPLACE PROCEDURE affiche_gen (
	nomtable IN VARCHAR2)
AS
	TYPE deux_colonnes_type IS
	 	RECORD (x INT, y varchar2(10));
	ligne DEUX_COLONNES_TYPE;
	c SYS_REFCURSOR;
	BEGIN
		OPEN c FOR 
	        SELECT *
			FROM tabledeuxcolonnes;
		LOOP
			FETCH c INTO ligne;
			EXIT WHEN c%NOTFOUND;
			dbms_output.put_line('Integer: '|| ligne.x || ' ... Varchar2: ' || ligne.y);
		END LOOP;
	END;
/


CREATE OR REPLACE PROCEDURE affiche_gen (
	nomtable IN VARCHAR2)
AS
	requete VARCHAR2(100);
	x INT;
	y VARCHAR2(10);
	c SYS_REFCURSOR;
	BEGIN
		OPEN c FOR 'SELECT * FROM ' || nomtable;
		--requete := 'BEGIN OPEN :1 FOR SELECT * FROM ' || nomtable || '; END;';
		--EXECUTE IMMEDIATE requete using IN c;
		LOOP
			FETCH c INTO x,y;
			EXIT WHEN c%NOTFOUND;
			dbms_output.put_line('Integer: '|| x || ' ... Varchar2: ' || y);
		END LOOP;
	END;
/

execute affiche_gen('tabledeuxcolonnes');


-- 14.3. 
--  /Creer un sejour (traitement 2)
CREATE OR REPLACE PROCEDURE traitement2 (
	c IN client.idc%type, d IN village.destination%type, j IN sejour.jour%type,
	v OUT village.idv%type, s OUT sejour.ids%type, a OUT village.activite%type)
IS
	x village.prix%type;
	BEGIN
		SELECT MAX(prix) INTO x FROM village
			WHERE destination = d;
		SELECT idv, activite INTO v, a FROM village
			WHERE prix = x AND destination=d AND rownum = 1;
		UPDATE client SET avoir = avoir - x WHERE idc = c;
		s := seq_sejour.nextval;
		INSERT INTO sejour VALUES (s, c, v, j);
	END;
/

-- Procedure traitement2_gen
CREATE OR REPLACE PROCEDURE traitement2_gen (
	l IN VARCHAR2, c IN client.idc%type, d IN village.destination%type, j IN sejour.jour%type,
	v OUT village.idv%type, s OUT sejour.ids%type, a OUT village.activite%type)
IS
	requete VARCHAR2(100);
	BEGIN
		requete := 'BEGIN ' || l || '.traitement2(:1,:2,:3,:4,:5,:6); END;';
		dbms_output.put_line(requete);
		EXECUTE IMMEDIATE requete using IN c, IN d, IN j, OUT v, OUT s, OUT a;
	END;
/

-- Appel a traitement2_gen
DECLARE 
	v village.idv%type;
	s sejour.ids%type;
	a village.activite%type;
BEGIN
	traitement2_gen('waller_a', 2, 'NY', 45, v, s, a);
	dbms_output.put_line('Identifiant village: ' || v);
	dbms_output.put_line('Identifiant sejour: ' || s);
	dbms_output.put_line('Activite: ' || a);
END;
/


-- 14.4.
--	/S'inscrire (traitement 1)
CREATE SEQUENCE seq_client START WITH 10;

CREATE OR REPLACE FUNCTION traitement1 (
	nom_p IN client.nom%type,
	age_p IN client.age%type)
RETURN client.idc%type
IS
	idc_p client.idc%type;
	BEGIN
		idc_p := seq_client.nextval;
		INSERT INTO client(idc,nom,age) VALUES (idc_p, nom_p, age_p);
		RETURN idc_p;
	END;
/

-- Procedure traitement1_gen
CREATE OR REPLACE PROCEDURE traitement1_gen (
	l IN VARCHAR2, nom_p IN client.nom%type, age_p IN client.age%type,
	idc_p OUT client.idc%type)
IS
	requete VARCHAR2(100);
	BEGIN
		requete := 'BEGIN :1 := ' || l || '.traitement1(:2,:3); END;';
		dbms_output.put_line(requete);
		EXECUTE IMMEDIATE requete using OUT idc_p, IN nom_p, IN age_p;
	END;
/

-- Appel a traitement1_gen
DECLARE 
	idc client.idc%type;
BEGIN
	traitement1_gen('waller_a', 'John', 25, idc);
	dbms_output.put_line('Identifiant nouveau client: ' || idc);
END;
/

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