Oracle 저장 프로시저에서 결과 세트 가져오기
저장 프로시저를 SQL Server에서 Oracle로 변환하는 작업을 하고 있습니다.이 저장 프로시저는 직접적인 결과 세트를 제공합니다.즉, Management Studio에 저장된 프로시저를 호출하면 결과 세트를 직접 얻을 수 있습니다.
Oracle로 변환하면 Oracle에서 결과 세트가 표시되지 않는 문제를 해결할 수 있습니다.
인터넷에서 검색한 결과 스토어드 프로시저가 REF CURSOR로 되어 있는 것을 알 수 있었습니다만, 그 과정에서 결과 세트를 얻기 위해 작은 코드를 작성하기 위해 문제를 안고 걷고 있습니다.
유사 코드:
저장 프로시저를 호출하여 커서를 가져옵니다. 결과 세트가 나타나도록 해당 커서로 작업을 수행합니다.
아이디어 있어요?
SQL Plus의 경우:
SQL> create procedure myproc (prc out sys_refcursor)
2 is
3 begin
4 open prc for select * from emp;
5 end;
6 /
Procedure created.
SQL> var rc refcursor
SQL> execute myproc(:rc)
PL/SQL procedure successfully completed.
SQL> print rc
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-1981 4999 10
7698 BLAKE MANAGER 7839 01-MAY-1981 2849 30
7782 CLARKE MANAGER 7839 09-JUN-1981 2449 10
7566 JONES MANAGER 7839 02-APR-1981 2974 20
7788 SCOTT ANALYST 7566 09-DEC-1982 2999 20
7902 FORD ANALYST 7566 03-DEC-1981 2999 20
7369 SMITHY CLERK 7902 17-DEC-1980 9988 11 20
7499 ALLEN SALESMAN 7698 20-FEB-1981 1599 3009 30
7521 WARDS SALESMAN 7698 22-FEB-1981 1249 551 30
7654 MARTIN SALESMAN 7698 28-SEP-1981 1249 1400 30
7844 TURNER SALESMAN 7698 08-SEP-1981 1499 0 30
7876 ADAMS CLERK 7788 12-JAN-1983 1099 20
7900 JAMES CLERK 7698 03-DEC-1981 949 30
7934 MILLER CLERK 7782 23-JAN-1982 1299 10
6668 Umberto CLERK 7566 11-JUN-2009 19999 0 10
9567 ALLBRIGHT ANALYST 7788 02-JUN-2009 76999 24 10
Oracle은 SQL 서버가 아닙니다.SQL Developer에서 다음을 시도합니다.
variable rc refcursor;
exec testproc(:rc2);
print rc2
제 해결책은 파이프라인 함수를 만드는 것이었습니다.장점은 쿼리가 한 줄일 수 있다는 것입니다.
select * from table(yourfunction(param1, param2));- 결과를 다른 테이블에 결합하거나 원하는 대로 필터링하거나 정렬할 수 있습니다.
- 결과는 일반 쿼리 결과로 나타나므로 쉽게 조작할 수 있습니다.
함수를 정의하려면 다음과 같은 작업을 수행해야 합니다.
-- Declare the record columns
TYPE your_record IS RECORD(
my_col1 VARCHAR2(50),
my_col2 varchar2(4000)
);
TYPE your_results IS TABLE OF your_record;
-- Declare the function
function yourfunction(a_Param1 varchar2, a_Param2 varchar2)
return your_results pipelined is
rt your_results;
begin
-- Your query to load the table type
select s.col1,s.col2
bulk collect into rt
from your_table s
where lower(s.col1) like lower('%'||a_Param1||'%');
-- Stuff the results into the pipeline..
if rt.count > 0 then
for i in rt.FIRST .. rt.LAST loop
pipe row (rt(i));
end loop;
end if;
-- Add more results as you please....
return;
end find;
그리고 위에서 설명한 바와 같이 결과를 보려면 다음과 같이 하십시오.
select * from table(yourfunction(param1, param2)) t order by t.my_col1;
안녕, 나는 이것이 오래전에 요청되었다는 것을 알지만 나는 방금 이것을 알아냈고 그것이 다른 누군가에게 도움이 될지도 몰라.이것이 당신이 찾고 있는 것인지 아닌지는 모르겠지만, 이것이 바로 저장된 프로시저를 호출하고 SQL Developer를 사용하여 출력을 표시하는 방법입니다.
SQL Developer에서 Proc를 표시할 때 마우스 오른쪽 버튼을 클릭하여 'Run'을 선택하거나 Ctrl+F11을 선택하여 Run PL/SQL 창을 띄웁니다.그러면 수정해야 하는 입력 및 출력 매개 변수가 포함된 템플릿이 생성됩니다.proc가 sys_refcursor를 반환합니다.저에게 어려운 부분은 proc가 반환하는 select stmt / sys_refcursor와 정확히 동일한 행 유형을 선언하는 것이었습니다.
DECLARE
P_CAE_SEC_ID_N NUMBER;
P_FM_SEC_CODE_C VARCHAR2(200);
P_PAGE_INDEX NUMBER;
P_PAGE_SIZE NUMBER;
v_Return sys_refcursor;
type t_row is record (CAE_SEC_ID NUMBER,FM_SEC_CODE VARCHAR2(7),rownum number, v_total_count number);
v_rec t_row;
BEGIN
P_CAE_SEC_ID_N := NULL;
P_FM_SEC_CODE_C := NULL;
P_PAGE_INDEX := 0;
P_PAGE_SIZE := 25;
CAE_FOF_SECURITY_PKG.GET_LIST_FOF_SECURITY(
P_CAE_SEC_ID_N => P_CAE_SEC_ID_N,
P_FM_SEC_CODE_C => P_FM_SEC_CODE_C,
P_PAGE_INDEX => P_PAGE_INDEX,
P_PAGE_SIZE => P_PAGE_SIZE,
P_FOF_SEC_REFCUR => v_Return
);
-- Modify the code to output the variable
-- DBMS_OUTPUT.PUT_LINE('P_FOF_SEC_REFCUR = ');
loop
fetch v_Return into v_rec;
exit when v_Return%notfound;
DBMS_OUTPUT.PUT_LINE('sec_id = ' || v_rec.CAE_SEC_ID || 'sec code = ' ||v_rec.FM_SEC_CODE);
end loop;
END;
SQL Plus의 경우:
SQL> var r refcursor
SQL> set autoprint on
SQL> exec :r := function_returning_refcursor();
마지막 행을 절차/함수에 대한 호출로 바꾸면 레퍼서의 내용이 표시됩니다.
참고로 Oracle 12c에서는 다음을 수행할 수 있습니다.
CREATE OR REPLACE PROCEDURE testproc(n number)
AS
cur SYS_REFCURSOR;
BEGIN
OPEN cur FOR SELECT object_id,object_name from all_objects where rownum < n;
DBMS_SQL.RETURN_RESULT(cur);
END;
/
EXEC testproc(3);
OBJECT_ID OBJECT_NAME
---------- ------------
100 ORA$BASE
116 DUAL
이는 다른 데이터베이스와 더 가까워지고 마이그레이션이 쉬워질 것으로 예상되었습니다.그러나 SQL 개발자가 일반 SELECT로 올바르게 표시하지 않는 등 완벽한 기능은 아닙니다.
파이프라인 기능의 출력을 선호하지만 코드를 작성하려면 더 많은 보일러 플레이트가 필요합니다.
상세정보 : https://oracle-base.com/articles/12c/implicit-statement-results-12cr1
CREATE OR REPLACE PROCEDURE SP_Invoices(p_nameClient IN CHAR)
AS
BEGIN
FOR c_invoice IN
(
SELECT CodeInvoice, NameClient FROM Invoice
WHERE NameClient = p_nameClient
)
LOOP
dbms_output.put_line('Code Invoice: ' || c_invoice.CodeInvoice);
dbms_output.put_line('Name Client : ' || c_invoice.NameClient );
END LOOP;
END;
SQL Developer에서 실행 중:
BEGIN
SP_Invoices('Perico de los palotes');
END;
-- Or:
EXEC SP_Invoices('Perico de los palotes');
출력:
> Code Invoice: 1
> Name Client : Perico de los palotes
> Code Invoice: 2
> Name Client : Perico de los palotes
언급URL : https://stackoverflow.com/questions/1170548/get-resultset-from-oracle-stored-procedure
'programing' 카테고리의 다른 글
| UI-Router를 사용하여 사용자가 부모 상태로 이행할 때 사용자를 자녀 상태로 유도 (0) | 2023.03.07 |
|---|---|
| jQuery 또는 플레인 JavaScript의 react js에서 변경 또는 입력 이벤트를 트리거하는 가장 좋은 방법은 무엇입니까? (0) | 2023.03.07 |
| AngularJng-옵션으로 범위 작성 (0) | 2023.03.07 |
| ng-repeat과 ng-animate를 사용하여 목록을 정렬하려면 어떻게 해야 합니까? (0) | 2023.03.07 |
| java9에서 모듈 읽기 패키지 오류를 해결하는 방법 (0) | 2023.03.07 |