Oracle Stored Procedures

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 it's the problem of being afraid of being in conflict with the keyword as in stored procedures 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 another matter.

  Select Af.keynode into KN from Appfoundation af where Af.appid=aid and af.foundationid=fid;-- have into, correctly compiled  select AF . Keynode from Appfoundation af where Af.appid=aid and af.foundationid=fid;-- without into, compile error, hint: compilation   Error:pls 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 into KN from A Ppfoundation af where Af.appid=appid and af.foundationid=foundationid;-- operation phase error, hint Ora-01422:exact fetch Returns more than requested number of rows

5. In the stored procedure, there is a problem with null
Suppose there is a table A, which is defined as follows:

CREATE TABLE A (ID varchar2 (null, vcount number (null, bid varchar2 (  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 a record of bid= "xxxxxx" does not exist 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 still becomes null), This can be problematic later when using Fcount, so it's a good idea to check here first:

if NULL then    fcount:=0if;

That's all OK. 6.Hibernate calling Oracle Stored Procedures

         This. Pnumbermanager.gethibernatetemplate (). Execute (NewHibernatecallback () { PublicObject Doinhibernate (Session session)throwshibernateexception, SQLException {callablestatement cs=session. Connection (). Preparecall ("{Call Modifyapppnumber_remain (?)}"); Cs.setstring (1, Foundationid);                        Cs.execute (); return NULL; }                });

Transferred from: http://www.cnblogs.com/chuncn/archive/2009/01/29/1381291.html

Oracle stored Procedures (RPM)