Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: Build table example Jump to:  
Category: >> Oracle PL/SQL >> Build table example Bookmark and Share

<< lastnext >>

Snippet Name: Build table example

Description: Simple skeleton code for table building

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: February 27th, 2009

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;
/
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 254 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?