Database laboratory

Updates, Integrity, Transactions, Coherence, PL/SQL

	SQL> set long 1024

	SQL> describe USER_VIEWS

	SQL> select * from USER_VIEWS

You can recreate them using the informations provided in the  TP1.

1  Updates through views.

In the relational model, tables and views are generally handled in a similar manner: he SELECT clause can be used with views as in the case of true tables. However, in the case of INSERT, UPDATE and DELETE there are some differences. The following exercises are meant to reveal those differences.

• Create a table (named rbb) with the same schema as bons_buveurs containing  the tuples from bons_buveurs
using the following command:

	    CREATE TABLE RBB AS SELECT * FROM BONS_BUVEURS;

• Update the table buveurs : set the drinker's type to 'gros' if the total quantity of wine that he drunk is larger than 100.
• Compare the contents of rbb  and bons_buveurs. What do you notice? Give a short explanation.
Insert in the view bons_buveurs another drinker having your own name, the number max(nb)+1, the type "moyen". Is this drinker visible through the view?

• Insert in the view bons_buveurs another drinker with the type "petit". Is this drinker visible? Give a short explanation.
  
• Delete and then re-create the view bons_buveurs  by adding the clause WITH CHECK OPTION to the end of the command. Insert another drinker with the type "petit". Is now the drinker visible? Briefly explain what the clause WITH CHECK OPTION is doing.
  
• Re-create the view buveurs_achats by adding the clause WITH CHECK OPTION to the end of the definition. Insert a new drinkers in that view. Briefly explain Oracle's behavior.
  
• Insert into q83pv the tuple ('PARIS', 'Cotes du Jura', 300). Attempt to extract the tuples with DRANK_QTY>100. Briefly explain Oracle's behavior.

  2  PL/SQL and Procedures

• Test the effect of the following program:

DECLARE
    x  NUMBER := 1;
BEGIN
    WHILE x < 100 LOOP
          DELETE FROM ACHATS WHERE nv=x;
	  x := x + 2;        
    END LOOP;
END;
/

You can undo (cancel or rollback) the effects of the program with the command ROLLBACK;

• Test the effect of the next program and then rollback:

BEGIN
    FOR i IN 1..100 LOOP
	IF MOD(i,2) = 0 THEN     -- i est pair
	    UPDATE ACHATS SET QTE=QTE*2 WHERE nv=i;
	ELSE
	    UPDATE ACHATS SET QTE=QTE/2 WHERE nv=i;
	END IF;
    END LOOP;
END;
/

• Create the following table:

CREATE TABLE TEMP (Vin NUMBER(3), description VARCHAR2(30),
	           commentaire VARCHAR2(30));

• Test the following program and briefly explain its result

DECLARE
   cursor V_CUR is select * from vins
              where nv in (select nv from recoltes, producteurs
                           where region='Alsace' and producteurs.np=recoltes.np)
              order by nv;
   V_LIGNE VINS%ROWTYPE;
BEGIN
   open V_CUR;
   loop
       fetch V_CUR into V_Ligne;
       exit when V_CUR%NOTFOUND;
       IF V_Ligne.mill in (1976, 1978, 1983) THEN
	  INSERT INTO TEMP 
		VALUES (V_Ligne.nv, V_Ligne.cru || '(' || to_char(V_Ligne.mill) || ')', 'Excellent harvest !');
       ELSE
          INSERT INTO TEMP 
		VALUES (V_Ligne.nv, V_Ligne.cru || '(' || to_char(V_Ligne.mill) || ')', 'Nothing special !');
       END IF;
   end loop;
   close V_CUR;
END;
/

• Test and explain the following program.

DECLARE
   cursor V_CUR is select * from vins
              where nv in (select nv from recoltes, producteurs
                           where region='Alsace' and producteurs.np=recoltes.np)
              order by nv;
BEGIN
   for V_Ligne in V_CUR loop
       IF V_Ligne.mill in (1976, 1978, 1983) THEN
	  INSERT INTO TEMP 
		VALUES (V_Ligne.nv, V_Ligne.cru || '(' || to_char(V_Ligne.mill) || ')', 'Excellent harvest !');
       ELSE
          INSERT INTO TEMP 
		VALUES (V_Ligne.nv, V_Ligne.cru || '(' || to_char(V_Ligne.mill) || ')', 'Nothing special !');
       END IF;
   end loop;
END;
/

• Change the program above in order to transform it into a stored procedure:

CREATE OR REPLACE PROCEDURE choix_experts AS
--DECLARE
   cursor V_CUR is select * from vins
              where nv in (select nv from recoltes, producteurs
                           where region='Alsace' and producteurs.np=recoltes.np)
              order by nv;
BEGIN
   for V_Ligne in V_CUR loop
       IF V_Ligne.mill in (1976, 1978, 1983) THEN
	  INSERT INTO TEMP 
		VALUES (V_Ligne.nv, V_Ligne.cru || '(' || to_char(V_Ligne.mill) || ')', 'Excellent harvest !');
       ELSE
          INSERT INTO TEMP 
		VALUES (V_Ligne.nv, V_Ligne.cru || '(' || to_char(V_Ligne.mill) || ')', 'Nothing special !');
       END IF;
   end loop;
END;
/




• Call this procedure using the following command :

EXECUTE choix_experts
OU 
BEGIN
  choix_experts;
END;
/

• Test the following function :

CREATE OR REPLACE FUNCTION recherche_vins (r PRODUCTEURS.REGION%TYPE, annee VINS.MILL%TYPE) RETURN number AS
--DECLARE
--   r PRODUCTEURS.REGION%TYPE;
--   annee VINS.MILL%TYPE;
   cursor V_CUR is select * from vins
              where nv in (select nv from recoltes, producteurs
                           where region=r and producteurs.np=recoltes.np)
              order by nv;
   cpt number:=0;

BEGIN
   for V_Ligne in V_CUR loop
       IF V_Ligne.mill = annee THEN
	  INSERT INTO TEMP 
		VALUES (V_Ligne.nv, V_Ligne.cru || '(' || to_char(V_Ligne.mill) || ')', 'Excellent harvest !');
          cpt:=cpt+1;
       END IF;
   end loop;
   RETURN cpt;
END;
/

• How to call fuctions and to enable the messages to the users:

SET SERVEROUTPUT ON
EXECUTE dbms_output.enable(1000000);

DECLARE 
  x number;
BEGIN
  x:=recherche_vins('Alsace',1983);
  dbms_output.new_line;
  dbms_output.put_line(x || ' selected wines ');
END;
/

3: Exercises:

• Write a stored procedure achat which allows to insert a buying order in the data base. The initial values will be passed as arguments to the procedure.
• Write a stored procedure VSTAT that allows to determine, for a given  wine ,  the different production regions, the number of wine makers and a top of the cities (places) of consumption (by the number of  sold bottles), grouped by year.