在 Oracle 中创建视图时更改列精度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10557030/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Changing the column precision while creating a view in Oracle
提问by roymustang86
CREATE OR REPLACE VIEW USER_AUD_VIEW ("AUDIT_ID", "USER_ID", "TABLE_NAME_TXT", "COLUMN_NAME_TXT", "OLD_VALUE_TXT", "NEW_VALUE_TXT", "AUDIT_LAST_UPDATED_DT", "AUDIT_UPDATED_USER_ID", "EVALUATOR_ID", "PRODUCT_ID")
AS
SELECT acm.audit_id,
GET_TOKEN(GET_TOKEN(acm.PRIMARY_KEY_VALUES,1,','),2,':') AS user_id,
acm.table_name_txt,
acm.column_name_txt,
CASE
WHEN UPPER(acm.column_name_txt) = 'PASSWORD_TXT'
THEN '******'
ELSE acm.old_value_txt
END AS old_value_txt,
CASE
WHEN UPPER(acm.column_name_txt) = 'PASSWORD_TXT'
THEN '******'
ELSE acm.new_value_txt
END AS new_value_txt,
acm.audit_last_updated_dt,
CASE
WHEN UPPER(acm.audit_updated_user_id) = 'UNKNOWN'
THEN acm.audit_updated_user_id
ELSE (users.user_id
|| ' ('
|| DECODE(users.last_name_txt, NULL,' ' , users.last_name_txt)
|| ', '
|| DECODE(users.first_name_txt, NULL,'' , users.first_name_txt)
|| ')' )
END
AS audit_uupdated_user_id,
acm.evaluator_id,
TO_NUMBER(GET_TOKEN(GET_TOKEN(acm.PRIMARY_KEY_VALUES,2,','),2,':')) AS product_id
FROM audit_config_maintenance acm,
users
WHERE acm.table_name_txt in ('USERS','XREF_USER_PRODUCT')
AND UPPER(acm.audit_updated_user_id) = UPPER(users.user_id)
AND acm.primary_key_values is not null
While creating the view as above, the get_token() function makes the column type a varchar2(4000). How do I change the command so that it makes that field a varchar2(64) ?
在创建上述视图时，get_token() 函数使列类型成为 varchar2(4000)。如何更改命令，使其成为 varchar2(64) 字段？
回答by Justin Cave
You can use the CASTfunction, i.e. CAST( GET_TOKEN(GET_TOKEN(acm.PRIMARY_KEY_VALUES,1,','),2,':') AS VARCHAR2(64)
您可以使用该CAST功能，即CAST( GET_TOKEN(GET_TOKEN(acm.PRIMARY_KEY_VALUES,1,','),2,':') AS VARCHAR2(64)
If you create a function
如果你创建一个函数
SQL> create or replace function my_function
2 return varchar2
3 is
4 begin
5 return 'foo';
6 end;
7 /
Function created.
Then a simple view that selects this function will have a VARCHAR2(4000)data type.
然后选择此函数的简单视图将具有VARCHAR2(4000)数据类型。
SQL> create or replace view v1 as select my_function col1 from dual;
View created.
SQL> desc v1;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 VARCHAR2(4000)
If you wrap the call in a CAST, however, the length changes
CAST但是，如果将调用包装在 a中，则长度会发生变化
SQL> ed
Wrote file afiedt.buf
1 create or replace view v2
2 as
3 select cast( my_function as varchar2(10) ) col1
4* from dual
SQL> /
View created.
SQL> desc v2;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 VARCHAR2(10)
回答by Kevin Rohrssen
Use the cast function. In this case, wrap get_toklen with cast
使用强制转换功能。在这种情况下，用 cast 包装 get_toklen
CAST(GET_TOKEN(GET_TOKEN(acm.PRIMARY_KEY_VALUES,1,','),2,':') as varchar2(64)) AS user_id
