Oracle learning-stored procedure (2) Note

Stored Procedures for oracle Learning (2) Note 1. in oracle, the alias of a data table cannot be added as [html] select. appname from appinfo a; -- select a correctly. appname from appinfo as a; -- Error 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. [Html] select af. keynode into kn from APPFOUNDATION af where af. appid = aid and af. foundationid = fid; -- with into, select af is compiled correctly. keynode from APPFOUNDATION af where af. appid = aid and af. foundationid = fid; -- no into, Compilation Error, prompt: Compilation Error: PLS-00428: an INTO clause is expected in this SELECT statement 3. in the use of select... into... make sure 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... into... 4. in the stored procedure, the alias cannot be the same as the field name, however, in the running stage, [html] select keynode into kn from APPFOUNDATION where appid = aid and foundationid = fid; -- run select af correctly. keynode into kn from APPFOUNDATION af where af. appid = appid and af. foundationid = foundationid; -- an error occurs during the runtime, prompting ORA-01422: exact fetch returns more than requested number of rows 5. in the stored procedure Assume that there is A table A, which is defined as follows: [html] create table A (id varchar2 (50) primary key not null, vcount number (8) not null, bid varchar2 (50) not null -- foreign key); If the stored procedure uses the following statement: [html] select sum (vcount) into fcount from A where bid = 'xxxxxx'; If the bid = "xxxxxx" record 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: [html] if fcount is null then fcount: = 0; end I F; then everything is OK. 6. hibernate calls the oracle stored procedure [html] 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 ;}});