Comparison of common SQL Server and Oracle Functions
Procedure Pro_SubPro
(
Dw_in in varchar2,
Cur out pkg_general.ROW_CURSOR
)
As
Begin
Open cur
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, only one distinct keyword can be displayed for the same record
1. Absolute Value
S: select abs (-1) value
O: select abs (-1) value from dual
2. INTEGER (large)
S: select ceiling (-1.001) value
O: select ceil (-1.001) value from dual
3. Round (small)
S: select floor (-1.001) value
O: select floor (-1.001) value from dual
4. Round (truncation)
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 is the base power
S: select Exp (1) value 2.7182818284590451
O: select Exp (1) value from dual 2.71828182
7. Take the base logarithm of e.
S: select log (1, 2.7182818284590451) value 1
O: select ln (2.7182818284590451) value from dual; 1
8. Use 10 as the base logarithm.
S: select log10 (10) value 1
O: select log (10, 10) value from dual; 1
9. 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. Evaluate the base power of any number
S: select power (3, 4) value 81
O: select power (3, 4) value from dual 81
12. Random Number acquisition
S: select rand () value
O: select sys. dbms_random.value (0, 1) value from dual;
13. Get the symbol
S: select sign (-8) value-1
O: select sign (-8) value from dual-1
14. Circumference Rate
S: select pi () value 3.1415926535897931
O: Unknown
15. sin, cos, and tan parameters are in radians.
For example, select sin (PI ()/2) value to get 1 (SQLServer)
16. Asin, Acos, Atan, Atan2 return radians
17. radian angle Interchange (, Oracle unknown)
DEGREES: Radian-> Angle
RADIANS: Angle-> radian
Comparison between values
18. Calculate 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)
O: select greatest (1,-2, 4, 3) value from dual
19. 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)
O: select least (1,-2, 4, 3) value from dual
20. How to Handle null values (replace null with 10 in F2)
S: select F1, IsNull (F2, 10) value from Tbl
O: select F1, nvl (F2, 10) value from Tbl
21. Search for the character serial number
S: select ascii ('A') value
O: select ascii ('A') value from dual
22. calculate characters from the serial number
S: select char (97) value
O: select chr (97) value from dual
23. Connection
S: select '11' + '22' + '33' value
O: select CONCAT ('11', '22') 33 value from dual
23. substring position -- return 3
S: select CHARINDEX ('s ', 'sdsq', 2) value
O: select INSTR ('sdsq', 's', 2) value from dual
23. Position of the fuzzy substring -- 2 is returned. If % is removed from the parameter, 7 is returned.
S: select patindex ('% d % q %', 'sdsfasdqe ') value
O: no oracle found, but instr can use the fourth parameter to control the number of occurrences.
Select INSTR ('sdsfasdqe ', 'sd', 1, 2) value from dual returns 6
24. Seek substrings
S: select substring ('abcd', 2, 2) value
O: select substr ('abc', 2, 2) value from dual
25. Return aijklmnef as a substring
S: select stuff ('abcdef', 2, 3, 'ijklmn ') value
O: SELECT Replace ('abcdef', 'bcd', 'ijklmn ') value from dual
26. replace all substrings
S: No
O: select Translate ('fasdbfasegas', 'fa ', 'my') value from dual
27. Length
S: len, datalength
O: length
28. case-insensitive lower, upper
29. uppercase letters
S: No
O: select INITCAP ('abcd dsaf df') value from dual
30. Left fill space (the first parameter of LPAD is space, which is the same as the space function)
S: select space (10) + 'abc' value
O: select LPAD ('abc', 14) value from dual
31. Right fill space (the first parameter of RPAD is space, which is the same as the space function)
S: select 'abc' + space (10) value
O: select RPAD ('abcd', 14) value from dual
32. Delete Spaces
S: ltrim, rtrim
O: ltrim, rtrim, trim
33. Duplicate string