Oracle PL/SQL/Select Query/rowid

Intersect rowid

 
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL> select * from emp;
     EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-1982 00:00:00       3000                    20
      7839 KING       PRESIDENT            17-NOV-1981 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-1983 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-1981 00:00:00        950                    30
      7902 FORD       ANALYST         7566 03-DEC-1981 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-1982 00:00:00       1300                    10
14 rows selected.
SQL> select * from dept;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
4 rows selected.
SQL>
SQL> select rowid from emp
  2  intersect
  3  select rowid from dept;
no rows selected
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table dept;
Table dropped.
SQL> --

ROWID as data type

 
SQL>
SQL> CREATE TABLE MyTable(MyRow INTEGER, MyDesc VARCHAR2(50));
Table created.
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2       v_CursorID  NUMBER;
  3       v_SelectRecords  VARCHAR2(500);
  4       v_NUMRows  INTEGER;
  5       v_MyNum INTEGER;
  6       v_MyText VARCHAR2(50);
  7       v_MyROWID ROWID;
  8       v_TotRow INTEGER;
  9
 10  BEGIN
 11       v_CursorID := DBMS_SQL.OPEN_CURSOR;
 12       v_SelectRecords := "SELECT * from MyTable FOR UPDATE";
 13
 14
 15       DBMS_SQL.PARSE(v_CursorID,v_SelectRecords,DBMS_SQL.V7);
 16       DBMS_SQL.DEFINE_COLUMN(v_CursorID,1,v_MyNum);
 17       DBMS_SQL.DEFINE_COLUMN(v_CursorID,2,v_MyText,50);
 18
 19       v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
 20      LOOP
 21           IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
 22                EXIT;
 23           END IF;
 24
 25           v_TOTROW := DBMS_SQL.LAST_ROW_COUNT;
 26           v_MyROWID := DBMS_SQL.LAST_ROW_ID;
 27           DBMS_OUTPUT.PUT_LINE("The last row count is: " || v_TOTROW || " The last ROWID is: " || v_MyROWID);
 28
 29           DBMS_SQL.COLUMN_VALUE(v_CursorId,1,v_MyNum);
 30           DBMS_SQL.COLUMN_VALUE(v_CursorId,2,v_MyText);
 31
 32           DBMS_OUTPUT.PUT_LINE(v_MyNum || " " || v_MyText);
 33
 34      END LOOP;
 35
 36  EXCEPTION
 37       WHEN OTHERS THEN
 38                 RAISE;
 39
 40       DBMS_SQL.CLOSE_CURSOR(v_CursorID); -- Close the cursor
 41
 42  END;
 43  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table mytable;
Table dropped.
SQL>
SQL> --