The only query that works here is the INSERT so that makes me thing that the condition is working properly (I tested running the SP twice without inserting nothing new to table1) but the UPDATE query do not do anything and the IF condition is proper.

This is my SP:

CREATE OR REPLACE PROCEDURE proc_user_messages_count
AS
   l_m_document_id    VARCHAR2(11);
   l_mc_document_id   VARCHAR2(11);
   --l_mc_messages INTEGER;
BEGIN
     SELECT document_id
       INTO l_m_document_id
       FROM table1
      WHERE status IN (0, 1)
   ORDER BY creationdate DESC
     OFFSET 0 ROWS
      FETCH NEXT 1 ROWS ONLY;

   SELECT document_id
     INTO l_mc_document_id
     FROM table2
    WHERE document_id = l_m_document_id;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      l_mc_document_id := 0;

      IF l_mc_document_id = 0
      THEN
         INSERT INTO table2
              VALUES (l_m_document_id, 1);
      ELSE
         --SELECT messages INTO l_mc_messages FROM table2 WHERE document_id = l_mc_document_id;
         UPDATE table2
            SET messages = messages   1
          WHERE document_id = l_mc_document_id;
      END IF;
END proc_user_messages_count;
/

The commented lines are me trying to figure it out if those conditions were messing this up. This is my first time doing Stored Procedures, any advice would be appreciated.

CodePudding user response：

I hope you debugged it well, i.e. made sure that UPDATE actually has something to update:

• if there's no row whose document_id = l_mc_document_id, nothing will happen
• if messages column value is NULL, result of messages 1 will also be NULL; consider nvl(messages, 0) 1 in that case

Furthermore, I think you misplaced exception; see whether enclosing the first two select statements into its own (inner) begin-exception-end block makes sense. Because, you don't really want to put the whole IF into exception - which is what your code currently does.

Modified, your code might look like this:

CREATE OR REPLACE PROCEDURE Proc_USER_MESSAGES_Count AS
  l_m_document_id VARCHAR2(11);
  l_mc_document_id VARCHAR2(11);
  --l_mc_messages INTEGER;
BEGIN
  BEGIN
    SELECT document_id INTO l_m_document_id 
      FROM table1 
      WHERE status IN (0,1) 
      ORDER BY creationdate DESC 
      OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;
      
    SELECT document_id INTO l_mc_document_id 
      FROM table2 
      WHERE document_id = l_m_document_id;

  EXCEPTION 
    WHEN NO_DATA_FOUND THEN l_mc_document_id := 0;
  END;
  
  IF l_mc_document_id = 0 THEN
     INSERT INTO table2 VALUES(l_m_document_id,1);
  ELSE
     --SELECT messages INTO l_mc_messages FROM table2 WHERE document_id = l_mc_document_id;
     UPDATE table2 SET messages = nvl(messages, 0)   1 
     WHERE document_id = l_mc_document_id;
  END IF;
END Proc_USER_MESSAGES_Count;
/

CodePudding user response：

The problem is a missing BEGIN END. I've reformatted your code so that it's clear what the structure of each block is. Then it becomes clear that the whole part with the INSERT / UPDATE is only executed if the NO_DATA_FOUND exception is thrown.

This should do it:

CREATE OR REPLACE PROCEDURE proc_user_messages_count
AS
   l_m_document_id    VARCHAR2(11);
   l_mc_document_id   VARCHAR2(11);
--l_mc_messages INTEGER;
BEGIN
     SELECT document_id
       INTO l_m_document_id
       FROM table1
      WHERE status IN (0, 1)
   ORDER BY creationdate DESC
     OFFSET 0 ROWS
      FETCH NEXT 1 ROWS ONLY;

   BEGIN
      SELECT document_id
        INTO l_mc_document_id
        FROM table2
       WHERE document_id = l_m_document_id;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         l_mc_document_id := 0;
   END;

   IF l_mc_document_id = 0
   THEN
      INSERT INTO table2
           VALUES (l_m_document_id, 1);
   ELSE
      --SELECT messages INTO l_mc_messages FROM table2 WHERE document_id = l_mc_document_id;
      UPDATE table2
         SET messages = messages   1
       WHERE document_id = l_mc_document_id;
   END IF;
END proc_user_messages_count;
/

The difference is an additional BEGIN before the second SELECT and the END after the exception handler.