소스플로우

-- 조합 데이터 타입의 2가지 종류

 -- 1. 레코드 (RECORD)

 -- 2. 컬렉션 (COLLECTION)

-- CURSOR

-- 수행하는 SQL문의 결과를 처리하기 위한 메모리영역

 -- 1. 암시적 커서: SQL%rowcount와 같은 커서를 말한다.

 -- 2. 명시적 커서: 메모리를 올려놓고 빈곳에다가 올리고 싶은 데이터를 올린다. 

-- EMP_CURSOR%FOUND : 커서에서 데이터가 발견되면 TRUE

-- EMP_CURSOR%NOTFOUND : 커서에서 데이터가 발견되지 않으면 TRUE 

1-2 부서번호를 입력하면 해당 부서번호에 속한 사원들의 사원번호, 이름, 월급을 출력하는 프로시저를 작성하라.

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE PR_TAB_EMP867_ALL_DATA

(P_DEPT_NO NUMBER)

IS

-- CURSOR 명 : EMP_CURSOR

-- EMP_CURSOR 에 조건에 맞는 TB_LS_EMP867 테이블에서 가져온 데이터들을 담아준다.

    CURSOR EMP_CURSOR IS

    SELECT EMP_NO, EMP_ID, EMP_NM, DEPT_NO, SAL

    FROM TB_LS_EMP867

    WHERE DEPT_NO = P_DEPT_NO;

-- 변수를 선언한다.

    V_EMP_NO NUMBER(8) := 0;

    V_EMP_ID VARCHAR2(30);

    V_EMP_NM VARCHAR2(20);

    V_DEPT_NO NUMBER(4) := 0;

    V_SAL NUMBER(10) := 0;

BEGIN

-- CURSOR를 연다.

    OPEN EMP_CURSOR;

-- LOOP 을 돌리면서 EMP_CURSOR 안에 담긴 데이터들을 변수에 삽입한다.

    LOOP

-- FETCH: (어디를가서)가져오다.

      FETCH EMP_CURSOR INTO V_EMP_NO, V_EMP_ID, V_EMP_NM, V_DEPT_NO, V_SAL;

      EXIT WHEN EMP_CURSOR%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(V_EMP_NM || '님의 레코드는 다음과 같습니다. : ' || V_DEPT_NO || ' | ' || V_EMP_NM || ' | ' || V_EMP_ID || ' | '  

|| V_DEPT_NO || ' | ' || V_SAL );

    END LOOP;

-- CURSOR를 닫는다.

    CLOSE EMP_CURSOR;

END;

/

EXEC PR_TAB_EMP867_ALL_DATA(82);

-- 조합 데이터 타입의 2가지 종류

 -- 1. 레코드 (RECORD)

 -- 2. 컬렉션 (COLLECTION)

-- CURSOR

-- 수행하는 SQL문의 결과를 처리하기 위한 메모리영역

 -- 1. 암시적 커서: SQL%rowcount와 같은 커서를 말한다.

 -- 2. 명시적 커서: 메모리를 올려놓고 빈곳에다가 올리고 싶은 데이터를 올린다. 

-- EMP_CURSOR%FOUND : 커서에서 데이터가 발견되면 TRUE

-- EMP_CURSOR%NOTFOUND : 커서에서 데이터가 발견되지 않으면 TRUE 

###########################################################################################

1-1. 부서번호를 물어보게 하고 부서번호를 입력하면 해당 부서번호에 속한 사원들의 사원번호, 이름, 월급을 출력하시오.

SET SERVEROUTPUT ON;

ACCEPT P_DEPT_NO PROMPT '정보를 원하는 부서번호를 입력하세요.';

DECLARE 

    -- CURSOR 명 : EMP_CURSOR

    -- EMP_CURSOR 에 조건에 맞는 TB_LS_EMP867 테이블에서 가져온 데이터들을 담아준다.

    CURSOR EMP_CURSOR IS

    SELECT EMP_NO, EMP_NM, EMP_ID, DEPT_NO, SAL

    FROM TB_LS_EMP867

    WHERE DEPT_NO = &P_DEPT_NO;

    -- 변수를 선언한다.

    V_EMP_NO NUMBER(8) := 0;

    V_EMP_ID VARCHAR2(30);

    V_EMP_NM VARCHAR2(20);

    V_DEPT_NO NUMBER(4) := 0;

    V_SAL NUMBER(10) := 0;

BEGIN

    -- CURSOR를 연다.

    OPEN EMP_CURSOR; 

    -- LOOP 을 돌리면서 EMP_CURSOR 안에 담긴 데이터들을 변수에 삽입한다.

    LOOP 

      -- FETCH: (어디를가서)가져오다.

      FETCH EMP_CURSOR INTO V_EMP_NO, V_EMP_NM, V_EMP_ID, V_DEPT_NO, V_SAL;

      EXIT WHEN EMP_CURSOR%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(V_EMP_NM || '님의 레코드는 다음과 같습니다. : ' || V_DEPT_NO || ' | ' || V_EMP_NM || ' | ' || V_EMP_ID || ' | ' ||

      V_DEPT_NO || ' | ' || V_SAL );

    END LOOP;

    -- CURSOR를 닫는다.

    CLOSE EMP_CURSOR;

END;

/

SET SERVEROUTPUT ON;

ACCEPT P_DEPT_NO PROMPT '정보를 원하는 부서번호를 입력하세요.';

DECLARE 

    V_EMP_NO NUMBER(8) := 0;

    V_EMP_ID VARCHAR2(30);

    V_EMP_NM VARCHAR2(20);

    V_DEPT_NO NUMBER(4) := &P_DEPT_NO;

    V_SAL NUMBER(10) := 0;

BEGIN

    SELECT EMP_NO, EMP_ID, EMP_NM, DEPT_NO, SAL 

    INTO V_EMP_NO, V_EMP_ID, V_EMP_NM, V_DEPT_NO, V_SAL

    FROM TB_LS_EMP867

    WHERE DEPT_NO = V_DEPT_NO;

    DBMS_OUTPUT.PUT_LINE(V_EMP_NO);

    DBMS_OUTPUT.PUT_LINE(V_EMP_ID);

    DBMS_OUTPUT.PUT_LINE(V_EMP_NM);

    DBMS_OUTPUT.PUT_LINE(V_DEPT_NO);

    DBMS_OUTPUT.PUT_LINE(V_SAL);

--ORA-01403: no data found 대처 SQL문  

-- 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다.

    EXCEPTION

    WHEN NO_DATA_FOUND THEN NULL;

    WHEN OTHERS THEN

    NULL;

END;

/

-- EXCEPTION 처리를 해줘야한다. (루프를 안돌렸으므로) 그렇지않으면 에러 발생 exact fetch returns more than requested number of rows

1-1. 사원번호를 물어보게하고 사원번호를 입력하면 해당 사원의 모든 컬럼의 정보가  EMP745 테이블에 입력되게 하는 PL/SQL을 작성하시

오.

SELECT * FROM EMP745;

-----------------------------------------------------------------------------------------------------

SET SERVEROUTPUT ON;

ACCEPT P_EMP_NO PROMPT 'EMP745테이블로 복사할 사원번호를 입력하세요'

DECLARE 

  V_DEPT_NO NUMBER(4) := 0;

  V_EMP_NO NUMBER(8) := &P_EMP_NO;

  V_EMP_NM VARCHAR2(20);

  V_EMP_ID VARCHAR2(30);

  V_SAL NUMBER(10) :=0;

BEGIN

  SELECT DEPT_NO, EMP_NO, EMP_NM, EMP_ID, SAL 

  INTO V_DEPT_NO, V_EMP_NO, V_EMP_NM, V_EMP_ID, V_SAL

  FROM TB_LS_EMP867

  WHERE EMP_NO = V_EMP_NO;

  DBMS_OUTPUT.PUT_LINE(V_EMP_NM || '님의 레코드는 다음과 같습니다. : ' || V_DEPT_NO || ' | ' || V_EMP_NM || ' | ' || V_EMP_ID || ' | ' || 

V_SAL);

  -- EMP745테이블에 DB MIGRATION

  INSERT INTO EMP745 (DEPT_NO, EMP_NO, EMP_NM, EMP_ID, SAL )

  VALUES (V_DEPT_NO, V_EMP_NO, V_EMP_NM, V_EMP_ID, V_SAL);

END;

/

-- EMP745 테이블 확인 

SELECT * FROM EMP745;

1-2. 사원번호를 입력하면 해당 사원의 모든 컬럼의 정보가  EMP745 테이블에 입력되게 하는 프로시저를 생성하라.

-- EXEC PR_MIG_EMP745(1)

-- LPAD 함수를 사용하라. LPAD(변수,8,0)

CREATE OR REPLACE PROCEDURE PR_MIG_EMP745

  (P_EMP_NO NUMBER)

IS

  V_DEPT_NO NUMBER(4) := 0;

  V_EMP_NO NUMBER(8) := LPAD(P_EMP_NO,8,0);   --LPAD 함수를 사용

  V_EMP_NM VARCHAR2(20);

  V_EMP_ID VARCHAR2(30);

  V_SAL NUMBER(10) :=0;

BEGIN

  SELECT DEPT_NO, EMP_NO, EMP_NM, EMP_ID, SAL 

  INTO V_DEPT_NO, V_EMP_NO, V_EMP_NM, V_EMP_ID, V_SAL

  FROM TB_LS_EMP867

  WHERE EMP_NO = V_EMP_NO;

  DBMS_OUTPUT.PUT_LINE(V_EMP_NM || '님의 레코드는 다음과 같습니다. : ' || V_DEPT_NO || ' | ' || V_EMP_NM || ' | ' || V_EMP_ID || ' | ' || 

V_SAL);

  -- EMP745테이블에 DB MIGRATION

  INSERT INTO EMP745 (DEPT_NO, EMP_NO, EMP_NM, EMP_ID, SAL )

  VALUES (V_DEPT_NO, V_EMP_NO, V_EMP_NM, V_EMP_ID, V_SAL);

END;

/

EXEC PR_MIG_EMP745(2);

SELECT * FROM EMP745;

-- 1. WHILE .. LOOP 을 중첩으로 사용하여 4단 ~7단까지 출력하는 프로시저를 생성해보아라.

CREATE OR REPLACE PROCEDURE PR_GUGUDAN_4TO7

IS 

  V_OUT_DAN NUMBER(2) := 7;

  V_IN_DAN NUMBER(2) := 9;

  i NUMBER(2) := 3;

  j NUMBER(2) := 0;

BEGIN    -- 내가 생각하는 while 문 문법과 조금 다르다.

  <<Outer_loop>>

    WHILE i < V_OUT_DAN LOOP

      i := i+1;

      j := 0;

    DBMS_OUTPUT.PUT_LINE(i);

    <<Inner_loop>>

      WHILE j < V_IN_DAN LOOP

        j := j+1;

        DBMS_OUTPUT.PUT_LINE(j);

        DBMS_OUTPUT.PUT_LINE( i || '*' || j || ' = ' || i*j );

      END LOOP Inner_loop;

    END LOOP Outer_loop;

END;

/

EXEC PR_GUGUDAN_4TO7;

2-1. 부서번호를 물어보게 하고 부서번호를 입력하면 해당 부서번호의 토탈월급을 출력되게 하시오.

SET SERVEROUTPUT ON

SET VERIFY OFF

ACCEPT P_DEPT_NO PROMPT '부서번호를 입력하시오!'

DECLARE 

  V_DEPT_NO NUMBER(4) := &P_DEPT_NO; --&를 배먹으면 안된다.

  V_SUM_SAL NUMBER(10) := 0;

BEGIN 

  SELECT SUM(SAL) 

  INTO V_SUM_SAL

  FROM TB_LS_EMP867

  WHERE DEPT_NO = V_DEPT_NO;

  DBMS_OUTPUT.put_line('TOTAL SALAD: '||V_SUM_SAL);

END;

/

2-2. 부서번호를 입력하면 해당 부서번호의 토탈월급을 출력되게 하는 프로시저를 만들어라.

-- 프로시저호출 EXEC PR_DEPT_TOT_SAL(82)은 82번으로 해라.

CREATE OR REPLACE PROCEDURE PR_DEPT_TOT_SAL

  ( P_DEPT_NO IN NUMBER ) 

IS

BEGIN

  DECLARE

  V_DEPT_SAL NUMBER(10):= 0;

  BEGIN 

    SELECT SUM(SAL) 

    INTO V_DEPT_SAL

    FROM TB_LS_EMP867 

    WHERE DEPT_NO = P_DEPT_NO;

    DBMS_OUTPUT.PUT_LINE(V_DEPT_SAL);

  END;

END;

/

EXEC PR_DEPT_TOT_SAL(82);

-- 부서번호를 입력하면 해당 부서번호의 토탈월급을 출력되게 하는 프로시저를 만들어라.

-- 프로시저호출 EXEC PR_DEPT_TOT_SAL(82)은 82번으로 해라.

CREATE OR REPLACE PROCEDURE PR_DEPT_TOT_SAL

  ( P_DEPT_NO IN NUMBER ) 

IS 

  V_DEPT_SAL NUMBER(10):= 0;  -- DECLARE는 생략가능하다.

  BEGIN 

    SELECT SUM(SAL) 

    INTO V_DEPT_SAL

    FROM TB_LS_EMP867 

    WHERE DEPT_NO = P_DEPT_NO;

    DBMS_OUTPUT.PUT_LINE(V_DEPT_SAL);

  END;

/

EXEC PR_DEPT_TOT_SAL(82);

3-1. 사원번호를 물어보게 하고 사원의 부서번호, 이름, 월급, 아이디를 출력하게 하시오.

SET SERVEROUTPUT ON

ACCEPT P_EMP_NO PROMPT '사원번호를 입력하시오!'

DECLARE 

  V_EMP_NO NUMBER(8) := LPAD(&EMP_NO,8,0);

  V_EMP_NM VARCHAR2(30);

  V_EMP_ID VARCHAR2(30);

  V_SAL NUMBER(20) := 0;

  V_DEPT_NO NUMBER(4) := 0;

BEGIN 

  SELECT EMP_NM, EMP_ID, SAL, DEPT_NO 

  INTO V_EMP_NM, V_EMP_ID, V_SAL, V_DEPT_NO

  FROM TB_LS_EMP867

  WHERE EMP_NO = V_EMP_NO;

  DBMS_OUTPUT.put_line('이름: ' || V_EMP_NM);

  DBMS_OUTPUT.put_line('부서번호: ' || V_DEPT_NO);

  DBMS_OUTPUT.put_line('아이디: ' || V_EMP_ID);

  DBMS_OUTPUT.put_line('월급: ' || V_SAL);

END;  

/

3-2. 사원번호를 입력하면 해당사원의 부서번호, 이름, 월급, 아이디를 출력하는 프로시저를 만들어라.

-- 프로시저호출 EXEC PR_MEM_INFO(1)은 1번으로 해라. 

CREATE OR REPLACE PROCEDURE PR_MEM_INFO 

  (P_EMP_NO IN NUMBER) -- 프로시저의 IN, OUT 변수를 만들때에는 BYTE수를 쓰지 않아야한다. NUMBER(8)이런거 ERROR

IS

  V_EMP_NM VARCHAR2(30);

  V_EMP_ID VARCHAR2(30);

  V_SAL NUMBER(20) := 0;

  V_DEPT_NO NUMBER(4) := 0;

BEGIN

  SELECT EMP_NM, EMP_ID, SAL, DEPT_NO 

  INTO V_EMP_NM, V_EMP_ID, V_SAL, V_DEPT_NO

  FROM TB_LS_EMP867

  WHERE EMP_NO = P_EMP_NO;

  DBMS_OUTPUT.put_line('이름: ' || V_EMP_NM);

  DBMS_OUTPUT.put_line('부서번호: ' || V_DEPT_NO);

  DBMS_OUTPUT.put_line('아이디: ' || V_EMP_ID);

  DBMS_OUTPUT.put_line('월급: ' || V_SAL);

END;

/

EXEC PR_MEM_INFO(1);

-- ★☆★☆★ basic loop (FOR LOOP)

-- 1.숫자 1부터 20까지 출력하는데 for loop 문으로 구현해서 출력하시오.

SET SERVEROUTPUT ON -- default : serveroutput (dbms_output.put_line) 이 OFF 상태이다.

BEGIN 

  FOR i IN 1..20 LOOP

    DBMS_OUTPUT.PUT_LINE('출력숫자 : ' || i );

  END LOOP;

END;  

/

-- 2. FOR .. LOOP 사용하여 구구단 6단을 출력하시오.

DECLARE 

  V_COUNT NUMBER(10) := 0;

BEGIN 

  FOR i IN 1..9 LOOP

    V_COUNT := 6 * i;

    DBMS_OUTPUT.PUT_LINE ( '6' || ' * ' || i || ' = ' || 6*i); 

  END LOOP;

END;

/

--3. FOR LOOP 을 중첩해서 구구단 2단 ~ 4단까지 출력하시오.

BEGIN   

  FOR i IN 2..4 LOOP

    FOR j IN 1..9 LOOP

    DBMS_OUTPUT.PUT_LINE( i || ' * ' || j  || ' = ' || i*j);  

    END LOOP;

  END LOOP;

END;

/

-- LPAD 함수: 좌측에 자리수 만큼 채워주는 함수

-- 사용법 : LPAD(변수, 길이, 변형자)

-- ex) SELECT LPAD('123',8,0) FROM DUAL

--4. TB_LS_EMP867 테이블을 생성하고 for 문을 이용해서 data를 입력하시오.

CREATE TABLE TB_LS_EMP867

  ( EMP_NO  NUMBER(8), EMP_ID VARCHAR2(12) );

DECLARE 

  V_EMP_NO VARCHAR2(8):= 0; 

  V_EMP_ID VARCHAR2(12);

BEGIN 

  FOR i IN 1..20 LOOP

    INSERT INTO TB_LS_EMP867(EMP_NO, EMP_ID)

    VALUES (LPAD((i),8,0),'KOR-'|| LPAD((i),8,0));

  END LOOP;  

END;

/

SELECT * FROM TB_LS_EMP867;

--5. 프로시저를 만들어보자.

CREATE OR REPLACE PROCEDURE PR_ISRT_TAB_EMP

IS

BEGIN

  DECLARE 

    V_EMP_NO VARCHAR2(8):= 0; 

    V_EMP_ID VARCHAR2(12);

  BEGIN 

    FOR i IN 1..20 LOOP

      INSERT INTO TB_LS_EMP867(EMP_NO, EMP_ID)

      VALUES (LPAD((i),8,0),'KOR-'|| LPAD((i),8,0));

    END LOOP;  

  END;

END;   

/

EXEC PR_ISRT_TAB_EMP;

--EXECUTE PR_ISRT_TAB_EMP;

--CALL PR_ISRT_TAB_EMP;

SELECT * FROM TB_LS_EMP867;