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

Question

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

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

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

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

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

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;

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

Я открыт для всех потенциальных решений и комментария, но я действительно хочу сохранить это на сингле МН функциональный / SQL функциональный (в противоположность коду на некотором другом языке / платформа, которая общается с базой данных многократно, заканчиваясь с некоторой формой 'ВЫБОРА * ОТ пользователей, ГДЕ username=blah'), поскольку система, вызывающая функцию и саму базу данных, может быть различными городами. За пределами того предела я открыт для изменения моих взглядов.

7

oracle plsql

задан Paul Hooper 26 February 2010 в 19:33

1 ответ

Другие вопросы по тегам:

oracle plsql

Похожие вопросы:

score 13 · Accepted Answer

Вот как я бы это сделал. Переменные / имена-таблиц / имена-столбцов в 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 , но можете, например, проверить имя пользователя .

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;

Решение, использующее ТРУБОПРОВОДНЫЕ СТРОКИ , приведено ниже, но это не работает. Вы не можете обновлять таблицы внутри запроса.

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;