PL/SQL (Embedded SQL)

1. PL/SQL (Embedded SQL) • Introduction • Benefits • Basic Constructs • Anonymous blocks • Procedures • Functions • Packages • Triggers • Cursors • Dynamic SQL

2. Introduction • Embedded SQL (PL/SQL, JAVA/ VB & DB) • Database Server Level Programming (PL/SQL, Transact-SQL, IBM DB2-Cobol, ProC, ProCobol) • Database Client Programming Developer 9i, JDeveloper 9i, Java (J2EE), VB, .Net

3. - Benefits • More powerful than pure SQL because it combines the power of SQL and • Iteration (loops) • Selection (Ifs) • Cursors • Block Structures • Stored Procedures • etc.

4. - Basic Constructs • Basic Structure • Running a program • Variables • SELECT INTO • Comments • IFs • LOOPs • Output

5. -- Basic Structure DECLARE BEGIN EXCEPTION END;

6. - OUTPUT … SET SERVEROUTPUT ON; BEGIN DBMS_OUTPUT.PUT_LINE('This is my fist program'); END; / Before executing code that contains DBMS_OUTPUT.PUT_LINE, must run (in a current session) at SQL prompt: set serveroutput on

7. --- Basic Structure: Example DECLARE v_id INTEGER; v_empno NUMBER; BEGIN v_id := 1234567; SELECT EMPNO INTO V_EMPNO FROM EMP WHERE empno = v_id; DBMS_OUTPUT.PUT_LINE('Value is '||v_empno); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No record exists'); END; /

8. --- Basic Structure: Example DECLARE v_id INTEGER; BEGIN v_id := 1234567; DELETE FROM EMP WHERE id = v_id; END; /

9. -- Running a Program DECLARE BEGIN EXCEPTION END; /

10. -- Variables • Common Data Types • NUMBER • DATE • INTEGER • VARCHAR2 • CHAR • BOOLEAN • Declaration • V_salary NUMBER(9,2); • V_id INTEGER; • V_dob DATE; • V_name VARCHAR2(35); • V_gender CHAR; • V_salary emp.salary%TYPE;

11. -- SELECT INTO DECLARE v_job emp.job%TYPE; v_sal emp.sal%TYPE; v_empno emp.empno%TYPE; BEGIN v_empno := 1234567; SELECT job, sal INTO v_job,v_sal FROM emp WHERE empno = v_empno; END; /

12. -- Another Example declare v_ename emp.ename%TYPE; v_sal emp.sal%TYPE; begin select ename, sal into v_ename, v_sal from emp where empno = '7844'; DBMS_OUTPUT.PUT_LINE('solution is:'||v_ename||'---'||v_sal); exception When others then DBMS_OUTPUT.PUT_LINE('no record'); end; / Before executing code that contains DBMS_OUTPUT.PUT_LINE, must run at SQL prompt: set serveroutput on

13. -- Comments DECLARE /* This block will do so and so and so. */ v_job emp.job%TYPE; v_sal emp.sal%TYPE; v_empno emp.empno%TYPE; BEGIN v_empno := 1234567; -- The use of this sentence is so and so. -- The following SELECT INTO statement will do so and so. SELECT job, sal INTOv_job,v_sal FROM emp WHERE empno = v_empno; END; /

14. -- IFs • IF – END IF • IF – ELSE – END IF • IF – ELSIF – ELSE – END IF

15. --- IF – END IF DECLARE … … BEGIN … … v_commison := 7500; IF v_dept = 10 THEN v_commision := 5000; END IF; … … END; /

16. --- IF – ELSE – END IF DECLARE … … BEGIN … … IF v_dept = 10 THEN v_commision := 5000; ELSE v_commision := 7500; END IF; … … END; /

17. --- IF – ELSIF – ELSE – END IF DECLARE … … BEGIN … … IF v_dept = 10 THEN v_commision := 5000; ELSIF v_dept = 20 THEN v_commison := 5500; ELSIF v_dept = 30 THEN v_commison := 6200; ELSE v_commision := 7500; END IF; … … END; /

18. -- LOOPs • LOOP – EXIT WHEN – END LOOP • FOR – LOOP – END LOOP • WHILE – LOOP – END LOOP

19. --- LOOP – EXIT WHEN – END LOOP DECLARE … v_deptno dept.deptno%TYPE := 50; v_counter integer := 1; … BEGIN … LOOP INSERT INTO dept(deptno) VALUES(v_deptno); v_counter := v_counter + 1; v_deptno := v_deptno + 10; EXIT WHEN v_counter > 5; END LOOP; … END; /

20. --- FOR – LOOP - END LOOP DECLARE … v_deptno dept.deptno%TYPE := 50; v_counter integer; … BEGIN … FOR v_counter IN 1..5 LOOP INSERT INTO dept(deptno) VALUES(v_deptno); v_deptno := v_deptno + 10; END LOOP; … END; /

21. --- WHILE – LOOP - END LOOP DECLARE … v_deptno dept.deptno%TYPE := 50; v_counter integer; … BEGIN … v_counter := 1; WHILE v_counter <= 5 LOOP INSERT INTO dept(deptno) VALUES(v_deptno); v_deptno := v_deptno + 10; END LOOP; … END; /

22. … - OUTPUT SET SERVEROUTPUT ON; DECLARE v_sum_sal emp.sal%TYPE; v_deptno emp.deptno%TYPE := 10; BEGIN SELECT SUM(sal) INTO v_sum_sal FROM emp WHERE deptno = v_deptno; DBMS_OUTPUT.PUT_LINE('The sum is ‘ || TO_CHAR(v_sum_sal)); END; /

23. - Anonymous Block DECLARE v_id INTEGER; BEGIN v_id := 1234567; DELETE FROM EMP WHERE id = v_id; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No record exists'); END; /

24. -- Nesting Anonymous Blocks

25. Exceptions Examples are NO_DATA_FOUND OTHERS To display details of oracle standard error message • EXCEPTION • WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘Error detail is: ‘||SQLERRM)

26. - Procedure • Is a block with a name • The DECLARE key word is not used • Parameters can be • IN • OUT • IN OUT • Is stored (USER_SOURCE)

27. -- Creating or Replacing a Procedure … CREATE OR REPLACE PROCEDURE pname( ) IS BEGIN EXCEPTION END; /

28. … -- Creating or Replacing a Procedure SET SERVEROUTPUT ON; CREATE OR REPLACE PROCEDURE proc_test(p_empno IN VARCHAR2) IS v_job EMP.job%TYPE; v_sal EMP.sal%TYPE; BEGIN SELECT job, sal INTO v_job,v_sal FROM emp WHERE empno = p_empno; DBMS_OUTPUT.PUT_LINE('job is '||v_job); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR...'); END; / SQL> Show errors SQL> execute proc_test(5893);

29. -- Invoking a Procedure DECLARE … BEGIN … proc_test(’23’); … END; / Or SQL> exec proc_test(‘1123’)

30. Another Example SQL> ed Wrote file afiedt.buf 1 create or replace procedure test_proc is 2 v_id INTEGER; 3 v_empno emp.empno%TYPE; 4 BEGIN 5 v_id := 1234567; 6 select empno into v_empno 7 FROM EMP 8 WHERE empno = v_id; 9 EXCEPTION 10 WHEN NO_DATA_FOUND THEN 11 DBMS_OUTPUT.PUT_LINE('No record exists'); 12* END; SQL> / Procedure created. SQL> exec test_proc No record exists PL/SQL procedure successfully completed. SQL> show errors (to see errors for procedures, functions)

31. - Function • Is a block with a name • Returns one value only. • The DECLARE key word is not used • Parameters can only be IN • Is stored

32. -- Creating or Replacing a Function … CREATE OR REPLACE FUNCTION fname( ) RETURN datatype IS BEGIN EXECPTION END; /

33. … -- Creating or Replacing a Function CREATE OR REPLACE FUNCTION sum_dept_sal(p_deptno IN NUMBER) RETURN emp.sal%TYPE IS v_sum_sal emp.sal%TYPE; BEGIN SELECT SUM(sal) INTO v_sum_sal FROM emp WHERE deptno = p_deptno; RETURN v_sum_sal; END; /

34. -- Invoking a Function SET SERVEROUTPUT ON; DECLARE … v_sal emp.sal%TYPE; … BEGIN … v_sal := sum_dept_sal(10); DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_sal)); … END; /

35. Example, passing parameters

36. Exercise • Write down a procedure that displays all records (all columns) from EMP table. Make sure program should restricted to fix number of records of this table. Display Date and TIME portions of column that has data type DATE. • Write down a procedure that can insert large number of records entered at the time of its execution. You can use EMP table and appending counter with some columns’ values

37. - Packages • Package • Package Specification • Package Body • Invoking Package subprogram

38. -- Package Specification CREATE OR REPLACE PACKAGE emp_info is v_count INTEGER; PROCEDURE insert_record( p_empno IN NUMBER , p_ename IN VARCHAR2 , p_job IN VARCHAR2 , p_sal IN NUMBER , p_comm IN NUMBER , p_deptno IN VARCHAR2); PROCEDURE delete_record(p_empno IN NUMBER); FUNCTION sum_dept_sal( p_deptno IN dept.deptno%TYPE) RETURN is dept.sal%TYPE; END emp_info; /

39. -- Package Body CREATE OR REPLACE PACKAGE BODY emp_info is` PROCEDURE insert_record(p_empno IN NUMBER,p_ename IN VARCHAR2, p_job IN VARCHAR2,p_sal IN NUMBER, p_comm IN NUMBER,p_deptno IN VARCHAR2) IS BEGIN INSERT INTO EMP(empno,ename,job,sal,comm,deptno) VALUES(p_empno,p_ename,p_job,p_sal,p_comm,p_deptno); END insert_record; PROCEDURE delete_record(p_empno IN NUMBER) IS BEGIN DELETE FROM EMP WHERE empno=p_empno; END delete_record; FUNCTION sum_dept_sal(p_deptno IN NUMBER) RETURN emp.sal%TYPE IS v_sum_sal emp.sal%TYPE; BEGIN SELECT SUM(sal) INTO v_sum_sal FROM emp WHERE deptno = p_deptno; RETURN v_sum_sal; END; END emp_info; --end of package body /

40. -- Invoking Package Subprogram DECLARE v_sum_sal emp.sal%TYPE; BEGIN … v_sum_sal := emp_info.sum_dept_sal(10); … emp_info.delete_record(1234567); … END; / Or SQL> exec emp_info.insert_record(…,…)

41. - Triggers • Is a stored subprogram associated with a table. • Are fired by an event • Are mainly used for • Security • Enforce complex integrity constraint • Prevent invalid transaction • Event logging

42. -- Creating or Replacing Triggers CREATE OR REPLACE TRIGGER del_emp( p_empno emp.empno%TYPE) BEFORE DELETE ON emp FOR EACH ROW BEGIN INSERT INTO emp_audit VALUES(p_empno, USER, sysdate); END; /

43. … -- Trigger with function usage Example

44. … --Exercise Write a trigger for the following: When a record is added or deleted from an employee table, DEPT.NoOfEmp column gets updated accordingly to number of employees in EMP table corresponding to department number.

45. - Cursors … • Is a pointer to a row. • Its is mainly used with tables which return more than one row. • It is handled implicitly and explicitly.

46. … - Cusrors … CURSOR c_emp IS SELECT empno, ename, job FROM emp WHERE deptno = 20; 7369 Smith Clerk 7566 Jones Manager current row 7788 SCOTT Analyst 7876 Adams Clerk 7906 FORD Analyst

47. … - Cursors DECLARE CURSOR c_emp IS SELECT empno, ename, job FROM emp WHERE deptno = 20; BEGIN FOR v_c IN c_emp LOOP … DBMS_OUTPUT.PUT_LINE(v_c.ename); … END LOOP; … end; /

48. - Example • Given a table with first three columns are composite keys. Write a PL/SQL program using cursor to update cat_template1 with LONG column cat_template. Assuming this tables contains more than 40,000 records.

49. … - Example’s solution

50. Dynamic SQL begin execute immediate 'create table tt(id number(3))'; end; /