CODEWITHSUNDEEP
sqloracle-databasefunction
Edwin Martin
Edwin Martin

Reputation: 339

calling a pl sql function issue

Hello every one i just was told about this site today and i could use some help. I have read the rules and i understand what i am doing code wise well we just started Pl sql last week before that i had only done basic sql. but now i am stuck on calling the function i made :/ i feel silly. It is a fairly straight forward function and i am sure it is correct as my sql teacher said 'it looks good at a glance' but she did not show me how to call the functions and i really want to see if it working.

I know how to call basic sql and functions and pl sql with dbms.out but i draw a blank here, i remember her saying use 1 instead of employee_no in the select and its just two lines to call it please please can some one show me how to call what i made. I attached the ERD below too.

(a) to calculate and return the total number of different task-types a given employee has worked on.

SHOW ERRORS

CREATE OR REPLACE FUNCTION task_types(eno employee.employee_no%TYPE)
  RETURN NUMBER IS

  task_var task.task_type_no%TYPE;


  BEGIN
    SELECT COUNT(DISTINCT t.task_type_no)
    INTO task_var
    FROM employee e, task t, assignment a
    WHERE e.employee_no = a.employee_no
          AND a.task_id = t.task_id
          AND e.employee_no = eno;

    RETURN task_var;

  END;

Entity-relational diagram:

the ERD

Thank you :)

Upvotes: 3

Views: 103

Answers (2)

Kacper
Kacper

Reputation: 4818

select task_types(your_employee_id) from dual;

As your_employee_id use any existing id or 1 mentioned by you in post.

Upvotes: 0

Rene
Rene

Reputation: 10541

You can use the function in SQL

select task_types(e.employee_no) as task_types
from employee e;

Or, if you know a specific employee number

select task_types(1) 
from dual;

Or in plsql

begin
   dbms_output.put_line(task_types(1));
end;
/

Upvotes: 3

Related Questions