0528 PL/SQL, 시퀀스, 프로시저

*NULL값 처리 - NVL2

NVL2(속성, 값1, 값2) : 값이 NULL인 경우와 아닌경우 반환할 값을 지정할 수 있다.

*조건부 논리 표

- DECODE함수

직책이 manager인 사람은 10% 인상, salesman인 사람은 5%, analyst인 사람은 그대로, 나머지는 3인상하여 사번, 이름 ,직책, 급여 출력.

SELECT EMPNO, ENAME, JOB, SAL, DECODE(JOB, 'MANAGER',SAL*1.1, 'SALESMAN', 
SAL*1.05, 'ANAYST', SAL, SAL*1.03) AS UPSAL
FROM EMP;

- CASE 문 : 늑정 속성의 값에 따라 어떤 데이터를 반환할지 결정할 때 사용. 각 조건에 사용하는 데이터가 서로 상관 없어도 사용이 가능하다. decode함수는 case문으로 변경이 가능하지만 역은 불가능.

*ROWNUM

 : 내장 함수는 아니지만 자주 사용되는 문법. 불러온 순서대로 1,2,3,... 숫자를 붙여준다. 실제 테이블에 추가되는 것은 아니고 편의상 사용.

SELECT ROWNUM "순번", CUSTID, NAME, PHONE
FROM CUSTOMER
WHERE ROWNUM <=2;

SELECT* FROM CUSTOMER WHERE ROWNUM BETWEEN 1 AND 3;
--이런식으도 가능.

 

*SEQUENCE

 : 특정 규칙에 맞는 연속 숫자를 생성하는 객체. 웹 서비스의 연속하는 숫자로 이루어진 새로운 게시판 번호나 상품 주문 번호 등을 생성.

CREATE TABLE DEPT_SEQUENCE
    AS SELECT * FROM DEPT
    WHERE 1<>1;
SELECT * FROM DEPT_SEQUENCE; --시퀀스가 저장될 테이블

CREATE SEQUENCE SEQ_DEPT_SEQUENCE--시퀀스 생성
INCREMENT BY 10
START WITH 10
MAXVALUE 90
MINVALUE 0
NOCYCLE
CACHE 2;

SELECT * FROM USER_SEQUENCES;--생성된 시퀀스 확인

INSERT INTO dept_sequence (DEPTNO, DNAME, LOC)
VALUES(SEQ_DEPT_SEQUENCE.NEXTVAL, 'DATABASE', 'SEOUL');
--SEQ_DEPT_SEQUENCE.NEXTVAL 시퀀스를 통해 VALUE값이 생성되어 INSERT된다.

SELECT*FROM dept_sequence ORDER BY DEPTNO;

ALTER SEQUENCE SEQ_DEPT_SEQUENCE--시퀀스 변경
INCREMENT BY 3
MAXVALUE 99
CYCLE;

 

[PL/SQL]

*익명 프로시저 블록 기본구조

DECLARE
	--선언영역
BEGIN
	--실행영역
    --예외처리 영역
END;

--사용자가 입력을 받은 경우
DECLARE
    v_num number;
begin
    v_num :=10/&분모;
dbms_output.put_line('결과' ||v_num);
end;

분모에 0을 넣으면 오류남 ->예외처리 해줘야 한다.

*예외처리(EXCEPTION)

DECLARE
    v_num number;
BEGIN
    v_num :=10/&분모;
    dbms_output.put_line(v_num);    
EXCEPTION
    when zero_divide then
        dbms_output.put_line('0이외의 다른 값을 입력하시오');
    when others then
        dbms_output.put_line('다른 오류 발생..');
END;

 

*제어구조

- WHILE문, FOR문 : LOOP

--WHILE문
declare
    i number :=0;
begin
    while i< 10 loop
dbms_output.put_line(i);
    i:=i+1;
    end loop;
end;

--FOR문
declare
    i number:=0;
begin
    for i in reverse 1..10 --거꾸로 출력하고싶을때 REVERSE를 써준다
loop
dbms_output.put_line(i);
    end loop;
end;

- CASE 문

declare
    ismale number(1);
begin
    ismale :=&성별;
    
    case ismale
    when 1 then
dbms_output.put_line('남자');
    when 2 then
dbms_output.put_line('여자');
    else dbms_output.put_line('Third Thing');
    end case;
end;

--1을 입력하면 남자, 2를 입력하면 여자, 그 외의 입력은 Third Thing으로 출력.

*커서를 이용해 데이터 쿼리하기

- 레코드 단위별 프로그래밍

DECLARE
    V_MID VARCHAR(50);
    V_PWD NUMBER;
    CURSOR CUR_MEMBER IS SELECT MID,PWD FROM MEMBERS; 
BEGIN
    OPEN CUR_MEMBER; --open
    FETCH CUR_MEMBER INTO V_MID, V_PWD; --members의 mid, pwd를 가져온다
    DBMS_OUTPUT.PUT_LINE(V_MID); --mid값만 출력.
    CLOSE CUR_MEMBER; --close
END;

DBMS에 CONAN 출력. 결과창에 PL/SQL 프로시저가 성공적으로 완료되었습니다. 출력

- 결과 집합 모두 출력하기

DECLARE
    V_MID VARCHAR(50);
    V_PWD NUMBER;
    CURSOR CUR_MEMBER IS SELECT MID,PWD FROM MEMBERS;
BEGIN
    OPEN CUR_MEMBER;
    LOOP    
        FETCH CUR_MEMBER INTO V_MID, V_PWD;
        IF CUR_MEMBER%NOTFOUND THEN EXIT;
        END IF;
        DBMS_OUTPUT.PUT_LINE(V_MID);
    END LOOP;
    CLOSE CUR_MEMBER;
END;

 

*프로시저

CREATE PROCEDURE문을 사용한다. 

선언부와 실행부로 구성되며 선언부에서는 변수와 매개변수를 선언하고, 실행부에서는 프로그램 로직을 구현.

변수는 저장프로시저나 트리거 내에서 사용되는 값이며, 매개변수는 저장 프로시저가 호출될 때 그 프로시저에 전달되는 값이다. 

*사용자 인증 프로시저 만들기

- 익명 프로시저

DECLARE
    v_mid members.mid%type := '&아이디'; --members테이블의 mid와 동일한 타입으로 만드는 것.
    v_pwd members.pwd%type := '&비밀번호';
    v_member members%rowtype; --members가 갖고 있는 레코드 rowtype을 한번에 저장
    cursor cur_member is select *from members where mid = v_mid;
begin
    open cur_member;
    fetch cur_member into v_member;
        if cur_member%notfound then dbms_output.put_line('회원이 존재하지 않음');
        ELSIF v_pwd <> v_member.pwd then
            dbms_output.put_line('비밀번호 불일치');
        ELSE dbms_output.put_line('인증 성공');
        end if;
    close cur_member;
end;

- 저장 프로시저

CREATE OR REPLACE PROCEDURE VALIDATE_MEMBER
(
    V_MID MEMBERS.MID%TYPE,
    V_PWD MEMBERS.PWD%TYPE
)
AS
    V_MEMBER MEMBERS%ROWTYPE;
    CURSOR CUR_MEMBER IS SELECT * FROM MEMBERS WHERE MID = V_MID;
BEGIN
   open cur_member;
    fetch cur_member into v_member;
        if cur_member%notfound then dbms_output.put_line('회원이 존재하지 않음');
        ELSIF v_pwd <> v_member.pwd then
            dbms_output.put_line('비밀번호 불일치');
        ELSE dbms_output.put_line('인증 성공');
        end if;
    close cur_member;
end;
    
EXECUTE VALIDATE_MEMBER('CONAN',1111);	--프로시저를 실행하는 명령문

*삽입 작업을 하는 프로시저

Book테이블에 한 개의 튜플을 삽입하는 프로시저

CREATE OR REPLACE PROCEDURE INSERTBOOK
(
    MYBOOKID IN NUMBER,
    MYBOOKNAME IN VARCHAR2,
    MYPUBLISHER IN VARCHAR2,
    MYPRICE IN NUMBER
)
AS
BEGIN
    INSERT INTO BOOK(BOOKID, BOOKNAME, PUBLISHER, PRICE)
    VALUES(MYBOOKID, MYBOOKNAME, MYPUBLISHER, MYPRICE);
END;
EXEC INSERBOOK(14,'스포츠과학','마당과학서적',25000);
SELECT *FROM BOOK;

*커서를 사용하는 프로시저

 : 실행 결과 테이블을 한번에 한행씩 처리하기 위해 테이블의 행을 순서대로 가리키는데 사용한다.

 

*트리거

 : 데이터의 변경문이 실행될 때 자동으로 따라서 실행되는 프로시저.

새로운 도서를 삽입한 후 자동으로 Book_log 테이블에 삽입한 내용을 기록하는 트리거

CREATE OR REPLACE TRIGGER AFTERINSERBOOK
AFTER INSERT ON BOOK FOR EACH ROW
DECLARE
    AVERAGE NUMBER;
    BEGIN
        INSERT INTO BOOK_LOG
            VALUES(:NEW.BOOKID, :NEW.BOOKNAME, :NEW.PUBLISHER, :NEW.PRICE);
        DBMS_OUTPUT.PUT_LINE('삽입 튜플을 BOOK_LOG 테이블에 백업');
    END;
    
INSERT INTO BOOK VALUES(18,'스포츠 과학3','이상미디어', 25000);
SELECT * FROM BOOK WHERE BOOKID = '16';
SELECT * FROM BOOK_LOG;