210614_1(데이터베이스8, PL/SQL, 커서, 프로시저)

DataBase(Sol)(정리대기중..언젠가)/Oracle

210614_1(데이터베이스8, PL/SQL, 커서, 프로시저)

너굴셉 2021. 6. 14. 17:19

PL/SQL (제어구조가있는 sql) --6/11

구조

declare 선언부 : 변수

begin 실행부

절차구조가 있는 sql

end ;

/ -- 실행하라

테스트1

1. emp에서 사번이 7900인 직원의 이름 사번을 출력하는 plsql을 작성하고 실행하시오

SQL> ed ex01 --작성

declare
 vempno number(4) ;
 vename varchar2(20) ;

begin
 select empno,ename into vempno,vename
 from emp
 where empno=7900;

 -- 결과 화면 출력
 dbms_output.put_line('-------실행결과--------');
 dbms_output.put_line(vempno||'    '||vename);

end;
/

SQL> @ex01 --실행

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> set serveroutput on <=== 결과 출력 설정 세션당 한번은 해줘야한다.
SQL> @ex01

-------실행결과--------
7900 JAMES

테스트2

declare

vemp emp%rowtype ---> vemp.ename, vemp.empno ,,,,, 전체를 포함한다

1. emp에서 사번이 7900인 직원의 이름,사번,입사일자,급여,부서번호를 출력하는 plsql을 작성하고 실행하시오

vemp emp%rowtype을 이용해라

declare
 vemp emp%rowtype ;

begin
 select * into vemp
 from emp
 where empno=7900;

 -- 결과 화면 출력
 dbms_output.put_line('-------실행결과--------');
 dbms_output.put_line('이름 사번 입사일자 급여 부서번호');
 dbms_output.put_line(
vemp.ename||'     '||   
vemp.empno||'     '||
vemp.hiredate||'     '||
vemp.sal||'     '||
vemp.deptno
);

end;
/

-------실행결과--------
이름 사번 입사일자 급여 부서번호
JAMES 7900 81/12/03 950 30

테스트3

declare vempno emp.empno%type; 한개컬럼/데이터형을 모를때

vdname varchar2(20) :=null; ':=' null로 초기화하라

emp에서 사번이 7900의 사번 이름 부서번호를 선택하여 사번이 10이면 Accounting

사번이 20이면 Research

사번이 30이면 Sales

사번이 40이면 Operations

로 처리하여 사번 이름 부서번호 부서명을 출력하라

----------------------조건식

if 조건 then

처리

end if;

ed ex03 -- 작성

declare
  vempno emp.empno%type;
  vename emp.ename%type;
  vdeptno emp.deptno%type;
 vdname varchar2(20) :=null;

begin
 select empno,ename,deptno into vempno, vename, vdeptno
 from emp
 where empno=7900;

if vdeptno = 10 then vdname :='Accounting';
 end if;
 if vdeptno = 20 then vdname :='Reseach';
 end if;
 if vdeptno = 30 then vdname :='Sales';
 end if;
 if vdeptno = 40 then vdname :='Operations';
 end if;

 -- 결과 화면 출력
 dbms_output.put_line('-------실행결과--------');
 dbms_output.put_line('사번 이름 부서번호 부서명');
 dbms_output.put_line(
vempno||'     '||   
vename||'     '||
vdeptno||'     '||
vdname
);

end;
/

SQL> @ex03 -- 실행
-------실행결과--------
사번 이름 부서번호 부서명
7900 JAMES 30 Sales
PL/SQL 처리가 정상적으로 완료되었습니다.

6/14

ex03 을 if elsif elsif end if; 로 전환하기 -- if들중 하나를 선택할때 많이 씌인다.

ed ex04 -- 작성

declare
  vempno emp.empno%type;
  vename emp.ename%type;
  vdeptno emp.deptno%type;
 vdname varchar2(20) :=null;

begin
 select empno,ename,deptno into vempno, vename, vdeptno
 from emp
 where empno=7900;

if vdeptno = 10 then vdname :='Accounting';
 elsif
 vdeptno = 20 then vdname :='Reseach';
 elsif
  vdeptno = 30 then vdname :='Sales';
 elsif
  vdeptno = 40 then vdname :='Operations';
 end if;

 -- 결과 화면 출력
 dbms_output.put_line('-------실행결과--------');
 dbms_output.put_line('사번 이름 부서번호 부서명');
 dbms_output.put_line(
vempno||'     '||   
vename||'     '||
vdeptno||'     '||
vdname
);

end;
/

@ex04 -- 실행

-------실행결과--------
사번 이름 부서번호 부서명
7900 JAMES 30 Sales

PL/SQL 처리가 정상적으로 완료되었습니다.

if 조건 then 참처리else 거짓처리 end if; -- ex05

1. vempno, vename, vcomm을 선언하자

2. 사번이 7900인 직원의 사번, 이름, 커미션을 선택하여

3. 커미션이 0보다 크면 xxx은 커미션이 xxx입니다 라고 출력하고

아니면 xxx은 커미션이 없습니다. 라고 출력하시오 (null인 경우 0으로처리하라)

declare
  vempno emp.empno%type;
  vename emp.ename%type;
  vcomm emp.comm%type;
  
begin
 select empno,ename,NVL(comm,0) into vempno, vename, vcomm
 from emp
 where empno=7900;
if vcomm> 0  then
 dbms_output.put_line(vename||'의 커미션은'||vcomm||'입니다');
else
 dbms_output.put_line(vename||'는 커미션이 없습니다.');  
 end if;
 
end;
/

SQL> @ex05
JAMES는 커미션이 없습니다.
PL/SQL 처리가 정상적으로 완료되었습니다.

반복문 -- ex06

for 변수 in 시작..끝 loop

반복처리식;

end loop;

문제) 구구단 만들기

for i in 1..9 loop

tot := vdan * i;

dbms_output.put_line(vdan ||'*'|| i || '=' tot);

end loop;

-- 2단을 출력하는 PL/SQL을 만들고 실행하시오

declare
 i number(2) := 0;
vdan number(2) := 2;
tot number(2) := 0;

begin

for i in 1..9 loop
 tot := vdan * i; -- 값 초기화 :=
dbms_output.put_line(vdan ||'*'|| i || '='||tot);
end loop;

end;
/

SQL> @ex06
2*1=2
2*2=4
2*3=6
2*4=8
2*5=10
2*6=12
2*7=14
2*8=16
2*9=18

위의 구구단 결과를 db에 저장하는 PL/SQL을 만들고 실행하시오 -- ex07

create table gugudan (

vdan number(2),

i number (2),

tot number (4)

); -- 테이블을 먼저 생성해놓는다

declare
 i number(2) := 0;
vdan number(2) := 2;
tot number(2) := 0;

begin

for i in 1..9 loop
 tot := vdan * i;
dbms_output.put_line(vdan ||'*'|| i || '='||tot);

insert into gugudan values(vdan,i,tot); --db에 저장하는 처리식

end loop;

end;
/

SQL> @ex07
2*1=2
2*2=4
2*3=6
2*4=8
2*5=10
2*6=12
2*7=14
2*8=16
2*9=18
PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select * from gugudan;
      VDAN          I        TOT
---------- ---------- ----------
         2          1          2
         2          2          4
         2          3          6
         2          4          8
         2          5         10
         2          6         12
         2          7         14
         2          8         16
         2          9         18

위의 결과에 시퀀스를 추가하는 gugu_seq

컬럼 idx 을 부여하는 컬럼추가 하시오 -- ex08

create sequence gugu_seq increment by 1 start with 1 ; -- 1부터 1씩 증가하는 시퀀스를 생성하고

alter table gugudan add (idx number(4)); -- 테이블에 idx컬럼을 추가하여 만들어놓는다

declare
 i number(2) := 0;
vdan number(2) := 2;
tot number(2) := 0;
begin

for i in 1..9 loop
 tot := vdan * i;
dbms_output.put_line(vdan ||'*'|| i || '='||tot);

insert into gugudan values(vdan,i,tot,gugu_seq.nextval);

end loop;

end;
/

SQL> @ex08
PL/SQL 처리가 정상적으로 완료되었습니다.

      VDAN          I        TOT        IDX
---------- ---------- ---------- ----------
         2          3          6          3
         2          4          8          4
         2          5         10          5
         2          6         12          6
         2          7         14          7
         2          8         16          8
         2          9         18          9
         2          1          2         10
         2          2          4         11
왠지 모르겠는데 꼬임

사용자 정의의 데이터 형 -- PL/SQL 테이블

PL/SQL 테이블이란 : 컬럼의 자료형이 들어간 연속된 공간 즉, 배열과 유사하다

기본형 : type 타입명 is table of 테이블.컬럼%type index by binary_integer;

변수명 타입명;

ex) vname varchar2(20);

type enames is table of

emp.ename%type

index by binary_integer;

venames enames;

ex09

emp에서 사번이 7900인 직원의 이름을 선택하여 저장하는 테이블타입 enames

변수 venames를 설정하고

'vename 첫번째 담긴 이름은'|| venames(0)||' 입니다'

라고 출력하하는 plsql을 작성하고 확인하시오

declare

vename varchar2(20);
  type enames is table of emp.ename%type index by binary_integer;
venames enames;
  
begin
 select ename into vename 
 from emp
 where empno=7900;
  venames(0) :=vename;
 dbms_output.put_line('venames 첫번째 담긴 이름은 :'||venames(0)||' 입니다');

end;
/

SQL> @ex09
venames 첫번째 담긴 이름은 :JAMES 입니다
PL/SQL 처리가 정상적으로 완료되었습니다.

PL/SQL 레코드 타입

PL/SQL 레코드 : 하나의 행을 담을 수 있는 사용자 정의 타입

기본형 : type 이름 is recode (

컬럼 데이터형,

컬럼 데이터형

);

변수명 타입이름;

부서번호가 10인 데이터의 정보를 dept에서 선택하여 부서번호, 부서명, 지역을 출력하시오 select * into 변수명

declare
type dept_recode is record(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
r_dept dept_recode ;

begin
select * into r_dept
from dept
where deptno=10;

 dbms_output.put_line('부서번호 부서명 지역');  
 dbms_output.put_line(r_dept.deptno||'  '||r_dept.dname||'     '||r_dept.loc);  

end;
/

SQL> @ex10
부서번호 부서명 지역
10 ACCOUNTING NEW YORK

커서 curosr (다중행 처리)

다중행 처리

* 지금까지 작성한 plsql의 select 문장의 결과는 1행 이었다.

만약 sql의 결과 행이 다중행이라면, cursor를 활용해야 한다.

커서란 sql문장의 실행 단위이며 명시적 커서와 암시적 커서가 있다.

지금까지 사용한 결과가 1행인 경우 암시적 커서를 사용한 것이다.

명시적 커서 기본형

declare

cursor 커서명 is select ~~~

begin

open 커서명;

loop

fetch 커서명 into 변수명 ,,,,, ;

exit when 커서명%notfound; -- 루프탈출

end loop;

close 커서명;

end;

ex11. emp에서 부서번호가 20인 직원의 사번, 이름, 급여를 출력하는 plsql을 만들고 실행하시오.

커서명 c1 변수명 vempno, vename, vsal

declare
  vempno emp.empno%type;
  vename emp.ename%type;
  vsal emp.sal%type;

cursor c1 is select empno, ename, sal from emp where deptno = 20;

begin
  open c1;
   dbms_output.put_line('사번 이름 급여');  
  loop
  fetch c1 into vempno, vename, vsal;
  exit when c1%notfound;
   dbms_output.put_line(vempno||'  '||vename||'     '||vsal);  

  end loop;
  close c1;
end;
/

사번 이름 급여
7369  SMITH     800
7566  JONES     2975
7902  FORD     3000
PL/SQL 처리가 정상적으로 완료되었습니다.

커서활용 2 -- open ~fetch ~ close없는 커서 활용

기본형

declare

vemp emp%rowtype;

cursor 커서명 is select~~begin

for vemp in 커서 loop

exit when 커서명%notfound;

반복 처리 문장 ---- vemp.empno

end loop;

end;

ex12 ex11을 open patch close없는 문장으로 바꾸어보시오

declare
 vemp emp%rowtype;
cursor c1 is select empno, ename, sal from emp where deptno = 20;

begin
   dbms_output.put_line('사번 이름 급여');  
  for vemp in c1 loop
  exit when c1%notfound;
   dbms_output.put_line(vemp.empno||'  '||vemp.ename||'     '||vemp.sal);  
  end loop;

end;
/

SQL> @ex12
사번 이름 급여
7369  SMITH     800
7566  JONES     2975
7902  FORD     3000

커서 상태 정리

%notfound 커서의 자료가 모두 패치 되었는가

%found 커서의 자료가 패치 되지 않은것이 있는가

%isopon 커서가 open되어있는 상태인가

%rowcount 패치(fetch)된 행이 몇개인가

실습1 -- ex13

아래처럼 출력되도록 plsql 작성하고 실행하시오 --ex13

이름 급여

-----------------

SMITH 800

ALLEN 1600

....중략..

-------------------

총합 ~~~

declare
 vename emp.ename%type;
 vsal emp.sal%type;
tot number(5) :=0;
cursor cr is select ename, sal from emp;

begin
   dbms_output.put_line('이름 급여');  
   dbms_output.put_line('--------------');  

  for vemp in cr loop
  exit when cr%notfound;
tot := tot+ vemp.sal;
   dbms_output.put_line(vemp.ename||'     '||vemp.sal);  
  end loop;

   dbms_output.put_line('--------------');
   dbms_output.put_line('총합           '||tot); 

end;
/

SQL> @ex13
이름 급여
--------------
SMITH     800
ALLEN     1600
WARD     1250
JONES     2975
MARTIN     1250
BLAKE     2850
CLARK     2450
KING     5000
TURNER     1500
JAMES     950
FORD     3000
MILLER     1300
--------------
총합           24925

실습2 -- ex14

아래처럼 출력되도록 plsql작성하고 실행하시오

*은 100당 한개

KING *********************<5000>

FORD******************<3000>

JONES**************<2975>

.........중략

SMITH *****<800>

declare

 cnt number(3) := 0;
 stars varchar2(200);
cursor cr is select ename, sal from emp order by sal desc;

begin

  for vemp in cr loop
  exit when cr%notfound;

cnt := round(vemp.sal/100);
for i in 1..cnt loop
 stars := stars || '*';
end loop;

   dbms_output.put_line(vemp.ename|| stars ||'<'||vemp.sal||'>');  

stars :=''; -- 초기화가없으면 * 가 중첩된다

  end loop;

end;
/

SQL> @ex14
KING**************************************************<5000>
FORD******************************<3000>
JONES******************************<2975>
BLAKE*****************************<2850>
CLARK*************************<2450>
ALLEN****************<1600>
TURNER***************<1500>
MILLER*************<1300>
MARTIN*************<1250>
WARD*************<1250>
JAMES**********<950>
SMITH********<800>

~~어렵당..~~

저장프로시져 / 함수 (DB에저장하면 저장프로시져, 반환값이 있으면 저장함수)

일반적으로 프로시져는 파일에 쿼리를 정의하고 파일을 실행시키는 방법이다.

하지만 저장프로시져는 그 내용을 db에 저장하고 실행하는 방법이다.

기본형 -------------------------------------

create or replace procedure 프로시져명(

인자 데이터형, 인자 데이터형,,,, )

is 지역변수 -- 지역변수 생성할 경우

begin

쿼리문(처리식);

end;

/ -- 생성(명령)

execute 프로시져명; -- 실행(명령)

----------------------------------------------

1.dept12의 데이터를 전부 삭제하는 저장 프로시져를 만들고 실행하기

SQL> select * from dept12;
    DEPTNO DNAME                          LOC
---------- ------------------------------ ------------------------------
         1 회계                           서울
         4 무역                           인천
         5 인사                           남양주

ed sp01 -- 프로시저 작성

create or replace procedure del_dept
 is
begin
 delete from dept12;
end;
/

@sp01 -- 프로시저 생성

프로시저가 생성되었습니다.

execute del_dept; -- 프로시저실행

PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select * from dept12;
선택된 레코드가 없습니다.

프로시져 정보보기

desc user_source;

이름 널? 유형
-----------------------------------------------------------------------------------------------------
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)

SQL> select text from user_source;
TEXT
--------------------------------
procedure del_dept
is
begin
delete from dept12;
end;

프로시져 에러보기

show error;

인자가 있는 저장 프로시져

emp_copy에서 직원의 이름, JONES를 입력받아서 데이터를 삭제하는 저장 프로시져 del_name을 생성하고 실행하시오.

(변수 in 데이터형) -- 들어오는 변수 (변수 out 데이터형) -- 나가는 변수

ed sp02 -- 작성

create or replace procedure del_name(
vename in emp_copy.ename%type) -- 입력받는 변수
 is 
begin
delete from emp_copy where ename=vename;
end;
/

@ep02 -- 프로시저생성

execute del_name('JONES') -- 프로시저실행

select * from emp_copy where ename=vename --확인

in out 변수를 사용하기

select empno, sal from emp_copy;

문제) emp에서 사번이 7521인 직원의 이름과 직책을 출력하는 저장 프로시저 job_empno를

생성하고 실행하고 확인하시오

ed sp03 -- 프로시저 작성

create or replace procedure job_empno(
vempno in emp_copy.empno%type --입력받는
, vename out emp_copy.ename%type --출력하는
, vjob out emp_copy.job%type ) --출력하는
 is 
begin
select ename, job into vename, vjob from emp_copy where empno=vempno;
end;
/

프로시저 작성 후 실행

variable var_name varchar2(15); --변수선언

variable var_job varchar2(15); --변수선언

execute job_empno(7521,:var_name,:var_job) -- 인자를 넣고 프로시저 실행

print var_name var_job -- 출력

VAR_NAME VAR_JOB
------------- ---------------
WARD SALESMAN

반환형이 있는 저장 함수 -- function

저장함수는 저장프로시저와 거의 동일하나 반환값이 있다라는 것만 다르다.

또 하나 특이한 점은 함수이기 때문에 select절에 사용할 수 있다.

기본형

create or replace function 함수명 (

이름 in/out 데이터형,

이름 in/out 데이터형, ,,,,)

return 데이터형 -- 반환형이있기 때문에 꼭 작성해야한다

is 지역변수;

begin

처리식;

return 값; -- 반환형이있기 때문에 꼭 작성해야한다

end;

문제) 사번을 받아서 급여의 200%를 돌려주는 저장함수cal_bonus()를 만들고 실행하시오

ed sp04 -- 프로시저 작성

create or replace function cal_bonus (
vempno emp_copy.empno%type
) return number
is vsal number(7,2);
begin
 select sal into vsal from emp_copy where empno=vempno;
  return vsal*2;
end;
/

SQL> @sp04 --프로시저 생성
함수가 생성되었습니다.
SQL> variable d_sal number ; --변수선언
SQL> execute :d_sal := cal_bonus(7844) -- 프로시저실행
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> print d_sal -- 출력

D_SAL
----------
3000

select cal_bonus(7844) from emp_copy; -- 함수로 만들었기 때문에 select 절에서 사용가능하다.
CAL_BONUS(7844)
---------------
3000