如何从 Oracle PL/SQL 函数的现有表中返回记录?

2021-12-24 00:00:00 oracle plsql

我知道这似乎是一件基本的事情，但我以前从未这样做过.

I know it seems like a basic thing, but I've never done this before.

我想从现有表中返回一条记录作为 Oracle PL/SQL 函数的结果.我已经找到了几种不同的方法来做到这一点，但我对最好的方法很感兴趣(阅读:我对我发现的东西并不满意).

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 best way to do it (read: I'm not all that happy with what I've found).

我正在做的事情的要点是......我有一个名为users"的表，我想要一个函数update_and_get_user"，它给出了一个用户名(以及有关所述用户的其他可信信息)可能会执行'user' 表上的各种操作，然后从所述表中返回零或一行/记录.

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.

这是目前我脑海中代码的基本轮廓(阅读:不知道语法是否接近正确):

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.

我对所有潜在的解决方案和评论持开放态度，但我真的希望将其保留在单个 PL/SQL 函数中(而不是使用其他语言/框架的代码与数据库多次，以某种形式的 'SELECT * FROM users WHERE username=blah') 结束，因为调用该函数的系统和数据库本身可能是不同的城市.超出这个限制，我愿意改变我的想法.

I'm open to all potential solutions and commentary, but I do really want 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.

推荐答案

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

This is how I would do it. Variables/table-names/column-names are case-insensitive in Oracle, so I would use user_name instead of UserName.

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

函数update_and_get_user.请注意，我返回的是 ROWTYPE 而不是流水线表.

Function update_and_get_user. Note that I return a ROWTYPE instead of 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，但您可以检查 username 例如.

And this is how you would call it. You can not check a ROWTYPE to be NULL, but you can check username for example.

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 的解决方案如下，但它不能那样工作.您不能在查询中更新表.

A solution using PIPED ROWS is below, but it doesn't work that way. You can not update tables inside a query.

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;

相关文章