2016년 7월 4일 월요일

03day PL/SQL

PL/SQL

  • 자료형
    • 데이터베이스 형식 + ...
  • 특수자료형
    • %type
    • %rowtype
    • 사용자정의 레코드
  • 집합자료형
    • varray
    • table
  • 변수 / 상수의 선언부에 쓴다.
제어문
  • if
  • if~elsif ~else
  • loop
  • for
  • while
  • →SQL문 + 프로그램 혼합
  • →SQL문 + java
cursor
  • 다중 SQL문 처리
1. 
set serveroutput on

declare
     v_empno               emp.empno%type;
     v_ename               emp.ename%type;
     v_job                 emp.job%type;
     v_mgr                 emp.mgr%type;
     v_hiredate            emp.hiredate%type;
     v_sal                 emp.sal%type;
     v_comm                emp.comm%type;
     v_deptno              emp.deptno%type;
begin
     for emp_record in (select * from emp order by deptno) loop
          v_empno          := emp_record.empno;
          v_ename          := emp_record.ename;
          v_job            := emp_record.job;
          v_mgr            := emp_record.mgr;
          v_hiredate       := emp_record.hiredate;
          v_sal            := emp_record.sal;
          v_comm           := emp_record.comm;
          v_deptno         := emp_record.deptno;

          if v_deptno = 10 then
               insert into emp10
               values (v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno);
          elsif v_deptno = 20 then
               insert into emp20
               values (v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno);
          elsif v_deptno = 30 then
               insert into emp30
               values (v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno);
          end if;
     end loop;

     dbms_output.put_line('처리가 완료되었습니다.');
    
end;
/

set serveroutput off

SQL> rollback;  --PL/SQL를 사용한 것도 롤백을 하면 지워짐 commit을 해줘야함

2. 
set serveroutput on

declare

begin
      --create table a (col1 varchar(10));
 --에러남 PL/SQL 안에서 DDL/DCL 직접 실행 불가
 --transaction때문에 commit 되는 것 방지

 --Dynamic SQL 기법
 --문자열을 SQL문 처럼 실행

 execute immediate 'create table a (col1 varchar(10))';
    
end;
/

set serveroutput off

set serveroutput on

declare
 sql_stmt varchar2(2000);
begin

 --Dynamic SQL 기법
 --문자열을 SQL문 처럼 실행

 sql_stmt := 'create table b (col1 varchar(10))';
 execute immediate sql_stmt;
 --문자열 실행문을 변수처럼 가져올 수 있음
    
end;
/

set serveroutput off

set serveroutput on

declare
 sql_stmt varchar2(2000);
begin
 --Dynamic SQL 기법
 --문자열을 SQL문 처럼 실행

 --execute immediate 'create table a (col1 varchar(10))';
 sql_stmt := 'drop table b purge';
 execute immediate sql_stmt;
     --테이블 지우기
end;
/

set serveroutput off

3. 
set serveroutput on

declare
 sql_stmt varchar2(2000);
begin
 --Dynamic SQL 기법

 --문자열 작은따옴표 escape
 sql_stmt := 'insert into dept values(90,'||'''개발'''||', '||'''부산'''||')';
 dbms_output.put_line(sql_stmt);
 execute immediate sql_stmt;
    
end;
/

set serveroutput off

4. 
set serveroutput on

declare
 sql_stmt varchar2(2000);
 sql_stmt2 varchar2(2000);
 dept_id  number(2) := 91;
 dept_name  varchar(14) := '총무';
 dept_loc  varchar(13) := '서울';
begin
 --statement 기법 :문자열으로 sql문을 만드는 것
 sql_stmt := 'insert into dept values('||dept_id||','''||dept_name||''','''||dept_loc||''')';
 sql_stmt2 := 'insert into dept values('||dept_id||','||''''||dept_name||''''||','||''''||dept_loc||''''||')';
 dbms_output.put_line(sql_stmt);
 dbms_output.put_line(sql_stmt2);
 --execute immediate sql_stmt;
    
end;
/

set serveroutput off

5. 
set serveroutput on

declare
 sql_stmt varchar2(2000);
 dept_id  number(2) := 91;
 dept_name  varchar(14) := '총무';
 dept_loc  varchar(13) := '서울';
begin
 --prepared statement 기법
 --미완성 문자열을 가지고 변수를 넣어줌 자바에 printf와 비슷
 sql_stmt := 'insert into dept values(:1,:2,:3)';
 execute immediate sql_stmt
  using dept_id, dept_name, dept_loc;
    
end;
/

set serveroutput off

6. 
--사원 이름을 통해서 사원 정보 출력
set verify off
set serveroutput on

accept p_ename prompt '사원명 입력:'

declare
 type emp_record_type is record
 (
  v_empno emp2.empno%type,
  v_ename emp2.ename%type,
  v_sal emp2.sal%type,
  v_deptno emp2.deptno%type
 ); 
 emp_record emp_record_type;
 g_ename  emp2.ename%type := upper('&p_ename');
begin
 --select 문장에 cursor가 없으면 반드시 한개의 데이터만 가져옴
 select empno, ename, sal, deptno
 into emp_record
 from emp2
 where ename = g_ename;
 
 dbms_output.put_line('사원번호 :' || emp_record.v_empno);
 dbms_output.put_line('사원급여 :' || emp_record.v_sal);
 dbms_output.put_line('부서번호 :' || emp_record.v_deptno);

end;
/

set serveroutput off
set verify on

7. 
SQL> create table emp2 as select * from emp;
SQL> insert into emp2 select * from emp where deptno=10;

--(예외처리)사원 이름을 통해서 사원 정보 출력
set verify off
set serveroutput on

accept p_ename prompt '사원명 입력:'

declare
 type emp_record_type is record
 (
  v_empno emp2.empno%type,
  v_ename emp2.ename%type,
  v_sal emp2.sal%type,
  v_deptno emp2.deptno%type
 ); 
 emp_record emp_record_type;
 g_ename  emp2.ename%type := upper('&p_ename');
begin
 --select 문장에 cursor가 없으면 반드시 한개의 데이터만 가져옴
 select empno, ename, sal, deptno
 into emp_record
 from emp2
 where ename = g_ename;
 
 dbms_output.put_line('사원번호 :' || emp_record.v_empno);
 dbms_output.put_line('사원급여 :' || emp_record.v_sal);
 dbms_output.put_line('부서번호 :' || emp_record.v_deptno);
--예외 처리
exception
 when no_data_found then
  dbms_output.put_line('자료가 없습니다');
 when too_many_rows then
  dbms_output.put_line('2개 이상의 자료는 출력할 수 없습니다');
 when others then
  dbms_output.put_line('기타 에러입니다');

end;
/

set serveroutput off
set verify on

8. 
SQL> drop table emp2 purge;
SQL> create table emp2 as select empno, ename, sal, deptno from emp;
SQL> alter table emp2 add constraint emp2_ename_uk unique(ename);

set verify off
set serveroutput on

accept p_empno prompt '사원번호 입력:'
accept p_ename prompt '사원이름 입력:'
accept p_sal prompt '사원급여 입력:'
accept p_deptno prompt '부서번호 입력:'

declare
 v_empno  emp2.empno%type := &p_empno;
 v_ename  emp2.ename%type := upper('&p_ename');
 v_sal   emp2.sal%type := &p_sal;
 v_deptno  emp2.deptno%type := &p_deptno;
begin
 insert into emp2
 values (v_empno, v_ename, v_sal, v_deptno);
 dbms_output.put_line('입력 완료');

exception
 --입력값 중복 에러
 when dup_val_on_index then
  dbms_output.put_line('&p_ename' || '은 중복되었습니다');
  dbms_output.put_line('코드: ' || to_char(sqlcode));
  --에러 코드 번호
  dbms_output.put_line('내용: ' || sqlerrm);
  --에러코드 내용
end;
/

set serveroutput off
set verify on

9. 
--서버 에러의 이름 선언
--foriegn key를 지울수 없는 에러는 에러명이 설정되어 있지 않아서 이름을 만들어 줌
SET VERIFY OFF
SET SERVEROUTPUT ON

ACCEPT p_ename PROMPT '삭제하고자 하는 사원의 이름을 입력하시오 : '

DECLARE
 v_ename emp.ename%TYPE := '&p_ename';
 v_deptno dept.deptno%TYPE;
 emp_constraint EXCEPTION;
 PRAGMA EXCEPTION_INIT (emp_constraint, -2292);
 --에러코드 -2292번은 에러명이 설정되지 않았음
BEGIN
 SELECT deptno
  INTO v_deptno
  FROM emp
  WHERE ename = UPPER(v_ename);
 DELETE dept
  WHERE deptno = v_deptno;
EXCEPTION
 WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('&p_ename' || '는 자료가 없습니다.');
 WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE('&p_ename' || '는 자료가 여러개 있습니다.');
 WHEN emp_constraint THEN
  DBMS_OUTPUT.PUT_LINE('&p_ename' || '는 삭제할 수 없습니다.');
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('기타 에러입니다.');

END;
/
SET VERIFY ON
SET SERVEROUTPUT OFF

10. 
--에러 강제 만들기
SET VERIFY OFF
SET SERVEROUTPUT ON

ACCEPT p_deptno PROMPT '부서명 : '

DECLARE
 v_deptno emp.deptno%type := &p_deptno;
 emp_deptno_ck EXCEPTION;
BEGIN
 --부서명이 10,20,30 중 하나를 입력하지 않으면 에러처리함
 IF v_deptno not in (10,20,30) THEN
  RAISE emp_deptno_ck;
 ELSE
  dbms_output.put_line('정상 처리');
 END IF;
EXCEPTION
 WHEN emp_deptno_ck THEN
  dbms_output.put_line('입력오류');
 WHEN others THEN
  dbms_output.put_line('기타오류');


END;
/
SET VERIFY ON
SET SERVEROUTPUT OFF

11. 
--프로시저 프로그램을 내장함. 실행속도가 더 빠름
create or replace procedure tel1
is  --( =as, declare)
 v_tel varchar2(10) := '123456';
begin
 v_tel := substr(v_tel,1,3) || '-' || substr(v_tel,4);
 dbms_output.put_line('전화번호:' || v_tel);
end;
/



--프로시저 조회
desc user_procedures;
select object_name, procedure_name from user_procedures;
select text from user_source where name =upper('tel1');

--프로시저 실행
SQL> set serveroutput on
SQL> execute tel1;
SQL> set serveroutput off


--프로시저 실행 다른방법
SQL> set serveroutput on
SQL> exec tel1;
SQL> call tel1();
--함수()처럼 사용가능함 = 입출력이 가능함
SQL> set serveroutput off

--main01파일에 프로시저 실행문 넣음
set serveroutput on
begin
 tel1;
 tel1();
end;
/
set serveroutput off

12. 
--프로시저 외부 데이터 받기
create or replace procedure tel2 (
 p_tel in varchar2
 --입력받을 변수의 varchar size 정하지 않음
)
is
 v_tel varchar2(10);
begin
 v_tel := substr(p_tel,1,3) || '-' || substr(p_tel,4);
 dbms_output.put_line('전화번호:' || v_tel);
end;
/


--main03 프로시저 실행 구문
set serveroutput on
begin
 tel2(1234567);
 tel2(4567890);
 tel2(1234321);

end;
/
set serveroutput off

13. 
--프로시저 내보내기
create or replace procedure tel3 (
 p_tel out varchar2
 --연산 처리결과를 p_tel로 내보냄
)
is
 v_tel varchar2(10) := '123456';
begin
 p_tel := substr(v_tel,1,3) || '-' || substr(v_tel,4);
end;
/


--SQL PLUS에서 프로시저 리턴값을 받을 변수를 만들고 프린트함
SQL> variable g_tel varchar2(20)
SQL> @c:\oracle\proc04
SQL> exec tel3(:g_tel)
SQL> print :g_tel


--main04 프로시저의 리턴값 받아서 프린트하는 것 실행구문
set serveroutput on
declare
 v_tel varchar2(20);

begin
 tel3(p_tel => v_tel);
 dbms_output.put_line('결과 :' || v_tel);

end;
/
set serveroutput off

14. 
--구구단 프로시저 만들기
create or replace procedure gugu (
 p_dan in number
)
is
 v_dan varchar(30);
begin
 dbms_output.put_line(p_dan||'단');
 FOR i in 1..9 LOOP
 v_dan := p_dan || '*' || i || '=' || p_dan*i;
 dbms_output.put_line(v_dan);
 END LOOP;
 dbms_output.put_line(chr(7));
end;
/


--main05 구구단 실행구문
set serveroutput on

begin
 gugu(1);
 gugu(2);
end;
/
set serveroutput off

15. 
--구구단 프로시저 입력받아서 내보내기
create or replace procedure gugu (
 p_dan  in number,
 v_result  out varchar2

)
is
 v_tot number;
begin
 FOR i_col in 1..9 LOOP
  v_tot := p_dan * i_col;
  v_result := v_result || p_dan || '*' || i_col || '=' ||v_tot || '    ';
 END LOOP;
end;
/


--SQL PLUS에서의 구구단 프로시저 실행문
SQL> @c:\oracle\proc05
SQL> variable g_result varchar2(100);
SQL> exec gugu(8, :g_result);
SQL> print :g_result;

--PL/SQL에서의 main05 구구단 실행구문
set serveroutput on
declare
 g_result varchar2(2000);

begin
 gugu(8, v_result => g_result);
 dbms_output.put_line(g_result);
end;
/
set serveroutput off

16. 
--사원의 이름을 입력받아 속한 부서에 최대 급여와 최소 급여를 출력하는 프로시저 생성
create or replace procedure sawon (
 p_ename  in varchar2,
 v_result  out varchar2

)
is
 v_hisal number;
 v_losal number;
 v_deptno number;
begin
 
 select deptno
  into v_deptno
  from emp
  where ename= upper(p_ename);

 select max(sal), min(sal)
  into v_hisal, v_losal 
  from emp
  group by deptno
  having deptno=v_deptno;

 v_result := '최대급여: '|| v_hisal ||'    최소급여:' || v_losal ;

 
end;
/


--SQL PLUS에서 실행문
SQL> @c:\oracle\proc06
SQL> set serveroutput on
SQL> variable g_result varchar2(2000);
SQL> exec sawon('scott', :g_result);
SQL> variable g_result varchar2(2000);
SQL> exec sawon('scott', :g_result);
SQL> set serveroutput off

17. 
--지정한 테이블 입력받아 그 외의 테이블 삭제 프로시저 생성, 삭제 테이블 목록 출력
create or replace procedure droptab (
 p_tab  in varchar2,
 v_result  out varchar2
)
is
 --삭제하면 안되는 테이블 명을 담을 nested table 타입을 정함
 type varchar_table_type is table of varchar2(30)
   index by binary_integer;
 v_tab varchar_table_type;
 v_bol number;
 v_del varchar2(2000);
begin

 FOR tab_record in (select tname from tab) LOOP
 --현재 있는 테이블을 CURSOR로 가져와서 하나하나씩 비교를 위해 꺼냄
  v_bol := 1;
  FOR i in 1..regexp_count(p_tab,',')+1 LOOP
   --쉼표를 기준으로 입력된 문자열을 잘라서 nested table에 넣음
   --LOOP횟수는 쉼표+1 / 대문자 처리 /스페이스바를 없앰
   v_tab(i) := regexp_replace(regexp_substr(upper(p_tab), '[^,]+',1,i),' ','');
   IF tab_record.tname = v_tab(i) THEN
    -- 테이블이 nested table에 있을경우 -1을 곱함 
    v_bol := v_bol*(-1);
   END IF;
  END LOOP;

  IF v_bol=-1 THEN
  -- bol 값이 -1이면 데이터를 보존함
  dbms_output.put_line('보존 : ' || tab_record.tname);
  ELSE
  -- bol 값이 1이면 데이터를 삭제하고, 삭제테이블 목록에 추가함
  v_del := v_del || tab_record.tname || chr(9);
  execute immediate 'drop table '|| tab_record.tname;
  END IF;
 END LOOP;
 dbms_output.put_line('삭제완료');

 --삭제 테이블 목록을 리턴값으로 정함
 v_result :=  '삭제데이터 : ' ||v_del;

 
end;
/

작성자: 시간:

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)