SET compatibility V6
/
DROP TABLE accounts
/
CREATE TABLE accounts(
    account_id  NUMBER(4) NOT NULL,
    bal         NUMBER(11,2))
/
CREATE UNIQUE INDEX accounts_index ON accounts (account_id)
/
DROP TABLE action
/
CREATE TABLE action(
    account_id  NUMBER(4) NOT NULL,
    oper_type   CHAR(1) NOT NULL,
    new_value   NUMBER(11,2),
    status      CHAR(45),
    time_tag    DATE NOT NULL)
/
DROP TABLE bins
/
CREATE TABLE bins(
    bin_num     NUMBER(2) NOT NULL,
    part_num    NUMBER(4),
    amt_in_bin  NUMBER(4))
/
DROP TABLE data_table
/
CREATE TABLE data_table(
    exper_num  NUMBER(2),
    n1         NUMBER(5),
    n2         NUMBER(5),
    n3         NUMBER(5))
/
DROP TABLE emp
/
CREATE TABLE emp(
    empno      NUMBER(4) NOT NULL,
    ename      CHAR(10),
    job        CHAR(9),
    mgr        NUMBER(4),
    hiredate   DATE,
    sal        NUMBER(7,2),
    comm       NUMBER(7,2),
    deptno     NUMBER(2))
/
DROP TABLE inventory
/
CREATE TABLE inventory(
    prod_id     NUMBER(5) NOT NULL,
    product     CHAR(15),
    quantity    NUMBER(5))
/
DROP TABLE journal
/
CREATE TABLE journal(
    account_id   NUMBER(4) NOT NULL,
    action       CHAR(45) NOT NULL,
    amount       NUMBER(11,2),
    date_tag     DATE NOT NULL)
/
DROP TABLE num1_tab
/
CREATE TABLE num1_tab(
    sequence   NUMBER(3) NOT NULL,
    num        NUMBER(4))
/
DROP TABLE num2_tab
/
CREATE TABLE num2_tab(
    sequence   NUMBER(3) NOT NULL,
    num        NUMBER(4))
/
DROP TABLE purchase_record
/
CREATE TABLE purchase_record(
    mesg        CHAR(45),
    purch_date  DATE)
/
DROP TABLE ratio
/
CREATE TABLE ratio(
    sample_id  NUMBER(3) NOT NULL,
    ratio      NUMBER)
/
DROP TABLE result_table
/
CREATE TABLE result_table(
    sample_id  NUMBER(3) NOT NULL,
    x          NUMBER,
    y          NUMBER)
/
DROP TABLE sum_tab
/
CREATE TABLE sum_tab(
    sequence   NUMBER(3) NOT NULL,
    SUM        NUMBER(5))
/
DROP TABLE temp
/
CREATE TABLE temp(
    num_col1   NUMBER(9,4),
    num_col2   NUMBER(9,4),
    char_col   CHAR(55))
/
CREATE OR REPLACE PACKAGE personnel AS
    TYPE charArrayTyp IS TABLE OF VARCHAR2(10)
        INDEX BY BINARY_INTEGER;
    TYPE numArrayTyp IS TABLE OF FLOAT
        INDEX BY BINARY_INTEGER;
    PROCEDURE get_employees(
        dept_number IN     INTEGER,
        batch_size  IN     INTEGER,
        found       IN OUT INTEGER,
        done_fetch  OUT    INTEGER,
        emp_name    OUT    charArrayTyp,
        job_title   OUT    charArrayTyp,
        salary      OUT    numArrayTyp);
END personnel;
/
CREATE OR REPLACE PACKAGE BODY personnel AS
    CURSOR get_emp (dept_number INTEGER) IS
        SELECT ename, job, sal FROM emp
            WHERE deptno = dept_number;
    PROCEDURE get_employees(
        dept_number IN     INTEGER,
        batch_size  IN     INTEGER,
        found       IN OUT INTEGER,
        done_fetch  OUT    INTEGER,
        emp_name    OUT    charArrayTyp,
        job_title   OUT    charArrayTyp,
        salary      OUT    numArrayTyp) IS
    BEGIN
        IF NOT get_emp%isopen THEN
            OPEN get_emp(dept_number);
        END IF;
        done_fetch := 0;
        found := 0;
        FOR i IN 1..batch_size LOOP
            FETCH get_emp INTO emp_name(i),
                    job_title(i), salary(i);
            IF get_emp%notfound THEN
                CLOSE get_emp;
                done_fetch := 1;
                EXIT;
            ELSE
                found := found + 1;
            END IF;
        END LOOP;
    END get_employees;
END personnel;
/