ORACLE PL_SQL 1일차

-- PL/SQL 블록은 BEGIN과 BLOCK의 배치
-- 실행 방법은 BEGIN - END 선택 후 CTRL + 엔터, 또는 F5
   BEGIN

     UPDATE EMP
     SET SAL = SAL * 1.2
     WHERE DEPTNO = 30;

     DELETE EMP
     WHERE DEPTNO = 10;
   END;

 SET SERVEROUTPUT ON; 

-- PL/SQL 블록은 BEGIN과 BLOCK의 배치
-- 실행 방법은 BEGIN - END 선택 후 CTRL + 엔터, 또는 F5 
BEGIN

  UPDATE EMP
  SET SAL = SAL * 1.2
  WHERE DEPTNO = 30;

  DBMS_OUTPUT.PUT_LINE( SQL%ROWCOUNT || 'rows updated');
 
  DELETE EMP
  WHERE DEPTNO = 10;
END;

DECLARE
   v_hiredate DATE;
   v_deptno NUMBER(2) NOT NULL := 10;  

    -- NOT NULL 이 들어가면 반드시 초기값을 지정해 주어야 함
   v_location VARCHAR2(13) := 'Atlanta';
   c_comm CONSTANT NUMBER := 1400;

   BEGIN

      SELECT  *    

      FROM DEPT;

   END

   => ORA-06550: 줄 3, 열7:PLS-00428: 해당 SELECT 문에 INTO 절이 필요합니다

   이렇게만 실행하면 항상 에러   INTO 절이 항상 있어야 한다. 왜냐하면 블록 내에서는 개별적인 실행 결과를 조회해 주지 않기 때문에..

   그렇다면 어떻게 해야함?

 => ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다

   위와 같은 에러 발생.. 해결? WHERE 절을 추가한다.

   DECLARE
      V_DEPTNO   NUMBER(2) ;
      V_DNAME    DEPT.DNAME%TYPE; 
      V_LOC      DEPT.LOC%TYPE;
  BEGIN 
      SELECT  *  INTO V_DEPTNO, V_DNAME, V_LOC
      FROM DEPT
      WHERE DEPTNO = 10;
  END;

  에러가 발생하는 이유? V_DEPTNO, V_DNAME, V_LOC는 변수 하나만을 저장하는 스칼라 타입이다. 그래서 조건절을 넣어서 반드시 결과값이 하나가 되도록 한다. 

검색되는 행이 없는 경우도 에러로 인식 

   DECLARE
      V_DEPTNO   NUMBER(2) ;
      V_DNAME    DEPT.DNAME%TYPE; 
      V_LOC      DEPT.LOC%TYPE;
  BEGIN 
      SELECT  *  INTO V_DEPTNO, V_DNAME, V_LOC
      FROM DEPT
      WHERE DEPTNO = 50;      <= 에러
  END;

 => ORA-01403: 데이터를 찾을 수 없습니다.

 => UPDATE

  DECLARE
     V_DEPTNO   NUMBER(2) ;
     V_DNAME    DEPT.DNAME%TYPE; 
     V_LOC      DEPT.LOC%TYPE;
  BEGIN 
     SELECT  *  INTO V_DEPTNO, V_DNAME, V_LOC
     FROM DEPT
     WHERE DEPTNO = 10;
  
     UPDATE EMP
     SET DNAME = V_DNAME
     WHERE DEPTNO = V_DEPTNO; 
  END; 
  여러개일 경우 LOOP를 사용..

  DECLARE
    V_DEPTNO   NUMBER(2) ;
    V_DNAME    DEPT.DNAME%TYPE; 
    V_LOC      DEPT.LOC%TYPE;
    V_CNT      V_DEPTNO%TYPE := 10;
  BEGIN 
    LOOP  
      SELECT  *  INTO V_DEPTNO, V_DNAME, V_LOC
      FROM DEPT
      WHERE DEPTNO = V_CNT;
    
      UPDATE EMP
      SET DNAME = V_DNAME
      WHERE DEPTNO = V_DEPTNO;  
    
      V_CNT := V_CNT + 10;
    
      EXIT  WHEN  V_CNT = 40;
    END LOOP;
  END;

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

  DECLARE

     v_date_of_joining DATE:= '02-Feb-2000';

  BEGIN

    NULL;

  END

 => 01858. 00000 -  "a non-numeric character was found where a numeric was expected"

  END

형변환을 명시적으로 해주어야 한다.

   DECLARE
      v_date_of_joining DATE:= TO_DATE('02-Feb-2000','DD-MON-YYYY',
                                                         'NLS_DATE_LANGUAGE=AMERICAN');
   BEGIN
      NULL;
   END;

 BEGIN

   EXCUTE IMMEDIATE 'DROP TABPE EMP' ;

 END ;

 FLASHBACK TABLE EMP TO BEFORE DROP;

DECLARE
  v_rows_deleted VARCHAR2(30) v_empno employees.employee_id%TYPE := 176;
BEGIN
  DELETE FROM employees WHERE employee_id = v_empno;
  v_rows_deleted := (SQL%ROWCOUNT || ' row deleted.');
  DBMS_OUTPUT.PUT_LINE (v_rows_deleted);
END;

 SET SERVEROUTPUT ON;
DECLARE
  v_myage NUMBER ;
BEGIN
  IF  v_myage < 11 THEN
    DBMS_OUTPUT.PUT_LINE('I AM A CHILD');
  ELSE
    DBMS_OUTPUT.PUT_LINE('I AM not A CHILD');
  END IF;
END;

=> 중요한 것은 IF 문은 TRUE 일 경우만 호출되고 ELSE구문은 FALSE 인 경우 뿐만 아니라 NULL 값인 경우에도 실행이 된다.

DECLARE
   v_countryid locations.country_id%TYPE := 'CA';
   v_loc_id locations.location_id%TYPE;
   v_counter NUMBER(2) := 1;
   v_new_city locations.city%TYPE := 'Montreal';
BEGIN
   SELECT MAX(location_id) INTO v_loc_id FROM locations
   WHERE country_id = v_countryid;
   LOOP
      INSERT INTO locations(location_id, city, country_id)
      VALUES((v_loc_id + v_counter), v_new_city, v_countryid);
      v_counter := v_counter + 1;
      EXIT WHEN v_counter > 3;
   END LOOP;
END;

=>현재  locations 테이블에 존재하는 location_id 값에 1을 더해서 'CA', 'Montreal'값을 저장

    여기서 INSERT 는 총 세번 진행

DECLARE
   v_countryid locations.country_id%TYPE := 'CA';
   v_loc_id locations.location_id%TYPE;
   v_new_city locations.city%TYPE := 'Montreal';
   v_counter NUMBER := 1;
BEGIN
   SELECT MAX(location_id) INTO v_loc_id FROM locations
   WHERE country_id = v_countryid;
   WHILE v_counter <= 3 LOOP  => 조건식을 먼저 체크
      INSERT INTO locations(location_id, city, country_id)
      VALUES((v_loc_id + v_counter), v_new_city, v_countryid);
      v_counter := v_counter + 1;
   END LOOP;
END;    => WHILE LOOP으로 변경

DECLARE
   v_countryid locations.country_id%TYPE := 'CA';
   v_loc_id locations.location_id%TYPE;
   v_new_city locations.city%TYPE := 'Montreal';
BEGIN
   SELECT MAX(location_id) INTO v_loc_id
   FROM locations
   WHERE country_id = v_countryid;
   FOR i IN 1..3 LOOP
      INSERT INTO locations(location_id, city, country_id)
      VALUES((v_loc_id + i), v_new_city, v_countryid );
   END LOOP;
END;    => FOR LOOP으로 변경

※ FOR LOOP 시 주의사항

• 루프 안에 있는 카운터만 참조하십시오. 루프 밖에는 카운터가 정의되어 있지 않습니다.
• 카운터를 할당 대상으로 참조하지 마십시오.
• 루프 상한이나 하한은 NULL일 수 없습니다.

SET SERVEROUTPUT ON;
DECLARE
    EMP_REC EMP%ROWTYPE;
BEGIN
    SELECT * INTO EMP_REC
    FROM EMP  WHERE EMPNO = 7788;
    DBMS_OUTPUT.PUT_LINE(EMP_rEC.EMPNO||' '||EMP_REC.ENAME);
END;

 => 구조체 비스무리한 개념이 있다.

SET SERVEROUTPUT ON;
DECLARE
   TYPE  EMP_REC_TYP  IS RECORD
    ( EMPNO NUMBER(4),
       ENAME EMP.ENAME%TYPE,
       SAL   NUMBER(7,2) );
   EMP_REC EMP_REC_TYP ;
BEGIN
   SELECT EMPNO, ENAME, SAL INTO EMP_REC
   FROM EMP  WHERE EMPNO = 7788;
   DBMS_OUTPUT.PUT_LINE(EMP_rEC.EMPNO||' '||EMP_REC.ENAME);
END;

 SET SERVEROUTPUT ON
DECLARE
  TYPE  ENAME_TAB_TYP IS TABLE OF VARCHAR2(10)   => 배열 구조 생성시 IS TABLE OF
  INDEX BY  PLS_INTEGER;
 
  ENAME_TAB ENAME_TAB_TYP;
BEGIN
  ENAME_TAB(1):=  'AAA';
  ENAME_TAB(2):=  'BBB';
  ENAME_TAB(3):=  'CCC';
  ENAME_TAB(4):=  'DDD';

  IF  ENAME_TAB.EXISTS(4)  THEN     => 실제 그 배열에 값이 있는지 체크하는 메소드 제공

     DBMS_OUTPUT.PUT_LINE(ENAME_TAB(3));

  END IF;

  DBMS_OUTPUT.PUT_LINE('ENAME_TAB.COUNT() = ' || ENAME_TAB.COUNT());
  DBMS_OUTPUT.PUT_LINE('ENAME_TAB.FIRST() = ' || ENAME_TAB.FIRST());
  DBMS_OUTPUT.PUT_LINE('ENAME_TAB.LAST()  = ' || ENAME_TAB.LAST());

END;

 DECLARE
  TYPE  EMP_TAB_TYPE IS TABLE OF EMP%ROWTYPE;
  EMP_TAB EMP_TAB_TYPE;
BEGIN
  SELECT * BULK COLLECT INTO EMP_TAB
  FROM EMP;
END;

 여러 FETCH 된 값을 한번에 중첩 테이블에 넣을 수 있다.  한번에 갖고와서 데이터를 처리하면 CURSOR를 이용하여 한건 한건 처리하는 것보다 성능의 향상을 맛볼 수 있다.