1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

need help plz

Discussion in 'SQL PL/SQL' started by abhi4u, Jan 5, 2010.

  1. abhi4u

    abhi4u Guest

    iam stuck with these can anyone help me

    6. Create a stored function called GET_SERVICE_DAYS to retrieve the total number of days of service for a specific employee for the current position. The function should accept the employee ID as a parameter and return the number of days of service (distance of hire date from today). Add error handling to account for user input with invalid employee ID.

    You can make use of the following template,

    CREATE OR REPLACE FUNCTION get_service_days
    (p_empid IN employees.employee_id%TYPE)
    RETURN number
    v_days NUMBER(8);
    (SQL to determine total number of days of services for the employee)

    (Code for returning the output of this function)

    WHEN (fill in the appropriate system exception) THEN
    DBMS_OUTPUT.PUT_LINE ('There is no employee with the specified ID');
    END get_service_days;

    7. Create a stored function get_emp_count to print the number of people working in a specific department. The function should accept the department name as a parameter and return the number employee in that department. Add error handling to account for user input with invalid department name.

    For example, if user inputs Marketing, you will see the following output from SQLDeveloper.

    2 employee(s) work for the Marketing department
    v_Return = 2

    8. Create a stored procedure promote_programmer to promote programmers who earn more than 5000 to Senior Programmer and increase their salary by 15%. Hint: Use a cursor with FOR UPDATE and CURRENT OF syntax

    You will need to add a record in the JOBS table first. You can use the add_jobs procedure developed before to add a record into the job table, with JOB_ID = SN_PROG, JOB_TITLE = Senior Programmer, Min_salary = 5000

    and plz tell me where i can learn pl/sql iam dumb in it.
  2. Sadik

    Sadik Community Moderator Forum Guru

    Likes Received:
    Trophy Points:
    Hi, There's no point in posting your homework assignments here... You should show the code you have written and we can help with any problem you may have in that.