Lecture 4

1. Lecture 4 PL/SQL language

2. PL/SQL language • PL/SQL – procedural SQL • Allows combining procedural and SQL code • PL/SQL code is compiled, including SQL fragments • PL/SQL code is executed: • on the server (procedures, packages, triggers) • can be executed on the client (e.g. Oracle Forms applications)

3. Example Java code Connection conn = getDbConnection(); PreparedStatement pstm=conn.prepareStatement( "DELETE FROM some_table WHERE ID = ?"); pstm.setInt(1, 100); pstm.executeUpdate(); • SQL code is: • included as text • interpreted at run-time

4. Example PL/SQL code DECLARE i INTEGER; BEGIN i := 100; IF i = 100 THEN DELETE FROM some_table WHERE id = i; END IF; COMMIT; END; • SQL code is mixed with procedural statements • SQL code is compiled together with PL/SQL block. SQL is validated at compilation time.

5. Real PL/SQL code • Trigger body that populates value of the ID column from sequence BEGIN IF :new.id IS NULL THEN SELECT test5_seq.NEXTVAL INTO :new.id FROM DUAL; END IF; END;

6. Trigger definition example • Full trigger definition CREATE OR REPLACE TRIGGER test5_trg BEFORE INSERT ON test5 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF :new.id IS NULL THEN SELECT test5_seq.NEXTVAL INTO :new.id FROM DUAL; END IF; END; /

7. Procedure definition example CREATE OR REPLACE PROCEDURE test_proc(p_id number) IS BEGIN delete from some_table where id = p_id; commit; END; /

8. Function definition example CREATE OR REPLACE FUNCTION count_rows RETURN NUMBER IS cnt NUMBER; BEGIN SELECT count(*) INTO cnt FROM some_table; RETURN cnt; END; /

9. PL/SQL blocks [DECLARE var1 INTEGER; var2 VARCHAR2(10000); ] BEGIN ... [EXCEPTION -- exception handlers] END;

10. PL/SQL language • PL/SQL language is case insensitive (as well as SQL in Oracle) DECLARE var1 INTEGER; BEGIN VAR1 := 3; -- the following two statements -- are identical SELECT count(*) INTO var1 FROM tab1; select COUNT(*) into VAR1 FROM TAB1; END;

11. Variables • Variables are defined in PASCAL convention: name of the variable, then type • Variable types: • all SQL types and more: • VARCHAR2 up to VARCHAR2(32767) • BINARY_INTEGER • PLS_INTEGER • BOOLEAN

12. Assignments • := operator is used for PL/SQL assignments • in SQL UPDATE statement normal = is used: DECLARE i NUMBER; BEGIN i := 0; UPDATE some_table t SET t.col = i where t.id = i; END;

13. Assignments • Functions and procedures are treated differently • Function returns result, which must be used CREATE FUNCTION f1 RETURN NUMBER ...; DECLARE res NUMBER; BEGIN f1; -- incorrect res := f1; -- correct SELECT f1 INTO res FROM DUAL; -- correct END;

14. PL/SQL • Operators • =,<=, >=, != (comparisons) • := assignments • LIKE, IN can be used in PL/SQL • -- starts one line comment (like // in C++) • /* multi line comment, like in C */

15. Conditional statements IF condition THEN statements; END IF; IF condition THEN statements; ELSIF condition THEN statements; ELSE statements; END IF;

16. Conditional statements CASE expression WHEN value1 THEN statement; WHEN value2 THEN statement; ELSE statement; END CASE;

17. Simple loop LOOP statement; IF ... THEN EXIT; -- exit loop END IF; statement -- or EXIT WHEN condition; END LOOP;

18. WHILE loop WHILE condition LOOP ... /* statements */ ... END LOOP; WHILE condition LOOP ... EXIT WHEN condition; ... END LOOP;

19. FOR loop FOR i IN 1..10 LOOP /* will execute 10 times */ .... END LOOP; FOR i IN REVERSE 1..10 LOOP -- starts from 10 -- will execute 10 times ... END LOOP;

20. FOR loop • FOR LOOP variable is declared automatically, there is no need to declare it in the DECLARE block • The variable cannot be used outside the loop: FOR i IN 1..10 LOOP ... EXIT WHEN i > 5 END LOOP IF i = 6 THEN -- error i is not declared ...

21. FOR loop • Upper and lower limits for FOR loop can be PL/SQL variables: FOR i IN lower_limit .. upper_limit LOOP ... END LOOP • If upper_limit < lower_limit, the loop will not execute at all

22. NULL statement IF condition THEN NULL; -- can be used as an empty -- statement ELSE statements; END IF; IF condition THEN ELSE statements; END IF; -- incorrect – IF must include at least one -- statement

23. NULL statement BEGIN ... EXCEPTION WHEN OTHERS THEN NULL; -- empty statement => -- ignore exceptions END; EXCEPTION WHEN OTHERS THEN END; -- incorrect

24. SQL statements in PL/SQL • SELECT, UPDATE, INSERT, DELETE statements can be used directly in PL/SQL • UPDATE, INSERT, DELETE can be used as in SQLPlus • SELECT statement returns results: • SELECT INTO can be used for statements that return one row • FOR loop can be used to iterate through the results • CURSOR can be used to fetch SELECT statement results

25. SQL statements in PL/SQL • COMMIT, ROLLBACK, SAVEPOINT can be used in PL/SQL • DDL statements cannot be used in PL/SQL directly • PL/SQL variables can be used in SQL statements: DECLARE cnt NUMBER; BEGIN SELECT count(*) INTO cnt FROM table1; UPDATE table2 SET col1 = cnt WHERE id = 3; END;

26. SQL statements in PL/SQL • Oracle compiles PL/SQL code. • Tables used in PL/SQL statements must exist when the code is compiled • Table, column names are fixed when the code is compiled. It is not possible to use dynamic table name like that: DECLARE tab_name VARCHAR2(30) := 'TABLE1'; BEGIN UPDATE tab_name SET col1 = 0; -- error

27. SQL statements in PL/SQL • In the following statement: UPDATE tab1 SET value = value1 • tab1 is a name of existing database table, to which current user must have access to. If the table does not exist, compilation error is reported • value is a name of a column in table tab1 • value1 can be a name of PL/SQL variable or name of a column in table tab1

28. Illegal PL/SQL code • The following code is illegal CREATE FUNCTION count_rows (table_name IN VARCHAR2) RETURN NUMBER IS cnt NUMBER; BEGIN SELECT count(*) INTO cnt FROM table_name; RETURN cnt; END;

29. SELECT statement in PL/SQL • SELECT INTO: SELECT x, y, z INTO var1, var2, var3 FROM ...; • x, y, z are table column names • var1, var2, var3 are PL/SQL variables • Statement must return exactly one row: • when no rows are returned NO_DATA_FOUND exception is thrown • when more than one row is returned TOO_MANY_ROWS exception is thrown

30. SELECT statement in PL/SQL • SELECT INTO example: BEGIN BEGIN SELECT col1 INTO val1 FROM table1; EXCEPTION WHEN NO_DATA_FOUND THEN val1 := NULL; END; ... END;

31. SELECT statement in PL/SQL FOR rec IN (SELECT * FROM TABLE1) LOOP IF rec.id > 100 THEN EXIT; END IF; END LOOP; • SQL statement must be in brackets • FOR LOOP variable is of type record. It has all the columns from the SELECT statement • The loop is executed for each row returned from the SELECT statement • SELECT statement can return any number of rows (also 0)

32. CURSORS DECLARE CURSOR c1 IS SELECT ename, job FROM emp WHERE sal < 3000; ... BEGIN OPEN c1; FETCH c1 INTO var1, var2; IF c1%NOTFOUND THEN ... END IF; CLOSE c1; END;

33. CURSOR with parameter DECLARE CURSOR c1(v NUMBER) IS SELECT ename, job FROM emp WHERE sal < v; value1 NUMBER; BEGIN OPEN c1(value1); LOOP FETCH c1 INTO var1, var2; EXIT WHEN c1%NOTFOUND; END LOOP; CLOSE c1; END;

34. CURSOR for loop DECLARE CURSOR c1(v NUMBER) IS SELECT ename, job FROM emp WHERE sal < v; BEGIN FOR c1_rec in c1(10) LOOP ... END LOOP; END;

35. Anonymous blocks [DECLARE -- variable declarations] BEGIN -- statements END; • Anonymous blocks can be used • in SQLPlus • in Java and other languages • as sub-block of larger PL/SQL block • DECLARE part is optional, must be used if variables are declared in anonymous block

36. Anonymous block example SQLPLUS=> BEGIN FOR i IN 1 .. 1000 LOOP INSERT INTO tab1 VALUES(i); END LOOP; END; / Note: • "/" at the end is not part of the PL/SQL language, it is just a signal to PL/SQL that block code is finished and should be executed.

37. Anonymous block example Connection conn = getDbConnection(); PreparedStatement pstm=conn.prepareStatement( "BEGIN proc1; END;"); pstm.executeUpdate(); -- executes procedure proc1 Note: • There is no "/" at the end of a block called from Java or other languages

38. Anonymous sub-block • Anonymous blocks: ... -- large PL/SQL block IF condition THEN DECLARE local_variable ...; BEGIN statements; END; END IF; ... -- large block continues

39. Procedures • Procedure (as in Pascal) – function without result CREATE PROCEDURE procedureName (param1 IN NUMBER, param2 INTEGER, -- default is IN param3 IN OUT VARCHAR2, param4 OUT DATE) IS localVar INTEGER; localVar1 VARCHAR2(100); BEGIN statements; END; /

40. Procedures • Local variable declarations follow after IS without DECLARE keyword • The procedure ends with the "END;" • The final "/" is used to signal to SQLPlus that the function body is finished. After the "/" SQLPlus will create the procedure

41. Parameter types • Parameter types are IN, IN OUT and OUT: • IN – caller passes input parameter to the procedure. Parameter value can be changed in the procedure, but caller will not see these changes • IN OUT– input/output parameter, caller will see changes to parameter value • OUT– output parameter, value is returned to the caller • Default parameter type is IN

42. Default parameter values • Procedure and function parameters can have default values • Parameter with default value does not have to be specified by the caller: CREATE PROCEDURE proc( p1 IN NUMBER DEFAULT 0) IS BEGIN ... END; / BEGIN proc(1); -- p1 = 1 proc; -- p1 = 0 END;

43. Passing parameters • Parameters can be passed using three methods: • positional – parameters are specified in the same order as they are declared in the procedure: proc1(1, 2, 'text value'); • named – parameter name is specified along with its value. proc1(p1 => 1, p2 => 2, p3 => 'text value'); • mixed – first parameters using positional notation, the remaining using named notation: proc1(1, p3 => 'text value');

44. Functions CREATE FUNCTION functionName (param1 IN NUMBER) RETURN VARCHAR2 IS localVar INTEGER; localVar1 VARCHAR2(100); BEGIN statements; RETURN localVar1; END; /