by 손님 [2004.10.21 11:21:28]
asp+oracle+crystal report로 개발하고 있습니다.
레포트에 데이타를 뿌려줄때 sp에서 생성된 temp table의 데이타를가져와서 뿌려주려고 하는데..
구문을 잘 몰라서...
오라클은 첨 써보는것이라...
CREATE OR REPLACE PACKAGE BODY pg_EMRBULD_REPORTS_temp is
PROCEDURE SP_EMRBULD_temp (
EMRBULD_Record_temp OUT bCur ,
p_CLASS1 in varchar2 /*대분류*/
,p_CLASS2 in varchar2 /*중분류*/
,p_ESTB_NAME in varchar2 /*시설명*/
)
IS
BEGIN
OPEN EMRBULD_Record_temp FOR
Create global temporary table EMRBULD_temp
(
p_ESTB_CODE in varchar2
,p_ESTB_NAME in varchar2
) ;
on commit delete rows;
INSERT INTO EMRBULD_temp
--> 이부분이 제가 추가하려고 하는것인데...
이렇게 쓰면 안되는것인지...
고수님들 좀 알려주세요...
이부분 빼고 sp실행 시키면 생성 잘 되구요...
SELECT A.ESTB_CODE
,C.ESTB_NAME
FROM EMRBULD A
,EMRBASE C
WHERE A.ESTB_CODE = C.ESTB_CODE(+)
AND SUBSTR(A.ESTB_CODE,8,2) like p_CLASS1 + '%'
AND SUBSTR(A.ESTB_CODE,10,2) like p_CLASS2 + '%'
AND C.ESTB_NAME LIKE p_ESTB_NAME + '%'
;
END SP_EMRBULD_temp ;
END pg_EMRBULD_REPORTS_temp ;
/