PL/SQL - 변수 사용법 다양한 방법

PL/SQL

(Procedure Language / Structured Query Language)
-> 블록잡고 실행

---

Procedure

create or replace procedure pcd_empInfo(프로시저명)
(p_매개변수명 in employees.employee_id%type)
is
	-- 변수의 선언부
	v_employee_id	employees.employee_id%type;
    v_ename			varchar2(50);
    v_gender		varchar2(10);
    v_monthsal		varchar2(15);
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')
    INTO
    v_employee_id, v_ename, v_gender, v_monthsal
 	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 프로시저명;

---

Procedure

create or replace procedure pcd_empInfo(프로시저명)
(p_매개변수명 in employees.employee_id%type)
is
	-- record 타입 생성
    type 레코드타입명 is record
    (emp_id			employees.employee_id%type
    ,ename			varchar2(50)
    ,gender			varchar2(10)
    ,monthsal		varchar2(15)
    ,age			number(3)
    );
    
    -- 변수 생성 --
    v_rcd 레코드타입명;
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')
    	, case when to_date(to_char(sysdate, 'yyyy') 
        	|| substr(jubun,3,4),'yyyymmdd')
         - to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd') > 0
         	then 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
            else extract(year from sysdate)
            	- (to_number(substr(jubun,1,2))
                + case when substr(jubun,7,1) in('1','2')
                then 1900 else 2000 end)
           	end
    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 프로시저명;

---

Procedure

create or replace procedure pcd_empInfo(프로시저명)
(p_매개변수명 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(p_매개변수명);
    
    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')
    	, case when to_date(to_char(sysdate, 'yyyy') 
        	|| substr(v_all.jubun,3,4),'yyyymmdd')
         	- to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd') > 0
         then 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
            else 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)
           	end || ' '
            || nvl(to_char(v_all.department_id),'부서없음');
    
    dbms_output.put_line(lpad('-',50,'-'));
    dbms_output.put_line
    	('사원번호	사원명	성별	월급	나이	부서번호');
    dbms_output.put_line(lpad('-',50,'-'));
    dbms_output.put_line(v_result);
 end 프로시저명;

---

DBMS 출력

exec 프로시저명(p_매개변수명의값);

---

생성되어진 Procedure 인 프로시저의 소스 알아보기

select *
from user_source
where type = 'PROCEDURE' and name = '프로시저명';
-- 반드시 대문자로 쓰기!!!(프로시저명)

---

정리

-> local_hr에서작업한것