1 / 22

Using SQL in PL/SQL

Using SQL in PL/SQL. ITEC 224 Database Programming. 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.

elam
Download Presentation

Using SQL in PL/SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related