Oracle Stored Procedures

Never give up, everything is possible!!!

Only to find a way to succeed, not to find excuses for failure!

Oracle Stored Procedures

I. Description of stored Procedures

1) Description:

1. A stored procedure is a PL/SQL statement block for a specific operation

2. Stored procedures are pre-compiled, optimized and stored in SQL memory, the use of the need not to compile again, improve the efficiency of use;

3. Stored procedure code directly in the database, generally directly through the name of the stored procedure call, Reduce network traffic, speed up the efficiency of system execution;

2) The difference between a stored procedure and a function:

1. In general, the function of the stored procedure implementation is a bit more complex, and the function implementation of the function is relatively strong.

2. For stored procedures, parameters (output) can be returned, and functions can only return values or table objects.

3. The stored procedure is typically performed as a separate part, and the function can be called as part of a query statement, since the function can return a Table object, so it can be located after the FROM keyword in the query statement.

3) Advantages of the stored procedure:

1. Execute faster – Stored procedure statements stored in the database are compiled

2. Allow modular programming, more portability of programs-similar methods of reuse (using stored procedures can be implemented in the storage process design and coding work separately, as long as the stored procedure name, parameters, return information, etc. to tell the programmer);

3. Improve system security – Prevent SQL injection (the user executing the stored procedure must have permission to use the stored procedure)

4. Reduce network liquidity – as long as the name of the transfer stored procedure (using stored procedure paging queries in bulk data queries is much faster than paging in other ways)

5. The use of stored procedures facilitates the efficient use of transaction processing mechanisms in SQL, while simultaneously maintaining data maintenance and validation between masters and tables

second, the grammar

1) Create a stored procedure

CREATE[ or replaceprocedure< Span style= "color: #000000;" > procedure_name [ (Parameter1[model] datatype1, parameter2 [model ] Datatype2..)] is[as]begin Pl/sql; end [procedure_name< Span style= "color: #ff0000;" >]; 

Description

1. Parameter for specifying parameters, model for specifying parameter patterns, datatype for specifying parameter types

2. when defining parameters for a stored procedure, you can specify only the data type and cannot specify the data length

3. Is/as for starting PL/SQL code blocks

4. When creating a stored procedure, you can either specify parameters or not specify any parameters;

5. Stored procedure parameters: 1) input parameter in in to receive input parameters for the calling environment (when creating a stored procedure, the input parameter's in can be omitted)

2) output parameters out-out for passing output data to the calling environment

3) input and output parameters (in out) where in is used to receive input parameters of the calling environment, out is used to pass the output data to the calling environment

2) Delete stored procedures

PROCEDURE procedure_name;

3) Compiling stored procedures

PROCEDURE procedure_name COMPILE

Third, stored procedure calls

1) Description:

1. Stored procedures can be referenced directly in PL/SQL (call or EXECUTE commands are required when calling stored procedures in sql*plus);

2. When the stored procedure is called, if there is no parameter, then directly refer to the stored procedure name, if there are input parameters, you need to provide the input parameter values, if there are output parameters, you need to use variables to receive the output results;

3. Parameters are passed with position passing, name passing and combination passing three methods, three parameters are passed as follows:

DECLAREV_para1VARCHAR2 (10); V_para2 Nvarchar2 (10); v_para3VARCHAR2 (30); V_para4VARCHAR2 (30);BEGINV_PARA1:=‘123‘; V_PARA2:=‘456‘; V_PARA4:= ' 789 ; Usp_learing (V_PARA1,V_PARA2,V_PARA3,V_PARA4); -- location pass Usp_learing (p_para1 =>v_para1,p_para2=>v_para2,p_para3=>v_para3,p_para4=>v_para4); --=>v_para3,p_para4=>v_para4); --dbms_ Output.put_line (V_PARA3); Dbms_output.put_line (V_PARA4); end;             

2) Example of a stored procedure call

REPLACE isBEGIN dbms_output. Put_Line (sysdate); END print_time;   

1.pl/sql calls Print_time () directly in the PL/SQL code block.

2.sql*plus EXEC print_time ();

Iv. common data types in stored procedures

1) record (single-row multi-column)

2) Table (multiple rows and columns)

3) Nested tables (table) (Multiple rows and columns)

4) variable-length array (varry) (Multi-row column)

5) Common Table Expression (CTE)

V. Transaction processing in Stored procedures

1) Transaction Description:

1. Transactions are used to ensure data consistency, with a set of related DML statements, and the actions performed by the reorganization DML statements are either fully acknowledged or canceled altogether.

2. When performing a transaction operation of DML, Oracle locks on the table being manipulated to prevent other users from altering the table structure and also locks on the rows being performed to prevent other transactions from performing DML operations on the row

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

4. Commit TRANSACTION (commit) confirms transaction change, ends current transaction, deletes savepoint, releases lock, causes all pending data in current transaction to change permanently

5. Save Point (SavePoint) in the current transaction, mark the savepoint of the transaction

6. Rollback operation (rollback) rolls back the entire transaction, deletes all the savepoint in the transaction, releases the lock, discards all pending data changes
7. ROLLBACK to savepoint rollback to the specified save point

2) Description of the transaction in the stored procedure:

　1. Keep the transaction as short as possible.

2. Minimize the amount of data that is accessed as much as possible in a transaction

3) Example

CREATEORREPLACEPROCEDURETrancproIsBEGININSERTInto TAB1VALUES (‘Aa‘,‘1212‘,‘1313‘);COMMIT; SavePoint S1;INSERTInto TAB1VALUES (‘Bb‘,‘1414‘,‘1515‘); Dbms_transaction. SavePoint (‘S2‘);UPDATE TAB1SET SNO=‘1515‘WHERE ID= ' bb ; commitwhen dup_val_on_index then rollback to savepoint S1; Raise_application_error (-20010,  ' error: Violation of unique index constraints ); when OTHERS then  Rollback; END Trancpro;                

vi. Examples of stored procedures

1) Simple example--printing dates with stored procedures

REPLACE isBEGIN dbms_output. Put_Line (sysdate); END print_time;   

2) Example 2--contains input and output parameters

CREATEORREPLACEPROCEDUREPara_procedure (para1VARCHAR2:=‘ParaString1‘, Para2Varchar2Default‘ParaString2‘, para3 outVarchar2, PARA4In OutVarchar2)IsBEGINDECLAREPara5VARCHAR2 (20);BEGINPara5:= '  input and output parameters: " ||  Para4; Para3: = "  output parameters:  ' | | para1 | |  Para2; Para4: =PARA5;　　　　Dbms_output.put_line (PARA5); Dbms_output.put_line ( ' para4 is  ' | | para4); end; END para_procedure;               

Vii. Java Program calls

In this section, we call the stored procedure using the Java language. The key is to use the CallableStatement object, the code is as follows:

String oracledrivername = "Oracle.jdbc.driver.OracleDriver";//The test used below is the table space in Oracle String oracleurltoconnect = "JDBC:ORACLE:THIN:@127.0.0.1:1521:ORCL"; Connection myconnection =Null;Try{Class.forName (oracledrivername);}Catch(ClassNotFoundException ex) {Ex.printstacktrace ();}Try{myconnection =Drivermanager.getconnection (Oracleurltoconnect, "xxxx", "xxxx");// here is the database user name and password  "catch (Exception ex) {ex.printstacktrace ();} try { callablestatement proc=null Myconnection.preparecall (" {Call Xs_proc (?,?)} " ); Proc.setstring (1, "Zhangsan"  Registeroutparameter (2, types.numeric); Proc.execute (); String teststring=proc.getstring (2); System.out.println (teststring); } catch (Exception ex) {ex.printstacktrace ();} 
                 /span> 

Oracle Stored Procedures