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;
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 /
job의 값을 입력하십시오: SALESMAN
구 17: DBMS_SQL.BIND_VARIABLE(v_cursor, 'JOB', '&job');
신 17: DBMS_SQL.BIND_VARIABLE(v_cursor, 'JOB', 'SALESMAN');
Emp = ALLEN
Emp = WARD
Emp = MARTIN
Emp = TURNER
PL/SQL 처리가 정상적으로 완료되었습니다.
댓글 없음:
댓글 쓰기