6 Questions about Oracle stored procedures

Moon Pale Wolf 2017-11-27 23:17:37

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

May conflict with the keyword as in the stored procedure in Oracle

in a stored procedure, when a field is select, it must be followed by into, and if you select the entire record, it is a different matter to use the cursor.

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;--no into, compile an error;

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 can not be the same as the field name, otherwise, although the compilation can pass, but in the runtime will be an error;

5. In the stored procedure, with regard to the problem of NULL, suppose that 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 in the stored procedure, use the following statement:

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

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;

That's all OK.

6.Hibernate calling Oracle Stored Procedures

This.pnumberManager.getHibernateTemplete (). Execute (

New Hibernatecallback () {

Public Object Doinhinernate (sessions session)

Throws hibernateexception,sqlexception{

CallableStatement cs=session.connection (). Preparecall ("{Call Modifyapppnumber_remain (?)}");

Cs.setstring (1,foundationid);

Cs.execute ();

return null;

}

} );