Oracle中N'String'和U'String'文字之间的区别 [英] Difference between N'String' vs U'String' literals in Oracle

问题描述

这些查询之间的含义和区别是什么?

What is the meaning and difference between these queries?

SELECT U'String' FROM dual;

和

SELECT N'String' FROM dual;

推荐答案

在此答案中，我将尝试提供来自官方资源的信息

N''用于将字符串转换为NCHAR或NVARCHAR2数据类型

N'' is used to convert a string to NCHAR or NVARCHAR2 datatype

根据此Oracle文档 Oracle-文字

According to this Oracle documentation Oracle - Literals

文本文字的语法如下:

The syntax of text literals is as follows:

其中N或n使用国家字符集(NCHAR或NVARCHAR2数据)指定文字.

where N or n specifies the literal using the national character set (NCHAR or NVARCHAR2 data).

也在第二篇文章 Oracle-数据类型

Also in this second article Oracle - Datatypes

N'String'用于将字符串转换为NCHAR数据类型

The N'String' is used to convert a string to NCHAR datatype

来自上面列出的文章:

下面的示例将pm.product_descriptions表的translated_description列与国家字符集字符串进行比较:

The following example compares the translated_description column of the pm.product_descriptions table with a national character set string:

SELECT translated_description FROM product_descriptions
  WHERE translated_name = N'LCD Monitor 11/PM';

---

(2)U字面量

U''用于处理Oracle调用接口(OCI)中的SQL NCHAR字符串文字

---

(2) The U'' Literal

U'' is used to handle the SQL NCHAR String Literals in Oracle Call Interface (OCI)

基于此Oracle文档使用Unicode编程

Based on this Oracle documentation Programming with Unicode

Oracle调用接口(OCI)是其余客户端数据库访问产品使用的最低级别的API.它为C/C ++程序提供了一种灵活的方式来访问存储在SQL CHAR和NCHAR数据类型中的Unicode数据.使用OCI，可以以编程方式为要插入或检索的数据指定字符集(UTF-8，UTF-16等).它通过Oracle Net访问数据库.

The Oracle Call Interface (OCI) is the lowest level API that the rest of the client-side database access products use. It provides a flexible way for C/C++ programs to access Unicode data stored in SQL CHAR and NCHAR datatypes. Using OCI, you can programmatically specify the character set (UTF-8, UTF-16, and others) for the data to be inserted or retrieved. It accesses the database through Oracle Net.

OCI是用于访问数据库的最低级别的API，因此它可以提供最佳的性能.

OCI is the lowest-level API for accessing a database, so it offers the best possible performance.

在OCI中处理SQL NCHAR字符串文字

您可以通过将环境变量ORA_NCHAR_LITERAL_REPLACE设置为TRUE来将其打开.您还可以通过使用OCIEnvCreate()和OCIEnvNlsCreate()中的OCI_NCHAR_LITERAL_REPLACE_ON和OCI_NCHAR_LITERAL_REPLACE_OFF模式以编程方式实现此行为.因此，例如，OCIEnvCreate(OCI_NCHAR_LITERAL_REPLACE_ON)打开NCHAR文字替换，而OCIEnvCreate(OCI_NCHAR_LITERAL_REPLACE_OFF)关闭它.

Handling SQL NCHAR String Literals in OCI

You can switch it on by setting the environment variable ORA_NCHAR_LITERAL_REPLACE to TRUE. You can also achieve this behavior programmatically by using the OCI_NCHAR_LITERAL_REPLACE_ON and OCI_NCHAR_LITERAL_REPLACE_OFF modes in OCIEnvCreate() and OCIEnvNlsCreate(). So, for example, OCIEnvCreate(OCI_NCHAR_LITERAL_REPLACE_ON) turns on NCHAR literal replacement, while OCIEnvCreate(OCI_NCHAR_LITERAL_REPLACE_OFF) turns it off.

[...]请注意，当打开NCHAR文字替换时，OCIStmtPrepare和OCIStmtPrepare2 将使用SQL文本中的U'文字转换N'文字并将其存储在SQL文本中.语句句柄中生成的SQL文本.因此，如果应用程序使用OCI_ATTR_STATEMENT从OCI语句句柄中检索SQL文本，则 SQL文本将返回U'而不是原始文本中指定的N' . /p>

[...] Note that, when the NCHAR literal replacement is turned on, OCIStmtPrepare and OCIStmtPrepare2 will transform N' literals with U' literals in the SQL text and store the resulting SQL text in the statement handle. Thus, if the application uses OCI_ATTR_STATEMENT to retrieve the SQL text from the OCI statement handle, the SQL text will return U' instead of N' as specified in the original text.

---

(3)回答您的问题

从数据类型的角度看，提供的两个查询之间没有区别

---

(3) Answer for your question

From datatypes perspective, there is not difference between both queries provided

这篇关于Oracle中N'String'和U'String'文字之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！