packages_examples.sql

CREATE OR REPLACE PACKAGE over_pack
IS
	PROCEDURE add_dept(p_depto IN departments.department_id%TYPE, p_name IN departments.department_name%TYPE DEFAULT 'unknown', p_loc IN departments.location_id%TYPE DEFAULT 0);
	PROCEDURE add_dept(p_name IN departments.department_name%TYPE DEFAULT 'unknown', p_loc IN departments.location_id%TYPE DEFAULT 0);
END over_pack;
/

CREATE OR REPLACE PACKAGE BODY over_pack
IS
	PROCEDURE add_dept(p_depto IN departments.department_id%TYPE, p_name IN departments.department_name%TYPE DEFAULT 'unknown', p_loc IN departments.location_id%TYPE DEFAULT 0)
	IS
	BEGIN
		INSERT INTO departments (department_id, department_name, location_id)
		VALUES (p_depto, p_name, p_loc);
	END add_dept;

	PROCEDURE add_dept(p_name IN departments.department_name%TYPE DEFAULT 'unknown', p_loc IN departments.location_id%TYPE DEFAULT 0)
	IS
	BEGIN
		INSERT INTO departments (department_id, department_name, location_id)
		VALUES (departments_seq.NEXTVAL, p_name, p_loc);
	END add_dept;

END over_pack;
/

CREATE TABLE TAX_RATES (rate_name varchar2(10) primary key, rate_value number)
/

INSERT INTO TAX_RATES VALUES ('TAX', 0.19);

CREATE or REPLACE PACKAGE taxes
IS
	tax NUMBER;
END taxes;
/

CREATE OR REPLACE PACKAGE BODY taxes
IS
BEGIN
	SELECT rate_value
	INTO tax
	FROM TAX_RATES
	WHERE rate_name = 'TAX';
END taxes;
/

SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('EL TAX ES ' || taxes.tax);
END;
/

CREATE OR REPLACE PACKAGE taxes_pack
IS
	FUNCTION tax (p_value IN NUMBER) RETURN NUMBER;
END taxes_pack;
/

CREATE OR REPLACE PACKAGE BODY taxes_pack
IS
	
	FUNCTION tax (p_value IN NUMBER) RETURN NUMBER
	IS
	v_rate NUMBER := 0.08;
	BEGIN
		RETURN(p_value * v_rate);
	END tax;

END taxes_pack;
/

DBMS_OUTPUT.PUT_LINE('EL TAX ES ' || taxes_pack.tax(10));

select taxes_pack.tax(salary), salary, last_name FROM employees;

CREATE OR REPLACE PACKAGE pack_cur
IS
	CURSOR c1 IS
		SELECT employee_id
		FROM employees
		ORDER BY employee_id DESC;
	PROCEDURE proc1_3rows;
	PROCEDURE proc4_6rows;
END pack_cur;
/

CREATE OR REPLACE PACKAGE BODY pack_cur
IS
	v_empno NUMBER;
	PROCEDURE proc1_3rows
	IS
	BEGIN
		OPEN c1;
		LOOP
			FETCH c1 INTO v_empno;
			DBMS_OUTPUT.PUT_LINE('Id :' || (v_empno));
			EXIT WHEN c1%ROWCOUNT >= 3;
		END LOOP;
	END proc1_3rows;

	PROCEDURE proc4_6rows
	IS
	BEGIN
		LOOP
			FETCH c1 INTO v_empno;
			DBMS_OUTPUT.PUT_LINE('Id :' || (v_empno));
			EXIT WHEN c1%ROWCOUNT >= 6;
		END LOOP;
		CLOSE c1;
	END proc4_6rows;
END pack_cur;
/