[PLSQL] block, 출력, 변수, 자료형, SQL

block

PL/SQL은 여러 블럭으로 구성돼 있는데 쉽게 짐작할 수 있는 실행할 SQL 명령이 모여있는 블럭 등이 있으며 이는 명령의 실행단위가 됨. 이외 익명 블럭, 이름이 있는 블럭 등도 있고, 내부는 기능별로 이름부, 선언부, 실행부, 예외처리부로도 나누기도 함.

1. 구성

-- PL/SQL로 하나의 단위명령을 실행할 때 
-- 아래와 같이 각 위치, 기능별 구성이 이루어짐
IS(AS)
-- 이름부
DECLARE
-- 선언부(변수 선언 등등)
BEGIN
-- 실행부(SQL 명령)
EXCEPTION
-- 예외처리부
END;
-- BEGIN, END를 제외한 나머지는 필요에 의해 생략이 가능함

2. 익명 블럭

DECLARE
    num NUMBER; --변수 선언
BEGIN
    num :=100; --실행명령
    DBMS_OUTPUT.PUT_LINE(num); --실행명령(화면출력)
END;

 

 

---

출력 명령

-- 화면 출력을 하기 위해 기능을 on 함
SET SERVEROUTPUT ON

-- 실행 시간을 출력하기 위한 기능을 on/off 함
SET TIMING ON
SET TIMING OFF

 

---

변수 선언

변수명 변수자료형;
변수명 변수자료형 := 초기값;
-- SQL 명령 내의 ' = '과 구분하기 위해 ' := '으로 사용함

 

PL/SQL의 자료형

-기존의 Oracle 자료형은 모두 포함하며 자유롭게 사용 가능

연산자

 

-- PL/SQL 블럭에 연산자를 사용한 예
DECLARE
    A INTEGER := 2**2*3**2;
BEGIN
    DBMS_OUTPUT.PUT_LINE('A='||TO_CHAR(A));
END;
-- BEGIN 등의 각 영역은 한 문장의 SQL문도 하나의 명령어로 
-- 연산자를 포함한 일반 명령어도 하나의 명령어로 인식해서 
-- 맨 뒤에 ';'이 있는 곳까지 실행함

---

SQL과 같이 사용하기

SQL Developer 쿼리창에는 반드시 블럭만 사용할 수 있는 건 아님. 일반적인 SQL문도 사용 가능

-- emp 테이블에서 사원번호가 7900인 사원의 이름을 출력
select ename from emp where empno=7900;

위 예를 PL/SQL의 블럭에 넣고 결과를 변수에 저장해 출력

DECLARE
    empname varchar2(30);
BEGIN
-- 블럭 안에 sql문은 다로 위치 지정 없이 
-- 그냥 다른 명령과 같이 기술하면 됨
    select ename 
    into empname -- sql문의 결과를 변수에 담아내는 방법
                 -- select와 from 사이에 into 키워드를 넣고 
                 -- 저장될 변수를 지정
    from emp 
    where empno=7900;
    DBMS_OUTPUT.PUT_LINE(empname);
END;

SQL문의 결과로 나온 필드값이 여러 개면, 위와 같은 한 개의 변수에 담을 수 없음. select와 from 사이에 지정한 필드명이 두개 이상이라면 갯수만큼 into에 변수의 갯수도 맞춰줘야 함.

-- 사원번호가 7900인 사원의 이름과 급여 출력
DECLARE
    empname varchar2(30);
    empsal number;
BEGIN
    select ename , sal
    into empname , empsal
    from emp 
    where empno=7900;
    DBMS_OUTPUT.PUT_LINE('성명: '||empname||', 급여: '||to_char(empsal));
END;

변수의 갯수가 많은 경우 자료형을 일일이 맞춰서 선언하기가 번거로우므로 매칭할 필드의 이름과 %type을 이용하여 자동으로 자료형이 맞춰지도록 함.

DECLARE
    empname emp.ename%TYPE; 
    -- emp 테이블의 ename 필드의 자료형으로 변수의 자료형 맞춰줌
    empsal emp.sal%TYPE;
BEGIN
    select ename , sal
    into empname , empsal
    from emp 
    where empno=7900;
    DBMS_OUTPUT.PUT_LINE('성명: '||empname||', 급여: '||to_char(empsal));
END;

 

연습하기

-- DBMS_OUTPUT.PUT_LINE()을 9번 사용해 구구단 7단을 출력
-- 이어붙이기 연산 사용
-- 현재는 변수가 필요하지 않기 때문에 DECLARE도 쓰지 않아도 됨

BEGIN
    DBMS_OUTPUT.PUT_LINE('7*1='||7*1);
    DBMS_OUTPUT.PUT_LINE('7*2='||7*2);
    DBMS_OUTPUT.PUT_LINE('7*3='||7*3);
    DBMS_OUTPUT.PUT_LINE('7*4='||7*4);
    DBMS_OUTPUT.PUT_LINE('7*5='||7*5);
    DBMS_OUTPUT.PUT_LINE('7*6='||7*6);
    DBMS_OUTPUT.PUT_LINE('7*7='||7*7);
    DBMS_OUTPUT.PUT_LINE('7*8='||7*8);
    DBMS_OUTPUT.PUT_LINE('7*9='||7*9);
END;
-- 사원테이블(emp)에서 1788번 사원의 이름과 부서명을 출력하는 익명 블록. 
-- join 명령 사용
-- '이름-부서명' 형식으로 스크립트 출력창에 출력

DECLARE
    empname emp.ename%TYPE;
    deptname dept.dname%TYPE;
BEGIN
    select a.ename , b.dname
    into empname , deptname
    from emp a, dept b
    where a.deptno=b.deptno and empno=7369;
    DBMS_OUTPUT.PUT_LINE(empname||' - '||deptname);
END;
-- select로 얻어낸 값을 insert 명령에 사용함
-- 사원테이블(emp) 테이블에서 가장 큰 사원번호로 조회하고
-- 그 사원번호보다 1만큼 큰 숫자를 
-- 새로운 입력 레코드의 사원번호로 하여 레코드를 추가
-- ename: HARRISON, JOB: MANAGER, MGR: 7369, HIREDATE: 2022/06/14
-- SAL: 3000, COMM: 700, DEPTNO: 40
-- select 결과로 저장한 변수명은 
-- insert에서도 바로 변수명으로 사용이 가능함
-- insert into emp values(변수이름, ...);
-- SQL Debeloper 프로그램은 이클립스처럼 Auto Commit이 아니기 때문에
-- insert update delete 명령 뒤엔 반드시 commit;을 사용함

DECLARE
    max_empno emp.empno%TYPE;
BEGIN
    select max(empno)
    into max_empno
    from emp;
    insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values(max_empno + 1,'HARRISON','MANAGER',7566,sysdate,3000,700,40);
    commit;
END;