Проблемы с Oracle PLSQL при создании процедуры

avatar
Beefstu
8 августа 2021 в 21:17
94
1
1

Я пытаюсь обернуть некоторый SQL в процедуру PLSQL, чтобы пользователь мог передавать параметры вместо того, чтобы вручную редактировать предложение WHERE, что дало бы им возможность сломать рабочий код. Код SQL, который я переношу, встроен в PROCEDURE, за исключением предложения INTO.

Я знаю, что в PLSQL для выбора строк должно быть предложение INTO. Оглядевшись, я увидел пример, который создает объект и тип таблицы. Что-то я не хотел делать и кажется мне слишком сложным. Если возможно, я хочу, чтобы все было локально для процедуры.

Я также открыт для использования BULK-сбора в таблице access_history, если это будет более эффективным методом.

Когда я пытаюсь создать описанную ниже процедуру, она не работает, и здесь я могу воспользоваться помощью и знаниями PLSQL, чтобы найти наилучшее направление для получения нужных данных.

Во-вторых, есть ли способ использовать значение DEFAULT для определения количества извлекаемых строк.

Если процедура вызывается следующим образом:

EXEC LAST_EMPLOYEE_HISTORY(1) означает получение последних 20 (ПО УМОЛЧАНИЮ) строк для employee_id=1, где 20 — значение по умолчанию.

Если процедура вызывается следующим образом:

EXEC LAST_EMPLOYEE_HISTORY(1, 50) означает получение последних 50 строк для employee_id=1.

Буду очень признателен за любую помощь и опыт в объяснении и помощи в решении моих проблем. Заранее спасибо всем ответившим.

Ниже мой тестовый СЛУЧАЙ.

    ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';


    CREATE OR REPLACE TYPE access_history_obj AS OBJECT(
    employee_id    NUMBER(6), 
    first_name       VARCHAR2(20),
     last_name         VARCHAR2(20),
     card_num         VARCHAR2(10),
     location_id        NUMBER(6),
    location_name  VARCHAR2(30),
    access_date      DATE
    );
 

    CREATE OR REPLACE TYPE access_history_table IS TABLE OF access_history_obj;

    Create table employees(
      employee_id NUMBER(6), 
      first_name VARCHAR2(20),
      last_name VARCHAR2(20),
     card_num VARCHAR2(10),
      work_days VARCHAR2(7)
   );

    INSERT INTO employees (
     employee_id,
     first_name, 
     last_name,
     card_num,
     work_days
    )
    WITH names AS   ( 
      SELECT 1, 'John',     'Doe',      'D564311','YYYYYNN' FROM dual UNION ALL
      SELECT 2, 'Justin',     'Case',      'C224311','YYYYYNN' FROM dual UNION ALL
    SELECT 3, 'Mike',     'Jones',      'J288811','YYYYYNN' FROM dual UNION ALL
     SELECT 4, 'Jane',     'Smith',      'S564661','YYYYYNN' FROM dual 
   ) SELECT * FROM names; 

  CREATE TABLE locations AS
      SELECT level AS location_id,
         'Door ' || level AS location_name,

      CASE round(dbms_random.value(1,3)) 
              WHEN 1 THEN 'A' 
              WHEN 2 THEN 'T' 
              WHEN 3 THEN 'T' 
           END AS location_type

      FROM   dual
      CONNECT BY level <= 5;
       
      create table access_history(
        seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
         employee_id NUMBER(6), 
         card_num varchar2(10),
         location_id number(4),
         access_date date,
         processed NUMBER(1) default 0
        );

   INSERT INTO       access_history(
      employee_id,
       card_num,
       location_id,
       access_date
     )
 WITH rws AS   ( 
      SELECT 1,'J11111',2,TO_DATE('2021/08/15 08:30:25', 'YYYY/MM/DD HH24:MI:SS') FROM dual UNION ALL
    SELECT 1,'J11111',3,TO_DATE('2021/08/15 18:30:35', 'YYYY/MM/DD HH24:MI:SS') FROM dual UNION ALL
   SELECT 2,'E11111',2,TO_DATE('2021/08/15 11:20:35', 'YYYY/MM/DD HH24:MI:SS') FROM dual) SELECT * FROM rws; 

            CREATE OR REPLACE PROCEDURE LAST_EMPLOYEE_HISTORY( 
    p_employee_id IN NUMBER,
     p_rws IN number)
     AS
    BEGIN
     with rws as (
         select e.employee_id, 
              e.first_name,
              e.last_name,
              e.card_num,
              l.location_id,
              l.location_name,
              a.access_date,
              row_number () over 
          (
           partition by e.employee_id
            order by a.access_date DESC
          ) rn  
         FROM  employees    e
        JOIN     access_history a ON a.employee_id = e.employee_id
 JOIN  locations     l ON l.location_id = a.location_id
         )

        select employee_id, 
                first_name,
                last_name,
                card_num,
                location_id,
                location_name,
               access_date INTO access_history_table
      from rws 
      where 
         employee_id = p_employee_id AND
         rn <= p_rws
         order by employee_id,  access_date desc;
      END;

    EXEC LAST_EMPLOYEE_HISTORY(1)
Источник
sticky bit
8 августа 2021 в 21:28
1

Вы можете рассмотреть возможность использования табличной функции вместо процедуры.

Beefstu
9 августа 2021 в 05:04
0

@sticky bit Я не хочу ВСТАВЛЯТЬ строки, мне просто нужно ВЫБРАТЬ последние N для employee_id. Я хочу, чтобы формат содержал значения, которые я указал в своем примере

sticky bit
9 августа 2021 в 08:56
1

Я в замешательстве... Какое отношение мое предложение табличной функции имеет к INSERTing?

Ответы (1)

avatar
MT0
8 августа 2021 в 21:39
3

Имейте курсор в качестве параметра OUT и используйте DEFAULT в подписи процедуры:

CREATE PROCEDURE LAST_EMPLOYEE_HISTORY( 
  i_employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE,
  i_rws         IN PLS_INTEGER DEFAULT 20,
  o_cursor      OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN o_cursor FOR
    SELECT e.employee_id, 
           e.first_name,
           e.last_name,
           e.card_num,
           l.location_id,
           l.location_name,
           a.access_date
    FROM   employees    e
           INNER JOIN access_history a
           ON a.employee_id = e.employee_id
           INNER JOIN locations l
           ON l.location_id = a.location_id
    WHERE  e.employee_id = i_employee_id
    ORDER BY access_date DESC
    FETCH FIRST i_rws ROWS ONLY;
END;
/

Затем в SQL/Plus или SQL Developer:

VARIABLE cur REFCURSOR;
EXECUTE LAST_EMPLOYEE_HISTORY(1, 50, :cur);
PRINT cur;

дб<>скрипка здесь

Примечание. В Oracle 12 можно использовать синтаксис FETCH FIRST n ROWS ONLY.

Beefstu
9 августа 2021 в 05:43
0

@MTO ваше решение работает отлично, но есть ли способ инкапсулировать весь код в процедуру вместо использования блока для их ВЫБОРА. Это для группы нетехнических людей, и чем меньше им нужно делать, тем лучше. Однострочная команда EXEC — это то, с чем они легко справятся. Возможно, инкапсулировать все в один пакет и называть это? Следующий шаг — тот же вывод, но с указанием location_id. Еще раз спасибо за ответ и опыт.

MT0
9 августа 2021 в 07:08
0

@Beefstu Я не уверен, что понимаю, о чем вы говорите, поскольку в моем ответе выше весь код находится внутри процедуры, и вы можете просто использовать EXEC для вызова процедуры (если используемый вами пользовательский интерфейс поддерживает это и вы определили переменную REFCURSOR, однако db<>fiddle не поддерживает это).

Beefstu
9 августа 2021 в 08:34
0

@MTO спасибо за объяснение. Я хотел инкапсулировать ВЕСЬ код в одну процедуру или, возможно, сделать анонимный блок GENERIC, чтобы его можно было использовать в другой процедуре для получения LAST_LOCATION_HISTORY (еще не закодированного) и отображения того же формата. Анонимный блок имеет вызов LAST_EMPLOYEE_HISTORY, так что это не делает его универсальным, если только для вызова любой процедуры не может быть использован какой-либо оператор CASE.

MT0
9 августа 2021 в 08:57
1

@Beefstu Я до сих пор не понимаю, как ЛЮБОЕ решение, которое вы придумаете, должно будет вызвать процедуру, и вы хотите вернуть набор результатов (или набор объектов), поэтому проблема, которая у вас возникла с этим решением будет та же проблема, что и с КАЖДЫМ другим решением. Если вы используете SQL/plus (или SQL Developer), просто используйте оператор VARIABLE, затем оператор EXEC, затем оператор PRINT. Если вы используете другой пользовательский интерфейс, посмотрите его документацию о том, как отображать курсор.

Beefstu
9 августа 2021 в 12:43
0

@MTO я пытаюсь сказать, что мне нужна оболочка для анонимного блока. Если передается «E», вызовите LAST_EMPLOYEE_HISTORY с параметрами. Если передается «L», вызовите LAST_LOCATION_HISTORY с параметрами. Первый параметр — число для employee_id или location_id, а второй — переопределение количества строк. Таким образом, я могу вызвать любую процедуру и сохранить остальную часть вашего кода, который кажется общим, в одном месте. Я думаю, все, что мне нужно сделать, это преобразовать блок в процедуру, чтобы передать параметры в правильную процедуру. Имеет ли это смысл?

MT0
9 августа 2021 в 17:59
0

@Beefstu Я не знаю сигнатуру для вашей процедуры LAST_LOCATION_HISTORY, но если у нее похожая сигнатура, вы можете обернуть ее в другую процедуру, чтобы вызывать любую из них на основе аргумента. Однако я бы не стал. Я бы просто дал людям два простых скрипта для копирования-редактирования-вставки; по одному на каждую процедуру.

Beefstu
9 августа 2021 в 19:15
0

@MTO подпись точно такая же, как процедура, которую вы создали для меня, за исключением того, что она будет искать location_id вместо employee_id. Конечные пользователи не являются техническими специалистами, поэтому я не хочу, чтобы они копировали и вставляли код, поскольку я уверен, что это приведет к проблемам. Поэтому я создаю оболочку из вашего блока, которая будет вызывать процедуру на основе переданного параметра, и все будет устарело!!! к правильной базовой процедуре. Таким образом, все, что пользователь должен отредактировать, — это значение, которое он хочет найти. Это кажется лучшим способом продолжить. Спасибо за терпение и профессионализм!!