Как выполнить процедуру оракула с параметром курсора out в анонимном блоке PL/SQL?

Я изучаю PL/SQL, используя базу данных Oracle XE HR.

Я создал следующую хранимую процедуру:

CREATE OR REPLACE PROCEDURE get_employees( p_country_id IN CHAR
                                         , p_emp        OUT SYS_REFCURSOR) 
IS
BEGIN

  OPEN p_emp FOR
    SELECT e.first_name
          ,e.last_name
          ,e.department_id
          ,d.department_name
          ,l.city
          ,l.state_province
      FROM employees e
     INNER JOIN departments d
        ON e.department_id = d.department_id
     INNER JOIN locations l
        ON d.location_id = l.location_id
     WHERE l.country_id = p_country_id; 
END;

Я знаю, как выполнить его в графическом интерфейсе SQL Developer и увидеть результаты. Я также узнал от Justin Cave здесь и здесь, как его выполнить и увидеть результаты в стиле SQL*Plus следующим образом:

VARIABLE CE REFCURSOR;
EXEC GET_EMPLOYEES('US', :CE);
PRINT CE;

Я хотел бы выполнить хранимую процедуру в анонимном блоке PL/SQL и увидеть результаты в виде таблицы, но она не удалась.

Как и то, что предложил Джастин Кейв, следующее выполняется просто отлично, но результаты не отображаются:

DECLARE
  C_EMP SYS_REFCURSOR;
BEGIN
  GET_EMPLOYEES('US', C_EMP);
END;

Следующее не удастся:

DECLARE
  C_EMP SYS_REFCURSOR;
  L_REC C_EMP%ROWTYPE; --THIS LINE FAILS.
BEGIN
  GET_EMPLOYEES('US', C_EMP);
  -- LOOP AND FETCH GOES HERE.
END;

Сообщение об ошибке говорит:

PLS-00320: объявление типа этого выражения неполное или искаженное

Я не понимаю это Я делал это в нескольких других анонимных PL/SQL-блоках, и это работало отлично. Что не так с этой строкой здесь? Не могу понять.

Источник

2 ответа

Решение

Я думаю, что вы неправильно понимаете использование%ROWTYPE. Вам следует просто использовать% ROWTYPE, когда вы сохраняете все строки таблицы. Вместо использования% ROWTYPE создайте свой собственный тип (запись), который соответствует типу столбцов, которые вы выбираете. Попробуй это:

DECLARE
  C_EMP SYS_REFCURSOR;
  TYPE new_type IS RECORD(FIRST_NAME VARCHAR2(100), LAST_NAME VARCHAR2(200), DEPARTMENT_ID NUMBER, DEPARTMENT_NAME VARCHAR2(200), CITY VARCHAR2(200), STATE_PROVINCE VARCHAR2(200));
  L_REC new_type; --instead of using %ROWTYPE, use the declared type
BEGIN
  GET_EMPLOYEES('US', C_EMP);
  LOOP
 FETCH c_emp INTO l_rec;
 EXIT WHEN c_emp%NOTFOUND;

     dbms_output.put_line(l_rec.first_name||'_'||
                          l_rec.last_name||'_'||
                          l_rec.department_id||'_'||
                          l_rec.department_name||'_'||
                          l_rec.city||'_'|| 
                          l_rec.state_province);
 END LOOP;

CLOSE c_emp;
END;
Источник

Я уверен, что нет короткого ответа на этот вопрос.

Чтобы понять, в чем проблема, вы должны выяснить, что такое строго типизированные и слаб типизированные рекурсоры.

СУБД Oracle не имеет встроенных инструментов для помещения результатов рефкурсоров в сетку. Если вы собираетесь написать одну попытку пакета DBMS_SQL и динамический PL/SQL - вполне возможно написать программу, которая выдаст ожидаемый вами результат (т.е. поместит любой sys_refcursor в сетку).

Но если вы только начали изучать PL/SQL, пожалуйста, не тратьте свое время сейчас - во-первых, приобретите некоторый опыт, и вы увидите, как это сделать. И пока этого не произошло, используйте взломщик SQL Developer.

Источник
Другие вопросы по тегам