Examples of easy-to-use Mysql stored procedures and Java calls

Simple Mysql Stored Procedure example and Java call ???? I read an article about Mysql Stored Procedure blog yesterday. The link is as follows :???? My. oschina. netu1264926blog199831 ???? I tried to run it and reported an error all the time. After finding it for a long time, I found that the expression value of the SET variable name should be used for Mysql Stored Procedure assignment.

Simple Mysql Stored Procedure example and Java call ??? ? I read an article about Mysql Stored Procedure blog yesterday. The link is as follows :???? Http://my.oschina.net/u/1264926/blog/199831 ???? I tried to run it and reported an error all the time. After finding it for a long time, I found that the value of the Mysql stored procedure should be SET variable name = expression value. For a long time, I have not saved Mysql

Mysql simple Stored Procedure getting started example and Java call

??? ? I read an article about Mysql Stored Procedure blog yesterday. The link is as follows:

???? Http://my.oschina.net/u/1264926/blog/199831

???? I tried to run it and reported an error all the time. After finding it for a long time, I found that the Mysql stored procedure should be assigned a value using the SET variable name = expression value. For a long time there was no Mysql stored procedure, and I forgot a lot of things, instead, I wrote this blog article, I used the database version Mysql 5.6.14 and used the Navicat Premium graphic interface. The first is my reference link:

????

http://www.cnblogs.com/jevo/p/3271359.htmlhttp://phpzf.blog.51cto.com/3011675/793775

??? Next, I will introduce the Mysql stored procedure. I will not write the syntax and so on. please google. My stored procedure is to accumulate the sum between 1 and limit, so we need to use a loop, mysql stored procedures commonly used Loop statements include: While, Loop, Repeat. The following describes how to write them one by one:

??? (1) first, use the While loop (WHILE ...... DO ...... End while)

???

Create procedure proc_mysql_getsum_bywhile (in v_limit int, out sum int) begin declare I int default 0; set sum = 0; while I
 
  
??? Here, I would like to say that Mysql does not have printing statements like DBMS_OUT.PUT_LINE of Oracle. to print the results, use the select variable.
  
?? While loop test:
  
???
  
set @limit=100;set @out=0;call proc_mysql_getsum_bywhile(@limit,@out);select @out
  
?? (2) repeat loop (REPEAT ...... End repeat)
  
??
  
create procedure proc_mysql_getsum_byrepeat(in v_limit int,out sum int)begin   declare i int default 0;   set sum=0;   repeat       begin      set sum=sum+i;      set i=i+1;       end;      until i>v_limit   end repeat;   /**select sum;**/end;
  
??? Repeat test:
  
???
  
set @limit=100;set @out=0;call proc_mysql_getsum_byrepeat(@limit,@out);select @out
  
??? (3) loop
  
???
  
create procedure proc_mysql_getsum_byloop(in v_limit int,out sum int)begin   declare i int default 0;   set sum=0;   loop_label:loop        begin        set sum=sum+i;        set i=i+1;      if i>v_limit then             leave loop_label;        end if;        end;   end loop;   /**select sum;**/end;
  
??? Loop Test:
  
???
  
set @limit=100;set @out=0;call proc_mysql_getsum_byloop(@limit,@out);select @out
  
??? The above describes a simple stored procedure with input and output. The following describes the stored procedure of getUserById, which is similar to the above.
  
??
  
create procedure proc_mysql_inout_test(in v_id int,out username varchar(20))begin   select username into username from user_t2 where id = v_id;    /**select username;**/end;
  
??? In out parameter test:
  
??? Navicat query interface test:
  
???
  
call proc_mysql_inout_test(2,@out);select @out
  
??? The return value is very strange. The result is Blob.
  
??

??? Test the Navicat command line: The returned gbk-encoded string is directly selected * from user_t2; without garbled characters, as shown below:
  
???

??? There is no garbled code in the cmd command line test, as shown below:
  
???

??? How can I write an SQL statement in a stored procedure? See the example:
  
???? Test creating a table and filling in values:
  
???
  
Drop PROCEDURE tables; create procedure tables (IN loop_times INT) begin declare var int default 0; prepare msql from 'create table if not exists mysql_employee (id INT (10) not null AUTO_INCREMENT, empname VARCHAR (16) not null comment ''name'', hiredate timestamp default CURRENT_TIMESTAMP, primary key (id) ENGINE = innodb default charset = utf8'; execute msql; deallocate prepare MSQL; WHILE var
   
    
??? Test
???
    
call proc_mysql_createtb_insert_data(10);select * from mysql_employee;
    
??
    
?? When you want to modify the Mysql stored procedure, you must first delete it before creating it. The deletion method is as follows:
    
??
    
drop procedure proc_mysql_getsum_bywhile
    
?? To view the stored procedure of a database, follow these steps:
    
??
    
select name from mysql.proc where db='test'
    
?? If I want to return a cursor like an Oracle stored procedure, how can I write it? Unfortunately, Mysql does not support writing Out ref_cur cursor or the like, you can create a temporary table in the stored procedure and delete the temporary table at the end. For more information, see create a new table.
    
?? Another method is to directly select the content without writing the returned results, as shown below:
    
??
    
CREATE PROCEDURE proc_mysql_return_cursor_method() beginselect * from user_t2;end;
    
??? The test method is as follows:
    
???
    
call proc_mysql_return_cursor_method();
    
??? Next, I will briefly introduce how to call the Mysql stored procedure in Java. If you are not interested, you don't have to look down.
    
??? The first is the public method:
    
???
    
Public Connection getMysqlConnection () {String driver = "com. mysql. jdbc. driver "; String url =" jdbc: mysql: // localhost: 3306/test "; // name of the database to be operated String username =" root "; // database username String password = "123"; // password return getConnection (driver, url, userName, password);} public Connection getConnection (String driver, String url, String username, string passwd) {Connection conn = null; try {Class. forName (driver); conn = DriverManager. getConnection (url, userName, passwd);} catch (Exception e) {e. printStackTrace ();} return conn ;}
    
?? Taking the while loop I wrote as an example, enter the int parameter and output the int parameter:
    
??
    
Public void testMysqlProcedureRtnInt (Connection con, CallableStatement cs, int limit) throws Exception {cs = con. prepareCall ("{call proc_mysql_getsum_bywhile (?,?)} "); // Set the cs parameter. setInt (1, limit); // register the output parameter cs. registerOutParameter (2, oracle. jdbc. oracleTypes. INTEGER); // Execution Process cs.exe cute (); // obtain the result int result = cs. getInt (2); System. out. println ("result:" + result );}
    
?? Input int. The method for outputting varchar data is similar:
    
??
    
Public void testMysqlProcedureRtnVarchar (Connection con, CallableStatement cs, int id) throws Exception {cs = con. prepareCall ("{call proc_mysql_inout_test (?,?)} "); // Set the cs parameter. setInt (1, id); // register the output parameter cs. registerOutParameter (2, oracle. jdbc. oracleTypes. VARCHAR); // Execution Process cs.exe cute (); // obtain the result String result = cs. getString (2); System. out. println ("result:" + result );}
    
??? Let's take a look at the returned calls similar to the cursor type:
    
???
    
Public void testMysqlProcedureRtnCursor (Connection con, CallableStatement cs, ResultSet rs) throws Exception {cs = con. prepareCall ("{call proc_mysql_return_cursor_method ()}"); // The Execution Process rs = cs.exe cuteQuery (); System. out. println ("id" + "\ t" + "username" + "\ t" + "passwd"); while (rs. next () {System. out. println (rs. getInt (1) + "\ t" + rs. getString (2) + "\ t" + rs. getString (3 ));}}
    
??? It's easy.
    
??? This article is original. For more information, see the source. Thank you.
    
???? The full text is complete.
    
??
    
???