Using SQL in PL/SQL

1. Using SQL in PL/SQL ITEC 224 Database Programming

2. Cursors used for SQL statements • Whenever an SQL command is executed in a PL/SQL block, Oracle assigns a private work area for that statement. • This work area contains information about the SQL statement and the set of data returned or affected by that statement. • There are two types of SQL cursor: • implicit cursor. The implicit cursor is used by Oracle server to test and parse the SQL statements • explicit cursor : the explicit cursors are declared by the programmers.

3. Attributes of Implicit Cursors • Using the implicit cursor, we can test the outcome of SQL statements in PL/SQL blocks using the following attributes: • SQL%ROWCOUNT: return the number of rows affected; • SQL%FOUND : BOOLEAN attribute indicating whether the recent SQL statement matches to any row; • SQL%NOTFOUND: BOOLEAN attribute indicating whether the recent SQL statement does not match to any row; • SQL%ISOPEN: a BOOLEAN attribute and always evaluated as FALSE immediately after the SQL statement is executed.

4. DML Commands • DML commands (insert/delete/update) are used with no change inside any PL/SQL block • Insert/delete/update commands modify the contents of the table and they do not really “return” any data. • It is possible to check the status of the statement using the implicit cursor attributes

5. DML commands Task: Insert a new row into the following table: Author(AuthorId, Lname, FName) BEGIN INSERT INTO Author(AuthorId, Lname, FName) VALUES(‘5266’, ‘Armanfar’, ‘Hamed’); END;

6. DML commands Task: Append “old_” to the beginning of all last names BEGIN UPDATE Author SET lname = ‘old_’ || lname; DBMS_OUTPUT.PUT_LINE(SQL%rowcount || ’ names modified’); END;

7. Use of DML commands • You can use as many DML commands as you need in one block • You should plan the DML commands and use the DCL commands commit or rollback in the appropriate places in your PL/SQL block.

8. Anchored declaration may be used here. v_dept_id has the same data type as the department_id column of the employees table Employees.department_id%TYPE Alternatively, rowcount may be used as part of the conditionIf the update command did not find any rows , t means rowcount=0. SQL%ROWCOUNT>0 Use of DML and DCL commands together • Task: • Increase salary of all employees in a given department by 10%. • Read the department id from the keyboard. • If any rows were updated, insert a new row into the raises table containing the department id of the specified department and the number of rows modified and commit. • Otherwise undo the update. • Use the following tables: DECLARE v_dept_id number(4) :=&did; v_count number; BEGIN UPDATE employees SET salary = salary * 1.1 WHERE department_id = v_dept_id; IF SQL%FOUND THEN v_count := SQL%ROWCOUNT; INSERT INTO raises VALUES(v_dept_id, v_count); COMMIT; ELSE ROLLBACK; END IF; END; Employees(employee_id, last_name, salary, department_id) Raises(department_id, number_modified)

9. Use of SELECT in PL/SQL blocks • Select statement returns a set of results and these results are normally displayed on screen • When used inside a PL/SQL block, the results returned by the SELECT statement must be saved into variables therefore SELECT INTO statement is used • Syntax of Select Into : SELECT <column list> INTO <variable list> FROM <table list> WHERE <conditions for choosing rows> GROUP BY <columns | expressions to form group> HAVING <conditions for choosing groups> ORDER BY <columns | expressions to order the results>

10. Example Select Into Task: Print on screen the average salary of all employees Employees(employee_id, last_name, salary, department_id) DECLARE v_avg_salary employees.salary%TYPE; BEGIN SELECT avg(salary) INTO v_avg_salary FROM employees; DBMS_OUTPUT.PUT_LINE( ‘The average salary of all employees is ’ ||v_avg_salary); END; The average salary of all employees is 6461.68

11. If select into returns more than one row, an exception (error) is raised and the program crashes. Example Select Into Task: Print on screen names of employees in the employees table. Employees(employee_id, last_name, salary, department_id) DECLARE v_name employees.last_name%TYPE; BEGIN SELECT last_name INTO v_name FROM employees; DBMS_OUTPUT.PUT_LINE( ‘Employee name is ’ ||v_name); END; DECLARE * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 4

12. If select into returns no rows, an exception (error) is raised and the program crashes. Example Select Into Task: Print on screen name of employees in department 99 DECLARE v_name employees.last_name%TYPE; BEGIN SELECT last_name INTO v_name FROM employees; WHERE department_id = 99; DBMS_OUTPUT.PUT_LINE( ‘Employee name is ’||v_name); END; DECLARE * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 4

13. Handling Select Into exceptions • NO_DATA_FOUND : A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. • TOO_MANY_ROWS : A SELECT INTO statement returns more than one row.VALUE_ERRORAn arithmetic, conversion, truncation, or size-constraint 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, PL/SQL aborts the assignment and raises • VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails.

14. DML related Exceptions Every error that can be detected by Oracle has a unique number. Some exceptions have pre-defined names associated with them.

15. Exception handling section Enter value for dept_id: 99 old 3: v_deptid employees.department_id%TYPE := &dept_id new 3: v_deptid employees.department_id%TYPE := 99 No data PL/SQL procedure successfully completed. Handling exceptions of Select Into DECLARE v_name employees.last_name%TYPE; v_deptid employees.department_id%TYPE := &dept_id BEGIN SELECT last_name INTO v_name FROM employees; WHERE department_id = v_deptid; DBMS_OUTPUT.PUT_LINE( ‘Employee name is ’||v_name); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(‘ More than one row’); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(‘ No data’); END; There are no employees in department 99

16. Exception handling section Enter value for dept_id: 20 old 3: v_deptid employees.department_id%TYPE := &dept_id new 3: v_deptid employees.department_id%TYPE := 20 More than one row PL/SQL procedure successfully completed. Handling exceptions of Select Into DECLARE v_name employees.last_name%TYPE; v_deptid employees.department_id%TYPE := &dept_id BEGIN SELECT last_name INTO v_name FROM employees; WHERE department_id = v_deptid; DBMS_OUTPUT.PUT_LINE( ‘Employee name is ’||v_name); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(‘ More than one row’); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(‘ No data’); END; There are two employees in department 20

17. OTHERS is a catch-all phrase for exceptions Enter value for dept_id: 10 old 3: v_deptid employees.department_id%TYPE := &dept_id new 3: v_deptid employees.department_id%TYPE := 10 Data type conversion problem PL/SQL procedure successfully completed. Handling exceptions of Select Into DECLARE v_name number; v_deptid employees.department_id%TYPE := &dept_id BEGIN SELECT last_name INTO v_name FROM employees; WHERE department_id = v_deptid; DBMS_OUTPUT.PUT_LINE( ‘Employee name is ’||v_name); EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE(‘Data type conversion problem’); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘error’); END; There is only one employee in department 10

18. SELECT INTO CAN HANDLE ONLY A SINGLE ROW

19. OTHERS is a catch-all phrase for exceptions Enter value for dept_id: 10 old 3: v_deptid employees.department_id%TYPE := &dept_id new 3: v_deptid employees.department_id%TYPE := 10 Data type conversion problem PL/SQL procedure successfully completed. Handling exceptions of Select Into DECLARE v_name varchar2(2); v_deptid employees.department_id%TYPE := &dept_id BEGIN SELECT last_name INTO v_name FROM employees; WHERE department_id = v_deptid; DBMS_OUTPUT.PUT_LINE( ‘Employee name is ’||v_name); EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE(‘Data type conversion problem’); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘error’); END; There is only one employee in department 10 and his last name is Whalen

20. Region_id must be unique PL/SQL procedure successfully completed. Handling exceptions of DML statements BEGIN INSERT INTO regions (region_id, region_name) VALUES(1, ‘SCT’); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE(‘Region_id must be unique’); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘Error’); END; Region_id is the Primary key of regions table and therefore must be unique. Region_id=1 already exists in the table

21. You should anticipate all possible problems and handle them in the exception handling section

22. Summary This week we covered • Cursors • Cursor Attributes • How to use DML commands in PL/SQL blocks • How to use select INTO in PL/SQL blocks • Handling exceptions related to SELECT INTO and DMLcommands Next Week we will learn about the explicit cursors