Oracle stored procedures and questions

Oracle stored procedures and questions 1. the overall structure of the create or replace procedure stored PROCEDURE name (parameter 1 in number, parameter 2 in number) IS variable 1 INTEGER: = 0; variable 2 DATE; BEGINEND stored PROCEDURE name 2. select into statement stores the select query results INTO variables. Multiple columns can be stored in multiple variables at the same time. One record is required; otherwise, an exception is thrown (if no record exists, NO_DATA_FOUND is thrown) example: BEGINSELECT col1, col2 into variable 1, variable 2 FROM typestruct where xxx; predictionwhen NO_DATA_FOUND THENxxxx; END ;... 3.IF judge IF V_TEST = 1 THENBEGIN do somethingEND; end if; 4. while loop WHIL E V_TEST = 1 LOOPBEGINXXXXEND; end loop; 5. variable value: V_TEST: = 123; 6. use cursor with for in... ISCURSOR cur is select * FROM xxx; BEGINFOR cur_result in cur LOOPBEGINV_SUM: = cur_result. column name 1 + cur_result. column name 2END; end loop; END; 7. cursorCURSOR C_USER (C_ID NUMBER) with parameters is select name from user where typeid = C_ID; OPEN C_USER (variable value); LOOPFETCH C_USER INTO V_NAME; exit fetch C_USER % NOTFOUND; do somethingEND LOOP; CLOSE C_USER; 8. use pl /SQL developer debug create a Test WINDOW after connecting to the database and enter the code for calling SP in the WINDOW. F9 start debug, CTRL + N one-step debugging several questions about oracle Stored Procedure 1. in oracle, the alias of a data table cannot be added as, for example, select. appname from appinfo a; -- select a correctly. appname from appinfo as a; -- an error may be caused by a conflict with the keyword as in the stored procedure in oracle. 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, select af is compiled correctly. keynode from APPFOUNDATION af where af. appid = aidand af. foundationid = fid; -- no into, compilation error, prompt: CompilationError: PLS-00428: anINTO clause is expectedin 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. during the stored procedure, the alias cannot be the same as the field name. Otherwise, although the compilation is successful, the select keynode into knfrom APPFOUNDATION where appid = aidand foundationid = fid will be reported during the runtime; -- run select af correctly. keynode into kn from APPFOUNDATION af where af. appid = appidand af. foundationid = foundationid; -- runtime error, prompting ORA-01422: exactfetch returns more than requestednumber of rows 5. in the stored procedure, assume that there is A table Create table A (id varchar2 (50) primary key not null, vcount number (8) not null, bid varchar2 (50) not null -- foreign key ); if the following statement is used in the stored procedure: 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 isnull thenfcount: = 0; end if; so that everything is OK. 6.hibernatecall the oraclestore process this.pnumbermanager.gethibernatetemplate(.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 ;}});