Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Compare SQL Server and Oracle Coding - Part 2


By:   |   Updated: 2015-06-18   |   Comments   |   Related: More > Other Database Platforms

Problem

I am working on both SQL Server and Oracle databases.  I know many of the concepts are the same, but I am not sure how to build the code I need to complete my daily tasks.  Can you provide some insight into SQL Server vs. Oracle?

Solution

In this tip of the SQL Server and Oracle comparison series, we will see how T-SQL compares to PL-SQL for:

  • Table Creation
  • Comment Code
  • Accessing the Top 5 Rows of a Table
  • Create a Function
  • Call a Function
  • Table Information
  • Get Object Code
  • Looping
  • Save Query Results in a Text File
  • IF, ELSIF, ELSE Clauses

 

Table Creation in SQL Server vs. Oracle

In our examples, we will use the customer and products table.  Here are the corresponding scripts:

SQL Server

Oracle

CREATE TABLE customer
(
id INT NOT NULL ,
NAME VARCHAR(30) NULL ,
GENDER CHAR(1) NULL ,
BIRTH_DT DATE NULL ,
LASTNAME VARCHAR(30) NULL ,
CITY VARCHAR(30) DEFAULT 'NY'
CONSTRAINT PK_PERSON PRIMARY KEY (id)
)
GO

CREATE TABLE customer
(
id INT NOT NULL ,
NAME VARCHAR(30) NULL ,
GENDER CHAR(1) NULL ,
BIRTH_DATE DATE NULL ,
LASTNAME VARCHAR2(30) NULL ,

CITY VARCHAR2(30) DEFAULT 'NY'
CONSTRAINT PK_PERSON PRIMARY KEY (id)
)
/

SQL Server

Oracle

create table product
(
id int,
price int
)
GO

create table product
(
id integer,
price integer
);
/

 

Comment Code in SQL Server vs. Oracle

SQL Server

Oracle

--This is a comment for 1 in SQL Server line

/*This is a comment in SQL Server multiple lines of T-SQL code*/

--In Oracle it is the same

/*In Oracle the comments used in PL-SQL are the same than in SQL Server*/

 

Return the Top 5 Rows in a Table in SQL Server vs. Oracle

SQL Server

Oracle

SELECT TOP 5 name from customer
GO

SELECT from customer
LIMIT 5;

 

Create a Function in SQL Server vs. Oracle

A function is a routine with a name that accepts parameters and return tables or values and includes calculations to perform different operations used in the code. The functions can be part of an expression.

The following functions receives a price value and calculates the price including the taxes of 30 %.

SQL Server

Oracle

CREATE FUNCTION price_with_taxes(@price float)
RETURNS float
AS
BEGIN
declare @pricetax float= @price*1.3;
return (@pricetax);
END

In SQL Server there are several types of functions. CLR Functions, Scalar, Inline table-valued, Multi-statemedt Table-Valued. For more information, refer to this link.

CREATE OR REPLACE FUNCTION price_with_taxes(price IN NUMBER)
RETURN NUMBER
IS pricetax NUMBER(11,2);
BEGIN
pricetax:=price*1.3;
return (pricetax);
END;
/

 

Call a Function in SQL Server vs. Oracle

SQL Server

Oracle

select dbo.price_with_taxes(price) from product

GO

select price_with_taxes(price) from product;

 

Table Information in SQL Server vs. Oracle

SQL Server

Oracle

sp_help customer

GO

The result will be similar to this one:

Tabular properties

desc customer;

The result will be similar to this one:

How to get informations about the table

 

Get Object Code in SQL Server vs. Oracle

SQL Server

Oracle

sp_helptext object_name

code of procedures

It returns the code of existing procedures, functions. It is not applicable to tables.

Select name,text from user_source where name='object_name'

How to use loops

 

Loops in SQL Server vs. Oracle

The loops can be used to iterate statements multiple times.

SQL Server

Oracle

declare @value int=1
while 10>[email protected]
BEGIN
SET @value = @value + 1

SELECT @value
END

In SQL Server a loop is like a cursor and a cursor in SQL Server is slow and resource intensive. It is not recommended for high volumes or data. You can safely use while clauses for small amounts of data.

declare
total integer;
BEGIN

select sum(price) into total from product;
WHILE (total) <= 1000
LOOP
select sum(price) into total from product;
update product
set price = price*1.1;
END LOOP;
END;
/

In oracle are many sentences to create loops like while loops, basic loops, for loops, etc. For more information about loops, refer to the loops documentation. For bigger amounts of data you can work with batches. For more information refer to this link.

 

Save Query Results in a Text File in SQL Server vs. Oracle

SQL Server

Oracle

1. Save this query in a file named customerquery.sql:

USE DB2
GO
select * from customer
GO

2. Now in the cmd run this command:

sqlcmd -i c:\scripts\customerquery.sql -o c:\scripts\results.txt

1. First enable the spool and specify a path:

spool c:\Oracle;

An Oracle.lst will be created.

2. Now, write a query.

select * from customer;

3. Disable the spool.

spool off;

4. Check the c:\oracle.lst file results.

 

IF, ELSIF, ELSE Clauses in SQL Server vs. Oracle

SQL Server

Oracle

In SQL Server you only have IF and ELSE. IF is used in the code for conditions. For example, if the value is higher than 5 (IF @value>5). In SQL Server you can have several if conditions.

When a condition occurs, some sentences are executed. ELSE is used if the IF condition(s) does not occur. ELSE is used at the END of the if conditions.

/*This sample shows the water state according to the temperature*/

declare temperature int;
BEGIN
declare @state varchar(10)
IF @temperature > 100
SET @state = 'Boiled'

IF @temperature between 1 and 99
SET @state = 'Liquid'

ELSE
SET @state = 'Frozen'

select @state

END

In Oracle, the use of IF AND ELSE statements is the same than in SQL Server, but you have also ELSIF statements. The ELSIF are used after the first IF. In Oracle, you have an if at the beginning of the statement and you can have several ELSIF conditions later.

DECLARE

temperature number(3):=0;
status varchar2(10);
BEGIN

IF temperature>100 THEN
status:='Boiled';


ELSIF temperature between 1 and 99 THEN

status:='liquid';
ELSE

status:='frozen';

END IF;
DBMS_OUTPUT.PUT_LINE(status);
END;
/

 

Next Steps

For more information about T-SQL and PL-SQL, refer to the following links:



Last Updated: 2015-06-18


get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools