Several Problems in Oracle Stored Procedure

The following articles mainly discuss several problems that may occur in Oracle stored procedures. The actual application proportion of Oracle stored procedures is still the majority, if you are curious, the following articles will unveil its mysteries. I hope you will gain some benefits after browsing.

1. in Oracle, the data table alias cannot be added with as, for example:

 
 

  
  
select a.appname from appinfo a; 
 
 

Correct

 
 

  
  
select a.appname from appinfo as a; 
 
 

Error

Maybe you are afraid of conflicts with the keyword as in the Oracle stored procedure.

2. In the stored procedure, when selecting a certain field, it must be followed by into. If the entire select record uses the cursor, it is another matter.

 
 

  
  
select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid; 
 
 

Correct compilation with

 
 

  
  
select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid; 
 
 

If there is no into, an error is reported during Compilation. The prompt is Compilation.

 
 

  
  
Error: PLS-00428: an INTO clause is expected in this Select statement  
 
 

3. When using the select... into... syntax, you must first ensure that this record exists in the Database; otherwise, an "no data found" exception is reported.

You can use select count (*) from to check whether the record exists in the Database. If yes, use select......

4. In the Oracle stored procedure, the alias cannot be the same as the field name. Otherwise, although the compilation is successful, an error is reported during the running stage.

 
 

  
  
select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid; 
 
 

Run properly

 
 

  
  
select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid; 
 
 

Error in running stage, prompt

 
 

  
  
orA-01422:exact fetch returns more than requested number of rows  
 
 

5. the null issue occurs during the stored procedure.

Assume that table A is defined as follows:

 
 

  
  
create table A(   
  
  
id varchar2(50) primary key not null,   
  
  
vcount number(8) not null,   
  
  
bid varchar2(50) not null  
 
 

Foreign key

); If you use the following statement during Oracle storage:

Select sum (vcount) into fcount from A where bid = 'xxxxxx'; if no bid = "xxxxxx" record exists in Table, fcount = null (even if the default value is set when fcount is defined, for example, fcount number (8): = 0 is still invalid, and fcount will still be null ), in this way, there may be problems when using fcount, so it is best to judge here:

 
 

  
  
if fcount is null then   
  
  
fcount:=0;   
 
 

End if; then everything is OK.

6. Hibernate calls the Oracle Stored Procedure

 
 

  
  
this.pnumberManager.getHibernateTemplate().execute(   
  
  
new HibernateCallback() ...{   
  
  
public Object doInHibernate(Session session)   
  
  
throws HibernateException, SQLException ...{   
  
  
CallableStatement cs = session   
  
  
.connection()   
  
  
.prepareCall("{call modifyapppnumber_remain(?)}");   
  
  
cs.setString(1, foundationid);   
  
  
cs.execute();   
  
  
return null;   
  
  
}   
  
  
});   
 
 

Additional reading:

Explain in detail how to plan and implement the stored procedure in DB2 9

Three differences between Oracle and SQL Server Models

Oracle 10g ASM experience

Differences between the statistical information of Oracle 9i and 10g in create index and rebuild index