Package Übungen

/*Vorher Tabelle anlegen*/
CREATE TABLE dept_test AS SELECT * FROM dept;
ALTER TABLE dept_test ADD (ma_anz_inc INTEGER);

/*Package und Package Body*/

CREATE OR REPLACE package ma_package IS
  PROCEDURE ma_anz_inc (indeptno IN dept.deptno%TYPE);
  FUNCTION erm_ma_anz (indetpno IN dept.deptno%TYPE)  RETURN INTEGER;
END ma_package;


CREATE OR REPLACE package body ma_package IS
  PROCEDURE ma_anz_inc (indeptno IN dept.deptno%TYPE) IS
  BEGIN
  UPDATE dept_test SET ma_anz_inc = ma_anz_inc +1 WHERE deptno = indeptno;
  END ma_anz_inc;

  FUNCTION erm_ma_anz (indetpno IN dept.deptno%TYPE)  RETURN INTEGER IS
    returnanz INTEGER;
    BEGIN
      SELECT ma_anz_inc INTO returnanz FROM dept_test WHERE deptno = deptno;
      RETURN returnanz;
  END erm_ma_anz;
  END ma_package;

  /*So kann man es testen*/
  SELECT * FROM dept_test;

  EXECUTE ma_package.ma_anz_inc (10);

  SELECT ma_package.erm_ma_anz (10)FROM dual;