Calling a function in select statement-CodePudding

I have a function:

CREATE OR REPLACE FUNCTION numOfOrders(name VARCHAR2) RETURN sys_refcursor AS
  test_cur sys_refcursor;
BEGIN
  OPEN test_cur for
  
    SELECT C_CustKey, C_Name, COUNT(*) AS Num_Of_Orders
      FROM Customer c
     INNER JOIN Orders o
        ON o.O_CustKey = c.C_CustKey
     WHERE C_Name = name
     GROUP BY C_CustKey, C_Name;

  RETURN test_cur;
END;
/

the screenshot of the code :

and i called it by typing

SELECT numOfOrders('john') FROM customer;

I get my intended output:

C_CustKey    C_Name     Num_Of_Orders 
0001         john        10

but it gets repeated for the number of rows in my customer table, i can fix this by adding fetch first 1 rows only; but how do I prevent it from happening.

Link to output (I can assure you all 1214 rows are the same) :

Tables

Customer :

Orders :

CodePudding user response：

To cut a long story short: this is wrong:

select numOfOrders('john') from customer;
                                --------
                                this

because you're selecting the same dataset for all rows in customer table. What would be better? This:

select numOfOrders('john') from dual;
                                ----
                                this

Why dual? Because it contains only one row.

A simple example based on Scott's schema (I don't have your data): there are 14 employees in the whole emp table, some of them working in department 10.

Function, similar to yours:

SQL> create or replace function num_of_employees (p_deptno in dept.deptno%type)
  2    return sys_refcursor
  3  is
  4    l_rc sys_refcursor;
  5  begin
  6    open l_rc for
  7      select d.dname,
  8             count(*) as num_of_employees
  9      from dept d join emp e on e.deptno = d.deptno
 10      where d.deptno = p_deptno
 11      group by d.dname;
 12    return l_rc;
 13  end;
 14  /

Function created.

This is what you should do - select from dual; the result is just a single line:

SQL> select num_of_employees(10) from dual;

NUM_OF_EMPLOYEES(10)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

DNAME          NUM_OF_EMPLOYEES
-------------- ----------------
ACCOUNTING                    3          --> this is the result

SQL>

But, if you select from emp (which has 14 rows), you'll get 14 duplicate rows as the result:

SQL> select num_of_employees(10) from emp;

NUM_OF_EMPLOYEES(10)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

DNAME          NUM_OF_EMPLOYEES
-------------- ----------------
ACCOUNTING                    3

CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

DNAME          NUM_OF_EMPLOYEES
-------------- ----------------
ACCOUNTING                    3

CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

DNAME          NUM_OF_EMPLOYEES
-------------- ----------------
ACCOUNTING                    3

<snip>    

14 rows selected.

SQL>

You asked whether it'll work (the dual table) if function returns more than a single row: it will.

I modified the function so that - if no department is passed to the function - it returns number of employees in all departments (i.e. multiple rows):

SQL> create or replace function num_of_employees (p_deptno in dept.deptno%type)
  2    return sys_refcursor
  3  is
  4    l_rc sys_refcursor;
  5  begin
  6    open l_rc for
  7      select d.dname,
  8             count(*) as num_of_employees
  9      from dept d join emp e on e.deptno = d.deptno
 10      where d.deptno = p_deptno or p_deptno is null   --> modified WHERE clause
 11      group by d.dname;
 12    return l_rc;
 13  end;
 14  /

Function created.

Calling the function:

SQL> select num_of_employees(null) from dual;

NUM_OF_EMPLOYEES(NUL
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

DNAME          NUM_OF_EMPLOYEES
-------------- ----------------
ACCOUNTING                    3
RESEARCH                      5
SALES                         6


SQL>