-
질문 & 답변
카테고리
-
세부 분야
웹 개발
-
해결 여부
미해결
11강 프로시저 질문있습니다!!
20.07.14 15:44 작성 조회수 164
1
sql developer에서
패키지 만드는 중
WEB_MAIN------------------------
create or replace PACKAGE web_main AS
PROCEDURE selectLogin
(
In_userId IN VARCHAR2,
ref_cursor OUT SYS_REFCURSOR
);
PROCEDURE insertBorder
(
In_userId IN VARCHAR2,
In_userIp IN VARCHAR2,
In_title IN VARCHAR2,
In_mytextarea IN VARCHAR2
);
PROCEDURE selectBorder
(
ref_cursor OUT SYS_REFCURSOR
);
PROCEDURE selectBorderView
(
In_borderId IN NUMBER,
ref_cursor OUT SYS_REFCURSOR
);
PROCEDURE insertBorderReply
(
In_originalId IN NUMBER,
In_userId IN VARCHAR2,
In_userIp IN VARCHAR2,
In_title IN VARCHAR2,
In_mytextarea IN VARCHAR2
);
END web_main;
----------------------------------------------------
위와 같이 작성되었고
WEB_MAIN Body는
create or replace PACKAGE BODY WEB_MAIN AS
PROCEDURE selectLogin
(
In_userId IN VARCHAR2,
ref_cursor OUT SYS_REFCURSOR
) IS
BEGIN
OPEN ref_cursor FOR
SELECT
USER_ID AS USER_ID
FROM EGOV_USER
where USER_ID=In_userId;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('오류발생');
DBMS_OUTPUT.PUT_LINE('에러내용:'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('발생위치:'||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END selectLogin;
PROCEDURE insertBorder
(
In_userId IN VARCHAR2,
In_userIp IN VARCHAR2,
In_title IN VARCHAR2,
In_mytextarea IN VARCHAR2
) IS
BEGIN
INSERT INTO EGOV_BBS
(
borderid,
bordertype,
group_num,
parentid,
group_order,
group_tab,
userid,
nickname,
writerip,
editid,
editip,
title,
bordertext,
seecount,
filename,
filetype,
fileurl,
replycount
)
VALUES
(
borderid_seq.nextval,
'0',
borderid_seq.currval,
0,
0,
0,
In_userId,
In_userId,
In_userIp,
'',
'',
In_title,
In_mytextarea,
0,
'',
'',
'',
0
);
COMMIT;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('오류발생');
DBMS_OUTPUT.PUT_LINE('에러내용:'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('발생위치:'||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
end insertBorder;
PROCEDURE selectBorder
(
ref_cursor OUT SYS_REFCURSOR
) IS
BEGIN
OPEN ref_cursor FOR
SELECT
borderid,
group_tab,
nickname,
writeday,
title,
seecount
FROM EGOV_BBS
ORDER BY GROUP_NUM DESC,GROUP_ORDER ASC;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('오류발생');
DBMS_OUTPUT.PUT_LINE('에러내용:'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('발생위치:'||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END selectBorder;
PROCEDURE selectBorderView
(
In_borderId IN NUMBER,
ref_cursor OUT SYS_REFCURSOR
) IS
BEGIN
OPEN ref_cursor FOR
SELECT
borderid,
nickname,
title,
bordertext
FROM EGOV_BBS
WHERE BORDERID=In_borderId;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('오류발생');
DBMS_OUTPUT.PUT_LINE('에러내용:'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('발생위치:'||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END selectBorderView;
PROCEDURE insertBorderReply
(
In_originalId IN VARCHAR2,
In_userId IN VARCHAR2,
In_userIp IN VARCHAR2,
In_title IN VARCHAR2,
In_mytextarea IN VARCHAR2
) IS
lo_group_num NUMBER(10,0);
lo_group_order NUMBER(10,0);
lo_group_tab NUMBER(10,0);
BEGIN
/*답글달고자하는 글의 데이터를 로컬변수에 저장.*/
SELECT
group_num,
group_order,
group_tab
INTO
lo_group_num,
lo_group_order,
lo_group_tab
FROM EGOV_BBS
WHERE borderid = In_originalId;
/*기존글 형식을 맞추기위해 업데이트*/
UPDATE EGOV_BBS
set group_order = group_order + 1
WHERE group_num = lo_group_num
AND group_order > lo_group_order;
IF(lo_group_order = 0)
THEN
SELECT
MAX(group_order)
INTO
lo_group_order
FROM EGOV_BBS
WHERE group_num = lo_group_num;
END IF;
/*답글달고자하는 글의 데이터를 로컬변수에 저장.*/
INSERT INTO EGOV_BBS
(
borderid,
bordertype,
group_num,
parentid,
group_order,
group_tab,
userid,
nickname,
writerip,
editid,
editip,
title,
bordertext,
seecount,
filename,
filetype,
fileurl,
replycount
)
VALUES
(
borderid_seq.nextval,
'0',
lo_group_num,
In_originalId,
lo_group_order+1,
lo_group_tab+1,
In_userId,
In_userId,
In_userIp,
'',
'',
In_title,
In_mytextarea,
0,
'',
'',
'',
0
);
COMMIT;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('오류발생');
DBMS_OUTPUT.PUT_LINE('에러내용:'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('발생위치:'||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END insertBorderReply;
END WEB_MAIN;
---------------------------------위와 같이 작성되었는데,
답변을 작성해보세요.
0
개발자Park
지식공유자2020.07.14
안녕하세요 답변입니다.
선언쪽:
PROCEDURE insertBorderReply(
In_originalId IN NUMBER,
본문쪽:
PROCEDURE insertBorderReply(
In_originalId IN VARCHAR2,
In_originalId의 타입이 일치하지 않아서 생기는 오류입니다.
아마 해당 웹페이지도 프로시저 오류로 동작을 안할 것으로 보입니다.
In_originalId의 타입을 하나로 통일시켜주시면 해결 될 것 같습니다.
감사합니다.
답변 1