MySQL stored procedures Quick Start

First, create a stored procedure

Create a stored procedure (a)

' Hello world '; END; // Execute stored procedure call Proc1 ();

Basic form of creating stored procedures

| Out | InOut] Param_name typecharacteristic parameter has multiple values: LANGUAGE sql: Description The Routine_body section consists of statements from the SQL language, which is also the default language for the database system. [NOT] Deterministic: Indicates whether the execution result of the stored procedure is deterministic. Deterministic indicates that the result is deterministic. The same input will get the same output each time the stored procedure is executed. Not deterministic indicates that the result is indeterminate, and the same input may get different output. is non-deterministic by default. ... ..

Create a stored procedure (ii)

Create PROCEDURE proc2 () COMMENT ' This was Hello World procedure! '  ' Hello world '; END; // view the stored procedure select * from INFORMATION_SCHEMA. ROUTINES t where T.routine_schema = ' your_db_name '; // The routine_comment in the results table shows the description of the PROC2. 

Second, delete stored procedures

Delete stored procedure (i)

drop PROCEDURE Pro1; // view the stored procedure select * from INFORMATION_SCHEMA. ROUTINES t where T.routine_schema = ' your_db_name ';

Delete a stored procedure (ii)

if exists vehicle.proc2; // view the stored procedure select * from INFORMATION_SCHEMA. ROUTINES t where T.routine_schema = ' your_db_name ';

Three, stored procedures with parameters

Stored procedure with parameters

// function is to see if a stored procedure with the specified name exists if EXISTS isexistsproc;create PROCEDURE isexistsproc (dbname varchar (ten), procname varchar)begin     Select COUNT (*) from INFORMATION_SCHEMA. ROUTINES t where T.routine_schema = dbname    = procname into @pro_count;    ' Procedure count '; end; // calling the stored procedure call vehicle.isexistsproc (' vehicle ', ' proc2 ');

Stored procedure with out parameters

Ifintintint) BEGIN    = a + b; End;call Proc_add (@sum); select @sum;

Iv. Use of variables

if EXISTS proc3;create PROCEDURE proc3 () BEGIN     int default ten;    Select Res; End;call proc3 ();

V. Use of cursors

if exists proc4;create PROCEDURE proc4 () BEGIN    DECLARE user_name varchar (ten);    DECLARE V_password varchar (ten);      for select Name,password from T_user;    OPEN Cur_user;    Fetch cur_user into user_name,v_password;#   fetch cur_user into User_name,v_password;    ' Name ', V_password as ' password ';    CLOSE Cur_user; End;call proc4 ();

Six or one-way comprehensive questions

//write a stored procedure, enter the table name, or return 1 if the table exists, otherwise return 0. If the table exists, the total number of rows in the table is returned by the out parameter
DROP PROCEDURE IF EXISTS getrowcountoftable;create PROCEDURE getrowcountoftable (tablename VARCHAR (Ten), Out R_countint) BEGIN declare tint default0; Select COUNT (*) from INFORMATION_SCHEMA. ' TABLES ' t where Table_schema = ' vehicle 'and table_name=tablename into t; if(T > 0) Then set @s= CONCAT (' Select COUNT (*) from ', TableName, ' into @rcount ');                PREPARE stmt from @s; Set @tname=TableName; Set @rcount= 0;                EXECUTE stmt;                Deallocate PREPARE stmt; Set R_count=@rcount; Select1; ELSE Select0; Endif; END;

VII. JDBC Calls SQL

ApplicationContext CTX =NewFilesystemxmlapplicationcontext ("Src/xml/dbconfig.xml"); Basicdatasource DataSource= (Basicdatasource) ctx.getbean ("DataSource"); Connection Conn=NULL; CallableStatement callstmt=NULL;Try{conn=datasource.getconnection (); Callstmt= Conn.preparecall ("{Call getrowcountoftable (?,?)}"); Callstmt.setstring (1, "T_user"); Callstmt.registeroutparameter (2, Types.integer);  Callstmt.execute (); ResultSet RS=Callstmt.getresultset (); intres = 0; if(Rs.next ()) Res= Rs.getint (1); if(Res > 0) {System.out.println ("Table \" T_user\ "is exists!"); intRowCount = Callstmt.getint (2); System.out.println ("The count of rows in the table is" +rowCount); }  Else{System.out.println ("Table \" T_user\ "is not exists!"); }}Catch(SQLException e) {e.printstacktrace ();}