Database Reference
In-Depth Information
Note:
Note in the previous PL/SQL code how variables are accessed as vari-
able := value; This is PL/SQL syntax.
Here is a single-line version of the same function showing how best to
write properly performing PL/SQL code:
CREATE OR REPLACE FUNCTION GETTIME(pTIME IN VARCHAR2)
RETURN NUMBER IS
BEGIN
RETURN TO_NUMBER(SUBSTR(pTIME,1,INSTR(pTIME,':')-
1))+(TO_NUMBER(SUBSTR(pTIME,INSTR(pTIME,':')+1,LENGTH(pTIME)-
INSTR(pTIME,':')))/60);
EXCEPTION WHEN OTHERS THEN RETURN 0;
END;
/
I can execute the GETTIME function on the SONG table
PLAYING_TIME column (SONG.PLAYING_TIME) using the following
script. The result is shown in Figure 24.3.
SELECT PLAYING_TIME, GETTIME(PLAYING_TIME) FROM SONG
WHERE PLAYING_TIME IS NOT NULL;
Note:
The GETTIME function is also known as a custom-written or user-
defined function.
24.2.2.3
Using Triggers
Here are some simple example triggers. The first trigger detects insertions to
the ARTIST table, the second updates, and the third deletions. Figure 24.4
shows the response from an INSERT, an UPDATE, and a DELETE com-
mand, one after the other.
CREATE OR REPLACE TRIGGER iARTIST
AFTER INSERT ON ARTIST FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('New Artist '||:NEW.NAME||'
added.');
