Database Reference
In-Depth Information
24.2.1
Blocks and Exception Trapping
A block of code is a group of lines of SQL or PL/SQL code enclosed
between BEGIN and END statements. A block of code is parsed and exe-
cuted after the END statement is submitted using the front slash (/) charac-
ter. The following SQL block consists of a variable declaration section
followed by a BEGIN to END code block. See the result in Figure 24.1.
This block of code queries the ARTIST table for the ARTIST_ID of Sheryl
Crow. It stores the ARTIST_ID in a variable and then uses the variable to
find the title of the first song of Sheryl Crow in the SONG table. It stores
the title in another variable. Then it displays the title and completes.
SET SERVEROUTPUT ON;
DECLARE
vARTIST_ID ARTIST.ARTIST_ID%TYPE;
vTITLE SONG.TITLE%TYPE;
BEGIN
SELECT ARTIST_ID INTO vARTIST_ID FROM ARTIST
WHERE NAME='Sheryl Crow';
SELECT TITLE INTO vTITLE FROM SONG
WHERE ARTIST_ID = vARTIST_ID AND ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE(vTITLE);
EXCEPTION WHEN OTHERS THEN
RAISE;
END;
/
SET SERVEROUTPUT OFF;
Note:
The statement SET SERVEROUTPUT ON is essential for the
proper functioning of the DBMS_OUTPUT.PUT_LINE packaged proce-
dure. DBMS_OUTPUT is an Oracle-provided package. The PUT_LINE
procedure within that package sends a line to the output. SET SERVER-
OUTPUT OFF switches output off.
Note:
%TYPE sets a variable to the datatype of the specified
TABLE.COLUMN.
