Exercice

Quelles instructions SQL renvoient la même valeur que la fonction PL/SQL sous Oracle suivante :

num. | Ligne | texte | copie |
CTRL+C pour copier, CTRL+V pour coller
1
CREATE FUNCTION fTruc RETURN number
2
IS
3
 CURSOR c IS SELECT a FROM t;
4
 x number;
5
 y number;
6
BEGIN
7
 y:=0;
8
 OPEN c;
9
 LOOP
10
  FETCH c INTO x;
11
  EXIT WHEN c%NOTFOUND;
12
  y:=y+x;
13
 END LOOP;
14
 RETURN y;
15
END;
CREATE FUNCTION fTruc RETURN number
IS
 CURSOR c IS SELECT a FROM t;
 x number;
 y number;
BEGIN 
 y:=0;
 OPEN c;
 LOOP
  FETCH c INTO x;
  EXIT WHEN c%NOTFOUND;	
  y:=y+x;		
 END LOOP;
 RETURN y;
END;

  • SELECT a FROM t;

  • SELECT count(a) FROM t;

  • SELECT avg(a) FROM t;

CorrectionRecommencer