oracle函数返回一个列表,如何从Oracle PL/SQL函数返回现有表中的记录？

我就是这样做的.变量/表名/列名在Oracle中不区分大小写,因此我将使用user_name而不是UserName.

CREATE TABLE users( UserName varchar2(20), OtherStuff VARCHAR2(20) );

函数update_and_get_user.请注意,我返回的是ROWTYPE而不是Pipelined Tables.

CREATE OR REPLACE FUNCTION update_and_get_user(

in_UserName IN users.UserName%TYPE,

in_OtherStuff IN users.OtherStuff%TYPE )

RETURN users%ROWTYPE

IS

output_rec users%ROWTYPE;

BEGIN

UPDATE users

SET OtherStuff = in_OtherStuff

WHERE UserName = in_UserName

RETURNING UserName, OtherStuff

INTO output_rec;

RETURN output_rec;

END update_and_get_user;

这就是你怎么称呼它.您无法将ROWTYPE检查为NULL,但您可以检查用户名,例如.

DECLARE

users_rec users%ROWTYPE;

BEGIN

users_rec := update_and_get_user('user', 'stuff');

IF( users_rec.username IS NOT NULL ) THEN

dbms_output.put_line('FOUND: ' || users_rec.otherstuff);

END IF;

END;

使用PIPED ROWS的解决方案如下,但它不起作用.您无法更新查询中的表.

SELECT * FROM TABLE(update_and_get_user('user', 'stuff'))

ORA-14551: cannot perform a DML operation inside a query

解决方案看起来像这样：

CREATE OR REPLACE TYPE users_type

AS OBJECT

(

username VARCHAR2(20),

otherstuff VARCHAR2(20)

)

CREATE OR REPLACE TYPE users_tab

AS TABLE OF users_type;

CREATE OR REPLACE FUNCTION update_and_get_user(

in_UserName IN users.username%TYPE,

in_OtherStuff IN users.otherstuff%TYPE )

RETURN users_tab PIPELINED

IS

output_rec users%ROWTYPE;

BEGIN

UPDATE users

SET OtherStuff = in_OtherStuff

WHERE UserName = in_UserName

RETURNING username, otherstuff

INTO output_rec;

PIPE ROW(users_type(output_rec.username, output_rec.otherstuff));

END;