Oracle PL/SQL之VARCHAR2 QUALIFIER
Oracle PL/SQL之VARCHAR2 QUALIFIER
在声明VARCHAR2变量时,我们可以指定其最大长度单位为CHAR:v1 VARCHAR2(2 CHAR); 或BYTE:v2 VARCHAR2(2 BYTE); ,
大多数默认什么都不指定:v3 VARCHAR2(2); 。 那么这三者有什么区别呢? 请看例子:
- DECLARE
- v1 VARCHAR2(2 CHAR);
- v2 VARCHAR2(2 BYTE);
- v3 VARCHAR2(2);
- BEGIN
- <<v1_test>>
- BEGIN
- v1 := '测试';
- dbms_output.put_line('v1_test successed, v1=' || v1);
- EXCEPTION
- WHEN OTHERS THEN
- dbms_output.put_line('v1_test failed, =>' ||
- dbms_utility.format_error_stack);
- END;
- <<v2_test>>
- BEGIN
- v2 := '测试';
- dbms_output.put_line('v2_test successed, v2=' || v2);
- EXCEPTION
- WHEN OTHERS THEN
- dbms_output.put_line('v2_test failed, =>' ||
- dbms_utility.format_error_stack);
- END;
- <<v3_test>>
- DECLARE
- l_default_qualifier nls_session_parameters.VALUE%TYPE;
- BEGIN
- --we can get the default qualifier by querying nls_session_parameters.
- SELECT VALUE
- INTO l_default_qualifier
- FROM nls_session_parameters
- WHERE parameter = 'NLS_LENGTH_SEMANTICS';
- dbms_output.put_line('v3_test, l_default_qualifier=' ||
- l_default_qualifier);
- v3 := '测试';
- dbms_output.put_line('v3_test successed, v3=' || v3);
- EXCEPTION
- WHEN OTHERS THEN
- dbms_output.put_line('v3_test failed, =>' ||
- dbms_utility.format_error_stack);
- END;
- END;
输出:
- v1_test successed, v1=测试
- v2_test failed, =>ORA-06502: PL/SQL: numeric or value error: character string buffer too small
- v3_test, l_default_qualifier=BYTE
- v3_test failed, =>ORA-06502: PL/SQL: numeric or value error: character string buffer too small
评论暂时关闭