DROP TABLE mvcBoard CASCADE CONSTRAINTS;
CREATE TABLE mvcBoard(
  no        NUMBER,
  name      VARCHAR2(34)      CONSTRAINT mb_name_nn   NOT NULL,
  subject   VARCHAR2(1000)    CONSTRAINT mb_sub_nn    NOT NULL,
  content   CLOB              CONSTRAINT mb_cont_nn   NOT NULL,
  pwd        VARCHAR2(10)     CONSTRAINT mb_pwd_nn    NOT NULL,
  regdate    DATE             DEFAULT SYSDATE,
  hit         NUMBER          DEFAULT 0,
  CONSTRAINT mb_no_pk PRIMARY KEY(no)
);

INSERT INTO mvcBoard(no,name,subject,content,pwd)
    VALUES((SELECT NVL(MAX(no)+1,1) FROM mvcBoard),'정도전','MVC 구조 게시판',
    'DB:MyBatis, Web:MVC를 이용한 게시판이다.','1234');
INSERT INTO mvcBoard(no,name,subject,content,pwd)
    VALUES((SELECT NVL(MAX(no)+1,1) FROM mvcBoard),'정도전','MVC 구조 게시판',
    'DB:MyBatis, Web:MVC를 이용한 게시판이다.','1234');
INSERT INTO mvcBoard(no,name,subject,content,pwd)
    VALUES((SELECT NVL(MAX(no)+1,1) FROM mvcBoard),'정도전','MVC 구조 게시판',
    'DB:MyBatis, Web:MVC를 이용한 게시판이다.','1234');
INSERT INTO mvcBoard(no,name,subject,content,pwd)
    VALUES((SELECT NVL(MAX(no)+1,1) FROM mvcBoard),'정도전','MVC 구조 게시판',
    'DB:MyBatis, Web:MVC를 이용한 게시판이다.','1234');
INSERT INTO mvcBoard(no,name,subject,content,pwd)
    VALUES((SELECT NVL(MAX(no)+1,1) FROM mvcBoard),'정도전','MVC 구조 게시판',
    'DB:MyBatis, Web:MVC를 이용한 게시판이다.','1234');
INSERT INTO mvcBoard(no,name,subject,content,pwd)
    VALUES((SELECT NVL(MAX(no)+1,1) FROM mvcBoard),'정도전','MVC 구조 게시판',
    'DB:MyBatis, Web:MVC를 이용한 게시판이다.','1234');
COMMIT;
SELECT * FROM mvcBoard;

CREATE OR REPLACE PROCEDURE boardListData(
    pStart  mvcBoard.no%TYPE,
    pEnd    mvcBoard.no%TYPE,
    pResult OUT SYS_REFCURSOR
)
IS
    BEGIN
        OPEN pResult FOR
            SELECT no,subject,name,regdate,hit,num
            FROM (SELECT no,subject,name,regdate,hit,rownum as num
            FROM (SELECT no,subject,name,regdate,hit
            FROM mvcBoard ORDER BY no DESC))
            WHERE num BETWEEN pStart AND pEnd;
    END;
/

CREATE OR REPLACE PROCEDURE boardTotalPage(
    pNumber   OUT   mvcBoard.no%TYPE
)
IS
BEGIN
    SELECT CEIL(COUNT(*)/10) INTO pNumber
    FROM mvcBoard;
END;
/

--내용보기
CREATE OR REPLACE PROCEDURE boardContentData(
    pNo     mvcBoard.no%TYPE,
    pResult OUT   SYS_REFCURSOR
)
IS
BEGIN
   UPDATE mvcBoard SET
   hit=hit+1
   WHERE no=pNo;
   COMMIT;

   OPEN pResult FOR
    SELECT no,name,subject,content,regdate,hit
    FROM mvcBoard
    WHERE no=pNo;
END;
/

CREATE OR REPLACE PROCEDURE boardInsert(
    pName     mvcBoard.name%TYPE,
    pSub      mvcBoard.subject%TYPE,
    pCont      mvcBoard.content%TYPE,
    pPwd      mvcBoard.pwd%TYPE
)
IS
BEGIN
    INSERT INTO mvcBoard VALUES(
        (SELECT NVL(MAX(no)+1,1) FROM mvcBoard),
        pName,pSub,pCont,pPwd,SYSDATE,0
        );
    COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE boardDelete(
    pNo     mvcBoard.no%TYPE,
    pPwd    mvcBoard.pwd%TYPE,
    pResult OUT     mvcBoard.name%TYPE
)
IS
    db_pwd  mvcBoard.pwd%TYPE;
BEGIN
    SELECT pwd INTO db_pwd
    FROM mvcBoard
    WHERE no=pNo;

    IF(pPwd=db_pwd) THEN
        pResult:='true';
        DELETE FROM mvcBoard
        WHERE no=pNo;
    ELSE
        pResult:='false';
    END IF;
    COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE boardUpdateData(
    pNo     mvcBoard.no%TYPE,
    pResult OUT     SYS_REFCURSOR
)
IS
BEGIN
    OPEN pResult FOR
        SELECT no,name,subject,content
        FROM mvcBoard
        WHERE no=pNo;
END;
/

CREATE OR REPLACE PROCEDURE boardUpdate(
    pNo     mvcBoard.no%TYPE,
    pName   mvcBoard.name%TYPE,
    pSub    mvcBoard.subject%TYPE,
    pCont   mvcBoard.content%TYPE,
    pPwd    mvcBoard.pwd%TYPE,
    pResult OUT  VARCHAR2
)
IS
    db_pwd  mvcBoard.pwd%TYPE;
BEGIN
    SELECT pwd INTO db_pwd
    FROM mvcBoard
    WHERE no=pNo;

    IF(pPwd=db_pwd) THEN
        pResult:='true';
        UPDATE mvcBoard SET
        name=pName,subject=pSub,content=pCont
        WHERE no=pNo;
        COMMIT;
    ELSE
        pResult:='false';
    END IF;
END;
/