MySQL stored procedures Explained

1. Database stored procedures: Simply put, a stored procedure is a program stored in a database.

2. The database stored procedure functions:

First: The stored procedure is running faster because the SQL statement has been pre-programmed Yi.

HTML code

1. Second: Stored procedures can accept parameters, output parameters, return single or multiple result sets, and return values. You can return the cause of the error to the program.
2. Third: Stored procedures run relatively stable, there will not be too many errors. Once successful, this program will be run later.
3. IV: Stored procedures are mainly run on the server, reducing the pressure on the client.
4. Five: Stored procedures can contain program flow, logic, and queries to the database. Data logic can also be encapsulated and hidden by entities.
5. Sixth: Stored procedures can execute a series of SQL statements in a single stored procedure.
6. Seventh: Stored procedures can reference other stored procedures from within their own stored procedures, which simplifies a series of complex statements.
7. In addition, if multiple SQL statements are executed, the process link returns the data as the input data for the subsequent link, if directly through the
8. SQL statement execution, which is bound to cause a large amount of data to be returned to the client through the network, and in the client operations, if encapsulated in a stored procedure,
9. The operation is placed on the server, not only reduces the pressure of the client, but also reduces the network traffic and improves the efficiency of the execution.

3.mysql Stored procedures:

MySQL5 stored procedures, the use of people do not seem to be many. According to the database design principle, the stored procedure is precompiled on the DB server, so the query speed will be much faster compared to the pure SQL statement. This may be the current trend of OO, which leads to the use of stored procedures greatly discounted. However, in effect, using stored procedures to implement business rules with DB server pressure is less stressful than implementing business rules with Java classes. Of course, stored procedures should not be abused, such as ordinary insert, UPDATE and other statements do not need to use stored procedures.

Okay, here we are. MySQL creates procudure syntax that differs greatly from SQL Server/oracle.
Example 1: procedure of incoming parameters

CREATE PROCEDURE usp_test (param varchar (20) SELECT * FROM Talbename where Column=param

Example 2: Updating the procedure of a table

CREATE PROCEDURE Usp_test2 (t varchar (20)) Begin Set xname = ' Test '; Update table Set column = XName where column1=t; End

MySQL creates stored procedures without an as, and () cannot be omitted, even if there are no incoming parameters. This is as straightforward as SQL Server or Oracle, and when declaring a variable, use declare without adding the @ or @@ (except in the Out variable). There is also a very strange syntax, if the "select" as the beginning of the stored procedure, is not added "Begin End". The "Begin End" represents the complex of multiple SQL statements.

Of course, virtually no database is fully compliant with the SQL3 standard, and much of it contains some of its own components, which also results in the use of stored procedures that can reduce the program's ease of movement.

Call procedure
MySQL uses the call keyword. Example: Call Usp_test (' test '), rather than execute, the same () cannot be omitted.
Once you have created procedure, see how Java calls procedure.
Connection conn = null;

Java code

2. CallableStatement cstmt = null;
4. ResultSet rs = null;
6. try{
8. conn = Dbconn.getdbconn (); //get Pool Conn
10. CallableStatement cstmt = Conn.preparecall ("{call Usp_test (?)}");
12. Call.setstring (1, "test");
14. rs = Call.executequery ();
16. While (Rs.next ()) {
18. String te = rs.getstring (1);
20. System.out.println ("TE:" +te);
22. }
24. }catch (Exception e) {
26. System.out.println ("e:" +e);
28. }finally{
30. try{
32. Rs.close ();
34. Cstmt.close ();
36. Conn.close ();
38. }catch (Exception ex) {
40. SYSTEM.OUT.PRINTLN ("ex:" +ex);
42. }
44. }

This uses "{Call Usp_test ()}" to invoke the stored procedure. It is also possible to programmatically pass in parameters for querying.
One of the fatal drawbacks of this approach is that the parameters passed in are determined by the writing of the stored procedure. This means that multi-parameter dynamic queries like PreparedStatement are not supported.

The following first describes the MySQL stored procedure input and output parameter application. Examples can be run directly from copy.
1. Input parameters (also default parameters)
CREATE PROCEDURE usp1 (in P INT)
BEGIN
SET @x = p;
END;
Call USP1 (123456);
Select @x
Returns a result of 123456
2. Output parameters (out)
CREATE PROCEDURE usp2 (out P int, in P2 int)
BEGIN
SET p =-5 + p2;
END
Call P6 (@y,10)
Select @y
Returns a result of 5
3. Input and output parameters (InOut)

CREATE PROCEDURE Demosp (in Inputparam VARCHAR (255), INOUT Inoutparam INT) BEGIN SET Inoutparam = 1000; SELECT Inoutparam; SELECT CONCAT (' Zyxw ', inputparam); END

Call DEMOSP (' Test ', @q)
Returns the result "Zyxw test".

MySQL call stored procedure syntax is finished, summed up is three parameters of the key values in, out, INOUT, respectively, representing input parameters, output parameters, input and output parameters. Here's how Java calls a stored procedure with an output parameter.
First create a procedure with an output parameter

CREATE PROCEDURE Demosp (out Inoutparam varchar (50)) BEGIN Set inoutparam = "Hello procedure"; END

It is then called in Java.

CallableStatement cstmt = Conn.preparecall ("{Call DEMOSP (?)}"); Cstmt.registeroutparameter (1, Java.sql.Types.VARCHAR); Boolean i = Cstmt.execute (); String x = cstmt.getstring (1); SYSTEM.OUT.PRINTLN ("Call Result:" +i+x);

It is obvious that a registeroutparameter is more than a stored procedure with no output parameters.
In other words, the type of the output parameter is first registered, execute execute (), and the value of the car-out parameter is finally retrieved.
This process cannot be reversed.

MySQL stored procedures Explained