PL/SQL(Procedure Language / Structured Query Language)

----- ***** PL/SQL 구문에서 변수의 사용법 ***** -----

/*
        <결과물>
        --------------------------------------
         사원번호    사원명    성별    월급
        --------------------------------------
          101       ...     ...     ...
     */

---

Procedure 생성하기

[방법 1]

    create or replace procedure pcd_empInfo 
    (p_employee_id IN number)  -- IN 은 입력모드를 말한다. number(5)와 같이 자리수를 넣어주면 오류이다.!!!!
    is 
      -- 변수의 선언부
      v_employee_id  number(5);     -- 자리수를 사용해도 된다.
      v_ename        varchar2(50);  -- 자리수를 사용해야 한다.
      v_gender       varchar2(10);  -- 자리수를 사용해야 한다.
      v_monthsal     varchar2(20);  -- 자리수를 사용해야 한다.
      v_age          number(3);
    begin
      -- 실행부
       select employee_id, first_name || ' ' || last_name,
              case when substr(jubun,7,1) in('1','3') then '남' else '여' end,
              to_char( nvl(salary + (salary * commission_pct), salary), '$9,999,999'),
              extract(year from sysdate) - ( to_number(substr(jubun,1,2)) + case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end ) + 1 
              INTO
              v_employee_id, v_ename, v_gender, v_monthsal, v_age
       from employees
       where employee_id = p_employee_id;
      
       dbms_output.put_line( lpad('-',40,'-') );
       dbms_output.put_line( '사원번호    사원명    성별     월급   나이' );
       dbms_output.put_line( lpad('-',40,'-') );
      
       dbms_output.put_line( v_employee_id || '  ' || v_ename || '  ' || v_gender || '  ' || v_monthsal );
      
    end pcd_empInfo;
   
   -- Procedure PCD_EMPINFO이(가) 컴파일되었습니다.

=== SQL Developer 의 메뉴의 보기를 클릭하여 DBMS 출력을 클릭해주어야 한다. ===
=== 이어서 하단부에 나오는 DBMS 출력 부분의 녹색 + 기호를 클릭하여 local_hr 로 연결을 해준다. === 

   exec pcd_empInfo(101);

 

   ----- ***** 생성된 프로시저의 소스를 조회해봅니다. ***** -----

   select text
   from user_source
   where type = 'PROCEDURE' and name = 'PCD_EMPINFO';

---

Procedure 생성하기

[방법 2]

   create or replace procedure pcd_empInfo_2 
   (p_employee_id IN employees.employee_id%type)  -- p_employee_id 변수의 타입은 employees 테이블에 있는 employee_id 컬럼의 타입을 사용하겠다는 말이다.
   is 
      -- 변수의 선언부
      v_employee_id  employees.employee_id%type;    
      v_ename        varchar2(50);  
      v_gender       varchar2(10); 
      v_monthsal     varchar2(20); 
      v_age          number(3);
   begin
      -- 실행부
      select employee_id, first_name || ' ' || last_name,
             case when substr(jubun,7,1) in('1','3') then '남' else '여' end,
             to_char( nvl(salary + (salary * commission_pct), salary), '$9,999,999'),
             extract(year from sysdate) - ( to_number(substr(jubun,1,2)) + case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end ) + 1
             INTO
             v_employee_id, v_ename, v_gender, v_monthsal, v_age
      from employees
      where employee_id = p_employee_id;
      
      dbms_output.put_line( lpad('-',40,'-') );
      dbms_output.put_line( '사원번호    사원명    성별     월급    나이' );
      dbms_output.put_line( lpad('-',40,'-') );
      
      dbms_output.put_line( v_employee_id || '  ' || v_ename || '  ' || v_gender || '  ' || v_monthsal || '  ' || v_age );
      
   end pcd_empInfo_2;
   -- Procedure PCD_EMPINFO_2이(가) 컴파일되었습니다.
   
   
   exec pcd_empInfo_2(103);

 

---

Procedure 생성하기

[방법 3]

   create or replace procedure pcd_empInfo_3 
   (p_employee_id IN employees.employee_id%type)
   is 
      --- record 타입 생성 ---
      type empInfoType is record
      ( employee_id  employees.employee_id%type
      , ename        varchar2(50)
      , gender       varchar2(10)
      , monthsal     varchar2(20) 
      , age          number(3)
      );
      
      v_rcd empInfoType;
      
   begin
      
      select employee_id, first_name || ' ' || last_name,
             case when substr(jubun,7,1) in('1','3') then '남' else '여' end,
             to_char( nvl(salary + (salary * commission_pct), salary), '$9,999,999'),
             extract(year from sysdate) - ( to_number(substr(jubun,1,2)) + case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end ) + 1
             INTO
             v_rcd
      from employees
      where employee_id = p_employee_id;
      
      dbms_output.put_line( lpad('-',40,'-') );
      dbms_output.put_line( '사원번호    사원명    성별        월급    나이' );
      dbms_output.put_line( lpad('-',40,'-') );
      
      dbms_output.put_line( v_rcd.employee_id || '  ' || v_rcd.ename || '  ' || v_rcd.gender || '  ' || v_rcd.monthsal || '  ' || v_rcd.age );
      
   end pcd_empInfo_3;
   
   -- Procedure PCD_EMPINFO_3이(가) 컴파일되었습니다.
   /
   exec pcd_empInfo_3(110);

 

---

Procedure 생성하기

[방법 4]

   create or replace procedure pcd_empInfo_4 
   (p_employee_id IN employees.employee_id%type)
   is 
      v_all     employees%rowtype; -- v_all 변수의 타입은 employees 테이블의 모든 컬럼을 받아주는 행타입이다.
      v_result  varchar2(1000);
      
   begin
      select * INTO v_all
      from employees
      where employee_id = p_employee_id;
      
      v_result := v_all.employee_id || '  ' || 
                  v_all.first_name || ' ' || v_all.last_name || '  ' || 
                  case when substr(v_all.jubun,7,1) in('1','3') then '남' else '여' end || '  ' || 
                  to_char( nvl(v_all.salary + (v_all.salary * v_all.commission_pct), v_all.salary), '$9,999,999') || '  ' || 
                  ( extract(year from sysdate) - ( to_number(substr(v_all.jubun,1,2)) + case when substr(v_all.jubun,7,1) in('1','2') then 1900 else 2000 end ) + 1 );
      
      dbms_output.put_line( lpad('-',40,'-') );
      dbms_output.put_line( '사원번호    사원명    성별        월급    나이' );
      dbms_output.put_line( lpad('-',40,'-') );
      
      dbms_output.put_line( v_result );
      
   end pcd_empInfo_4;
   /
   exec pcd_empInfo_4(102);

 

Procedure들 조회하기

   select name, text
   from user_source
   where type = 'PROCEDURE' and name in ( 'PCD_EMPINFO_1', 'PCD_EMPINFO_2', 'PCD_EMPINFO_3', 'PCD_EMPINFO_4' );