3 Triggers and Stored Procedures

3.2.1 Individual SQL Statements

In individual SQL statements, you should try to follow ANSI-standard SQL whenever possible. However, there are cases where you need to use database-specific SQL constructs, mostly for ease of use, simplicity of coding, and performance enhancement. For example, Microsoft SQL Server or Sybase Adaptive Server constructs such as the following are SQL Server or Sybase Adaptive Server-specific:

update <table_name>
set ...
from <table1>, <table_name>
where...

The conversion performed by the automatic conversion utilities for statements such as this can be seen in the following examples:

DELETE sales
FROM sales, titles
WHERE sales.title_id = titles.title_id
AND titles.type = 'business'

DELETE sales
WHERE ROWID IN
( SELECT sales.ROWID
  FROM sales ,
       titles
     WHERE sales.title_id = titles.title_id
             AND titles.TYPE = 'business' );

UPDATE titles
SET price = price + author_royalty
FROM titles, title_author
WHERE titles.title.id = title_author.title_id

MERGE INTO titles
USING (SELECT * FROM title_author) title_author
ON ( title.id = title_author.title_id )
WHEN MATCHED THEN UPDATE SET price = price + author_royalty;

3.2.2 Logical Transaction Handling

In Microsoft SQL Server or Sybase Adaptive Server, transactions are explicit by definition. This implies that an individual SQL statement is not part of a logical transaction by default. A SQL statement belongs to a logical transaction if the transaction explicitly initiated by the user with a BEGIN TRANSACTION (or BEGIN TRAN) statement is still in effect. The logical transaction ends with a corresponding COMMIT TRANSACTION (or COMMIT TRAN) or ROLLBACK TRANSACTION (or ROLLBACK TRAN) statement. Each SQL statement that is not part of a logical transaction is committed on completion.

In Oracle, transactions are implicit as set by the ANSI standard. The implicit transaction model requires that each SQL statement is part of a logical transaction. A new logical transaction is automatically initiated when a COMMIT or ROLLBACK command is executed. This also implies that data changes from an individual SQL statement are not committed to the database after execution. The changes are committed to the database only when a COMMIT statement is run. The differences in the transaction models impact the coding of application procedures.

3.2.3 Error Handling Within the Stored Procedure

Oracle PL/SQL checks each SQL statement for errors before proceeding with the next statement. If an error occurs, control immediately jumps to an exception handler. This avoids you having to check the status of every SQL statement. For example, if a SELECT statement does not find any rows in the database, an exception is raised, and the code to deal with this error is executed.

In Microsoft SQL Server or Sybase Adaptive Server, you need not check for errors after each SQL statement. Control is passed to the next statement, irrespective of the error conditions generated by the previous statement. It is your responsibility to check for errors after the execution of each SQL statement. Failure to do so may result in erroneous results.

In Oracle, to simulate the behavior of Microsoft SQL Server or Sybase Adaptive Server and to pass the control to the next statement regardless of the status of execution of the previous SQL statement, you must enclose each SQL statement in an equivalent PL/SQL block. This block must deal with all possible exceptions for that SQL statement. This coding style is required only to simulate Microsoft SQL Server or Sybase Adaptive Server behavior. An Oracle PL/SQL procedure ideally has only one exception block, and all error conditions are handled in that block.

Consider the following code in a Microsoft SQL Server or Sybase Adaptive Server stored procedure:

begin
        
        select @x = col1 from table1 where col2 = @y
        select @z = col3 from table2 where col4 = @x
        
end

In this code example, if the first SELECT statement does not return any rows, the value of @x could be UNDEFINED. If the control is passed on to the next statement without raising an exception, the second statement returns incorrect results because it requires the value of @x to be set by an earlier statement. In a similar situation, Oracle PL/SQL raises a NO_DATA_FOUND exception if the first statement fails.

3.3.1 Local Variable

T/SQL local variables can be any server data type except TEXT and IMAGE. PL/SQL local variables can be any server data type including the following:

• BINARY_INTEGER

• BOOLEAN

PL/SQL local variables can also be either of the following composite data types allowed by PL/SQL:

• RECORD

• TABLE

3.3.2 Server Data Types

See the "Data Types" for a list of Microsoft SQL Server or Sybase Adaptive Server data types and their equivalent Oracle data types.

3.3.3 Composite Data Types

Microsoft SQL Server or Sybase Adaptive Server does not have composite data types.

3.4.1 Procedure

This section provides the following tables for the schema object procedure :

• Create

• Drop

• Execute

• Alter

CREATE PROCEDURE procedure [@formal_parameter formal_parameter_data type  [= default_value] [OUTPUT] [,@formal_parameter formal_parameter_data type [= default_value] [OUTPUT]] ...

AS BEGIN] procedural_statements [END]

CREATE [OR REPLACE] PROCEDURE [schema.]procedure [(]                        [formal_parameter [IN | OUT | IN OUT]         formal_parameter_data type] [DEFAULT default_value]  [,formal_parameter             [IN | OUT | IN OUT]formal_parameter_data type] [DEFAULT default_value]] ...   [)]                             IS | AS                    [local_variable data type;]... BEGIN                       PL/SQL statements | PL/SQL blocks      END;

DROP PROCEDURE procedure 

DROP PROCEDURE [schema.]procedure 

DROP PROCEDURE myproc

DROP PROCEDURE sam.credit;

EXEC [@return_value = ] procedure [[@formal_parameter = ] {@actual_parameter | constant_literal} [OUTPUT]] [,[[@formal_parameter = ] {@actual_parameter | constant_literal} [OUTPUT]]] ...

procedure
 [([{actual_parameter | 
      constant_literal |
   formal_parameter =>
     {actual_parameter | 
      constant_literal}
   }]
 [,{actual_parameter | 
      constant_literal |
      formal_parameter =>
     {actual_parameter | 
      constant_literal}
   }] ....
)]

  @formal_parameter = @actual_parameter

'@actual_parameter ' as:
@formal_parameter = 10

  formal_parameter => actual_parameter

as: 
   formal_parameter => 10

  EXEC GetEmplName @EmpID
  EXEC @status = GetAllDeptCodes 
  EXEC @status =  UpdateEmpSalary @EmpID, 
   @EmpName
  EXEC UpdateEmpSalary 13000,'Joe Richards'

  EXEC UpdateEmpSalary 
@Employee = @EmpName, 
   @Employee_Id = @EmpID

 EXEC UpdateEmpSalary 
@EmpName, @Employee_Id = @EmpID 
 EXEC UpdateEmpSalary 
@Employee = @EmpName, @EmpID

 credit (accno, accname, amt, retstat); 

  credit (acc_no => accno, acc => accname, amount => amt, 
      return_status => retstat)

  credit (accno, accname, amount => amt, return_status => retstat)

ALTER PROCEDURE procedure_name 
    [ @parameter data_type [ = default ] [ OUTPUT ] 
[, @parameter data_type [ = default ] [ OUTPUT ]] …
    ] 
[ WITH 
    { RECOMPILE | ENCRYPTION 
        | RECOMPILE , ENCRYPTION 
    } 
]
[ FOR REPLICATION ] 
AS 
[BEGIN]
    Statements
[END]

ALTER PROCEDURE update_employee_salary 
@employee_id INT, @salary FLOAT
AS
   UPDATE employee
   SET salary = @salary
   WHERE employee_id = @employee_id;

sp_recompile my_proc

ALTER PROCEDURE [schema.]procedure COMPILE

sp_recompile my_proc

ALTER PROCEDURE sam.credit COMPILE;

3.4.2 Function

This section provides the following tables for the schema object Function:

• Create

• Drop

• Execute

• Alter

CREATE [OR REPLACE] FUNCTION [user.]function [(parameter [OUT] data type[,(parameter [IN OUT] data type]...)]
RETURN data type {IS|AS} block

  CREATE FUNCTION get_bal 
(acc_no IN NUMBER)
   RETURN  NUMBER 
 IS
     acc_bal NUMBER(11,12);
  BEGIN
   SELECT balance
     INTO acc_bal
     FROM accounts
     WHERE account_id = acc_no;
   RETURN(acc_bal);
  END;

DROP FUNCTION [schema.]function 

DROP FUNCTION sam.credit;

function [({actual_parameter | constant_literal}...)]

 1) IF sal_ok (new_sal, new_title) THEN
   ....
   END IF;

2) promotable:=
   sal_ok(new_sal, new_title) AND
   (rating>3);

ALTER FUNCTION [schema.]function COMPILE

 ALTER FUNCTION sam.credit COMPILE

3.4.3 Package

This section provides the following tables for the schema object Package:

• Create

• Drop

• Alter

CREATE [OR REPLACE] PACKAGE [user.]package {IS | AS} {variable_declaration | cursor_specification | exception_declaration | record_declaration | plsql_table_declaration | procedure_specification | function_specification | [{variable_declaration | cursor_specification | exception_declaration | record_declaration | plsql_table_declaration | procedure_specification | function_specification}; ]...}        END [package] 

CREATE PACKAGE emp_actions AS 
  -- specification
  TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL);
  CURSOR desc_salary (emp_id NUMBER) RETURN EmpRecTyp;

  PROCEDURE hire_employee
   (ename CHAR,
    job CHAR,
     mgr NUMBER,
     sal NUMBER,
     comm NUMBER,
     deptno NUMBER);
  PROCEDURE fire-employee (emp_id NUMBER);
END emp_actions;

DROP PACKAGE [BODY] [schema.]package 

DROP PACKAGE emp_actions;

ALTER PACKAGE [user.]package COMPILE [PACKAGE | BODY]

ALTER PACKAGE emp_actions COMPILE PACKAGE

3.4.4 Package Body

This section provides the following tables for the schema object Package Body:

• Create

• Drop

• Alter

CREATE [OR REPLACE] PACKAGE BODY [schema.]package                               {IS | AS} pl/sql_package_body

CREATE PACKAGE BODY emp_actions AS
-- body
   CURSOR desc_salary (emp_id NUMBER)
       RETURN EmpRecTyp IS
       SELECT empno, sal FROM emp
       ORDER BY sal DESC;
   PROCEDURE hire_employee
       (ename    CHAR,
        job      CHAR,
        mgr      NUMBER,
        sal      NUMBER,
        comm     NUMBER,
        deptno   NUMBER) IS
   BEGIN
       INSERT INTO emp VALUES 
            (empno_seq.NEXTVAL, ename,
             job, mgr, SYSDATE, sal,
             comm, deptno);
   END hire_employee;
   
   PROCEDURE fire_employee 
       (emp_id   NUMBER) IS
   BEGIN
        DELETE FROM emp
        WHERE empno = emp_id;
   END fire_employee;

END emp_actions;

DROP PACKAGE [BODY] [schema.]package 

DROP PACKAGE BODY emp_actions;

ALTER PACKAGE [user.]package COMPILE [PACKAGE | BODY]

ALTER PACKAGE emp_actions COMPILE BODY

3.5.1 CREATE PROCEDURE Statement

CREATE PROC proc1
AS 
RETURN 0

CREATE OR REPLACE FUNCTION proc1 RETURN NUMBER
AS
BEGIN
   RETURN 0;
END;

Comments

The REPLACE keyword is added to replace the procedure, function, or package if it already exists.

The procedure is translated to an Oracle function because it returns a value.

3.5.2 Parameter Passing

CREATE PROC proc1
@x int=-1,
@y money,
@z bit OUT,
@a char(20) = 'TEST'
AS 
    RETURN 0

CREATE OR REPLACE FUNCTION proc1
(
  v_x IN NUMBER DEFAULT -1,
  v_y IN NUMBER,
  v_z OUT NUMBER,
  v_a IN CHAR DEFAULT 'TEST'
)
RETURN NUMBER
AS
BEGIN
   RETURN 0;
END;

Comments

Parameter passing is almost the same in Microsoft SQL Server or Sybase Adaptive Server and Oracle. By default, all the parameters are INPUT parameters, if not specified otherwise.

The value of the INPUT parameter cannot be changed from within the PL/SQL procedure. Thus, an INPUT parameter cannot be assigned any values nor can it be passed to another procedure as an OUT parameter. In Oracle, only IN parameters can be assigned a default value.

The @ sign in a parameter name declaration is removed in Oracle.

In Oracle, the parameter data type definition does not include length/size.

Microsoft SQL Server or Sybase Adaptive Server data types are converted to Oracle base data types. For example, all Microsoft SQL Server or Sybase Adaptive Server numeric data types are converted to NUMBER and all alphanumeric data types are converted to VARCHAR2 and CHAR in Oracle.

3.5.3 DECLARE Statement

CREATE PROC proc1
AS 
DECLARE
        @x int,
        @y money,
        @z bit,
        @a char(20)
                RETURN 0
GO

CREATE OR REPLACE PROCEDURE proc1
AS
   v_x NUMBER(10,0);
   v_y NUMBER(19,2);
   v_z NUMBER(1,0);
   v_a CHAR(20);
BEGIN
   RETURN;
END;

Comments

Microsoft SQL Server or Sybase Adaptive Server and Oracle follow similar rules for declaring local variables.

SQL Developer overrides the scope rule for variable declarations. As a result, all the local variables are defined at the top of the procedure body in Oracle.

3.5.4 IF Statement

CREATE PROC proc1 @Flag int = 0
AS 
BEGIN
DECLARE @x int
IF ( @Flag=0 )
 SELECT @x = -1
ELSE
 SELECT @x = 10
END

CREATE OR REPLACE PROCEDURE proc1
(
  v_Flag IN NUMBER DEFAULT 0
)
AS
   v_x NUMBER(10,0);
BEGIN
   IF ( v_Flag = 0 ) THEN
      v_x := -1;
   ELSE
      v_x := 10;
   END IF

CREATE PROC proc1 @Flag char(2) = ''
AS 
BEGIN
DECLARE @x int
IF ( @Flag='' )
 SELECT @x = -1
ELSE IF (@Flag = 'a')
 SELECT @x = 10
ELSE IF (@Flag = 'b')
 SELECT @x = 20
END

CREATE OR REPLACE PROCEDURE proc1
(
  v_Flag IN CHAR DEFAULT ''
)
AS
   v_x NUMBER(10,0);
BEGIN
   IF ( v_Flag = '' ) THEN
      v_x := -1;
   ELSE
      IF ( v_Flag = 'a' ) THEN
         v_x := 10;
      ELSE
         IF ( v_Flag = 'b' ) THEN
            v_x := 20;
         END IF;
      END IF;
   END IF;
END;

CREATE PROC proc1
AS 
BEGIN
DECLARE @x int
IF EXISTS ( SELECT * FROM table2 )
 SELECT @x = -1
END

CREATE OR REPLACE PROCEDURE proc1
AS
   v_x NUMBER(10,0);
   v_temp NUMBER(1, 0) := 0;
BEGIN
   SELECT 1 INTO v_temp
      FROM DUAL
      WHERE EXISTS ( SELECT *
                     FROM table2  );
      
   IF v_temp = 1 THEN
      v_x := -1;
   END IF;
END;

CREATE PROC proc1 @basesal money, @empid int
AS 
BEGIN
IF (select sal from emp where   empid = @empid) < @basesal
     UPDATE emp
     SET sal_flag = -1
     WHERE empid = @empid
END

CREATE OR REPLACE PROCEDURE proc1
(
  v_basesal IN NUMBER,
  v_empid IN NUMBER
)
AS
   v_temp NUMBER(1, 0) := 0;
BEGIN
   SELECT 1 INTO v_temp
      FROM DUAL
      WHERE ( SELECT sal
              FROM emp 
                WHERE empid = v_empid ) < v_basesal;
      
   IF v_temp = 1 THEN
      UPDATE emp
         SET sal_flag = -1
         WHERE empid = v_empid;
   END IF;
END;

Comments

IF statements in Microsoft SQL Server or Sybase Adaptive Server and Oracle are nearly the same except in the following two cases:

• If EXISTS(...) in Microsoft SQL Server or Sybase Adaptive Server does not have an equivalent PL/SQL construct. Therefore, it is converted to a SELECT INTO WHERE EXISTS clause and an IF statement as shown in Example 3 in Table 3-19.

• IF (SELECT... ) with comparison does not have an equivalent PL/SQL construct. Therefore, it is converted to a SELECT INTO...WHERE... clause, as shown in Example 4 in Table 3-19.

3.5.5 RETURN Statement

CREATE PROC proc1
@x int
AS 
IF @x = -1
        RETURN 25022
ELSE
        RETURN 25011

CREATE OR REPLACE FUNCTION proc1
(
  v_x IN NUMBER
)
AS
BEGIN
   IF v_x = -1 THEN
      RETURN 25022;
   ELSE
      RETURN 25011;
   END IF;
END;

Comments

A RETURN statement is used to return a single value back to the calling program and works the same in both databases. Microsoft SQL Server or Sybase Adaptive Server can return only the numeric data type, while Oracle can return any of the server data types or the PL/SQL data types.

In a PL/SQL procedure, a RETURN statement can only return the control back to the calling program without returning any data. SQL Developer translates procedures returning values to functions automatically.

3.5.6 RAISERROR Statement

 CREATE PROC proc1
 AS 
      RAISERROR 12345 "No Employees found"

CREATE OR REPLACE PROCEDURE PROC1
AS
BEGIN
  raise_application_error(-20999, 'No Employees found');
END PROC1;

Comments

Microsoft SQL Server or Sybase Adaptive Server uses RAISERROR to notify the client program of any error that occurred. This statement does not end the execution of the procedure, and the control is passed to the next statement.

PL/SQL provides similar functionality with RAISE_APPLICATION_ERROR statements.However, it ends the execution of the stored subprogram and returns the control to the calling program. It is equivalent to a combination of RAISERROR and a RETURN statement.

3.5.7 EXECUTE Statement

 CREATE PROC proc1
 AS 
EXEC SetExistFlag
EXEC SetExistFlag yes=@yes, @Status OUT
EXEC @Status = RecordExists
EXEC SetExistFlag @yes

CREATE OR REPLACE PROCEDURE proc1
 AS
 BEGIN          
  SetExistFlag;
  SetExistFlag(yes=>v_yes,Status);
  Status:=RecordExists;
  SetExistFlag(v_yes);
END proc1;

Comments

The EXECUTE statement is used to execute another stored procedure from within a procedure. In PL/SQL, the procedure is called by its name within the PL/SQL block.

SQL Developer converts the parameter-calling convention to be either positional, named, or mixed. For information on parameter-calling conventions, see "Schema Objects".

3.5.8 WHILE Statement

CREATE PROC proc1
 @i int
 AS 
         WHILE @i > 0

         BEGIN
          print 'Looping inside WHILE....'
          SELECT @i = @i + 1
         END

CREATE OR REPLACE PROCEDURE proc1
(
  iv_i IN NUMBER
)
AS
   v_i NUMBER(10,0) :=iv_i;
BEGIN
   WHILE v_i > 0 
   LOOP 
      BEGIN
         DBMS_OUTPUT.PUT_LINE('Looping inside WHILE....');
         v_i := v_i + 1;
      END;
   END LOOP;
END;

CREATE PROC proc1 
 @i int,
 @y int
 AS
         WHILE @i > 0
         BEGIN
          print 'Looping inside WHILE....'
          SELECT @i = @i + 1
         END

CREATE OR REPLACE PROCEDURE proc1
(
  iv_i IN NUMBER,
  v_y IN NUMBER
)
AS
   v_i NUMBER(10,0):=iv_i;
BEGIN
   WHILE v_i > 0 
   LOOP 
      BEGIN
         DBMS_OUTPUT.PUT_LINE('Looping inside WHILE....');
         v_i := v_i + 1;
      END;
   END LOOP;
END;

CREATE PROC proc1
 AS
 DECLARE @sal money
 SELECT @sal = 0 
 WHILE EXISTS(SELECT * FROM emp where sal < @sal )
 BEGIN
  SELECT @sal = @sal + 99

  DELETE emp
  WHERE sal < @sal
 END
 GO

CREATE OR REPLACE PROCEDURE proc1
AS
   v_sal NUMBER(19,2);
   v_temp NUMBER(1, 0) := 0;
BEGIN
   v_sal := 0;
   LOOP
      v_temp := 0;
      SELECT 1 INTO v_temp
         FROM DUAL
         WHERE EXISTS ( SELECT *
                        FROM emp 
                          WHERE sal < v_sal );
      IF v_temp != 1 THEN
         EXIT;
      END IF;
      BEGIN
         v_sal := v_sal + 99;
         DELETE emp
          WHERE sal < v_sal;
      END;
   END LOOP;
END;

CREATE PROC proc1
 AS

 DECLARE @sal money

 WHILE (SELECT count (*) FROM emp ) > 0
 BEGIN
 SELECT @sal = max(sal) from emp
  WHERE stat = 1

   DELETE emp
  WHERE sal < @sal
 END
 GO

CREATE OR REPLACE PROCEDURE proc1
AS
   v_sal NUMBER(19,2);
   v_temp NUMBER(1, 0) := 0;
BEGIN
   LOOP
      v_temp := 0;
      SELECT 1 INTO v_temp
         FROM DUAL
         WHERE ( SELECT COUNT(*)
                 FROM emp  ) > 0;
      IF v_temp != 1 THEN
         EXIT;
      END IF;
      BEGIN
         SELECT MAX(sal)
           INTO v_sal
           FROM emp 
           WHERE stat = 1;
         DELETE emp
           WHERE sal < v_sal;
      END;
   END LOOP;
END;

Comments

SQL Developer can convert most WHILE constructs. However, the CONTINUE within a WHILE loop in Microsoft SQL Server or Sybase Adaptive Server does not have a direct equivalent in PL/SQL. It is simulated using the GOTO statement with a label.

3.5.9 GOTO Statement

 CREATE PROC proc1 @Status int
 AS 
 DECLARE @j int
         IF @Status = -1
                 GOTO Error

         SELECT @j = -1
 Error:
         SELECT @j = -99

CREATE OR REPLACE PROCEDURE proc1
(
  v_Status IN NUMBER
)
AS
   v_j NUMBER(10,0);
BEGIN
   IF v_Status = -1 THEN
      GOTO Error;
   END IF;
   v_j := -1;
   <<Error>>
   v_j := -99;
END;

Comments

The GOTO <label> statement is converted automatically. No manual changes are required.

3.5.10 @@Rowcount and @@Error Variables

CREATE PROC proc1
 AS 
   DECLARE @x int
   SELECT @x=count(*) FROM emp
    IF @@rowcount = 0
        print 'No rows found.'
         IF @@error = 0
         print 'No errors.'

CREATE OR REPLACE PROCEDURE proc1
AS
   v_sys_error NUMBER := 0;
   v_x NUMBER(10,0);
BEGIN
   BEGIN
      SELECT COUNT(*)
        INTO v_x
        FROM emp ;
   EXCEPTION
      WHEN OTHERS THEN
         v_sys_error := SQLCODE;
   END;
   IF SQL%ROWCOUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('No rows found.');
   END IF;
   IF v_sys_error = 0 THEN
      DBMS_OUTPUT.PUT_LINE('No errors.');
   END IF;
END;

Comments

@@rowcount is converted to the PL/SQL cursor attribute SQL%ROWCOUNT.

@@error is converted to v_sys_error, which contains the value returned by the SQLCODE function. The value returned by SQLCODE should only be assigned within an exception block; otherwise, it returns a value of zero.

3.5.11 ASSIGNMENT Statement

CREATE PROC proc1
AS 
DECLARE @x int
   SELECT @x = -1
   SELECT @x=sum(salary) FROM employee

CREATE OR REPLACE PROCEDURE proc1
AS
   v_x NUMBER(10,0);
BEGIN
   v_x := -1;
   SELECT SUM(salary)
     INTO v_x
     FROM employee ;
END;

Comments

Assignment in Microsoft SQL Server or Sybase Adaptive Server is done using the SELECT statement, as illustrated in Table 3-26.

PL/SQL assigns values to a variable as follows:

It uses the assignment statement to assign the value of a variable or an expression to a local variable. It assigns a value from a database using the SELECT..INTO clause. This requires that the SQL returns only one row.

3.5.12 SELECT Statement

CREATE PROC proc1
AS
SELECT ename FROM employee

CREATE OR REPLACE PROCEDURE proc1
(
  cv_1 IN OUT SYS_REFCURSOR
)
AS
BEGIN
   OPEN cv_1 FOR
      SELECT ename
        FROM employee ;
END;

CREATE PROC proc1
AS 
DECLARE @name char(20)
  SELECT @id = id 
  FROM employee
  
  RETURN id

CREATE OR REPLACE FUNCTION proc1
AS
   v_name CHAR(20);
BEGIN
   SELECT id
     INTO v_id
     FROM employee ;
   RETURN v_id;
END;

Comments

Because of the differences in their architectures, Microsoft SQL Server or Sybase Adaptive Server stored procedures return data to the client program in a different way than Oracle.

Microsoft SQL Server or Sybase Adaptive Server and Oracle can all pass data to the client using output parameters in the stored procedures. Microsoft SQL Server or Sybase Adaptive Server 6.5 uses another method known as result sets to transfer the data from the server to client.

3.5.13 SELECT Statement with GROUP BY Clause

CREATE PROC proc1
AS
DECLARE @ename char(20)
DECLARE @salary int
SELECT @ename=ename, @salary=salary FROM emp
WHERE salary > 100000
GROUP BY deptno

CREATE OR REPLACE PROCEDURE proc1
AS
   v_ename CHAR(20);
   v_salary NUMBER(10,0);
BEGIN
   SELECT ename,
          salary
     INTO v_ename,
          v_salary
     FROM emp 
     WHERE salary > 100000
     GROUP BY deptno;
END;

Comments

T/SQL allows GROUP BY statements where the column used in the GROUP BY clause does not need to be part of the SELECT list. PL/SQL does not allow this type of GROUP BY clause.

SQL Developer converts this type of SELECT statement to PL/SQL. However, the equivalent PL/SQL statement returns an error in Oracle.

3.5.14 Column Aliases

CREATE PROC proc1
@Status int=0
AS 
        SELECT x=sum(salary) FROM employee

CREATE OR REPLACE PROCEDURE proc1
(
  v_Status IN NUMBER DEFAULT 0,
  cv_1 IN OUT SYS_REFCURSOR
)
AS
BEGIN
   OPEN cv_1 FOR
      SELECT SUM(salary) x
        FROM employee ;
END;

Comments

SQL Developer can convert Microsoft SQL Server or Sybase Adaptive Server-specific column aliases to the equivalent Oracle format. No manual changes are required.

3.5.15 UPDATE with FROM Statement

CREATE PROC proc1
AS 
        UPDATE table1
        SET col1 = 1
        FROM table1, table2
        WHERE table1.id = table2.id

CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
   UPDATE table1
      SET ( col1 ) = ( SELECT 1
     FROM table1 ,
          table2 
     WHERE table1.id = table2.id );
END;

3.5.16 DELETE with FROM Statement

CREATE PROC proc1
AS 
        DELETE FROM table1
        FROM table1, table2
        WHERE table1.id = table2.id 

CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
DELETE FROM table1
    WHERE ROWID IN
      (SELECT table1.ROWID
      FROM table1, table2
      WHERE table1.id = table2.id);
END;

3.5.17 Temporary Tables

CREATE PROC proc1
AS 
        SELECT col1, col2
        INTO #Tab
        FROM table1
        WHERE table1.id = 100

CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
   DELETE FROM tt_Tab;
   
   INSERT INTO tt_Tab ( 
      SELECT col1,
             col2
        FROM table1 
        WHERE table1.id = 100 );
END;

Comments

Temporary tables are supported by Oracle, and SQL Developer uses this feature. The DDL of the temporary table is extracted and generated as a standalone object.

3.5.18 Cursor Handling

CREATE PROC cursor_demo
AS 
DECLARE @empno   INT
DECLARE @ename   CHAR(100)
DECLARE @sal     FLOAT
DECLARE cursor_1 CURSOR
FOR SELECT empno, ename, sal FROM emp

OPEN cursor_1

FETCH cursor_1 INTO @empno, @ename, @sal

CLOSE cursor_1

DEALLOCATE CURSOR cursor_1 

CREATE OR REPLACE PROCEDURE cursor_demo
AS
   CURSOR cursor_1
     IS SELECT empno,
   ename,
   sal
     FROM emp ;
   v_empno NUMBER(10,0);
   v_ename CHAR(100);
   v_sal NUMBER;
BEGIN
   OPEN cursor_1;
   FETCH cursor_1 INTO v_empno,v_ename,v_sal;
   CLOSE cursor_1;
END;

Comments

Microsoft SQL Server and Sybase Adaptive Server introduced cursors in T/SQL. Syntactical conversion of cursors from Microsoft SQL Server or Sybase Adaptive Server to Oracle is straightforward.

3.6.1 Transaction Handling Semantics

Microsoft SQL Server or Sybase Adaptive Server

Microsoft SQL Server or Sybase Adaptive Server offers two different transaction models: the ANSI-standard implicit transaction model and the explicit transaction model.

Microsoft SQL Server or Sybase Adaptive Server provides options to support ANSI-standard transactions. These options can be set or un-set using the SET command.

The following SET command sets the implicit transaction mode:

set chained on

The following SET command sets the isolation level to the desired level:

set transaction isolation level {1|3}

isolation level 1 prevents dirty reads. Isolation level 2 prevents un-repeatable reads. Isolation level 3 prevents phantoms. Isolation level 3 is required by ANSI standards. For Microsoft SQL Server or Sybase Adaptive Server, the default is isolation level 1.

To implement isolation level 3, Microsoft SQL Server or Sybase Adaptive Server applies HOLDLOCK to all the tables taking part in the transaction. In Microsoft SQL Server or Sybase Adaptive Server, HOLDLOCK, along with page-level locks, can block users for a considerable length of time, causing poor response time.

If the Microsoft SQL Server or Sybase Adaptive Server application implements ANSI-standard chained (implicit) transactions with isolation level 3, the application migrates smoothly to Oracle because Oracle implements the ANSI-standard implicit transaction model, which ensures repeatable reads.

In a non-ANSI standard application, Microsoft SQL Server or Sybase Adaptive Server transactions are explicit. A logical transaction has to be explicitly started with the statement BEGIN TRANSACTION. The transaction is committed with a COMMIT TRANSACTION or rolled back with a ROLLBACK TRANSACTION statement. The transactions can be named. For example, the following statement starts a transaction named

account_tran.
BEGIN TRANSACTION account_tran

The explicit transaction mode allows nested transactions. However, the nesting is only syntactical. Only outermost BEGIN TRANSACTION and COMMIT TRANSACTION statements actually create and commit the transaction. This could be confusing as the inner COMMIT TRANSACTION does not actually commit.

The following example illustrates the nested transactions:

BEGIN TRANSACTION
        /* T/SQL  Statements */
        BEGIN TRANSACTION
        /* T/SQL  Statements */
                BEGIN TRANSACTION account_tran
                /* T/SQL  Statements */
                IF SUCCESS
                        COMMIT TRANSACTION account_tran
                ELSE
                        ROLLBACK TRANSACTION account_tran
                END IF
        /* T/SQL  Statements */
        IF SUCCESS
                COMMIT TRANSACTION
        ELSE
                ROLLBACK TRANSACTION
        END IF
        /* T/SQL  Statements */
COMMIT TRANSACTION

When BEGIN TRANSACTION and COMMIT TRANSACTION statements are nested, the outermost pair creates and commits the transaction while the inner pairs only keep track of nesting levels. The transaction is not committed until the outermost COMMIT TRANSACTION statement is executed. Normally the nesting of the transaction occurs when stored procedures containing BEGIN TRANSACTION /COMMIT TRANSACTION statements call other procedures with transaction-handling statements. The global variable @@trancount keeps track of the number of currently active transactions for the current user. If you have more than one open transaction, you need to ROLLBACK, then COMMIT.

The named and unnamed inner COMMIT TRANSACTION statements have no effect. The inner ROLLBACK TRANSACTION statements without the name roll back the statements to the outermost BEGIN TRANSACTION statement and the current transaction is canceled. The named inner ROLLBACK TRANSACTION statements cancel the respective named transactions.

Oracle

Oracle applies ANSI-standard implicit transaction methods. A logical transaction begins with the first executable SQL statement after a COMMIT, ROLLBACK, or connection to the database. A transaction ends with a COMMIT, ROLLBACK, or disconnection from the database. An implicit COMMIT statement is issued before and after each DDL statement. The implicit transaction model prevents artificial nesting of transactions because only one logical transaction per session can be in effect. The user can set SAVEPOINT in a transaction and roll back a partial transaction to the SAVEPOINT.

For example:

UPDATE test_table SET col1='value_1';
SAVEPOINT first_sp;
UPDATE test_table SET col1='value_2';
ROLLBACK TO SAVEPOINT first_sp;
COMMIT;   /* col1 is 'value_1'*/

BEGIN TRAN

BEGIN TRAN tran_1

 SAVEPOINT tran_1

COMMIT TRAN

 COMMIT

COMMIT TRAN

COMMIT TRAN tran_1

 COMMIT

COMMIT TRAN tran_1

ROLLBACK TRAN

ROLLBACK

ROLLBACK TRAN tran_1

ROLLBACK TO SAVEPOINT tran_1

BEGIN TRAN

/*BEGIN TRAN  >>> statement ignored <<<*/

BEGIN TRAN tran_1

 SAVEPOINT tran_1;

COMMIT TRAN 

 COMMIT WORK;

COMMIT TRAN

 COMMIT WORK;

COMMIT TRAN tran_1

 COMMIT WORK;

COMMIT TRAN tran_1

 COMMIT WORK;

ROLLBACK TRAN

 ROLLBACK WORK;

ROLLBACK TRAN tran_1

 ROLLBACK TO SAVEPOINT tran_1

SAVE TRAN tran_1 

SAVEPOINT tran_1

3.6.2 Exception-Handling and Error-Handling Semantics

Microsoft SQL Server or Sybase Adaptive Server

In Microsoft SQL Server or Sybase Adaptive Server, you must check for errors after each SQL statement because control is passed to the next statement regardless of any error conditions generated by the previous statement. The client ERROR_HANDLER routine is invoked as a call-back routine if any server error occurs, and the error conditions can be handled in the call-back routine.

Stored procedures use the RAISERROR statement to notify the client of any error condition. This statement does not cause the control to return to the calling routine.

Microsoft SQL Server or Sybase Adaptive Server allows you to customize the error messages using a system table. The system procedures allow the user to add error messages to this table.

Oracle

In Oracle, each SQL statement is automatically checked for errors before proceeding with the next statement. If an error occurs, control immediately jumps to an exception handler if one exists. This frees you from needing to check the status of every SQL statement. For example, if a SELECT statement does not find any row in the database, an exception is raised. The corresponding exception handler part of the block should include the code to deal with this error. The built-in RAISE_APPLICATION_ERROR procedure notifies the client of the server error condition and returns immediately to the calling routine.

Oracle places an implicit SAVEPOINT at the beginning of a procedure. The built-in RAISE_APPLICATION_ERROR procedure rolls back to this SAVEPOINT or the last committed transaction within the procedure. The control is returned to the calling routine.

The Oracle RAISE_APPLICATION_ERROR statement allows the user to customize the error message. If an exception is raised, SQLCODE is returned automatically by PL/SQL to the caller. It keeps propagating until it is handled.

Recommendations

To simulate Microsoft SQL Server or Sybase Adaptive Server behavior in Oracle, you must enclose each SQL statement in an equivalent PL/SQL block. This block must deal with the exceptions that need to be trapped for the SQL statement.

See "T/SQL Versus PL/SQL Constructs" for more information about the extra code required to simulate Microsoft SQL Server or Sybase Adaptive Server behavior.

If the RAISERROR statement in a Microsoft SQL Server or Sybase Adaptive Server stored procedure is immediately followed by the RETURN statement, these two statements can be converted to the Oracle RAISE_APPLICATION_ERROR statement.

You can customize error messages with the help of a user-defined table. You can write standard routines to add and retrieve error messages to this table. This method serves a two-fold purpose: it ensures that the system is portable, and it gives the administrator centralized control over the error messages.

3.6.3 Special Global Variables

Microsoft SQL Server or Sybase Adaptive Server

In Microsoft SQL Server or Sybase Adaptive Server, the following global variables are particularly useful in the conversion process:

@@error: 

The server error code indicating the execution status of the most recently executed T/SQL statement. For code examples, see "@@Rowcount and @@Error Variables".

@@identity:

Returns the last identity value generated by the statement. It does not revert to a previous setting due to ROLLBACKS or other transactions.

@@rowcount:

The number of rows affected by the most recently executed T/SQL statement. For code examples, see "@@Rowcount and @@Error Variables".

@@servername:

The name of the local Microsoft SQL Server or Sybase Adaptive Server server.

@@sqlstatus:

The status information resulting from the last FETCH statements.

@@tranchained: 

The current transaction mode of the T/SQL procedure. If @@tranchained returns 1, the TL/SQL procedure is in chained, or implicit transaction mode.

@@trancount: 

Keeps track of the nesting level for the nested transactions for the current user.

@@transtate: 

The current state of the transaction.

Oracle

SQLCODE:

The server error code indicating the execution status of the most recently executed PL/SQL statement.

SQL%ROWCOUNT:

The variable attached to the implicit cursor associated with each SQL statement executed from within the PL/SQL procedures. This variable contains the number of rows affected by the execution of the SQL statement attached to the implicit cursor.

Recommendations:

The @@error variable has a direct equivalent in Oracle, and that is the SQLCODE function. The SQLCODE function returns the server error code.

The SQL%ROWCOUNT variable in Oracle is functionally equivalent to @@rowcount.

There are many more special global variables available with PL/SQL. Not all those variables are listed here. There are more special global variables available in T/SQL also. Not all those variables are listed here because they do not play a major role in the conversion process.

3.6.4 Operators

See "Data Manipulation Language" for a discussion of Microsoft SQL Server or Sybase Adaptive Server and Oracle operators.

3.6.5 Built-in Functions

See "Data Manipulation Language" for a discussion of built-in functions in Microsoft SQL Server or Sybase Adaptive Server and Oracle.

3.6.6 DDL Constructs within Microsoft SQL Server or Sybase Adaptive Server Stored Procedures

Microsoft SQL Server or Sybase Adaptive Server allows DDL constructs to be part of the stored procedures. Oracle allows DDL statements as part of the dynamic SQL. Oracle issues an implicit COMMIT statement after each DDL statement.

SQL Developer currently converts DDL constructs within stored procedures into standalone DDL constructs. The DDL is removed from the body of the stored procedure and is created before the procedure. Support for dynamic SQL is planned for a future release.