Sql-server – How to translate SQL Server stored procedure into Oracle stored procedure

It wouldn't perform as well, but to meet your requirements given your limitations, you could use a pipelined result set. Both your original procedure and your new procedure could then use the function. The original one would return the entire results, and the new procedure would wrap the function in a larger query that does the GROUP BY. In this way the core query code would not be duplicated. Here is a pipelined result set example along with two queries using it:

create or replace PACKAGE Example AUTHID Definer AS

Type tRow Is Record (
     F1 Date
   , F2 Date
   );
Type tTable Is Table of tRow;
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined;

END Example;
/


create or replace PACKAGE BODY Example AS

Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined AS
   vYesterday Date := trunc(sysdate-1);
Begin
  For vRow IN (
      SELECT pDate, vYesterday FROM DUAL
      UNION ALL
      SELECT pDate, vYesterday-1 FROM DUAL
      UNION ALL
      SELECT pDate+1, vYesterday FROM DUAL
  )
  Loop
    Pipe Row(vRow);
  End Loop;
End;

END Example;
/


SELECT * FROM TABLE(Example.PipelineResult);

SELECT F1, count(*) FROM TABLE(Example.PipelineResult) GROUP BY F1;

You can do this by using a save point.

CREATE OR REPLACE PROCEDURE SPTest
AS 
BEGIN
   -- We create a savepoint here.
   SAVEPOINT sp_sptest;
   
   INSERT INTO emptest(empid, empname, deptno)
   VALUES(1, 'ravi', 10);
   
   INSERT INTO test1(id, name, sal)
   VALUES(1, 'raju', 4444);
   
   UPDATE emptest
   SET empname = 'hari'
   WHERE empid = 1;
   
-- If any exception occurs
EXCEPTION
   WHEN OTHERS THEN
      -- We roll back to the savepoint.
      ROLLBACK TO sp_sptest;
      
      -- And of course we raise again,
      -- since we don't want to hide the error.
      -- Not raising here is an error!
      RAISE;
END;

The commit will typically be left to the caller. This construct just guarantees that either all of the inserts and the update are done, or none of them is.

On the most outer level, Oracle will do a rollback by itself. In other words, if you do EXEC sptest(); on the SQL+ command line, it will be expanded automatically to

DECLARE
BEGIN
   sptest();
EXCEPTION
   ROLLBACK;
   RAISE;
END;

But if a calling procedure has exception handling, this doesn't happen, since the exception may not get to that outer level. So you may end up with the inserts done, the update throwing an exception which may be caught and handled by the caller, so that the update will have failed but the inserts succeeded.

By doing a rollback in the stored procedure to the beginning of the procedure, you ensure that all of them succeed, or none of them do.