A summary of the differences between Oracle and SQL Server

My main database used for oracle10g and SqlServer2008, through the actual use and access to data collated as follows:

Theme	Oracle 10g	SQL Server 2008
Stored Procedure Format	Create Or Replace Procedure stored procedure name (parameter list) Is Begin ---stored procedure content End stored procedure name;	Create Procedure Stored Procedure name (parameter list) As Begin --Stored Procedure content End
Stored Procedure Parameters	1, specifies the parameter type, but does not specify the length; 2, in the parameters and types to add input and output identification (in, out); Eg:param in int	1, specify the parameter type, and specify the length; 2. Output parameters need to be marked with output or out after type eg: @param nvarchar (ten) out
Using Stored Procedures	1, directly use the name of the stored procedure; 2, the parameters are listed in parentheses after the stored procedure; Eg:proc_getlist (param1, param2);	1, the keyword exec, stored procedure name; 2, the parameters are listed after the stored procedure, the output parameters should be added after the output identification (output or out); Eg:exec proc_getlist @param1, @param2 out
Custom Function format	create  Or   Replace  function  function name (argument list) return  return value  --can be parameter   or type (nvarchar) is begin    --function contents end	create  function function name (parameter list) returns  return value type as begin    --function contents end
Custom Function Content	1, specifies the parameter type, but does not specify the length; 2, in the parameters and types to add input and output identification (in, out); Eg:param in int 3, support the data increase, delete, change operation 4. Support Dynamic SQL statements	1, specify the parameter type, and specify the length; 2. Output parameters need to be marked with output or out after type eg: @param nvarchar (ten) out 3, do not support the data increase, delete, change operation 4. Dynamic SQL statements are not supported
Using a custom function	1. Use function name name directly Eg:result: = Func_isandbegin (flow_id, step_id)	1. Precede the function name with the dbo. Eg:set @result = Dbo.func_isandbegin (@flow_id, @step_id)
Cursor	1. Cursor declaration Declare CURSOR Cursor name is SELECT statement 2. Using the cursor procedure open (open), extraction (fetch), close (Close) 3, support the quick use of cursors, directly using for loop, the database will automatically open, extract and close cursors	1. Cursor declaration DECLARE cursor NAME CURSOR FOR SELECT statement 2, using the cursor procedure open (open), extraction (fetch), Close, destroy
Variable	1, the variable before the @ symbol cannot be added; 2, the variable declaration in the stored procedure does not need declare; Eg:param nvarchar (5); 3. Variable types can be dynamically defined by the field type in the table eg: _id Tablename.id%type;	1, before the variable needs to add the @ symbol; 2, the variable declaration needs to use the DECLARE keyword; eg:declare @param nvarchar (5); 3. Dynamic Definition by table field type is not supported
Assign value	1, variable direct assignment, variable: = expression; Eg:result: = ' ABCD '; 2. SQL statement: Select expression into variable from table Eg:select code,name to _code,_name from tableName where id = 10;	1, variable direct assignment, Set variable = expression; Eg:set @result = ' ABCD ' 2. Assigning values through SQL statements: Select variable = expression from table Eg:select @code =code, @name =name from tablename where id = 10
Statement Terminator	The SQL statement uses semicolons; As the end of the statement	The SQL statement does not need a semicolon; as a Terminator (plus)
Uppercase and lowercase	Oracle is case-sensitive to characters	Default pairs of characters are case-insensitive, and database configuration support is case-sensitive
Sequence	1, there is sequence sequence object, no auto-growth column	1, no Sequence object, the table has an automatic growth column identity (a)
Select syntax	1, the result set can be used as a table, the use of the alias can not be added Eg:select * FROM (Select * from TAB1) 2, the use of virtual table dual, for Select to calculate some non-entity table-independent expression, to use the virtual table dual Eg:select round (1/3,2) from dual;	1, the result set can be used as a table, using the alias must be added Eg:select * FROM (Select * from TAB1) a 2. For select to calculate some expressions that are not related to the entity table, use a SELECT statement without a from Eg:select round (1/3,2);
Update syntax	Non-associative table update Eg: For cur in (select A.id,b.value from a    inner   join B on a.id = b.ID) loop    UPD Ate   a set a.value = Cur.value    Where   a.id = cur.id; End Loop; or Update a set a.value = (select B.value from b where b.id = a.id)	can correlate table update Eg: Update a set a.value = IsNull (B.value, ') from a INNER JOIN B on a.id = b.id   or Update a set a.value = IsNull (B.value, ') from B where a.id = b.ID
Delete syntax	Non-Associative table deletion Eg:delete A where exists (select b.id from b where a.id = b.ID)	Can associate table Delete Eg:delate A from a Inner joins B on a.id = b.ID
Dynamic SQL statements	1. Normal Dynamic SQL statements Begin Execute immediate ' update tab1 set column1=5 '; End; Replacing SQL statements with variables V_sql: = ' update tab1 set column1=5 ' Execute immediate v_sql; 2. Take out the dynamic SQL statement N_count number (10); V_sql VARCHAR2 (max); V_sql: = ' SELECT count (*) from TableName '; Execute immediate v_sql into N_count; 3. Dynamic stored procedure (with and out) V_sql: = ' Begin Proc_test (: V1,:V2,:V3); End; ' Execute Immediate v_sql Using in V_code,in v_name,out v_result; Note: proc_test is a stored procedure name	1. Normal Dynamic SQL statements EXEC (' update tab1 set column1 = 5 ') Or EXEC sp_executesql n ' select * from TableName '--the string must be added n before Replacing SQL statements with variables Declare @sql Nvarchar (max) Set @sql = ' select * from TableName ' EXEC sp_executesql @sql 2. Take out the dynamic SQL statement DECLARE @count int declare @sqls nvarchar (max) Set @sqls = ' Select @a=count (*) from TableName ' EXEC sp_executesql @sqls, N ' @a int output ', @count output 3. Dynamic stored procedure (with and out) Declare @result nvarchar (50), @sql nvarchar (max), @para nvarchar (200); Set @sql = ' proc_test @code, @name, @result output ' Set @para = ' @code nvarchar, @name nvarchar (ten), @result nvarchar output ' EXEC sp_executesql @sql, @para, ' 001 ', ' Zhang San ', @result OUTPUT Note: proc_test is a stored procedure name
Top usage	1. Using pseudo-column rownum in Oracle to get the first partial records in the result set Eg: Returns the first 10 records in the result set Select * from TableName where rownum <=10 RowNum can use <, <= symbol, not use >, >= symbol, if Use = number can only =1 2. The rownum column is also commonly used to form the sequence number of the result set, thus obtaining a row of a certain ordinal range Eg: Get line 10th to 20th by code SELECT * FROM (select A.*,rownum as Nrow from TAB01 a order by code) where Nrow between and 20	1, SQL Server in the top way to get the result set in the previous part of the record Eg: Returns the first 10 records in the result set Select Top Ten * from SYSC01 2. SQL Server can be implemented by row_number () ranking function Eg: Get line 10th to 20th by code Select A.* from (select A.*, Row_number () over (Order by A.code) as Nrow from Tab01 a) a where nrow between and 20
IF ... Else Flow control	IF condition Expression Then {Statement block} Else {Statement block} End if;	An IF condition expression {Statement block} Else {Statement block} End If there are more than 1 SQL statements in the statement block, you must use the BEGIN ... end construct
Case usage	1, expression: Case expression when match expression then result expression 1 else result expression 2 end Eg: Select case Name W Hen ' Zhang San ' then 1 if ' John Doe ' then 2 else 0   end From person or   case when condition expression then result expression 1 else result expression 2 End Eg: Select case if Name= ' Zhang San ' then 1 if name= ' John Doe ' then 2 else 0   end From person 2, Stream Control statement Flow control is similar to expression structure, except that the result expression is replaced with a statement block Case expression when match expression then {statement Block 1} else {statement block 2} end case; Eg: Case v_name  if ' Zhang San ' then  v_number: = 1; When ' John Doe ' then v_number:=2; Else v_number:=0  end case; or   case when conditional expression then {statement Block 1} else {statement block 2} end case; Eg: case  when v_name = ' Zhang San ' then  v_number: = 1;  when v_name = ' John Doe ' then V_number:=2; else  v_number:=0  end case;	1,   CASE expression with Oracle              2, flow control not supported
Temp table	1, the temporal table structure needs to be defined in advance as an entity table and then used in SQL scripts. The temporary table name is the same as the Entity table name rule Eg: create temporary table WFMW02 (Paraid number (9),   vvalue nvarchar2 (),   flowid number (9),   id     number (9)) on commit delete rows;	1,   temporary table is created and used during a database scripting session, preceded by a # sign (or # #全局临时表) Eg: create table #WFMW02   vvalue nvarchar (a),   Flowid int,   id     int) Temporary table can also be automatically created during use Select paraid,vvalue, Flowid,id To #wfmw02 from wfmb01c
Common functions	1, NVL (expression 1, expression 2); --expression 1 is null return expression 2 2, Sysdate--system time 3, InStr (character expression 1, character expression 2 [, Pos][,nth]) Search for the position of "Expression 2" of "Nth" from the pos position of expression 1 (return 0 if not found) 4, To_char (), To_date (), To_number ()	1, isnull (expression 1, expression 2) 2, GETDATE () 3, CHARINDEX (character expression 1, character expression 2 [, POS]) Searches for expression 1 from the POS position of expression 2 and returns the starting position of expression 1 (returns 0 if not found) 4. Convert (), cast ()
Operator	Add Characters | | Eg: ' A ' | | ' B ' = ' AB '	Add characters + Eg: ' A ' + ' B ' = ' AB '
Null	For character data, an empty string is equivalent to null	For character data, the empty string differs from null

A summary of the differences between Oracle and SQL Server