美食网页模板免费下载,seo网页优化服务,建站公司获客成本,wordpress内核总的目录和进度#xff0c;请参见开始读 Oracle PL/SQL Programming 第6版
具有字符数据类型的变量存储文本并由字符函数操作。
本章重点讨论单字节字符集#xff0c;不涉及Unicode 或多字节字符集#xff0c;不涉及CLOB#xff08;字符大对象#xff09;和 LONG。
Str…总的目录和进度请参见开始读 Oracle PL/SQL Programming 第6版
具有字符数据类型的变量存储文本并由字符函数操作。
本章重点讨论单字节字符集不涉及Unicode 或多字节字符集不涉及CLOB字符大对象和 LONG。
String Datatypes
Oracle 支持四种字符串数据类型
定长变长数据库字符集CHARVARCHAR国家字符集NCHARNVARCHAR2
建议永远不要使用CHAR和NCHAR。
The VARCHAR2 Datatype
variable_name VARCHAR2 (max_length [CHAR | BYTE]);对于PL/SQL如果单位是BYTE则最大为32,767。对于SQL12c前最大400012c后32,767字节但要设置MAX_SQL_STRING_SIZE参数为EXTENDED。
多字节字符集建议以CHAR为单位单位为CHAR时实际的BYTE与字符集有关。
当省略BYTE或CHAR时实际的单位由初始化参数NLS_LENGTH_SEMANTICS 确定 SQL show parameter NLS_LENGTH_SEMANTICSNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics string BYTE
您可以通过查询 NLS_SESSION_PARAMETERS 来确定当前设置。
SQL select * from NLS_SESSION_PARAMETERS;PARAMETER VALUE
__________________________ _______________________________
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE17 rows selected.The CHAR Datatype
CHAR 数据类型指定固定长度的字符串。
若不指定长度则默认为1。
String Subtypes
子类型是为了提供与 ANSI SQL 标准的兼容性。一般用不到。
子类型对应的PL/SQL类型CHAR VARYINGVARCHAR2CHARACTERCHARCHARACTER VARYINGVARCHAR2NATIONAL CHARNCHARNATIONAL CHAR VARYINGNVARCHAR2NATIONAL CHARACTERNCHARNATIONAL CHARACTER VARYINGNVARCHAR2NCHAR VARYINGNVARCHAR2STRINGVARCHAR2VARCHARVARCHAR2
不要使用 VARCHAR 使用VARCHAR2。
Working with Strings
使用字符串主要是使用 Oracle 丰富的内置字符串函数库来操作它们。
Specifying String Constants
You are welcome!
-- 以下使用了转义并等同
Youre welcome!
q!Youre welcome!!
q{Youre welcome!}当您使用 q 前缀时您仍然必须将整个字符串括在单引号内。 紧跟在第一个引号后面的字符将成为字符串的分隔符。 如果起始分隔符是 [、{、 或 () 之一则结束分隔符必须分别是 ]、}、 或 )。
通常字符串常量使用数据库字符集表示。 如果将这样的字符串常量分配给 NCHAR 或 NVARCHAR2 变量则该常量将隐式转换为国家字符集。 数据库会在必要时执行此类转换您无需担心它们。 但是有时您可能需要显式指定要在国家字符集中表示的字符串常量。 您可以使用 n 前缀来执行此操作
nPils vom faß: 1₠u前缀表示国家字符集Unicode
uPils vom fa\00DF: 1\20AC在SQL Plus和SQL Developer中有特殊含义表示替换变量可以SET DEFINE OFF禁止。例如
select 1 2 from dual;
set define off
select 1 2 from dual;Using Nonprintable Characters
使用CHR函数
SQL select 1 || CHR(10) || 2 from dual;1||
---
1
2CHR函数的逆操作为ASCII。
SQL select ASCII(A) from dual;ASCII(A)
----------65SQL select CHR(65) from dual;C
-
A
Concatenating Strings
两种方法||或CONCAT函数。
如CONCAT函数两参数类型不一返回数据类型将是保留最多信息的那个。
两个参数以上可以使用多个CONCAT函数嵌套。
Dealing with Case
比较时不区分大小写
若希望比较时不区分大小写可以强制字符串全部大写或小写LOWER或UPPER函数。
可以使用初始化参数 NLS_COMP 和 NLS_SORT 来使所有字符串比较不区分大小写。 将 NLS_COMP 参数设置为 LINGUISTIC这将告诉数据库使用 NLS_SORT 进行字符串比较。 然后将 NLS_SORT 设置为不区分大小写的设置例如 BINARY_CI 或 XWEST_EUROPEAN_CI。 尾随 _CI 表示不区分大小写。
SQL show parameter nls_compNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_comp string BINARY
SQL SELECT LEAST (JONATHAN,Jonathan,jon) FROM dual;LEAST(J
--------
JONATHANSQL show parameter nls_compNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_comp string BINARY
SQL show parameter nls_sortNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string
SQL SELECT LEAST (JONATHAN,Jonathan,jon) FROM dual;LEAST(J
--------
JONATHANSQL ALTER SESSION SET NLS_COMPLINGUISTIC;Session altered.SQL ALTER SESSION SET NLS_SORTBINARY_CI;Session altered.SQL SELECT LEAST (JONATHAN,Jonathan,jon) FROM dual;LEA
---
jon
NLS_COMP 和 NLS_SORT 设置会影响您执行的所有字符串操作。 这些设置会“保留”直到您更改它们或终止会话为止。
Oracle 还支持不区分重音的排序您可以通过将 _AI而不是 _CI附加到排序名称来获得这种排序。 要查找语言排序名称的完整列表请参阅 Oracle 数据库全球化支持指南。 该指南还详细解释了 NLS_COMP 和 NLS_SORT 的操作。
不区分大小写和索引
如果设置 NLS_COMPLINGUISTIC 和 NLS_SORTBINARY_CI 来启用不区分大小写时以下SQL使用全表扫描而不使用定义在last_name列上的索引
SELECT * FROM employees WHERE last_name lname可以定义基于函数的索引以避免以上问题
CREATE INDEX last_name_ci ON EMPLOYEES (NLSSORT(last_name, NLS_SORTBINARY_CI))将字符串中的每个单词大写
INITCAP函数。
Traditional Searching, Extracting, and Replacing
查找位置用INSTR
提取字符串用SUBSTR
从1或-1开始计数
select substr(abcdefg, 2);SUBSTR
------
bcdefgSQL select substr(abcdefg, -2);SU
--
fg注意INSTR也可以反向查找但返回的位置是正数。
SQL select instr(abcdabcd, bc);INSTR(ABCDABCD,BC)
----------------------2SQL select instr(abcdabcd, bc, 4);INSTR(ABCDABCD,BC,4)
------------------------6SQL select instr(abcdabcd, bc, -1);INSTR(ABCDABCD,BC,-1)
-------------------------6SQL select instr(abcdabcd, bc, -2);INSTR(ABCDABCD,BC,-2)
-------------------------6-- 下面这个不太好理解
-- 是从倒数第3个字符即b开始查找查找的方向是反向但匹配是按正向因此返回6
SQL select instr(abcdabcd, bc, -3);INSTR(ABCDABCD,BC,-3)
-------------------------6替换用REPLACE
select REPLACE(Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec, ,, chr(10)) as mon from dual;Padding
使用RPAD或LPDD。
可以单个字符或字符串填充指定的长度若小于需填充的字符串则会造成截断
SQL select lpad(abcdefg, 4) from dual;LPAD
----
abcdSQL select lpad(abcdefg, 10, .) from dual;LPAD(ABCD
----------
...abcdefgTrimming
LPAD 和 RPAD用于填充而TRIM、LTRIM 和 RTRIM则用于移除。
LTRIM和RTRIM可以移除单个字符或字符集合。
TRIM是为了兼容ISO SQL 标准可从左右或两端移除。但其只能移除单个字符
SQL select trim(. from .abcd.);TRIM
----
abcdSQL select trim(.a from .abcd.);
select trim(.a from .abcd.)*
ERROR at line 1:
ORA-30001: trim set should have only one character
Help: https://docs.oracle.com/error-help/db/ora-30001/Regular Expression Searching, Extracting, and Replacing
正则表达式为您提供了一种模式语言您可以使用它来描述要查找和操作的文本。
Oracle的正则表达式基于POSIX标准又做了扩展功能更强。详见Oracle Regular Expression Support
例如^([a-z A-Z]*,)([a-z A-Z]*){1}$表示以逗号分隔的单词列表如Jan, Feb, Mar, Apr, May。
Detecting a pattern
REGEXP_LIKE返回TRUE和FALSE。
LIKE函数则是简单匹配用_或%。
Locating a pattern
REGEXP_INSTR是INSTR的增强。返回匹配的字符串或NULL。
Extracting text matching a pattern
REGEXP_SUBSTR是SUBSTR的增强。
Counting regular expression matches
REGEXP_COUNT
Replacing text
REGEXP_REPLACE
Grokking greediness
根据Quora的解释 Grok意思是充分理解并整合先前的知识。 这个词出自罗伯特·A·海因莱因的科幻小说《异乡的陌生人》。 在某种程度上“你明白吗” 类似于俚语“明白了吗” 这里所说的贪婪是指正则表达式的每个元素将匹配尽可能多的字符。
例如对于字符串Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec正则表达式.*将匹配为Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov而不仅仅是最开始部分的Jan,。
SQL select REGEXP_SUBSTR(Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec, .*,) from dual;REGEXP_SUBSTR(JAN,FEB,MAR,APR,MAY,JUN,JUL,A
--------------------------------------------
Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,-- ^ 表示 排除
SQL select REGEXP_SUBSTR(Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec, [^,]*,) from dual;REGE
----
Jan,可以在*号后加?号指定为非贪婪算法
SQL select REGEXP_SUBSTR(Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec, .*?,) from dual;REGE
----
Jan,非贪婪量词?实现了尽快地匹配而不是尽可能多地匹配。
Learning more about regular expressions
Oracle Database Application Developer’s Guide—Fundamentals 第4章 或者最新版本看这里。Mastering Oracle SQL, 2nd Edition亚马逊4.5星Mastering Regular Expressions, 3rd Edition亚马逊4.5星Oracle Regular Expressions Pocket Reference亚马逊4星
Working with Empty Strings
注意Oracle 数据库将空字符串视为 NULL特别是对于在使用其他数据库之后才使用 Oracle 的人来说。 这与 ISO SQL 标准相反该标准将空字符串和 NULL 区别开来。
/* File on web: empty_is_null.sql */
DECLAREempty_varchar2 VARCHAR2(10) : ;empty_char CHAR(1) : ;
BEGINIF empty_varchar2 IS NULL THENDBMS_OUTPUT.PUT_LINE(empty_varchar2 is NULL);END IF;IF IS NULL THENDBMS_OUTPUT.PUT_LINE( is NULL);END IF;IF empty_char IS NULL THENDBMS_OUTPUT.PUT_LINE(empty_char is NULL);ELSIF empty_char IS NOT NULL THENDBMS_OUTPUT.PUT_LINE(empty_char is NOT NULL);END IF;
END;输出为
empty_varchar2 is NULLis NULL
empty_char is NOT NULL因此编程时在比较时需特别小心。因为 NULL 永远不会等于或不等于任何其他值。
Mixing CHAR and VARCHAR Values
Database-to-variable conversion
当您从 CHAR 数据库列中 SELECT 或 FETCH 数据到 VARCHAR2 变量中时会保留尾随空格。 如果您从 VARCHAR2 数据库列 SELECT 或 FETCH 到 CHAR 变量PL/SQL 会自动用空格填充该值使其达到最大长度。
Variable-to-database conversion
和上面的规则是一致的。
当您将 CHAR 变量插入或更新到 VARCHAR2 数据库列时SQL 内核在执行更改之前不会修剪尾随空格。 当您将 VARCHAR2 变量插入或更新到 CHAR 数据库列时SQL 内核会自动用空格填充可变长度字符串使其达到创建表时指定的最大固定长度然后存入数据库。
String comparisons
两个字符串变量比较的规则
如果两个都是 CHAR 变量则使用空白填充比较短的那个一致填充到长的那个定义的长度。如果其中一个是可变长度如VARCHAR2则 PL/SQL 执行非空白填充比较。此规则也同样适用于涉及两个以上变量的表达式以及涉及 IN 运算符的表达式
DECLAREc10 CHAR(10): welcome;c15 CHAR(15): welcome;vc15 VARCHAR2(15): welcome;
BEGIN-- Compare two CHARs, so blank-padding is usedIF c10 c15 THENDBMS_OUTPUT.PUT_LINE (first comparison is TRUE);ELSEDBMS_OUTPUT.PUT_LINE (first comparison is FALSE);END IF;-- Compare a CHAR and a VARCHAR2, so nonblank-padding is usedIF c15 vc15 THENDBMS_OUTPUT.PUT_LINE (second comparison is TRUE);ELSEDBMS_OUTPUT.PUT_LINE (second comparison is FALSE);END IF;
END;输出
first comparison is TRUE
second comparison is FALSECharacter functions and CHAR arguments
字符函数是一种采用一个或多个字符值作为参数并返回字符值或数字值的函数。 当字符函数返回字符值时该值始终为 VARCHAR2可变长度类型。
String Function Quick Reference
以下是常用的详见SQL Language Reference
ASCII(char)- 返回 char 第一个字符在数据库字符集中的十进制表示形式。
ASCIISTR(char) - 将任何字符集中的字符串或解析为字符串的表达式作为其参数并返回数据库字符集中该字符串的 ASCII 版本。
CHR(n) 返回与 n 等效的二进制字符作为数据库字符集或国家字符集如果指定 USING NCHAR_CS中的 VARCHAR2 值。
COMPOSE(char) 将字符值 char 作为其参数并返回对其应用 Unicode 规范组合如 Unicode 标准定义 D117 中所述的结果。
CONCAT(char1, char2) CONCAT 返回 char1 与 char2 的拼接。
CONVERT(char1, dest_char_set ) 将字符串从一种字符集转换为另一种字符集。
DECOMPOSE(string) 将字符值字符串作为其第一个参数并返回对其应用其中一个 Unicode 分解的结果。
GREATEST(expr) GREATEST 返回一个或多个表达式列表中最大的一个。
INITCAP(char) INITCAP 返回 char每个单词的第一个字母大写所有其他字母小写。
INSTR(string, substring) 函数在字符串中搜索子字符串。
LEAST(expr) 返回一个或多个表达式列表中的最小值。
LENGTH(char) 返回char的长度。
LOWER(char) 返回 char所有字母均小写。
LPAD(expr1, n, expr2) 返回 expr1用 expr2 中的字符序列向左填充长度为 n 个字符。 此函数对于格式化查询的输出很有用。
LTRIM(char) 从 char 的左端删除 set 中包含的所有字符。
NCHR(number) 返回与国家字符集中的数字等效的二进制字符。
NLS_INITCAP(char) 返回 char每个单词的第一个字母大写所有其他字母小写。 单词由空格或非字母数字字符分隔。
NLS_LOWERNLS_UPPERNLSSORT类似。
REGEXP_COUNT, REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR系列。
REPLACE(char, search_string, replacement_string) REPLACE 返回 char其中每次出现的 search_string 都替换为 replacement_string。
RPAD(expr1, n, expr2) 返回 expr1用 expr2 右填充到长度 n 个字符根据需要复制多次。
RTRIM(char, set) 从 char 的右端删除集中出现的所有字符。
SOUNDEX(char) 返回包含 char 的语音表示形式的字符串。 此功能可让您比较拼写不同但英语发音相似的单词。
SUBSTR(char, position, substring_length) 函数返回 char 的一部分从字符position开始substring_length 个字符长。
TO_CHAR() 字符将 NCHAR、NVARCHAR2、CLOB 或 NCLOB 数据转换为数据库字符集。 返回的值始终是 VARCHAR2。
TO_MULTI_BYTE(char) 返回 char 并将其所有单字节字符转换为相应的多字节字符。 char 可以是 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型。 返回值的数据类型与 char 相同。
SQL SELECT dump(TO_MULTI_BYTE( A)) FROM DUAL;DUMP(TO_MULTI_BYTE(A))
------------------------
Typ1 Len3: 239,188,161SQL SELECT dump(A) FROM DUAL;DUMP(A)
----------------
Typ96 Len1: 65TO_NCHAR() 将数据库字符集中的数据转换为国家字符集中的等效表示形式。
TO_SINGLE_BYTE(char) 返回 char 其中所有多字节字符都转换为相应的单字节字符。
TRANSLATE(expr, from_string, to_string) 返回 expr并将 from_string 中每个字符的所有出现位置替换为 to_string 中对应的字符。
TRIM不说了。
UNISTR(string) 将文本文字或解析为字符数据的表达式作为其参数并以国家字符集返回它。
UPPER(char) UPPER 返回 char所有字母均大写。
