04day PL/SQL

1.

--구구단 프로시저 입력받아서 내보내기
create or replace procedure gugudan (
 p_dan1  in number,
 p_dan2  in number,
 v_result  out varchar2

)
is
 dan1_dan2_big EXCEPTION;
begin
 IF p_dan1>p_dan2 then
  RAISE dan1_dan2_big;
 ELSE
  FOR i_col in p_dan1..p_dan2 LOOP
   FOR j in 1..9 LOOP
    v_result := v_result|| i_col || '*' || j || ' = '|| i_col*j || ' '||chr(9);
   END LOOP;
   v_result := v_result|| chr(10);
  END LOOP;
 END IF;
EXCEPTION
 WHEN dan1_dan2_big THEN
  dbms_output.put_line('첫단이 끝단보다 높습니다');

end;
/

SQL> @c:\oracle\gugudan
SQL> variable g_result varchar2(2000);
SQL> exec gugudan(3,2, :g_result);
SQL> exec gugudan(2,9, :g_result);
SQL> print :g_result;

--익명 실행문 main99
set serveroutput on

--start와 end를 accept로 받아줄 수 있음
declare
 v_start  number :=1;
 v_end  number :=2;
 v_result varchar2(2000); 
begin
 gugudan(v_start,v_end,v_result => v_result);
 dbms_output.put_line(v_result);
end;
/
set serveroutput off

2. 동이름 입력받아 주소를 출력하는 프로시저

--디렉토리 권한부여
SQL> conn system/123456
SQL> grant create any directory to scott;
--외부파일 읽어드릴 폴더이름 지정
SQL> create directory zip_dir as 'c:\oracle';
--외부 zipcode파일을 읽어서 태이블로 만듦
SQL> create table zipcode(
  2          zipcode char(7),
  3          sido varchar2(6),
  4          gugun varchar2(27),
  5          dong varchar2(39),
  6          ri varchar(67),
  7          bunji varchar2(18),
  8          seq number(5))
  9     organization external(
 10          type oracle_loader
 11          default directory zip_dir
 12          access parameters(
 13                  records delimited by newline
 14                  badfile 'BAD_ZIP'
 15                  logfile 'LOG_ZIP'
 16                  fields terminated by ','(
 17                          zipcode char,
 18                          sido char,
 19                          gugun char,
 20                          dong char,
 21                          ri char,
 22                          bunji char,
 23                         seq char)
 24                 )
 25         location('zipcode.csv')
 26         )
 27       parallel 7
 28       reject limit 200;

--동이름 입력 받아서 우편번호를 출력하는 저장 프로시저 생성
create or replace procedure zipsearch (
 p_dong  in varchar2,
 v_result  out varchar2

)
is
begin
 FOR zip_record in (select * from zipcode where dong like p_dong || '%')
 LOOP
  v_result :=v_result ||  '[' ||zip_record.zipcode ||']'|| chr(9) || zip_record.sido || chr(9) 
  || zip_record.gugun || chr(9) || zip_record.dong || chr(9) || zip_record.ri || chr(9) || zip_record.bunji || chr(10);
 END LOOP;

end;
/

 --sqlplus실행문
SQL> @c:\oracle\zipsearch
SQL> set serveroutput on
SQL> variable g_result varchar2(2000);
SQL> exec zipsearch('신사', :g_result);
SQL> print :g_result;

--익명 실행문 mainzip
set serveroutput on

declare
 v_dong  varchar2(30) := '신사';
 v_result varchar2(32767);
 --v_result 값에 충분한 숫자를 할당해 주어야한다
begin 
 zipsearch(v_dong,v_result=>v_result);
 dbms_output.put_line(v_result); 
end;
/
set serveroutput off

3.

--함수 만들기
create or replace function ename_deptno (
 v_ename  in emp.ename%type
)
return number
is
 v_deptno emp.deptno%type; 
begin
 select deptno
 into v_deptno
 from emp
 where lower(ename) = v_ename;
 
 return v_deptno;
end;
/

--sql plus 함수의 사용
SQL> @c:\oracle\function01
SQL> var g_deptno number
SQL> exec :g_deptno := ename_deptno('scott')
SQL> print :g_deptno;
SQL> select ename, ename_deptno(ename) from emp;
--함수를 select문에 쓸수 있음

4.

--급여를 입력받아서 등급을 출려하는 함수를 생성

create or replace function levelsal (
 v_sal  in emp.sal%type
)
return varchar2
is
 v_result varchar2(100); 
begin
 IF v_sal between 0 and 1000 then
  v_result := 'A 등급';
 ELSIF v_sal between 1001 and 2000 then
  v_result := 'B 등급'; 
 ELSIF v_sal between 2001 and 3000 then
  v_result := 'C 등급';
 ELSIF v_sal between 3001 and 4000 then
  v_result := 'D 등급';
 ELSIF v_sal between 4001 and 5000 then
  v_result := 'E 등급';
 END IF;
 return v_result;
end;
/

--sql plus 함수 실행문
SQL> @c:\oracle\function02
SQL> var g_result varchar2(50)
SQL> exec :g_result := levelsal(3000)
SQL> print :g_result;
SQL> select ename, sal, levelsal(sal) from emp;

5.

--패키지 생성 (선언부)

CREATE OR REPLACE PACKAGE emp_info AS
 PROCEDURE all_emp_info; 
 -- 모든 사원의 사원 정보
 PROCEDURE all_sal_info; 
 -- 모든 사원의 급여 정보
END emp_info;
/


--패키지 생성 (본문)

CREATE OR REPLACE PACKAGE BODY emp_info AS
-- 모든 사원의 사원 정보
 PROCEDURE all_emp_info
 IS
  CURSOR emp_cursor IS
  SELECT empno, ename, to_char(hiredate, 'RRRR/MM/DD') hiredate
  FROM emp
  ORDER BY hiredate;
 BEGIN
  FOR aa IN emp_cursor LOOP
   DBMS_OUTPUT.PUT_LINE('사번 : ' || aa.empno);
   DBMS_OUTPUT.PUT_LINE('성명 : ' || aa.ename);
   DBMS_OUTPUT.PUT_LINE('입사일 : ' || aa.hiredate);
  END LOOP;
 EXCEPTION
  WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
 END all_emp_info;

-- 모든 사원의 급여 정보
 PROCEDURE all_sal_info
 IS
  CURSOR emp_cursor IS
  SELECT round(avg(sal),3) avg_sal, max(sal) max_sal, min(sal) min_sal
  FROM emp;
 BEGIN
  FOR aa IN emp_cursor LOOP
   DBMS_OUTPUT.PUT_LINE('전체급여평균 : ' || aa.avg_sal);
   DBMS_OUTPUT.PUT_LINE('최대급여금액 : ' || aa.max_sal);
   DBMS_OUTPUT.PUT_LINE('최소급여금액 : ' || aa.min_sal);
  END LOOP;
 EXCEPTION
  WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
 END all_sal_info;
END emp_info;
/

--sqlplus 패키지내의 프로시저 실행
SQL> @c:\oracle\package01
SQL> set serveroutput on
-- set serveroutput on 은 DBMS_OUTPUT.PUT_LINE 을 출력하기 위해 사용
SQL> exec emp_info.all_sal_info;
SQL> exec emp_info.all_emp_info;

6.