oracle 19c学习笔记1

• 表自增列的使用

GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL 和MSSQL IDENTITY(1,1)类似

CREATE TABLE FIT_DATACENTER.BSFC_INSIGHTOPERATION (
    ID NUMBER(11,0) GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
    PRODUCTCODE NVARCHAR2(50) NOT NULL,
    PROCESSCODE NVARCHAR2(10) NOT NULL,
    PRODUCTNO NVARCHAR2(100) NOT NULL,
    OPERATIONNAME NVARCHAR2(50) NOT NULL,
    GHOPNAME NVARCHAR2(50) NULL,
    INSIGHTOPNAME NVARCHAR2(50) NULL,
    STATIONCLASS NVARCHAR2(50) NULL,
    DATATABLE NVARCHAR2(100) NULL,
    DATABDB NVARCHAR2(50) NULL,
    FAReturnOPERATIONNAME NVARCHAR2(50) NULL,
    CHECKRULE NVARCHAR2(50) NULL,
    ADDRULE NVARCHAR2(50) NULL,
    TESTPOLICY NVARCHAR2(10) NULL,
    OPENTIME_01 NUMBER(10) NULL,
    OPENTIME_02 NUMBER(10) NULL,
    REMARK NVARCHAR2(40) NULL,
    UOPCHECK NVARCHAR2(50) NULL,
    OFFLINEFLAG NVARCHAR2(50) NULL,
    OFFLINETYPE NVARCHAR2(50) NULL,
    OFFLINENSEQ NUMBER(10) NULL,
    CHECKALL NVARCHAR2(10) NULL,
    CHECKSAMPLESTATION NVARCHAR2(100) NULL,
    PASSPOLICY NVARCHAR2(50) NULL,
    PASSMESSTATION NVARCHAR2(100) NULL,
    DELFLAG NUMBER(1) DEFAULT 0 NOT NULL,
    CREATEUSER NVARCHAR2(20) NULL,
    CREATEDATE DATE DEFAULT SYSDATE NOT NULL,
    MODIFYUSER NVARCHAR2(20) NULL,
    MODIFYDATE DATE DEFAULT SYSDATE NOT NULL,
    CONSTRAINT PK_BSFC_INSIGHTOPERATION PRIMARY KEY (ID)
) TABLESPACE "D_BASE_DATA";

•  存储过程隐式返回结果集

在Oracle 19c中，使用 DBMS_SQL.RETURN_RESULT可以让存储过程隐式返回一个或多个结果集，而无需显式定义OUT参数。这在从其他数据库（如SQL Server）迁移存储过程时特别有用。以下是一个清晰的示例和说明：

CREATE OR REPLACE PROCEDURE get_employee_basic (p_emp_id IN NUMBER DEFAULT NULL)
AS
  l_cursor SYS_REFCURSOR;
BEGIN
  -- 打开游标用于查询
  OPEN l_cursor FOR
    SELECT employee_id, first_name, last_name, email
    FROM employees
    WHERE employee_id = NVL(p_emp_id, employee_id); -- 如果参数为NULL则返回所有员工

  -- 使用 DBMS_SQL.RETURN_RESULT 将结果集隐式返回给客户端
  DBMS_SQL.RETURN_RESULT(l_cursor);
END get_employee_basic;

 

SYS_REFCURSOR和 REF CURSOR都是 Oracle 数据库中用于处理结果集的游标变量，但它们在定义方式和灵活性上有所不同。为了帮你快速把握核心区别，我们先来看一个对比表格：

特性	REF CURSOR(用户自定义)	SYS_REFCURSOR(Oracle 预定义)
​定义方式​	需要先用 TYPE语句自定义类型	Oracle 系统提供的预定义类型，直接使用
​类型强度​	可定义为强类型​（指定返回结构）或弱类型​（不指定）	属于弱类型，可关联任何查询
​灵活性​	强类型提供编译时类型检查，更安全；弱类型更灵活	灵活性高，但运行时才能发现类型错误
​适用场景​	强类型适合结构明确的固定查询；弱类型适合动态或未知结构的查询	非常适合作为存储过程OUT参数返回动态结果集

🔑 核心概念详解

• ​REF CURSOR​：这是一个通用概念，代表一种游标变量​（cursor variable），它本质上是一个指向查询结果集的指针。它的关键特性是动态性，可以在运行时与不同的查询语句关联。使用前必须先声明类型： 

  sql

  -- 声明一个强类型的REF CURSOR，限定只能打开EMP表结构的查询
  TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; 
  
  -- 声明一个弱类型的REF CURSOR，可以打开任何查询
  TYPE GenericCurTyp IS REF CURSOR;

  强类型在编译时就能检查关联的查询是否匹配返回类型，更安全；弱类型则提供了最大的灵活性。 

• ​SYS_REFCURSOR​：这是 Oracle 系统预定义的一个弱类型的 REF CURSOR​ 类型。你无需自定义类型，可以直接用它来声明变量： 

  sql 

  DECLARE
      my_cursor SYS_REFCURSOR; -- 直接使用预定义类型
  BEGIN
      OPEN my_cursor FOR SELECT * FROM employees;
      -- ... 后续操作
  END;

  它的便利性使其特别适合作为存储过程或函数的OUT参数，用于向调用者（如应用程序）返回一个结果集。 

💡 使用场景与选择建议

了解区别后，如何选择呢？

• ​优先考虑 SYS_REFCURSOR的情况​：

  • 当需要从存储过程返回结果集给应用程序（如Java、Python程序）时，使用 SYS_REFCURSOR作为 OUT参数是最常见和便捷的做法。 

  • 当查询的返回结构不固定或需要在运行时动态决定时，利用其弱类型的灵活性。

• ​优先考虑自定义 REF CURSOR的情况​：

  • 当结果集的结构非常明确且固定时，可以定义强类型的 REF CURSOR。这能在编译阶段就发现类型不匹配的错误，提高代码的健壮性。 

  • 在复杂的PL/SQL程序包内部，需要精确控制游标类型时。

⚠️ 重要注意事项

无论使用哪种游标变量，都需遵循以下操作步骤：​OPEN（打开）​​ -> ​FETCH（提取数据）​​ -> ​CLOSE（关闭）​​

。及时关闭游标以释放资源非常重要。 

弱类型游标（包括 SYS_REFCURSOR）虽然灵活，但因为它直到运行时才检查查询返回的数据结构与接收变量是否匹配，容易引发 ROWTYPE_MISMATCH错误 

。因此，强类型游标在需要类型安全时是更可靠的选择。 

希望这个解释能帮助你清晰地理解 SYS_REFCURSOR和 REF CURSOR。如果你有特定的使用场景，我们可以继续探讨更具体的实现细节。 

• 动态执行sql  

CREATE OR REPLACE PROCEDURE FIT_MES.CXT
AS
    v_tt VARCHAR2(30);
    v_cSql VARCHAR2(300);
    CURSOR cur_t IS SELECT TABLE_NAME FROM USER_TABLES;
BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE BSFC_CC';
    
    OPEN cur_t;
    LOOP
        FETCH cur_t INTO v_tt;
        EXIT WHEN cur_t%NOTFOUND;
        
        v_cSql := 'INSERT INTO BSFC_CC SELECT ''' || v_tt || ''', (SELECT COUNT(*) FROM "' || v_tt || '")';
        EXECUTE IMMEDIATE v_cSql;
    END LOOP;
    CLOSE cur_t;
END CXT;