Как вернуть запись из существующей таблицы из функции Oracle PL/SQL?

Я знаю, что это кажется простой вещью, но я никогда не делал этого раньше.

Я хотел бы вернуть одну запись из существующей таблицы в результате выполнения функции Oracle PL / SQL. Я уже нашел несколько различных способов сделать это, но меня интересует Лучший способ сделать это (читай: я не очень доволен тем, что я нашел).

Суть того, что я делаю, заключается в следующем... У меня есть таблица под названием "users", и я хочу функцию "update_and_get_user", которая дает Имя пользователя (а также другая достоверная информация о данном пользователе) потенциально будет выполнять различные действия над таблицей "пользователи", а затем возвращать либо ноль, либо одну строку/запись из указанной таблицы.

Это основная схема кода в моей голове на данный момент (читай: нет идеи, если синтаксис даже близок к правильному): 

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;

Я видел примеры, когда возвращается запись или таблица, тип записи или таблицы был создан / объявлен заранее, но похоже, что если таблица имеет уже будучи определенным, я должен быть в состоянии использовать это, и, таким образом, не нужно беспокоиться о синхронизации кода объявления типа, если когда-либо будут внесены изменения в таблицу.

Я открыт для всех потенциальных решений и комментариев, но я действительно действительно Хочу сохранить это в одной функции PL/SQL (в отличие от кода на каком-то другом языке / фреймворке, который взаимодействует с базой данных несколько раз, заканчивая некоторой формой "SELECT * FROM users WHERE username=blah") как система, вызывающая базу данных. функция и сама база данных могут быть разными городами. За пределами этого предела я готов изменить свое мышление.

1 7
plsql

1 ответ:

Это как я бы сделал это. Переменные / имена таблиц/имена столбцов не зависят от регистра в Oracle, поэтому я бы использовал user_name вместо UserName.

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

Функция 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;
И вот как бы вы это назвали. Вы не можете проверить ROWTYPE, чтобы быть 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;

Решение с использованием 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;
13