相当于ASCIISTR(N'str')的Oracle PLSQL [英] Oracle PLSQL equivalent of ASCIISTR(N'str')

问题描述

我的数据库有NLS_LANGUAGE:AMERICAN / NLS_CHARACTERSET:WE8ISO8859P15 / NLS_NCHAR_CHARACTERSET:AL16UTF16;在我的 Windows>属性>高级系统设置>高级>环境变量中将NLS_LANG设置为AMERICAN_AMERICA.WE8MSWIN1252 -希望它适用于我的PLSQL Dev.

My database has NLS_LANGUAGE:AMERICAN / NLS_CHARACTERSET:WE8ISO8859P15 / NLS_NCHAR_CHARACTERSET:AL16UTF16; NLS_LANG is set to AMERICAN_AMERICA.WE8MSWIN1252 in my Windows> properties> advanced system settings> advanced> environment variables - hope it applies to my PLSQL Dev.

我使用ASCIISTR为这样的奇异字符获取unicode编码的值:

I use ASCIISTR to get a unicode encoded value for exotic chars like this:

SELECT ASCIISTR(N'κόσμε') FROM DUAL;

结果

ASCIISTR(UNISTR('\03BA\1F79\03...
---------------------------------
\03BA\1F79\03C3\03BC\03B5

看起来'N'表示该字符串是unicode，因为如果我不指定它，则会得到错误的编码.

It looks like the 'N' means the string is unicode, because if I don't specify it I get it wrong encoded.

SELECT ASCIISTR('κόσμε') FROM DUAL;

结果

ASCIISTR('??SµE')
--------------------
??s\00B5e

"N"代表什么?如何在PLSQL中调用它?

我打算在pl/sql变量上使用它来编码这样的外来字符:

What does this 'N' stands for? How do I invoke it in PLSQL?

I intend to use it on a pl/sql variable to encode exotice characters like this:

DECLARE 
  l_in VARCHAR2(2000);
  l_ec VARCHAR2(2000);
  l_dc VARCHAR2(2000);
BEGIN 
  l_in := 'κόσμε';
  execute immediate 'select ASCIISTR(N'''||l_in||''') from dual'  into l_ec;
  DBMS_OUTPUT.PUT_LINE(l_ec);

  select unistr(l_ec) into l_dc from dual;
  DBMS_OUTPUT.PUT_LINE (l_dc);
END;

但是我明白了

??s\00B5e
??sµe

就像我在上面的第二种情况下一样，没有'N'

As if I were in the second case above, without the 'N'

推荐答案

N'κόσμε'(或多或少)等同于CAST('κόσμε' AS NVARCHAR2(..))

N'κόσμε' is (more or less) equivalent to CAST('κόσμε' AS NVARCHAR2(..))

使用N'κόσμε'时，您说将字符串作为NVARCHAR处理".如果仅编写'κόσμε'，则该字符串将被视为VARCHAR.但是，您的NLS_CHARACTERSET是WE8ISO8859P15，它不支持希腊字符.因此，您将?作为占位符.

With N'κόσμε' you say "treat the string as NVARCHAR". If you write just 'κόσμε' then the string is treated as VARCHAR. However, your NLS_CHARACTERSET is WE8ISO8859P15 which does not support Greek characters. Thus you get ? as placeholder.

您没有告诉我们您的NLS_NCHARACTERSET设置，很可能这支持Unicode.

You didn't tell us your NLS_NCHARACTERSET setting, most likely this supports Unicode.

顺便说一句，您不必select ... from dual，只需编写类似

btw, you don't have to select ... from dual, simply write like

l_ec := ASCIISTR('κόσμε');

在PL/SQL中.

您在客户端的本地 NLS_LANG值是多少?它很可能与您的SQL * Plus的字符编码不匹配.请参阅以下答案以了解更多详细信息: OdbcConnection返回的汉字为?"

What is your local NLS_LANG value, i.e. at your client side? Most likely it does not match the character encoding of your SQL*Plus. See this answer for more details: OdbcConnection returning Chinese Characters as "?"

这篇关于相当于ASCIISTR(N'str')的Oracle PLSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！