Control-of-Flow Language

The control-of-flow language controls the flow of execution of SQL statements, statement blocks, and stored procedures. PL/SQL and Transact-SQL provide many of the same constructs, although there are some syntax differences.

Keywords

These are the keywords supported by each RDBMS.

Statement	Oracle PL/SQL	Microsoft SQL Server Transact-SQL
Declare variables	DECLARE	DECLARE
Statement block	BEGIN...END;	BEGIN...END
Conditional processing	IF…THEN, ELSIF…THEN, ELSE ENDIF;	IF…[BEGIN…END] ELSE <condition> [BEGIN…END] ELSE IF <condition> CASE expression
Unconditional exit	RETURN	RETURN
Unconditional exit to the statement following the end of the current program block	EXIT	BREAK
Restarts a WHILE loop	N/A	CONTINUE
Wait for a specified interval	N/A (dbms_lock.sleep)	WAITFOR
Loop control	WHILE LOOP…END LOOP; LABEL…GOTO LABEL; FOR…END LOOP; LOOP…END LOOP;	WHILE <condition> BEGIN… END LABEL…GOTO LABEL
Program comments	/* … */, --	/* … */, --
Print output	RDBMS_OUTPUT.PUT_ LINE	PRINT
Raise program error	RAISE_APPLICATION_ ERROR	RAISERROR
Execute program	EXECUTE	EXECUTE
Statement terminator	Semicolon (;)	N/A

Declaring Variables

Transact-SQL and PL/SQL variables are created with the DECLARE keyword. Transact-SQL variables are identified with @) and, like PL/SQL variables, are initialized to a null value when they are first created.

Oracle	Microsoft SQL Server
DECLARE VSSN CHAR(9); VFNAME VARCHAR2(12); VLNAME VARCHAR2(20); VBIRTH_DATE DATE; VLOAN_AMOUNT NUMBER(12,2);	DECLARE @VSSN CHAR(9), @VFNAME VARCHAR2(12), @VLNAME VARCHAR2(20), @VBIRTH_DATE DATETIME, @VLOAN_AMOUNT NUMERIC(12,2)

Transact-SQL does not support the %TYPE and %ROWTYPE variable data type definitions. A Transact-SQL variable cannot be initialized in the DECLARE command. The Oracle NOT NULL and CONSTANT keywords cannot be used in Microsoft SQL Server data type definitions.

Like Oracle LONG and LONG RAW data types, text and image data types cannot be used for variable declarations. Additionally, the PL/SQL style record and table definitions are not supported.

Assigning Variables

Oracle and Microsoft SQL Server offer these ways to assign values to local variables.

Oracle	Microsoft SQL Server
Assignment operator (:=)	SET @local_variable = value
SELECT...INTO syntax for selecting column values from a single row	SELECT @local_variable = expression [FROM…] for assigning a literal value, an expression involving other local variables, or a column value from a single row
FETCH…INTO syntax	FETCH…INTO syntax

Here are some syntax examples.

Oracle	Microsoft SQL Server
DECLARE VSSN CHAR(9); VFNAME VARCHAR2(12); VLNAME VARCHAR2(20); BEGIN VSSN := '123448887'; SELECT FNAME, LNAME INTO VFNAME, VLNAME FROM STUDENTS WHERE SSN=VSSN; END;	DECLARE @VSSN CHAR(9), @VFNAME VARCHAR(12), @VLNAME VARCHAR(20) SET @VSSN = '12355887' SELECT @VFNAME=FNAME, @VLNAME=LNAME FROM STUDENTS WHERE SSN = @VSSN

Statement Blocks

Oracle PL/SQL and Microsoft SQL Server Transact-SQL support the use of BEGIN…END terminology to specify statement blocks. Transact-SQL does not require the use of a statement block following the DECLARE statement. The BEGIN…END statement blocks are required in Microsoft SQL Server for IF statements and WHILE loops if more than one statement is executed.

Oracle	Microsoft SQL Server
DECLARE DECLARE VARIABLES ... BEGIN -- THIS IS REQUIRED SYNTAX PROGRAM_STATEMENTS ... IF ...THEN STATEMENT1; STATEMENT2; STATEMENTN; END IF; WHILE ... LOOP STATEMENT1; STATEMENT2; STATEMENTN; END LOOP; END; -- THIS IS REQUIRED SYNTAX	DECLARE DECLARE VARIABLES ... BEGIN -- THIS IS OPTIONAL SYNTAX PROGRAM_STATEMENTS ... IF ... BEGIN STATEMENT1 STATEMENT2 STATEMENTN END WHILE ... BEGIN STATEMENT1 STATEMENT2 STATEMENTN END END -- THIS IS REQUIRED SYNTAX

Conditional Processing

The Microsoft SQL Server Transact-SQL conditional statement includes IF and ELSE rather than the ELSIF statement in Oracle PL/SQL. Multiple IF statements can be nested to achieve the same effect. For extensive conditional tests, the CASE expression may be easier to read.

Oracle	Microsoft SQL Server
DECLARE VDEGREE_PROGRAM CHAR(1); VDEGREE_PROGRAM_NAME VARCHAR2(20); BEGIN VDEGREE_PROGRAM := 'U'; IF VDEGREE_PROGRAM = 'U' THEN VDEGREE_PROGRAM_NAME := 'Undergraduate'; ELSIF VDEGREE_PROGRAM = 'M' THEN VDEGREE_PROGRAM_ NAME := 'Masters'; ELSIF VDEGREE_PROGRAM = 'P' THEN VDEGREE_PROGRAM_ NAME := 'PhD'; ELSE VDEGREE_PROGRAM_ NAME := 'Unknown'; END IF; END;	DECLARE @VDEGREE_PROGRAM CHAR(1), @VDEGREE_PROGRAM_NAME VARCHAR(20) SELECT @VDEGREE_PROGRAM = 'U' SELECT @VDEGREE_PROGRAM_ NAME = CASE @VDEGREE_PROGRAM WHEN 'U' THEN 'Undergraduate' WHEN 'M' THEN 'Masters' WHEN 'P' THEN 'PhD'. ELSE 'Unknown' END

Oracle

Microsoft SQL Server

DECLARE
VDEGREE_PROGRAM CHAR(1);
VDEGREE_PROGRAM_NAME VARCHAR2(20);
BEGIN
    VDEGREE_PROGRAM := 'U';
    IF VDEGREE_PROGRAM = 'U' THEN
        VDEGREE_PROGRAM_NAME :=
            'Undergraduate';
    ELSIF VDEGREE_PROGRAM = 'M'         THEN VDEGREE_PROGRAM_
          NAME := 'Masters';
    ELSIF VDEGREE_PROGRAM = 'P'            THEN     VDEGREE_PROGRAM_
             NAME := 'PhD';
    ELSE VDEGREE_PROGRAM_
             NAME := 'Unknown';
    END IF;
END;

DECLARE
@VDEGREE_PROGRAM CHAR(1),
@VDEGREE_PROGRAM_NAME VARCHAR(20)
SELECT @VDEGREE_PROGRAM = 'U'
SELECT @VDEGREE_PROGRAM_
NAME = CASE @VDEGREE_PROGRAM
WHEN 'U' THEN 'Undergraduate'
WHEN 'M' THEN 'Masters'
WHEN 'P' THEN 'PhD'.
ELSE 'Unknown'
END

Repeated Statement Execution (Looping)

Oracle PL/SQL provides the unconditional LOOP and FOR LOOP. Transact-SQL offers the WHILE loop and the GOTO statement for looping purposes.

WHILE Boolean_expression

    {sql_statement | statement_block}

    [BREAK] [CONTINUE]

The WHILE loop tests a Boolean expression for the repeated execution of one or more statements. The statement(s) are executed repeatedly as long as the specified expression evaluates to TRUE. If multiple statements are to be executed, they must be placed within a BEGIN…END block.

Oracle	Microsoft SQL Server
DECLARE COUNTER NUMBER; BEGIN COUNTER := 0 WHILE (COUNTER <5) LOOP COUNTER := COUNTER + 1; END LOOP; END;	DECLARE @COUNTER NUMERIC SELECT@COUNTER = 1 WHILE (@COUNTER <5) BEGIN SELECT @COUNTER = @COUNTER +1 END

Statement execution can be controlled from inside the loop with the BREAK and CONTINUE keywords. The BREAK keyword causes an unconditional exit from the WHILE loop, and the CONTINUE keyword causes the WHILE loop to restart, skipping any statements that follow. The BREAK keyword is equivalent to the Oracle PL/SQL EXIT keyword. Oracle does not have an equivalent to CONTINUE.

GOTO Statement

Both Oracle and Microsoft SQL Server have GOTO statements, with different syntax. The GOTO statement causes the execution of a Transact-SQL batch to jump to a label. None of the statements between the GOTO statement and the label are executed.

Oracle	Microsoft SQL Server
GOTO label; <<label name here>>	GOTO label

PRINT Statement

The Transact-SQL PRINT statement performs the same operation as the PL/SQL RDBMS_OUTPUT.put_line procedure. It is used for printing user-specified messages.

The message limit for the PRINT statement is 8,000 characters. Variables that are defined using the char or varchar data type can be embedded in the printed statement. If any other data type is used, the CONVERT or CAST function must be used. Local variables, global variables, and text can be printed. Both single and double quotation marks can be used to enclose text.

Returning from Stored Procedures

Both Microsoft SQL Server and Oracle have RETURN statements. RETURN lets your program exit unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements following RETURN are not executed.

Oracle	Microsoft SQL Server
RETURN expression:	RETURN [integer_expression]

Raising Program Errors

The Transact-SQL RAISERROR statement returns a user-defined error message and sets a system flag to record that an error has occurred. It is similar in function to the PL/SQL raise_application_error exception handler.

The RAISERROR statement allows the client to retrieve an entry from the sysmessages table or build a message dynamically with user-specified severity and state information. When defined, this message is sent back to the client as a server error message.

RAISERROR ({msg_id | msg_str}, severity, state

    [, argument1 [, argument2]])

    [WITH options]

When converting your PL/SQL programs, it may not be necessary to use the RAISERROR statement. In the following code example, the PL/SQL program uses the raise_application_error exception handler, while the Transact-SQL program uses nothing. The raise_application_error exception handler has been included to prevent the PL/SQL program from possibly returning an ambiguous unhandled exception error message. Instead, it always returns the Oracle error message (SQLERRM) when an unanticipated problem occurs.

When a Transact-SQL program fails, it always returns a detailed error message to the client program. Therefore, unless some specialized error handling is required, the RAISERROR statement is not always needed.

Oracle	Microsoft SQL Server
CREATE OR REPLACE FUNCTION DEPT_ADMIN.DELETE_DEPT (VDEPT IN VARCHAR2) RETURN NUMBER AS BEGIN DELETE FROM DEPT_ADMIN.DEPT WHERE DEPT = VDEPT; RETURN(SQL%ROWCOUNT); EXCEPTION WHEN OTHER THEN RAISE_APPLICATION_ERROR (-20001,SQLERRM); END DELETE_DEPT; /	CREATE PROCEDURE DEPT_ADMIN.DELETE_DEPT @VDEPT VARCHAR(4) AS DELETE FROM DEPT_DB.DBO.DEPT WHERE DEPT = @VDEPT RETURN @@ROWCOUNT GO