Oracle Stored Procedure (2)-call of Stored Procedure

The use of any language or technology is the final principle. Why should we use stored procedures? Not applicable? Is there anything better than a stored procedure ?, With these series of problems, we can enter the use stage of the stored procedure.
1. Stored Procedure without return values

create or replace procedure AddNewUser       (n_id      user_info.id%TYPE,        n_name    user_info.name%TYPE,        n_pwd     user_info.pwd%TYPE,        n_address user_info.address%TYPE        ) is begin  INSERT INTO user_info    (id, name, pwd, address)  VALUES    (n_id, n_name, n_pwd, n_address);end AddNewUser;

2. Stored Procedures with returned values (non-list)

create or replace procedure count      (username in varchar2,       userpassword in varchar2,        userCount out integer       ) as       begin          select count(*) into userCount from USER_INFO where NAME = username and PWD = userpassword;       end count;

Stored Procedures with return values for JDBC calls:

Package COM. citichy; import Java. SQL. callablestatement; import Java. SQL. connection; import Java. SQL. drivermanager; import Java. SQL. types; public class testprocedure {public void test () {connection con = NULL; try {class. forname ("oracle. JDBC. driver. oracledriver "); con = drivermanager. getconnection ("JDBC: oracle: thin: @ 127.0.0.1: 1521: orcl", "wepull", "wepull"); // set the SQL statement to be executed, if a stored procedure is executed, the format of the stored procedure is as follows: Cal Lablestatement CST = con. preparecall ("{call check_user (?,?,?)} "); Cst. setstring (1, "zhangxuan"); Cst. setstring (2, "123"); // set the value of the output parameter in the Stored Procedure Cst. registeroutparameter (3, types. integer); cst.exe cute (); // After the stored procedure is executed, the output parameter value int testprint = CST is obtained. getint (3); system. out. println (testprint);} catch (classnotfoundexception e) {e. printstacktrace ();} catch (sqlexception e) {e. printstacktrace ();} finally {try {If (null! = Con) {con. Close () ;}} catch (sqlexception e) {e. printstacktrace ();}}}}

 Note that the proc. the value 2 in getstring (2) is not arbitrary, but corresponds to the out column in the stored procedure. If the out column is in the first position, it is Proc. getstring (1). If it is the third position, it is Proc. getstring (3), of course, you can also have multiple return values at the same time, that is, add a few more out parameters.

3. Stored Procedures with returned values (list)

Since the Oracle stored procedure does not return values, all of its return values are replaced by the out parameter, and the list is no exception. However, because it is a set, general parameters cannot be used, you must use pagkage. therefore, it must be divided into two parts,
(1) create a package

create or replace package testpackage   as     type test_cursor is ref cursor;   end testpackage;

Type test_cursor is ref cursor ----- create the cursor "cursor" pointer type variable named test_cursor;

(2) Stored Procedure

create or replace procedure testc(p_cursor out testpackage.test_cursor)is   begin      open p_cursor for select * form user_info;   end testc;

(3) JDBC calls stored procedures with returned lists

Package COM. citichy; import Java. SQL. callablestatement; import Java. SQL. connection; import Java. SQL. drivermanager; import Java. SQL. types; public class testprocedure {public void test () {connection con = NULL; resultset rs = NUL; try {class. forname ("oracle. JDBC. driver. oracledriver "); con = drivermanager. getconnection ("JDBC: oracle: thin: @ 127.0.0.1: 1521: orcl", "wepull", "wepull"); // set the SQL statement to be executed, if the stored procedure is executed Callablestatement CST = conn. preparecall ("{call hyq. testc (?)} "); Cst. registeroutparameter (1, Oracle. JDBC. oracletypes. cursor); // Cst. registeroutparameter (1, types. cursor); cst.exe cute (); // After the stored procedure is executed, obtain the value of the output parameter rs = (resultset) Proc. getObject (1); While (RS. next () {system. out. println ("<tr> <TD>" + Rs. getstring (1) + "</TD> <TD>" + Rs. getstring (2) + "</TD> <TD>" + Rs. getstring (3) + "</TD> </tr>") ;}} catch (classnotfoundexception e) {e. printstacktrace ();} catch (sqlexception E) {e. printstacktrace ();} finally {try {If (null! = Con) {con. Close () ;}} catch (sqlexception e) {e. printstacktrace ();}}}}

OK. You can understand the writing and calling of the stored procedure.

1. What is a stored procedure.
A stored procedure is an SQL assembly on the database server. It has two types. A SELECT query is used to retrieve data. The retrieved data can be returned to the customer in the form of a dataset. The other is similar to an insert or delete query. It does not return data, but only executes an action. Some servers allow the same stored procedure to return both data and perform actions.
Ii. When to use stored procedures
If the server defines a stored procedure, you should decide whether to use the stored procedure as needed. Stored procedures are usually tasks that are frequently executed. These tasks are usually performed for a large number of records. Executing stored procedures on the server can improve the performance of applications. This is because: A. servers often have powerful computing power and speed; B. avoid downloading a large amount of data to the client and reduce the amount of network transmission.
For example, assume that an application needs to compute a piece of data, which involves many records. If the stored procedure is not used, download the data to the client, resulting in a sharp increase in network traffic.
In addition, the client may be an old computer, and its operation speed is very slow. After switching to a stored procedure, the server will quickly calculate the data and transmit only one data to the client, which is very efficient.

Advantages:

1. Pre-Compilation: the stored procedure is pre-compiled and stored in the database to reduce the time spent on compiling statements.

2. cache: Pre-compiled stored procedures will enter the cache. Therefore, for frequently executed stored procedures, apart from the first execution, the execution speed of other times will be significantly improved.

3. reduce network transmission: especially for some data processing stored procedures, you do not have to send data to the Client Multiple times as you directly use SQL statements.

4. high maintainability: updating stored procedures usually requires less time and effort than modifying, testing, and deploying applications.

5. code reuse: a reusable stored procedure can be applied to multiple application locations.

6. Enhanced Security: by authorizing users to access stored procedures, they can provide access to specific data and improve data security to prevent SQL injection.

Disadvantages:

1. If you need to change the input and output parameters of a stored procedure, you also need to change the program.

2. Poor Portability: Because stored procedures bind the application's business processing to the database, using stored procedures to process the business logic limits the portability of the application.
Iii. stored procedure parameters
To execute the stored procedure on the server, some parameters are often required. These parameters are divided into four types:
The first type is the input parameter, which is passed by the customer program to the stored procedure.
The second type is the output parameter. The stored procedure returns the result to the client program.
The third type is the input/output parameter, which can be used to pass a value to a stored procedure or to return a result to the client program.
The fourth type is the state parameter. The stored procedure returns an error message to the client program.
Note that not all servers support the preceding four types of parameters. For example, Interbase does not support status parameters.