标量函数转义功能 - ODBC 驱动程序 V2.0.8

标量函数转义功能

更新时间：2026-04-09 15:10:59

OceanBase Connector/ODBC 在 Oracle 模式下，支持对 IFNULL、SUBSTRING、LEFT 三个标量函数的转义功能。

IFNULL 函数

IFNULL 函数的语法如下：

{fn IFNULL(exp1, exp2) }

IFNULL 函数先判断 exp1 是否为 NULL，如果不为NULL 则返回 exp1, 否则返回 exp2。

如下示例中 exp1 为 NULL，则结果返回 exp2，即 “aaa”。

SELECT {fn IFNULL(NULL, 'aaa')} FROM DUAL;

对于如下示例，如果 table.a 为 NULL，则结果为 b；如果 table.a 不为 NULL，结果为 a。

SELECT {fn IFNULL(a, b) } FROM TABLE;

LEFT 函数

LEFT 函数的语法如下：

{fn LEFT (string_exp, n)}

LEFT 函数从 string_exp 中从头开始截取 n 个字符长度的字符串。

示例如下：

SELECT {fn LEFT('helloword', 5)} FROM DUAL; -- 结果为 hello

SUBSTRING 函数

SUBSTRING 函数的语法如下：

{fn SUBSTRING (string_exp, start, length**) }

SUBSTRING 函数从 string_exp 截取字符串，起始位置为 start, 长度为 length。其中 start=0 或者 start=1 都表示从的 string_exp 起始字符开始。其中 length 是可选参数，如果没有指定 length，那么长度就直到 string_exp 结尾。

示例如下：

SELECT {fn SUBSTRING('helloword', 5, 4)} FROM DUAL;  -- 结果为 owor
SELECT {fn SUBSTRING('helloword', 5)} FROM DUAL;     -- 结果为 oword
SELECT {fn SUBSTRING('helloword', 1)} FROM DUAL;     -- 结果为 helloword
SELECT {fn SUBSTRING('helloword', 0)} FROM DUAL;     -- 结果为 helloword

综合示例

ODBC_TEST(test_ifnull)
{
  SQLRETURN ret = 0;
  SQLSMALLINT cnt;
  char buf[1024] = { 0 };
  char buf1[1024] = { 0 };
  SQLCHAR * drop_sql = (SQLCHAR *)"DROP TABLE type_number";
  SQLExecDirect(Stmt, (SQLCHAR*)drop_sql, (SQLINTEGER)strlen((char*)drop_sql));
  OK_SIMPLE_STMT(Stmt, "CREATE TABLE type_number(a varchar2(100), b varchar2(100))");
  OK_SIMPLE_STMT(Stmt, "insert into type_number values(NULL, 'my name is a')");

  OK_SIMPLE_STMT(Stmt, "select {FN IFNULL(NULL, 100)} from dual");
  CHECK_STMT_RC(Stmt, ret = SQLNumResultCols(Stmt, &cnt));
  CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 1, SQL_C_CHAR, buf, 1024, NULL));
  CHECK_STMT_RC(Stmt, ret = SQLFetch(Stmt));
  CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
  printf("%s\n", buf);

  OK_SIMPLE_STMT(Stmt, "select {FN LEFT('helloword', 1)} from dual");
  CHECK_STMT_RC(Stmt, ret = SQLNumResultCols(Stmt, &cnt));
  CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 1, SQL_C_CHAR, buf, 1024, NULL));
  CHECK_STMT_RC(Stmt, ret = SQLFetch(Stmt));
  CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
  printf("%s\n", buf);

  OK_SIMPLE_STMT(Stmt, "select {FN SUBSTRING ('helloword', 5, 4)} from dual");
  CHECK_STMT_RC(Stmt, ret = SQLNumResultCols(Stmt, &cnt));
  CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 1, SQL_C_CHAR, buf, 1024, NULL));
  CHECK_STMT_RC(Stmt, ret = SQLFetch(Stmt));
  CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
  printf("%s\n", buf);

  OK_SIMPLE_STMT(Stmt, "select {FN SUBSTRING ('helloword', 5)} from dual");
  CHECK_STMT_RC(Stmt, ret = SQLNumResultCols(Stmt, &cnt));
  CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 1, SQL_C_CHAR, buf, 1024, NULL));
  CHECK_STMT_RC(Stmt, ret = SQLFetch(Stmt));
  CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
  printf("%s\n", buf);

  OK_SIMPLE_STMT(Stmt, "select {FN LEFT(b, 5)},b from type_number");
  CHECK_STMT_RC(Stmt, ret = SQLNumResultCols(Stmt, &cnt));
  CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 1, SQL_C_CHAR, buf, 1024, NULL));
  CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 2, SQL_C_CHAR, buf1, 1024, NULL));
  CHECK_STMT_RC(Stmt, ret = SQLFetch(Stmt));
  CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
  printf("%s,%s\n", buf, buf1);

  OK_SIMPLE_STMT(Stmt, "select {FN IFNULL(a, 5)},b from type_number");
  CHECK_STMT_RC(Stmt, ret = SQLNumResultCols(Stmt, &cnt));
  CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 1, SQL_C_CHAR, buf, 1024, NULL));
  CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 2, SQL_C_CHAR, buf1, 1024, NULL));
  CHECK_STMT_RC(Stmt, ret = SQLFetch(Stmt));
  CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
  printf("%s,%s\n", buf, buf1);

  return OK;
}