”当作结束标志(否则默认是把“;“号当作结束标记),这样存储过程中的语句结束符";"就不会当作过程的结束

记。

DELIMITER$$

CREATE PROCEDURE p1()

BEGIN

   SELECT * FROM stud;

   INSERT INTO stud(id,sname,age,score,dept) VALUES(1014,‘刘三丰‘,33,55,‘通信学院‘);

END$$

DELIMITER;   //把结束标记还原回来


CALL p1();  //调用存储过程p1

1、带参数的存储过程

DELIMITER$$

CREATE PROCEDURE p2(IN id INT, IN nm VARCHAR(30) )

BEGIN

   INSERT INTO stud(id,sname) VALUES(id,nm);

END$$

DELIMITER ; 

DROP PROCEDURE p2;

CALL p2(1015,‘吊丝‘);

2、有返回值的存储过程----参数与变量问题( @变量名  ，一个@为用户变量，两个@即 @@为全局的系统变量 )

DELIMITER$$

CREATE PROCEDURE p3(IN id INT, IN nm VARCHAR(30), OUT num INT )

BEGIN

   INSERT INTO stud(id,sname) VALUES(id,nm);

   SELECT COUNT(*) INTO num FROM stud;

END$$

DELIMITER ; 

CALL p3(1016,‘无名‘,@aa);

SELECT @aa; //输出变量aa的值

七、事务处理

START TRANSACTION

  DELETE FROM stud WHERE id=1015;

  DELETE FROM stud WHERE id=1014;

  SELECT * FROM stud;

ROLLBACK /  COMMIT;

◎采用事务的java编程

try{

    st.execute("START TRANSACTION;");

    st.execute("DELETE FROM stud WHERE id=1015;");

    st.execute("DELETE FROM stud WHERE id=1014;");

    ....

    st.execute(" commit();  ");

}catch(Exception e){

   rollback();
}

SQL learning

标签：