----------------------------------------------------------------------
-- Hello World
-- Demonstrates an anonymous block and terminal output from SQL*Plus

-- This allows SQL*Plus to use terminal output from PL/SQL
SET SERVEROUTPUT ON

-- Anonymous PL/SQL block
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
/

----------------------------------------------------------------------
/* Hello-world 2 example */
-- Demonstrates DECLARE variable, FOR LOOP and IF-THEN-ELSE 

SET SERVEROUTPUT ON

DECLARE
  v_i NUMBER;
BEGIN

  v_i := 10;

  FOR i IN 1 .. v_i LOOP
    IF i IN (3, 6, 9)  THEN
      DBMS_OUTPUT.PUT_LINE (i || ' Hello, there!');
    ELSE
      DBMS_OUTPUT.PUT_LINE (i || ' Hello, world!');
    END IF;
  END LOOP;

END;
/

/* Single-row query */

SET SERVEROUTPUT ON

DECLARE
  v_name VARCHAR(20);
  v_sal   NUMBER;
BEGIN

  SELECT name, salary
  INTO   v_name, v_sal					-- MUST return ONE row
  FROM instructor
  WHERE id = '10101';

  DBMS_OUTPUT.PUT_LINE('Name: ' ||  v_name || ' Sal: ' || v_sal);

END;
/

/* Multi-row query */
SET SERVEROUTPUT ON

DECLARE
  v_name VARCHAR(20);
  v_sal   NUMBER;
  CURSOR  c1 is SELECT name, salary FROM instructor;
BEGIN
  OPEN c1;

  LOOP
    FETCH c1 INTO v_name, v_sal;
    
    EXIT WHEN c1%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE('Name: ' ||  v_name || ' Salary: ' || v_sal);
  END LOOP;

  CLOSE c1;  
END;
/

----------------------------------------------------------------------
-- Hello World 2
-- Demonstrates procedures, parameters, one-row queries

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE HelloWorld (n IN NUMBER) AS
BEGIN
  FOR i IN 1 .. n LOOP
    DBMS_OUTPUT.PUT_LINE(i || ' Hello World!');
  END LOOP;
END;
/

-- Anonymous block w/ constant for argument
BEGIN
  HelloWorld(5);
END;
/

----------------------------------------------------------------------
-- Anonymous block w/ one-row query and variable argument
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM instructor;
  HelloWorld(v_count);
END;
/

----------------------------------------------------------------------
-- Instructor List
-- Demonstrates explicit cursor declaration, column type (%TYPE),
-- loop, cursor variables (%NOTFOUND, %ROWCOUNT), exception handler

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE InstructorList (v_dept VARCHAR) AS
  CURSOR c IS SELECT name 
               FROM   instructor NATURAL JOIN department 
			   WHERE UPPER(dept_name) = UPPER(v_dept);
   v_name instructor.name%TYPE;
BEGIN
  OPEN c;

  DBMS_OUTPUT.PUT_LINE('Here are the instructors for department ' || v_dept);

  LOOP
    FETCH c INTO v_name;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('There are ' || c%ROWCOUNT || ' instructors.');

  CLOSE c;
END;
/

-- Call procedure in w/ anonymous block
BEGIN
  InstructorList('physics');
END;
/

-- Call function with invalid department name
BEGIN
	InstructorList('mailroom');
END;
/

----------------------------------------------------------------------
-- Instructor List
-- Demonstrates IMPLICIT cursor loop using a record

BEGIN
   FOR instructors IN (SELECT * FROM Instructor) LOOP

      DBMS_OUTPUT.PUT_LINE (instructors.id || '	' || 
			RPAD(instructors.name,15) || '	' ||
			RPAD(instructors.dept_name,15) || '	' ||
			TO_CHAR(instructors.salary) 
			);

   END LOOP; -- implicit CLOSE occurs
END;

----------------------------------------------------------------------
-- Exception Handling
-- Demonstrates exception handling, SQLCODE, SQLERRM in anonymous block

SET SERVEROUTPUT ON

DECLARE
  v_name VARCHAR(20);
  v_sal   NUMBER;
BEGIN

  INSERT INTO instructor VALUES('10101', 'Grasso', 'Comp. Sci.', 65000);

  SELECT name, salary
  INTO   v_name, v_sal
  FROM instructor
  WHERE id = 7499;

  DBMS_OUTPUT.PUT_LINE('Name: ' || 
                        v_name || ' Salary: ' || v_sal);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data found.');
  WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE('You tried to enter a duplicate row.');
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Hey, you tried to divide by zero!');
    DBMS_OUTPUT.PUT_LINE(SQLCODE || ' ' || SQLERRM);
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Hey, this query will return more than 1 row!');
    DBMS_OUTPUT.PUT_LINE(SQLCODE || ' ' || SQLERRM);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE
                          || ' ' || SQLERRM);
END;
/

----------------------------------------------------------------------
/* Multi-row query with two cursors */
-- Demonstrates 2 declared cursors in nested LOOP
SET SERVEROUTPUT ON

DECLARE
  v_dname  department.dept_name%TYPE;
  v_name  VARCHAR(20);
  v_sal    NUMBER;
  CURSOR   c1 is SELECT dept_name FROM department;
  CURSOR   c2 is SELECT name, salary FROM instructor
                 WHERE  dept_name = v_dname;
BEGIN
  OPEN c1;

  LOOP
    FETCH c1 INTO v_dname;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Dname: ' || v_dname);

    OPEN c2;
    LOOP
      FETCH c2 INTO v_name, v_sal;
      EXIT WHEN c2%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('--Name: ' || v_name || ' Salary: ' || v_sal);
    END LOOP;

    CLOSE c2;
  END LOOP;

  CLOSE c1;  
END;
/


----------------------------------------------------------------------
-- Simple Trigger
-- Demonstrates trigger, raising error condition
-- (Obviously, you could just use a foreign key constraint instead)
/* Make sure deptno is always valid */
CREATE OR REPLACE TRIGGER inst_valid_dept
BEFORE UPDATE OR INSERT
ON instructor
FOR EACH ROW
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO   v_count
  FROM   instructor
  WHERE  dept_name = :new.dept_name;

  IF v_count = 0 THEN
    RAISE_APPLICATION_ERROR(-20000, 'Not a valid department name');
  END IF;
END;
/

INSERT INTO instructor (id, name, dept_name, salary) VALUES (11111, 'Grasso', 'BILL', '65000');


CREATE OR REPLACE TRIGGER timeslot_check1 
AFTER INSERT
ON section
FOR EACH ROW
DECLARE
  v_count NUMBER;
BEGIN

  SELECT COUNT(*)
  INTO   v_count
  FROM   time_slot
  WHERE  time_slot_id= :NEW.time_slot_id;

  IF v_count = 0 THEN
	RAISE_APPLICATION_ERROR(-20999,'Time_slot_id value not in time_slot table');
  END IF;
  
END;
/

INSERT INTO section VALUES ('BIO-109', '1', 'Summer', '2009', 'Painter', '514', 'X');