calling a pl sql function issue

Question

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 :)

Ah thank you i put the wrong tag i am using oracle iSQL*plus — Edwin Martin
– Edwin Martin, Commented Oct 10, 2016 at 17:18

Rene · Accepted Answer · 2016-10-10 13:55:17Z

3

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;
/

answered Oct 10, 2016 at 13:55

Rene

10.6k5 gold badges37 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kacper · Accepted Answer · 2016-10-10 13:57:02Z

0

select task_types(your_employee_id) from dual;

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

answered Oct 10, 2016 at 13:57

Kacper

4,8182 gold badges23 silver badges35 bronze badges

Collectives™ on Stack Overflow

calling a pl sql function issue

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related