Oracle cursor in sqlplus, and optimizer hints

Example of How to use cursor in Oracle sqlplus,and optimizer hints is used also:

SQL> CREATE TABLE TESTTAB(
  2      A NUMBER,3      B CHAR(4),4      C VARCHAR2(20),5      CONSTRAINT PK_TESTTAB PRIMARY KEY (A));

Table created.

SQL> INSERT INTO TESTTAB VALUES(3,'CCCC','ccccc');

1 row created.

SQL> INSERT INTO TESTTAB VALUES(1,'AAAA','aaaaa');

1 row created.

SQL> INSERT INTO TESTTAB VALUES(2,'BBBB','bbbbb');

1 row created.

SQL> SELECT * FROM TESTTAB;

         A B    C
---------- ---- --------------------
         3 CCCC ccccc
         1 AAAA aaaaa
         2 BBBB bbbbb

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE 
  2      TESTTAB_REC TESTTAB%ROWTYPE;
  3      CURSOR C_TESTTAB IS SELECT /*+ INDEX_DESC(TESTTAB PK_TESTTAB) */ * FROM TESTTAB WHERE A >= 2;
  4  BEGIN 
  5      OPEN C_TESTTAB; 
  6      FETCH C_TESTTAB INTO TESTTAB_REC; 
  7      DBMS_OUTPUT.PUT_LINE (TO_CHAR(TESTTAB_REC.A) || ' ' || TESTTAB_REC.B || ' ' || TESTTAB_REC.C); 
  8      FETCH C_TESTTAB INTO TESTTAB_REC; 
  9      DBMS_OUTPUT.PUT_LINE (TO_CHAR(TESTTAB_REC.A) || ' ' || TESTTAB_REC.B || ' ' || TESTTAB_REC.C); 
 10      CLOSE C_TESTTAB; 
 11  END; 
 12  /
3 CCCC ccccc
2 BBBB bbbbb

PL/SQL procedure successfully completed.

SQL> DECLARE
  2      V_A TESTTAB.A%TYPE;
  3      V_B TESTTAB.B%TYPE;
  4      V_C TESTTAB.C%TYPE;
  5      CURSOR C_TESTTAB IS SELECT /*+ INDEX_ASC(TESTTAB PK_TESTTAB) */ A,B,C FROM TESTTAB;
  6  BEGIN
  7      OPEN C_TESTTAB;
  8      LOOP
  9              FETCH C_TESTTAB INTO V_A,V_B,V_C;
 10              EXIT WHEN C_TESTTAB%NOTFOUND;
 11              DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_A) || ' ' || V_B || ' ' || V_C);
 12      END LOOP;
 13      CLOSE C_TESTTAB;
 14  END;
 15  /
1 AAAA aaaaa
2 BBBB bbbbb
3 CCCC ccccc

PL/SQL procedure successfully completed.

The End

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!