1. Basic Structure
CREATE OR REPLACE PROCEDURE
(
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 result of the select query to a variable. Multiple columns can be stored in multiple variables at the same time. One
Record; otherwise, an exception is thrown (if no record exists, NO_DATA_FOUND is thrown)
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
V_TEST: = 123;
6. Use cursor with for in
...
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 SP call code in the window, F9 start debug, CTRL + N single-step debugging
Some Questions about Oracle stored procedures 1. in oracle, data table aliases cannot be added with as, for example:
Select A. appname From Appinfo; -- Correct
Select A. appname From Appinfo As A; -- Error
Maybe you are afraid of conflicts with the keyword as in the oracle stored procedure.
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; -- Correct compilation with
Select Af. keynode From APPFOUNDATION af Where Af. appid = Aid And Af. foundationid = Fid; -- If there is no into, an error is reported during Compilation. The prompt is Compilation.
Error: PLS - 00428 : INTO Clause Is Expected In This SELECT Statement
3. When using the select... into... syntax, you must first ensure that this record exists in the Database; otherwise, an "no data found" exception is reported.
You can use Select count (*) fromCheck whether the record exists in the Database. If yes, use select......
4. In the stored procedure, the alias cannot be the same as the field name. Otherwise, although the compilation is successful, an error is reported during the running stage.
Select Keynode Into Kn From APPFOUNDATION Where Appid = Aid And Foundationid = Fid; -- Run properly
Select Af. keynode Into Kn From APPFOUNDATION af Where Af. appid = Appid And Af. foundationid = Foundationid; -- Error in running stage, prompt
ORA - 01422 : Exact Fetch Returns More than requested Number Of Rows5. the null issue occurs during the stored procedure.
Assume that table A is 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 use the following statement in the stored procedure:
Select sum (vcount) into fcount from Where Bid = ' Xxxxxx ' ;If bid = "xxxxxx" is not found in Table A, fcount = null (even if the default value is set during fcount definition, for example, fcount number (8): = 0, fcount will still be null), so there may be problems when using fcount in the future, so it is best to judge here:
If Fcount Is Null Then
Fcount: = 0 ;
End If ;In this way, everything is OK.
6. Hibernate calls the oracle Stored Procedure
This .Pnumbermanager.gethibernatetemplate(cmd.exe cute (
New HibernateCallback () ... {
PublicObject doInHibernate (Session session)
ThrowsHibernateException, SQLException...{
CallableStatement cs=Session
. Connection ()
. PrepareCall ("{Call modifyapppnumber_remain (?)}");
Cs. setString (1, Foundationid );
Cs.exe cute ();
Return Null;
}
} );
