■ SELECT ~ BULK COLLECT INTO문을 사용해 데이터를 일괄로 추가하는 방법을 보여준다.
▶ 예제 코드 (SQL)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
-- 직원 테이블을 생성한다. CREATE TABLE EMP ( EMPNO NUMBER(4, 0) ,ENAME VARCHAR2(10) ,SAL NUMBER(7, 2) ,CONSTRAINT PK_EMP PRIMARY KEY (EMPNO) ); -- 직원 테이블에 데이터를 추가한다. INSERT INTO EMP VALUES(7369, 'SMITH' , 800); INSERT INTO EMP VALUES(7499, 'ALLEN' , 1600); INSERT INTO EMP VALUES(7521, 'WARD' , 1250); INSERT INTO EMP VALUES(7566, 'JONES' , 2975); INSERT INTO EMP VALUES(7654, 'MARTIN', 1250); INSERT INTO EMP VALUES(7698, 'BLAKE' , 2850); INSERT INTO EMP VALUES(7782, 'CLARK' , 2450); INSERT INTO EMP VALUES(7788, 'SCOTT' , 3000); INSERT INTO EMP VALUES(7839, 'KING' , 5000); INSERT INTO EMP VALUES(7844, 'TURNER', 1500); INSERT INTO EMP VALUES(7876, 'ADAMS' , 1100); INSERT INTO EMP VALUES(7900, 'JAMES' , 950 ); INSERT INTO EMP VALUES(7902, 'FORD' , 3000); INSERT INTO EMP VALUES(7934, 'MILLER', 1300); COMMIT; SET SERVEROUTPUT ON; DECLARE TYPE NUMBER_TABLE_TYPE IS TABLE OF NUMBER; V_SAL_NUMBER_TABLE NUMBER_TABLE_TYPE; BEGIN SELECT SAL BULK COLLECT INTO V_SAL_NUMBER_TABLE FROM EMP; IF SQL%ROWCOUNT > 0 THEN FOR I IN V_SAL_NUMBER_TABLE.FIRST..V_SAL_NUMBER_TABLE.LAST LOOP DBMS_OUTPUT.PUT_LINE(V_SAL_NUMBER_TABLE(I)); END LOOP; END IF; END; |