PLSQL language

1
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)

2
Example Java code

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

3
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.

4
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

5
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
• /

6
Procedure definition example

• CREATE OR REPLACE PROCEDURE
• test_proc(p_id number)
• IS
• BEGIN
• delete from some_table where id p_id
• commit
• END
• /

7
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
• /

8
PL/SQL blocks

• DECLARE
• var1 INTEGER
• var2 VARCHAR2(10000)
• BEGIN
• ...
• EXCEPTION
• -- exception handlers
• END

9
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

10
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

11
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

12
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

13
PL/SQL

• Operators
• ,lt, gt, ! (comparisons)
• assignments
• LIKE, IN can be used in PL/SQL
• -- starts one line comment (like // in C)
• / multi line comment, like in C /

14
Conditional statements
IF condition THEN statements END IF IF
condition THEN statements ELSIF condition
THEN statements ELSE statements END
15
Conditional statements
CASE expression WHEN value1 THEN
statement WHEN value2 THEN statement
ELSE statement END CASE
16
Simple loop
LOOP statement IF ... THEN EXIT --
exit loop END IF statement -- or
EXIT WHEN condition END LOOP
17
WHILE loop
WHILE condition LOOP ... / statements /
... END LOOP WHILE condition LOOP ...
EXIT WHEN condition ... END LOOP
18
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
19
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 gt 5
• END LOOP
• IF i 6 THEN -- error i is not declared
• ...

20
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 lt lower_limit, the loop will not
  execute at all

21
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
22
NULL statement
BEGIN ... EXCEPTION WHEN OTHERS THEN
NULL -- empty statement gt --
ignore exceptions END EXCEPTION WHEN OTHERS
THEN END -- incorrect
23
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

24
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

25
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

26
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

27
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

28
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

29
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

30
SELECT statement in PL/SQL

• FOR rec IN (SELECT FROM TABLE1) LOOP
• IF rec.id gt 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)

31
CURSORS

• DECLARE
• CURSOR c1 IS SELECT ename, job
• FROM emp WHERE sal lt 3000
• ...
• BEGIN
• OPEN c1
• FETCH c1 INTO var1, var2
• IF c1NOTFOUND THEN
• ...
• END IF
• CLOSE c1
• END

32
CURSOR with parameter
DECLARE CURSOR c1(v NUMBER) IS SELECT ename,
job FROM emp WHERE sal lt v value1
NUMBER BEGIN OPEN c1(value1) LOOP
FETCH c1 INTO var1, var2 EXIT WHEN
c1NOTFOUND END LOOP CLOSE c1 END
33
CURSOR for loop
DECLARE CURSOR c1(v NUMBER) IS SELECT ename,
job FROM emp WHERE sal lt v BEGIN
FOR c1_rec in c1(10) LOOP ... END
LOOP END
34
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

35
Anonymous block example

• SQLPLUSgt
• 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.

36
Anonymous block example

• Connection conn getDbConnection()
• PreparedStatement pstmconn.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

37
Anonymous sub-block

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

38
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
• /

39
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

40
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

41
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
• p1 -- p1 0
• END

42
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 gt 1, p2 gt 2, p3 gt 'text value')
• mixed first parameters using positional
  notation, the remaining using named notation
• proc1(1, p3 gt 'text value')

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