PL/SQL Procedural Language / Structured Query Language

1. PL/SQLProcedural Language / Structured Query Language

2. What is PL/SQL? • PL/SQL is Oracle's extension to SQL with design features of programming languages. • The data manipulation and query statements are included in the procedural units of codes. • PL/SQL allows the applications to be written in a PL/SQL procedure or a package and stored within an Oracle server, where these PL/SQL codes can be used as shared libraries or applications, thus enhancing the integration and code reuse. • Oracle pre-compiles PL/SQL codes prior to the actual code execution thus improving the performance.

3. What is PL/SQL? See Handout: Chapter 2 Client Side SQL Application PL/SQL Application Server Side SQL Statement Processor PL/SQL Engine

4. PL/SQL Code Structure? • DECLARE • -- optional, which declares and define variables, cursors and user-defined exceptions. • BEGIN-- mandatory • - SQL statements • - PL/SQL statements • EXCEPTION-- optional, which specifies what actions to take when error occurs. • END; -- mandatory

5. PL/SQL Example 1 -- Declare an Integer variable i.e. v1 -- Assign a value of 3 -- Print it on screen SET SERVEROUTPUT ON DECLAREv1  NUMBER(3);BEGINv1 := 3;DBMS_OUTPUT.PUT_LINE('v1=' || v1);  END;

6. PL/SQL Example 2 DECLARETYPE v_arr IS VARRAY(25) of NUMBER(3);v1 v_arr; v_empno employee.empno%TYPE;BEGIN v1(2) := 3;    DBMS_OUTPUT.PUT_LINE('The Value of v1(2) = ' || v1(2));  END;

7. DML Statements within PL/SQL DECLARE    v_sal employee.sal%TYPE; BEGIN     INSERT INTO employee VALUES (6, ‘John Smith', 10000);     UPDATE employee SET sal = sal + 5000 WHERE empno = 6; SELECT sal INTO v_sal FROM employee WHERE empno = 6;     DBMS_OUTPUT.PUT_LINE('Salary increased to ' || v_sal);  COMMIT; END;

8. Control Statements Conditions Checking IF <condition> THEN [ELSIF <condition> THEN] [ELSE <condition> THEN] END IF;

9. Control Statements LOOP, FOR and WHILE Loops (1) LOOP ... EXIT WHEN <condition> END LOOP; (2) FOR counter IN lower_bound .. upper_bound  ... END LOOP; (3) WHILE <condition> LOOP ... END LOOP;

10. Cursor Manipulation See Handout: Chapter 8 DECLARE CURSOR csr_ac (p_name VARCHAR2) ISSELECT empno, name, salFROM employeeWHERE name LIKE '%p_name%'; v_a employee.empno%TYPE; v_b employee.name%TYPE; v_c employee.sal%TYPE; BEGIN     OPEN csr_ac (‘Jo');     LOOP           FETCH csr_ac INTO v_a, v_b, v_c;         EXIT WHEN csr_ac%NOTFOUND;                                 DBMS_OUTPUT.PUT_LINE(v_a || ' ' || v_b || ' '||v_c);     END LOOP;    CLOSE csr_ac;END;

11. Cursor Manipulation See Handout: Chapter 8 DECLARE CURSOR csr_ac (p_name VARCHAR2) ISSELECT empno, name, salFROM employeeWHERE name LIKE '%p_name%'; v_a employee.empno%TYPE; v_b employee.name%TYPE; v_c employee.sal%TYPE; BEGIN     OPEN csr_ac (‘Jo');     LOOP           FETCH csr_ac INTO v_a, v_b, v_c;         EXIT WHEN csr_ac%NOTFOUND;                                 DBMS_OUTPUT.PUT_LINE(v_a || ' ' || v_b || ' '||v_c);     END LOOP;    CLOSE csr_ac;END;

12. PL/SQL Procedures/Functions • PL/SQL functions returns a scalar value and PL/SQL procedures return nothing. Both can take zero or more number of parameters as input or output. • The special feature about PL/SQL is that a procedure/function argument can be of input (indicating the argument is read-only), output (indicating the argument is write-only) or both (both readable and writable). • A function is called as part of an expression. • For example, the function sal_ok might be called as follows: • IF sal_ok(new_sal, new_title) THEN ...

13. Procedures/Functions Examples PROCEDURE hire_employee (emp_id INTEGER, name VARCHAR2) ISBEGIN INSERT INTO employee VALUES (emp_id, name, 1000);END hire_employee; FUNCTION sal_ok (salary REAL, title REAL) RETURN BOOLEAN IS min_sal REAL; max_sal REAL; BEGIN SELECT losal, hisal INTO min_sal, max_sal FROM sals WHERE job = title; RETURN (salary >= min_sal) AND (salary <= max_sal); END sal_ok;