SQL Server vs. Oracle

Recently went to the Iron Academy interview, the project leader asked me this question: what is the difference between SQL Server and Oracle? From entering the software industry, from the beginning of the code of CS to the code of the BS is SQL Server, in the process of starting to knock on the DRP six months ago to contact Oracle, then gave me the feeling that Oracle is too large, perhaps the new things are this feeling, Let me introduce you in detail below:

1. Data types are different.
data types for SQL Server

int, smallint , Char,varchar,nchar,nvarchar,ntext,datetime,smalldatetime,money,decima, Float,bit  

Oracle data type: Number (p,s), Char,varchar2,date,lob
Note: INSERT INTO table_name values (' 1 ', ' Zhang San ', ' Male ', date ' 2012-3-5 '); Insert string date plus date conversion type

2. The function that obtains the current system time is different.
SQL Server:getdate ()

Oracle:sysdate
For example: the function of setting date format: To_char (sysdate, ' yyy-mm-dd ');
3. The assertion that there is no default constraint in Oracle
Add default constraint in SQL Server: ALTER TABLE talbe_name add df_table_name default (' Male ') for sex;

Add default value in Oracle: ALTER TABLE table_name modify (' Male ');

4. Connecting variables and strings in different ways
Connect in SQL Server: Use "+" connection, for example: print ' aaaa ' + @name;

Connect in Oracle: Use "| |" Connection, for example: Dbms_output.put_line (' aaa ' | | name);---name is a variable

5.oracle does not have an identity auto-grow column, but instead uses the sequence to achieve growth
SQL Server autogrow: Growth can be achieved directly using identity in the table's primary key column

Oracle uses sequence auto-growth:
Create sequence se_id
Start with 1
Increment by 1
Automating growth with sequences: Se_id.nextval
6. Conditional Statement If......else ... The syntax is different
In SQL Server:
If condition
Begin
............
End
Else
Begin
............
End
In Oracle:
If condition 1 Then
............;
elsif Condition 2 Then
............;
Else
............;
End If;

7.case statement syntax is different
In SQL Server:
Select ..... case ..... (else) .... End .... Statement
Select Stuno ' study Number ', case
When grade>=90 and grade<=100 then '
When grade>=80 and grade<90 then '
When grade>=70 and grade<80 then '
When grade>=60 and grade<70 then '
Else ' poor '
End as ' level ' from score
Go
In Oracle:
Declare
Nums Number:=&nos;&nos indicates the incoming value is prompt
Begin
Case Nums
When
Dbms_output.put_line (' Perfect score also, good ');
When the
Dbms_output.put_line (' 90 page very good ');
End case;
End
8. Trigger creation syntax is different
In SQL Server:
First determine if the trigger already exists
if exists (SELECT * from sys.sysobjects where name= ' Tr_delete ')
If there is a delete first
Drop Trigger Tr_delete
Go

Create a Trigger
Create Trigger Tr_delete
On BookInfo
Instead of delete
As
Defining variables
DECLARE @bookid int
Select @bookid =bookid from deleted---deleted execute DELETE statement (delete from BookInfo where bookid=1), automatically generated deleted table
Delete Related records from the book (delete the main table from the table first)
Delete from borrowinfo where [email protected]
Delete from backinfo where [email protected]
Delete from BookInfo where [email protected]
Judge
If @ @error <>0
Begin
print ' Delete failed '
ROLLBACK TRANSACTION
End
Else
Begin
print ' Delete succeeded '
End
Go
Delete from BookInfo where bookid=1

In Oracle:
Create a Trigger
Create or Replace Trigger Tri_test
Before insert or UPDATE or delete
On table_name
[For each row]---if you want to use: New/:old You must use a row trigger
Declare
Nums varchar2 (20);
Begin
Select ' F ' | | Lpad (' AA ', 5,0) into the nums from dual;
End

Stored Procedures in 9.oracle
stored procedures in SQL Server:

Determine if the stored procedure already exists
if exists (SELECT * from sys.sysobjects where name= ' Proc_name ')
If there is a delete first
drop proc Proc_name
Go

Create a Stored procedure statement
Create Proc/procedure Proc_name
@ Parameter name 1 data type [Out/output],
@ Parameter Name 2 data type [Out/output]
As
............
Go

Call a stored procedure
If you have an output parameter, you need to define the variable (assuming @ parameter 2 is the output parameter)
DECLARE @ variable name data type
EXEC proc_name @ parameter name 1= ' AAA ', @ parameter name [email protected] variable name out


Stored procedures with cursors and loops in Oracle

Create or replace procedure Proc_selcurrent
(
Names VARCHAR2
)
As
Cursor Cursor_sel
Is
Select Depositsum,cardtype,name,state from Currentaccount where name like '% ' | | names| | ' %‘;
DD number;
CC number;
nn varchar2 (20);
STA number;
Begin
Open Cursor_sel;
Loop
Fetch Cursor_sel into Dd,cc,nn,sta;
Dbms_output.put_line (' Deposit amount: ' | | dd| | ' Name: ' | | NN);
Exit when Cursor_sel%notfound;
End Loop;
Close Cursor_sel;
End

Call a stored procedure
Begin
Proc_selcurrent (' a ');
End

10. Different ways to create users
In SQL Server
1. Create login account: SA-----123456
Create login login name with password= ' login password '

To change your login account:
ALTER LOGIN login name with name= ' new login name ' and password= ' new login password '
Disable/Enable login account
ALTER LOGIN login name disable (disabled)/enable (enabled)
Delete login Account
Drop Login Login Name

2. Create User:
Create user username For/from Login login Name

Modify User Name
Alter user username with Name= ' new username '

Delete User name
Drop user Username

Authorization limit
Grant Select/update/delete/insert on table name to user name


In Oracle:

Create user syntax:
Create user username
Identified by password
Default Tablespace users
Temporary Tablespace Temp
Quota 10M on users

Change Password:
Alter user username identified by new password

To grant permissions:
Grant create session to user name

Delete User
Drop user username cascade;

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

SQL Server vs. Oracle