[ORACLEJAVA커뮤니티오라클교육,SQL교육학원,오라클 REF CURSOR,오라클커서]DBMS_SQL을 이용하여 REF CURSOR 예제
DBMS_SQL을 이용하여 REF CURSOR를 사용한 예제이다.
DBMS_SQL의 ref_cursor지원,DBMS_SQL Dydnamic SQL
SQL> DECLARE
DBMS_SQL을 이용하여 REF CURSOR를 사용한 예제이다.
DBMS_SQL의 ref_cursor지원,DBMS_SQL Dydnamic SQL
SQL> DECLARE
2 type emp_type IS TABLE OF emp%ROWTYPE
3 INDEX BY PLS_INTEGER;
4
5 v_emps emp_type;
6 v_sql CLOB;
7 v_cursor BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;
8 v_rc SYS_REFCURSOR;
9 v_execute BINARY_INTEGER;
4
5 v_emps emp_type;
6 v_sql CLOB;
7 v_cursor BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;
8 v_rc SYS_REFCURSOR;
9 v_execute BINARY_INTEGER;
10 BEGIN
11 v_sql := 'SELECT * FROM emp WHERE job = :job';
12
13
14 DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
15
16
17 DBMS_SQL.BIND_VARIABLE(v_cursor, 'JOB', '&job');
18
19
20 v_execute := DBMS_SQL.EXECUTE(v_cursor);
21 v_rc := DBMS_SQL.TO_REFCURSOR(v_cursor);
22
23 LOOP
24 FETCH v_rc BULK COLLECT INTO v_emps LIMIT 100;
25 FOR i IN 1 .. v_emps.COUNT LOOP
26 DBMS_OUTPUT.PUT_LINE('Emp = ' || v_emps(i).ename);
27 END LOOP;
28 EXIT WHEN v_rc%NOTFOUND;
29 END LOOP;
30 CLOSE v_rc;
31
32 END;
33 /
11 v_sql := 'SELECT * FROM emp WHERE job = :job';
12
13
14 DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
15
16
17 DBMS_SQL.BIND_VARIABLE(v_cursor, 'JOB', '&job');
18
19
20 v_execute := DBMS_SQL.EXECUTE(v_cursor);
21 v_rc := DBMS_SQL.TO_REFCURSOR(v_cursor);
22
23 LOOP
24 FETCH v_rc BULK COLLECT INTO v_emps LIMIT 100;
25 FOR i IN 1 .. v_emps.COUNT LOOP
26 DBMS_OUTPUT.PUT_LINE('Emp = ' || v_emps(i).ename);
27 END LOOP;
28 EXIT WHEN v_rc%NOTFOUND;
29 END LOOP;
30 CLOSE v_rc;
31
32 END;
33 /
job의 값을 입력하십시오: SALESMAN
구 17: DBMS_SQL.BIND_VARIABLE(v_cursor, 'JOB', '&job');
신 17: DBMS_SQL.BIND_VARIABLE(v_cursor, 'JOB', 'SALESMAN');
신 17: DBMS_SQL.BIND_VARIABLE(v_cursor, 'JOB', 'SALESMAN');
Emp = ALLEN
Emp = WARD
Emp = MARTIN
Emp = TURNER
Emp = WARD
Emp = MARTIN
Emp = TURNER
PL/SQL 처리가 정상적으로 완료되었습니다.
댓글 없음:
댓글 쓰기