[20110715] SEQUENCE & PROCEDURE

SELECT * FROM EMP;

DELETE FROM EMP
WHERE empno = 7499;

REM Stored PROCEDURE(저장프로시저)
--1)목적 : 속도, 보안
--2)compile(pcode로 변환)상태로 RDBMS 에 저장
--3)나중에 실행될 일련의 명령의 집합
--4)리턴하는 행의 갯수가 많으면 에러난다.
--5)pl/sql에서 SELECT는 반드시 SELECT INTO 를 사용해야
--6)syntax
CREATE OR REPLACE PROCEDURE procedure_name
(
    매개변수 영역
    변수이름    변수모드   변수타입
    v_empno     IN(OUT, IN OUT)        emp.EMPNO%TYPE
)
IS
    내부변
BEGIN
   pl/SQL 문장들
END;

--test.sql로 저장
CREATE OR REPLACE PROCEDURE test
(
     v_empno     IN     emp.empno%TYPE
)
IS
BEGIN
	DELETE FROM emp
	WHERE empno = v_empno;
END test;
/
--실행할 때
EXEC test(7922);

DESC DEPT;

CREATE OR REPLACE PROCEDURE sp_insert_dept
(
    v_deptno    IN     dept.deptno%TYPE,
    v_dname     IN     dept.dname%TYPE,
    v_loc       IN     dept.loc%TYPE
)
IS
BEGIN
  INSERT INTO DEPT
  VALUES (v_deptno, UPPER(v_dname), UPPER(v_loc));
END sp_insert_dept;

EXEC sp_insert_dept(50, 'marketting', 'yatap');

SELECT * FROM dept;

SELECT * FROM EMP
WHERE job  = UPPER('analyst');

CREATE OR REPLACE PROCEDURE sp_emp_sal
(
	v_sal	IN	emp.sal%TYPE,
    v_job	IN	emp.job%TYPE
)
IS
BEGIN
  UPDATE EMP
  SET sal = v_sal
  WHERE job = UPPER(v_job);
END sp_emp_sal;

DROP PROCEDURE sp_emp_sal;

EXEC sp_emp_sal(1000, 'clerk');

SELECT sal FROM emp WHERE job = 'CLERK';

CREATE OR REPLACE PROCEDURE sp_emp_select
(
	v_empno	IN	emp.EMPNO%TYPE,
	v_ename	OUT	emp.ENAME%TYPE,
    v_sal	OUT	emp.sal%TYPE
)
IS
BEGIN
  SELECT ename, sal
  INTO v_ename, v_sal
  FROM EMP
  WHERE empno = v_empno;
END sp_emp_select;

SQL> var g_ename VARCHAR2(20);
SQL> var g_sal    NUMBER;
SQL> EXEC sp_emp_select(7566, :g_ename, :g_sal);

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> print g_ename;

G_ENAME
--------------------------------
JONES

SQL> print g_sal;

     G_SAL
----------
      2975

CREATE SEQUENCE emp_empno_seq
    START WITH 1
    INCREMENT BY 1
    MAXVALUE  999
    NOCYCLE
    CACHE 10;
--Q)emp 테이블에서 새로운 사원의 정보를 이름, 업무,
--매니저, 급여를 입력받아 등록하는 emp_input 프로시저
--를 생성하시오. 단, 부서번호는 매니저의 부서 번호와
--동일하게 하고 보너스는 SALESMAN 은 0을 그 외의 부서는
--NULL을 입력하시오.
CREATE OR REPLACE PROCEDURE emp_input
(
    v_name  IN	emp.ENAME%TYPE,
    v_job	IN	emp.JOB%TYPE,
    v_mgr	IN	emp.MGR%TYPE,
    v_sal	IN	emp.SAL%TYPE
)
IS
	v_comm	emp.comm%TYPE;
    v_deptno emp.DEPTNO%TYPE;
BEGIN
    IF UPPER(v_job) = 'SALESMAN' THEN v_comm := 0
    ELSE v_comm := NULL
    END IF;
    SELECT deptno
    INTO   v_deptno
    FROM emp
    WHERE empno = v_mgr;
    INSERT INTO EMP
    VALUES(EMP_EMPNO_SEQ.NEXTVAL, v_name, UPPER(v_job),
    v_mgr, SYSDATE, v_sal, v_comm, v_deptno);
END emp_input;