Oracle Stored Procedure memo

Document directory

• Questions about Oracle Stored Procedure

Stored Procedure creation Syntax:

Create or replace procedure stored procedure name (param1 in type, param2 out type)

As

Variable 1 type (value range );

Variable 2 type (value range );

Begin

Select count (*) into variable 1 from Table A where column name = param1;

If (condition) then

Select column name into variable 2 from Table A where column name = param1;

Dbms_output. Put_line ('print info ');

Elsif (Judgment condition) then

Dbms_output. Put_line ('print info ');

Else

Raise Exception name (no_data_found );

End if;

Exception

When others then

Rollback;

End;

 

Note:

1. stored procedure parameters do not have a value range. In indicates input, and out indicates output.

2. The variable has a value range followed by a semicolon

3. Use the count (*) function to determine whether the operation record exists before determining the statement.

4. Select... Into... Assign values to variables

5. If an exception is thrown in the code, use raise + Exception name.

 

 

An exception named after

Cause of naming system exception

Access_into_null: Undefined object

If no corresponding when exists in case_not_found case

Else time

Collection_is_null collection element not initialized

Curser_already_open cursor opened

The column corresponding to the unique index of dup_val_on_index has duplicate values.

Invalid_cursor operates on an invalid cursor

Invalid_number embedded SQL statements cannot convert characters into numbers

No_data_found: If select into is used, no rows are returned, or the application index table is not initialized.

 

When too_many_rows executes select into, the result set exceeds one row.

The zero_divide divisor is 0.

Subscript_beyond_count element subscript exceeds the maximum value of nested tables or varray

When subscript_outside_limit uses a nested table or varray, the subscript is specified as a negative number.

When value_error is assigned a value, the variable length is insufficient to accommodate actual data.

When the login_denied PL/SQL application connects to the Oracle database

Correct user name or password

The not_logged_on PL/SQL application is not connected to the oralce database.

Access Data

Program_error PL/SQL internal problems, you may need to reinstall the data dictionary & pl./SQL

System package

Rowtype_mismatch: the host cursor variable is incompatible with the PL/SQL cursor variable return type

When self_is_null uses the object type, the object method is called on the null object.

When storage_error runs PL/SQL, the memory space is exceeded.

Invalid sys_invalid_id rowid string

Timeout_on_resource Oracle timeout while waiting for resources
Bytes ---------------------------------------------------------------------------------------------------------------------

Basic syntax and precautions for Oracle stored procedures

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

 

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)
Throws hibernateexception, sqlexception {
Callablestatement cs = session
. Connection ()
. Preparecall ("{call modifyapppnumber_remain (?)} ");
CS. setstring (1, foundationid );
Cs.exe cute ();
Return NULL;
}
});