[Oracle] PL/SQL Class 5 - replace procedure, sequence, replace function, 아웃풋, 과제(3문제)

2023.11.13

 

 

(설명1)

drop table sawon_ex;

create table sawon_ex as select *from sawon;

 

create or replace procedure p_sawon_del

                   (v_bun in sawon_ex.sabun%type)          // in 생략 가능(기본값)  단, out시에는 입력필요.

is

begin

              delete sawon_ex where sabun =v_bun;

end;

/

-- 호출형태

exec p_sawon_del(15)                                             // 15번 사원을 삭제(del)하라는 명령어

 

 

 

(연습1)

-- 사원 테이블에 사원정보를 입력하는 프로시져

-- 사원은 시퀀스를 이용하고(201~299, 2씩 증가)

-- 입사일은 현재날짜로 입력되고

-- 커미션은 직책 조건에 따라 입력(직책이 대리 --> 급여 10%, 과장 --> 급여15%, 부장 --> 급여 20%, 그 외 직책 -->0)

-- 관리자번호는 부서사 영업부는 3번 사번입력, 총무부는 10번 사번입력, 전산부는 6번 사번을 입력되도록 한다.

-- 호출형태
-- exec p_sawon_in('홍동우', 3000, '대리', '남자', '영업부');           // 입력( in )하라는 명령어 

create sequence  s_sawon_sabun
                                increment by 2            // 2개씩 증가
                                start with 201                // 시작하는 숫자
                                maxvalue 299;              // 끝나는 숫자

create or replace procedure p_sawon_in
                             (v_saname sawon.saname%type,
                              v_sajob sawon.sajob%type,
                              v_sapay sawon.sapay%type,
                              v_sasex sawon.sasex%type,
                              v_dname dept.dname%type,
                              v_samgr sawon.samgr%type );
is
-- dname_dept dept.deptno%type;
-- v_comm sawon.comm%type;
  
begin
-- select sajob, comm, sapay, into v_job, v_comm, v_pay from sawon;
-- if v_job='대리' then v_comm=v_pay*0.1;
-- elsif v_job='과장' then v_comm=v_pay*0.15;
-- elsif v_job='부장' then v_comm=v_pay*0.2;
-- else v_comm=0;
-- end if;

-- if d.name = '영업부' then v_smgr :=3;
-- elsif d.name = '총무부' then v_smgr :=10;
-- elsif d.name = '전산부' then v_smgr :=6;
-- end if;
             insert into sawon(sabun, saname, sapay, sajob, deptno, comm, smgr, sahire)
-- values(v_sabun, v_name, v_job, v_sahire, v_comm, v_deptno, v_smgr);

              values(s_sawon_sabun.nextval, v_saname, v_sapay, v_sajob, v_sex,

                          (select deptno from dept where dname=v_dname),
              (case v_sajob when '대리' then v_sapay*0.1 when '과장' then v_sapay*0.15 when '부장'

              then v_sapay*0.2 else 0 end), v_samge, sysdate); 
              commit;
end;
/

 

(참고)

1. 오류 확인 방법

SQL> show error procedure 테이블명(프로시저명)

 

2. 오류보는 방법 

SQL> desc user_errors        // object_type 컬럼에서 객체지정 필요.

 

3. 오류객체 보는 방법

SQL> user_objects 테이블명         // 객체의 정보

 

(연습2)
-- 사원 테이블에 사원 정보를 변경하는 프로시저
-- 호출형태
-- exec p_sawon_up(201, 3500, '과장', '관리부') --> 201번 사원의 정보를 지정한 값들로 변경

-- 커미션은 직책의 조건에 따라 입력(직책이 대리 --> 급여10%, 과장-->급여의 15%, 부장 --> 급여의 20%, 그 외의 직책-->0)
-- 관리자번호는 부서가 영업부면 3번 사번입력, 총무부는 10번 사번입력, 전산부는 6번사번 입력 되도록 한다.

create or replace procedure p_sawon_up(
                           v_sabun sawon.sabun%type,
                           v_sapay sawon.sapay%type,
                           v_sajob sawon.sajob%type,
                           v_dname dept.dname%type)
is
begin
             select deppno into b_deptno from dept where dname=v_dname;                        //  부서번호
             if v_dname = '영업부' then v_mgr := 3;
             elsif v_dname = '총무부' then v_mgr :=10;
             elsif v_dname = '전산부' then v_mgr :=6;
             end if;
             if v_job = '대리' then v_comm := v_sal*0.1;
             elsif v_job = '과장' then v_comm := v_sal*0.15;
             elsif v_job = '부장' then v_comm := v_sal*0.2;
             else v_comm :=0;
             end if;

              update sawon
              set sapay = v_sal, deptno = v_deptno, sajob=v_job, comm=v_comm, samgr=v_mgr 
              where sabun = v_bun;
-- update sawon
-- set sapay = v_sapay, sajob=v_sajob, 
-- deptno=(select deptno from dept where dname=v_dname),
-- comm=(case v_sajob when '대리' then v_sapay*0.1 when '과장' then v_sapay*0.15 when '부장' then v_sapay)
-- samgr=(case v_dname when '영업부' then 3 when '총무부' then 10 when '전산부' then 6 end)
-- where sabun=v_sabun;
              commit;
              end;
end;
/

 

 

(설명3)
## out mode가 있는 프로시저(외부 컴퓨터(로컬 서버 or 서버)에서 내 컴퓨터(서버 or 로컬서버)로 접속해 DB 확인 방법)
-- 사번을 가지고 이름을 리턴해주는 프로시저를 생성

create or replace procedure p_name_output
                   (v_bun in sawon.sabun%type

                    v_name out sawon.sanme%type)

is

begin
                select saname into v_name from sawon where sabun=v_bun;
end;
/

 호출형태(SQL 내에서 입력)
SQL> variable name varchar2(100)                 // 1. 외부변수 선언
SQL> exec p_name_output(12, ;name)            // 2. 프로시저 호출
SQL> print name                                                // 3. 변수 출력

 

 

(연습3)

---해당 입사년도에 입사한 사원의 인원수와 평균급여를 출력하는 프로시저

-- 호출형태

-- SQL> variable result varchar2(100)

-- SQL> exec p_state(1990, :result)

-- SQL> print result           // [1990년 입사한 사원은 XX명, 평균급여는 XXXX이다] 형태로 출력

 

** SQL내에서 직접입력(외부 명령, 아웃풋)

--SQL> variable name varchar2(100)               // 1. 외부변수 선언
--SQL> exec p_name_output(12, :name)          // 2. 프로시저 호출
--SQL> print name                                             // 3. 변수 출력

 

(연습4)

-- out mode가 있는 프로시저
-- 사번을 가지고 이름을 리턴해주는 프로시저를 생성
create or replace procedure p_state(year in number, output out varchar2)
is
                    cnt number(3);
                    pay_avg number(10,2);
begin
                    select count(*), avg(sapay) into cnt, pay_avg from sawon where to_char(sahire, 'yyyy') = year;
                    output := '입사년도: ' || year || ', 인원수: ' || cnt || ',평균급여: ' || pay_avg;
end;
/

명령 프롬프트 내에서 명령어를 입력해서 결과 값 확인하는 방법(아웃풋 이라고 함. output)

SQL> variable result varchar2(100)

SQL> exec p_state(1990, :result)

SQL> print result

 

 

## Function (함수)

함수는 대상테이블이 없고, 호출만 해서 사용하므로 성능은 좋음

-- 반복되고 복잡하고 

-- 리턴값이 반드시 1개 존재

 

-- 이름의 성을 제외한 이름만 추출하는 함수
create or replace functon f_name (name varchar2 )
                      return varchar2
is
begin
            return substr(name, 2, 4);
end;
/

SQL>  select saname, f_name(saname) name from sawon;

 

SQL> select saname, f_name(saname) name from sawon;

 

(연습5)

-- 주민번호 형태의 데이터를 가지고 성별을 반환하는 함수
-- 호출형태

-- select goname, f_sex(gojumin) "성별" from gogek;

create or replace function f_sex (num varchar2) 
                   return varchar2

-- (혹은 다른 방법)

-- create or replace function f_sex (num varchar2) 

-- 
is

             sex_num number(1);
begin
             return case when substr(num, 8, 1) = 1 then '남자' else '여자'

-- (혹은 다른 방법)

--           sex_num := substr(v_jumin, 8, 1);

--           if sex_num = 1 or sex_num = 3 then return '남자';

--           else return '여자';

--           end if; 

            end;
end;
/ 

실행방법

SQL> select goname, f_sex(gojumin) "성별" from gogek;

 

 

(연습6)

-- 숫자형태의 데이터를 가지고 100의 " # " 하나를 반환하는 함수
-- 호출형태
-- select saname, f_star(sapay) 급여현황 from sawon;

create or replace function f_star (star number) 
                   return varchar2
is
begin
                  return lpad( '*', star/100, '*');         // 100개당 하나를 #로 찍는 방법
end;
/

SQL> select saname, f_star(sapay) from sawon;

참고: col 테이블명 format a50                    // (50은 최대길이 값) 입력해 가지런히 정렬.....

         Load( "값","총 문자길이","채움문자")       // 왼쪽부터 정렬

         Rpad ("값","총 문자길이","채움문자")      // 오른쪽부터 정렬

 

(과제1)

1. 날짜 형태의 값을 가지고 [xx년 xx개월]형태로 기간을 반환하는 함수
-- 호출형태
-- col star format a50
-- col 근무기간 format a20
-- select saname, sapay, f_star(sapay) star, sahire, f_gigan(sahire) 근무기간 from sawon;

create or replace function f_gigan(hire_date date)

                 return varchar2
is
begin
               return floor(months_between(sysdate, hire_date)/12) || ' 년  ' ||
                            floor(mod(months_between(sysdate, hire_date),12)) || ' 개월';
end;
/

 

(남교수님 정답)

create or replace function f_gigan(v_date date)
                  return varchar2
is
begin
               return floor(months_between(sysdate, v_sahire)/12) || '년 ' ||  

                         floor(mod(months_between(sysdate, v_sahire), 12)) || '개월';
end;
/

 

2. 날짜에 년수, 개월수, 날수를 매개변수로 보내 더하는 함수를 생성하라
-- (입사일로부터 년 수/개월/일 을 더해서 출력하는 함수)
-- 입사일로부터 3년 1개월 3일 더한값 출력
-- 적용 예) select saname, sahire, add_date(sahire, 3, 1, 3) "심사일" from sawon;

create or replace function add_date (v_date date,      
                   v_year number,
                  v_mon number,
                  v_day number)
                  return date
is
begin
                 return add_months(v_date, v_year*12+v_mon)+v_day; 

                                                                                            // add_months(date, integer)-지정한 날짜에서 해당 월에 일정한 정수 (혹은 마이너스 정수)값을 더한 날짜를 반환
end;
/

(남교수님 정답)

create or replace function add_date(
                                          v_hire date,
                                          year number,
                                          month number,
                                          day number)
                   return date
is
begin   
   return add_months(v_hire,((year*12)+month))+day;
end;
/

 

3. 주민번호를 가지고 나이를 계산하는 함수를 생성하라.
--적용 예) select goname, gojumin, f_jumin_age(gojumin) age from gogek;

 

create or replace function f_jumin_age 
                                    (birth varchar2)
                                     return number
is
                                    v_age number;
begin
           if substr(birth, 8, 1) in (1, 2) then 
           v_age := to_number(to_char(sysdate, 'yyyy')) - (substr(birth, 1, 2)+1900);
           else 
           v_age := to_number(to_char(sysdate, 'yyyy')) - (substr(birth, 1, 2)+2000);
         end if;
         return v_age;
end;
/

 

(남교수님 정답)

create or replace function f_jumin_age(
                       jumin varchar2)
                      return varchar2
is
                   cen number(2);
                  year number(4);
                  go_year number(2);
begin
               cen := substr(jumin,8,1);  -- 1900년생, 2000년생 구별하기 위한 데이터
               year := to_char(sysdate,'yyyy'); -- 현재의 년도 구한 데이터
               go_year := substr(jumin,1,2); -- 년생 구하기

   if (cen = 1 or cen = 2) then
                return year-(1900+go_year)||'살';
   else
                 return year-(2000+go_year)||'살';
   end if;
end;
/