带参数的存储过程:
这个是带参数的查询,调用方法是:call pro_getUserName("1");
DELIMITER//(必须要加这个DELIMITER,否则创建的时候会报错,切记)
CREATE PROCEDURE pro_getUserName(id VARCHAR(50))
BEGIN
DECLARE username VARCHAR(20);
select user_name as a into username from user_t where id = id ;
select username;
END
复制表的存储过程:
delimiter//
CREATE PROCEDURE pro_copy(id varchar(200))
BEGIN
insert INTO user_t_copy (id,user_name,password,age)
select id,user_name,password,age from user_t where id = id ;
end
//调用方法
call pro_copy("1")
函数的创建:
delimiter//(注意此处的“//”必不可少,少了就会报错)
create FUNCTION func_getUserName(idStr varchar(200)) RETURNS varchar(200)
BEGIN
DECLARE username VARCHAR(200);
select user_name into username from user_t where id = idStr;
RETURN username;
END
select func_getUserName("1")