Simple Programs in PL/SQL

The simplest form of program has some declarations followed by an executable section consisting of one or more of the SQL statements. The major nuance is that the form of the SELECT statement is different from its SQL form. After the SELECT clause, we must have an INTO clause listing variables, one for each attribute in the SELECT clause, into which the components of the retrieved tuple must be placed. Notice “tuple” rather than “tuples” is mentioned since the SELECT statement in PL/SQL only works only if the result of the query contains a single tuple. If the query returns more than one tuple, a cursor needs to be used. 

 SQL> CREATE TABLE  T1 (
   2    e  INTEGER,
   3    f  INTEGER );
 Table created.

 SQL> DELETE FROM  T1;
 0 rows deleted.

 SQL> INSERT INTO  T1  VALUES(1, 3);
 1 row created.

 SQL> INSERT INTO  T1  VALUES(2, 4);
 1 row created.

 /* Above is plain SQL; below is a PL/SQL program. */

 SQL> DECLARE
   2    a  NUMBER;
   3    b  NUMBER;
   4  BEGIN
   5    SELECT  e, f  INTO  a, b  FROM  T1  WHERE  e > 1;
   6    INSERT INTO  T1  VALUES( b, a );
   7  END;
   8  /
 PL/SQL procedure successfully completed.




Fortuitously, there is only one tuple of T1 that has first component greater than 1, namely (2, 4). The INSERT statement thus inserts (4, 2) into T1.




      When I was a kid, I used to pray every night for a new bike.    
      Then I realized that God doesn’t work that way.    
      So I stole a bike and asked Him to forgive me.    
      - Emo Phillips