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.