티스토리 뷰



IF~ THEN~ END IF;




1) 임의의 성적을 사용하여 학점을 구하시오. 

DECLARE
	score number(3);
	grade varchar2(2);
BEGIN
	score := 88;
	if score BETWEEN 90 and 100 	then grade:= 'A'; end if;
	if score BETWEEN 80 and 89 		then grade:= 'B'; end if;
	if score	BETWEEN 70 and 79 		then grade:= 'C'; end if;
	if score	BETWEEN 60 and 69 		then grade:= 'D'; end if;
	if score	<60 								then grade:= 'F'; end if;	
	dbms_output.put_line(score);
	dbms_output.put_line(grade);
END;





IF~ ELSIF~ END IF;



2) 1번 문제를 if~elsif~end if문장으로 바꿔보자. 

 DECLARE
 	score number(3);
	grade varchar2(2);
 BEGIN
 	score := 88;
 	IF score BETWEEN 90 and 100 	then grade:= 'A'; 
 	ELSIF score BETWEEN 80 and 89 	then grade:= 'B'; 
 	ELSIF score BETWEEN 70 and 79 	then grade:= 'C'; 
 	ELSIF score BETWEEN 60 and 69 	then grade:= 'D'; 
 	ELSIF score <60 	then grade:= 'F'; END IF;
 	dbms_output.put_line(score);
	dbms_output.put_line(grade);
 END;
 



3) 사번, 성명, 급여, 연락처, 기타를 호출하되, 아래의 조건에 만족하도록 출력하시오.

단,  a. 급여는 천단위 분리 기호 사용

b. 급여가 5000이하인 경우 기타에 '저임금', 5000~10000 '보통임금', 10000 초과 '고임금'

DECLARE
	type rec_emp is RECORD (
		dnum employees.department_id%type,
		name employees.first_name%type,
		sal employees.salary%type,
		phone employees.phone_number%type ); r rec_emp;
	type tbl_emp is table of rec_emp index by BINARY_INTEGER; t tbl_emp;
	cnt binary_integer := -1;
	etc varchar2(20); --기타
BEGIN
	for i in (
		SELECT DEPARTMENT_ID, FIRST_NAME, SALARY, PHONE_NUMBER
		FROM EMPLOYEES ) loop
			cnt := cnt+1;
			t(cnt) := i;
		end LOOP;	

	for i in 0..cnt LOOP
	r := t(i);
			IF r.sal >10000 then etc:='고임금';
			ELSIF r.sal>5000 AND r.sal<=10000 then etc:='보통임금';
			ELSIF r.sal<=5000 then etc:='저임금'; END IF;
	dbms_output.put_line(r.dnum||'	'||r.name||'	'||to_char(r.sal, '999,999')||'	'||r.phone||'		'||etc);
	end LOOP;	
END;



4) 사번, 성명, 부서코드, 부서명, 기타를 출력하되, 부서코드가 80이면 '우리부서', 아니면 '타부서'를 기타 자리에 출력. 

DECLARE
	type rec_emp is RECORD (
		mid employees.EMPLOYEE_ID%type,
		name employees.first_name%type,
		dcode employees.department_id%type,
		dname departments.department_name%type ); r rec_emp;
	type tbl_emp is table of rec_emp index by BINARY_INTEGER; t tbl_emp;
	cnt binary_integer := -1;
	etc varchar2(30); --기타
BEGIN
	for i in (
		SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.DEPARTMENT_ID, d.department_name
		FROM EMPLOYEES e JOIN DEPARTMENTS d ON e.department_id = d.department_id 
		) LOOP
			cnt := cnt+1;
			t(cnt) := i;
		end LOOP;	

	for i in 0..cnt LOOP
	r := t(i);
			IF r.dcode = 80 then etc:='우리부서';
			ELSIF r.dcode != 80 then etc:='타부서'; END IF;
	dbms_output.put_line('	'||r.mid||'		'||r.name||'		'||r.dcode||'		'||r.dname||'		'||etc);
	end LOOP;	
END;




CASE 조건문

CASE [조건] 
    WHEN 조건1 THEN 결과1
    WHEN 조건2 THEN 결과2
    ...
    WHEN 조건n THEN 결과n
[else 기본값]
END;


5) 3번 문제를 case문으로 수정

DECLARE
	type rec_emp is RECORD (
		dnum employees.department_id%type,
		name employees.first_name%type,
		sal employees.salary%type,
		phone employees.phone_number%type ); r rec_emp;
	type tbl_emp is table of rec_emp index by BINARY_INTEGER; t tbl_emp;
	cnt binary_integer := -1;
	etc varchar2(20); --기타
BEGIN
	for i in (
		SELECT DEPARTMENT_ID, FIRST_NAME, SALARY, PHONE_NUMBER
		FROM EMPLOYEES ) loop
			cnt := cnt+1;
			t(cnt) := i;
		end LOOP;	

	for i in 0..cnt LOOP
	r := t(i);
		etc := CASE 
		WHEN r.sal>10000 THEN '고임금'
		WHEN r.sal>5000 and r.sal<=10000 THEN '보통임금'
		WHEN r.sal<=5000 THEN '저임금' --else '저임금' 
		end;

	dbms_output.put_line(r.dnum||'	'||r.name||'	'||to_char(r.sal, '999,999')||'	'||r.phone||'		'||etc);
	end LOOP;	
END;



6) 4번 문제를 case문으로 수정

DECLARE
	type rec_emp is RECORD (
		mid employees.EMPLOYEE_ID%type,
		name employees.first_name%type,
		dcode employees.department_id%type,
		dname departments.department_name%type ); r rec_emp;
	type tbl_emp is table of rec_emp index by BINARY_INTEGER; t tbl_emp;
	cnt binary_integer := -1;
	etc varchar2(30); --기타
BEGIN
	for i in (
		SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.DEPARTMENT_ID, d.department_name
		FROM EMPLOYEES e JOIN DEPARTMENTS d ON e.department_id = d.department_id 
		) LOOP
			cnt := cnt+1;
			t(cnt) := i;
		end LOOP;	

	for i in 0..cnt LOOP
	r := t(i);
		etc := CASE 
		WHEN r.dcode=80 THEN '우리부서'
		WHEN r.dcode!=80 THEN '타부서' --else '타부서' 이렇게 써도 돼 
		end;
	dbms_output.put_line('	'||r.mid||'		'||r.name||'		'||r.dcode||'		'||r.dname||'		'||etc);
	end LOOP;	
END;

--실행결과는 3, 4번 실행결과와 같음. 





①LOOP~END LOOP;
②WHILE~LOOP~END LOOP;
③FOR x IN n1..n2 LOOP~END LOOP;


7) 입력한 단수의 구구단을 출력해보자. (dan변수에 5 대입)

DECLARE
	dan NUMBER := 5;
	i 	NUMBER := 1;
	r 	NUMBER;
BEGIN
	LOOP
		r := dan*i;
		dbms_output.put_line(dan||' * '||i||' = '||r);
		i := i+1;
		
		EXIT WHEN i > 9; --exit 안 해주면 오라클 죽을 때까지 돌아버려... when 안 쓰면 단순 종료.
	END LOOP;
END;



8) 2단부터 9단까지의 구구단 출력. (단, LOOP를 사용할 것. )

DECLARE
	dan number := 2;
	i number :=2;
	r number;
	
BEGIN
	LOOP
	dbms_output.put_line('-----------'||dan||'단'); 	
		LOOP
			r := dan*i;
			dbms_output.put_line(dan||' * '||i||' = '||r);
			i := i+1;
			exit when i>9; END LOOP;
		i:=2;
		dan := dan+1;
		exit when dan>9;
	END LOOP;
END;


9) 2단부터 9단까지의 구구단 출력. (단, WHILE을 사용할 것. )

DECLARE
	dan number:=2;
	i number:=1;
	r number;
BEGIN
	while dan<10 loop
		
		dbms_output.put_line('-----------'||dan||'단');
		while i<10 loop		
			r := dan*i;
			dbms_output.put_line(dan||' * '||i||' = '||r);
			i := i+1;
		end loop;
		i:=2;
		dan := dan+1;
	end loop;
END;



10) 2단부터 9단까지의 구구단 출력. (단, FOR를 사용할 것. )

DECLARE
	dan NUMBER;
	i 		NUMBER;
	r 		NUMBER;
	a BINARY_Integer :=0;
BEGIN
	FOR z IN 1..9 LOOP
		a := a+1;
		i := 2;
		dbms_output.put_line('-----------'||a||'단');
		FOR x IN 2..9 LOOP
		dan := a;
		r :=dan*i;
		dbms_output.put_line(dan||' * '||i||' = '||r);
		i := i+1;
			exit when i>9;
		END LOOP;
	END LOOP;
END;



번외) 구구단 거꾸로 출력해보기.

DECLARE
	i number :=0;
	j number :=0;
	r number;
BEGIN
	FOR	i in reverse 2..9 loop
		dbms_output.put_line('---------'||i||'단');
		FOR j in 2..9 loop
			r := i*j;
			dbms_output.put_line(i||' * '||j||' = '||r);
		end loop;
	end loop;	
END;





PL/SQL - Cursor(커서)

Explicit Cursor(명시적 커서): 

다른 변수와 마찬가지로 PL/SQL의 선언부(declare)에 선언되어야 한다. 





1) 사번과 이름을 명시적 커서에 담아 그 결과를 출력하시오.

DECLARE
	CURSOR cur IS
		SELECT EMPLOYEE_ID, FIRST_NAME
		FROM EMPLOYEES where DEPARTMENT_ID=30;
	emid EMPLOYEES.employee_id%type;
	ename EMPLOYEES.first_name%type;
BEGIN
	OPEN cur;
	loop
		FETCH cur INTO emid, ename;
		exit when cur%notfound;
		dbms_output.put_line(emid||': 	'||ename);
	end loop;
	CLOSE cur;
END;



2) 1번의 내용을 for문으로 수정 -> 절차가 간단해짐. 

DECLARE
	CURSOR cur IS
		SELECT EMPLOYEE_ID vid, FIRST_NAME vname
		FROM EMPLOYEES WHERE DEPARTMENT_ID=30;
BEGIN
	FOR rec IN cur loop
		dbms_output.put_line(rec.vid||': 	'||rec.vname);
	END loop;
END;

--실행결과 1번과 같음.



3) 사번, 성명, 급여, 기타(고/저/보통 임금)를 출력하되 명시적 커서 c1을 사용하여 출력하시오. 

DECLARE
	etc varchar2(20);
	CURSOR c1 IS
		SELECT EMPLOYEE_ID vid, FIRST_NAME vname, SALARY vsal
		FROM EMPLOYEES;
BEGIN
	FOR rec IN cur loop
		IF rec.vsal >10000 then etc:='고임금';
				ELSIF rec.vsal<=5000 then etc:='저임금'; 
				ELSE etc:= '보통임금' END IF;
		IF 
		dbms_output.put_line(rec.vid||': 	'||rec.vname||'	/급여'||rec.vsal||' - '||etc);
	END loop;
END;



4) 부서코드를 파라메터로 받아 해당 부서의 직원들 성명, 연락처, 급여, 부서명을 출력하는 커서를 만드시오.

DECLARE
	na employees.first_name%type;
	ph employees.phone_number%type;
	sa employees.salary%type;
	dna departments.department_name%type;
	CURSOR c1 (dcode employees.department_id%type) IS --여기서 선언해도 되는구나!
		SELECT e.first_name na, e.phone_number ph, e.salary sa, d.department_name dna
		FROM EMPLOYEES e JOIN DEPARTMENTS d ON e.department_id = d.department_id WHERE e.DEPARTMENT_ID = dcode;
BEGIN
		OPEN c1(60);
	loop
		FETCH c1 INTO na, ph, sa, dna;
		exit when c1%notfound;
		dbms_output.put_line('성명: '||na||'	연락처: '||ph||'	급여: '||sa||'	부서명:'||dna);
	end loop;
	CLOSE c1;	
END;



4-2) for문 사용.

DECLARE
	CURSOR c1 (dcode employees.department_id%type) IS --여기서 선언해도 되는구나!
		SELECT e.first_name na, e.phone_number ph, e.salary sa, d.department_name dna
		FROM EMPLOYEES e JOIN DEPARTMENTS d ON e.department_id = d.department_id WHERE e.DEPARTMENT_ID = dcode;
BEGIN
		FOR r IN c1(60) LOOP
			dbms_output.put_line('성명: '||r.na||'	연락처: '||r.ph||'	급여: '||r.sa||'	부서명:'||r.dna);
		END loop;	
END;

--실행결과 4번과 같음.



댓글