set echo on
set serveroutput on
--
-- joseph speigle
-- july 17, 2002
-- lab 11
--
-- question one
--
--
DROP TABLE mylist;
CREATE TABLE mylist (

        id NUMBER PRIMARY KEY,
        lname VARCHAR2(20),
        fname VARCHAR2(20),
        age NUMBER(3)

);
--
-- question 2
--
insert into mylist values (101, 'Shannon','Mary',25);
insert into mylist values (105,'Sanchez','Juan',35);
insert into mylist values (110,'Kwan','Charles',22);
insert into mylist values (112,'O''Hara','Patrick',57);
insert into mylist values (122,'Richman','Cathy',23);

select * from mylist;

--
-- question 3
--
CREATE OR REPLACE PROCEDURE insert_mylist
(i_id IN NUMBER, i_lname IN VARCHAR, i_fname IN VARCHAR,
i_age IN NUMBER)
AS
BEGIN

INSERT INTO mylist VALUES (i_id, i_lname, i_fname, i_age);
EXCEPTION

        WHEN DUP_VAL_ON_INDEX THEN
                DBMS_OUTPUT.PUT_LINE ('try with different id');
        
        WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('exception occurred.  check your values');

END;
.
/
--
--question four
execute insert_mylist(101, 'sp', 'j', 30);
--
-- question five
--
--
DELETE FROM mylist WHERE id = 150;
DECLARE

        v_id mylist.id%TYPE := &sv_id;
        v_lname mylist.lname%TYPE := '&sv_lname';
        v_fname mylist.fname%TYPE := '&sv_fname';
        v_age mylist.age%TYPE :=  &sv_age;
BEGIN
        insert_mylist(v_id, v_lname, v_fname, v_age);
        DBMS_OUTPUT.PUT_LINE('Student id '|| v_id ||' (age '|| v_age||
         ') was added to mylist as '|| v_lname||', '|| v_fname);
EXCEPTION
        WHEN OTHERS THEN NULL;

END;
.
/
--
-- question 6
--
--
SELECT * FROM mylist;
--
--
-- question 7
--
CREATE OR REPLACE PROCEDURE delete_mylist
(i_id IN NUMBER)
AS

v_dummy NUMBER;
BEGIN

        SELECT id INTO v_dummy FROM mylist where id = i_id;
        DELETE FROM mylist WHERE mylist.id = i_id;
        DBMS_OUTPUT.PUT_LINE ('record for id #'||i_id||' successfully deleted.');
EXCEPTION
        WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE ('that id is not in the table');
        WHEN DUP_VAL_ON_INDEX THEN
                DBMS_OUTPUT.PUT_LINE ('try with different id');
        
        WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('exception occurred.  check your values');

END;
.
/
--
-- question 8
--
execute delete_mylist(101);
--
-- question 9
--
select * from mylist;
--
--
--
-- B.
-- create a procedure that will use dual to retrieve user_name and date
CREATE OR REPLACE PROCEDURE retrieval
IS

        v_user VARCHAR2(20);
        v_date DATE;
BEGIN
        SELECT user, sysdate INTO v_user, v_date FROM DUAL;
        DBMS_OUTPUT.PUT_LINE (v_user || chr(10) || to_char(v_date, 'DD/Mon/YYYY HH:MM'));
EXCEPTION
        WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE ('that id is not in the table');
        WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('exception occurred.  check your values');

END;
.
/
--
-- execute that procedure
execute retrieval;
--
-- C
-- write the selects on pg. 317 for insert_mylist
COL object_name FORMAT A18
SELECT object_name, object_type, status FROM user_objects WHERE object_name = 'INSERT_MYLIST';