Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2010
    Posts
    2

    Unanswered: Help with procedure

    hey everyone,

    this is the question i am trying to write so i just want to make sure i am goin into the right direction as i am bit confused.

    Write a procedure that uses a collection to find and display how many employees have the same name and what is the most common name.

    this is the table i am working from

    CREATE TABLE EMPX
    (EMPNO NUMBER(4) NOT NULL,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7, 2),
    COMM NUMBER(7, 2),
    DEPTNO NUMBER(2),
    constraint emp_pk primary key(empno));



    here is my code so far


    Code:
    create or replace
    procedure common_names
    is
    -- define a varray type to store upto 25 rows from the empx table
    TYPE empArray IS VARRAY(25) OF empx%rowtype; 
    -- declare an empty instance of the varray type
    empList empArray := empArray();
    
    -- define a cursor to hold multiple (all) rows from the emp table
    cursor empCur is select * from empx;
    
    vCount number(2):=1;
    --declare and initialise counter for varray
    
    begin
    
    empList.extend(25); -- extend the varray to hold 25 elements
    
    -- declare a cursor for loop to retrieve all the rows from the cursor
    for empRec in empcur loop -- empRec is a record based on the cursor type
    
    empList(vCount) := empRec; -- put an empRec into the varray at the index specified
    dbms_output.put_line(empList(vCount).ename); -- print out the employees name
    vCount := vcount +1; -- increment vcount
    end loop;
    
    end;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select ename, count(*) from EMPX group by ename order by 2 DESC;

    most common name will be at top of the list
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2010
    Posts
    2
    Cool thanks anacedent

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •