Basic loop문에서 이중 루프문
반복문 자체를 반복 시키고 싶을 때 이중 반복문을 사용합니다.
문법
begin
loop
loop
실행문;
exit when 안쪽 반복문을 종료시킬 조건;
end loop;
exit when 바깥쪽 반복문을 종료시킬 조건;
end loop;
end;
/======================================================
내부 loop문을 반복시키고 싶을 때 외부 loop문 사용
*주의*
각각 exit when을 사용해서 종료시킬 조건 작성해줘야함
그렇지 않으면 무한루프 돌게됨
예제1) — Basic loop
basic loop문으로 숫자 1부터 9까지 출력하시오
set serveroutput ondeclare
v_count number(10) :=1 ;
begin
loop
dbms_output.put_line( v_count ); #반복해서 실행하고 싶은 실행문
v_count := v_count +1 ; #반복문을 종료시킬 코드
exit when v_count >9; #반복문을 종료시킬 코드
end loop;
end;
/=============================================================
v_count의 값이 exit when의 조건인 v_count >9 에 해당되지 않으면,
종료하지 못하고 다시 반복문 돌게됨
v_count =1+1=2 #9보다 작으므로 2 출력됨
v_count =2+1=3 #9보다 작으므로 2 출력됨
v_count =3+1=4 #9보다 작으므로 2 출력됨
v_count =4+1=5 #9보다 작으므로 2 출력됨
v_count =5+1=6 #9보다 작으므로 2 출력됨
v_count =6+1=7 #9보다 작으므로 2 출력됨
v_count =7+1=8 #9보다 작으므로 2 출력됨
v_count =8+1=9 #9보다 작으므로 2 출력됨
v_count =9+1=10 #9크기 때문에 exit로 loop문 탈출
end loop;
end;
/
예제2)
구구단 2단을 출력하는 기본 basic loop문을 수행하시오
set serveroutput ondeclare
v_count number(10) :=1 ;
begin
loop
dbms_output.put_line( '2 x '|| v_count ||' = '||2* v_count );
v_count := v_count +1 ;
exit when v_count >9;
end loop;
end;
/
예제3) —BASIC LOOP 이중 루프 문
basic loop 문으로 구구단 전체를 출력하시오 !
set serveroutput ondeclare
v_dan number(10) :=2;
v_count number(10) :=1 ;
begin
loop
loop
dbms_output.put_line( v_dan ||' x '|| v_count ||' = '|| v_dan * v_count );
v_count := v_count +1 ;
exit when v_count >9;
end loop;
v_dan := v_dan +1; -- 단을 증가 시키는 코드 (2단, 3단)
v_count :=1; -- v_count 를 다시 1로 변경하는 코드
exit when v_dan >9; -- 밖의 루프문을 종료시키는 코드end loop;
end;
/
문제풀며 익히기
Q1.
예제 3의 코드에 새로운 코드를 추가하여 구구단 짝수단만 출력되게 하시오.
A.
set serveroutput ondeclare
v_dan number(10) :=2;
v_count number(10) :=1 ;
begin
loop
if mod(v_dan, 2) =0then
loop
dbms_output.put_line( v_dan ||' x '|| v_count ||' = '|| v_dan * v_count );
v_count := v_count +1 ;
exit when v_count >9;
end loop;
end if;
v_dan := v_dan +1; -- 단을 증가 시키는 코드 (2단, 3단)
v_count :=1; -- v_count 를 다시 1로 변경하는 코드
exit when v_dan >9; -- 밖의 루프문을 종료시키는 코드end loop;
end;
/================================================================
내부 loop문을 둘러싸며 if문 넣기
029. While loop문에서 이중 루프문
📖
While loop문에서 이중 루프문
반복문 자체를 반복 시키고 싶을 때 이중 반복문을 사용합니다
문법
begin
while 조건 loop
while 조건 loop
실행문 ;
[안쪽 루프문을 종료시킬 코드]; => 안쪽루프문의 조건을 false로 만들 조건 작성
end loop;
[바깥쪽 루프문을 종료시킬 코드]; => 바깥쪽루프문의 조건을 false로 만들 조건 작성
end loop;
end;
/
예제1) 구구단 2단을 while loop 문으로 구현하시오
declare
v_count number(10) :=1;
begin
while v_count <10 loop
dbms_output.put_line( '2 x '|| v_count ||' = '||2* v_count );
v_count := v_count +1 ;
end loop;
end;
/
예제2. 구구단 전체를 while loop문으로 출력하시오
set serveroutput ondeclare
v_dan number :=2 ;
v_count number(10) :=1;
begin
while v_dan <=9 loop
while v_count <=9 loop
dbms_output.put_line( v_dan ||' x '|| v_count ||' = '|| v_dan * v_count );
v_count := v_count +1 ; -- 곱하는 수를 증가시키는 코드end loop;
v_dan := v_dan +1 ; -- 단을 증가시키는 코드
v_count :=1; -- 단이 증가될 때마다 다시 1로 초기화해줘야 합니다.end loop;
end;
/=======================================================
v_dan <=9 일때, 안쪽 루프문 시작
//////////////////////////////////////////////
v_count <=9 일때, 즉 while조건에 맞을 때 실행.
v_count 가 10이되면, 9보다 크기 때문에 안쪽 루프문 탈출하여
v_dan 을 1 증가시키고, v_count를 1로 초기화해줌
//////////////////////////////////////////////
v_dan이 10이되면, 즉 9보다 커지면 바깥쪽 루프문 endloop;
문제풀며 익히기
Q1.
구구단의 짝수단만 출력되게 하시오
set serveroutput ondeclare
v_dan number :=2 ;
v_count number(10) :=1;
begin
while v_dan <=9 loop
if mod(v_dan, 2) =0then
while v_count <=9 loop
dbms_output.put_line( v_dan ||' x '|| v_count ||' = '|| v_dan * v_count );
v_count := v_count +1 ; -- 곱하는 수를 증가시키는 코드end loop;
end if;
v_dan := v_dan +1 ; -- 단을 증가시키는 코드
v_count :=1; -- 단이 증가될 때마다 다시 1로 초기화해줘야 합니다.end loop;
end;
/
030. For loop문에서 이중 루프문(간단해서 많이 사용)
📖
For loop문에서 이중 루프문
반복문 자체를 반복 시키고 싶을 때 이중 반복문을 사용합니다
문법
beginfor 카운터 인덱스 in 반복할 범위 loop
for 카운터 인덱스 in 반복할 범위 loop
실행문;
end loop;
end loop;
end;
/=======================================================
루프문을 종료시킬 조건을 따로 주지 않아도 됨.
왜? 범위를 주기 때문
예제1) 이번에는 for loop문으로 구구단 2단을 출력하시오
beginfor i in1 .. 9 loop
dbms_output.put_line( '2 x '|| i ||' = '||2* i );
end loop;
end;
/
예제2) 이번에는 for loop문으로 구구단 2단과 3단을 출력하시오
< 이중 루프문 사용 >beginfor i in2 .. 3 loop
for j in1 .. 9 loop
dbms_output.put_line( i ||' x '|| j ||' = '|| i * j );
end loop;
end loop;
end;
/==========================================================
바깥 쪽 루프문
i =2
내부 루프문
j =1
j =2
j =3
j =4
j =5
j =6
j =7
j =8
j =9end loop;
바깥 쪽 루프문
i =3
내부 루프문
j =1
j =2
j =3
j =4
j =5
j =6
j =7
j =8
j =9end loop;
end;
/----------------------------------------------------------< 이중 루프문을 사용하지 않는다면? >beginfor i in1 .. 9 loop
dbms_output.put_line( '2 x '|| i ||' = '||2* i );
end loop;
for i in1 .. 9 loop
dbms_output.put_line( '3 x '|| i ||' = '||2* i );
end loop;
end;
/===========================================================
이중 루프문을 사용하지 않으면 비효율적이게 됨
9단까지 한다면 루프문을 9번 써줘야됨
문제풀며 익히기
Q1.
구구단 2단부터 9단까지 전체를 다 출력하시오
출력예시
2 x 1 = 2
2 x 2 = 4
2 x 3 = 6
:
:
:
:
9 x 8 = 72
9 x 9 = 81
set serveroutput onbeginfor i in2 .. 9 loop
for j in1 .. 9 loop
dbms_output.put_line( i ||' x '|| j ||' = '|| i * j );
end loop;
end loop;
end;
/
Q2.
위의 코드를 수정해서 홀수 단만 출력하시오.
set serveroutput onbeginfor i in2 .. 9 loop
if mod(i,2) =1thenfor j in1 .. 9 loop
dbms_output.put_line( i ||' x '|| j ||' = '|| i * j );
end loop;
end if;
end loop;
end;
/
031. 이중 루프문에서 레이블 사용법
📖
이중 루프문에서 레이블 사용법
이중 루프문에서 레이블을 사용하게 되면 어느 코드까지가 밖의 루프문이고
어느 코드까지가 안쪽 루프문인지를 명확히 구분할 수 있습니다
즉, 코드를 이용해서 구분 가능(가시적이고 코드 수정이 편함)
예제1)
구구단 전체를 출력하는 basic loop문에서 레이블을 사용하시오
declare
v_dan number(10) :=2;
v_count number(10) :=1 ;
begin<<Outer_loop>> #대문자로 안써도 됨
loop
<<Inner_loop>>
loop
dbms_output.put_line( v_dan ||' x '|| v_count ||' = '|| v_dan * v_count );
v_count := v_count +1 ;
exit Inner_loop when v_count >9;
end loop Inner_loop;
v_dan := v_dan +1; -- 단을 증가 시키는 코드
v_count :=1; -- v_count 를 다시 1로 변경하는 코드
exit Outer_loop when v_dan >9; -- 밖의 루프문을 종료시키는 코드end loop Outer_loop;
end;
/--------------------
notepad같은 경우 더블클릭시 같은 단어가 표시되기 때문에 수정 편
문제풀며 익히기
Q1.
다음의 이중 for loop문에 레이블을 추가하세요.
beginfor i in2 .. 9 loop
for j in1 .. 9 loop
dbms_output.put_line( i ||' x '|| j ||' = '|| i * j );
end loop;
end loop;
end;
/
begin<<outer_loop>>for i in2 .. 9 loop
<<inner_loop>>for j in1 .. 9 loop
dbms_output.put_line( i ||' x '|| j ||' = '|| i * j );
end loop inner_loop;
end loop outer_loop;
end;
/
032. 예제32. PL/SQL 코드 디버깅하는 방법
📖
디버깅(debuging) 이란 ?
말 그대로 코드에서 버그(에러의 원인)를 찾아내는것 입니다.
cmd 창에서 돌렸을 때, 과정은 알 수 없이 결과만 나오기 때문에
천천히 보는 방법이 필요함. cmd창이 아닌 sqldeveloper에서 수행가능
프로시저를 만들어야 디버깅을 할 수 있음
예제) PL/SQL 코드를 디버깅 하기
1. sqldeveloper 를 실행하고 아래의 설정을 합니다.
도구 -> 환경설정 ->디버거-> DBMS_DEBUG 사용 체크
2. 아래의 pro77 프로시져(구구단 전체를 whileloop로 구성한)를 생성합니다.
createor replace procedure pro77
is
v_dan number :=2 ;
v_count number(10) :=1;
begin
while v_dan <=9 loop
while v_count <=9 loop
dbms_output.put_line( v_dan ||' x '|| v_count ||' = '|| v_dan * v_count );
v_count := v_count +1 ;
end loop;
v_dan := v_dan +1 ;
v_count :=1;
end loop;
end;
/>>Procedure PRO77이(가) 컴파일되었습니다.
3. sqldeveloper 에서 프로시져 pro77을 클릭하면 소스코드가 보이는데
여기서 중단점을 찍어야합니다.
중단점을 찍는 이유는 중단점에서 일시멈춤을하기 위해서 입니다.
4. pro77 을 클릭하고 오른쪽 마우스를 눌러서 디버그를 위한 컴파일을 합니다.
5. 무당벌레 아이콘을 누릅니다.
6. 오른쪽 아래에 텝에 스마트 데이터 텝을 클릭하고
7. F7 을 누르면서 디버깅을 진행합니다.
문제풀며 익히기
Q1.
구구단 2단을 출력하는 아래의 코드를 디버깅 하시오
set serveroutput ondeclare
v_count number(10) :=1 ;
begin
loop
dbms_output.put_line( '2 x '|| v_count ||' = '||2* v_count );
v_count := v_count +1 ;
exit when v_count >9;
end loop;
end;
/
createor replace procedure pro78
as
v_count number(10) :=1 ;
begin
loop
dbms_output.put_line( '2 x '|| v_count ||' = '||2* v_count );
v_count := v_count +1 ;
exit when v_count >9;
end loop;
end;
/set serveroutput onexecute pro78;
6장. PL/SQL 에서의 조합 변수
💡
PL/SQL 변수의 종류 3가지 ?
스칼라 변수 : 단일값 만 저장
조합변수 : 여러개값을 저장 (레코드 변수, 컬렉션 )
바인드 변수 : 호스트 변수
033. PL/SQL 레코드 변수란
📖
PL/SQL 레코드 변수란
" 스칼라 변수(값을 한개만 저장 가능)와는 다르게 여러개의 값을 저장하는 변수 “
PL/SQL 에서의 조합 변수의 종류 2가지?
PL/SQL 레코드 : 가로로 데이터가 담기는 변수
PL/SQL 컬렉션 : 세로로 데이터가 담기는 변수
PL/SQL 레코드 생성 예제)
사원번호를 물어보게하고 사원번호를 입력하면 해당 사원의
사원이름, 월급과 직업이 출력되는 PL/SQL 코드를 작성하시오
set serveroutput onset verify off
accept p_empno prompt '사원번호를 입력하세요 ~'declare
v_empno emp.empno%type :=&p_empno;
#v_empno는 값을 하나만 담는 스칼라변수
type t_rec is record
( v_ename emp.ename%type,
v_sal emp.sal%type,
v_job emp.job%type );
v_myrec t_rec ;
beginselect ename, sal, job into v_myrec
from emp
where empno = v_empno;
v_myrec.v_sal :=9000;
#레코드 변수 안의 값을 9000으로 바꿔버릴 수 있는 것
dbms_output.put_line ( v_myrec.v_ename ||' '||
to_char(v_myrec.v_sal) ||' '||
v_myrec.v_job ) ;
#문자끼리 연결하기 때문에 명시적으로 to_char해줌
end;
/=========================================================
type [레코드 타입 이름] is record
( [첫번째 방에 들어갈 값의 이름] emp.ename%type,
[두번째 방에 들어갈 값의 이름] emp.sal%type,
[세번째 방에 들어갈 값의 이름] emp.job%type );
[레코드 변수 이름] [레코드 타입 이름] ;
V_MGREC
-------------------------------||||-------------------------------
v_ename v_sal v_job
=> 즉, 타입을 먼저 만들어줘야 레코드 변수를 생성할 수 있음
------------------------
v_myrec.v_sal :=9000;
위와 같이 바꾼 이유는 해당 값을 암호화 하기 위해
원래 값과 다른 값을 출력시켜버린 것
문제풀며 익히기
Q1.
사원번호를 물어보게 하고 사원번호를 입력하면 해당 사원의 직업, 월급과 커미션이 출력되는 PL/SQL 코드를 작성하는데 커미션은 무조건 0으로 출력되게 하시오
출력예시
사원 번호를 입력하세요 ~ 7788
직업은 ANALYST
월급은 3000
커미션 0
set serveroutput onset verify off
accept p_empno prompt '사원번호를 입력하세요 ~'declare
v_empno emp.empno%type :=&p_empno;
type t_rec is record
( v_job emp.job%type,
v_sal emp.sal%type,
v_comm emp.comm%type );
v_myrec t_rec ;
beginselect job, sal, comm into v_myrec
from emp
where empno = v_empno;
v_myrec.v_comm :=0;
dbms_output.put_line ( v_myrec.v_job ||' '||
to_char(v_myrec.v_sal) ||' '||
to_char(v_myrec.v_comm) ) ;
end;
/=================================================================
comm은 노출되면 안되는 값으로 선정되었기 때문에
값을 모두 0으로 출력하게 함
출력시 일관성 있기 문자형태로 연결연산자 사용하기 위해 to_char해줌
--------------------------------------------------------------
gpt의 말
set serveroutput onset verify off
accept p_empno prompt '사원번호를 입력하세요 ~'declare
v_empno emp.empno%type :=&p_empno;
type t_rec is record
( v_job emp.job%type,
v_sal emp.sal%type,
v_comm emp.comm%type );
v_myrec t_rec ;
beginselect job, sal, comm
into v_myrec.v_job, v_myrec.v_sal, v_myrec.v_comm
from emp
where empno = v_empno;
v_myrec.v_comm :=0;
dbms_output.put_line ( v_myrec.v_job ||' '||
to_char(v_myrec.v_sal) ||' '||
to_char(v_myrec.v_comm) ) ;
end;
/
034. PL/SQL 레코드 변수의 이점
📖
PL/SQL 레코드 변수의 이점
%rowtype을 이용해서 레코드 변수를 선언하면 다음의 기능을 이용할 수 있습니다.
<%ROWTYPE 사용 안할 때 >
type t_rec is record
( v_job emp.job%type,
v_sal emp.sal%type,
v_comm emp.comm%type );
v_myrec t_rec ;
=======================================================================<%ROWTYPE 사용하여 쉽게 만드는 법 >1.select*into 레코드 변수명 from emp where empno = v_empno;
2.update emp
setrow= 레코드 변수명 #해당 row전체를 한번
where empno = v_empno;
3.insertinto emp values 레코드 변수명; #한번에 insert
예제 )
1. emp 테이블과 똑같은 emp_test 라는 테이블로 생성하는데
데이터는 가져오지 말고 구조만 구성합니다.
createtable emp_test
asselect*from emp
where1=2;
2. 사원번호를 물어보게하고 사원번호를 입력하면 해당 사원의
모든 데이터가 emp_test 테이블에 입력되는 PL/SQL 코드를 작성하시오
(데이터 이행을 쉽게 할 수 있음)
데이터 이행(ETL)
emp -------------------------> emp_test
accept p_empno prompt '사원번호를 입력하세요 ~'declare
v_empno emp.empno%type :=&p_empno; #스칼라변수
v_emp emp%rowtype; -- emp 테이블의 컬럼의 모든 데이터 타입을 그대로 따르겠다.
#레코드변수
beginselect*into v_emp
from emp
where empno = v_empno;
v_emp.sal :=0 ;
insertinto emp_test values v_emp;
end;
/select*from emp_test;
================================================< 설명 >
v_emp emp%rowtype; #레코드 변수
=> 컬럼 8개를 만들 수 있는 레코드블록들이 만들어짐
=> 각 블록 칸들의 이름은 EMP테이블의 컬럼 명들임
=> emp 테이블의 컬럼의 모든 데이터 타입을 그대로 따르겠다는 것.
select*into v_emp
=> 레코드 변수로 인해 컬럼명을 하나하나 쓸 필요 없이 * 사용 가능해짐
insertinto emp_test values v_emp;
=> 레코드 변수 v_emp에 있는 값이 한번에 emp_test 테이블로 insert
문제풀며 익히기
Q1.
dept 테이블의 구조를 가지고 있는 dept_backup 테이블을 다음과 같이
생성하고 부서번호를 물어보게 하고 부서번호를 입력하면
해당 부서번호의 모든 행을 dept 테이블에서 찾아서
dept_backup 테이블에 입력되게 PL/SQL 코드를 작성하세요
< 테이블 구조 생성 >createtable dept_backup
asselect*from dept
where1=2;
----------------------------------< 정답 >
accept p_deptno prompt '부서번호를 입력하세요 ~'declare
v_deptno dept.deptno%type :=&p_deptno;
v_dept dept%rowtype; -- emp 테이블의 컬럼의 모든 데이터 타입을 그대로begin-- 따르겠다.select*into v_dept
from dept
where deptno = v_deptno;
insertinto dept_backup values v_dept;
end;
/
035. 레코드 변수를 사용하여 DML 작업 쉽게 하기
📖
emp ----------------------> emp_test2 에 update 수행하기
예제)
emp 테이블과 똑같은 구조를 갖는 emp_test2 테이블을 생성하시오 !
그리고 emp 테이블에서 사원번호만 emp_test2 테이블에 입력하세요.
createtable emp_test2
asselect*from emp where1=2;
insertinto emp_test2(empno)
select empno
from emp;
SQL>select empno, ename, sal
2from emp_test2;
예제2)
사원번호를 입력받아 해당 사원번호의 데이터를 emp 테이블에서 불러와서
emp_test2 테이블에 입력되게 PL/SQL을 작성하시오
accept p_empno prompt '사원번호를 입력하세요 !'declare
v_empno emp.empno%type :=&p_empno;
v_emp emp%rowtype ;
beginselect*into v_emp
from emp
where empno = v_empno;
v_emp.sal :=0 ;
update emp_test2
setrow= v_emp
where empno = v_empno;
commit; #sql developer에서 보고싶다면 커밋해줘야함
end;
/========================================================
# SETROW
V_EMPNO의 값을 emp_test2에 한번에 UPDATErow사용해서 레코드변수를 이용해 한번에 update
문제풀며 익히기
Q1.
먼저 salgarde 테이블의 구조를 가지고 salgrade_test 테이블을 생성하세요
그리고 grade 컬럼에 1~5까지 입력하세요
암시적 커서 : 오라클이 알아서 자체적으로 할당한 메모리.
이 메모리는 PL/SQL에서 DML 문을 처리했을때
몇건의 행을 처리했는지 그 데이터를 저장할 메모리.
예: SQL%rowcount, SQL%found, SQL%notfound
명시적 커서 : PL/SQL 프로그래머가 프로그램 내에서 사용할
데이터를 미리 SQL 로 SELECT 해서 올려놓은 메모리 영역
(프로그래머가 사용하기 위해 직접 만든것)
명시적 커서 사용 순서
커서를 먼저 선언을 합니다.
커서를 엽니다.
커서내의 데이터를 첫행부터 하나씩 fetch 합니다.
더이상 fetch 할께 없을때까지 fetch 합니다.
커서를 닫습니다.
*fetch : 데이터를 한 행 씩 읽어오는 것
★ 꼭 알아야하는 중요 파라미터
"db 에서 열 수 있는 최대 커서의 갯수”
< 방법 1>SQL>showparameter cursors
NAME TYPE VALUE------------- ------------------ ------------------
open_cursors integer300
session_cached_cursors integer50SQL>select name, valuefrom v$parameterwhere name like'%cursor%';
!주의!
open_cursor 가 300개 입니다. 300 개를 초과해서 커서를 열려고 하면
더 이상 열 수 가 없게 됩니다.
반드시 커서를 선언하고 열어서 썼으면 커서를 닫는 코드를 써주셔야 합니다.
예제)
커서선언 --> 커서오픈 --> 커서패치 --> 커서 닫기
accept p_deptno prompt '부서번호를 입력하시오 ~ 'declarecursor emp_cursor is-- 커서 선언 #emp_cursor는 커서이름select ename, sal, job, deptno
from emp
where deptno =&p_deptno ;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_job emp.job%type;
v_deptno emp.deptno%type;
beginopen emp_cursor; -- 커서 열기
loop
fetch emp_cursor into v_ename, v_sal, v_job, v_deptno; -- 커서 패치
exit when emp_cursor%notfound;
dbms_output.put_line( v_ename ||' '|| v_sal ||' '|| v_job ||' '|| v_deptno);
end loop;
close emp_cursor; --커서 닫기end;
/==============================================================begin절에서
basic loop를 사용해 fetch 반복시킴
--커서 패치(커서에 있는 데이터들을 한 행씩 패치 시킴)fetch emp_cursor into v_ename, v_sal, v_job, v_deptno;
KING 5000 PRESIDENT 10 패치됨
CLARK 2450 MANAGER 10 패치됨 (loop에 의해 반복)
MILLER 1300 CLERK 10 패치됨 (loop에 의해 반복)
--exit when
exit when emp_cursor%notfound;
=> emp_cursor에서 더 이상 데이터가 발견되지 않을 때 종료
=> 즉, 더 이상 fetch할게 없으면 종료하라
--end loop;close emp_cursor; --커서 닫기
나중에 문제가 될 수 있기 때문에 열고 닫는걸 짝꿍으로 수행하는게 좋음
=====================================================< 결과 예쁘게 출력 >
dbms_output.put_line( v_ename ||' '|| v_sal ||' '|| v_job ||' '|| v_deptno);
을 공백 대신
dbms_output.put_line( v_ename || chr(9) || v_sal || chr(9) || v_job || chr(9) || v_deptno);
로 바꾸면 됨
부서번호 10번인 경우 아래 결과를 커서 선언으로 메모리에 올려놓은 것.
결과
예쁘게 출력
문제풀며 익히기
Q1.
직업을 물어보게 하고 직업을 입력하면 해당 직업을 갖는 사원들의 이름과
월급과 직업이 출력되게 PL/SQL 코드를 작성하시오
accept p_job prompt '직업을 입력하시오 ~ 'declarecursor emp_cursor is-- 커서 선언select ename, sal, job
from emp
where job ='&p_job' ;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_job emp.job%type;
beginopen emp_cursor; -- 커서 열기
loop
fetch emp_cursor into v_ename, v_sal, v_job; -- 커서 패치
exit when emp_cursor%notfound;
dbms_output.put_line( v_ename || chr(9) || v_sal || chr(9) || v_job || chr(9) );
end loop;
close emp_cursor;
end;
/
ctrl + H 눌러 바꾸기
037. 레코드(record) 변수를 사용한 커서문 작성하기
📖
PL/SQL 레코드 변수란
" 스칼라 변수(값을 한개만 저장 가능)와는 다르게 여러개의 값을 저장하는 변수 “
문법
1. 레코드 변수를 사용안했을때 코드 :
accept p_deptno prompt '부서번호를 입력하시오 ~ 'declarecursor emp_cursor is-- 커서 선언select ename, sal, job, deptno
from emp
where deptno =&p_deptno ;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_job emp.job%type;
v_deptno emp.deptno%type;
--------------------------------------------------------
부서번호가 10번일 때 10번인 사원들의 이름,월급,직업,부서번호를
커서 메모리에 올려놓는 것.
========================================================2. 레코드 변수를 사용했을때의 코드:
accept p_deptno prompt '부서번호를 입력하시오 ~ 'declarecursor emp_cursor is-- 커서 선언select ename, sal, job, deptno
from emp
where deptno =&p_deptno ;
emp_record emp_cursor%rowtype;
----------------------------------------------------------
emp_record emp_cursor%rowtype;
커서의 데이터 유형을 그대로 따르겠다는 것.
그리고
행이 4개이므로 4개의 데이터를 담을 수 있는 블록 4가지가
레코드 변수에 만들어짐
예제)
accept p_deptno prompt '부서번호를 입력하시오 ~ 'declarecursor emp_cursor is-- 커서 선언select ename, sal, job, deptno
from emp
where deptno =&p_deptno ;
emp_record emp_cursor%rowtype;
beginopen emp_cursor; -- 커서 열기
loop
fetch emp_cursor into emp_record; -- 커서 패치
exit when emp_cursor%notfound;
dbms_output.put_line( emp_record.ename || chr(9) ||
emp_record.sal || chr(9) ||
emp_record.job || chr(9) ||
emp_record.deptno);
end loop;
close emp_cursor;
end;
/
문제풀며 익히기
Q1.
입사한 년도를 물어보게하고 입사한 년도를 입력하면 해당 입사한 년도에 입사한 사원들의 이름과 월급과 입사일이 출력되는 PL/SQL 코드를 레코드 변수를 이용한 커서루프문으로 작성하시오
출력예시 : 입사한 년도를 4자리로 입력하시오 ~
set serveroutput on
accept p_year prompt '입사한 년도를 4자리 입력하시오 ~ 'declarecursor emp_cursor is-- 커서 선언select ename, sal, hiredate
from emp
where to_char(hiredate,'RRRR') ='&p_year' ;
emp_record emp_cursor%rowtype;
beginopen emp_cursor; -- 커서 열기
loop
fetch emp_cursor into emp_record; -- 커서 패치
exit when emp_cursor%notfound;
dbms_output.put_line( emp_record.ename || chr(9) ||
emp_record.sal || chr(9) ||
emp_record.hiredate);
end loop;
close emp_cursor;
end;
/
038. 레코드를 사용한 커서 for loop문
📖
레코드를 사용한 커서 for loop문
기존 커서문: 1.커서 선언 --> 2. 커서 오픈 --> 3. 커서 패치 --> 4. 커서 닫기
레코드를 사용한 커서 for loop문 : 1. 커서 선언
즉, 커서 오픈, 커서패치, 커서 닫기 모두생략됨
예제 1) 기존 PL/SQL 커서문
직업을 물어보게하고, 직업을 입력하면, 해당 지업인 사원들의 이름, 월급, 직업을 출력하는 코드
accept p_job prompt '직업을 입력하시오 ~ 'declarecursor emp_cursor is-- 커서 선언select ename, sal, job
from emp
where job =upper('&p_job') ;
emp_record emp_cursor%rowtype;
beginopen emp_cursor; -- 커서 열기
loop
fetch emp_cursor into emp_record; -- 레코드 변수에 커서 패치
exit when emp_cursor%notfound;
dbms_output.put_line( emp_record.ename || chr(9) ||
emp_record.sal || chr(9) ||
emp_record.job );
end loop;
close emp_cursor;
end;
/==========================SQL>@P
직업을 입력하시오 ~ CLERK
JAMES 950 CLERK
SMITH 800 CLERK
ADAMS 1100 CLERK
MILLER 1300 CLERK
PL/SQL 처리가 정상적으로 완료되었습니다.
예제 2) 커서 for 루프문 실습 예제
set verify off
accept p_job prompt '직업을 입력하세요 'declarecursor emp_cursor is-- 커서 선언select ename, sal, job
from emp
where job=upper('&p_job');
beginfor emp_record in emp_cursor loop
dbms_output.put_line( emp_record.ename || chr(9) ||
emp_record.sal || chr(9) ||
emp_record.job );
end loop;
end;
/======================================================
레코드 변수를 선언해주지 않아도 BEGIN절에서 바로 사용 가능
따라서 emp_record를 바로 사용했음
아래는 job이 clerk일 때 커서 메모리에 올라가있는 데이터
결과
문제풀며 익히기
Q1.
위의 코드를 프로시져로 만들어 다음과 같이 수행되게 하시오
수행할 사항 : exec pro_job('SALESMAN');
프로시저로 만들어야 data base에 pl/sql코드가 저장이 되기 때문!
프로시저 이름만 불러서 호출할 수 있는 장점이 있음.
------------------------------------------------------createor replace procedure pro_job
(p_job emp.job%type)
iscursor emp_cursor is-- 커서 선언select ename, sal, job
from emp
where job=upper(p_job); -- &을 없애줘야함beginfor emp_record in emp_cursor loop
dbms_output.put_line( emp_record.ename || chr(9) ||
emp_record.sal || chr(9) ||
emp_record.job );
end loop;
end;
/exec pro_job('SALESMAN');
======================================================
레코드 변수를 선언해주지 않아도 BEGIN절에서 바로 사용 가능
따라서 emp_record를 바로 사용했음
에러시
show err을 하면 오류 확인 가능 (cmd창 명령어)
Q2.
부서번호를 입력해서 프로시저를 실행하면, 해당 부서번호에 속한 사원들의 이름과 월급과 부서번호가 출력되는 프로시져를 생성하시오.
실행 문장 : exec pro_deptno(10);
createor replace procedure pro_deptno
( p_deptno emp.deptno%type )
iscursor emp_cursor isselect ename, sal, deptno
from emp
where deptno = p_deptno;
beginfor emp_record in emp_cursor loop
dbms_output.put_line( emp_record.ename || chr(9) ||
emp_record.sal || chr(9) ||
emp_record.deptno );
end loop;
end;
/
039. 서브쿼리를 사용하는 커서 for 루프문 작성하기
📖
서브쿼리를 사용하는 커서 for 루프문 작성하기
💡
서브쿼리를 사용하는 커서 for 루프문 작성하기
기존 커서문: 1.커서 선언 --> 2. 커서 오픈 --> 3. 커서 패치 --> 4. 커서 닫기
레코드를 사용한 커서 for loop문 : 1. 커서 선언
즉, 커서 오픈, 커서패치, 커서 닫기 모두생략됨
커서 선언만 해주면 오라클이 암시적으로 알아서 오픈, 패치, 닫기
서브쿼리를 사용하는 커서 for 루프문 : x
즉, 선언조차 하지 않고 사용 가능
⇒ ※ 점점 코드가 간결하고 단순화 되고 있습니다.
즉, 굳이 어렵게 하려고 하지 말고 DB 프로그래밍을 하라는 말.
PL/SQL이 DB프로그래밍의 끝판왕
예제)
부서번호를 물어보게하고 부서번호를 입력하면 해당 부서번호인
사원들의 이름과 월급과 부서번호가 출력되게 커서 for loop문을
작성하시오 !
출력예시 ; 부서번호를 입력하세요 ~ 10
set verify off
set serveroutput on
accept p_deptno prompt '부서번호를 입력하세요 'beginfor emp_record in (select ename, sal, deptno
from emp
where deptno=&p_deptno ) loop
dbms_output.put_line( emp_record.ename || chr(9) ||
emp_record.sal || chr(9) ||
emp_record.deptno );
end loop;
end;
/======================================================================declare절의 커서 선언이 없어지고,
커서 선언에 필요한 select 문장을 (커서 선언하는 select문장)
begin절에 바로 넣고 emp_record 라는 레코드 변수를 바로 선언해주며 값을 넣음
즉, 노란 형관펜 부분이 오라클 메모리로 올라가는 것.
올라갈 때 부서번호 10번이 들어왔다면, 아래 cmd창 결과와 같이
부서번호 10번인 사원들이 오라클 메모리로 올라가는 것
올라간 후 바로
for emp_record in으로 한 행 fetch후 출력되고
for emp_record in으로 한 행 fetch후 출력되고
for emp_record in으로 한 행 fetch후 출력된 후 패치할게 없으면 end loop;
chr(9) => tab키 역
문제풀며 익히기
Q1.
예제의 PL/SQL을 프로시져로 생성하여 다음과 같이 수행되게하시오
실행 문 : SQL> exec pro_deptno(10);
createor replace procedure pro_deptno
( p_deptno emp.deptno%type )
isbeginfor emp_record in (select ename, sal, deptno
from emp
where deptno= p_deptno ) loop
dbms_output.put_line( emp_record.ename || chr(9) ||
emp_record.sal || chr(9) ||
emp_record.deptno );
end loop;
end;
/exec pro_deptno(10);
=================================================================is 또는 as를 넣어주고
치환변수를 날려줘야함. 즉, &제거하기
(프로시져 생성시 치환변수 사용안됨)
자바 프로그래머들이 procedure을 자주 사용.
그 프로시져 관리는 DBA들이 하고 있음
Q2.
아래와 같이 직업을 입력해서 프로시져를 실행하면 해당 직업인 사원들의 이름, 월급, 직업이 출력되게 프로시저를 실행하시오.
실행 문 : SQL> exec pro_job(’SALESMAN’);
createor replace procedure pro_job
( p_job emp.job%type )
isbeginfor emp_record in (select ename, sal, job
from emp
where job= p_job ) loop
dbms_output.put_line( emp_record.ename || chr(9) ||
emp_record.sal || chr(9) ||
emp_record.job );
end loop;
end;
/=====================================================
위 코드는 DBA 관리 수업시 자주 사용할 예정
040. where current of 절 사용법
📖
where current of 절 사용법
update set 문에서 사용하는 절
Where current of 사용 이유
방금 fetch해온 행의 data를 갱신
- 이 절을 사용하지 않으면
예를들어 where ename = v_ename 이런식으로 해줘야하는데,
동명이인이 있을경우 이상해짐
- 따라서 forupdate 와 함께 wherecurrentof를 사용해주는게 좋음
문법
update emp
set grade = v_grade
wherecurrentof emp_cursor;
=====================================================
emp_cursor에서 지금 패치한 행의 데이터를 갱신하라는 의미
예제 1) emp 테이블에 grade 라는 컬럼을 추가하시오
altertable emp
add grade varchar2(10);
예제 2)
emp 테이블에 grade 라는 컬럼에 다음의 조건에 해당되는 데이터로 값을 갱신하시오 (커서문을 이용한 PL/SQL로 자동으로 등급 값이 갱신되도록 하고 싶음)
조건
월급이 3000 이상이면 A 등급
월급이 2000 이상이면 B 등급
월급이 1000 이상이면 C 등급
나머지 월급은 D 등급으로 갱신
declarecursor emp_cursor is-- 커서 선언select empno, ename, sal
from emp
forupdate; -- 내가 갱신하고 있는 데이터를 -- 누구도 갱신하지 못하도록 LOCK을 거는 것-- FOR UPDATE를 반드시 선언해줘야 -- 아래의 WHERE CURRENT OF를 사용 할 수 있음
emp_record emp_cursor%rowtype; -- 레코드 변수 생성
v_grade varchar2(5); --스칼라 변수 생성beginopen emp_cursor ; -- 커서 열기(메모리에 올라간 것 열기)
loop
fetch emp_cursor into emp_record; -- fetch
exit when emp_cursor%notfound;
if emp_record.sal >=3000then
v_grade :='A';
elsif emp_record.sal >=2000then
v_grade :='B';
elsif emp_record.sal >=1000then
v_grade :='C' ;
else
v_grade :='D';
end if;
update emp
set grade = v_grade
wherecurrentof emp_cursor; -- 매우 중요end loop;
commit;
close emp_cursor;
end;
/===============================================================FORUPDATE;
를 사용하여 LOCK을 걸어둠. 즉, EMP의 총 14개 행이 잠겨버림
emp_record emp_cursor%rowtype;
레코드 변수를 선언하는데, emp_cursor의 type을 따름
fetch emp_cursor into emp_record;
커서(emp_cursor)에 있는 데이터를 레코드(emp_record)에 넣음
exit when emp_cursor%notfound;
커서에 더이상 데이터가 없으면 종료
update emp
set grade = v_grade
wherecurrentof emp_cursor; -- 매우 중요
방금 fetch해온 행의 data를 갱신
즉, 방금 king의 데이터를 fetch에 의해 가져왔으면,
방금 fetch된 행의 v_grade 값을 갱신하겠다는 의미.
=>
if문에 의해 해당된 조건인 emp_record.sal >=3000에 해당하므로
v_grade에 'A'가 할당되며 그 할당 된 값으로 갱신하겠다는 의미.
문제풀며 익히기
Q1.
방금 수행했던 예제2번을 프로시져로 만들어서 다음과 같이 직업을 입력하고 실행하면, 해당 직업인 사원들의 데이터만 아래의 월급의 조건에 맞춰 grade 값이 갱신되게하시오.
실행 문 : exec cursor_job('SALESMAN');
조건 :
월급이 3000 이상이면 A 등급
월급이 2000 이상이면 B 등급
월급이 1000 이상이면 C 등급
나머지 월급은 D 등급으로 갱신
SQL>update emp
2set grade =null;
SQL>commit;
-------------------------------------------------------------------createor replace procedure cursor_job
(p_job emp.job%type)
iscursor emp_cursor is-- 커서 선언select empno, ename, sal
from emp
where job = p_job #where절에 조건 추가해주면 됨
forupdate; -- 내가 갱신하고 있는 데이터를 -- 누구도 갱신하지 못하도록 LOCK을 거는 것-- FOR UPDATE를 반드시 선언해줘야 -- 아래의 WHERE CURRENT OF를 사용 할 수 있음
emp_record emp_cursor%rowtype; -- 레코드 변수 생성
v_grade varchar2(5); --스칼라 변수 생성beginopen emp_cursor ; -- 커서 열기(메모리에 올라간 것 열기)
loop
fetch emp_cursor into emp_record; -- fetch
exit when emp_cursor%notfound;
if emp_record.sal >=3000then
v_grade :='A';
elsif emp_record.sal >=2000then
v_grade :='B';
elsif emp_record.sal >=1000then
v_grade :='C' ;
else
v_grade :='D';
end if;
update emp
set grade = v_grade
wherecurrentof emp_cursor; -- 매우 중요end loop;
commit;
close emp_cursor;
end;
/
8장. 예외처리
041. 예외처리란 무엇인가?
📖
예외처리란 무엇인가?
예외 처리(exception handling)란, 프로그래머가 예기치못한 예외의 발생에
미리 대처하는 코드를 작성하는 것으로,
실행중인 프로그램의 비정상적인 종료를 막고 상태를 정상상태로 유지하는 것이다.
⇒ 즉, 코드에 이상은 없는데, PL/SQL에 입력된 데이터에 문제가 있는 경우
미리 대처하는 코드를 작성해. 비정상적 종료를 막고, 정상상태로 유지
문법
declare
변수 선언 ;
예외 선언 ;
begin
실행문;
예외처리문;
end;
/=======================================--declare 절에서 예외선언을 해줘야 begin절에서 예외처리문 작성 가능
실습
▣ 실습1. —예외처리 하기 전 PL/SQL 코드
이름을 입력하면 해당 사원의 월급이 출력되는 PL/SQL코드를 작성하시오 !
set verify off
set serveroutput on
accept p_ename prompt '이름을 입력하세요 ~'declare
v_ename emp.ename%type :=upper('&p_ename');
v_sal emp.sal%type ;
beginselect sal into v_sal
from emp
where ename = v_ename;
dbms_output.put_line( v_sal );
end;
/=============================-- 존재하는 이름 입력시SQL>@p
이름을 입력하세요 ~scott
3000--존재하지 않는 입력시SQL>@p
이름을 입력하세요 ~aaaa
!!ERROR!!declare*1행에 오류:
ORA-01403: 데이터를 찾을 수 없습니다.
ORA-06512: 5행
=> 코드문제가 아니라 입력된 데이터에 문제가 있을 때 발생하는 오류
▣ 실습2.
MARTIN 의 이름을 SCOTT 으로 변경하고 실습1번의 PL/SQL 코드를
수행하는데 이름을 SCOTT 으로 입력해보세요.
update emp
set ename='SCOTT'where ename='MARTIN';
commit;
----------------------------------------------SQL>selectcount(*)
2from emp
3where ename ='SCOTT';
COUNT(*)
----------2===============================================--SCOTT 입력해보기SQL>@P
이름을 입력하세요 ~SCOTT
!!ERROR!!declare*1행에 오류:
ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다
ORA-06512: 5행
=> 이 에러 메시지를 고객의 눈 높이에 맞춰서 바꿔주는게 예외처리
▣ 실습3. 위의 경우를 대비하기 위한 예외처리를 하세요.
set verify off
set serveroutput on
accept p_ename prompt '이름을 입력하세요 ~'declare
v_ename emp.ename%type :=upper('&p_ename');
v_sal emp.sal%type ;
beginselect sal into v_sal
from emp
where ename = v_ename;
dbms_output.put_line( v_sal );
EXCEPTION ---예외 처리절when too_many_rows THEN
dbms_output.put_line('해당 사원이름이 여러명 있습니다. 고객센터로 문의하세요' );
end;
/--------------------------------------------------------------SQL>@P
이름을 입력하세요 ~SCOTT
해당 사원이름이 여러명 있습니다. 고객센터로 문의하세요
===============================================================
too_many_rows
: 오라클 개발자가 만들어놓은 예외 함수로
여러개가 스칼라 변수에 담기려고 할 때 발생하는 에러 예외 함수
아래의 PL/SQL 코드를 수행하는데 사원 이름을 물어볼때 없는 사원 이름을
넣고 실행하세요
set verify off
set serveroutput on
accept p_ename prompt '이름을 입력하세요 ~'declare
v_ename emp.ename%type :=upper('&p_ename');
v_sal emp.sal%type ;
beginselect sal into v_sal
from emp
where ename = v_ename;
dbms_output.put_line( v_sal );
end;
/----------------------------------------SQL>@P
이름을 입력하세요 ~aaaa
declare*1행에 오류:
ORA-01403: 데이터를 찾을 수 없습니다.
ORA-06512: 5행
▣ 실습2.
위의 코드를 수정하여, 없는 사원 이름을 넣으면, 에러가 발생하지 않고 , 예외 처리를 해서 해당 사원은 없습니다. 라는 메세지가 화면에 출력되게 하시오.
set verify off
set serveroutput on
accept p_ename prompt '이름을 입력하세요 ~'declare
v_ename emp.ename%type :=upper('&p_ename');
v_sal emp.sal%type ;
beginselect sal into v_sal
from emp
where ename = v_ename;
dbms_output.put_line( v_sal );
EXCEPTION
when no_data_found then
dbms_output.put_line('해당 사원은 없습니다.');
end;
/-------------------------------------------------------SQL>@p
이름을 입력하세요 ~aaaa
해당 사원은 없습니다.
문제풀며 익히기
Q1.
다음과 같이 부서번호를 물어보게 하고 부서번호를 입력하면 해당 부서번호의
부서위치가 출력되는 PL/SQL 코드를 실행하는데 없는 부서번호를 입력하면
해당 부서번호는 없는 부서번호입니다. 라는 메세지가 출력되게 예외처리를 하세요.
set serveroutput on
accept p_deptno prompt '부서번호를 입력하세요 ~'declare
v_deptno dept.deptno%type :=&p_deptno;
v_loc dept.loc%type ;
beginselect loc into v_loc
from dept
where deptno = v_deptno;
dbms_output.put_line( v_loc );
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('해당 부서번호는 없는 부서번호입니다.');
end;
/
043. 미리 정의하지 않은 오라클 예외처리
📖
미리 정의하지 않은 오라클 예외처리 (OCP시험에 자주 출제)
오라클에서 미리 정의 하지 않은 예외처리 인 경우는
직접 예외처리를 해야하는데
pragma exception_init 를 이용해서 예외처리 하면 됩니다.
declare
[정의할 예외 이름] exception; --e_insert_except라는 예외를 선언하겠다는 것
pragma exception_init([예외이름], [연결해줄 에러번호]);
--예외와 에러번호 연결
accept p_empno prompt '사원번호를 입력하세요~ '
accept p_ename prompt '사원이름을 입력하세요~ '
accept p_sal prompt '월급을 입력하세요~ 'DECLARE
E_EXCEPT1 EXCEPTION;
PRAGMA EXCEPTION_INIT( E_EXCEPT1, -00001);
BEGINinsertinto emp(empno, ename, sal)
values(&p_empno, '&p_ename', &p_sal );
commit; -- exception 전에 넣기-- 그래야 insert에서 에러가 발생했을 때 commit을 수행하지 않고-- 바로 exception절로 뛰어넘을 수 있음
EXCEPTION
WHEN E_EXCEPT1 THEN
DBMS_OUTPUT.PUT_LINE('해당 사원번호는 기존에 존재합니다.');
end;
/
044. 사용자 정의 예외처리
📖
사용자 정의 예외처리
PL/SQL 프로그램을 개발하다보면 오라클이 제공하는 예외 만으로는
다양한 종류의 예외를 다 표현할 수 없습니다.
오라클 에러가 발생하는 것은 아닌데, 특정 부분을 예외처리 하고 싶을 때
이때, 직접 예외를 정의하여 사용하는 예외를 사용자 정의 예외 라고 합니다
if [조건] THEN
raise [예외명];
end if;
예제)
set serveroutput on
accept p_deptno prompt '부서번호를 입력하세요 ~ 'declare
v_deptno number :=&p_deptno;
e_invalid exception; -- 예외 선언beginupdate dept
set dname ='testing'where deptno = v_deptno;
if SQL%NOTFOUND THEN--암시적 커서..에서 본 조건
raise e_invalid;
end if;
commit;
exception
when e_invalid then
dbms_output.put_line('부서번호가 없어요');
end;
/================================================================
if SQL%NOTFOUND THENUPDATE문에 의해서 0건의 행이 갱신되었다고 나오면 SQL%NOTFOUND가 TRUE가 됨
즉, 없는 부서번호 90번을 넣으면 90번은 DEPTNO에 없기 때문에
0건의 행이 갱신되었다고 나와서
SQL%NOTFOUND =TRUE가 되는 것
이게 TRUE라면 예외처리 하도록 만들어진 SQL문임.
없는 번호를 넣으면 0 건의 행이 갱신되지,
오라클 에러가 발생하는 것이 아님 => 따라서 사용자 정의 예외처리 해준 것
문제풀며 익히기
Q1.
이름을 물어보게하고 이름을 입력하면 해당 사원의 월급이 출력되는 PL/SQL 코드를 작성하세요.
그런데 월급이 3000 이상이면 해당 사원의 월급은 볼 수 없니다.
라는 메세지가 출력되게 사용자 정의 예외처리를 생성하세요.
set serveroutput on
accept p_ename prompt '이름을 입력하세요 ~ 'declare
v_ename emp.ename%type :=upper('&p_ename');
v_sal emp.sal%type;
e_invalid exception;
beginselect sal into v_sal
from emp
where ename = v_ename;
if v_sal >=3000THEN
raise e_invalid;
end if;
dbms_output.put_line( v_sal );
commit; --예외 전에 commit;
exception
when e_invalid then
dbms_output.put_line('해당 사원의 월급은 볼 수 없습니다.');
end;
/=================================================================< 차이 >
if v_sal >=3000THEN
dbms_output.put_line('해당 사원의 월급은 볼 수 없습니다.');
위와 같이 조건으로 그냥 특정 조건이면 메세지를 출력하도록 할 수 있지만,
exception의 장점은 중간에 여러 코드 줄을 실행하지 않고
skip한 후 진행할 수 있다.
045. 예외트랩함수(SQLCODE, SQLERRM)
📖
예외트랩함수
PL/SQL 프로그램에서 발생하는 오류 번호와 오류 메세지를 잡아내는 함수
예외트랩 함수의 종류 2가지
SQLCODE : PL/SQL에서 발생하는 에러 번호를 트랩(trap) 하는 함수
PL/SQL에서 오류번호를 잡아내는 함수
SQLERRM : PL/SQL에서 발생하는 에러 메세지를 트랩(trap) 하는 함수
EX) 쿠팡
오늘 총 매출액 -> 매출 테이블
- 프로시저
- 스케줄
이 두가지를 통해 자동화 해놓는데, 만약 밤 사이 에러가 발생했다면..?
빠르게 원인 파악을 해줘야함.
원인 파악을 했을 때 어떤 에러가 발생했는지 잡아내는 함수를 사용해서
각각 테이블에 입력하게 하는 코드가 필요함
▣ 관련 실습
1. 직업과 직업별 토탈월급을 저장할 테이블을 생성합니다.
createtable daily_machul
( job varchar2(10),
sumsal number(10) );
2. 사원 테이블에서 직업과 직업별 토탈월급을 daily_machul 에
입력하는 프로시져를 생성합니다.
createor replace procedure daily_sum
isbegininsertinto daily_machul
select job, sum(sal)
from emp
groupby job;
commit;
end;
/-- 직업과 직업별 토탈 월급을 ISERT함(DAILY_MACHUL에)3. 프로시져를 수행합니다.
exec daily_sum;
SQL>SELECT*FROM DAILY_MACHUL;
JOB SUMSAL
-------------------- ----------
PRESIDENT 5000
MANAGER 8275
SALESMAN 5600
CLERK 4150
ANALYST 60004. 프로시져를 수행했을때 오류가 나겠금 데이터를 수정합니다.
update emp
set sal =0where job='SALESMAN';
truncatetable daily_machul;
altertable daily_machul
addconstraint m_ck check( sumsal between100and9000);
-- CHECK제약 걸려있음(100~9000사이)exec daily_sum;
1행에 오류:
ORA-02290: 체크 제약조건(C##SCOTT.M_CK)이 위배되었습니다
5. 프로시져를 수행했을 때 발생하는 오류가 error 테이블에 입력되게
daily_sum 프로시져를 수정합니다.
createtable error --LOG_TABLE 혹은 ERROR_TABLE만듦
( e_date timestamp, -- 에러 발생 날짜
e_code number(10), -- 에러 코드
e_message varchar2(200) ); --에러메세createor replace procedure daily_sum
is
e_code number;
e_message varchar2(200);
begininsertinto daily_machul
select job, sum(sal)
from emp
groupby job;
commit;
| exception
추 |when others then--모든 에러를 다 잡아내는
가 |rollback;
된 | e_code := SQLCODE; -- 에러번호| e_message := SQLERRM; --에러 메세지
부 |
분 |insertinto error
|values ( systimestamp, e_code, e_message );
--현재시간 --에러코드 --에러메세지end;
/SQL>exec daily_sum;
PL/SQL 처리가 정상적으로 완료되었습니다. --에러나지 않고 정상 완료-- 하지만 에러 났을 순 있음SQL>select*from daily_machul;
선택된 레코드가 없습니다.
SQL>select*from error;
SELECT*FROM ERROR;
문제풀며 익히기
Q1.
위의 예제의 프로시져를 수행했을때 발생하는 에러 코드와 에러 메세지가
error 테이블에 입력됨과 동시에 출력도 되게 프로시져를 수정하세요
개발자가 자주 실행해야하는 특정 작업을 필요할 때 호출하기위해
절차적인 언어를 이용하여 이름이 있는 프로그램 모듈(Block)을 의미한다.
즉, PL/SQL코드에 이름을 부여해서 DB에 저장
DB에 저장된 프로시저 확인하기
invaild된 프로시저를 컴파일 눌러서 vaild 할 수 있음.
프로시저(procedure) 의 장점?
프로그램 동작의 간편한 실행
하나의 요청으로 여러 SQL문을 실행 할 수 있습니다.
보안성 향상
프로시저 단위로 실행 권한을 부여할 수 있기 때문에 기본적인 보안 사고에대처가 유연한 편이다. ( 즉, A에게는 프로시저 k, B에게는 프로시저 m)
네트워크 소요 시간 절감
쿼리를 다중으로 실행하는 경우, 한번의 호출을 통해 다중의 쿼리가 실행되기 때문에 네트워크에 대한 부담과 소요 시간을 줄일 수 있다.
소스 코드의 운영 배포 용이성
별도의 WAS 서버 재기동 없이 프로시져 수정으로 조회, 수정, 추가 등의
가벼운 소스 변경 등이 가능하여, 긴급 배포 등이 용이하다.
Web Was <-------> DB
HTML코드 JAVA코드
JAVA코드 수정 시 Was 서버 재가동 없이
프로시저 수정으로 조회, 수정 ,추가 등의 가벼운 소스 변경은 가능
문법
create [ or replace ] procedure [ 프로시저 이름 ]
( 입력 매개 변수 데이터 타입 )
is/as--is 또는 as
[ 선언부 ]
begin
[ 실행부 ]
exception
[ 예외 처리부 ]
end [ 프로시저 이름 ];
예제 1)
익명 PL/SQL 일 때(즉, 이름 없는 코드)-이름이 없는 pl/sql은 DB에 저장 안됨
accept p_deptno prompt '부서번호를 입력하세요 ~ 'declare
v_deptno emp.deptno%type :=&p_deptno;
v_sumsal number(10,2) ;
beginselectsum(sal) into v_sumsal
from emp
where deptno = v_deptno ;
dbms_output.put_line ( '토탈월급은 : '|| v_sumsal );
end;
/==============================================================
DB의 소스코드가 변경되지 않고
단발성으로 사용할 수 있음
예제 2) 프로시져 일때
createor replace procedure pro1
( p_deptno emp.deptno%type)
is
v_deptno emp.deptno%type := p_deptno;
v_sumsal number(10,2) ;
beginselectsum(sal) into v_sumsal
from emp
where deptno = v_deptno ;
dbms_output.put_line ( '토탈월급은 : '|| v_sumsal );
end pro1;
/=====================================================
맨 위의 두줄 즉, 입력매개변수만 입력해줘도
DB에 저장되어 언제든지 EXEC명령으로 호출해서 실행할 수 있음
문제풀며 익히기
Q1.
위에서 생성한 프로시져 목록을 SQL로 조회하시오
select*from dba_objects
where object_type ='PROCEDURE'--대문자AND owner ='C##SCOTT'; ---대문자
select object_name, status
from dba_objects
where object_type ='PROCEDURE'AND owner ='C##SCOTT';
alterprocedure get_data compile;
select object_name, status
from dba_objects
where object_type ='PROCEDURE'AND owner ='C##SCOTT';
==============================
변경됨 INVALID로
altertable emp
add grade number(10);
alterprocedure cursor_job compile;
select object_name, status
from dba_objects
where object_type ='PROCEDURE'AND owner ='C##SCOTT';
컬럼 추가 삭제가 일어나면, 그 컬럼 추가, 삭제한 테이블 관련 프로시져들이
전부 invalid 상태가 됨. 그래서 dba는 전부 valid될 수 있게 해줘야함
create [ or replace ] procedure [ 프로시저 이름 ]
is|as
[ 선언부 ]
begin
[ 실행부 ]
exception
[ 예외 처리부 ]
end [ 프로시저 이름 ];
▣ 파라미터가 없는 프로시저 생성 예제
1. demo.sql 을 수행해서 emp 와 dept 테이블을 초기화 시킵니다.
SQL>@demo.sql2. emp 테이블에 grade 컬럼을 추가합니다.
SQL>altertable emp
add grade varchar2(10);
SQL>select ename, sal, grade
from emp;
ENAME SAL GRADE
-------------------- ---------- --------------------
KING 5000
BLAKE 2850
CLARK 2450
JONES 2975
MARTIN 1250
ALLEN 1600
TURNER 1500
JAMES 950
WARD 1250
FORD 3000
SMITH 800
ENAME SAL GRADE
-------------------- ---------- --------------------
SCOTT 3000
ADAMS 1100
MILLER 13003. 프로시저 사용하지 않은 익명 PL/SQL 블럭 코드:
emp 테이블에 grade 라는 컬럼에 다음의 조건에 해당되는 데이터로 값을 갱신하시오 !
월급이 3000 이상이면 A 등급
월급이 2000 이상이면 B 등급
월급이 1000 이상이면 C 등급
나머지 월급은 D 등급으로 갱신
단, 월급이 없는 사원들은 등급은 null 로 갱신되어야합니다.
declarecursor emp_cursor isselect empno, ename, sal
from emp
forupdate;
emp_record emp_cursor%rowtype;
v_grade varchar2(5);
beginopen emp_cursor ;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
if emp_record.sal >=3000then
v_grade :='A';
elsif emp_record.sal >=2000then
v_grade :='B';
elsif emp_record.sal >=1000then
v_grade :='C' ;
else
v_grade :='D';
end if;
update emp
set grade = v_grade
wherecurrentof emp_cursor;
end loop;
commit;
close emp_cursor;
end;
/SQL>select ename, sal, grade
2from emp;
ENAME SAL GRADE
-------------------- ---------- --------------------
KING 5000 A
BLAKE 2850 B
CLARK 2450 B
JONES 2975 B
MARTIN 1250 C
ALLEN 1600 C
TURNER 1500 C
JAMES 950 D
WARD 1250 C
FORD 3000 A
SMITH 800 D
ENAME SAL GRADE
-------------------- ---------- --------------------
SCOTT 3000 A
ADAMS 1100 C
MILLER 1300 C
14 행이 선택되었습니다.
=================================================
프로시저를 생성한게 아니기 때문에 매번 @p.sql해야지만 등급을 갱신할 수 있음...
그래서 프로시저를 만들어 편하게 해보자!==================================================4. grade 컬럼을 null 값으로 변경합니다.
SQL>update emp
set grade =null;
SQL>commit;
4. 프로시저 생성 코드:
SQL>createor replace procedure pro2
iscursor emp_cursor isselect empno, ename, sal
from emp
forupdate;
emp_record emp_cursor%rowtype;
v_grade varchar2(5);
beginopen emp_cursor ;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
if emp_record.sal >=3000then
v_grade :='A';
elsif emp_record.sal >=2000then
v_grade :='B';
elsif emp_record.sal >=1000then
v_grade :='C' ;
else
v_grade :='D';
end if;
update emp
set grade = v_grade
wherecurrentof emp_cursor;
end loop;
commit;
close emp_cursor;
end;
/SQL>exec pro2;
SQL>select ename, sal, grade
from emp;
ENAME SAL GRADE
-------------------- ---------- --------------------
KING 5000 A
BLAKE 2850 B
CLARK 2450 B
JONES 2975 B
MARTIN 1250 C
ALLEN 1600 C
TURNER 1500 C
JAMES 950 D
WARD 1250 C
FORD 3000 A
SMITH 800 D
ENAME SAL GRADE
-------------------- ---------- --------------------
SCOTT 3000 A
ADAMS 1100 C
MILLER 1300 C
문제풀며 익히기
Q1.
다음의 예제를 먼저 수행해보고 문제를 풀어주세요
1. grade 컬럼을 null 값으로 변경합니다.
SQL>update emp
set grade =null;
SQL>commit;
2. SMITH 의 월급을 null 값으로 변경합니다.
SQL>update emp
set sal =nullwhere ename='SMITH';
SQL>commit;
3. 앞의 예제에서 생성한 프로시저를 수행합니다.
SQL>exec pro2;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>select ename, sal, grade
from emp;
ENAME SAL GRADE
-------------------- ---------- --------------------
KING 5000 A
BLAKE 2850 B
CLARK 2450 B
JONES 2975 B
MARTIN 1250 C
ALLEN 1600 C
TURNER 1500 C
JAMES 950 D
WARD 1250 C
FORD 3000 A
SMITH D
SCOTT 3000 A
ADAMS 1100 C
MILLER 1300 C
========================================================
SMITH와 같이 월급이 null값이면 grade가 갱신되면 안됨
Q2.
SMITH 처럼 월급이 NULL 값인 사원들의 GRADE 는 갱신되지 않도록 프로시저를 수정하세요.
1. grade 컬럼을 null 값으로 변경합니다.
SQL>update emp
set grade =null;
SQL>commit;
2. SMITH 의 월급을 null 값으로 변경합니다.
SQL>update emp
set sal =nullwhere ename='SMITH';
SQL>commit;
SQL>createor replace procedure pro2
iscursor emp_cursor isselect empno, ename, sal
from emp
WHERE SAL ISNOTNULLforupdate;
emp_record emp_cursor%rowtype;
v_grade varchar2(5);
beginopen emp_cursor ;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
if emp_record.sal >=3000then
v_grade :='A';
elsif emp_record.sal >=2000then
v_grade :='B';
elsif emp_record.sal >=1000then
v_grade :='C' ;
else
v_grade :='D';
end if;
update emp
set grade = v_grade
wherecurrentof emp_cursor;
end loop;
commit;
close emp_cursor;
end;
/3. 앞의 예제에서 생성한 프로시저를 수행합니다.
SQL>exec pro2;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>select ename, sal, grade
from emp;
ENAME SAL GRADE
-------------------- ---------- --------------------
KING 5000 A
BLAKE 2850 B
CLARK 2450 B
JONES 2975 B
MARTIN 1250 C
ALLEN 1600 C
TURNER 1500 C
JAMES 950 D
WARD 1250 C
FORD 3000 A
SMITH
SCOTT 3000 A
ADAMS 1100 C
MILLER 1300 C
048. 파라미터가 있는 형태의 프로시저 생성 방법
📖
입력 파라미터가 있는 프로시저 문법
create [ or replace ] procedure [ 프로시져 이름 ]
( 입력 매개 변수 데이터 타입 )
is|as
[ 선언부 ]
begin
[ 실행부 ]
exception
[ 예외 처리부 ]
end [ 프로시져 이름 ];
예제 )
1. demo.sql 을 수행해서 emp 와 dept 테이블을 초기화 시킵니다.
SQL>@demo.sql2. 프로시저가 아닌 익명 PL/SQL 코드
set serveroutput onset verify off
accept p_deptno prompt '부서번호를 입력하세요 ~ 'declare
v_deptno emp.deptno%type :=&p_deptno;
v_sumsal number(10,2) ;
beginselectsum(sal) into v_sumsal
from emp
where deptno = v_deptno ;
dbms_output.put_line ( '토탈월급은 : '|| v_sumsal );
end;
/3. 프로시저를 사용했을때의 코드
createor replace procedure pro1
( p_deptno emp.deptno%type )
is
v_deptno emp.deptno%type := p_deptno;
v_sumsal number(10,2) ;
beginselectsum(sal) into v_sumsal
from emp
where deptno = v_deptno ;
dbms_output.put_line ( '토탈월급은 : '|| v_sumsal );
end;
/4. 프로시저 수행하는 방법
SQL>set serveroutput onSQL>exec pro1(10);
토탈월급은 : 8750SQL>exec pro1(20);
토탈월급은 : 10875
문제풀며 익히기
Q1.
부서번호를 입력하게하고 부서번호의 토탈월급이
10000 이상이면 high_salary 테이블에 부서번호와 토탈월급이 입력되게하고
10000 보다 작으면 normal_salary 테이블에 입력되게하는 아래의
익명 PL/SQL 을 프로시져로 변경하시오 !
1. 익명 PL/SQL 블럭
set serveroutput on
accept p_deptno prompt '부서번호를 입력하세요 ~ 'declare
v_deptno emp.deptno%type :=&p_deptno;
v_sumsal number(10,2) ; --스칼라변수beginselectsum(sal) into v_sumsal
from emp
where deptno = v_deptno ;
if v_sumsal >=10000theninsertinto high_sal
values( v_deptno, v_sumsal );
elseinsertinto normal_sal
values( v_deptno, v_sumsal );
end if;
commit;
end;
/2. 프로시저 실행
exec pro1(10);
exec pro1(20);
select*from high_sal;
select*from normal_sal;
<프로시저 생성>CREATEOR REPLACE PROCEDURE PRO1
(P_DEPTNO EMP.DEPTNO%TYPE)
IS
v_deptno emp.deptno%type := p_deptno; -- &빼
v_sumsal number(10,2) ;
beginselectsum(sal) into v_sumsal
from emp
where deptno = v_deptno ;
if v_sumsal >=10000theninsertinto high_sal
values( v_deptno, v_sumsal );
elseinsertinto normal_sal
values( v_deptno, v_sumsal );
end if;
commit;
end;
/< 프로시저 실행 >exec pro1(10);
exec pro1(20);
select*from high_sal;
DEPTNO SUMSAL
---------- ----------2010875select*from normal_sal;
DEPTNO SUMSAL
---------- ----------108750
10장. 함수(function)
049. 함수(function)의 정의와 장점
📖
사용자 정의 함수(function)란?
오라클에 내장되어 있는 함수가 아니라 PL/SQL 개발자가 필요에 의해서
직접 작성한 함수를 말합니다.
프로그래밍을 하다보면 특정 기능을 만들어서 사용하고 싶은 경우가 있습니다.
그럴때 유용한 PL/SQL 객체가 사용자 정의 함수입니다.
단, 너무 과용할 경우 튜닝의 대상이 될 수 있으므로 주의
EX) 사용자 정의 함수 안에 사용자 정의 함수 안에 사용자 정의 함수가 있는,,
원래는 사용자정의 함수를 사용하여 튜닝을 자주
사용자 정의 함수(function)의 장점?
간결하고 이해하기 쉬운 SQL을 만들 수 있다.
반복 사용되는 기능을 함수로 정의하면 동일 코드를 중복 작성하지 않아도 된다.
잘 설계하여 만든 함수는 그 기능이 필요한 여러 가지 다른 SQL에서 재사용하기 쉽다.
▣ 장점 설명 예제
1. 함수를 사용하지 않았을 때:
SELECT ename, sal, casewhen sal >=3000then'고소득'when sal >=2000then'중간소득'else'저소득'end "소득"
from emp;
2. 함수를 사용했을 때 :
SELECT ename, sal, fun179(ename) 소득
FROM emp;
/* 결과
ENAME SAL 소득
-------------------- ---------- -------------------
KING 5000 고소득
BLAKE 2850 중간소득
CLARK 2450 중간소득
JONES 2975 중간소득
MARTIN 1250 저소득
ALLEN 1600 저소득
TURNER 1500 저소득
JAMES 950 저소득
WARD 1250 저소득
FORD 3000 고소득
SMITH 800 저소득
SCOTT 3000 고소득
ADAMS 1100 저소득
MILLER 1300 저소득
14 개의 행이 선택되었습니다.
*/
- -소득이 '고소득' 인 사원의 이름과 월급을 출력하시오 !
1. 함수를 사용하지 않았을 때 :
select ename, sal, 소득
from (
SELECT ename, sal, casewhen sal >=3000then'고소득'when sal >=2000then'중간소득'else'저소득'end "소득"
from emp
)
where 소득='고소득';
/* 결과
ENAME SAL 소득
---------- ---------- ---------
KING 5000 고소득
FORD 3000 고소득
SCOTT 6000 고소득
*/2. 함수를 사용했을 때 :
SELECT ename, sal, fun179(ename) 소득
FROM emp
WHERE fun179(ename) ='고소득';
/* 결과
ENAME SAL 소득
---------- ---------- ---------
KING 5000 고소득
FORD 3000 고소득
SCOTT 6000 고소득
*/
050. 함수(function) 사용예제1 —select
📖
함수(function) 사용예제1
사용자 정의 함수 문법
createor replace function 함수이름
(입력변수 데이터 유형)
is|asreturn 출력 데이터 유형 --프로시져와 차이점-- 사용자 정의 함수가 출력될 데이터 유형을 RETURNbegin
실행문
return 출력변수; -- 출력변수를 RETURN으로 출력-- dbms_output.put_line으로 출력하는 것이 아님end;
/=================================return 출력 데이터 유형
return 출력변수;
이 두가지 반드시 작성해줘야함
▣ 사용자 정의 함수 생성 예제1
이름을 입력했을때, 해당 사원의 월급의 순위가 출력되게하는 함수를 생성하시오
/*
SQL> select ename, sal, fun178(ename)
from emp;
KING 5000 1
SCOTT 3000 2
*/
CREATEOR REPLACE FUNCTION fun178
( p_ename emp.ename%TYPE )
RETURN NUMBER -- 사용자 정의 함수가 출력될 데이터 유형을 RETURNAS
v_rank NUMBER(5);
BEGINSELECT 순위 INTO v_rank
FROM (
SELECT ename, DENSE_RANK() OVER ( ORDERBY sal DESC ) 순위
FROM emp
)
WHERE ename =UPPER(p_ename);
RETURN v_rank; -- 출력변수를 RETURN으로 출력END;
//* 결과확인 */SELECT ename, sal, fun178(ename) 순위
FROM emp
ORDERBY 순위;
/* 결과
ENAME SAL 순위
---------- ---------- ----------
SCOTT 6000 1
KING 5000 2
FORD 3000 3
JONES 2975 4
BLAKE 2850 5
CLARK 2450 6
ALLEN 1600 7
TURNER 1500 8
MILLER 1300 9
WARD 1250 10
MARTIN 1250 10
ADAMS 1100 11
JAMES 950 12
SMITH 800 13
14 개의 행이 선택되었습니다.
*//* 기타 출력해보기 */SQL>select fun178('SCOTT')
2from dual;
FUN178('SCOTT')
---------------2SQL>select ename, sal, fun178(ename)
2from emp
3where job ='SALESMAN';
ENAME SAL FUN178(ENAME)
-------------------- ---------- -------------
MARTIN 12509
ALLEN 16006
TURNER 15007
WARD 12509=> 그냥 RANK()함수로는 출력된 것 중 순위만 가능한데
=> 사용자 정의 함수를 사용하여 전체 중 직업이 SALESMAN인 사원의 순위를
그대로 가져올 수 있
▣ 사용자 정의 함수 생성 예제2
- 위의 함수코드를 수정해서 아래와 같이 결과가 나오게 하시오 !
-- 즉, 월급의 순위가 1 ~ 3등까지는 고소득,
-- 나머지 순위는 저소득 이란 글씨가 출력되게 하는 함수를 생성하시오 !
/*
SELECT ename, sal, fun179(ename) 소득
FROM emp;
KING 5000 고소득
SCOTT 3000 고소득
SMITH 1200 저소득
...
*/CREATEOR REPLACE FUNCTION fun179
( p_ename emp.ename%TYPE )
RETURN VARCHAR2 --사용자 정의함수 fun179의 datatype VARCHAR2AS
v_rank NUMBER(5);
v_msg VARCHAR2(20);
BEGINSELECT 순위 INTO v_rank
FROM (
SELECT ename, DENSE_RANK() OVER ( ORDERBY sal DESC ) 순위
FROM emp
)
WHERE ename =UPPER(p_ename);
IF v_rank <4THEN
v_msg :='고소득';
ELSE
v_msg :='저소득';
END IF;
RETURN v_msg;
END;
//* 결과확인 */SELECT ename, sal, fun179(ename) 소득
FROM emp;
/* 결과
ENAME SAL 소득
---------- ---------- ------------
KING 5000 고소득
BLAKE 2850 저소득
CLARK 2450 저소득
JONES 2975 저소득
MARTIN 1250 저소득
ALLEN 1600 저소득
TURNER 1500 저소득
JAMES 950 저소득
WARD 1250 저소득
FORD 3000 고소득
SMITH 800 저소득
SCOTT 6000 고소득
ADAMS 1100 저소득
MILLER 1300 저소득
14 개의 행이 선택되었습니다.
*/
문제풀며 익히기
Q1.
위에서 생성한 사용자 정의 함수 FUN178을 이용해서 사원테이블에서 월급의 순위가 12위인 사원의 이름과 월급을 출력하시오.
select ename, sal
from emp
where fun178(ename) =12;
/* 결과
ENAME SAL
-------------------- ----------
SMITH 800
*/
Q2.
위의 결과를 사용자 정의 함수를 이용하지 말고 출력하시오.
select ename, sal
from ( select ename, sal, dense_rank() over (orderby sal desc) 순위
from emp )
where 순위 =12;
Q3.
위에서 만든 함수(FUN129)를 사용해서 고소득인 사원의 이름과 월급을 출력하시오
select ename, sal
from emp
where fun179(ename) ='고소득';
/* 결과
ENAME SAL
-------------------- ----------
KING 5000
JONES 2975
FORD 3000
SCOTT 3000
*/
Q4.
소득이 고소득자인 사원들을 출력하는데, 월급이 3000 이상인 사원들만 출력하고, 월급이 높은 사원부터 출력되게하시오.
select ename, sal
from emp
where fun179(ename) ='고소득'and sal >=3000orderby sal desc;
/* 결과
ENAME SAL
-------------------- ----------
KING 5000
SCOTT 3000
FORD 3000
*/
Q5.
우리반 테이블에서 나이를 입력하면, 아래와 같이 출력되는 함수를 생성하시오.
/*
select ename, func3(age)
from emp18;
최재건 20대
전수진 30대
서형진 30대
오운학 20대
*/
createor replace function func3
( p_age emp18.age%type )
return varchar2
as
v_agedae varchar2(10);
beginselect substr(p_age, 1, 1) ||'0대'into v_agedae
from dual; --입력 받는 age를 기반으로 잘라내는 것이므로 dual테이블return v_agedae;
end;
/select ename, func3(age)
from emp18;
051. 함수(function) 사용예제2 —update
📖
사용자 정의 함수를 이용하여 테이블에 UPDATE 하기
UPDATE 방법
상호관련 서브쿼리 update 문 사용
merge문을 사용하여 update
사용자 정의 함수를 사용하여 update
사용자 정의 함수를 이용하여 테이블에 UPDATE 하기 생성 예제)
-- 예제1. 먼저 demo.sql 을 수행합니다.SQL>@demo.sql-- 예제2. 부서번호, 부서번호별 토탈월급을 출력하시오 !SQL>select deptno, sum(sal)
from emp
groupby deptno;
/*결과
DEPTNO SUM(SAL)
---------- ----------
10 8750
30 9400
20 10875
*/-- 예제3. 이름과 월급과 부서번호, 자기가 속한 부서번호의 토탈월급이 출력되게하시오/* 출력
SQL> SELECT ename, sal, deptno, fun51(deptno)
FROM emp;
SCOTT 3000 10 8750
SMITH 1200 20 10875
...
*/
답:
CREATEOR REPLACE FUNCTION fun51
( p_deptno emp.deptno%TYPE ) #입력 매개변수
RETURN NUMBER
AS
v_sum NUMBER(10); #스칼라변수 선언
BEGINSELECTSUM(sal) INTO v_sum
FROM emp
WHERE deptno = p_deptno;
RETURN v_sum;
END;
//* 결과 확인 */SQL>SELECT ename, sal, deptno, fun51(deptno)
FROM emp;
/* 결과
ENAME SAL DEPTNO FUN51(DEPTNO)
---------- ---------- ---------- --------------
KING 5000 10 8750
BLAKE 2850 30 9400
CLARK 2450 10 8750
JONES 2975 20 13875
MARTIN 1250 30 9400
ALLEN 1600 30 9400
TURNER 1500 30 9400
JAMES 950 30 9400
WARD 1250 30 9400
FORD 3000 20 13875
SMITH 800 20 13875
SCOTT 6000 20 13875
ADAMS 1100 20 13875
MILLER 1300 10 8750
14 개의 행이 선택되었습니다.
*/-- 예제4. 부서테이블에 sumsal 컬럼을 숫자형으로 추가하시오 !SQL>ALTERTABLE dept
ADD sumsal NUMBER(10);
-- 예제5. 방금 추가한 sumsal 컬럼에 해당 부서번호의 토탈월급으로 값을 갱신하시오 !SQL>UPDATE dept
SET sumsal = fun51(deptno);
SQL>COMMIT;
SQL>SELECT*FROM dept;
/* 결과
DEPTNO DNAME LOC SUMSAL CNT
---------- -------------- ------------- ---------- ----------
10 ACCOUNTING NEW YORK 8750 3
20 RESEARCH DALLAS 13875 5
30 SALES CHICAGO 9400 6
40 OPERATIONS BOSTON 0
*/
문제풀며 익히기
Q1.
부서테이블에 cnt 라는 컬럼을 아래와 같이 추가하시오.
SQL>ALTERTABLE dept
ADD cnt NUMBER(10);
Q2.
CNT 컬럼의 data 를 해당 부서번호의 인원수로 갱신하는 함수를 생성하시오
-- 업데이트 수행SQL>UPDATE dept
SET cnt = pro_cnt(deptno);
SQL>COMMIT;
-- 결과 확인SQL>SELECT*FROM dept;
/* 결과
DEPTNO DNAME LOC SUMSAL CNT
---------- -------------- ------------- ---------- ----------
10 ACCOUNTING NEW YORK 8750 3
20 RESEARCH DALLAS 13875 5
30 SALES CHICAGO 9400 6
40 OPERATIONS BOSTON 0
*/
A.
CREATEOR REPLACE FUNCTION pro_cnt
( p_deptno emp.deptno%TYPE )
RETURN NUMBER
AS
v_cnt NUMBER(10);
BEGINSELECTcount(*) INTO v_cnt
FROM emp
WHERE deptno = p_deptno;
RETURN v_cnt;
END;
/
![[PLSQL-2] PL/SQL기본 2 (5~10장)](https://image.inblog.dev?url=https%3A%2F%2Fsource.inblog.dev%2Ffeatured_image%2F2024-03-31T10%3A17%3A49.961Z-9278e75b-9b82-4e59-aed2-a438aed5d1f6&w=2048&q=75)
![[TUNING-1]튜닝 1(1~3장)](https://image.inblog.dev?url=https%3A%2F%2Finblog.ai%2Fapi%2Fog%3Ftitle%3D%255BTUNING-1%255D%25ED%258A%259C%25EB%258B%259D%25201%281%7E3%25EC%259E%25A5%29%26logoUrl%3Dhttps%253A%252F%252Finblog.ai%252Finblog_logo.png%26blogTitle%3DHye-Min%2520Son&w=750&q=75){kind=logo}
![[PLSQL-3] PL/SQL고급](https://image.inblog.dev?url=https%3A%2F%2Fsource.inblog.dev%2Ffeatured_image%2F2024-03-31T10%3A18%3A45.820Z-21fdb190-4cb4-4567-b1ea-c7b8ec97c85d&w=750&q=75)
![[PLSQL-1] PL/SQL기본 1(1~4장)](https://image.inblog.dev?url=https%3A%2F%2Fsource.inblog.dev%2Ffeatured_image%2F2024-03-31T10%3A15%3A39.968Z-187fdc8d-aa31-43e9-be65-cb952c7d34cc&w=750&q=75)
![[SQL-4] SQL중급 2](https://image.inblog.dev?url=https%3A%2F%2Fsource.inblog.dev%2Ffeatured_image%2F2024-03-31T09%3A08%3A24.935Z-61b5e45c-74e9-482e-bc38-8546771597be&w=750&q=75)