There have been many articles about the differences between oracle and SQL Server, describing the general differences between them from the perspective of enterprises and database administrators. In this article, I will describe the differences between SQL Server and Oracle platforms from the perspective of applications, and discuss several possible methods to develop applications that do not depend on the database environment. At the same time, I won't discuss the obvious differences between the two application platforms, such as table segmentation and indexing.
Defining common interfaces and languages Few common languages and interfaces can make applications independent of databases and can be applied to relational databases in the same way: ANSI is defined by the National Bureau of Standards, an organization of volunteer members (operated by private funds). They have formulated nationally recognized standards in a wide range of fields such as equipment and procedures. In the field of database, ANSI defines the standard for writing SQL commands, assuming that the commands can be run on any database without changing the syntax of the commands.
ODBC is an open database connection (ODBC) interface defined by Microsoft, which allows applications to access data in a database management system (DBMS). Using SQL as the standard for accessing data, ODBC allows maximum interconnection, which means that a single application can access different database management systems, and then the end user of the application can add an open database connection (ODBC). OLE DB is the successor of ODBC, which is a set of software components that allow you to connect to the foreground, such as SQL Server Oracle DB MySQL based on VB C++ Access. In many cases, OLE DB components provide better performance than the original ODBC.
JDBC(Java database connection) application programming interface is a Java programming language and a wide range of databases, SQL databases and other tabular data sources (such as spreadsheets or plain text files). JDBD API is an industry standard for database-independent connection, which provides a call-level application program interface for SQL-based database access. Unfortunately, not all database-level commands are ANSI, and each database platform has its own extended functions. Generally speaking, ANSI or universal interface represents several functions, so it may also mean a loss of performance. For small databases and small applications, it is simple to maintain universal access to databases, but when databases and/or applications become larger and more complex, you must add functions to your code.
Comparison of Common Functions between SQL Server and Oracle
mathematical function
absolute value
S: select abs () value.
O: select abs () value from dual.
Round (large) s: select ceiling () value o: select ceiling () value from dual.
Round (small)
S: select floor () value o: select floor () value from dual.
Rounding (truncation)
S: select cast( as int) value o: select trunc () value from dual.
Round s: select round () value o: select round () value from dual.
E-radix power s: selectexp () value o: selectexp () value from dual.
E-based logarithm s: select log () value o: select ln () value from dual;
Base logarithm
S: select the log () value.
O: select the log () value from dual;
square
S: Select the square () value.
O: select the power () value from dual.
Find the square root
S: select SQRT () value.
O: select SQRT () value from dual.
Find the power S with any number as the radix: choose the power () value O: choose the power () value from the dual.
random digit
S: select rand () value.
O: select the value of sys dbms_random value () from dual;
Get symbols
S: Select the symbol () value.
O: Select the symbol () value from dual.
Trigonometric function correlation
Pi S: select pi () value.
Question: I don't know.
The tangent parameters of sine and cosine are all in radians.
For example, select the value of sin(PI()/) (SQLServer).
Asin Acos Atan Atan returns radians.
Arc angle exchange (unknown to SQLServer Oracle)
Radian > angle
Radian angle > radian
Comparison between values
Find the maximum value of a set
S: select the maximum value.
(Select value
alliance
Selection value
alliance
Selection value
alliance
Selection value) a
O: select the maximum () value from dual.
Find the minimum value of a set
S: select the minimum value from it.
(Select value
alliance
Selection value
alliance
Selection value
alliance
Selection value) a
O: select the minimum () value from dual.
How to deal with null values (changed to null in F) S: Select F is a null (f) value from TBL o: select f nvl (f) value from TBL.
String processing function
Find character sequence number
S: select ascii( a) value.
O: select ascii( a) value from dual.
Find characters from serial numbers
S: select char () value.
O: select chr () value from dual.
connect
S: select++value.
O:select CONCAT( )|| Value from dual
The substring position returns s: select charindex (sdsq) valueo: select instr (sdsq) value from dual.
The position return parameter of fuzzy substring returns if the middle% is removed//This paper is transferred from the research of C++Builder? I =& = dwrnrns: select patindex (%d% q% sdsfasdqe) Valueo: Oracle didn't find it, but instr can control the number of occurrences through the fourth parameter. Select INSTR (sdsfasdqe SD) value is returned from dual.
Find the substring s: select substring (ABCD) value o: select substr (ABCD) value from dual.
Substring replacement returns aijklmnefs: select stuff (abcdefjklmn) value o: select replace (abcdefbcdijklmn) value from dual.
Substring substitution
Student: Change.
O: select the translation (fasdbfasegasfa me) value from dual.
Length S:len data length
O: length
Upper and lower case conversion
Capitalize the first letter of a word.
S: I can't find it.
O: select INITCAP( abcd dsaf df) value from dual.
Fill in the space on the left (the first parameter of LPAD is a space, which is the same as the space function).
S: Select space ()+abcd value.
O: select LPAD( abcd) value from dual.
Fill in spaces to the right (if the first parameter of RPAD is a space, it is the same as the space function).
S: select abcd +space () value.
O: select RPAD( abcd) value from dual.
Delete the space S:ltrim rtrim O:ltrim rtrim trim.
Repeat the string s: select the copy (abcd) value.
O: I didn't find it.
Pronunciation similarity comparison (these two words have the same pronunciation and return the same value) S: Select Soundex(Smith)Soundex(Smythe) O: Select Soundex (Smith) Soundex (Smythe) from Dual SQL Server, compare the differences of Soundex with Select Difference (Smithers Smythers) and return it as the highest homonym.
Date function
System time
S: select the getdate () value.
O: select the system date value from dual.
A few days before and after
Add and subtract directly with integers.
Find the date s: select convert (char () getdate ()) value.
O: select trunc(sysdate) value from dual.
Select to_char(sysdate yyyy mm dd) value from dual.
Search time s: select convert (char () getdate ()) value o: select to _ char (sysdate hh: mm: ss) value from dual.
Take another part of the date and time
S:DATEPART and DATENAME functions (determined by the first parameter)
The second parameter of O:to_char function determines
The following parameter list needs to be supplemented.
Year yyyy quarter qq q (quarter) month mm m (m O invalid) year Dyy day Dyy(O stands for week) day dd d (d O invalid) week wk ww (wk O invalid).
Working day dw (O unclear)
Hour hh hh (hh hh S is invalid) minute min(n 0 is invalid) second ss(S 0 is invalid)
Seconded ms (o is invalid)
The last day of every month
S: I don't know
O: select the last day (system date) value from dual.
One day of the week (such as Sunday)
S: I don't know
O: select the Next_day(sysdate) value from DUAL;
String to time
S: You can directly switch or select the value of cast (as datetime).
O: select to _ date (::yyyymm DD hh mis) value from DUAL;
Find the difference (such as seconds) between some parts of two dates.
S: select datediff (ssgetdate () getdate ()+) valueo: directly subtract two dates (for example, d d =).
Select (d d )* * * value from DUAL;
Find a new date (such as minutes) according to the difference.
S: select dateadd(mi getdate ()) value.
O: select sysdate+//value from DUAL;
Find the time in different time zones
S: I don't know
O: select New_time(sysdate ydt gmt) value from DUAL;
The time zone parameter of East District Beijing should be Ydt.
Atlantic standard time
BST BDT Bering sea standard time
Central standard time
eastern standard time
GMT Greenwich mean time
HST HDT Alaska-Hawaii Standard Time
MST MDT mountain standard time
Newfoundland standard time
Pacific Standard Time
YST YDT Yukon standard time
Character functions supported by Oracle and their Microsoft SQL Server equivalents;
Function Oracle Microsoft SQL Server
Convert characters to ASCII ASCII ASCII.
String concatenation (expression+expression)
Convert ASCII to character CHR CHAR.
Returns the starting character (starting from the left) in a string.
Convert characters to lowercase lowercase lowercase
Convert characters to uppercase.
Fill in the left LPAD N/A of the string.
Clear the blank LTRIM LTRIM at the beginning
Clear the blank RTRIM RTRIM at the end.
The starting pattern in the string instr patindex.
The repeating string RPAD is copied many times.
The phonetic representation of the string is SOUNDEX SOUNDEX.
Chord RPAD space with repetitive space
TO_CHAR string converted from digital data to character data
Substring substring
Substitution character replacement material
Capitalize the first letter of each word in the string initcap n/a.
Translation string translation is not applicable.
String length length date length or length
Maximum does not apply, the largest string in the list.
The smallest string in the list is not applicable.
Lishi Xinzhi/Article/program/Oracle/20 13 1 1/ 1656 1