Question about PCORACLE7 SQL funcstions and procedures

Netters..I am getting really frustrated when it comes to ORACLE7..and I am trying to fight my way through some code. I am stuck with several examples…and I am wondering if anyone could give me some pointers on what I may be doing wrong…
Also..I have downloaded the PCORCLE7..and I have a question about the environment. When I am at the SQL> prompt..and I type edit updatehours  I go to the notepad file updatehours.sql. Now I make my changes to the code..and I exit notepad and come back to the SQL prompt. My question is. Has the changes I made to update hours been recompiled?? Or will the changes that I just made be used the next time I call the procedure?  Thanks for any and all help!!!..>Dan

My address is
Roberts_D_at_bms.com

Here is the code which I am stuck on.. There are 2 examples total

In this first case I am trying to either add new tuples to the works_on table or modify existing information within the works_on table...

SQL> describe works_on

 Name                            Null?    Type


 ------------------------------- -------- ----

 ESSN                            NOT NULL CHAR(9)
 PNO                             NOT NULL NUMBER(38)
 HOURS                           NOT NULL NUMBER(3,1)

CREATE OR REPLACE PROCEDURE updatehours(

essn   WORKS_ON.ESSN%TYPE,
pno    WORKS_ON.PNO%TYPE,
hours  WORKS_ON.HOURS%TYPE)  IS

DECLARE CURSOR c1 IS
BEGIN
SELECT essn,pno,hours from works_on;

  IF c1%NOTFOUND THEN
    INSERT INTO WORKS_ON(ESSN,PNO,HOURS) VALUES(works_on_essn,works_on_pno,works_on_hours);   ELSE
    UPDATE WORKS_ON SET
    ESSN='123456789',PNO=1,HOURS=15;
  END IF;
COMMIT;
  END updatehours;

SQL> BEGIN
  2 updatehours('123456789',1,32.5);
  3 END;
  4 /

PL/SQL procedure successfully completed.

SQL> select* from works_on;

no rows selected

and..I never get it to work!!!!!!!!!!!!!!1

EXAMPLE#2 In this example I am trying to determine the age of an employee based on his bdate using the function called age from an employee table…and print out the employee name and his age…

SQL> describe 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)

  CREATE OR REPLACE FUNCTION AGE(BDATE DATE)RETURN INTEGER IS actual_age INTEGER
;
BEGIN
SELECT employee.bdate FROM EMPLOYEE
ACTUAL_AGE=(SYSDATE-EMPLOYEE.BDATE)/12
DBMS_OUTPUT.PUT_LINE(lname,fname'HIS AGE IS '|| ACTUAL_AGE); END AGE; I tried two different methods

CREATE OR REPLACE FUNCTION AGE (BDATE DATE) RETURN INTEGER IS  CURSOR c1 IS SELECT bdate FROM employee;  BEGIN
 actual_age integer
 SELECT bdate FROM employee
 actual_age:=(SYSDATE-bdate)/12
 dbms_output.put_line('age is: '(actual_age));  END AGE; and as you can tell..I have no idea where I may be going wrong!!

Thanks for your help!!..>Dan Received on Mon Jul 22 1996 - 00:00:00 CEST