Declare variables and assign values

Declaring variables and assigning values to the variables is done quite differently in SQL Server and Oracle as explained below

 

Oracle

 

Declaring the variable:

 

Syntax:

            DECLARE VARIABLE1 DATATYPE;

                          VARIABLE2 DATATYPE;

 

Example:

            DECLARE OUTPUTER VARCHAR (200);

         INPUTER INT;

 

As we can see compared to SQL Server, Oracle does not need to have @ in front of the variables and uses “;” as separator between variables.

 

Assigning/Set the variable value:

 

In oracle we cannot directly set the value to a variable, we can only assign a value to a variable between the Begin and End blocks.

Assigning the values to variables can be done as direct input (:=) or using select into clause.

 

Example:

            DECLARE OUTPUTER1 VARCHAR (200);

                            OUTPUTER2 VARCHAR (200);

                            --assignment during declaration  

                            OUTPUTER3 VARCHAR (200):='SAM';

            BEGIN

                        --direct assignment

OUTPUTER1:='OUTPUT SAM FROM OUTPUTER1';

--select into assignment

                        SELECT 'OUTPUT SAM FROM OUTPUTER2' INTO OUTPUTER2 FROM DUAL;

                         DBMS_OUTPUT.PUT_LINE (OUTPUTER1);

                         DBMS_OUTPUT.PUT_LINE (OUTPUTER2);

                         DBMS_OUTPUT.PUT_LINE (OUTPUTER3);

            END;

 

Note: If you are using the SQL Developer, to see the output go to “View”à”Dbms Output” then a new windows opens in the browser, click “+” and connect to the schema where the above statement is executed.

 

Declaring the internal variable inside the stored procedure or Function is done in between the IS and Begin statements

 

Example:

 

Procedure

            CREATE PROCEDURE SP (PARAM1 OUT VARCHAR)

--internal variables below

IS OUTPUTER1 VARCHAR (200);

                   OUTPUTER2 VARCHAR (200);

            BEGIN

            --procedure statements

            END

 

Function

CREATE FUNCTION FN (PARAM1 VARCHAR)

RETURN VARCHAR2

 IS

--internal variables below

       OUTPUTER1 VARCHAR (200);

                   OUTPUTER2 VARCHAR (200);

            BEGIN

            --Function statements

            END

 

SQL Server

 

Declaring the variable:

 

Syntax:

            DECLARE @VARIABLE1 DATATYPE;

                          @VARIABLE2 DATATYPE;

 

Example:

            DECLARE @OUTPUTER VARCHAR (200),

        @ INPUTER INT;

 

As we can see SQL Server need to have @ in front of the variables and uses “,” as separator between variables.

 

Assigning/Set the variable value:

 

In SQL Server we can directly set the value to a variable, Assigning the values to variables can be done as direct input using SET operator or can be done directly in select clause.

 

Example:

            DECLARE @OUTPUTER1 VARCHAR (200),

                            @OUTPUTER2 VARCHAR (200),

                            @OUTPUTER3 VARCHAR (200),

                            --assignment during declaration  

                            @OUTPUTER4 VARCHAR (200)= 'SAM'

            --assignment using set operation

SET   @OUTPUTER1 ='OUTPUT SAM FROM OUTPUTER1'

--assigning from select statement

SELECT @OUTPUTER2='OUTPUT SAM FROM OUTPUTER2'  

--another way to set variable from select statement

SET @OUTPUTER3= (SELECT 'OUTPUT SAM FROM OUTPUTER3' )

PRINT @OUTPUTER1;

PRINT @OUTPUTER2;

PRINT @OUTPUTER3;            

PRINT @OUTPUTER4;

 

Declaring the internal variable inside the stored procedure or Function

 

Example:

 

PROCEDURE

CREATE PROCEDURE SP (@PARAM1 VARCHAR (100) OUTPUT)

AS

BEGIN

--internal variables below

DECLARE @OUTPUTER1 VARCHAR (200),

        @OUTPUTER2 VARCHAR (200)

--procedure statements

END

 

FUNCTION

CREATE FUNCTION FN (@PARAM1 VARCHAR (100))

RETURN VARCHAR (100)

AS

BEGIN

--internal variables below

DECLARE @OUTPUTER1 VARCHAR (200),

        @OUTPUTER2 VARCHAR (200)

--function statements

END

 

If you are looking for working with table variables/reference cursors in Oracle and SQL Server then click here, if you are trying to output a table then click here.

 

Thank you for reading this article, check out my other SQL Server vs. Oracle posts.