Help needed w/ Explicit Cursors and FETCH STMTS
Date: 1996/08/02
Message-ID: <Roberts_Daniel_G.PriLVMS3-0208960702270001_at_riversend.bms.com>#1/1
Hi Netters..I am getting all tangled up with using an explicit cursor..
What I am trying to do is get all the SSN's of employee's from the
employee table
1 CREATE OR REPLACE PROCEDURE ASSIGNNEWPJT IS
and select those those employee's who work for the research dept (DNUMBER
and DNO=5, DNAME='Research' )
and take those reserch dept employees..and add these employees to the
Works_on table and assing them to project 10 and assign them
20 hours worth of work..I have tried this tons of different ways..and I
know I am getting hung up on the use of the explicit cursor..and
how to either insert or update he works-on table..>Thanks for any and all
help!!!>.>Dan
2 V_SSN EMPLOYEE.SSN%TYPE;
3 V_PNO Works_on.pno%TYPE;
4 V_Hours Works_on.hours%TYPE;
5 C_ResearchEmp_rec Employee%ROWTYPE;
6 CURSOR C_ResearchEmp_cur IS
7 SELECT *
8 FROM employee,department
9 WHERE dno=dnumber AND dname='Research';
10 BEGIN
11 LOOP
12 OPEN C_ResearchEmp_cur;
13 FETCH C_ResearchEmp_cur
14 INTO C_ResearchEmp_rec;
15 UPDATE works_on
16 SET V_SSN= C_ResearchEmp_rec.ssn,
17 V_PNO=10,V_HOURS=20
18 WHERE dname='Research' and DNO=Dnumber and Employee.ssn=works_ssn;
19 EXIT WHEN C_ResearchEmp_cur%NOTFOUND;
20 END LOOP;
21 COMMIT;
22* END assignnewpjt;
SQL> /
Warning: Procedure created with compilation errors.
SQL> show errors;
Errors for PROCEDURE ASSIGNNEWPJT:
LINE/COL ERROR
-------- ----------------------------------------------------------------- 13/2 PLS-00394: wrong number of values in the INTO list of a FETCH statement 13/2 PL/SQL: SQL Statement ignored 15/2 PLS-00417: table or view or column does not exist 15/2 PL/SQL: SQL Statement ignored
SQL> SQL> desc employee;
Name Null? Type
------------------------------- -------- ----
FNAME NOT NULL CHAR(15) MINIT CHAR(1) LNAME NOT NULL CHAR(15) SSN NOT NULL CHAR(9) BDATE DATE ADDRESS CHAR(30) SEX CHAR(1) SALARY NUMBER(7) SUPERSSN CHAR(9) DNO NUMBER(1) SQL> desc works_on; Name Null? Type
------------------------------- -------- ----
ESSN NOT NULL CHAR(9) PNO NOT NULL NUMBER(2) HOURS NUMBER(4,1) SQL> desc department; Name Null? Type
------------------------------- -------- ----
DNAME NOT NULL CHAR(15) DNUMBER NOT NULL NUMBER(1) MGRSSN CHAR(9) MGRSTARTDATE DATE
S
-- Dan Roberts BRISTOL-MYERS SQUIBB PHARM. RES. CENTER "Nature is last at Bats" PRINCETON, NEW JERSEY U.S.A. PLANET EARTH, MILKY-WAY GALAXY <<<<INTERNET ADDRESS>>>>Roberts_Daniel_G.PriLVMS3_at_MSMAIL.BMS.COMReceived on Fri Aug 02 1996 - 00:00:00 CEST