Fetching Data from the Cursor OR c_emp_cursor%NOTFOUND; Non-Predefined Error Trapping

DECLARE DBMS_OUTPUT.PUT_LINE( v_emp_rec.employee_id To trap Oracle Server error 01400 (“cannot insert NULL”):
CURSOR c_emp_cursor IS ||' '||v_emp_record.last_name); DECLARE
SELECT employee_id, last_name END LOOP; e_insert_excep EXCEPTION;
FROM employees CLOSE c_emp_cursor; PRAGMA EXCEPTION_INIT(e_insert_excep,-01400);
WHERE department_id =30; END; BEGIN
v_empno employees.employee_id%TYPE; The example in the slide retrieves the first 10 employees one by one. INSERT INTO departments(department_id,
v_lname employees.last_name%TYPE; This example shows how the %ROWCOUNT and %NOTFOUND department_name)VALUES (280, NULL);
BEGIN attributes can be used for exit conditions in a loop. EXCEPTION
OPEN c_emp_cursor; WHEN e_insert_excep THEN
LOOP Cursor FOR Loops Using Subqueries DBMS_OUTPUT.PUT_LINE('INSERT OPERATION
FETCH c_emp_cursor INTO v_empno, v_lname; BEGIN FAILED');
EXIT WHEN c_emp_cursor%NOTFOUND; FOR emp_record IN (SELECT employee_id, DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE( v_empno ||' last_name FROM employees END;
'||v_lname); WHERE department_id =30)
END LOOP; LOOP Functions for Trapping Exceptions
CLOSE c_emp_cursor; DBMS_OUTPUT.PUT_LINE(emp_record.employee_id DECLARE
END; ||' '||emp_record.last_name); error_code NUMBER;
END LOOP; error_message VARCHAR2(255);
Cursors and Records END; BEGIN
DECLARE ...
CURSOR c_emp_cursor IS Cursors with Parameters EXCEPTION
SELECT employee_id, last_name DECLARE ...
FROM employees WHERE department_id =30; CURSOR c_emp_cursor (deptno NUMBER) IS WHEN OTHERS THEN
v_emp_rec c_emp_cursor%ROWTYPE; SELECT employee_id,last_name FROM employees ROLLBACK;
BEGIN WHERE department_id = deptno; error_code := SQLCODE ;
OPEN c_emp_cursor; ... error_message := SQLERRM ;
LOOP BEGIN INSERT INTO errors (e_user, e_date,
FETCH c_emp_cursor INTO v_emp_record; OPEN c_emp_cursor (10); error_code,error_message)
EXIT WHEN c_emp_cursor%NOTFOUND; ... VALUES(USER,SYSDATE,error_code,
DBMS_OUTPUT.PUT_LINE(v_emp_rec.employee_id CLOSE c_emp_cursor; error_message);
||' '||v_emp_record.last_name); OPEN c_emp_cursor (20); END;
END LOOP; ...
CLOSE c_emp_cursor; You can pass parameters to the cursor that is used in a cursor FOR Trapping User-Defined Exceptions
END; DECLARE
loop:
v_deptno NUMBER := 500;
Cursor FOR Loops DECLARE
v_name VARCHAR2(20) := 'Testing';
CURSOR c_emp_cursor(p_deptno NUMBER,
DECLARE e_invalid_department EXCEPTION;
CURSOR c_emp_cursor IS p_job VARCHAR2) IS
BEGIN
SELECT employee_id,last_name FROM employees SELECT ...
UPDATE departments
WHERE department_id =30; BEGIN
SET department_name = v_name
BEGIN FOR emp_record IN c_emp_cursor(10, 'Sales')
WHERE department_id = v_deptno;
FOR emp_record IN c_emp_cursor LOOP ...
IF SQL%NOTFOUND THEN
LOOP RAISE e_invalid_department;
Handling the Exception
DBMS_OUTPUT.PUT_LINE(emp_record.employee_id END IF;
||' ' ||emp_record.last_name); DECLARE COMMIT;
END LOOP; v_lname VARCHAR2(15);
EXCEPTION
END; BEGIN
WHEN e_invalid_department THEN
SELECT last_name INTO v_lname FROM employees
DBMS_OUTPUT.PUT_LINE('No such
%ROWCOUNT and %NOTFOUND: Example WHERE first_name='John';
department id.');
DBMS_OUTPUT.PUT_LINE ('John''s last name
DECLARE END;
is :' ||v_lname);
CURSOR c_emp_cursor IS The block shown in the slide updates the department_name of a
EXCEPTION
SELECT employee_id,last_name FROM employees; department. The user supplies the department number and the new
WHEN TOO_MANY_ROWS THEN
v_emp_rec c_emp_cursor%ROWTYPE; name. If the supplied department number does not exist, no rows are
DBMS_OUTPUT.PUT_LINE (' Your select
BEGIN
statement retrieved multiple rows. updated in the departments table.
OPEN c_emp_cursor;
Consider using a cursor.');
LOOP Propagating Exceptions in a Subblock
END;
FETCH c_emp_cursor INTO v_emp_rec; DECLARE
EXIT WHEN c_emp_cursor%ROWCOUNT > 10
. . . INTO v_sal,v_dept_id FROM employees END query_emp;
e_no_rows exception; WHERE employee_id=p_empno; /
e_integrity exception; SELECT avg(salary) INTO v_avg_sal DECLARE
PRAGMA EXCEPTION_INIT (e_integrity, -2292); FROM employees WHERE department_id=v_dept_id; v_emp_name employees.last_name%TYPE;
BEGIN IF v_sal > v_avg_sal THEN v_emp_sal employees.salary%TYPE;
FOR c_record IN emp_cursor LOOP RETURN TRUE; BEGIN
BEGIN ELSE query_emp(171, v_emp_name, v_emp_sal);
SELECT ... RETURN FALSE; DBMS_OUTPUT.PUT_LINE(v_emp_name||' earns '
UPDATE ... END IF; ||to_char(v_emp_sal, '$999,999.00'));
IF SQL%NOTFOUND THEN EXCEPTION END;
RAISE e_no_rows; WHEN NO_DATA_FOUND THEN
END IF; RETURN NULL; Viewing OUT Parameters: Using SQL*Plus Host Variables
END; END; VARIABLE b_name VARCHAR2(25)
END LOOP; VARIABLE b_sal NUMBER
EXCEPTION Invoking the Function with a Parameter EXECUTE query_emp(171, :b_name, :b_sal)
WHEN e_integrity THEN ... BEGIN PRINT b_name b_sal
WHEN e_no_rows THEN ... DBMS_OUTPUT.PUT_LINE('Checking for employee
END; with id 205'); Using IN OUT Parameters
IF (check_sal(205) IS NULL) THEN VARIABLE b_phone_no VARCHAR2(15)
Note in the example that the exceptions (no_rows and
DBMS_OUTPUT.PUT_LINE('The function returned EXECUTE :b_phone_no := '8006330575'
integrity) are declared in the outer block. In the inner block, NULL due to exception'); PRINT b_phone_no
when the no_rows exception is raised, PL/SQL looks for the ELSIF (check_sal(205)) THEN CREATE OR REPLACE PROCEDURE format_phone
exception to be handled in the subblock. Because the exception is not DBMS_OUTPUT.PUT_LINE('Salary > average'); (p_phone_no IN OUT VARCHAR2) IS
handled in the subblock, the exception propagates to the outer block, ELSE BEGIN
where PL/SQL finds the handler. DBMS_OUTPUT.PUT_LINE('Salary < average'); p_phone_no := '(' || SUBSTR(p_phone_no,1,3)
END IF; ||') ' || SUBSTR(p_phone_no,4,3)
Creating a Procedure DBMS_OUTPUT.PUT_LINE('Checking for employee ||'-' || SUBSTR(p_phone_no,7);
... with id 70'); END format_phone;
CREATE TABLE dept AS SELECT * FROM departments; IF (check_sal(70) IS NULL) THEN /
CREATE PROCEDURE add_dept IS DBMS_OUTPUT.PUT_LINE('The function returned EXECUTE format_phone (:b_phone_no)
v_dept_id dept.department_id%TYPE; NULL due to exception'); PRINT b_phone_no
v_dept_name dept.department_name%TYPE; ELSIF (check_sal(70)) THEN
BEGIN The slide example creates a procedure with an IN OUT parameter
...
v_dept_id:=280; to accept a 10-character string containing digits for a phone number.
END IF;
v_dept_name:='ST-Curriculum'; END; The procedure returns the phone number formatted with
INSERT INTO dept parentheses around the first three characters and a hyphen after the
(department_id,department_name) The check_sal function is written to determine whether the
sixth digit—for example, the phone string 8006330575 is returned as
VALUES(v_dept_id,v_dept_name); salary of a particular employee is greater than or less than the
(800) 633-0575.
DBMS_OUTPUT.PUT_LINE(' Inserted ' average salary of all employees working in the same department.
|| SQL%ROWCOUNT ||' row '); Passing Parameters
Using IN Parameters
END; CREATE OR REPLACE PROCEDURE add_dept
... CREATE OR REPLACE PROCEDURE raise_salary (p_name IN departments.department_name%TYPE,
BEGIN (p_id IN employees.employee_id%TYPE, p_loc IN departments.location_id%TYPE) IS
add_dept; p_percent IN NUMBER)IS BEGIN
END; BEGIN INSERT INTO departments
/ UPDATE employees (department_id,department_name, location_id)
SET salary = salary * (1 + p_percent/100) VALUES
In the code example, the add_dept procedure inserts a new WHERE employee_id = p_id; (departments_seq.NEXTVAL, p_name , p_loc );
department with department ID 280 and department name ST- END raise_salary; END add_dept;
Curriculum. / /
EXECUTE raise_salary(176, 10) -- Passing param using the positional notation.
Passing a Parameter to the Function
CREATE FUNCTION check_sal EXECUTE add_dept ('TRAINING', 2500)
Using the OUT Parameters -- Passing parameters using the named notation.
(p_empno employees.employee_id%TYPE) CREATE OR REPLACE PROCEDURE query_emp
RETURN Boolean IS EXECUTE add_dept (p_loc=>2400,p_name=>'EDUC')
(p_id IN employees.employee_id%TYPE,
v_dept_id employees.department_id%TYPE; p_name OUT employees.last_name%TYPE,
v_sal employees.salary%TYPE; Using the DEFAULT Option for Parameters
p_salary OUT employees.salary%TYPE) IS CREATE OR REPLACE PROCEDURE add_dept
v_avg_sal employees.salary%TYPE; BEGIN
BEGIN (p_name departments.department_name%TYPE
SELECT last_name, salary INTO p_name,p_salary :='Unknown',
SELECT salary,department_id FROM employees WHERE employee_id = p_id;
p_loc departments.location_id%TYPE The Editing department with a manager_id of 99 is not inserted ORDER BY tax(salary) DESC;
DEFAULT 1700) IS because a foreign key integrity constraint violation on manager_id
BEGIN Restrictions on Calling Functions from SQL
ensures that no manager has an ID of 99.
INSERT INTO departments CREATE OR REPLACE FUNCTION dml_call_sql
(department_id, department_name, location_id) Creating and Invoking a Stored Function Using the CREATE (p_sal NUMBER) RETURN NUMBER IS
VALUES FUNCTION Statement BEGIN
(departments_seq.NEXTVAL, p_name, p_loc); CREATE OR REPLACE FUNCTION get_sal INSERT INTO employees(employee_id, last_name,
END add_dept; (p_id employees.employee_id%TYPE) email, hire_date, job_id, salary)
EXECUTE add_dept RETURN NUMBER IS VALUES(1, 'Frost', '[email protected]',
EXECUTE add_dept ('ADVERTISING', p_loc => 1200) v_sal employees.salary%TYPE := 0; SYSDATE, 'SA_MAN', p_sal);
EXECUTE add_dept (p_loc => 1200) BEGIN RETURN (p_sal + 100);
The second code box in the slide shows three ways of invoking the SELECT salary INTO v_sal END;
FROM employees WHERE employee_id = p_id; UPDATE employees
add_dept procedure.
RETURN v_sal; SET salary = dml_call_sql(2000)
Invoke a procedure from another stored procedure END get_sal; WHERE employee_id = 170;
CREATE OR REPLACE PROCEDURE process_employees / The dml_call_sql function in the slide contains an INSERT
IS -- Invoke the function as an expression or as statement that inserts a new record into the EMPLOYEES table and
CURSOR cur_emp_cursor IS -- a parameter value. returns the input salary value incremented by 100. This function is
SELECT employee_id EXECUTE dbms_output.put_line(get_sal(100)) invoked in the UPDATE statement that modifies the salary of
FROM employees; A function must always return a value. The example does not return a employee 170 to the amount returned from the function. The
BEGIN value if a row is not found for a given id. Ideally, create an exception UPDATE statement fails with an error indicating that the table is
FOR emp_rec IN cur_emp_cursor handler to return a value as well. mutating (that is, changes are already in progress in the same table).
LOOP
raise_salary(emp_rec.employee_id, 10); Using Different Methods for Executing Functions In the following example, the query_call_sql function queries
END LOOP; -- As a PL/SQL expression, get the results the SALARY column of the EMPLOYEES table:
COMMIT; -- using host variables CREATE OR REPLACE FUNCTION query_call_sql
END process_employees; VARIABLE b_salary NUMBER (p_a NUMBER) RETURN NUMBER IS
/ EXECUTE :b_salary := get_sal(100) v_s NUMBER;
The example in the slide shows you how to invoke a procedure from -- As a PL/SQL expression, get the results BEGIN
another stored procedure. The PROCESS_EMPLOYEES stored -- using a local variable SELECT salary INTO v_s FROM employees
procedure uses a cursor to process all the records in the EMPLOYEES DECLARE WHERE employee_id = 170;
table and passes each employee’s ID to the RAISE_SALARY sal employees.salary%type; RETURN (v_s + p_a);
procedure, which results in a 10% salary increase across the BEGIN END;
sal := get_sal(100); /
company.
DBMS_OUTPUT.PUT_LINE('The salary is: '||sal);
END; When invoked from the following UPDATE statement, it returns the
Handled Exceptions error message similar to the error message shown in the slide:
CREATE PROCEDURE add_department /
(p_name VARCHAR2, p_mgr NUMBER, p_loc NUMBER) -- Use as a parameter to another subprogram
UPDATE employees SET salary=query_call_sql(100)
IS EXECUTE dbms_output.put_line(get_sal(100))
WHERE employee_id = 170;
BEGIN -- Use in a SQL statement
INSERT INTO DEPARTMENTS (department_id, SELECT job_id, get_sal(employee_id) Example of Using Named and Mixed Notation from a SQL
department_name, manager_id, location_id) FROM employees; Statement
VALUES (DEPARTMENTS_SEQ.NEXTVAL, p_name, CREATE OR REPLACE FUNCTION f
p_mgr, p_loc); Function in SQL Expressions
(p_parameter_1 IN NUMBER DEFAULT 1,
DBMS_OUTPUT.PUT_LINE('Added Dept: ' CREATE OR REPLACE FUNCTION tax
p_parameter_5 IN NUMBER DEFAULT 5)
|| p_name); (p_value IN NUMBER) RETURN NUMBER IS
RETURN NUMBER IS
EXCEPTION BEGIN
v_var number;
WHEN OTHERS THEN RETURN (p_value * 0.08);
BEGIN
DBMS_OUTPUT.PUT_LINE('Err: adding dept: ' END tax;
v_var := p_parameter_1 + (p_parameter_5 * 2);
|| p_name); /
RETURN v_var;
END; SELECT employee_id,last_name,salary,tax(salary)
END f;
CREATE PROCEDURE create_departments IS FROM employees WHERE department_id = 100;
/
BEGIN SELECT f(p_parameter_5 => 10) FROM DUAL;
add_department('Media', 100, 1800); Calling User-Defined Functions in SQL Statements
add_department('Editing', 99, 1800); SELECT employee_id, tax(salary) FROM employees
add_department('Advertising', 101, 1800); WHERE tax(salary) > (SELECT MAX(tax(salary))
END; FROM employees
WHERE department_id = 30)