In-depth discussion on several issues of Oracle database Stored Procedures

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; -- with into, correct compilation

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 message "Compilation" is displayed.

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 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; -- an error is reported during the running stage, prompting

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 (

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 in the stored procedure:

Select sum (vcount) into fcount from A where bid = xxxxxx; If bid = "xxxxxx" does not exist in Table A, fcount = null (even if the default value is set during fcount definition, 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(cmd.exe cute (

New HibernateCallback ()...{

Public Object doInHibernate (Session session)

Throws HibernateException, SQLException ...{

CallableStatement cs = session

. Connection ()

. PrepareCall ("{call modifyapppnumber_remain (?)} ");

Cs. setString (1, foundationid );

Cs.exe cute ();

Return null;

}

});