PL/SQL 문법

3 분 소요

PL/SQL 요약

DB 메모리에서 실행 할때마다 반복실행?
함수는 NOCASH 이므로 부를때 마다 실행됨.
배치처리에서 프로시져, 함수 많이 사용.

PL/SQL 기본 예제

SET SERVEROUTPUT ON
DECLARE -- 선언부 시작됨
    vno NUMBER(4);
    vname VARCHAR2(10);
BEGIN --실행부 시작됨
    SELECT empno, ename INTO vno, vname --INTO => 데이터베이스에서 수행된 결과 값을 위에서 선언한 두 변수에 저장했습니다.
    FROM emp
    WHERE empno = 7900;
DBMS_OUTPUT.PUT_LINE(vno||' '||vname); -- 두 변수에 저장된 값을 화면에 출력합니다.
END; --실행부 종료됨
/-- 작성된 PL/SQL 블록을 실행 시킵니다.

예제 1

DESC PROFESSOR;
select * from professor;    
    SET SERVEROUTPUT ON
    DECLARE
        vPROFNO NUMBER(10);
        vPAY NUMBER(10);
        -- vPROFNO PROFESOR.PROFNO%TYPE   이건 지정된 타입을 그대로 갖고오겠당
        -- vROW     PROFESOR%ROWTYPE      여러게의 컬럼을 한꺼번에 저장할 변수로 선언함
    BEGIN
        SELECT PROFNO, PAY INTO vPROFNO, vPAY
        FROM PROFESSOR
        WHERE PROFNO = 1001;
    DBMS_OUTPUT.PUT_LINE(vPROFNO||'번 교수의 급여는 ?? '||vPAY);
    END;
    /

예제 2

    SET SERVEROUTPUT ON
    DECLARE
        v_profno professor.profno%type;
        v_name professor.name%type;
        v_deptno professor.deptno%type;
        v_hiredate professor.hiredate%type;
    BEGIN
        SELECT profno, name, deptno, hiredate INTO v_profno,v_name,v_deptno,v_hiredate
        FROM PROFESSOR
        WHERE PROFNO = '&교수번호';
    DBMS_OUTPUT.PUT_LINE(v_profno||' '||v_name||' '|| v_deptno||' '||v_hiredate);
    END;
    /

PL/SQL 레코드 타입 예제

   SET SERVEROUTPUT ON;
    DECLARE
        TYPE emp_record_type is record
        (emp_no emp.empno%type
        emp_no emp.empno%type
        job emp.job%type);
        
        v_rec1 emp_record_type;
    BEGIN
        SELECT empno
    DBMS_OUTPUT.PUT_LINE(v_row.empno||' '||v_row.ename||' '||v_row.sal);   
    END;
/
execute nmChange;--프로시저를 실행.

   SET SERVEROUTPUT ON;
    DECLARE
        TYPE dept_record_type is record
        (deptno dept.deptno%type,
        dname dept.dname%type,
        loc dept.loc%type);
        v_dept dept_record_type;
    BEGIN
        SELECT deptno, dname, loc
        INTO v_dept
        FROM dept
        WHERE deptno=30;
    DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.dname||' '||v_dept.loc);   
    END;
/

select * from dept

PL/SQL 컬렉션 요약

page
TYPE {type_name} IS TABLE OF
프로그래밍의 배열과 같은 구조
array는 인덱스 값에 대입하고 인덱스 까지의 크기의 배열이 생성됨

PL/SQL 컬렉션 타입 예제

SET SERVEROUTPUT ON
DECLARE
    t_name VARCHAR2(20);
    
    TYPE tbl_emp_name IS TABLE OF
    emp.ename%TYPE INDEX BY BINARY_INTEGER;
    
    v_name tbl_emp_name;
BEGIN
    SELECT ename 
    INTO t_name
    FROM emp
    WHERE empno = 7499;
    
    v_name(0) := t_name;
    DBMS_OUTPUT.PUT_LINE(v_name(0));
    END;

PL/SQL 컬렉션 타입 WHILE 문

반복문은 반복 횟수를 알 수 없는 경우에 사용하는 BASIC LOOP문과
WHILE 문이 있고 반복횟수를 지정하는 FOR문이 있음.

CONTINUE 문과 EXIT(break) 문으로 제어 가능

-- BASIC LOOP 문 시용법
LOOP
  PL/SQL 문장;
  PL/SQL 문장;
  EXIT[조건];
END LOOP;

--       문제 1
SET SERVEROUTPUT ON;
DECLARE
    num number := 0;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(num);
        num := num +1;
        EXIT WHEN num > 5;
    END LOOP;
END;
/
-- 루프 초기에 조건을 주고 싶을 때 LOOP 앞에 WHILE을 붙인다.
SET SERVEROUTPUT ON;
DECLARE
    num number := 0;
BEGIN
    WHILE num < 6 LOOP
        DBMS_OUTPUT.PUT_LINE(num);
        num := num +1;
        EXIT WHEN num > 5;
    END LOOP;
END;
/

    -- 루프 초기에 조건을 주고 싶을 때 LOOP 앞에 FOR 문을 붙이는 경우.
SET SERVEROUTPUT ON;
DECLARE
    num number := 0;
BEGIN
    FOR i IN 0..5 LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
/  
---- 슬래쉬는 실행한다는 의미이다.

-- REVERSE 는 거꾸로 출력하는 키워드
SET SERVEROUTPUT ON;
DECLARE
    num number := 0;
BEGIN
    FOR i IN REVERSE 0..5 LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
/     

--       문제 2 이름의 length 만큼 별찍기
SET SERVEROUTPUT ON;
DECLARE
    v_empno emp.empno%type := &emp입력;
    v_ename emp.ename%type;
    v_result varchar2(20);
BEGIN
    SELECT ename
    INTO v_ename
    FROM emp
    where empno = v_empno;
    FOR i IN 1..LENGTH(v_ename) LOOP
        v_result := CONCAT(v_result,'*');
        DBMS_OUTPUT.PUT_LINE(v_result);
    END LOOP;
END;
/

PL/SQL 컬렉션 타입 FOR 문

SET SERVEROUTPUT ON
DECLARE
    TYPE e_table_type IS TABLE OF emp.ename%TYPE 
    INDEX BY BINARY_INTEGER;
    tab_type e_table_type;
    a BINARY_INTEGER:=0;
    BEGIN
    FOR i IN(SELECT ename FROM emp) LOOP
        a := a+1;
        tab_type(a) := i.ename;
    END LOOP;
    FOR j IN 1..a LOOP
    DBMS_OUTPUT.PUT_LINE(tab_type(j));
    END LOOP;
    END;
/

PL/SQL 컬렉션 타입 IF 문

-- ================== PL/SQL == IF 문====================
SET SERVEROUTPUT ON
DECLARE
    v_empno emp.empno%type;
    v_deptno emp.deptno%type;
    v_ename emp.ename%type;
    v_dname varchar2(20);
    
BEGIN
    SELECT empno, deptno, ename
    INTO v_empno, v_deptno, v_ename
    FROM emp
    where empno = &입력;
    
    IF v_deptno = 10 THEN
        v_dname := 'ACCOUNT';
    ELSIF v_deptno = 20 THEN
        v_dname := 'RESEARCH';
    ELSIF v_deptno = 30 THEN
        v_dname := 'SALES';
    ELSIF v_deptno = 40 THEN
        v_dname := 'OPERATIONS';
    END IF;
   DBMS_OUTPUT.PUT_LINE(v_empno||'  '||v_deptno||'  '||v_ename||'  '||v_dname);
END;
/

-- ================== PL/SQL == IF 문 2====================
SET SERVEROUTPUT ON
DECLARE
    v_empno emp.empno%type;
    v_ename emp.ename%type;
    v_comm emp.comm%type;
    
BEGIN
    SELECT empno, ename, comm
    INTO v_empno, v_ename, v_comm
    FROM emp
    where empno = &입력;
    
    IF v_comm > 0 THEN
        DBMS_OUTPUT.PUT_LINE('뽀나쓰 아주 좋음 = '||v_comm );
    ELSIF v_comm < 0 THEN
        DBMS_OUTPUT.PUT_LINE('난 뽀나쓰이 없음= '||v_comm );
    ELSE
        DBMS_OUTPUT.PUT_LINE('뭐니? = '||v_comm );
    END IF;
END;
/

-- ================== PL/SQL == CASE 문 (급여인상)====================
SET SERVEROUTPUT ON
DECLARE
    v_empno emp.empno%type;
    v_ename emp.ename%type;
    v_sal emp.sal%type;
    v_deptno emp.sal%type;
    v_up_sal emp.sal%type;
    
BEGIN
    SELECT empno, ename, sal, deptno
    INTO v_empno, v_ename, v_sal, v_deptno
    FROM emp
    where empno = &입력;
    v_up_sal := CASE 
                    WHEN v_deptno = 10 THEN v_sal*1.1
                    WHEN v_deptno IN(20,30) THEN v_sal*1.2
                    WHEN v_deptno > 30 THEN v_sal*1.3
                    ELSE v_sal
                    END;
--    EQUAL 만 사용할때 이방식,,,,
--    v_up_sal := CASE v_deptno
--            WHEN 10 THEN v_sal*1.1
--            WHEN 20 THEN v_sal*1.2
--            WHEN 30 THEN v_sal*1.3
--            ELSE v_sal
--            END;
            DBMS_OUTPUT.PUT_LINE('사원번호'||' '||'이름'||' '||'급여'||' '||'부서번호'||' '||'인상후 급여' );
            DBMS_OUTPUT.PUT_LINE(v_empno||'  '||v_ename||'  '||v_sal||'  '||v_deptno||'  '||v_up_sal );
END;
/

Twitter Facebook LinkedIn

PL/SQL 문법

PL/SQL 요약

PL/SQL 기본 예제

예제 1

예제 2

PL/SQL 레코드 타입 예제

PL/SQL 컬렉션 요약

PL/SQL 컬렉션 타입 예제

PL/SQL 컬렉션 타입 WHILE 문

PL/SQL 컬렉션 타입 FOR 문

PL/SQL 컬렉션 타입 IF 문

공유하기

댓글남기기

참고