The simplest form of program has some declarations followed
by an executable section consisting of one or more of the SQL statements with
which one are familiar. The form of the SELECT statement is different from its
SQL form. After the SELECT clause, one 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.
The SELECT statement in PL/SQL only works if the result of
the query contains a single tuple. If the query returns more than one tuple,
one needs to use a cursor.
For example,
CREATE TABLE Ti (
e INTEGER,
f INTEGER );
DELETE FROM T1;
INSERT INTO T1 VALUES(1, 3);
INSERT INTO TI VALUES(?. 4);
/*Above is plain SQL; below is the PL/SQL program. */
DECLARE
a NUMBER:
b NUMBER;
BEGIN
SELECT e. f INTO a. b FROM T1
WHERE e>1 ;
INSERT INTO T1 VALUES(b. a);
END:
run:
There is only one tuple of T I that has first component
greater than I, ine4 (2,4). The INSERT
statement thu.. inserts (4,2) into T1.