Common problems with Oracle stored procedures

1. In Oracle, the data table alias cannot be added as.

Such as:

Select A.appname from AppInfo a;--correct

Select A.appname from appinfo as a;--error

Perhaps, is afraid and Oracle in the stored procedure in the key word as conflict problem bar

2. In a stored procedure, when a field is select, it must be followed by into, and if you select the entire record, the cursor is a different story.

Select Af.keynode into KN

From Appfoundation AF

where Af.appid=aid and Af.foundationid=fid; --have into, compile correctly

Select Af.keynode

From Appfoundation AF

where Af.appid=aid and af.foundationid=fid;--do not into, compile an error, hint: compilation Error:pls-00428:an into clause is expected in This SELECT statement

3. In the use of select...into ... Syntax, you must first ensure that the record in the database, or you will report "No data Found" exception.

You can use SELECT COUNT (*) from to see if the record exists in the database before this syntax, and if it exists, reuse select...into ...

4. In the stored procedure, the alias cannot be the same as the field name, otherwise the compilation can be passed, but the error occurs at run time.

Select Keynode into kn from appfoundation where Appid=aid and Foundationid=fid;

--run correctly

Select Af.keynode into KN from Appfoundation af where af.appid=appid and Af.foundationid=foundationid;

--run Phase error, prompt:

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

5. In the stored procedure, the question of the occurrence of NULL

Suppose there is a table A, defined as follows:

CREATE TABLE A (

ID VARCHAR2 (m) primary key NOT NULL,

Vcount Number (8) is not NULL,

Bid varchar2 NOT null--foreign key

If the following statement is used in the stored procedure:

Select SUM (vcount) into Fcount from where bid= ' xxxxxx ';

If the record for bid= "xxxxxx" does not exist in table A, then fcount=null (even if the Fcount definition has a default value set, such as: Fcount number (8): =0 is still invalid, fcount will still be null), This may be a problem when you use Fcount, so it's best to decide here:

If Fcount is null then

fcount:=0;

End If;

So it's all OK.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

6.Hibernate calling Oracle Stored Procedures

This.pnumberManager.getHibernateTemplate (). Execute

New Hibernatecallback () ... {

Public Object Doinhibernate (sessions session)

Throws Hibernateexception, SQLException ... {

CallableStatement cs = Session

. Connection ()

. Preparecall ("{Call Modifyapppnumber_remain"? ）}"）；

Cs.setstring (1, Foundationid);

Cs.execute ();

return null;

}

}）；