데이터베이스 질문과 답변
아래와 같은 쿼리를 실행 시키면 에러가 발생합니다. (바로 아래 내용)
제가 쿼리 작성을 잘못 한건가요???
ORA-06550: line 56, column 12:
PLS-00103: Encountered the symbol "SQL_CUR" when expecting one of the following:
:= . ( @ % ;
ORA-06550: line 86, column 18:
PLS-00103: Encountered the symbol "PRIOR" when expecting one of the following:
. ( ) * @ % & = - + < / > at in is mod remainder not rem with
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between gr
ORA-06550: line 87, column 16:
PLS-00103: Encountered the symbol "UNION" when expecting one of the following:
. ( ) , * @ % & - + / at mod remainder rem <an exponent (**)>
and or using || multiset
The symbol ")" was substituted for "UNION" to continue.
ORA-06550: line 93, column 18:
PLS-00103: Encountered the symbol "PRIOR" when expecting one of the following:
. ( ) * @ % & = - + < / > at in is mod remainder not rem with
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between gr
ORA-06550: line 104, column 76:
PLS-00103: Encountered the symbol "T2" when expecting one of the of the hen expecting one of the of the
DECLARE
V_ORGAN_CODE VARCHAR2(100) := 100105706;
V_GUBUN_NAME VARCHAR2(100);
V_FULL_NAME VARCHAR2(100);
V_TYPE_NAME VARCHAR2(100);
V_ITEM_NAME VARCHAR2(100);
V_GRADE_NAME VARCHAR2(100);
V_B_DATE VARCHAR2(100);
V_B_NUM VARCHAR2(100);
V_CHANGE_CONTENT VARCHAR2(1000);
V_MEMO VARCHAR2(100);
V_JUNGWON VARCHAR2(100);
BEGIN
FOR S1 IN
(
SELECT DISTINCT
O_ORGAN_CODE,
CASE
WHEN TO_CHAR(O_SIHANG_DATE + LV -1) < 2000 THEN TO_CHAR(O_SIHANG_DATE + LV -1)||'1231'
WHEN TO_CHAR(O_SIHANG_DATE + LV -1) >= 2000 THEN TO_CHAR(O_SIHANG_DATE + LV -1)||'0101'
END STD_YYYY
FROM
(SELECT
O_ORGAN_CODE, O_GUBUN, substr(O_SIHANG_DATE,1,4) O_SIHANG_DATE,
substr(O_CLOSE_DATE,1,4) O_CLOSE_DATE, O_BEFORE_NAME, O_BEFORE_NAME2,
SUBSTR (O_FULL_NAME, 5, 100) AS ORGAN_NAME, O_JUSO, O_BEFORE_ORGANCODE,
O_BEFORE_ORGANCODE2, CONNECT_BY_ISCYCLE CBI
FROM J_ORGAN
START WITH
O_ORGAN_CODE LIKE V_ORGAN_CODE||'%'
CONNECT BY NOCYCLE
PRIOR O_BEFORE_ORGANCODE = O_ORGAN_CODE
UNION
SELECT
O_ORGAN_CODE, O_GUBUN, substr(O_SIHANG_DATE,1,4) O_SIHANG_DATE,
substr(O_CLOSE_DATE,1,4) O_CLOSE_DATE, O_BEFORE_NAME, O_BEFORE_NAME2,
SUBSTR (O_FULL_NAME, 5, 100) AS ORGAN_NAME, O_JUSO, O_BEFORE_ORGANCODE,
O_BEFORE_ORGANCODE2, CONNECT_BY_ISCYCLE
FROM J_ORGAN
START WITH
O_ORGAN_CODE LIKE V_ORGAN_CODE||'%'
CONNECT BY NOCYCLE
PRIOR O_ORGAN_CODE = O_BEFORE_ORGANCODE) A,
(SELECT LEVEL LV
FROM DUAL
CONNECT BY
LEVEL <= 99)
WHERE
LV <= O_CLOSE_DATE - O_SIHANG_DATE + 1
)
LOOP
CURSOR SQL_CUR IS
SELECT
MIN(DECODE(NO,1,O_FULL_NAME ||' 총계',2,DECODE(B1.B_GUBUN, 0, '111', 1, '222'))) B_GUBUN_NAME,
MIN(DECODE(NO,1,'',2,O_FULL_NAME)) O_FULL_NAME,
MIN(DECODE(NO,1,'',2,T_TYPE_NAME)) T_TYPE_NAME,
MIN(DECODE(NO,1,'',2,I_ITEM_NAME)) I_ITEM_NAME,
MIN(DECODE(NO,1,'',2,DECODE(G_GRADE_NAME,'정무','',G_GRADE_NAME))) G_GRADE_NAME,
MIN(DECODE(NO,1,'',2,B_DATE)) B_DATE, SUM(B_NUM) B_NUM,
MIN(DECODE(NO,1,'',2,C_CHANGE_CONTENT)) C_CHANGE_CONTENT,
MIN(DECODE(NO,1,'',2,B_MEMO)) MEMO,
MIN(DECODE(NO,1,'',2,B_MANAGE_JUNGWON)) M_JUNGWON
FROM
(SELECT
B_SEQ, T1.B_GUBUN B_GUBUN, T1.B_ORGAN_CODE B_ORGAN_CODE,
T1.B_TYPE_CODE B_TYPE_CODE, T1.B_ITEM_CODE B_ITEM_CODE,
T1.B_GRADE_CODE B_GRADE_CODE, T1.B_DATE B_DATE, T1.B_NUM B_NUM,
T1.B_CHANGE_CODE B_CHANGE_CODE, T1.B_MEMO B_MEMO, T1.B_MANAGE_JUNGWON
FROM
J_BASE T1,
(SELECT
B_GUBUN, B_ORGAN_CODE, B_TYPE_CODE, B_ITEM_CODE, B_GRADE_CODE,
MAX(B_DATE) B_DATE
FROM J_BASE
WHERE
B_ORGAN_CODE IN
(SELECT O_ORGAN_CODE
FROM J_ORGAN
START WITH
O_ORGAN_CODE LIKE S1.O_ORGAN_CODE
CONNECT BY NOCYCLE
PRIOR O_BEFORE_ORGANCODE = O_ORGAN_CODE
UNION
SELECT O_ORGAN_CODE
FROM J_ORGAN
START WITH
O_ORGAN_CODE LIKE S1.O_ORGAN_CODE
CONNECT BY NOCYCLE
PRIOR O_ORGAN_CODE = O_BEFORE_ORGANCODE) AND
B_GUBUN LIKE '%'
AND
B_TYPE_CODE LIKE '%'
AND
B_GRADE_CODE LIKE '%'
AND
B_ITEM_CODE LIKE '%'
AND
B_DATE <= S1.STD_YYYY
GROUP BY
B_GUBUN, B_ORGAN_CODE, B_TYPE_CODE, B_ITEM_CODE, B_GRADE_CODE) T2
WHERE
T1.B_NUM > 0 AND
T1.B_GUBUN = T2.B_GUBUN AND
T1.B_ORGAN_CODE = T2.B_ORGAN_CODE AND
T1.B_TYPE_CODE = T2.B_TYPE_CODE AND
T1.B_ITEM_CODE = T2.B_ITEM_CODE AND
T1.B_GRADE_CODE = T2.B_GRADE_CODE AND
T1.B_DATE = T2.B_DATE) B1, J_ORGAN, J_TYPE, J_ITEM, J_GRADE, J_CHANGE,
J_COPY
WHERE
B1.B_ORGAN_CODE = O_ORGAN_CODE AND
B1.B_TYPE_CODE = T_TYPE_CODE AND
B1.B_ITEM_CODE = I_ITEM_CODE AND
B1.B_GRADE_CODE = G_GRADE_CODE AND
B1.B_CHANGE_CODE = C_CHANGE_CODE(+) AND
NO BETWEEN 1 AND 2
GROUP BY
DECODE(NO,1,' ',2,B1.B_GUBUN||B1.B_ORGAN_CODE||B1.B_TYPE_CODE||B1.B_GRADE_CODE||B1.B_ITEM_CODE),
O_FULL_NAME;
BEGIN
FOR V_CUR IN SQL_CUR LOOP
DBMS_OUTPUT.PUT_LINE(V_CUR.B_GUBUN_NAME||' '||V_CUR.O_FULL_NAME||' '||V_CUR.T_TYPE_NAME||' '||V_CUR.I_ITEM_NAME||' '||V_CUR.G_GRADE_NAME||' '||V_CUR.B_DATE||' '||V_CUR.B_NUM||' '||V_CUR.C_CHANGE_CONTENT||' '||V_CUR.MEMO||' '||V_CUR.M_JUNGWON||' '||'');
END LOOP;
END;
END LOOP;
END;
커서 선언은 Declare 에서 해야 합니다.
DECLARE -- 요거 추가하세요
CURSOR SQL_CUR IS -- 요놈 앞에
감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.