Compare SQL Server and Oracle Coding - Part 2

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

create table product
(
id int,
price int
)
GO

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

--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*/

SELECT TOP 5 name from customer
GO

SELECT from customer
LIMIT 5;

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;
/

select dbo.price_with_taxes(price) from product

GO

select price_with_taxes(price) from product;

sp_help customer

GO

The result will be similar to this one:

desc customer;

The result will be similar to this one:

sp_helptext object_name

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

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

declare @value int=1
while 10>=@value
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.

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.

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;
/