stored proc help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: stored proc help

  1. #1
    Join Date
    Feb 2009
    Posts
    91

    stored proc help

    I would like to create a package that contains to PL/SQL blocks:

    department
    dept_id location
    1 London
    2 New York
    3 Singapore
    4 Tokyo


    employee
    emp_id dept_id salary tier_id
    1 2 20000 4
    2 4 200000 1
    3 3 25000 4
    4 1 60000 2
    5 1 30000 3


    tier
    tier_id tier_name
    1 Director
    2 Manager
    3 Analyst
    4 Intern


    The first procedure will take in an department ID and will return both the current timestamp and a list of the different distinct tiers belonging to that department ID. These will be returned as two separate parameters.

    The second stored procedure will take in a tier ID and provide back the second highest salary for that tier.

    Any help would be appreciated

  2. #2
    Join Date
    Feb 2009
    Posts
    91
    I forgot to mention each table has a timestamp column ts

    Quote Originally Posted by PaulH View Post
    I would like to create a package that contains to PL/SQL blocks:

    department
    dept_id location
    1 London
    2 New York
    3 Singapore
    4 Tokyo


    employee
    emp_id dept_id salary tier_id
    1 2 20000 4
    2 4 200000 1
    3 3 25000 4
    4 1 60000 2
    5 1 30000 3


    tier
    tier_id tier_name
    1 Director
    2 Manager
    3 Analyst
    4 Intern


    The first procedure will take in an department ID and will return both the current timestamp and a list of the different distinct tiers belonging to that department ID. These will be returned as two separate parameters.

    The second stored procedure will take in a tier ID and provide back the second highest salary for that tier.

    Any help would be appreciated

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Create table statements and insert statements will help immensely.

    check if thid sql does what you want play with it then you can wrap it in a stored proc

    Code:
    select
    e.tier_id,
    e.salary,
    e.dept_id
    from(
    select 
    e.dept_id,
    e,tier_id,
    e.salary,
    rank() over(partition by e.dept_id,e.tier_id) rank
    from employee e
    where dept_id=1) t
    where rank=2
    from t
    i have a hunch your questions is ambiguous.

    even with the formatting your question is not legible i am afraid
    Last edited by hrishy; 10-01-2009 at 03:40 AM.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    is that home work, isn't it?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Feb 2009
    Posts
    91
    No its me trying to figure out to return back the second highest value in a list
    since I want to throw away the lowest and highest values

Posting Permissions

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



Click Here to Expand Forum to Full Width