November 6, 2007
使用REF CURSOR处理Oracle的结果集
作者:eygle
出处:http://blog.eygle.com
Oracle提供REF CURSOR，通过该功能可以实现在程序间传递结果集的功能，利用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 serveroutput 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-82PL/SQL procedure successfully completed.
-The End-
Posted by eygle at 4:54 PM | Comments (5)
Varchar2(4000)能存多少数据?
作者:eygle
出处:http://blog.eygle.com
今天一个朋友在MSN上问我，Varchar2(4000)能存多少数据？
他说他的Varchar2(4000)有的条目，用lengthb计算，达到了4046.
我说不可能，他说可能，但是查询时会出以下错误：
ORA-00600:internal error code, arguments:[rworupo.1],[4046],[4000],[],[],[],[],[]
看来是我错了，在异常情况下，一切皆有可能。
已经出现600错误了，当然可能出现lengthb > 4000 的情况。
这里的rworupo.1代表RoW Operand vector IMPlementation A
Metalink上有很多相关问题及解决方案。记录一下。
-The End-
Posted by eygle at 11:09 AM | Comments (4)