Basic oracle Stored Procedure syntax

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
Note: is can also be changed to

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;
Single Cycle:
LOOP
Statements
End loop;
// Keep loop statement segment, unless the displayed input EXIT/exit when statement ends the loop

WHILE loop:
WHILE condition LOOP
Statements
End loop;

Example:
Count: = 0;
WHILE counter <6 LOOP
Count: = count + 1;
End loop;
5. for Loop

FOR loop_variable IN [REVERSE] lower_bound... upper_bound LOOP
Statements;
End loop;

Example:
FOR id IN 3 .. 6 LOOP
DBMS_OUTPUT.PUT_LINE (id );
End loop;
6. Variable assignment
V_TEST: = 123;
Variable name: variable type; //: = [initial value];
Id INTEGER;
Name VARCHAR2 (20): = 'binming ';
* Proname product. price % TYPE; // (% TYPE) indicates that the TYPE of proname must be the same as the TYPE of price in the product table.

7. 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;

8. 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;

9. 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
 
 
10. Block Structure
PL/SQL programs are divided into blocks that contain PL/SQL program statements. Typical PL/SQL blocks have
The following structure:
[DECLARE
Declaration_statements
]
BEGIN
Executable_statements
[EXCEPTION
Prediction_handling_statements
]
END;
Syntax element:
Declaration_statement:
Declares the variables used in the rest of the block. These variables are block local variables.
Executable_statment:
The actual executable statement of the block.
Exeception_handling_statement:
Handle possible errors of executable statements.

Note: Each statement must END with a semicolon (;), and the block must END with the END keyword.

Routine:
DECLARE
Width INTEGER: = 2;
Height INTEGER;
Area INTEGER;
BEGIN
Height: = 3;
Area: = width * height;
END;
/// Indicates executing this PL/SQL block;
 
Note: This block must be written together and cannot be separated.
 
11. Use of cursors

Step 1: declare a variable to store the sample value:

DECLARE
Id products. id % TYPE;

Step 2: declare the cursor:

The cursor must be placed in the Declaration section.

CURSOR product_cursor IS
Select
Id, name, price
FROM
Products
ORDER
Id;

// Declares the type of the cursor or the method

Step 3: Open the cursor:
OPEN the cursor using the OPEN statement, which must be placed in the executable part of the block.

OPEN product_cursor;

Step 4: Obtain rows from the cursor:
Use the FETCH statement to read rows in the cursor:
FETCH:
Product_cursor;
INTO
Id, name, price; // store the value in the three variables declared above.
// If the cursor returns a result that may contain many rows, each row of data will be retrieved cyclically,
You can use product_cursor % NOTFOUND to determine when the illusion ends.

Step 5: Close the cursor:
CLOSE product_cursor;
 
12. Execute the stored procedure in the Command window
Command: exec stored procedure name
 
 
 
 
Questions about oracle Stored Procedure
 
1. in oracle, the data table alias cannot be added with as, for example:
 
Select a. appname from appinfo a; -- 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; -- with into, correct compilation
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 message "Compilation" is displayed.
Error: PLS-00428: an 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 (*) from to check 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; -- an error is reported during the running stage, prompting
ORA-01422: exact fetch returns more than requested number of rows
 
5. the null issue occurs during the stored procedure.
Assume that table A is defined as follows:
Create table (
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 A 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 (){
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;
}
});