更新语句在存储过程中不工作;没有错误，也没有例外

I have created a stored procedure in Oracle. The procedure is compiled successfully with no errors. The procedure has 3 UPDATE queries which updates 3 tables 'TBLHOTEL', 'TBLHOTELDETAIL' & 'TBLHOTELFARE'.

我在Oracle中创建了一个存储过程。该过程成功编译，没有错误。该过程有3个更新查询，更新3个表“TBLHOTEL”、“TBLHOTELDETAIL”和“TBLHOTELFARE”。

After every Update statement a variable successCnt1 is incremented to get the number of successful insert queries. At last successCnt1 is assigned to successCnt to store the final result. If exception happens then in any query, it is set to 0 , to indicate no insertion happens.

在每次更新语句之后，都会增加一个变量successesscnt1，以获取成功插入查询的数量。最后，将成功的scnt1分配到成功的成功的scnnt以存储最终的结果。如果在任何查询中发生异常，则将其设置为0，以表明没有插入发生。

Problem is no exception is happening and also no update is happening to the database.

问题是没有异常发生，数据库也没有更新发生。

Here is my code:

这是我的代码:

Schemas:

模式:

TBLHOTEL Schema: {DATE1 (DATE) , ACROOMS (NUMBER) , NACROOMS (NUMBER), HOTELID (VARCHAR2(10)) }

TBLHOTEL模式:{DATE1(日期)，ACROOMS(编号)，NACROOMS(编号)，HOTELID (VARCHAR2(10))}

TBLHOTELFARE Schema: {HOTELID (VARCHAR2(10)), CLASS (VARCHAR2(5)), FARE (NUMBER)}

TBLHOTELFARE Schema: {HOTELID (VARCHAR2(10))， CLASS (VARCHAR2(5))， FARE(数字)}

TBLHOTELDETAIL Schema: {HOTELID (VARCHAR2(10)) , PLACE (VARCHAR2(15)) , HOTELNAME (VARCHAR2(15)) }

TBLHOTELDETAIL模式:{HOTELID (VARCHAR2(10))， PLACE (VARCHAR2(15))， HOTELNAME (VARCHAR2(15)))}

Procedure:

过程:

CREATE OR REPLACE PROCEDURE TableUpdateByParameter (acrooms   in number,
                                           nacrooms  in number,
                                           date1      in date,
                                           hotelid   in varchar2,
                                           fare in number,
                                           place in varchar2,
                                           hotelname in varchar2,
                                           class in varchar2,
                                           successCnt  out number) IS

successCnt1 number(6) NOT NULL := 0;
rowUpdated1 number(6) NOT NULL := 0;
rowUpdated2 number(6) NOT NULL := 0;
rowUpdated3 number(6) NOT NULL := 0;

BEGIN
  SAVEPOINT before;

  UPDATE tblhotel
     SET acrooms = acrooms, nacrooms = nacrooms
   WHERE date1 = (to_date(date1, 'mm/dd/yyyy'))
     AND hotelid = 'hotelid' ;

rowUpdated1 := SQL%RowCount;
successCnt1 := successCnt1 + 1;

   dbms_output.put_line('Successful Insertion tblhotel. count ='||successCnt1);
   dbms_output.put_line('Successful Insertion tblhotel. Row Updated ='||rowUpdated1);

  UPDATE tblhoteldetail
 SET place = 'place', hotelname = 'hotelname'
   WHERE hotelid = 'hotelid' ;

rowUpdated2 := SQL%RowCount;
successCnt1 := successCnt1 + 1;

dbms_output.put_line('Successful Insertion tblhoteldetail. count ='||successCnt1);
dbms_output.put_line('Successful Insertion tblhoteldetail. Row Updated= '||rowUpdated2);

  UPDATE tblhotelfare
     SET fare = fare
   WHERE hotelid = 'hotelid' 
     AND class = 'class';

rowUpdated3 := SQL%RowCount;
successCnt1 := successCnt1 + 1;
successCnt := successCnt1;

 COMMIT;

 dbms_output.put_line('Successful Insertion tblhotelfare. count ='||successCnt);
dbms_output.put_line('Successful Insertion tblhotelfare. Row Updated= '||rowUpdated3);

EXCEPTION
  WHEN Others THEN
    successCnt1 := 0;
    successCnt := successCnt1;
    dbms_output.put_line('An error has occured. count ='||successCnt);

    ROLLBACK TO before;

END;

CALLING STATEMENT

调用语句

DECLARE 
 C  number;

BEGIN

   TableUpdateByParameter (140,200,TO_DATE('03/24/2013','MM/DD/YYYY'),'H1',3000,'GANGTOK','TRIPTI','AC',C);
END;

DBMS OUTPUT:

DBMS输出:

Successful Insertion tblhotel. count =1
Successful Insertion tblhotel. Row Updated =0
Successful Insertion tblhoteldetail. count =2
Successful Insertion tblhotel. Row Updated =0
Successful Insertion tblhotelfare. count =3
Successful Insertion tblhotel. Row Updated =0

Please help me to identify the problem. If extra information needed, please let me know.

请帮我找出问题所在。如果需要额外的信息，请告诉我。

3 个解决方案

#1

The UPDATE statement is working, but updates 0 rows, as shown per your log (Row Updated =0). This is not an error, your updates evaluate the where clause, finds 0 rows matching, and performs 0 modification. In Oracle, an update that doesn't match any row in the where clause is still a success.

UPDATE语句正在工作，但是更新0行，如您的日志(行更新=0)。这不是错误，您的更新计算where子句，找到0行匹配，并执行0修改。在Oracle中，不匹配where子句中的任何一行的更新仍然是成功的。

Now why does it happen. Let's take your first update:

为什么会这样呢?让我们来看看你的第一个更新:

UPDATE tblhotel
   SET acrooms = acrooms, nacrooms = nacrooms
 WHERE date1 = (to_date(date1, 'mm/dd/yyyy'))
   AND hotelid = 'hotelid' ;

I think you want to update the row that have the column hotelid with the value passed as parameter. There are a few problems with that:

我认为您需要更新具有作为参数传递的值的列hotelid的行。这其中有几个问题:

First of all, you're not comparing the column and the parameter, but the column with a constant. Parameters don't use quotes. Constants (VARCHAR2) do.
首先，不是比较列和参数，而是比较列和常数。参数不使用引号。常数(VARCHAR2)。
Secondly, you shouldn't call a parameter with the same name as your column, this causes confusion and could even cause variable shadowing. I recommend using a prefix that no column whatsoever uses in your schema. One common prefix for parameters is p_.
其次，不应该调用与列名称相同的参数，这会导致混淆，甚至可能导致变量隐藏。我建议使用在模式中不使用任何列的前缀。参数的一个常见前缀是p_。
Finally, you don't need conversion functions if your parameter are of the good type (since your parameter p_date1 is of type date, you don't need the to_date function).
最后，如果您的参数类型很好，则不需要转换函数(因为您的参数p_date1类型为date，所以不需要to_date函数)。

So if you rename your parameters p_hotelid and p_date1, your statement should read:

因此，如果您重命名参数p_hotelid和p_date1，您的语句应该是:

UPDATE tblhotel
   SET acrooms = acrooms, nacrooms = nacrooms
 WHERE date1 = p_date1
   AND hotelid = p_hotelid;

In this case there is no confusion nor conversion error possible.

在这种情况下，不可能出现混淆或转换错误。

On an unrelated note:

在一个不相关的注意:

there seems to be a mismatch between your procedure name and the log it generates: no insertion will ever be done by update statements.
您的过程名称和它生成的日志之间似乎存在不匹配的地方:不需要通过update语句来执行插入操作。
Don't catch when others, let the error propagate. PL/SQL will rollback the procedure changes if the error propagates. PL/SQL statements (DML and blocks) are atomic by nature, they either fail completely or succeed entirely.
不要捕捉别人，让错误传播。如果错误传播，PL/SQL将回滚该过程。PL/SQL语句(DML和块)本质上是原子的，它们要么完全失败，要么完全成功。

#2

If hotelid is a column in your database, you almost certainly do not want to overload the name to use it as the name of an argument to the procedure. Doing so makes using the parameter in a SQL statement rather complicated. Most people develop some sort of convention to differentiate the two. I prefer prefixing parameter names with a p_ which is a relatively common convention.

如果hotelid是数据库中的一个列，那么您几乎肯定不想重载这个名称，以将其用作过程参数的名称。这样做使得在SQL语句中使用参数变得相当复杂。大多数人会形成某种习惯来区分这两者。我更喜欢使用p_的前缀参数名称，这是一种比较常见的约定。

CREATE OR REPLACE PROCEDURE TableUpdateByParameter (p_acrooms   in number,
                                           p_nacrooms  in number,
                                           p_date1      in date,
                                           p_hotelid   in varchar2,
                                           p_fare in number,
                                           p_place in varchar2,
                                           p_hotelname in varchar2,
                                           p_class in varchar2,
                                           p_successCnt  out number) 
IS

Your SQL statement would then become

然后，您的SQL语句将变成

 WHERE tblhoteldetail.hotelid = p_hotelid

If you really want to overload the parameter name, you'd have to prefix the parameter name with the procedure name. But that will generally cause you no end of grief as you inadvertently write code like

如果您真的想重载参数名，那么必须在参数名前面加上过程名。但这通常会让您感到无限痛苦，因为您无意中编写了类似的代码

 WHERE tblhoteldetail.hotelid = hotelid

and inadvertently update every row in your table.

不小心更新了表中的每一行。

#3

Each of your where statements is similar to this:

你的每一个陈述都类似于:

AND hotelid = 'hotelid'

This will match the hotelid column on the table against that string literal "hotelid" each time, not the value passed into the proc.

这将会匹配表上的hotelid列，而不是每次传递给proc的值。

To get them to reference the parameter you need to use something like:

要让它们引用参数，您需要使用以下内容:

AND tblhotel.hotelid = TableUpdateByParameter.hotelid

where the left hand side is the column on the table and the right hand side is your proc parameter.

左边是表上的列右边是proc参数。

An alternative would be to have a naming convention that all params are prefixed with "p", so that you can use:

另一种选择是制定一个命名约定，所有params都以“p”为前缀，以便您可以使用:

AND tblhotel.hotelid = photelid

#1