-- Oracle PL/SQL example by Sandeep Desai (http://www.thedesai.net)
-- type show errors to show compilation errors

set serverout on

-- Have to drop table before dropping type
DROP TABLE emp2;
-- Custom Type
DROP TYPE address_ty;
CREATE TYPE address_ty AS OBJECT
( street VARCHAR(60),
  zip NUMBER
);
/

-- Variable Array which can be column in table e.g array of strings
CREATE OR REPLACE TYPE emp_va as object (name VARCHAR2(10), zip NUMBER);
/




CREATE TABLE emp2 (empno INTEGER, empname VARCHAR2(10), address address_ty,
   CONSTRAINT emp_pk PRIMARY KEY (empno));

DESC emp2;

CREATE OR REPLACE PACKAGE MYEMP AS
  -- parameters are IN, OUT or IN OUT
  PROCEDURE addEmployee(p_empno IN INTEGER, p_empname IN VARCHAR2);
  PROCEDURE updateEmployee(p_empno IN INTEGER, p_empname IN VARCHAR2);
  FUNCTION isEmpCreated(p_empname IN VARCHAR2) RETURN INTEGER;
END MYEMP;
/

CREATE OR REPLACE PACKAGE BODY MYEMP AS
   PROCEDURE addEmployee(p_empno IN INTEGER, p_empname IN VARCHAR2) AS
   BEGIN
     INSERT INTO emp2 (empno, empname) values (p_empno, p_empname);
     COMMIT; -- will release all locks held
   END addEmployee;

   PROCEDURE updateEmployee(p_empno IN INTEGER, p_empname IN VARCHAR2) AS
     v_count INTEGER;
     e_employeeNotFound EXCEPTION;
   BEGIN
     -- SELECT and LOCK ROW
     SELECT COUNT(empno) INTO v_count FROM emp2 WHERE empno = p_empno;
     IF v_count = 0 THEN
        RAISE e_employeeNotFound;
     END IF;
     UPDATE emp2 set empname = p_empname WHERE empno = p_empno;
     COMMIT; -- will release all locks held
   END updateEmployee;


   FUNCTION isEmpCreated(p_empname IN VARCHAR2) RETURN INTEGER IS
     v_ecount INTEGER;
     v_returnValue INTEGER;
   BEGIN
      SELECT COUNT(empname)
      INTO v_ecount
      FROM emp2
      WHERE empname = p_empname;
      IF (v_ecount > 0) THEN
         v_returnValue := 1;
      ELSE
         v_returnValue := 0;
      END IF;
      RETURN v_returnValue;
   END isEmpCreated;
END MYEMP;
/

CREATE OR REPLACE PROCEDURE updateTest AS
  e_employeeNotFound EXCEPTION;
BEGIN
  myemp.updateEmployee(456, 'bar');
  EXCEPTION
  --WHEN e_employeeNotFound THEN
  --   DBMS_OUTPUT.PUT_LINE('caught not found exception');
  WHEN OTHERS THEN
    ROLLBACK;
END updateTest;
/



CREATE OR REPLACE PROCEDURE myemplist(p_empname IN VARCHAR2) AS
   v_empname emp2.empname%TYPE; -- %TYPE or %ROWTYPE
   -- EXPLICIT CURSOR (STATIC CURSOR)
   CURSOR c_emps IS SELECT empname FROM emp2 WHERE empname LIKE p_empname;
   -- DYNAMIC CURSOR
   TYPE t_empRef IS REF CURSOR RETURN emp2%ROWTYPE;
   v_empCV t_empRef;

   -- COLLECTION
   TYPE t_empTab IS TABLE of emp2%ROWTYPE;
   v_emps t_empTab;
BEGIN
   OPEN c_emps;
   LOOP
     FETCH c_emps INTO v_empname;
     EXIT WHEN c_emps%NOTFOUND; -- %FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT
     DBMS_OUTPUT.PUT_LINE('LOOP--' || v_empname); -- || is string concat
   END LOOP;
   CLOSE c_emps;

   FOR v_emp IN c_emps LOOP
      DBMS_OUTPUT.PUT_LINE('FOR--' || v_emp.empname);
   END LOOP;

   OPEN v_empCV FOR SELECT * FROM emp2; -- SELECT list should match t_empRef
   -- IF v_empCV IS NULL will return true
   v_emps := t_empTab();
   --v_emps(1) := t_empTab(789, 'zyx');
   OPEN c_emps;

--   LOOP
--     FETCH c_emps INTO v_emps;
--     EXIT WHEN c_emps%NOTFOUND;
--   END LOOP;
END myemplist;
/



-- WHILE ... LOOP .. END LOOP
-- CASE WHEN x = 3 THEN .. WHEN x = 4 THEN ... ELSE .. END CASE

-- PREDEFINED EXCEPTIONS
-- ORA-0001 DUP_VAL_ON_INDEX
-- ORA-0051 TIMEOUT_ON_RESOURCE
-- ORA-0061 TRANSACTION_BACKED_OUT rolled back due to deadlock
-- ORA-1001 INVALID_CURSOR
-- ORA-1012 NOT_LOGGED_ON
-- ORA-1017 LOGIN_DENIED
-- ORA-1403 NO_DATA_FOUND
-- ORA-1422 TOO_MANY_ROWS
-- More errors such as ZERO_DIVIDE

INSERT INTO emp2 (empno, empname, address) values (111, 'fb', address_ty('123 lane', 98765));
EXECUTE myemp.addEmployee(123, 'foo');
SELECT myemp.isEmpCreated('foo') FROM DUAL;
EXECUTE DBMS_OUTPUT.PUT_LINE(myemp.isEmpCreated('foo'));
EXECUTE myemplist('fo%');
EXECUTE updateTest();

SELECT * FROM emp2;