SQL 如何在 Oracle 中将 NUMBER 转换为 VARCHAR2？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20616806/
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
How do I CAST a NUMBER to VARCHAR2 in Oracle?
提问by Joergen Bech
I have a problem with some P-SQL syntax. I have reduced the code sample to its minimum below.
我有一些 P-SQL 语法的问题。我已将代码示例减少到下面的最小值。
The following works:
以下工作：
CREATE OR REPLACE FUNCTION MyFunction(LINE_ID SMALLINT)
RETURN VARCHAR2 IS
tmp VARCHAR2(4000);
BEGIN
tmp := CAST(LINE_ID AS VARCHAR2);
RETURN(tmp);
END MyFunction;
/
However, I need to change the LINE_ID parameter to NUMBER(5, 0), after which the following does not work:
但是，我需要将 LINE_ID 参数更改为 NUMBER(5, 0)，之后以下操作不起作用：
CREATE OR REPLACE FUNCTION MyFunction2(LINE_ID NUMBER(5, 0))
RETURN VARCHAR2 IS
tmp VARCHAR2(4000);
BEGIN
tmp := CAST(LINE_ID AS VARCHAR2);
RETURN(tmp);
END MyFunction2;
/
The error message in Oracle SQL Developer 3.2.10.09 is
Oracle SQL Developer 3.2.10.09 中的错误消息是
Error(1,36): PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue.
错误（1,36）：PLS-00103：在期望以下之一时遇到符号“（”：：=。），@%默认字符符号“：=”被替换为“（”以继续。
How should I write the CASTstatement in order to make it work with NUMBER(5, 0)instead of SMALLINT?
我应该如何编写CAST语句以使其与NUMBER(5, 0)而不是一起工作SMALLINT？
Again, this is not the original code but I am looking for a solution that does not deviate too much from the second version and preferably not another function call either. The VARCHAR2return type is important as well.
同样，这不是原始代码，但我正在寻找一种解决方案，该解决方案不会与第二个版本偏离太多，并且最好也不是另一个函数调用。在VARCHAR2返回类型也很重要。
采纳答案by GriffeyDog
You can't specify NUMBERprecision and scale for a function's parameter. Just declare it like this:
您不能NUMBER为函数的参数指定精度和小数位数。只需像这样声明：
CREATE OR REPLACE FUNCTION MyFunction2(LINE_ID NUMBER)
回答by Mureinik
The function you're looking for is TO_CHAR:
您正在寻找的功能是TO_CHAR：
tmp := TO_CHAR(LINE_ID);
回答by Craig
The issue is not in your CAST, but rather in your parameter definition. From the documentation:
问题不在于您的 CAST，而在于您的参数定义。从文档：
You can declare a formal parameter of a constrained subtype, like this:
您可以声明受约束子类型的形式参数，如下所示：
DECLARE
SUBTYPE n1 IS NUMBER(1);
SUBTYPE v1 IS VARCHAR2(1);
PROCEDURE p (n n1, v v1) IS ...
But you cannot include a constraint in a formal parameter declaration, like this:
但是您不能在形参声明中包含约束，如下所示：
DECLARE
PROCEDURE p (n NUMBER(1), v VARCHAR2(1)) IS ...
