1 Replies - 682 Views - Last Post: 14 December 2010 - 04:07 AM

#1 FljpFl0p  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 58
  • Joined: 13-October 10

Question about inserting data into tables

Posted 30 November 2010 - 11:40 AM

Well, I have 3 table:
EMPLOYEE
CREATE TABLE TRKEMPLOYEE(
        E#              NUMBER(12)      NOT NULL,
        NAME            VARCHAR(50)     NOT NULL,
        DOB             DATE                    ,
        ADDRESS         VARCHAR(300)    NOT NULL,
        HIREDATE        DATE            NOT NULL,
        CONSTRAINT TRKEMPLOYEE_PKEY PRIMARY KEY(E#) );

DRIVER
CREATE TABLE TRKDRIVER(
        E#              NUMBER(12)      NOT NULL,
        L#              NUMBER(8)       NOT NULL,
        STATUS          VARCHAR(10)     NOT NULL,
        CONSTRAINT TRKDRIVER_PKEY PRIMARY KEY(E#),
        CONSTRAINT TRKDRIVER_UNIQUE UNIQUE(L#),
        CONSTRAINT TRKDRIVER_FKEY FOREIGN KEY(E#) REFERENCES TRKEMPLOYEE(E#),
        CONSTRAINT TRKDRIVER_STATUS CHECK ( STATUS IN 
                        ('AVAILABLE', 'BUSY', 'ON LEAVE')) );

MECHANIC
                        
CREATE TABLE TRKMECHANIC(
        E#              NUMBER(12)      NOT NULL,
        L#              NUMBER(8)       NOT NULL,
        STATUS          VARCHAR(10)     NOT NULL,
        EXPERIENCE      VARCHAR(10)     NOT NULL,
        CONSTRAINT TRKMECHANIC_PKEY PRIMARY KEY(E#),
        CONSTRAINT TRKMECHANIC_UNIQUE UNIQUE(L#),
        CONSTRAINT TRKMECHANIC_FKEY FOREIGN KEY(E#) REFERENCES TRKEMPLOYEE(E#),
        CONSTRAINT TRKMECHANIC_STATUS CHECK ( STATUS IN 
                        ('AVAILABLE', 'BUSY', 'ON_LEAVE')),     
        CONSTRAINT TRKMECHANIC_EXPERIENCE CHECK ( EXPERIENCE IN 
                        ('BEGINNER', 'STANDARD', 'EXPERT')) );  

This is what I'm supposed to do:

Quote

Implement a stored PL/SQL procedure that insert into a database full information about an employee i.e., the values of the following attributes E#, NAME, DOB, ADDRESS, HIREDATE, L#, STATUS, EXPERIENCE (only for mechanics).

So far this is all i can get:
CREATE OR REPLACE PROCEDURE INSEMP(
	EMPNUM IN NUMBER,
	EMPNAME IN VARCHAR,
	EMPDOB IN DATE,
	EMPADDRESS IN VARCHAR,
	EMPHIREDATE IN DATE,
	LNUM IN NUMBER,
	STATUS IN VARCHAR, 
	EMPEXP IN VARCHAR) IS
BEGIN
	INSERT INTO TRKEMPLOYEE VALUES(
		EMPNUM, EMPNAME, EMPDOB, EMPADDRESS, EMPHIREDATE);

The problem is, how do I make it so that the program knows when to insert into the DRIVER table and when to insert into the MECHANIC table? And how do I execute it?
Thanks for helping me guys, or for at least checking out my question.
BTW, it's 2:38am right now and I'm about to go to sleep so if you guys reply early, I won't be able to reply back for a while. Just wanna let you know in case you think the problem is solved and I didn't even bother saying thanks.

This post has been edited by FljpFl0p: 30 November 2010 - 11:43 AM


Is This A Good Question/Topic? 1
  • +

Replies To: Question about inserting data into tables

#2 Guest_Priyanka*


Reputation:

Re: Question about inserting data into tables

Posted 14 December 2010 - 04:07 AM

Hi
You can pass one more input parameter as EMPTYPE with values either DRIVER OR MECHANIC.
and insert in tables accordingly. Try this procedure.
=====================================================================
CREATE OR REPLACE PROCEDURE INSEMP(
EMPNUM IN NUMBER,
EMPNAME IN VARCHAR,
EMPDOB IN DATE,
EMPADDRESS IN VARCHAR,
EMPHIREDATE IN DATE,
LNUM IN NUMBER,
STATUS IN VARCHAR,
EMPEXP IN VARCHAR,
-------ADDED BY Priyanka - pinks.priyankapatel@gmail.com
-------Add new input parameter with values either DRIVER OR MECHANIC
EMPTYPE in VARCHAR
) IS
BEGIN

INSERT INTO TRKEMPLOYEE VALUES(
EMPNUM, EMPNAME, EMPDOB, EMPADDRESS, EMPHIREDATE);

IF EMPTYPE = 'DRIVER' THEN
INSERT INTO TRKDRIVER VALUES(
EMPNUM, LNUM, STATUS);

ELSIF EMPTYPE = 'MECHANIC' THEN
INSERT INTO TRKMECHANIC VALUES(
EMPNUM, LNUM, STATUS, EXPERIENCE);

ELSE
DBMS_OUTPUT.put_line('PLEASE ENTER EMPTYPE')

END IF;
END
=====================================================================
Thanks
Priyanka
pinks.priyankapatel@gmail.com
Was This Post Helpful? 0

Page 1 of 1