rem
rem $Header: timesten/quickstart/sample_code/plsql/select_exception.sql /main/2 2011/09/22 14:46:33 slaw Exp $
rem
Rem  Copyright (c) 1991 by Oracle Corporation
Rem    NAME
Rem      pls_examp11.sql - <one-line expansion of the name>
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem    RETURNS
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem    MODIFIED   (MM/DD/YY)
Rem     rolawson   04/03/09  - moved from sample_code/plsql/scripts to
Rem                            sample_code/plsql
Rem     rolawson   03/04/09  - initial checkin of quickstart files
Rem     nmeng      11/29/06  - renamed file
Rem     rvasired   05/12/92 -  Creation
/*
** This block calculates the ratio between the X and Y columns of
** the RESULT_TABLE table.  If the ratio is greater than 0.72, the block
** inserts the ratio into RATIO.  Otherwise, it inserts -1.
** If the denominator is zero, ZERO_DIVIDE is raised, and a
** zero is inserted into RATIO.
**
** Copyright (c) 1989,1992 by Oracle Corporation
*/

DECLARE
    numerator    NUMBER;
    denominator  NUMBER;
    the_ratio    NUMBER;
    lower_limit  CONSTANT NUMBER := 0.72;
    samp_num     CONSTANT NUMBER := 132;
BEGIN
    SELECT x, y INTO numerator, denominator FROM result_table
        WHERE sample_id = samp_num;
    the_ratio := numerator/denominator;
    IF the_ratio > lower_limit THEN
        INSERT INTO ratio VALUES (samp_num, the_ratio);
    ELSE
        INSERT INTO ratio VALUES (samp_num, -1);
    END IF;
    COMMIT;
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        INSERT INTO ratio VALUES (samp_num, 0);
        COMMIT;
    WHEN OTHERS THEN
        ROLLBACK;
END;
/


/*
** This block uses a cursor to fetch and display the employees
** in a given department. If no employee is found, the exception
** NO_DATA_FOUND is raised.
*/

SET SERVEROUTPUT ON
DECLARE
   CURSOR emp_cursor is select empno, ename from emp where deptno=10;
   emp_rec emp_cursor%rowtype;
BEGIN
   OPEN emp_cursor;
   LOOP
     FETCH emp_cursor INTO emp_rec;
     EXIT WHEN emp_cursor%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE (emp_rec.empno||','||emp_rec.ename);
   END LOOP;
   IF emp_cursor%ROWCOUNT = 0 THEN 
     RAISE NO_DATA_FOUND;
   END IF;
  CLOSE emp_cursor; 
EXCEPTION 
   WHEN NO_DATA_FOUND THEN
     DBMS_OUTPUT.PUT_LINE ('No Employee Found');
END;
/