[ORACLE] PL/SQL [2]

 

 

 # cursor for loop(암묵적 커서)
​

- 따로 커서를 선언하지 않아도 IN안에 SELECT문을 넣으면 자동으로 읽음
- 가장 많이 씀

​

CREATE OR REPLACE PROCEDURE p_emp_info
  (p_dept_id s_emp.dept_id%TYPE)
IS
BEGIN
      FOR  emp_record  IN 
               ( SELECT last_name, salary  FROM s_emp
                WHERE dept_id = p_dept_id )                                      // IN 안에 SELECT문 바로 옴   
     LOOP 
               DBMS_OUTPUT.PUT_LINE(emp_record.last_name || ' ' 
                        || emp_record.salary);
     END LOOP;
END;
/

​
 #  WHERE CURRENT OF

​

DECLARE 
      CURSOR  c_emp_sal  IS
              SELECT last_name, salary  FROM s_emp FOR UPDATE;   

               // SELECT절을 이용해 데이터를 메모리에 올려 수정을 할려는데 중간에
              // 다른사람이 수정을 하면 꼬이니까 FOR UPDATE로 락을걸음
​BEGIN
           FOR  emp_record  IN  c_emp_sal  LOOP
                    DBMS_OUTPUT.PUT_LINE(emp_record.last_name || ' '
                                                 || emp_record.salary);
                    IF  emp_record.salary < 2000  THEN
                            UPDATE s_emp  SET salary = salary * 1.2 
                            WHERE CURRENT OF c_emp_sal;                // 커서의 현재행 수정
                    ELSE
                            UPDATE s_emp  SET salary = salary * 1.1 
                            WHERE CURRENT OF c_emp_sal;
           END IF;
           END LOOP;
  commit;                   // LOOP 가 끝나기전에 commit하면 락이 풀려서 다른사람이 수정 할 수 있음
END;
/

  # Parameter가 있는 커서

CREATE OR REPLACE PROCEDURE p_emp_info
IS
      CURSOR  c_dept  IS
               SELECT *  FROM s_dept;
      CURSOR  c_emp_sal  (p_dept_id   s_emp.dept_id%TYPE)  IS

               ​// 커서가 오픈할때 파라미터를 받아 올 수있다(부서이름)
               SELECT last_name, salary  FROM s_emp
               WHERE dept_id = p_dept_id;
BEGIN
       FOR dept  IN  c_dept  LOOP
       DBMS_OUTPUT.PUT_LINE('=========================');
       DBMS_OUTPUT.PUT_LINE(dept.id|| ' :: ' || dept.name);
       // 부서명을 커서로 얻음
                FOR  emp_record  IN  c_emp_sal (dept.id)  LOOP
                DBMS_OUTPUT.PUT_LINE(emp_record.last_name || ' ' || emp_record.salary);
                // 받아온 파라미터의 부서명 안에 있는 정보들을 커서로 얻음
                END LOOP;
       END LOOP;
END;
/

 #  위치와 이름 표기법

CREATE OR REPLACE PROCEDURE CallMe(
       p_ParameterA VARCHAR2,
       p_ParameterB NUMBER,
       p_ParameterC BOOLEAN,
       p_ParameterD DATE) AS
BEGIN
      NULL;
END CallMe;
/

​

​

 1.위치 표기법

DECLARE
     v_Variable1 VARCHAR2(10);
     v_Variable2 NUMBER(7,6);
     v_Variable3 BOOLEAN;
     v_Variable4 DATE;
BEGIN
    CallMe(v_Variable1, v_Variable2, v_Variable3, v_Variable4);
              // 파라미터 순서대로 호출(위치에 의한 호출)
END;
/

​

​
  2.이름 표기법
 
 
DECLARE
     v_Variable1 VARCHAR2(10);
     v_Variable2 NUMBER(7,6);
     v_Variable3 BOOLEAN;
     v_Variable4 DATE;
BEGIN
     CallMe(p_ParameterB => v_Variable2,
               p_ParameterA => v_Variable1,
               p_ParameterD => v_Variable4,
               p_ParameterC => v_Variable3  );
               // 이름을 지정해 호출(순서는 상관없음)
END;
/

​

 Q.이런거 언제 쓰나?

CREATE OR REPLACE PROCEDURE AddNewStudent (
       p_FirstName  students.first_name%TYPE,
       p_LastName   students.last_name%TYPE,
       p_Major      students.major%TYPE DEFAULT 'Economics') AS      //  p_Major 디폴트 값 입력
BEGIN
       INSERT INTO students (ID, first_name, last_name,major, current_credits)
       VALUES (student_sequence.nextval, p_FirstName, p_LastName,p_Major, 0);
END AddNewStudent;
/

​ 2가지 방법으로 호출

​

BEGIN
    AddNewStudent('Zelda', 'Zudnik');                  // 위치
END;
/

BEGIN
  AddNewStudent( p_LastName  => 'Vassily'

                        ​p_FirstName => 'Veronica' );       // 이름
END;
/

 이거는 둘다 된다. 그러나 디폴트값을 준 파라미터가 첫번째,사이사이에 있을 경우
 위치 표기법은 표현 할 수 없다. 그래서 이럴경우 이름 표기법만 사용가능하다.