Oracle 6일차 : PL/SQL, 루프, 커서

PL/SQL

• SQL언어에 절차적 언어요소를 추가한 프로그래밍 언어

-- 형식
DECLARE
	변수, 커서 …
BEGIN	
		SQL문, PL/SQL문
END;
	
-- 콘솔에서 실행시 / 
-- 콘솔에서 실행시 결과값이 안보일 경우 SET SERVEROUTPUT ON을 한번만 주면 된다

🐣 실습하기

--예제1
	DECLARE
		v_no number(4,1);
	BEGIN
		v_no  := 40; -- 오라클의 대입연산자는 := 이다. 동등비교는 = 이고..
		DBMS_OUTPUT.PUT_LINE(v_no);
	END
	
-- 예제2
	DECLARE
		vempno number(4);
		vename varchar2(10);		
	BEGIN
		select empno, ename
		into vempno, vename
		from emp
		where empno=7369;
		DBMS_OUTPUT.PUT_LINE( vempno||' '||vename );
	END;
	/
	
-- 예제3
	DECLARE
		v_year number(4) := 2022;
		v_ani number(2) := mod(v_year, 12); /*나머지구함*/
	BEGIN
		if v_ani = 0 then 
		DBMS_OUTPUT.PUT_LINE(v_year||'년도 생은 돼지띠 입니다');
		elsif v_ani = 1 then 
		DBMS_OUTPUT.PUT_LINE(v_year||'년도 생은 쥐띠 입니다');
		elsif v_ani = 2 then 
		DBMS_OUTPUT.PUT_v_year||'년도 생은 소띠 입니다');
		elsif v_ani = 3 
		DBMS_OUTPUT.PUT_LINE(v_year||'년도 생은 호랑이띠 입니다');
		elsif v_ani = 4 then 
		DBMS_OUTPUT.PUT_LINE(v_year||'년도 생은 토끼띠 입니다');
		elsif v_ani = 5 then 
		DBMS_OUTPUT.PUT_LINE(v_year||'년도 생은 용띠 입니다');
		elsif v_ani = 6 then 
		DBMS_OUTPUT.PUT_LINE(v_year||'년도 생은 뱀띠 입니다');
		elsif v_ani = 7 then 
		DBMS_OUTPUT.PUT_LINE(v_year||'년도 생은 말띠 입니다');
		elsif v_ani = 8 then 
		DBMS_OUTPUT.PUT_LINE(v_year||'년도 생은 양띠 입니다');
		elsif v_ani = 9 then 
		DBMS_OUTPUT.PUT_LINE(v_year||'년도 생은 원숭이띠 입니다');
		elsif v_ani = 10 then 
		DBMS_OUTPUT.PUT_LINE(v_year||'년도 생은 닭띠 입니다');
		elsif v_ani = 11 then 
		DBMS_OUTPUT.PUT_LINE(v_year||'년도 생은 개띠 입니다');
		end if
	END;
	/
		
	--실습문제
	--v_no에 초기값으로 숫자을 지정한 후 
	-- v_no가 짝수이면 '숫자'는 짝수입니다
	-- 홀수이면 '숫자'는 홀수입니다 라고 출력하기
		
		DECLARE
			v_no number(4) := 10;
		BEGIN
			if mod(v_no, 2)=0 then
			DBMS_OUTPUT.PUT_LIND(v_no||'는 짝수입니다.');
			elsif mod(v_no, 2)=1 then
			DBMS_OUTPUT.PUT_LIND(v_no||'는 홀수입니다.');
			end if
		END; 
/

반복문

-- 형식
LOOP 
  명령문;
  EXITWHEN 조건 
END LOOP;

🐣 실습하기

-- 1부터 10까지 출력
DECLARE 
    num NUMBER(2) := 1; 
BEGIN 
    LOOP 
        dbms_output.Put_line(num); 
        num := num + 1; 
        exit WHEN num > 10; 
    END LOOP; 
END; 
/ 
		
		
-- gudu라는 변수에 숫자를 지정한 후 
-- 그 숫자에 해당하는 구구단을 출력
DECLARE 
    gudu NUMBER(5) := 8; 
    num  NUMBER(5) := 1; 
BEGIN 
    LOOP 
        dbms_output.Put_line(gudu 
                             ||' x ' 
                             ||num 
                             ||' = ' 
                             ||gudu * num); 
        num := num + 1; 
        exit WHEN num > 9; 
    END LOOP; 
END; 
/ 

• 선언부에서, 변수명테이블명.컬럼명%TYPE; 라두면, 해당 테이블의 컬럼의 데이터타입을 참조하게 된다.
• 즉 이 경우 테이블쪽에서 타입을 수정하면 프로그램에서는 자동으로 반영하므로 수정할 필요가 없다는게 장점

DECLARE 
  vempno emp.empno%TYPE; 
  vename emp.ename%TYPE; 
  vcomm emp.comm%TYPE; 
BEGIN 
-- 세개의 컬럼값을 조회해서 위의 선언된 변수에 넣어준다
  SELECT empno, 
         ename, 
         comm 
  INTO   vempno, 
         vename, 
         vcomm 
  FROM   emp 
  WHERE  empno=7369; 
   
  IF vcomm IS NOT NULL THEN 
    dbms_output.put_line(vename 
    ||'님의 커미션은 ' 
    ||vcomm 
    ||'만원 입니다.' 
    ||); 
  ELSE 
    dbms_output.Put_line(vename 
    ||'님은 커미션이 없습니다.'); 
  END IF; 
END;

--실습예제

-- angel 계정의 shop 테이블로 문제를 푸세요
-- shop 테이블에서 상품명이 '블랙진' 색상과 단가를 얻어내서 출력하세요.
-- 출력예 : 블랙진바지는 black색상이며 23000원 입니다
DECLARE 
    sname  shop.sangpum%TYPE; 
    scolor shop.color%TYPE; 
    sprice shop.price%TYPE; 
BEGIN 
    SELECT sangpum, 
           color, 
           price 
    INTO   sname, scolor, sprice 
    FROM   shop 
    WHERE  sangpum = '블랙진'; 

    dbms_output.Put_line(sname 
                         ||'은(는) ' 
                         ||scolor 
                         ||'색상이며ㅋ' 
                         ||sprice 
                         ||'원 입니다'); 
END; 
/ 

조건문

-- 형식
 CASE 표현식
		WHEN 값1 THEN 결과1
		WHEN 값2 THEN 결과2
		[ELSE 결과3]
END;
	
	
DECLARE 
    vgrade   CHAR(1) := 'B'; 
    vmessage VARCHAR2(20); 
BEGIN 
    vmessage := CASE vgrade 
                  WHEN 'A' THEN 'Excellent' 
                  WHEN 'B' THEN 'Very Good' 
                  WHEN 'C' THEN 'Good' 
                  ELSE 'Bad' 
                END; 

    dbms_output.Put_line(vgrade 
                         ||': ' 
                         ||vmessage); 
END; 

/ 
	
	
FOR counter IN [REVERSE] 시작값..최종값 
LOOP 
  문장1;문장2;END LOOP;
		
BEGIN 
    FOR counter IN 1..4 LOOP 
        dbms_output.Put_line('Hello' 
                             ||counter); 
    END LOOP; 
END; 

/ 

-- FOR문을 활용하여 구구단 써보기
			
DECLARE 
    gudu NUMBER(2) := 8; 
BEGIN 
    FOR counter IN 1..9 LOOP 
        dbms_output.Put_line(gudu 
                             ||' x ' 
                             ||counter 
                             ||' = ' 
                             ||gudu * counter); 
    END LOOP; 
END; 
/ 

커서

• sql문을은 실행될 때마다 명령이 분석되고 실행되어 결과를 보고하기 위한 특별한 메모리영역을 사용하는데 이 영역을 참조하는것이 커서이다.

커서 속성	반환값
sql%rowcount	실행된행 수를반환
sql%found	가장 최근의sql문이하나 이상의 행에 영향을 준경우true 반환
sql%notfound	결과 행이 없는경우true 반환
sql%isopen	항상false 반환(항상close를하기 때문에 항상false)

-- 형식
CURSOR 커서명 
IS 
  SELECT 문장; --select의 결과를 커서에 저장
	
-- cursor의 데이타를 반복적으로 출력을 하려면
-- loop 이나 for 문을 사용하면 된다
	
LOOP 
  FETCH 커서명 
  INTO  변수명1, 
        변수명2;EXITWHEN 커서명%NOTFOUND;END LOOP;
        
-- FOR문은 커서 자동 오픈
	FOR 레코드명 IN 커서명 LOOP
		문장1;
		문장2;
	END LOOP;	
	
-- 커서를 사용하려면 OPEN 커서명
-- 모두 사용한 후에는 CLOSE 커서명

🐣 실습하기

--@myscott
DECLARE 
    CURSOR c1 IS 
      SELECT deptno, 
             dname, 
             loc 
      FROM   dept; 
BEGIN 
    --커서에 저장된 레코드를 반복해서 출력, 자동으로 커서가 오픈 
    FOR dept_recored IN c1 LOOP 
        EXIT WHEN c1%NOTFOUND; 
        --검색된 데이타가 없으면 반복문을 빠져나간다 

        dbms_output.Put_line(dept_record.deptno 
                             ||' ' 
                             ||dept_record.dname 
                             ||' ' 
                             ||dept_record.loc); 
    END LOOP; 
END; 

/ 
	
--@angel
DECLARE 
    vsum NUMBER(7) := 0; --상품가격의 합계를 구할 변수 선언 
    vavg NUMBER(7) := 0; --상품가격의 평균을 구할 변수 선언 
    i    NUMBER(2) := 1; --rowcount를 받을 변수 
    CURSOR c1 IS 
      SELECT num, 
             sangpum, 
             color, 
             price 
      FROM   shop; 
BEGIN 
    --커서에 저장된 레코드를 반복해서 출력, 자동으로 커서가 오픈 
    FOR dept_record IN c1 LOOP 
        dbms_output.Put_line(c1%rowcount 
                             ||'번째 상품'); 

        vsum := vsum + dept_record.price; 

        EXIT WHEN c1%NOTFOUND; 
        --검색된 데이타가 없으면 반복문을 빠져나간다 

        dbms_output.Put_line(dept_record.num 
                             ||' ' 
                             ||dept_record.sangpum 
                             ||' ' 
                             ||dept_record.color 
                             ||' ' 
                             ||dept_record.price); 

        i := c1%rowcount; 
    END LOOP; 

    vavg := vsum / i; 

    dbms_output.Put_line('합계는 ' 
                         ||vsum 
                         ||' 평균은 ' 
                         ||vavg); 
END; 

/ 
	
-- 다른 방식으로 해보자
DECLARE 
  vcnt NUMBER(5) := 0; 
  vavg NUMBER(7,1) :=0; 
  CURSOR c1 IS 
    SELECT num, 
           sangpum, 
           color, 
           price, 
           to_char(ipgoday, 'yyyy-mm-dd') iday 
    FROM   shop;

BEGIN 
    SELECT Avg(price) 
    INTO   vavg 
    FROM   shop; 

    --상품들의 평균을 sql 문으로부터 얻어온다 
    SELECT Count(*) 
    INTO   vcnt 
    FROM   shop; 

    --커서에 저장된 레코드를 반복해서 출력, 자동으로 커서가 오픈 
    FOR dept_record IN c1 LOOP 
        EXIT WHEN c1%NOTFOUND; 

        dbms_output.Put_line(dept_record.num 
                             ||' ' 
                             ||dept_record.sangpum 
                             ||' ' 
                             ||dept_record.color 
                             ||' ' 
                             ||dept_record.price 
                             ||' ' 
                             ||dept_record.iday); 
    END LOOP; 

    dbms_output.Put_line('상품들의 총 개수: ' 
                         ||vcnt); 

    dbms_output.Put_line('상품들의 평균가: ' 
                         ||vavg); 
END; 
/ 
	
--@myscott / 교재 12장 p.23
DECLARE 
    tot_sum NUMBER := 0; 
    CURSOR c1 IS 
      SELECT ename, 
             sal 
      FROM   emp; 
BEGIN 
    dbms_output.Put_line('이름      급여'); 

    dbms_output.Put_line('---------------'); 

    FOR emp_record IN c1 LOOP 
        tot_sum := tot_sum + emp_record.sal; 

        dbms_output.Put_line(emp_record.ename 
                             ||'      ' 
                             ||emp_record.sal); 
    -- 왜 여기는 EXIT WHEN이 없을까? 
    END LOOP; 

    dbms_output.Put_line('---------------'); 

    dbms_output.Put_line('총 급여 : ' 
                         ||'       ' 
                         ||tot_sum); 
END; 
/