Basic syntax for Oracle stored procedures

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
By saving the results of a select query into a variable, you can store multiple columns in multiple variables at the same time, you must have a
Record, otherwise throw an exception (if no record is thrown no_data_found)
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 Loop
While V_test=1 loop
BEGIN
Xxxx
End;
End LOOP;

5. Variable Assignment value
V_test: = 123;

6. Use cursor with for
...
Is
CURSOR cur is SELECT * from xxx;
BEGIN
For Cur_result in Cur loop
BEGIN
V_sum: =cur_result. Column name 1+cur_result. Column Name 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
Create a test WINDOW after connecting to the database
Enter the code for the calling SP in the window, F9 start debug,ctrl+n step

a few questions about Oracle stored procedures Memo1. 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; --no into, compile an error, hint: compilation
Error:pls-00428:an into clause are 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 a stored procedure, a problem with 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 in the stored procedure, use the following statement:
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.
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;
}
} );

Java Execution Stored Procedures

proc = Conn.preparecall ("{call HyQ.") Testa (?,?)} ");

Proc.setstring (1, "100");

Proc.setstring (2, "Testone");

Proc.execute ();