Control-of-Flow Language

The control-of-flow language is quite similar between PL/SQL and Transact-SQL. Regardless of DBMS, the control-of-flow language controls the flow of execution of SQL statements, statement blocks, and stored procedures.

Keywords

These are the keywords supported by each DBMS.

Statement	PL/SQL	Transact-SQL
Declare variables	DECLARE	DECLARE
Statement block	BEGIN...END;	BEGIN...END
Conditional processing	IF…THEN, ELSIF…THEN, ELSE ENDIF;	IF…[BEGIN…END] ELSE [BEGIN…END] CASE expression
Unconditional exit	RETURN	RETURN
Wait for a specified interval	N/A	WAITFOR
Loop control	WHILE…END LOOP; LABEL…GOTO LABEL; FOR…END LOOP; LOOP…END LOOP;	WHILE… LABEL…GOTO LABEL
Program comments	/* … */, --	/* … */, --
Print output	DBMS_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 using the DECLARE keyword. Transact-SQL variables must be identified with the at sign (@) and, like PL/SQL variables, are initialized to a null value when they are first created.

Oracle	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 statement. The NOT NULL and CONSTANT keywords cannot be used.

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 offers three ways to assign values to local variables:

The assignment operator (:=)
The SELECT...INTO syntax for selecting column values from a single row
The FETCH…INTO syntax for retrieving column values a row at a time from a cursor

SQL Server, in contrast, offers two ways to assign values:

SELECT @var=<expression> [FROM…] for assigning a literal value, an expression involving other local variables, or a column value from a single row
FETCH…INTO for retrieving column values a row at a time from a cursor

Declaring and assigning local variables in Oracle and SQL Server looks like this.

Oracle	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) SELECT @VSSN = '12355887' SELECT @VFNAME=FNAME, @VLNAME=LNAME FROM STUDENTS WHERE SSN = @VSSN

SQL Server does not offer a constant declaration, nor can you assign variables in the DECLARE statement.

Statement Blocks

PL/SQL and Transact-SQL support the use of the 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 for IF statements and WHILE loops if more than one statement is executed.

Oracle	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 Transact-SQL conditional statement includes the IF and ELSE terminology. There is no ELSIF as in PL/SQL. However, multiple IF statements can be embedded within each other to achieve the same effect. For extensive conditional tests, it is recommend that you use the CASE statement.

Oracle

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)

Transact-SQL offers the WHILE loop. The PL/SQL unconditional LOOP and FOR LOOP statements are not supported. The GOTO statement can also be used 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	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. The CONTINUE keyword causes the WHILE loop to restart, skipping any statements that follow.

PRINT Statement

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

The message limit for the PRINT statement is 255 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 function must be used. Local variables, global variables, and text can be printed. Both single and double quotes can be used to enclose text.

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 very similar in function to the PL/SQL raise_application_error exception handler.

RAISERROR ({msg_id | msg_str}, severity, state
   [, argument1 [, argument2]])
   [WITH options]

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. Once defined, this message is sent back to the client as a server error message.

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) whenever 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	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