PL SQL 4 Databases and transactional applications

https://drive.google.com/file/d/0BxYoN5rLDBaRTjhOWWZsMjdPejQ/edit?usp=sharing
Course Objectives
Exceptions


create table enrollment
(student_id number not null,
course varchar2(10));
insert into enrollment values(1,'A1');
insert into enrollment values(2,'A1')
insert into enrollment values(1,'B3');

create table instructor
(instructor_id number not null,
first_name varchar2(20) not null,
last_name varchar2(20) not null,
course varchar2(10));
insert into instructor values (10,'camille','salinesi','A1');
insert into instructor values (10,'camille','salinesi','B12');
insert into instructor values (15,'Raul','Mazo','A1');

Example 1
DECLARE
v_num1 INTEGER := &sv_num1;
v_num2 INTEGER := &sv_num2;
v_result NUMBER;
BEGIN
v_result := v_num1 / v_num2;
DBMS_OUTPUT.PUT_LINE ('v_result: '||v_result);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE ('A number cannot be divided by zero.');
END;

-----------------------------
Example 2
SET SERVEROUTPUT ON;
DECLARE
v_num NUMBER := &sv_num;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Square root of '||v_num||' is '||SQRT(v_num));
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;

-----------------------------
example 3
SET SERVEROUTPUT ON;
DECLARE
v_num NUMBER := &sv_num;
BEGIN
IF v_num mayor= 0 THEN
DBMS_OUTPUT.PUT_LINE ('Square root of '||v_num ||'is '||SQRT(v_num));
ELSE
DBMS_OUTPUT.PUT_LINE ('A number cannot be negative');
END IF;
END;

----------------------------
Example 4
DECLARE
v_student_name VARCHAR2(50);
BEGIN
SELECT first_name||' '||last_name
INTO v_student_name
FROM student
WHERE student_id = 101;
DBMS_OUTPUT.PUT_LINE ('Student name is '||v_student_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('There is no such student');
END;

--------------------------
Example 5
DECLARE
v_student_id NUMBER := &sv_student_id;
v_enrolled VARCHAR2(3) := 'NO';
BEGIN
DBMS_OUTPUT.PUT_LINE ('Check if the student is enrolled');
SELECT 'YES'
INTO v_enrolled
FROM enrollment
WHERE student_id = v_student_id;
DBMS_OUTPUT.PUT_LINE ('The student is enrolled into one course');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('The student is not enrolled');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE
('The student is enrolled in too many courses');
END;

--------------------------
Example 5
DECLARE
v_instructor_id NUMBER := &sv_instructor_id;
v_instructor_name VARCHAR2(50);
BEGIN
SELECT first_name||' '||last_name
INTO v_instructor_name
FROM instructor
WHERE instructor_id = v_instructor_id;
DBMS_OUTPUT.PUT_LINE ('Instructor name is '||v_instructor_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;

--------------------------
Example 6
DECLARE
v_student_id student.student_id%type := &sv_student_id;
v_total_courses NUMBER;
e_invalid_id EXCEPTION;
BEGIN
IF v_student_id menor 0 THEN
RAISE e_invalid_id;
ELSE
SELECT COUNT(*)
INTO v_total_courses
FROM enrollment
WHERE student_id = v_student_id;
DBMS_OUTPUT.PUT_LINE ('The student is registered for '||v_total_courses||' courses');
END IF;
DBMS_OUTPUT.PUT_LINE ('No exception has been raised');
EXCEPTION
WHEN e_invalid_id THEN
DBMS_OUTPUT.PUT_LINE ('An id cannot be negative');
END;

------------------------
Example 7
set serveroutput on
DECLARE
e_test_exception EXCEPTION;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Exception has not been raised');
RAISE e_test_exception;
DBMS_OUTPUT.PUT_LINE ('Exception has been raised');
EXCEPTION
WHEN e_test_exception THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;

------------------------
Example 8
set serveroutput on;
DECLARE
v_zip VARCHAR2(5) := '&sv_zip';
v_city VARCHAR2(15);
v_state CHAR(2);
v_err_code NUMBER;
v_err_msg VARCHAR2(200);
BEGIN
SELECT city, state
INTO v_city, v_state
FROM zipcode
WHERE zip = v_zip;
DBMS_OUTPUT.PUT_LINE (v_city||', '||v_state);
EXCEPTION
WHEN OTHERS THEN
v_err_code := SQLCODE;
v_err_msg := SUBSTR(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE ('Error code: '||v_err_code);
DBMS_OUTPUT.PUT_LINE ('Error message: '||v_err_msg);
END;