Pl/sql - Oracle's Procedural Language Extension To Sql

PL/SQL

ISAD211

1

PL/SQL - Oracle's Procedural Language extension to SQL 4 4

loosely based on Ada includes (inter alia) 4 4 4 4 4

block structure datatypes (including Boolean) variables & constants assignment statement conditional statements if .. then 4 if .. then .. else 4 if .. then .. elsif 4

4

loops basic loop (needs exit condition) 4 for loop 4 while loop 4

ISAD211

4

allows most SQL commands to be embedded

2

PL/SQL blocks 4 anonymous

blocks

4 procedures 4 functions

ISAD211

3

Anonymous block examples BEGIN  DBMS_OUTPUT.PUT_LINE('Hello World!'); END; 



DECLARE  v_day_of_week VARCHAR2(12); -- local variable BEGIN  SELECT TO_CHAR(SYSDATE, 'Day')  INTO v_day_of_week  FROM sys.dual;  IF v_day_of_week IN ('Saturday', 'Sunday')  THEN  -- note use of extra quote in string  DBMS_OUTPUT.PUT_LINE('It''s the weekend!');  ELSE  DBMS_OUTPUT.PUT_LINE('Too bad - it''ll be the weekend soon');  END IF; END; SET SERVEROUTPUT ON 

ISAD211

to get output from DBMS_OUTPUT.PUT_LINE

4

Stored programs 4 Code

stored in the database and executed on the server 4 advantages

of DB approach

4 store

once, use many 4 backup/recovery 4 access control

4 Multiple

use by multiple users/applications



ISAD211

GRANT EXECUTE ON <proc_name> TO <username>

5

Stored procedure CREATE OR REPLACE PROCEDURE TGITW IS  v_day_of_week VARCHAR2(12); -- DECLARE not used here BEGIN  SELECT TO_CHAR(SYSDATE, 'Day')  INTO v_day_of_week  FROM sys.dual;  IF v_day_of_week IN ('Saturday', 'Sunday') THEN  DBMS_OUTPUT.PUT_LINE('It''s the weekend!');  ELSE  DBMS_OUTPUT.PUT_LINE('Too bad - it''ll be the weekend soon');  END IF; END; 

  

-- SQL*Plus exec TGITW



-- SQL*Plus and SQL Developer BEGIN  TGITW; END;  

ISAD211

6

Stored function CREATE OR REPLACE FUNCTION get_age (p_birth_date IN DATE) -- INput parameter  RETURN INTEGER IS -- data type of result  v_age INTEGER; BEGIN  SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,  p_birth_date)/12)  INTO v_age  FROM sys.dual;  RETURN(v_age); -- result of function END; 

 

GRANT EXECUTE ON get_age TO PUBLIC;



SELECT sname, bdate, brian.get_age(bdate) age FROM rearp.student ORDER BY age DESC, bdate ASC; 

ISAD211

7

Also available … SELECT brian.valid_postcode('PL4 8AA') FROM sys.dual; 



TRUE

 

SELECT brian.valid_postcode('PL0 8AA') FROM sys.dual; 



FALSE



ISAD211

8

Cursors 4 SQL

query delivers an indivisible SET of rows 4 Cursor allows row by row processing of the result set of a query 4 Think of a cursor as a local table of results 4 Read

one record or row at a time 4 Do something with it 4

ISAD211

9

Cursor operations 4 DECLARE 4 specify

query to populate cursor

4 OPEN 4 run

query to populate cursor

4 FETCH 4 get

a 'row' from the cursor

4 CLOSE

ISAD211

10

DECLARE title_cur cursor CURSOR title_cur IS  SELECT title  FROM books; 

4

ISAD211

11

OPEN title_cur; Serpent's Reach Faded Sun, Kesrith, The Faded Sun, Shon'jir, The … Titans, The Black Tulip, The

ISAD211

12

FETCH title_cur INTO v_title; Serpent's Reach Faded Sun, Kesrith, The Faded Sun, Shon'jir, The …

v_title

Serpent's Reach

Titans, The Black Tulip, The

ISAD211

13

FETCH title_cur INTO v_title; Serpent's Reach Faded Sun, Kesrith, The Faded Sun, Shon'jir, The …

v_title

Faded Sun, Kesrith, The

Titans, The Black Tulip, The

ISAD211

14

FETCH title_cur INTO v_title; Serpent's Reach Faded Sun, Kesrith, The Faded Sun, Shon'jir, The …

v_title

Faded Sun, Shon'jir, The

Titans, The Black Tulip, The

ISAD211

15

FETCH title_cur INTO v_title; Serpent's Reach Faded Sun, Kesrith, The Faded Sun, Shon'jir, The …

v_title

Titans, The

Titans, The Black Tulip, The

ISAD211

16

FETCH title_cur INTO v_title; Serpent's Reach Faded Sun, Kesrith, The Faded Sun, Shon'jir, The …

v_title

Black Tulip, The

Titans, The Black Tulip, The

ISAD211

17

EXIT WHEN title_cur %NOTFOUND; Serpent's Reach Faded Sun, Kesrith, The Faded Sun, Shon'jir, The …

v_title

Titans, The Black Tulip, The No rows left in cursor

ISAD211

18

Cursor example DECLARE v_new_title VARCHAR2(60);  v_title VARCHAR2(60);  v_length INTEGER;  v_char CHAR(1);  CURSOR c_title IS  SELECT title  FROM books; BEGIN  OPEN c_title;  LOOP  FETCH c_title INTO v_title;  EXIT WHEN c_title%NOTFOUND;  v_length := LENGTH(v_title);  v_new_title := '';  FOR i IN 1..v_length LOOP  v_char := SUBSTR(v_title, i, 1);  IF UPPER(v_char) IN ('A', 'E', 'I', 'O', 'U')  THEN  v_char := '*';  END IF;  v_new_title := v_new_title||v_char;  END LOOP;  DBMS_OUTPUT.PUT_LINE(v_new_title);  END LOOP;  CLOSE c_title; END;  

ISAD211

19

Listing stored code in SQL Developer

ISAD211

20

PL/SQL exception handling 4 Enables

‘bulletproofing’ of code to handle run-time errors and retain processing control

ISAD211

21

Example

(modified from Oracle documentation) DECLARE  v_pe_ratio NUMBER(3,1); BEGIN  SELECT price / earnings -- potential division-by-zero error  INTO v_pe_ratio  FROM stocks  WHERE symbol = 'XYZ';  INSERT INTO stats (symbol, ratio)  VALUES ('XYZ', v_pe_ratio);  COMMIT;  EXCEPTION -- exception handlers begin  WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error  INSERT INTO stats (symbol, ratio) VALUES ('XYZ', NULL);  COMMIT;  WHEN OTHERS THEN -- handles all other errors  ROLLBACK; END; -- exception handlers and block end here 

ISAD211

22

Advantages 4 Need

to code multiple checks removed 4 A single exception handler will process all errors of a given type

ISAD211

23

Some common pre-defined PL/SQL exceptions 4

INVALID_NUMBER 4

4

NO_DATA_FOUND 4

4

An arithmetic, conversion, truncation, or sizeconstraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable (ORA06502)

PROGRAM_ERROR 4

ISAD211

A SELECT INTO statement returns no rows (ORA01403)

VALUE_ERROR 4

4

conversion of a character string into a number fails (ORA-01722)

PL/SQL has an internal problem (ORA-06501)

24

Database triggers revisited 4coded

in PL/SQL 4critical for data integrity 4server-side integrity ensures more robust database than client-side validation 4

4 ISAD211

25

Client-side integrity

ISAD211

integrity checks

application

database

database is vulnerable

26

Server-side integrity

ISAD211

integrity checks

application

database

database is protected

27

Issues 4 Data

sent to the server before being checked for integrity increases network traffic and server load 4 Perform simple client-side 'belt and braces' integrity checking, e.g. check for required fields 4 Opaque server error messages need to be communicated sensibly to the application/user ISAD211

28

Server- & client-side integrity

integrity checks

ISAD211

integrity checks

application

database

DB & network load is reduced

29

Example trigger CREATE OR REPLACE TRIGGER trg_clients BEFORE INSERT OR UPDATE OF dob ON clients FOR EACH ROW BEGIN  IF INSERTING THEN  SELECT seq_client_id.nextval  INTO :NEW.client_id  FROM sys.dual;  END IF; IF MONTHS_BETWEEN(SYSDATE,:NEW.dob) < 18*12 THEN  /* Issue error code (ORA-20000) and message */  RAISE_APPLICATION_ERROR(-20000, 'Client must be at least 18 years of age');  END IF; END; 

4

ISAD211

30

References 4 PLSQL

by Example on Portal

4 4 PL/SQL

User's Guide and Reference

 

4 Oracle10g

Database Error Messages

4 

ISAD211

31

Exercise 4

Write a PL/SQL function (AVERAGE_PRICE) to calculate the average retail price of books (rounded to the nearest penny) in the Harrington books table where the retail price is above an amount specified as a parameter. You should use a cursor.



4

Test your results against

 



ISAD211



SELECT ROUND(AVG(retail_price),2) FROM books WHERE retail_price > &threshold

32