mysql預存程序快速入門

標籤：style   blog   color   使用   os   io   資料   for   

一、建立預存程序

建立預存程序(一)

create PROCEDURE pro1()BEGIN select ‘Hello World‘;END;//執行預存程序call proc1();

建立預存程序的基本形式

CREATE PROCEDURE sp_name([proc_parameter[,…]])[characteristic …] routine_body其中：sp_name:預存程序名稱proc_parameter:預存程序參數，可以為in,out,inout參數，其形式如下[in | out | inout ] param_name typecharacteristic參數有多個取值：LANGUAGE SQL:說明routine_body部分是由SQL語言的語句組成，這也是資料庫系統預設的語言。[NOT] DETERMINISTIC:指明預存程序的執行結果是否是確定的。DETERMINISTIC表示結果是確定的。每次執行預存程序時，相同的輸入會得到相同的輸出。NOT DETERMINISTIC表示結果是非確定的，相同的輸入可能得到不同的輸出。預設情況下是非確定的。…

建立預存程序(二)

create PROCEDURE proc2() COMMENT ‘This is Hello World Procedure!‘BEGIN select ‘Hello World‘;END;//查看預存程序select * from information_schema.ROUTINES t where t.ROUTINE_SCHEMA = ‘your_db_name’;//結果表中的ROUTINE_COMMENT顯示了proc2的說明。

二、刪除預存程序

刪除預存程序(一)

drop PROCEDURE pro1;//查看預存程序select * from information_schema.ROUTINES t where t.ROUTINE_SCHEMA = ‘your_db_name’;

刪除預存程序(二)

drop PROCEDURE if exists vehicle.proc2;//查看預存程序select * from information_schema.ROUTINES t where t.ROUTINE_SCHEMA = ‘your_db_name’;

三、 帶參數的預存程序

帶參數的預存程序

//功能是 查看指定名稱的預存程序是否存在drop PROCEDURE if EXISTS isExistsProc;create PROCEDURE isExistsProc(dbname varchar(10),procname varchar(10))begin     select count(*) from information_schema.ROUTINES t where t.ROUTINE_SCHEMA = dbname    and t.ROUTINE_NAME = procname into @pro_count;    select @pro_count as ‘procedure count‘;end;//調用預存程序call vehicle.isExistsProc(‘vehicle‘,‘proc2‘);

帶out參數的預存程序

drop PROCEDURE if EXISTS proc_add;create PROCEDURE proc_add(a int,b int,out sum int)BEGIN    set sum = a + b;END;call proc_add(1,2,@sum);select @sum;

四、變數的使用

drop PROCEDURE if EXISTS proc3;create PROCEDURE proc3()BEGIN    declare res int default 10;    select res;END;call proc3();

五、遊標的使用

drop PROCEDURE if exists proc4;create PROCEDURE proc4()BEGIN    DECLARE user_name varchar(10);    DECLARE v_password varchar(10);    DECLARE cur_user CURSOR 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;    select user_name as ‘name‘,v_password as ‘password‘;    CLOSE cur_user;END;call proc4();

六、一道綜合題

//寫一個預存程序，輸入表名，如果表存在，則返回1，否則返回0.如果表存在，則通過out參數將表的總行數返回
DROP PROCEDURE IF EXISTS getRowCountOfTable;create PROCEDURE getRowCountOfTable(tablename VARCHAR(10),OUT r_count int)BEGIN    declare t int default 0;    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;                select 1;    ELSE            select 0;    end if;END;

七、Jdbc調用sql

ApplicationContext ctx = new FileSystemXmlApplicationContext("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();  int res = 0;  if(rs.next())  res = rs.getInt(1);  if(res > 0)  {     System.out.println("table \"t_user\" is exists!");     int rowCount = 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(); }