Java-oracle Stored Procedure Knowledge puzzle

java-oracle Stored procedure knowledge Puzzle

A stored procedure is a collection of SQL statements that is saved to accept or return user-supplied arguments. In daily use, complex business logic and operation of the database are often encountered, which can be encapsulated by using stored procedures. You can define subroutines in the database, and then the handlers are stored on the database server and then called by name. One, characteristics: 1. Lifting Performance

Stored procedures are precompiled, optimized, stored in SQL memory, when used without recompiling, to improve productivity. 2. Reduce network traffic

The code of the stored procedure is stored directly in the database, the user calls through the name, reduces the network traffic, speeds up the execution speed. For example: More than millions of data queries, stored procedures paging more than other ways of paging faster than 3. Improve security

Stored procedures can reduce SQL injection attacks and improve the security of the system. The process of execution is also controlled by the user's identity, so users who do not have data manipulation rights can only store the data indirectly under permission control. 4. transaction processing mechanism

At the same time, the master-slave table and the data maintenance and validation of the championship, the stored procedure is more convenient, can effectively use the SQL transaction processing mechanism. 5. Separation Design coding and use

Using stored procedures, you can implement stored procedure design and coding work separately, as long as the stored procedure name, parameters, and return information tell the coder. 6. (disadvantage) not easy to transplant and modify

Using stored procedures to encapsulate business logic limits the portability of applications, and it is cumbersome to change the relevant code if the parameters are changed or the returned data and types need to be modified. Second, the grammatical structure

The complete process structure is as follows:

Create to replace procedure procedure name as

declaration statement segment; begin

Execute the sentence segment;

exception

Exception handling statement segment;

Give examples:

--Student Form studentcreate table student (

Sno Number (6),

Sname VARCHAR2 (25),

Pno Number (6) Primary key

);

--Stored procedure Create or replace procedure Stu_proc as

P_name varchar2 (n); begin

Select Sname into P_name from student where sno=1;

Dbms_output.put_line (P_name);

--Invokes the stored procedure call Stu_proc (); three, on the type of parameters, stored procedures roughly provide the following

1. parameter-free stored procedures

Defined

Create or replace procedure Stu_proc as pname varchar2; begin

Select Sname into PName from student where sno=1;

Dbms_output.put_line (pname);

The use method is: Call Stu_proc ();

2. process with input parameters only

Create or Replace procedure Stu_proc1 (pno in Student.sno%type) as PName varchar2 (a); begin

Select Sname into PName from student where sno=pno;

Dbms_output.put_line (pname);

Use the method: Call Stu_proc1 (' 001 ')

3. process with output parameters only

Create or Replace procedure stu_proc2 (PName out student.sname%type) as begin

Select Sname into PName from student where sno=1;

Dbms_output.put_line (pname);

This stored procedure cannot be invoked directly with call, and needs to be called in the Oracle function. Using the method: Call STU_PROC2 (name)

4. stored procedures with input and output

Create or Replace procedure stu_proc3 (pname out student.sname%type,pname out Student.sname%type) as begin

Select Sname into PName from student where sno=pno;

Dbms_output.put_line (pname);

Using the method: Call STU_PROC3 (name, ' 001 ') iv. exception handling for stored procedures

To improve the robustness of stored procedures and avoid running errors, you should include portions of exception handling when establishing stored procedures. Exceptions include predefined exceptions, predefined exceptions, and custom exceptions.

• Predefined Exceptions: System exceptions provided by Pl\sql

• Fee predefined exceptions: for handling Oracle errors unrelated to predefined exceptions

• Custom Exceptions: Handling Exceptions other than Oracle errors

How to use:

Create or Replace procedure Stu_proc6 (pno in Student.sno%type,pname out Student.sname%type)

Is

Begin

Select Sname into PName from student where sno=pno;

EXCEPTION

When No_data_found Then

Raise_application_error

( -20011, ' ERROR: does not exist! ');

End

Common Exception Handling:

named system exception	cause
Access_into_null	Defining objects
Case_not_found	The case does not contain the corresponding when, and the initialization of the collection element is not set
Collection_is_null	Collection element not initialized
Curser_already_open	Cursor already open
Dup_val_on_index	Duplicate values on the column corresponding to the unique index
Invalid_cursor	operate on an illegal cursor
Invalid_number	Embedded SQL statements cannot speak characters to change numbers
No_data_found	Using SELECT INTO does not return rows, or to apply an index table that is not initialized
Too_many_rows	Execute SELECT INTO with more than one row of result sets
Zero_divide	Divisor is 0
Subscript_beyond_count	Element The following table exceeds the maximum value of a nested table or Varray
Subscript_outside_limit	When using a nested class or Varray, specify the following table as a negative number
Value_error	Variable length is not sufficient to accommodate actual data when assigning values
Login_denied	Pl\sql an incorrect username password is supplied when the application is connected to Oracle
not_logged_on	Pl\sql applications access data without connecting Oracle data
Program_error	Pl\sql internal issues, you may need to reload the data dictionary
Rowtype_mismatch	The primary cursor variable is incompatible with the return type of the PLSQL cursor variable
Self_is_null	To invoke an object method on a Null object when the object type is used
Storage_Error	Memory space is exceeded when running Pl\sql
sys_invalide_id	Invalid ROWID string
Timeout_on_resource	Oracle waits for resource connection timeout

v. the difference between stored procedures and functions 1. on the definition

The name of the definition this is not said, one is function, one is procedure;

· The parameter list for the stored procedure has input parameters, output parameters, input and output parameters

· The parameter of the function only has an input parameter, and finally a return value is added. 2. On The return value

· The return value of the stored procedure, which can have multiple

· The return value of the function is only one 3. on call mode

· Stored procedures are invoked as follows: Exec, execute, statement block calls

· Functions can be invoked in either a function block or directly in SQL, such as the following:

Create or Replace function add_three_numbers

(

A number:=0,b number:=0,c number:=0

)

Return number is

Begin

return a+b+c;

End

Select Add_three_numbers (1,2,3) from dual; vi. transaction processing

1. Transactions are used to ensure that the data is consistent, either fully confirmed or canceled altogether.

2. When the file performs a transaction operation, Oracle will be able to add a lock on the table to prevent other users from changing the table. A row lock is also added to the action line to prevent other transactions from performing DML operations on the corresponding rows.

3. When performing a transaction commit or a transaction rollback, Oracle confirms that the transaction changes or rolls back the transaction, ends the transaction, deletes the savepoint, and releases the lock. vii. Reference

Oracle Stored Procedures

Http://wenku.baidu.com/view/e56d8071be1e650e52ea99a4.html?from=search