Oracle PL(Procedure Language)/SQL

---- ★ PL(Procedure Language)/SQL
--- Prosedure
create or replace procedure p_empinfo
(v_sano in number) -- number타입의 매개변수를 v_empno(지정한 변수)에 입력 받는다.
is -- is 다음에는 사용할 변수를 정의해 준다.
   v_empno number(4);    -- ┐
   v_ename varchar2(20); -- │ 하드코딩 데이타 타입 (데이타가 변경되서 자리수를 넘으면 곤란해짐)
   v_job varchar2(20);   -- │ 잘 사용 안하는 타입이다.
   v_yearpay number(6);  -- ┘
begin   -- begin절 속에는 DDL문과 DCL문은 사용할 수 없다. 즉, select와 DML, TCL문만 사용가능하다.
  -- 그런데 select문은 반드시 into를 사용한 변수로 처리를 해야한다.
 select empno, ename, job, coalesce(sal*12+comm,sal*12,0)
     into v_empno, v_ename, v_job, v_yearpay
 from emp
 where empno = v_sano;

 dbms_output.put_line('-------------------------');
 dbms_output.put_line('사원번호 사원명 직종 연봉');
 dbms_output.put_line('-------------------------');
 dbms_output.put_line(v_empno || ' ' ||
       v_ename || ' ' ||
       v_job   || ' ' ||
       v_yearpay);
 -- dbms_output.put_line은 C언어에서 사용하는 print문같은 오라클에서 사용하는 print문이다.
end;
/
/* 명령프롬프트에서 프로시져생성시 오류가 나게되면 SQL> show errors로 오류를 확인후 수정하면된다. */
/* 명령프롱프트에서 출력값을 보려면 set serveroutput on을해서 활성화 시켜줘야한다. */
----------------------------------------------------------------------------------------------
execute p_empinfo(7499);   -- 입력한 변수를 출력하는 문 execute(또는 exec) 프로시져네임(변수)
---|| 연습문제 : p_empinfo2(사원번호)를 입력하면 부서명, 사원명, 직종, 연봉이 출력되는 Procedure를 생성
create or replace procedure p_empinfo2
(v_sano in number)
is
   v_dname varchar2(20);
   v_ename varchar2(20);
   v_job varchar2(20);
   v_yearpay number(6);
begin
 select D.dname, E.ename, E.job, coalesce(E.sal*12+comm,E.sal*12,0)
  into v_dname, v_ename, v_job, v_yearpay
 from emp E, dept D
 where E.deptno = D.deptno and E.empno = v_sano;

 dbms_output.put_line('--------------------------');
 dbms_output.put_line('부서명  사원명  직종  연봉');
 dbms_output.put_line('--------------------------');
 dbms_output.put_line(v_dname || ' ' ||
          v_ename || ' ' ||
                v_job   || ' ' ||
                v_yearpay);
end;
/
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
--// 하드코딩 데이타타입을 대신할 방법 //--
-- 1. 해당테이블 데이타타입을 참조하게 한다.
create or replace procedure p_empinfo
(v_sano in number)
is
   v_empno emp.empno%type; -- 해당테이블 데이타타입을 참조하게 한다.
   v_ename emp.ename%type;
   v_job emp.job%type;
   v_yearpay number(6);    -- 컬럼이 아닌경우는 타입값을 넉넉히 주어야 한다.
begin
 select empno, ename, job, coalesce(sal*12+comm,sal*12,0)
     into v_empno, v_ename, v_job, v_yearpay
 from emp
 where empno = v_sano;

 dbms_output.put_line('-------------------------');
 dbms_output.put_line('사원번호 사원명 직종 연봉');
 dbms_output.put_line('-------------------------');
 dbms_output.put_line(v_empno || ' ' ||
       v_ename || ' ' ||
       v_job   || ' ' ||
       v_yearpay);
end;
/
-----------------------------------------------------------------------------------------------------
-- 2. view를 생성하여 만든다.
create or replace view v_empinfo 
as
select empno, ename, job, coalesce(sal*12+comm,sal*12,0) yearpay
from emp;           -- view 생성

select * from v_empinfo; 

create or replace procedure p_empinfo
(v_sano in number)
is
  v_result v_empinfo%rowtype;  -- 해당 뷰에 있는 모든 행의 데이타 타입을 받아온다.
begin
 select * into v_result
 from v_empinfo
 where empno = v_sano;

 dbms_output.put_line('-------------------------');
 dbms_output.put_line('사원번호 사원명 직종 연봉');
 dbms_output.put_line('-------------------------');
 dbms_output.put_line(v_result.empno || ' ' ||
       v_result.ename || ' ' ||
       v_result.job   || ' ' ||
       v_result.yearpay);
end;
/
-----------------------------------------------------------------------------------------------------
-- 3. 새로운 데이타 타입을 선언
create or replace procedure p_empinfo
(v_sano in number)
is
  type emprcd                 -- 새로운 데이타 타입을 선언해준다.
  is record(a emp.empno%type,
      b emp.ename%type,
      c emp.job%type,
      d number(6)
      );
  v_emprcd emprcd;  -- v_emprcd는 emprcd 라는 변수를 사용하는 데이타 타입임을 명시
begin
 select empno, ename, job, coalesce(sal*12+comm,sal*12,0)
     into v_emprcd
 from emp
 where empno = v_sano;

 dbms_output.put_line('-------------------------');
 dbms_output.put_line('사원번호 사원명 직종 연봉');
 dbms_output.put_line('-------------------------');
 dbms_output.put_line(v_emprcd.a || ' ' ||
       v_emprcd.b || ' ' ||
       v_emprcd.c || ' ' ||
       v_emprcd.d);
end;
/
-----------------------------------------------------------------------------------------------------
--|| 위에 배운것을 이용하여 매개변수를 문자로 받아본다.
create or replace procedure p_empinfo2
(v_jikjong in varchar2)
is
  v_result v_empinfo%rowtype;
begin
 select * into v_result
 from v_empinfo
 where upper(job) = upper(v_jikjong); -- 실제 데이타 입력값이 대문자 혹은 소문자로 들어올수 있는데
                -- 오라클은 대소문자 구분을 하기때문에 upper로 모두 대문자로 변경

 dbms_output.put_line('-------------------------');
 dbms_output.put_line('사원번호 사원명 직종 연봉');
 dbms_output.put_line('-------------------------');
 dbms_output.put_line(v_result.empno || ' ' ||
       v_result.ename || ' ' ||
       v_result.job   || ' ' ||
       v_result.yearpay);
end;
/
/*지금까지 한것은 하나의 변수여서 레코드 값이 하나만 나오기때문에 값이 두개 이상인 결과물이 나오게 되면
  이것은 프로시져 생성시는 문제가 없으나 결과값에서 오류가 난다. 이럴경우 반복문을 사용하여야한다.*/