FAQs about Oracle Development

FAQs about Oracle Development

SQL Server and Oracle are the most common databases in daily project development, but more beginners may be more familiar with SQL Server, because Microsoft is easy to use, it is silly. However, Oracle's position in the market cannot be ignored, while Oracle and SQL Server are still very different. This section describes some issues that beginners often encounter when using Oracle for development.

5.5.1 comparison of common functions of Oracle and SQL Server

1. mathematical functions

 
 

  
  
 
  
① Absolute value
 
  
  
 
  
S: Select ABS (-1) Value
 
  
  
 
  
O: Select ABS (-1) value from dual
 
  
  
 
  
② INTEGER (large)
 
  
  
 
  
S: Select ceiling (-001) Value
 
  
  
 
  
O: Select Ceil (-001) value from dual
 
  
  

  
③ Round (small)
 
  
  
 
  
S: Select floor (-001) Value
 
  
  
 
  
O: Select floor (-001) value from dual
 
  
  
 
  
④ INTEGER (truncation)
 
  
  
 
  
S: Select cast (-002As Int) Value
 
  
  
 
  
O: Select trunc (-002) value from dual
 
  
  
 
  
⑤ Rounding
 
  
  
 
  
S: Select round (23456,4) value 23460
 
  
  

  
O: Select round (23456,4) value from dual 2346
 
  
  
 
  
⑥ E is the base power
 
  
  
 
  
S: Select exp (1) Value
 
  
  
 
  
O: Select exp (1) value from dual
 
  
  
 
  
7. Take the base logarithm of E.
 
  
  
 
  
S: Select log (7182818284590451) Value
 
  
  
 
  
O: Select Ln (7182818284590451) value from dual;
 
  
  
 
  
Returns the base 10 logarithm of a shard.
 
  
  

  
S: Select log10 (10) Value
 
  
  
 
  
O: Select log (10, 10) value from dual;
 
  
  
 
  
Returns the square of a shard.
 
  
  
 
  
S: Select square (4) Value
 
  
  
 
  
O: Select power (4, 2) value from dual
 
  
  
 
  
Returns the square root of a shard.
 
  
  
 
  
S: Select SQRT (4) Value
 
  
  
 
  
O: Select SQRT (4) value from dual
 
  
  
 
  
Evaluate the base power of any number
 
  
  

  
S: Select power (3, 4) Value
 
  
  
 
  
O: Select power (3, 4) value from dual
 
  
  
 
  
Random Number
 
  
  
 
  
S: Select rand () Value
 
  
  
 
  
O: select Sys. dbms_random.value (0, 1) value from dual;
 
  
  
 
  
Get the symbol
 
  
  
 
  
S: Select sign (-8) Value-1
 
  
  
 
  
O: Select sign (-8) value from dual-1
 
 
 

2. Numerical Comparison

 
 

  
  

  
① Calculate the maximum value of a 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
 
  
  
 
  
② Calculate the minimum value of a 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
 
  
  
 
  
③ How to handleNullValue (in the F2 FieldNullReplace with 10)
 
  
  
 
  
S: Select F1, isnull (F2, 10) value from TBL
 
  
  
 
  
O: Select F1, nvl (F2, 10) value from TBL
 
 
 

3. String Functions

 
 

  
  
 
  
① Calculate the character serial number
 
  
  

  
S: Select ASCII ('A') Value
 
  
  
 
  
O: Select ASCII ('A') Value from dual
 
  
  
 
  
② Calculate characters from the serial number
 
  
  
 
  
S: selectChar(97) Value
 
  
  
 
  
O: Select CHR (97) value from dual
 
  
  
 
  
③ Connection
 
  
  
 
  
S: select'11'+'22'+'33'Value
 
  
  

  
O: Select Concat ('11','22') | 33 value from dual
 
  
  
 
  
④ Substring position -- return 3
 
  
  
 
  
S: Select charindex ('S','Dsq', 2) Value
 
  
  
 
  
O: Select instr ('Dsq','S', 2) value from dual
 
  
  
 
  
⑤ 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
 
  
  
 
  
6. substring
 
  
  
 
  
S: Select substring ('Abcd', 2, 2) Value
 
  
  
 
  
O: Select substr ('Abcd', 2, 2) value from dual
 
  
  
 
  
7. substring substitution -- Return aijklmnef
 
  
  

  
S: select stuff ('Abcdef', 2, 3,'Ijklmn') Value
 
  
  
 
  
O: Select Replace ('Abcdef','Bcd','Ijklmn') Value from dual
 
  
  
 
  
Trim left space filling (the 1st parameters of lpad are spaces, which is the same as the space function)
 
  
  
 
  
S: Select space (10) +'Abcd'Value
 
  
  
 
  
O: Select lpad ('Abcd', 14) value from dual
 
  
  
 
  
Trim right fill space (the 1st parameters of rpad are spaces, which is the same as the space function)
 
  
  

  
S: select'Abcd'+ Space (10) Value
 
  
  
 
  
O: Select rpad ('Abcd', 14) value from dual
 
  
  
 
  
Comparative pronunciation similarity (the two words return the same value and have the same pronunciation)
 
  
  
 
  
S: Select soundex ('Smith'), Soundex ('Smy')
 
  
  
 
  
O: Select soundex ('Smith'), Soundex ('Smy') From dual
 
  
  

  
SQL Server uses select difference ('Smithers','Smythers')
Returns 0 to compare the soundex difference ~ 4, 4 is the same tone, 1 is the highest
 
 
 

4. Date Functions

 
 

  
  
 
  
① System time
 
  
  
 
  
S: Select getdate () Value
 
  
  
 
  
O: Select sysdate value from dual
 
  
  
 
  
② Calculate the date
 
  
  
 
  
S: Select convert (Char(10), getdate (), 20) Value
 
  
  
 
  
O: Select trunc (sysdate) value from dual
 
  
  

  
Select to_char (sysdate,'Yyyy-mm-dd') Value from dual
 
  
  
 
  
③ Time
 
  
  
 
  
S: Select convert (Char(8), getdate (), 108) Value
 
  
  
 
  
O: Select to_char (sysdate,'Hh24: mm: ss') Value from dual
 
  
  
 
  
④ Last day of the month
 
  
  
 
  
S: Unknown
 
  
  
 
  
O: Select last_day (sysdate) value from dual
 
  
  
 
  
⑤ One day of the week (such as Sunday)
 
  
  

  
S: Unknown
 
  
  
 
  
O: Select next_day (sysdate, 7) vaule from dual;
 
  
  
 
  
⑥ String Conversion time
 
  
  
 
  
S: Select cast ('2017-09-08'AsDatetime) Value
 
  
  
 
  
O: Select to_date ('2017-01-05 22:09:38','Yyyy-mm-dd Co., hh24-mi-ss')
Vaule from dual;
 
  
  
 
  
7. Calculate the difference of a part of the date (for example, the second)
 
  
  
 
  
S: Select datediff (SS, getdate (), getdate () + 3) Value
 
  
  

  
O: Subtract directly with two dates (for example, d1-d2 = 3)
 
  
  
 
  
Select (d1-d2) * 24*60*60 vaule from dual;
 
  
  
 
  
Seek a new date (for example, minute) based on the difference value)
 
  
  
 
  
S: Select dateadd (MI, 8, getdate () Value
 
  
  
 
  
O: Select sysdate + 8/60/24 vaule from dual;
 
  
  
 Differences between Oracle statements and SQL Server statements  
 
1. How to Implement Auto-increment columns in Oracle
 
 
If you have always used SQL Server for database development and new projects require Oracle databases, you will suddenly find that SQL Server and Oracle are still very different. First of all, the self-increasing fields that I use in SQL Server cannot be found in Oracle. Instead, Oracle can be implemented using sequences. Example.
 
      
    

       
     
 
     
(1) create a test data table.
 
       
     
 
     
Create Table Test
 
       
     
 
     
(
 
       
     

     
ID number,
 
       
     
 
     
Name varchar2 (20 ),
 
       
     
 
     
Primary Key (ID)
 
       
     
 
     
);
 
       
     
 
     
(2) create a sequence.
 
       
     
 
     
Create sequence seq_test;
 
       
     
 
     
(3) create a trigger.
 
       
     
 
     
Create or replace trigger autoincrement
 
       
     
 
     
Before insert on Test
 
       
     
 
     
For each row
 
       
     
 
     
When (New. ID is null)
 
       
     
 
     
Begin
 
       
     
 
     
Select seq_test.nextval into: New. ID from dual;
 
       
     

     
End;
 
       
     
 
     
(4) Insert data.
 
       
     
 
     
Insert into test (name) values ('Name1');
 
       
     
 
     
(5) view the insert result.
 
       
     
 
     
Select * from test;
 
      
    
 
2. parameter differences
 
 
In SQL Server, we can use the SQL statement as follows: "insert into table (field1, field2) values (@ value1, @ value2)", and then we will create several new paramters: new sqlparameter ("@ value1", value )...
 
 
The @ + character is used in the query string to describe the parameter. The parameter name in sqlparameter must also use the "@" symbol. In Oracle, SQL statements cannot use the @ symbol instead of the colon ":", for example:
 
       
     


        
      
-- SQL statement of SQL Server

        
      
Insert into table (column1, column2) values (@ value1, @ value2)

        
      
 

        
      
-- SQL statement in cmdel

        
      
Insert into table (column1, column2) values (: value1,: value2)

       
     
 
 
3. Different stored procedures
 
 
The stored procedure in Oracle is called packages. A package is divided into a packet header and a package body, similar to class declaration in C ++. The header defines the name and parameters of a stored procedure. Besides the name and parameters, the package body also includes all statements of the stored procedure. Unlike SQL Server, stored procedures in Oracle are generally written as functions rather than procedure. Oracle stored procedures do not directly return record sets. Record Sets are returned through parameters in the form of cursors. A package can contain multiple stored procedures. stored Procedure name: The following is a typical Oracle stored procedure, which is located in the packages named "test". Its usage should be test. getlist.
 
      
    

       
     

     
Function getlist (keywords in varchar2, p_info_list_cursorOut 
Get_data_cur_type)
 
       
     
 
     
Return number
 
       
     
 
     
As
 
       
     
 
     
Begin
 
       
     
 
     
Open p_info_list_cursor
 
       
     
 
     
Select * from test where key = keywords
 
       
     
 
     
;
 
       
     
 
     
Return 0;
 
       
     
 
     
End;
 
       
     

     
Solution for ASP. NET connection to Oracle failure 
After the ASP. NET Oracle client is installed, ASP. NET ApplicationsProgramUse System. data. if this error occurs when the oracleclient accesses the Oracle database: system. exception: system. data. oracleclient requires Oracle client software version 8.1.7 or greater. however, on the same machine, the winform client can access the database normally.
The reason is:
When the Oracle 9i client is installed in the NTFS partition of windows, the Security Authentication Settings are incorrect. As a result, the authenticated users user on the local machine cannot see the contents in the ORACLE_HOME directory. net to authenticated users permission to use system. data. the preceding error is reported when the oracleclient connects to the Oracle database.
The solution is as follows:
(1) log on as an administrator.
(2) Find the ORACLE_HOME folder (my name is c: \ oracle \ ora92), right-click it, and choose "properties"> "security" from the shortcut menu, select the "Authenticated Users" option in the group or user column, remove the "read and run" permission from the permission list below, and then click the "application" button; reselect the "read and run" option, click the "application" button, click the "advanced" button under the permission box, and select the application following the "Authenticated Users" option, select the "folder, subfolders, and files" option and click "OK" to apply the permission change to the folder.
(3) restart the computer to make the permission settings take effect (this step is important ).
(4) log on and run the ASP. NET application to connect to the Oracle database.