Oracle Stored Procedure Learning uses

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 (judging condition) then

Select column name into Variable 2 from table a where column name =param1;

Dbms_output. Put_Line (' Print information ');

elsif (judging condition) then

Dbms_output. Put_Line (' Print information ');

Else

Raise exception name (no_data_found);

End if;

Exception

When others then

Rollback;

End;

Precautions:

1, the stored procedure parameter does not take a range of values, in represents the incoming, out represents the output

2, variable with value range, followed by semicolon

3, it is best to use the count (*) function to determine if there is a record of the operation before judging the statement.

4, with Select ... Into ... Assigning values to variables

5, throwing exceptions in code with raise+ exception name

To name the exception

Named system exception causes

Access_into_null undefined Object

If the corresponding when is not included in the Case_not_found case and is not set

ELSE when

Collection_is_null collection Element not initialized

Curser_already_open Cursor already open

Dup_val_on_index duplicate values on a column corresponding to a unique index

Invalid_cursor operation on an illegal cursor

Invalid_number Embedded SQL Statements cannot convert characters to numbers

No_data_found using SELECT INTO not returning rows, or applying the index table uninitialized

Too_many_rows when performing a select INTO, the result set exceeds one row

Zero_divide Divisor is 0

Subscript_beyond_count element subscript exceeds the maximum value of a nested table or Varray

Subscript_outside_limit the subscript is specified as a negative number when using a nested table or Varray

When Value_error is assigned, the variable length is not sufficient to accommodate the actual data

Login_denied PL/SQL applications that connect to the Oracle database provide a

The correct user name or password

not_logged_on PL/SQL applications without a connection to the Oralce database

accessing data

Program_error PL/SQL Internal issues, you may need to reload the data dictionary & Pl./sql

System Package

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

Self_is_null calling an object method on a NULL object when using an object type

Storage_Error when running PL/SQL, out of memory space

SYS_INVALID_ID Invalid ROWID string

Timeout_on_resource Oracle timed out while waiting for resources
--------------------------------------------------------------------------------------------------------------- ------

Basic syntax and considerations for Oracle stored Procedures 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
Storing the results of a select query in a variable allows multiple columns to be stored in multiple variables at the same time and 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 Cycle
While V_test=1 LOOP
BEGIN
Xxxx
END;
END LOOP;

5. Assigning values to variables
V_test: = 123;

6. Using the 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 the 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. Developer Debug with PL/SQL
Create a test WINDOW after connecting to the database
Enter the code for the calling SP in the window, F9 start debug,ctrl+n single-Step debugging

Several issues with Oracle stored Procedures Memo 1. In Oracle, data table aliases cannot be added as, for example:
Select A.appname from AppInfo a;--correct
Select A.appname from appinfo as a;--error maybe, Is the problem of being afraid to conflict with the keyword as in a stored procedure in Oracle. 2. In a stored procedure, when a field is select, it must be followed by into, and if the entire record is select, the cursor is a matter of other things.
Select Af.keynode to 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;--not into, compile 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 make sure that the record is in the database, otherwise you will report "No data Found" exception.
Before this syntax, you can use SELECT COUNT (*) from to see if the record exists in the database, and if so, then use the Select...into ... 4. In the stored procedure, the alias cannot be the same as the field name, or although the compilation can pass, but at run stage will be error
Select Keynode to kn from appfoundation where Appid=aid and foundationid=fid;--run correctly
Select Af.keynode to kn from Appfoundation af where af.appid=appid and af.foundationid=foundationid;--run stage error, hint
Ora-01422:exact fetch returns more than requested number of ROWS5. In the stored procedure, the issue of NULL occurs
Suppose there is a table A, which is defined as follows:
CREATE TABLE A (
ID VARCHAR2 (primary) key NOT NULL,
Vcount Number (8) is not NULL,
Bid varchar2 () 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 no record of bid= "XXXXXX" is present 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, Fcount will become null), so the use of fcount later may have problems, so it is better to judge here first:
If Fcount is null then
fcount:=0;
End If; so everything is OK. 6.Hibernate calling Oracle Stored Procedures
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;
}
});Other Learning Links:1.Oracle Stored Procedure Basic syntax Introduction http://www.jb51.net/article/31805.htm

Oracle Stored Procedure Learning uses