Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to use stored procedures in oracle. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

1. Basic structure

CREATE OR REPLACE PROCEDURE stored procedure name

(

Parameter 1 IN NUMBER

Parameter 2 IN NUMBER

) IS

Variable 1 INTEGER: = 0

Variable 2 DATE

BEGIN

END stored procedure name

2.SELECT INTO STATEMENT

Save the results of a select query into variables. Multiple columns can be stored in multiple variables at the same time. There must be one.

Record, otherwise throw an exception (throw NO_DATA_FOUND if there is no record)

Example:

BEGIN

SELECT col1,col2 into variable 1, variable 2 FROM typestruct where xxx

EXCEPTION

WHEN NO_DATA_FOUND THEN

Xxxx

END

...

3.IF judgment

IF V_TEST=1 THEN

BEGIN

Do something

END

END IF

4.while cycle

WHILE V_TEST=1 LOOP

BEGIN

XXXX

END

END LOOP

5. Variable assignment

V_TEST: = 123

6. Use for in to use cursor

...

IS

CURSOR cur IS SELECT * FROM xxx

BEGIN

FOR cur_result in cur LOOP

BEGIN

V_SUM: = cur_result. Listed as 1+cur_result. Column 2

END

END LOOP

END

7. Cursor with parameters

CURSOR C_USER (C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID

OPEN C_USER (variable value)

LOOP

FETCH C_USER INTO V_NAME

EXIT FETCH C_USER%NOTFOUND

Do something

END LOOP

CLOSE C_USER

8. Use pl/sql developer debug

Establish a Test WINDOW after connecting to the database

Enter the code to call SP in the window, and F9 starts debug,CTRL+N step debugging

Notes on some questions about oracle stored procedures

1. In oracle, the data table alias cannot be added with as, such as: select a.appname from appinfo a share-correct

Select a.appname from appinfo as a Ting Mui-error

Perhaps it is afraid of conflicts with the keyword as in stored procedures in oracle

two。 In a stored procedure, when you select a field, it must be followed by into, which is another matter if you select the entire record and use cursors. Select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- has into, compiled correctly

Select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- does not have into, compilation error, prompt: Compilation

Error: PLS-00428: an INTO clause is expected in this SELECT statement

3. Taking advantage of select...into... Syntax, you must first make sure that the record is in the database, otherwise a "no data found" exception will be reported. Before this syntax, you can use select count (*) from to check whether the record exists in the database, and if so, use select...into...

4. In the stored procedure, the alias cannot be the same as the field name, otherwise, although the compilation can pass, it will report an error at run time that the select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;-- runs correctly

An error is reported during the run time of select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid;--.

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

5. In a stored procedure, the question about the occurrence of null assumes that there is a table A defined as follows: create table A (

Id varchar2 (50) primary key not null

Vcount number (8) not null

Bid varchar2 (50) not null-Foreign key

);

If you are in a stored procedure, use the following statement: select sum (vcount) into fcount from A where bid='xxxxxx'

If the record for bid= "xxxxxx" does not exist in table A, fcount=null (even if the default value is set when fcount is defined, such as: fcount

Number (8): = 0 is still invalid, and fcount will still become null), so there may be problems when using fcount later, so it's best to judge here: if fcount is null then.

Fcount:=0

End if

That makes everything ok.

6.Hibernate calls the oracle stored procedure this.pnumberManager.getHibernateTemplate (). Execute (

New HibernateCallback (). {

Public Object doInHibernate (Session session)

Throws HibernateException, SQLException... {

CallableStatement cs = session

.connection ()

.prepareCall ("{call modifyapppnumber_remain}")

Cs.setString (1, foundationid)

Cs.execute ()

Return null

}

});

This is how to use stored procedures in oracle shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.