Difference between SQL server and oracle: sqloracle
Reproduced in: http://blog.csdn.net/it_fengli/article/details/8213839
-- Difference between SQL server and oracle:
-- DBMS Database Management System
-- 1. Different data types.
-- SQL server data types: 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 '); --- add the date conversion type before inserting the string date
-- 2. The function for obtaining the current system time is different.
-- SQL server: getdate ()
-- Oracle: sysdate
-- For example, the function that sets the date format: to_char (sysdate, 'yyy-mm-dd ');
-- 3. There is no default constraint in oracle
-- Add the default constraint to SQL server: alter table talbe_name add DF_table_name default ('male') for sex;
-- Add the default value in oracle: alter table table_name modify (sex default ('male '));
-- 4. The method for connecting variables and strings is different
-- SQL server connection: Use "+" connection, for example, print 'aaa' + @ name;
-- Oracle connection: Use "|" to connect, for example, dbms_output.put_line ('aaa' | name); --- name is the variable
-- 5. oracle does not have an automatic growth column for identity, but uses a sequence to achieve growth.
-- Automatic growth of SQL server: identity () can be directly used in the primary key column of the Table to achieve growth.
-- Auto-increment of oracle sequence:
Create sequence se_id
Start with 1
Increment by 1
-- Use sequence to achieve automatic growth: se_id.nextval
-- 6. conditional statement if ...... Else ...... Different syntax
-- SQL server:
If condition
Begin
............
End
Else
Begin
............
End
-- Oracle:
If condition 1 then
............;
Elsif condition 2 then
............;
Else
............;
End if;
-- 7. the syntax of the case statement is different.
-- SQL server:
-- Select ...... case ...... (else) ...... end ...... statement
Select stuno 'student ID ', 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 'bad'
End as 'level' from score
Go
-- Oracle:
Declare
Nums number: = & nos; -- & nos indicates that the input value is prompted.
Begin
Case nums
When 100 then
Dbms_output.put_line ('full score is good too ');
When 90 then
Dbms_output.put_line ('90 paging is good ');
End case;
End;
-- 8. The trigger creation syntax is different.
-- SQL server:
-- First, determine whether the trigger already exists.
If exists (select * from sys. sysobjects where name = 'tr _ delete ')
-- If yes, delete it first.
Drop trigger tr_delete
Go
-- Create a trigger
Create trigger tr_delete
On bookInfo
Instead of delete
As
-- Define variables
Declare @ bookid int
Select @ bookid = Bookid from deleted --- deleted execute the delete from BookInfo where BookId = 1) Statement to automatically generate the deleted table
-- Delete records related to the book (delete the master table first)
Delete from borrowinfo where bookid = @ bookid
Delete from backinfo where bookid = @ bookid
Delete from BookInfo where BookId = @ bookid
-- Judgment
If @ error <> 0
Begin
Print 'deletion failed'
Rollback transaction
End
Else
Begin
Print 'deleted successfully'
End
Go
Delete from BookInfo where BookId = 1
-- 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 ('A', 5, 0) into nums from dual;
End;
-- 9. Stored Procedures in oracle
-- Stored procedures in SQL server:
-- Determine whether a stored procedure already exists
If exists (select * from sys. sysobjects where name = 'proc _ name ')
-- If yes, delete it 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 an output parameter exists, 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 2 = @ 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 a user
-- SQL server
-- 1. Create a Logon account: sa ----- 123456
Create Login Name with password = 'login password'
-- Modify the Logon account:
Alter Login name with name = 'new Login name' and password = 'new Login password'
-- Disable/enable Logon account
Alter Login Name disable (disabled)/enable (Enabled)
-- Delete the Logon account
Drop Login Logon Name
-- 2. Create a user:
Create user username for/from Login Name
-- Modify user name
Alter user Username with name = 'new username'
-- Delete user name
Drop user Username
--- Grant permissions
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 10 M on users
-- Change password:
Alter user Username identified by new password
-- Grant permissions:
Grant create session to user name
-- Delete a user
Drop user Username cascade;
One point I have summarized is for reference only.
