为什么此存储过程将NULL值插入表中? [英] Why is this Stored Procedure Inserting NULL values into the table?

问题描述

我试图将数据(2列)从XML_HOURS_LOAD (columns: code,product)加载到名为STAGING (columns: code, product)的表中，并且正在为两列插入空值:

I am trying to load data (2 columns) from XML_HOURS_LOAD (columns: code,product) to a table called STAGING (columns: code, product) and am getting null values inserted for both columns:

所以我有以下存储过程:

So I have the following stored procedure:

Create or Replace Procedure Cascade_Load (
   p_code in XML_HOURS_LOAD.p_code%TYPE,
   p_product in XML_HOURS_LOAD.p_product%TYPE
)
AS
BEGIN
   INSERT INTO STAGING(code, product)
   VALUES(p_code, p_product)

   COMMIT;
END;

我做错了什么?预先感谢.

What am I doing wrong? Thanks in advance.

推荐答案

回答您为什么插入空值的问题，这是因为在执行过程时没有为过程参数提供任何值.

To answer your question of why it's inserting nulls, that is because you aren't providing any values to the procedure parameters when you execute it.

根据您在问题中所说的和上面的评论，看来您在使用Oracle方面缺少一些基本技能.您编写的代码是一个过程，而不是一个函数，因此您不能在SELECT语句中调用它.在plsql块内部调用一个过程.编写的过程带有两个参数，您必须通过调用代码将其传递给过程调用.您编写的过程代码不会从XML_HOURS_LOAD表中查找数据.

Based on what you stated in the question and your comment above, it seems you are missing some fundamental skills in working with Oracle. The code you wrote is a procedure, not a function, so you can't call it in a SELECT statement. A procedure is called inside of a plsql block. Your procedure as written takes two arguments, which you must pass to the procedure call via the calling code. The procedure code you wrote does not look for data from the XML_HOURS_LOAD table.

我们都是学习Oracle的新人.您将需要看一些教程，以开始使用pl/sql编码的基础知识，以帮助清除函数和存储过程之间的差异以及如何使用参数自变量.

We've all been the new person learning Oracle. You'll want to look at some tutorials to get you started on the fundamentals of pl/sql coding to help clear up the differences between functions and stored procedures and how to use parameter arguments.

根据您在问题中所写的内容，我相信这是您想要的代码:

From what you wrote in your question, I believe this is the code you want:

DECLARE
   p_code IS XML_HOURS_LOAD.code%TYPE,
   p_product IS XML_HOURS_LOAD.product%TYPE;
   CURSOR cXmlHoursLoadCursor IS (SELECT code, product FROM xml_hours_load); --You can add a WHERE condition to this cursor query
BEGIN
    FOR v IN cXmlHoursLoadCursor LOOP
       Cascade_Load(v.code, v.product);
       COMMIT; --I recommend calling commit here instead of inside your stored procedure so that the calling code has control of the transaction state
    END LOOP;
END;

这篇关于为什么此存储过程将NULL值插入表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！