如何从 Oracle PL/SQL 函数的现有表中返回记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2336743/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to return a record from an existing table from an Oracle PL/SQL function?
提问by Paul Hooper
I know it seems like a basic thing, but I've never done this before.
我知道这似乎是一件基本的事情，但我以前从未这样做过。
I'd like to return a single record from an existing table as the result of an Oracle PL/SQL function. I've found a few different ways of doing this already, but I'm interested in the bestway to do it (read: I'm not all that happy with what I've found).
我想从现有表中返回一条记录作为 Oracle PL/SQL 函数的结果。我已经找到了几种不同的方法来做这件事，但我对最好的方法很感兴趣（阅读：我对我发现的东西并不满意）。
The jist of what I am doing is this... I have a table called 'users', and I want a function 'update_and_get_user' which given a UserName (as well as other trusted information about said user) will potentially perform various actions on the 'users' table, and then return either zero or one row/record from said table.
我正在做的事情的要点是……我有一个名为“users”的表，我想要一个函数“update_and_get_user”，它给出了一个用户名（以及有关所述用户的其他受信任信息）可能会执行各种操作'users' 表，然后从所述表中返回零或一个行/记录。
This is the basic outline of the code in my head at the moment (read: no idea if syntax is even close to correct):
这是目前我脑海中代码的基本轮廓（阅读：不知道语法是否接近正确）：
CREATE FUNCTION update_and_get_user(UserName in VARCHAR2, OtherStuff in VARCHAR2)
RETURN users PIPELINED IS
TYPE ref0 IS REF CURSOR;
cur0 ref0;
output_rec users%ROWTYPE;
BEGIN
-- Do stuff
-- Return the row (or nothing)
OPEN cur0 FOR 'SELECT * FROM users WHERE username = :1'
USING UserName;
LOOP
FETCH cur0 INTO output_rec;
EXIT WHEN cur0%NOTFOUND;
PIPE ROW(output_rec);
END LOOP;
END update_and_get_user;
I've seen examples where a record or table is returned, the type of record or table having been created / declared beforehand, but it seems like if the table has already been defined, I should be able to utilize that, and thus not have to worry about syncing the type declaration code if table changes are ever made.
我见过返回记录或表的示例，记录或表的类型已预先创建/声明，但似乎如果表已经定义，我应该能够利用它，因此没有如果曾经进行过表更改，则担心同步类型声明代码。
I'm open to all potential solutions and commentary, but I do reallywant to keep this in a single PL/SQL function (as opposed to code in some other language / framework that communicates with the database multiple times, finishing with some form of 'SELECT * FROM users WHERE username=blah') as the system calling the function and the database itself may be different cities. Outside of that limit, I'm open to changing my thinking.
我对所有潜在的解决方案和评论持开放态度，但我确实希望将其保留在单个 PL/SQL 函数中（而不是其他语言/框架中的代码与数据库多次通信，并以某种形式完成'SELECT * FROM users WHERE username=blah') 作为调用函数的系统和数据库本身可能是不同的城市。在这个限制之外，我愿意改变我的想法。
回答by Peter Lang
This is how I would do it. Variables/table-names/column-names are case-insensitive in Oracle, so I would use user_nameinstead of UserName.
这就是我要做的。变量/表名/列名不区分大小写在Oracle中，所以我会用user_name代替UserName。
CREATE TABLE users( UserName varchar2(20), OtherStuff VARCHAR2(20) );
Function update_and_get_user. Note that I return a ROWTYPEinstead of Pipelined Tables.
功能update_and_get_user。请注意，我返回的是一个ROWTYPE而不是流水线表。
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;
And this is how you would call it. You can not check a ROWTYPEto be NULL, but you can check usernamefor example.
这就是你会怎么称呼它。你不能检查 aROWTYPE是NULL，但你可以检查username例如。
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;
A solution using PIPED ROWSis below, but it doesn't work that way. You can not update tables inside a query.
使用的解决方案PIPED ROWS如下，但它不能那样工作。您不能在查询中更新表。
SELECT * FROM TABLE(update_and_get_user('user', 'stuff'))
ORA-14551: cannot perform a DML operation inside a query
Solution would look like that:
解决方案看起来像这样：
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;
