SQL Oracle 中的存储函数未将值插入所需的表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9851062/
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
Stored Function in Oracle not Inserting Values into the desired table
提问by Dimitar Yankovski
Here is my Code given below. I am trying to add 5 parameters which the function takes into the table Employee. But I am not successful in doing it and have tried a lot of things.
这是我下面给出的代码。我正在尝试将函数采用的 5 个参数添加到 Employee 表中。但是我做起来并不成功，并且尝试了很多东西。
Error
错误
ORA-01858: a non-numeric character was found where a numeric was expected ORA-06512: at "xxxxxxx.A1SF_ADDEMP", line 14 01858. 00000 - "a non-numeric character was found where a numeric was expected" *Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model. *Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation.
ORA-01858：在需要数字的地方找到非数字字符 ORA-06512：在“xxxxxxx.A1SF_ADDEMP”，第 14 行 01858. 00000 - “在需要数字的地方找到非数字字符” *原因：要使用日期格式模型转换的输入数据不正确。输入数据不包含数字，其中格式模型需要数字。*操作：修复输入数据或日期格式模型以确保元素在数量和类型上匹配。然后重试该操作。
Plus how do I test a Stored function that has a Insert/Update or Delete Statement in it?
另外，如何测试包含插入/更新或删除语句的存储函数？
Execution Statement
执行声明
Select A1SF_ADDEMP('Adesh', '33', 'M', 8000, '26/03/1990')
From dual;
Code
代码
CREATE OR REPLACE Function A1SF_ADDEMP
(pEmpName In Varchar2,
pTaxFileNo In Varchar2,
pGender In Varchar2,
pSalary In Number,
pBirthdate In Varchar2
) Return Varchar2
Is
tEmpId Number(38,0);
tBirthDate Date;
BEGIN
tEmpId := A1Seq_Emp.nextval;
tBirthdate := to_date('pBirthdate','dd/mm/yyyy');
Insert Into Employee(EmpId, EmpName, TaxFileNo, Gender, Salary, Birthdate)
Values (tEmpId, pEmpName, pTaxFileNo, pGender, pSalary, tBirthdate);
Commit;
Return null;
END;
回答by a_horse_with_no_name
You should use a procedure(instead of a function) if you are not returning any values.
如果不返回任何值，则应使用过程（而不是函数）。
If you look at the line mentioned in the error message you can spot your error:
如果您查看错误消息中提到的行，您可以发现您的错误：
tBirthdate := to_date('pBirthdate','dd/mm/yyyy');
You are passing the string literal'pBirthdate' to the to_date() call. But you want to pass the parameter, so it should be
您将字符串文字'pBirthdate' 传递给 to_date() 调用。但是你想传递参数，所以应该是
tBirthdate := to_date(pBirthdate,'dd/mm/yyyy');
(note the missing single quotes arount pBirthdate).
（注意 pBirthdate 中缺少的单引号）。
So as a procedure the whole thing would look like this:
所以作为一个程序，整个事情看起来像这样：
CREATE OR REPLACE PROCEDURE A1SF_ADDEMP
(pEmpName In Varchar2,
pTaxFileNo In Varchar2,
pGender In Varchar2,
pSalary In Number,
pBirthdate In Varchar2
)
IS
BEGIN
Insert Into Employee(EmpId, EmpName, TaxFileNo, Gender, Salary, Birthdate)
Values (A1Seq_Emp.nextval, pEmpName, pTaxFileNo, pGender, pSalary, to_date(pBirthdate,'dd/mm/yyyy'));
Commit;
END;
To run it:
运行它：
execute A1SF_ADDEMP('Adesh', '33', 'M', 8000, '26/03/1990');
回答by Ben
Firstly you cannot call a function with DMLin it in a select statement. You have to assign the output to a variable in a PL/SQL block, something like:
首先，您不能在 select 语句中调用带有DML的函数。您必须将输出分配给 PL/SQL 块中的变量，例如：
declare
l_output number;
begin
l_output := my_function(variable1, variable2);
end;
It's bad practice to do DML in a function; partly because it causes the errors you're coming across. You should use a procedure as detailed below. The other reason for this is that you're as always returning null there's no need to return anything at all!
在函数中执行 DML 是不好的做法；部分原因是它会导致您遇到的错误。您应该使用如下详述的程序。另一个原因是你总是返回 null 根本不需要返回任何东西！
create or replace procedure my_procedure ( <variables> ) is
begin
insert into employees( <columns> )
values ( <values > );
end;
The specific reason for your error is this line:tBirthdate := to_date('pBirthdate','dd/mm/yyyy');
您的错误的具体原因是这一行：tBirthdate := to_date('pBirthdate','dd/mm/yyyy');
pBirthdateis already a string; by putting a 'around it you're passing the string 'pBirthdate'to the function to_dateand Oracle can't convert this string into a day, month or year so it's failing.
pBirthdate已经是一个字符串；通过'在它周围放置一个，您将字符串传递'pBirthdate'给函数，to_date而 Oracle 无法将此字符串转换为日、月或年，因此它失败了。
You should write this as:tBirthdate := to_date(pBirthdate,'dd/mm/yyyy');
你应该这样写：tBirthdate := to_date(pBirthdate,'dd/mm/yyyy');
You also don't need to specify number(38,0), you can just write numberinstead.
你也不需要指定number(38,0)，你可以直接写number。
It is possible to return a value from a procedure using the outkeyword. If we assume that you want to return empidyou could write is as something like this:
可以使用out关键字从过程中返回一个值。如果我们假设你想返回empid你可以写成这样：
create or replace procedure A1SF_ADDEMP (
pEmpName in varchar2
, pTaxFileNo in varchar2
, pGender in varchar2
, pSalary in number
, pBirthdate in varchar2
, pEmpid out number
) return varchar2 is
begin
pempid := A1Seq_Emp.nextval;
Insert Into Employee(EmpId, EmpName, TaxFileNo, Gender, Salary, Birthdate)
Values ( pEmpId, pEmpName, pTaxFileNo, pGender
, pSalary, to_date(pBirthdate,'dd/mm/yyyy');
end;
To just execute the procedure call it like this:
要执行该过程，请像这样调用它：
begin
A1SF_ADDEMP( EmpName, TaxFileNo, Gender
, Salary, Birthdate);
commit;
end;
If you want to return the empidthen you can call it like this:
如果你想返回empid然后你可以这样调用它：
declare
l_empid number;
begin
l_empid := A1SF_ADDEMP( EmpName, TaxFileNo, Gender
, Salary, Birthdate);
commit;
end;
Notice how I've moved the committo the highest level, this is to avoid committing stuff in every procedure when you might have more things you need to do.
请注意我如何将 移到commit最高级别，这是为了避免在您可能需要做更多事情时在每个过程中提交内容。
Incidentally, if you're using Oracle 11g then there's no need to assign the value A1Seq_Emp.nextvalto a variable. You can just insert it directly into the table in the valueslist. You, of course won't be able to return it, but you could return A1Seq_Emp.curval, as long as there's nothing else getting values from the sequence.
顺便说一句，如果您使用的是 Oracle 11g，则无需将该值分配A1Seq_Emp.nextval给变量。您可以直接将其插入列表中的values表中。您当然无法返回它，但您可以返回A1Seq_Emp.curval，只要没有其他任何东西从序列中获取值。
回答by Dimitar Yankovski
In your situation you need to use procedure with out parameter where your out param is your returning param that contains your desirable value. This is I think best practice in this situation when you want to use DML in select statement.
在您的情况下，您需要使用带有 out 参数的过程，其中 out 参数是包含所需值的返回参数。当您想在 select 语句中使用 DML 时，这是我认为在这种情况下的最佳实践。
Second way to do it but its not a good practice is to use one function like yours but before return a value you need to use if statement to check what is the value and if value is what you desire to call in this if statement PRAGMA AUTONOMOUS_TRANSACTION procedure which will do your DML independently of function calling to it. For more information about pragma transactions your can read here: http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/autonotransaction_pragma.htm
第二种方法，但它不是一个好的做法是使用一个像你这样的函数，但在返回一个值之前，你需要使用 if 语句来检查值是什么，如果值是你想要在这个 if 语句中调用的值 PRAGMA AUTONOMOUS_TRANSACTION程序将独立于函数调用执行您的 DML。有关 pragma 事务的更多信息，您可以在此处阅读：http: //docs.oracle.com/cd/B19306_01/appdev.102/b14261/autonotransaction_pragma.htm
Best Regards and hope I was helpful
最好的问候，希望我有帮助
