2016년 7월 1일 금요일

02day PL/SQL


1. %TYPE 속성
set serveroutput on
declare
 -- type으로 변수속성 가져옴
 v_deptno  dept.deptno%type;
 v_dname  dept.dname%type;
 v_loc  dept.loc%type;
begin
 --select 순서대로 into 순서를 맞춘다
 --데이터를 채움
 --select 문의 출력결과가 한개여야 함 / 여러개 출력은 에러
 select deptno, dname, loc
 into v_deptno, v_dname, v_loc
 from dept
 where deptno=10;

 dbms_output.put_line('deptno: ' || v_deptno);
 dbms_output.put_line('dname: ' || v_dname);
 dbms_output.put_line('loc: ' || v_loc);
end;
/
set serveroutput off


2.
set serveroutput on
declare
 -- 한번에 모든 컬럼타입을 선언함
 v_dept dept%rowtype;
begin
 --
 select *
 into v_dept
 from dept
 where deptno=10;

 dbms_output.put_line(v_dept.deptno);
 dbms_output.put_line(v_dept.dname);
 dbms_output.put_line(v_dept.loc);
end;
/
set serveroutput off




3.
set serveroutput on
declare
 -- 예제 7788사원의 정보를 출력
 v_emp emp%rowtype;
begin
 --
 select *
 into v_emp
 from emp
 where empno=7788;

 dbms_output.put_line('사원번호 : ' || v_emp.empno);
 dbms_output.put_line('이름 : ' || v_emp.ename);
 dbms_output.put_line('담당업무 : ' || v_emp.job);
 dbms_output.put_line('매니저 : ' || v_emp.mgr);
 dbms_output.put_line('입사일 : ' || v_emp.hiredate);
 dbms_output.put_line('급여 : ' || v_emp.sal);
 dbms_output.put_line('보너스 : ' || v_emp.comm);
 dbms_output.put_line('부서번호 : ' || v_emp.deptno);

end;
/
set serveroutput off

4.
set verify off
set serveroutput on

--입력받기
accept p_empno prompt '사원번호'

declare
 v_emp emp%rowtype;
 -- 입력값 변수 만들기
 v_empno emp.empno%type := &p_empno;
begin
 --
 select *
 into v_emp
 from emp
 where empno=v_empno;

 dbms_output.put_line('사원번호 : ' || v_emp.empno);
 dbms_output.put_line('이름 : ' || v_emp.ename);
 dbms_output.put_line('담당업무 : ' || v_emp.job);
 dbms_output.put_line('매니저 : ' || v_emp.mgr);
 dbms_output.put_line('입사일 : ' || v_emp.hiredate);
 dbms_output.put_line('급여 : ' || v_emp.sal);
 dbms_output.put_line('보너스 : ' || v_emp.comm);
 dbms_output.put_line('부서번호 : ' || v_emp.deptno);

end;
/
set serveroutput off
set verify on

5.
-- 예제 사원명 입력받아서 사원번호, 사원명, 급여, 부서번호, 부서명, 부서위치를 출력하는 스크립트 생성
set verify off
set serveroutput on

--입력받기
accept p_ename prompt '사원명 :'

declare
 v_dept dept%rowtype;
 v_emp emp%rowtype;
 v_ename emp.ename%type := '&p_ename';
begin
 --
 select *
 into v_emp
 from emp
 where ename=v_ename;
 --
 select *
 into v_dept
 from dept
 where deptno=v_emp.deptno;

 dbms_output.put_line('사원번호 : ' || v_emp.empno);
 dbms_output.put_line('이름 : ' || v_emp.ename);
 dbms_output.put_line('급여 : ' || v_emp.sal);
 dbms_output.put_line('부서번호 : ' || v_emp.deptno);
 dbms_output.put_line('부서명 : ' || v_dept.dname);
 dbms_output.put_line('부서위치 : ' || v_dept.loc);

end;
/
set serveroutput off
set verify on

-- 예제 사원명 입력받아서 사원번호, 사원명, 급여, 부서번호, 부서명, 부서위치를 출력하는 스크립트 생성
set verify off
set serveroutput on

--입력받기
accept p_ename prompt '사원명 :'

declare
 v_deptno dept.deptno%type;
 v_loc  dept.loc%type;
 v_dname dept.dname%type;
 v_empno emp.empno%type;
 v_sal emp.sal%type;
 v_deptno2 emp.deptno%type;
 v_ename emp.ename%type := '&p_ename';
begin
 --
 select dept.deptno, dept.loc, dept.dname, emp.empno, emp.sal, emp.ename, emp.deptno
 into v_deptno, v_loc, v_dname, v_empno, v_sal, v_ename, v_deptno2
 from emp
 join dept
 on emp.ename=v_ename AND emp.deptno=dept.deptno;
 --
 dbms_output.put_line('사원번호 : ' || v_empno);
 dbms_output.put_line('이름 : ' || v_ename);
 dbms_output.put_line('급여 : ' || v_sal);
 dbms_output.put_line('부서번호 : ' || v_deptno);
 dbms_output.put_line('부서명 : ' || v_dname);
 dbms_output.put_line('부서위치 : ' || v_loc);

end;
/
set serveroutput off
set verify on


6.
set verify off
set serveroutput on

declare
 --type ~ is record() 형선언
 type emp_record_type is record (
  v_empno  emp.empno%type,
  v_ename  emp.ename%type,
  v_job   emp.job%type,
  v_mgr    emp.mgr%type
 );
 
 -- 변수에 형적용
 v_emp emp_record_type;
begin
 --
 select empno, ename, job, mgr
 into v_emp
 from emp
 where empno=7369;

 dbms_output.put_line(v_emp.v_empno);
 dbms_output.put_line(v_emp.v_ename);
 dbms_output.put_line(v_emp.v_job);
 dbms_output.put_line(v_emp.v_mgr);

end;
/
set serveroutput off
set verify on

7.
set serveroutput on

declare
 x number;
 y number;
begin
 x := 1;
 y := 2;

 dbms_output.put_line('x1 : ' || x);
 dbms_output.put_line('y1 : ' || y);
 
 --내부선언
 declare
  --내부에서 y를 선언했으므로 밖의 y과 구분됨
  y number;
  z number;
 begin
  --내부에서 x를 선언하지 않았으므로 밖의 x값을 바꿈
  x :=3;
  y :=4;
  z :=5;

  dbms_output.put_line('x2 : ' || x);
  dbms_output.put_line('y2 : ' || y);
  dbms_output.put_line('z2 : ' || z);
 end;

 dbms_output.put_line('x3 : ' || x);
 dbms_output.put_line('y3 : ' || y);
 --dbms_output.put_line('z3 : ' || z); 내부변수 밖에서 실행못함
end;
/
set serveroutput off

8.
set serveroutput on

declare
 v_cnt number :=0;
 v_eq boolean;
 v_valid boolean;

 v_n1 number :=1;
 v_n2 number :=2;
 v_empno varchar2(10);

begin
 v_cnt := v_cnt +1;
 dbms_output.put_line(v_cnt);

 v_eq := (v_n1 = v_n2);
 --dbms_output.put_line(v_eq);          불린값을 출력하지 못함
 --dbms_output.put_line(to_char(v_eq)); 불린값을 출력하지 못함

end;
/
set serveroutput off

9.
코드 규약

코드 지정 규약


10.
SET serveroutput on

DECLARE
 v_eq boolean;
 v_n1 number :=1;
 v_n2 number :=2;
BEGIN
 v_eq := (v_n1 = v_n2);

 IF v_eq THEN
  dbms_output.put_line('같습니다.');
 ELSE 
  dbms_output.put_line('다릅니다.');
 END IF;


END;
/
SET serveroutput off

11.
-- 성적을 입력받아서 학점을 출력하는 스크립트 작성
SET verify off
SET serveroutput on

ACCEPT p_score PROMPT '성적입력 :'

DECLARE
 v_score number := &p_score;
BEGIN

 IF v_score >= 90 THEN
  dbms_output.put_line('A');
 ELSIF v_score >= 80 THEN
  dbms_output.put_line('B');
 ELSIF v_score >= 70 THEN
  dbms_output.put_line('C');
 ELSIF v_score >= 60 THEN
  dbms_output.put_line('D');
 ELSE
  dbms_output.put_line('F');
 END IF;


END;
/
SET serveroutput off
SET verify on

12.
-- 이름을 입력받아 업무를 조회하여 업무별로 급여를 갱신하는 SCRIPT를 작성하여라. 
-- 단 PRESIDENT:10%, MANAGER:20%, ANALYST:30%, SALESMAN:40%, CLERK:50%를 적용
SET VERIFY OFF
SET SERVEROUTPUT ON

ACCEPT p_name PROMPT ' 이 름: '

DECLARE
 v_empno emp.empno%TYPE;
 v_name emp.ename%TYPE := UPPER('&p_name');
 v_sal emp.sal%TYPE;
 v_job emp.job%TYPE;

BEGIN
 SELECT empno,job
  INTO v_empno,v_job
  FROM emp
  WHERE ename = v_name;

 IF v_job = 'PRESIDENT' THEN
  v_sal := v_sal * 1.1;
 ELSIF v_job = 'MANAGER' THEN
  v_sal := v_sal * 1.2;
 ELSIF v_job = 'ANALYST' THEN
  v_sal := v_sal * 1.3;
 ELSIF v_job = 'SALESMAN' THEN
  v_sal := v_sal * 1.4;
 ELSIF v_job = 'CLERK' THEN
  v_sal := v_sal * 1.5;
 ELSE
  v_sal := NULL;
 END IF;

 UPDATE emp
  SET sal = v_sal
  WHERE empno = v_empno;
 DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || '개의 행이 갱신되었습니다.');

EXCEPTION
 WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE(v_name || '는 자료가 없습니다.');
 WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE(v_name || '는 동명 이인입니다.');
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('기타 에러가 발생 했습니다.');
 END;
/
SET VERIFY ON
SET SERVEROUTPUT OFF

13. LOOP
SET VERIFY OFF
SET SERVEROUTPUT ON

DECLARE
 v_cnt number :=1;

BEGIN
 LOOP
  dbms_output.put_line(v_cnt);
  v_cnt := v_cnt +1;
  
  
  /*
  IF v_cnt >=10 THEN
   exit;
  END IF;
  */
  --위 코드를 줄임
  exit WHEN v_cnt >=10;
 END LOOP;
END;
/
SET SERVEROUTPUT OFF
SET VERIFY OFF

14.FOR LOOP
SET VERIFY OFF
SET SERVEROUTPUT ON

DECLARE

BEGIN
 FOR idx in reverse 1..10 LOOP
  dbms_output.put_line(idx);
 END LOOP;
END;
/
SET SERVEROUTPUT OFF
SET VERIFY OFF

15. WHILE LOOP
SET VERIFY OFF
SET SERVEROUTPUT ON

DECLARE
 v_cnt number :=1;
BEGIN
 WHILE v_cnt <= 10 LOOP
  dbms_output.put_line(v_cnt);
  v_cnt := v_cnt + 1;
 END LOOP;
END;
/
SET SERVEROUTPUT OFF
SET VERIFY OFF

16. 이중 LOOP
SET VERIFY OFF
SET SERVEROUTPUT ON

DECLARE

BEGIN
 FOR i_idx in 1..5 LOOP
  FOR j_idx in 1..3 LOOP
   dbms_output.put_line(i_idx || '/' || j_idx);
  END LOOP;
 END LOOP;
END;
/
SET SERVEROUTPUT OFF
SET VERIFY OFF

17.
--별표 피라미드 만들기
SET VERIFY OFF
SET SERVEROUTPUT ON

DECLARE
 v_star varchar2(30) := null;
BEGIN
 FOR i_idx in 1..10 LOOP
  FOR j_idx in 1..i_idx LOOP
   dbms_output.put('★');
  END LOOP;
  dbms_output.put_line('');
 END LOOP;

 FOR k_idx in 1..10 LOOP
  v_star := v_star || '☆';
  dbms_output.put_line(v_star);
 END LOOP;
END;
/
SET SERVEROUTPUT OFF
SET VERIFY OFF

18.
SET SERVEROUTPUT ON

DECLARE
 --배열형태 varray / table

 --형선언  (20) 데이터입력수 20개까지
 type varray_type1 is varray(20) of number;
 type varray_type2 is varray(20) of varchar2(20);
 
 --변수 선언
 varray1  varray_type1;
 varray2  varray_type2;
BEGIN
 varray1 := varray_type1(10,20,30,50);
 --인덱스가 1부터 시작함
 dbms_output.put_line(varray1(1));
 varray1(1) :=100;
 dbms_output.put_line(varray1(1));

 --배열의 크기
 dbms_output.put_line(varray1.count);

 --FOR문으로 데이터 모두 찍기
 FOR i in 1..varray1.count LOOP
  dbms_output.put(rpad(varray1(i),12));
    --rpad는 간격을 벌림
 END LOOP;
 dbms_output.put_line('');


 varray2 := varray_type2('AA','BB','CC', 'DD', 'EE');

 --FOR문으로 데이터 모두 찍기
 FOR i in 1..varray2.count LOOP
  dbms_output.put(rpad(varray2(i),12));
 END LOOP;
 dbms_output.put_line('');

END;
/
SET SERVEROUTPUT OFF

19.
SET SERVEROUTPUT ON

DECLARE
 --배열형태 varray / table

 --형선언
 type number_table_type is table of number
  index by binary_integer;
 --변수선언
 v_table number_table_type;

BEGIN
 --변수를 하나씩 추가해서 집어넣을 수 있음
 v_table(1) := 10;
 v_table(2) := 20;
 v_table(3) := 30;
 v_table(4) := 40;
 v_table(5) := 50;
 
 --테이블 사이즈
 dbms_output.put_line(v_table.count);

 --데이터 모두 출력
 FOR i in 1..v_table.count LOOP
  dbms_output.put_line(v_table(i));
 END LOOP;

END;
/
SET SERVEROUTPUT OFF

20.
SET SERVEROUTPUT ON

DECLARE
 --커서
 CURSOR emp_cursor is
  SELECT empno, ename, sal 
  FROM emp
  WHERE deptno =10;
 --변수선언
 v_empno  emp.empno%type;
 v_ename  emp.ename%type;
 v_sal  emp.sal%type;

BEGIN
 --
 OPEN emp_cursor;
 LOOP
  FETCH emp_cursor into v_empno, v_ename, v_sal;
  exit when emp_cursor%notfound;

  dbms_output.put_line(v_empno);
  dbms_output.put_line(v_ename);
  dbms_output.put_line(v_sal);
  --빈줄을 포함함
  dbms_output.put_line(chr(7));
 END LOOP;

 CLOSE emp_cursor;

END;
/
SET SERVEROUTPUT OFF

SET SERVEROUTPUT ON

DECLARE
 --커서 전체 열을 다가져오는 방법
 CURSOR emp_cursor is
  SELECT *
  FROM emp
  WHERE deptno =10;
 --변수선언
 v_emp  emp%rowtype;

BEGIN
 --
 OPEN emp_cursor;
 LOOP
  FETCH emp_cursor into v_emp;
  exit when emp_cursor%notfound;

  dbms_output.put_line(v_emp.empno);
  dbms_output.put_line(v_emp.ename);
  dbms_output.put_line(v_emp.sal);
  
  --빈줄을 포함함
  dbms_output.put_line(chr(7));
 END LOOP;

 CLOSE emp_cursor;

END;
/
SET SERVEROUTPUT OFF


21.
SET VERIFY OFF
SET SERVEROUTPUT ON

ACCEPT p_deptno PROMPT ' 부서번호를 입력하시오 : '

DECLARE
 --변수선언
 v_deptno emp.deptno%type := &p_deptno;
 v_empno  emp.empno%type;
 v_ename  emp.ename%type;
 v_sal  emp.sal%type;
 v_sal_total  NUMBER(10,2) := 0;
 --커서
 CURSOR emp_cursor IS
  SELECT empno,ename,sal
  FROM emp
  WHERE deptno = v_deptno
  ORDER BY empno;

BEGIN
 --
 OPEN emp_cursor;
 DBMS_OUTPUT.PUT_LINE('사번    이 름        급 여');
 DBMS_OUTPUT.PUT_LINE('---- ---------- ----------------');
 LOOP
  FETCH emp_cursor INTO v_empno,v_ename,v_sal;
  EXIT WHEN emp_cursor%NOTFOUND;
  v_sal_total := v_sal_total + v_sal;
  DBMS_OUTPUT.PUT_LINE(RPAD(v_empno,6) ||
  RPAD(v_ename,12) || LPAD(TO_CHAR(v_sal,'$99,999,990.00'),16));
 END LOOP;
 DBMS_OUTPUT.PUT_LINE('----------------------------------');
 DBMS_OUTPUT.PUT_LINE(RPAD(TO_CHAR(v_deptno),2) || '번 부서의 합 ' ||
  LPAD(TO_CHAR(v_sal_total,'$99,999,990.00'),16));
 CLOSE emp_cursor;

END;
/
SET SERVEROUTPUT OFF
SET VERIFY ON

22.
SET SERVEROUTPUT ON

DECLARE
 
BEGIN
 --
 FOR dept_record in (select * from dept) LOOP
  dbms_output.put_line(dept_record.deptno);
  dbms_output.put_line(dept_record.dname);
  dbms_output.put_line(dept_record.loc);
  dbms_output.put_line(chr(7));
 END LOOP;

END;
/
SET SERVEROUTPUT OFF

23.
--테이블의 목록을 출력하는 스크립트 생성
SET SERVEROUTPUT ON

DECLARE

BEGIN
 --
 FOR tab_record in
  (select * from tab where tabtype =upper('table')) LOOP
  dbms_output.put_line(rpad(tab_record.tname,30) ||
  rpad(tab_record.tabtype,6));
 END LOOP;

END;
/
SET SERVEROUTPUT OFF

24.
--주민번화 확인
SET verify off
SET SERVEROUTPUT ON

ACCEPT p_jumin PROMPT '주민번호입력 :'
 
DECLARE
 --입력값 변수 선언
 v_jumin varchar2(14) := '&p_jumin';
 --숫자하나씩 넣을 변수선언
 v_result number;
 --곱하기할 숫자 어레이만듬
 type varray_type1 is varray(12) of number;
 v_array varray_type1;

BEGIN
 v_array := varray_type1(2,3,4,5,6,7,8,9,2,3,4,5);
 v_result  := 0;

 IF length(v_jumin)!=14 THEN
  dbms_output.put_line('******-******* 형식으로 입력하세요');
 ELSE
  --숫자만 잘라서 붙임
  v_jumin := substr(v_jumin,1,6)||substr(v_jumin,8,7);
 
  --곱하고 축적함
  FOR i in 1..12 LOOP
   v_result := v_result + to_number(substr(v_jumin,i,1))*v_array(i);
  END LOOP;
  --나눈 나머지 계산
  v_result := 11-mod(v_result,11);
  v_result := mod(v_result,10);
 
  IF v_result=to_number(substr(v_jumin,13,1)) THEN
   dbms_output.put_line('정확한 주민번호입니다');
  ELSE
   dbms_output.put_line('부정확한 주민번호입니다');
  END IF;
 
 END IF;


END;
/
SET SERVEROUTPUT OFF
SET verify on


댓글 없음:

댓글 쓰기