November 17th, 2005, 02:38 PM
|
|
Contributing User
|
|
Join Date: May 2004
Posts: 123
Time spent in forums: 2 Days 5 h 49 m 16 sec
Reputation Power: 5
|
|
|
sequence in procedure
OK, I want to use a sequence inside an insert procedure to generate surrogate keys. I have tried many different things and cannot make it work. This is only my latest attempt. Does anyone know how to make this work?
PHP Code:
(
new_emp_last_name employee.emp_last_name%type,
new_emp_first_name employee.emp_first_name%type,
new_status employee.status%type,
new_street address.street%type,
new_city address.city%type,
new_state address.state%type,
new_zip address.zip%type
)
AS
new_emp_no int; /* declare variable */
base_hour_wage number(1) :=6;
rowcount number(4) :=0;
CURSOR emp_insert IS
SELECT emp_no
FROM employee;
BEGIN
new_emp_no := emp_no_seq.nextVal; /* attempt to apply value to variable*/
SELECT count(*) INTO rowcount
FROM employee
WHERE emp_no = new_emp_no;
IF(rowcount = 0 AND new_status = 'tmp') THEN
INSERT INTO employee(emp_no, emp_last_name, emp_first_name, status)
VALUES(new_emp_no, new_emp_last_name, new_emp_first_name, new_status);
INSERT INTO temp(emp_no)
VALUES(new_emp_no);
UPDATE temp SET base_wage = base_hour_wage
WHERE emp_no = new_emp_no;
UPDATE temp SET summers_worked = summers_worked + 1
WHERE emp_no = new_emp_no;
UPDATE temp SET hourly = summers_worked + base_hour_wage
WHERE emp_no = new_emp_no;
ELSE
IF(rowcount > 0)THEN
DBMS_OUTPUT.PUT_LINE('Employee number in use.');
ELSE
INSERT INTO employee(emp_no, emp_last_name, emp_first_name, status)
VALUES(new_emp_no, new_emp_last_name, new_emp_first_name, new_status);
END IF;
IF(new_status = 'mgr')THEN
INSERT INTO manager(emp_no, salary)
VALUES(new_emp_no, 30000.00);
END IF;
IF(new_status = 'asc')THEN
INSERT INTO associate(emp_no, salary)
VALUES(new_emp_no, 20000.00);
END IF;
END IF;
INSERT into address values(
address_seq.nextVal, new_street, new_city, new_state, new_zip);
INSERT into emp_address values(
address_seq.currVal, new_emp_no);
DBMS_OUTPUT.PUT_LINE('Employee added.');
END;
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
