TypeScript中oracledb的execute()方法为何返回unknown类型？

问题描述 #

调用存储过程后访问返回的游标p_activerecords时，触发TypeScript错误：

'dbresult.outBinds' is of type 'unknown'.ts(18046)

根源是dbconn.execute()的返回类型被识别为unknown。虽然可以通过声明dbresult: any临时规避，但希望通过正确的类型处理让execute返回OracleDB.Result类型，避免使用any。

用户原始代码示例：

import { dashboardRecord, dashboardRecordsRequest, dashboardRecordsResponse } from "datamodel/dashboard/dashboard";
import { logger } from "logger/logger";
import OracleDB from "oracledb";

export async function getActiveRequestRecords(dashboardRecordsRequest: dashboardRecordsRequest): Promise<dashboardRecordsResponse> {
    let dashboardRecordsResponse: dashboardRecordsResponse;
    dashboardRecordsResponse = {
        message: "",
        records: [],
        request: dashboardRecordsRequest,
        totalRecords: 0,
    }
    const sql: string = `BEGIN get_dashboard_active_request_records(:p_entityname, :p_sortby, :p_sortorder, :p_startindex, :p_pagesize, :p_totalrecords, :p_activerecords); END;`;
    const p_entityname: OracleDB.BindParameter = { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.entityName, type: OracleDB.STRING };
    const p_sortby: OracleDB.BindParameter = { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.sortBy, type: OracleDB.STRING };
    const p_sortOrder: OracleDB.BindParameter = { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.sortOrder, type: OracleDB.STRING };
    const p_startindex: OracleDB.BindParameter = { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.startIndex, type: OracleDB.NUMBER };
    const p_pagesize: OracleDB.BindParameter = { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.pageSize, type: OracleDB.NUMBER };
    const p_totalrecords: OracleDB.BindParameter = { dir: OracleDB.BIND_OUT, type: OracleDB.NUMBER };
    const p_activerecords: OracleDB.BindParameter = { dir: OracleDB.BIND_OUT, type: OracleDB.CURSOR };
    const bindVar: OracleDB.BindParameter[] = [
        p_entityname,
        p_sortby,
        p_sortOrder,
        p_startindex,
        p_pagesize,
        p_totalrecords,
        p_activerecords
    ];
    const options: OracleDB.ExecuteOptions = { outFormat: OracleDB.OUT_FORMAT_OBJECT };

    try {
        const dbconn = await OracleDB.getConnection('requestpool');
        const dbresult = await dbconn.execute(sql, bindVar, options);
        const activerecords = dbresult.outBinds.p_activerecords;
        const rows = await activerecords?.getRows();
        dashboardRecordsResponse.totalRecords = dbresult.outBinds.p_totalrecords;

        let dashboardRecord: dashboardRecord;
        for (const row of rows) {
            dashboardRecord = {
                approvedDate: row.ACTIONDATE,
                creationDate: new Date(Date.now()),
                employeeGEN: '',
                employeeName: '',
                lastActionDate: row.ACTIONDATE,
                pendingWith: row.PENDINGWITH,
                processType: row.PROCESSTYPE,
                requestNumber: row.REQUESTNUMBER,
                requestorName: row.REQUESTOR,
                requestState: row.REQUESTSTATUS,
                submissionDate: new Date(Date.now()),
                withdrawnDate: new Date(Date.now()),
            }
            dashboardRecordsResponse.records?.push(dashboardRecord);
        }
        await dbconn.close();
    } catch (err) {
        dashboardRecordsResponse.message = 'Error while retrieving Active Request records.';
        logger.error(err);
    }
    console.log(dashboardRecordsResponse);
    return dashboardRecordsResponse;
}

解决方案 #

1. 显式指定execute方法的泛型参数 #

oracledb的execute方法支持泛型参数，通过指定泛型可以明确返回的Result类型，特别是outBinds的结构：

首先定义outBinds的类型接口：

// 定义存储过程输出参数的类型结构
interface ActiveRequestOutBinds {
  p_totalrecords: number;
  p_activerecords: OracleDB.RefCursor;
}

然后在调用execute时传入泛型参数：

// 第一个泛型参数是行数据类型（不需要可设为unknown），第二个是outBinds类型
const dbresult = await dbconn.execute<unknown, ActiveRequestOutBinds>(sql, bindVar, options);

这样TypeScript就能正确识别dbresult.outBinds的类型，不再是unknown，访问p_activerecords和p_totalrecords时也不会报错。

2. 优化绑定变量定义（可选） #

将绑定变量从数组改为对象形式，参数名与存储过程的参数一一对应，不仅更直观，还能让TypeScript更好地关联参数名和类型：

const bindVars = {
  p_entityname: { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.entityName, type: OracleDB.STRING },
  p_sortby: { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.sortBy, type: OracleDB.STRING },
  p_sortorder: { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.sortOrder, type: OracleDB.STRING },
  p_startindex: { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.startIndex, type: OracleDB.NUMBER },
  p_pagesize: { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.pageSize, type: OracleDB.NUMBER },
  p_totalrecords: { dir: OracleDB.BIND_OUT, type: OracleDB.NUMBER },
  p_activerecords: { dir: OracleDB.BIND_OUT, type: OracleDB.CURSOR }
};

3. 修改后的完整代码 #

import { dashboardRecord, dashboardRecordsRequest, dashboardRecordsResponse } from "datamodel/dashboard/dashboard";
import { logger } from "logger/logger";
import OracleDB from "oracledb";

// 定义存储过程输出参数的类型结构
interface ActiveRequestOutBinds {
  p_totalrecords: number;
  p_activerecords: OracleDB.RefCursor;
}

export async function getActiveRequestRecords(dashboardRecordsRequest: dashboardRecordsRequest): Promise<dashboardRecordsResponse> {
    let dashboardRecordsResponse: dashboardRecordsResponse = {
        message: "",
        records: [],
        request: dashboardRecordsRequest,
        totalRecords: 0,
    };
    const sql: string = `BEGIN get_dashboard_active_request_records(:p_entityname, :p_sortby, :p_sortorder, :p_startindex, :p_pagesize, :p_totalrecords, :p_activerecords); END;`;
    
    const bindVars = {
        p_entityname: { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.entityName, type: OracleDB.STRING },
        p_sortby: { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.sortBy, type: OracleDB.STRING },
        p_sortorder: { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.sortOrder, type: OracleDB.STRING },
        p_startindex: { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.startIndex, type: OracleDB.NUMBER },
        p_pagesize: { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.pageSize, type: OracleDB.NUMBER },
        p_totalrecords: { dir: OracleDB.BIND_OUT, type: OracleDB.NUMBER },
        p_activerecords: { dir: OracleDB.BIND_OUT, type: OracleDB.CURSOR }
    };
    
    const options: OracleDB.ExecuteOptions = { outFormat: OracleDB.OUT_FORMAT_OBJECT };

    try {
        const dbconn = await OracleDB.getConnection('requestpool');
        // 指定泛型参数，明确返回类型
        const dbresult = await dbconn.execute<unknown, ActiveRequestOutBinds>(sql, bindVars, options);
        const activerecords = dbresult.outBinds.p_activerecords;
        const rows = await activerecords?.getRows();
        dashboardRecordsResponse.totalRecords = dbresult.outBinds.p_totalrecords;

        if (rows) {
            for (const row of rows) {
                const dashboardRecord: dashboardRecord = {
                    approvedDate: row.ACTIONDATE,
                    creationDate: new Date(Date.now()),
                    employeeGEN: '',
                    employeeName: '',
                    lastActionDate: row.ACTIONDATE,
                    pendingWith: row.PENDINGWITH,
                    processType: row.PROCESSTYPE,
                    requestNumber: row.REQUESTNUMBER,
                    requestorName: row.REQUESTOR,
                    requestState: row.REQUESTSTATUS,
                    submissionDate: new Date(Date.now()),
                    withdrawnDate: new Date(Date.now()),
                };
                dashboardRecordsResponse.records.push(dashboardRecord);
            }
        }
        await dbconn.close();
    } catch (err) {
        dashboardRecordsResponse.message = 'Error while retrieving Active Request records.';
        logger.error(err);
    }
    console.log(dashboardRecordsResponse);
    return dashboardRecordsResponse;
}

内容的提问来源于stack exchange，提问作者Ranjeet