Database Reference
In-Depth Information
Function
. Like a procedure but allows a return value.
Trigger
. No transactional termination commands allowed and exe-
cuted automatically by database event occurrences. Triggers are
known as event-driven procedures.
Package
. Groups multiple procedures and functions together into
blocked units.
24.2.2.1
Using Named Procedures
The following named procedure is a slightly more sophisticated copy of the
anonymous procedure presented previously. The procedure now has a
name, accepts a parameter, is stored in the database, and can be executed
repeatedly by executing the procedure name as shown in the following
script. The result is shown in Figure 24.2.
CREATE OR REPLACE PROCEDURE GETSONG (pARTIST IN VARCHAR2) AS
vARTIST_ID ARTIST.ARTIST_ID%TYPE;
vTITLE SONG.TITLE%TYPE;
BEGIN
SELECT ARTIST_ID INTO vARTIST_ID FROM ARTIST
WHERE NAME=pARTIST;
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 ON;
EXEC GETSONG('Sheryl Crow');
EXEC GETSONG('Avril Lavigne');
SET SERVEROUTPUT OFF;
24.2.2.2
Using Functions
Following are two versions of a function used previously in this topic. This
function will split a string time value of HH:SS into its hours and seconds
constituent parts and convert them to a real number.
CREATE OR REPLACE FUNCTION GETTIME(pTIME IN VARCHAR2)
RETURN NUMBER IS
--variable declaration section
