sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】深入研究Oracle 10G数据库dbms_rowid案例

时间:2016-11-19 18:49   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库dbms_rowid的笔记,深入研究dbms_rowid内部结构和使用方法。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 解密dbms_rowid -for 10g


PACKAGE BODY dbms_rowid IS
  PROCEDURE ROWIDBUILD(ROW_ID   OUT ROWID,
                       TYPE     IN NUMBER,
                       OBJNUM   IN NUMBER,
                       FILENUM  IN NUMBER,
                       BLOCKNUM IN NUMBER,
                       ROWNUM   IN NUMBER);
  PRAGMA INTERFACE(C, ROWIDBUILD);
  PROCEDURE ROWIDINFO(ROW_ID     IN ROWID,
                      TYPE       OUT NUMBER,
                      OBJNUM     OUT NUMBER,
                      FILENUM    OUT NUMBER,
                      BLOCKNUM   OUT NUMBER,
                      ROWNUM     OUT NUMBER,
                      TS_TYPE_IN IN VARCHAR2 DEFAULT 'SMALLFILE');
  PRAGMA INTERFACE(C, ROWIDINFO);

  PROCEDURE ROWIDAFN(ROW_ID       IN ROWID,
                     SCHEMA_NAME  IN VARCHAR2,
                     OBJECT_NAME  IN VARCHAR2,
                     ABSOLUTE_FNO OUT NUMBER);
  PRAGMA INTERFACE(C, ROWIDAFN);

  PROCEDURE ROWIDCNVTE(NEW_ROWID       OUT ROWID,
                       OLD_ROWID       IN ROWID,
              Oracleо         SCHEMA_NAME     IN VARCHAR2,
                       OBJECT_NAME     IN VARCHAR2,
                       CONVERSION_TYPE IN INTEGER);
  PRAGMA INTERFACE(C, ROWIDCNVTE);

  PROCEDURE ROWIDCNVTR(NEW_ROWID       OUT ROWID,
                       OLD_ROWID       IN ROWID,
                       CONVERSION_TYPE IN INTEGER);
  PRAGMA INTERFACE(C, ROWIDCNVTR);

  FUNCTION ROWID_CREATE(ROWID_TYPE    IN NUMBER,
                        OBJECT_NUMBER IN NUMBER,
                        RELATIVE_FNO  IN NUMBER,
                        BLOCK_NUMBER  IN NUMBER,
                        ROW_NUMBER    IN NUMBER) RETURN ROWID IS
    ROW_ID ROWID;
  BEGIN

    ROWIDBUILD(ROW_ID,
               ROWID_TYPE,
               OBJECT_NUMBER,
               RELATIVE_FNO,
               BLOCK_NUMBER,
               ROW_NUMBER);

    RETURN(ROW_ID);
  END;

  PROCEDURE ROWID_INFO(ROWID_IN      IN ROWID,
                       ROWID_TYPE    OUT NUMBER,
                       OBJECT_NUMBER OUT NUMBER,
                       RELATIVE_FNO  OUT NUMBER,
                       BLOCK_NUMBER  OUT NUMBER,
                       ROW_NUMBER    OUT NUMBER,
                       TS_TYPE_IN    IN VARCHAR2 DEFAULT 'SMALLFILE') IS
  BEGIN

    ROWIDINFO(ROWID_IN,
              ROWID_TYPE,
              OBJECT_NUMBER,
              RELATIVE_FNO,
              BLOCK_NUMBER,
              ROW_NUMBER,
              TS_TYPE_IN);
  END;

  FUNCTION ROWID_TYPE(ROW_ID IN ROWID) RETURN NUMBER IS
    ROWID_TYPE    NUMBER;
    OBJECT_NUMBER NUMBER;
    RELATIVE_FNO  NUMBER;
    BLOCK_NUMBER  NUMBER;
    ROW_NUMBER    NUMBER;
  BEGIN

    ROWIDINFO(ROW_ID,
              ROWID_TYPE,
              OBJECT_NUMBER,
              RELATIVE_FNO,
              BLOCK_NUMBER,
              ROW_NUMBER);

    RETURN(ROWID_TYPE);
  END;

  FUNCTION ROWID_OBJECT(ROW_ID IN ROWID) RETURN NUMBER IS
    ROWID_TYPE    NUMBER;
    OBJECT_NUMBER NUMBER;
    RELATIVE_FNO  NUMBER;
    BLOCK_NUMBER  NUMBER;
    ROW_NUMBER    NUMBER;
  BEGIN

    ROWIDINFO(ROW_ID,
              ROWID_TYPE,
              OBJECT_NUMBER,
              RELATIVE_FNO,
              BLOCK_NUMBER,
              ROW_NUMBER);

    RETURN(OBJECT_NUMBER);
  END;

  FUNCTION ROWID_RELATIVE_FNO(ROW_ID     IN ROWID,
                              TS_TYPE_IN IN VARCHAR2 DEFAULT 'SMALLFILE')
    RETURN NUMBER IS
    ROWID_TYPE    NUMBER;
    OBJECT_NUMBER NUMBER;
    RELATIVE_FNO  NUMBER;
    BLOCK_NUMBER  NUMBER;
    ROW_NUMBER    NUMBER;
  BEGIN

    ROWIDINFO(ROW_ID,
              ROWID_TYPE,
              OBJECT_NUMBER,
              RELATIVE_FNO,
              BLOCK_NUMBER,
              ROW_NUMBER,
              TS_TYPE_IN);

    RETURN(RELATIVE_FNO);
  END;

  FUNCTION ROWID_BLOCK_NUMBER(ROW_ID     IN ROWID,
                              TS_TYPE_IN IN VARCHAR2 DEFAULT 'SMALLFILE')
    RETURN NUMBER IS
    ROWID_TYPE    NUMBER;
    OBJECT_NUMBER NUMBER;
    RELATIVE_FNO  NUMBER;
    BLOCK_NUMBER  NUMBER;
    ROW_NUMBER    NUMBER;
  BEGIN

    ROWIDINFO(ROW_ID,
              ROWID_TYPE,
              OBJECT_NUMBER,
              RELATIVE_FNO,
              BLOCK_NUMBER,
              ROW_NUMBER,
              TS_TYPE_IN);

    RETURN(BLOCK_NUMBER);
  END;

  FUNCTION ROWID_ROW_NUMBER(ROW_ID IN ROWID) RETURN NUMBER IS
    ROWID_TYPE    NUMBER;
    OBJECT_NUMBER NUMBER;
    RELATIVE_FNO  NUMBER;
    BLOCK_NUMBER  NUMBER;
    ROW_NUMBER    NUMBER;
  BEGIN

    ROWIDINFO(ROW_ID,
              ROWID_TYPE,
              OBJECT_NUMBER,
              RELATIVE_FNO,
              BLOCK_NUMBER,
              ROW_NUMBER);

    RETURN(ROW_NUMBER);
  END;

  FUNCTION ROWID_TO_ABSOLUTE_FNO(ROW_ID      IN ROWID,
                                 SCHEMA_NAME IN VARCHAR2,
                                 OBJECT_NAME IN VARCHAR2) RETURN NUMBER IS
    ABSOLUTE_FNO NUMBER;
  BEGIN

    ROWIDAFN(ROW_ID, SCHEMA_NAME, OBJECT_NAME, ABSOLUTE_FNO);

    RETURN(ABSOLUTE_FNO);
  END;

  FUNCTION ROWID_TO_EXTENDED(OLD_ROWID       IN ROWID,
                             SCHEMA_NAME     IN VARCHAR2,
                             OBJECT_NAME     IN VARCHAR2,
                             CONVERSION_TYPE IN INTEGER) RETURN ROWID IS
    NEW_ROWID ROWID;
  BEGIN

    ROWIDCNVTE(NEW_ROWID,
               OLD_ROWID,
               SCHEMA_NAME,
               OBJECT_NAME,
               CONVERSION_TYPE);

    RETURN(NEW_ROWID);
  END;

  FUNCTION ROWID_TO_RESTRICTED(OLD_ROWID       IN ROWID,
                               CONVERSION_TYPE IN INTEGER) RETURN ROWID IS
    NEW_ROWID ROWID;
  BEGIN

    ROWIDCNVTR(NEW_ROWID, OLD_ROWID, CONVERSION_TYPE);

    RETURN(NEW_ROWID);
  END;

  FUNCTION ROWID_VERIFY(ROWID_IN        IN ROWID,
                        SCHEMA_NAME     IN VARCHAR2,
                        OBJECT_NAME     IN VARCHAR2,
                        CONVERSION_TYPE IN INTEGER) RETURN NUMBER IS
    NEW_ROWID ROWID;
  BEGIN

    ROWIDCNVTE(NEW_ROWID,
               ROWID_IN,
               SCHEMA_NAME,
               OBJECT_NAME,
               CONVERSION_TYPE);
    RETURN(0);
  EXCEPTION
    WHEN ROWID_INVALID OR ROWID_BAD_BLOCK THEN
      RETURN(1);
  END;

END;

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】深入研究Oracle 10G数据库dbms_rowid案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1294.html

Oracle研究中心

关键词:

dbms_rowid

Oracle 10G dbms_rowid