PL/SQL数据类型——字符串
什么是字符串
string/character data：a sequence of selected symbols from a particular set of characters.（从特定字符集中选中的一系列字符）
在PL/SQL中有三种类型：
- Fixed-length strings（定长字符串）：在右边用空格填充，直到达到在声明时指定的长度的字符。
- Variable-length strings（变长字符串）：有指定的最大长度（必须小于21767），但不会被填充。
- Character large objects (CLOBs)（字符大对象）：是不定长度的字符串，可以达到128TB。
字符串可以是文字或变量。字符串文字被包在单引号中：
'This is a string literal'
要在字符串中嵌入单引号，使用两个挨着的单引号''
'This isn''t a date'
还可以用“q”来转义，比''更易读。
q'[This isn't a date]'
声明字符变量
ORACLE数据库提供的字符数据类型有：CHAR， NCHAR,，VARCHAR2，NVARCHAR2， CLOB和NCLOB。以N开头的是“national character set”，可以存储Unicode字符数据。
变长字符串声明：在声明时必须指定字符串的最大长度，否则会抛出编译异常。
DECLARE
l_company_name VARCHAR2(100);
定长字符串声明：使用CHAR数据类型，可以不用指定最大长度。如果没有指定的话，ORACLE数据库自动将此变量的最大长度设为1。如果声明时指定的最大长度大于1，数据库就会自动在你为这个变量赋的值的右边填充空格，直到达到指定的最大长度。
DECLARE
l_yes_or_no1 CHAR(1) := 'Y';
l_yes_or_no2 CHAR := 'Y';
※如果不指定最大长度，值的长度又大于1的话，可以通过编译，运行时会报错。
P_TEST CHAR := 'YY';
ORA-06502: PL/SQL: 数値または値のエラー: 文字列バッファが小さすぎます。が発生しました
ORA-06512: "XXX", 行3
ORA-06512: 行8
字符大对象声明：使用CLOB数据类型，不需要指定最大长度。它的最大长度基于数据库块大小，由ORACLE数据库自动决定。
DECLARE
l_lots_of_text CLOB;
在程序中使用哪种数据类型
- 如果大于32767个字符，用CLON(NCLOB)。
- 如果这个字符总是长度固定的（性别、邮编），用CHAR(NCHAR)。
- 其他的，用VARCHAR2(NVARCHAR2)
注意，
l_variable VARCHAR2 (10) := 'Logic';和l_fixed CHAR (10) := 'Logic';是不相等的。
字符内建方法
连接多个字符
- CONCAT方法：连接两个参数，不常用。
- || 操作符：可连接多个参数，常用。
DECLARE
l_first VARCHAR2 ( 10 ) := 'Steven';
l_middle VARCHAR2 ( 5 ) := 'Eric';
l_last VARCHAR2 ( 20 ) := 'Feuerstein';
BEGIN
/* Use the CONCAT function */
DBMS_OUTPUT.put_line ( CONCAT ( 'Steven' ,'Feuerstein' ) ) ;
/* Use the || operator */
DBMS_OUTPUT.put_line ( l_first || ' ' || l_middle || ' ' || l_last ) ;
END;
/
-- output
StevenFeuerstein
Steven Eric Feuerstein
字符大小写切换
- UPPER：所有字符转换成大写
- LOWER：所有字符转换成小写
- INITCAP：所有单词首字母大写（字符由空格或非字母数字间隔）
DECLARE
test_string VARCHAR(20) := 'juSt tEst';
BEGIN
DBMS_OUTPUT.put_line (UPPER (test_string));
DBMS_OUTPUT.put_line (LOWER (test_string));
DBMS_OUTPUT.put_line (INITCAP (test_string));
END;
-- output
JUST TEST
just test
Just Test
截取字符串
SUBSTR(str, start [, num])有三个参数，目标字符串，要截取的字符串的开始位置，以及截取的字符串数（可选）。
- start要小于目标字符串的长度，可以为负数。
- start为-1，则表示从目标字符串的末尾开始，逆向截取字符。
- num必须大于0，可大于目标字符串的长度。
DECLARE
l_company_name VARCHAR2 ( 6 ) := 'Oracle';
BEGIN
/* start可为负数 */
DBMS_OUTPUT.put_line ( SUBSTR ( l_company_name , - 2 ,1 ) ) ;
/* num可大于字符串长度 */
DBMS_OUTPUT.put_line ( SUBSTR ( l_company_name ,2 ,11 ) ) ;
/* num可省略 */
DBMS_OUTPUT.put_line ( SUBSTR ( l_company_name ,3 ) ) ;
/* num要大于0 */
DBMS_OUTPUT.put_line ( SUBSTR ( l_company_name ,2 ,0 ) ) ;
END;
/
-- output
l
racle
acle
查找特定字符串
INSTR(strA, strB [, start, Nth])strA是目标字符串，strB是要在strA中查找的字符串，start是开始查找的位置，Nth是查找到的第几个。函数返回strB第Nth次出现时，在strA中的位置。如果没有查找到，就返回0。
- start，Nth可选
- 查找时大小写区分
- start为0的话，不能查找，返回0
- 若查询不到，返回0
- start为负数时，表示从strA的右边第start位开始，从右到左查询
- Nth小于0的话，会抛出异常
BEGIN
/* 寻找'E' */
DBMS_OUTPUT.put_line ( INSTR ( 'oracle oraclE' ,'E' ) ) ;
/* 从第7位开始，检索到的第一个'e'的位置 */
DBMS_OUTPUT.put_line ( INSTR ( 'oracle oraclE oracle' ,'e' ,7 ) ) ;
/* 从右边第1位开始，从左往右检索到的第一个e的位置 */
DBMS_OUTPUT.put_line ( INSTR ( 'oracle oraclE oracle' ,'e' , - 1 ) ) ;
/* 从第2位开始，检索到的第二个'l'的位置 */
DBMS_OUTPUT.put_line ( INSTR ( 'oracle oraclE' ,'l' ,2 ,2 ) ) ;
END;
/
-- output
13
20
20
12
用空格（或其他字符）填充字符串
LPAD/RPAD(str, length, cha)在字符串str的左侧/右侧填充cha，使字符串的位数达到length。
DECLARE
l_first VARCHAR2 ( 10 ) := 'Steven';
l_last VARCHAR2 ( 20 ) := 'Feuerstein';
l_phone VARCHAR2 ( 20 ) := '773-426-9093';
BEGIN
DBMS_OUTPUT.put_line ( 'Header' ) ;
/* 如果length小于str的length，就会截去多余的字符 */
DBMS_OUTPUT.put_line ( LPAD ( 'Sub-header' ,5 ,'.' ) ) ;
/* 在str右边填充'123'，直到达到20位 */
DBMS_OUTPUT.put_line ( RPAD ( 'abc' ,20 ,'123' ) ) ;
/* Display headers and then values to fit within the columns. */
DBMS_OUTPUT.put_line (
/*1234567890x12345678901234567890x*/
'First Name Last Name Phone' ) ;
DBMS_OUTPUT.put_line ( RPAD ( l_first ,10 ) || ' ' || RPAD ( l_last ,20 ) || ' ' || l_phone ) ;
END;
/
置换
- REPLACE：用一组字符替换另一组字符
- TRANSLATE： 翻译或替换单个字符
替换单个字符时，REPLACE和TRANSLATE的作用是一样的。替换多字符时情况就不同了。
DECLARE
l_name VARCHAR2 ( 50 ) := 'Steven Feuerstein';
BEGIN
/* 将'abc'替换成'123' */
DBMS_OUTPUT.put_line ( REPLACE ( 'abc-a-b-c-abc' ,'abc' ,'123' ) ) ;
/* 'a'->'1' 'b'->'2' 'c'->'' */
DBMS_OUTPUT.put_line ( TRANSLATE ( 'abc-a-b-c-abc' ,'abc' ,'12' ) ) ;
END;
/
--output
123-a-b-c-123
12-1-2--12
删除字符
LTRIM/RTRIM(str [, char])从str左边或右边删除char，直到遇到除char以外的字符。若没有设定char参数，就删去左侧或右侧的空格。
DECLARE
a VARCHAR2 ( 40 ) := 'This sentence has too many periods....';
b VARCHAR2 ( 40 ) := 'The number 1';
BEGIN
DBMS_OUTPUT.put_line ( RTRIM ( a ,'.' ) ) ;
DBMS_OUTPUT.put_line ( LTRIM ( b ,'ABCDEFGHIJKLMNOPQRSTUVWXYZ ' || 'abcdefghijklmnopqrstuvwxyz' ) ) ;
END;
-- output
This sentence has too many periods
1
TRIM：删去两边的字符。
- BOTH '.' FROM x，去掉两边的'.'，只能用于单字符
- RTRIM(LTRIM(x,',.;'),',.;')，去掉两边的多字符
DECLARE
x VARCHAR2 ( 30 ) := '.....Hi there!.....';
BEGIN
DBMS_OUTPUT.put_line ( TRIM ( LEADING '.' FROM x ) ) ;
DBMS_OUTPUT.put_line ( TRIM ( TRAILING '.' FROM x ) ) ;
DBMS_OUTPUT.put_line ( TRIM ( BOTH '.' FROM x ) ) ;
--The default is to trim
--from both sides
DBMS_OUTPUT.put_line ( TRIM ( '.' FROM x ) ) ;
--The default trim character
--is the space:
DBMS_OUTPUT.put_line ( TRIM ( x ) ) ;
END;
-- output
Hi there!.....
.....Hi there!
Hi there!
Hi there!
.....Hi there!.....
其他
字符串长度溢出
一个VARCHAR2类型的变量，如果给它赋值的长度大于其声明的最大长度，会抛出VALUE_ERROR异常(ORA-06502: PL/SQL: numeric or value)。
如果使用insert或update操作，值的长度大于字段的规定长度，会抛出不同的异常（ORA-12899: value too large for column）。
不同的最大长度
VARCHAR2在PL/SQL中的最大长度是32767bytes，在SQL中是4000。CHAR在PL/SQL中也是32767bytes，在SQL中是2000.CLOB在PL/SQL中的最大长度是128TB，在SQL中是(4 GB - 1) * DB_BLOCK_SIZE。
在存储VACHAR2类型字段到表中时，为了避免ORA-12899错误，有两种做法
- 使用SUBSTR使值的长度在4000以内，但是会失去部分字符。
- 把字段的数据类型换成CLOB。
