快捷搜索:  as  2018  FtCWSyGV  С˵  test  xxx  Ψһ  w3viyKQx

塞班岛官方网站:如何使用REF CURSOR处理Oracle的结果集



在实际的事情和进修中,我们可以经由过程Oracle数据库供给的REF CURSO塞班岛官方网站R功能实现在法度榜样间通报结果集的功能,别的,使用REF CURSOR可以同时实现BULK SQL,以此前进SQL的机能。

首先,我们必要应用scott用户的emp表实现以下测试:

SQL> desc emp

Name Null? Type

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

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(2)

着末应用ref cursor得到结果集输出:

SQL> set serverout塞班岛官方网站put on

SQL> DECLARE

2 TYPE mytable IS TABLE OF emp%ROWTYPE;

3 l_data mytable;

4 l_refc sys_refcursor;

5 BEGIN

6 OPEN l_refc FOR

7 SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno塞班岛官方网站 FROM emp;

8

9 FETCH l_refc BULK COLLECT INTO l_data;

10

11 CLOSE l_refc;

12

13 FOR i IN 1 .. l_data.COUNT

14 LOOP

15 DBMS_OUTPUT.put_line ( l_data (i).ename

16 || ' was hired since '

17 || l_data (i).hiredate

18 );

19 END LOOP;

20 END;

21 /

SMITH was hired since 17-DEC-80

ALLEN was hired since 20-FEB-81

WARD was hired since 22-FEB-81

JONES was 塞班岛官方网站hired since 02-APR-81

MARTIN was hired since 28-SEP-81

BLAKE塞班岛官方网站 was hired since 01-MAY-81

CLARK was hired since 09-JUN-81

SCOTT was hired since 19-APR-87

KING was hired since 17-NOV-81

TURNER was hired since 08-SEP-81

ADAMS was hired since 23-MAY-87

JAMES was hired since 03-DEC-81

FORD was hired since 03-DEC-81

MILLER was hired since 23-JAN-82

PL/SQL procedure successfully completed.

您可能还会对下面的文章感兴趣: