SQL Server and Oracle common function comparisons

Procedure Pro_subpro
(
Dw_in in Varchar2,
Cur out pkg_general. Row_cursor
)
As
Begin
Open cur for
Select YQMC from Fle80 where dwbm=dw_in;
End Pro_subpro;

Procedure Pro_mainpro (
Dw_in in VARCHAR2
)
As
MC NVARCHAR2 (20);
Cur pkg_general. Row_cursor;
Begin
Pro_subpro (dw_in,cur);
Fetch cur into MC;
Loop
Exit when Cur%notfound;
Dbms_output.put_line (MC);
Fetch cur into MC;
End Loop;
End Pro_mainpro; Mathematical functions
In Oracle, the DISTINCT keyword can display the same record and display only one
1. Absolute value
S:select ABS ( -1) value
O:select ABS ( -1) value from dual

2. Rounding (Large)
S:select Ceiling ( -1.001) value
O:select ceil ( -1.001) value from dual

3. Rounding (small)
S:select Floor ( -1.001) value
O:select Floor ( -1.001) value from dual

4. Rounding (interception)
S:select cast ( -1.002 as int) value
O:select trunc ( -1.002) value from dual

5. Rounding
S:select round (1.23456,4) value 1.23460
O:select round (1.23456,4) value from dual 1.2346

6.E to the bottom of the power
S:select EXP (1) Value 2.7182818284590451
O:select EXP (1) value from dual 2.71828182

7. Take e as the base of the logarithm
S:select log (2.7182818284590451) value 1
O:select ln (2.7182818284590451) value from dual; 1

8. Take 10 as the base logarithm
S:select log10 (a) value 1
O:select log (10,10) value from dual; 1

9. Take the Square
S:select SQUARE (4) value 16
O:select Power (4,2) value from dual 16

10. Take the square root
S:select SQRT (4) Value 2
O:select SQRT (4) value from dual 2

11. To find any number of the bottom of the power
S:select Power (3,4) value 81
O:select Power (3,4) value from dual 81

12. Take random number
S:select rand () value
O:select Sys.dbms_random.value (0,1) value from dual;

13. Take the symbol
S:select sign ( -8) value-1
O:select sign ( -8) value from Dual-1

14. Pi
S:select PI () value 3.1415926535897931
O: I don't know.

15.sin,cos,tan parameters are in radians
For example: Select Sin (PI ()/2) value gets 1 (SQL Server)

16.asin,acos,atan,atan2 return radians

17. Radian Angle Interchange (sqlserver,oracle don't know)
DEGREES: Radian-〉 Angle
RADIANS: Angle-〉 radians

Comparison between values

18. Find the maximum value of the set
S:select max (value) value from
(Select 1 value
Union
Select-2 value
Union
Select 4 Value
Union
Select 3 Value) a

O:select Greatest (1,-2,4,3) value from dual

19. Find the minimum value of the set
S:select min (value) value from
(Select 1 value
Union
Select-2 value
Union
Select 4 Value
Union
Select 3 Value) a

O:select least (1,-2,4,3) value from dual

20. How to handle null values (Null in F2 in lieu of 10)
S:select f1,isnull (f2,10) value from TBL
O:select F1,NVL (f2,10) value from TBL

21. Ask for character serial number
S:select ASCII (' a ') value
O:select ASCII (' a ') value from dual

22. Ask for character from ordinal number
S:select char () value
O:select chr () value from dual

23. Connection
S:select ' one ' + ' + ' + ' value
O:select CONCAT (' One ', ') ' value from dual

23. Sub-string position--return 3
S:select CHARINDEX (' s ', ' SDSQ ', 2) value
O:select INSTR (' sdsq ', ' s ', 2) value from dual

23. Position of fuzzy substring--return 2, parameter minus middle% return 7
S:select patindex ('%d%q% ', ' sdsfasdqe ') value
O:oracle didn't find it, but InStr can control the number of occurrences with a fourth parameter.
Select INSTR (' sdsfasdqe ', ' SD ', 1,2) value from dual return 6

24. Find the substring
S:select substring (' ABCD ', 2,2) value
O:select substr (' ABCD ', 2,2) value from dual

25. Substring instead of return AIJKLMNEF
S:select STUFF (' abcdef ', 2, 3, ' IJKLMN ') value
O:select Replace (' abcdef ', ' BCD ', ' ijklmn ') value from dual

26. Sub-string Replace all
S: I didn't find it.
O:select Translate (' Fasdbfasegas ', ' fa ', ' I ') value from dual

27. Length
S:len,datalength
O:length
28. Capitalization Conversion Lower,upper

29. Capitalize the first letter of the word
S: I didn't find it.
O:select initcap (' ABCD Dsaf df ') value from dual

30. Left fill space (the first parameter of Lpad is a space function)
S:select space + ' ABCD ' value
O:select lpad (' ABCD ') value from dual

31. Right complement space (the first parameter of Rpad is a space function)
S:select ' ABCD ' +space (+) value
O:select rpad (' ABCD ') value from dual

32. Delete Space
S:ltrim,rtrim
O:ltrim,rtrim,trim

33. Duplicate string