如何创建可以返回特定实体以及所有实体的Oracle存储过程 [英] How to create Oracle stored procedure which can return specific entities as well all entity

问题描述

我是存储过程的初学者.

I am beginner to stored procedure.

我们需要创建一个存储过程，该存储过程应该返回所有记录或仅返回通过IN参数传递的过程，过程返回游标.

We need to create a stored procedure which should return all records or return only passed as IN parameter, procedure returns cursor.

如果通过传递accountId的列表来调用过程，则该过程应仅返回那些帐户，否则返回所有帐户.

If procedure is called by passing list of accountId, it should return only those accounts, else all accounts.

如果您能举一些例子，那就太好了.

If you can place some example it would be great.

推荐答案

下面是一个简单的示例:

Here's a simple example:

考虑表格:PERSONS (person_id, name)

此函数将返回一个游标，如果没有提供任何参数，则该游标将返回一个记录或所有记录:

This function will return a cursor that returns either one record, or all records if no argument is supplied:

CREATE FUNCTION get_person
   (person_id IN persons.person_id%TYPE := NULL)
   RETURN SYS_REFCURSOR IS
   rc SYS_REFCURSOR;
BEGIN
   OPEN rc FOR
     SELECT *
     FROM   persons p
     WHERE  p.person_id = get_person.person_id
     OR     get_person.person_id IS NULL;
   RETURN rc;
END;

这篇关于如何创建可以返回特定实体以及所有实体的Oracle存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！