The difference between Oracle and SQL Server

The difference between--sql server and Oracle:

--dbms Database Management System
--1. Data types are different.
--sql Server data type: int, smallint, Char,varchar,nchar,nvarchar,ntext,datetime,smalldatetime,money,decima,
--float,bit ...


Data type of--oracle: Number (p,s), Char,varchar2,date,lob
--NOTE: INSERT INTO table_name values (' 1 ', ' Zhang San ', ' Male ', date ' 2012-3-5 ');---insert string date and type

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

--oracle:sysdate
-For example: Set the function of the 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 (sex default (' Male '));


--4. Connecting variables and strings in different ways
--sql Server Connection: Use "+" connection, for example: print ' aaaa ' [email protected];

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 primary key column of the table

--oracle uses sequence auto-growth:
Create sequence se_id
Start with 1
Increment by 1
--Automatic growth using 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;

The syntax of the--7.case statement is different
In--sql server:
--select ..... (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--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 before deleting it)
Delete from borrowinfo where [email protected]
Delete from backinfo where [email protected]
Delete from BookInfo where [email protected]
--Judgment
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 the--9.oracle
--sql stored procedures in 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 the 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 the 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 '

--Modify 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


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

--Grant Permissions:
Grant create session to user name

--Delete User
Drop user username cascade;


Own summary of a point, for reference only

The difference between Oracle and SQL Server