mysql-存储过程笔记-zfzfzz

SELECT * from employees;
-- 无参
create PROCEDURE method1 ()
begin
    insert into employees (last_name) values('zf1');
    insert into employees (last_name) values('zf2');
    insert into employees (last_name) values('zf3');
    insert into employees (last_name) values('zf4');
    insert into employees (last_name) values('zf5');
end

create PROCEDURE method2(in lastName varchar(20))
BEGIN
    INSERT into employees (last_name) value (lastName);
end

ALTER PROCEDURE method4 (in lastName varchar(20))
BEGIN
        SELECT * from employees where last_name like lastName;
end

create PROCEDURE method5 (in lastName varchar(20),in firstName varchar(20))
BEGIN
    DECLARE count1 int DEFAULT 0;
    SELECT count(*) into count1 from employees where last_name like lastName or first_name like firstName;
    SELECT count1;
end

create PROCEDURE method7 (in lastName varchar(20),in firstName varchar(20))
BEGIN
    DECLARE count1 int DEFAULT 0;
    SELECT count(*) into count1 from employees where last_name like lastName or first_name like  CONCAT('%',firstName,'%') ;
    SELECT count1;
end

call method7 ('张飞','a');

create PROCEDURE methodOut1 (in lastName varchar(20),out firstName varchar(20))
BEGIN
    SELECT first_name into firstName from employees where last_name like CONCAT('%',lastName,'%');
end;

set @fName;-- 可省去
call methodOut1('Kochhar',@fName);-- 输出值存入变量
SELECT @fName;

create PROCEDURE methodOut2 (in lastName varchar(20),out firstName varchar(20),out email varchar(20))
BEGIN
    SELECT e.first_name,e.email into firstName,email from employees e where last_name like CONCAT('%',lastName,'%') limit 1;
end;

call methodOut2('Kochhar',@fName,@email);-- 多个输出
select @fName,@email;

-- 错误
-- create PROCEDURE methodInOut1(inout a int,inout b int)
-- BEGIN
--     SELECT a*2 into a, b*2 into b;
-- end

create PROCEDURE methodInOut2(inout a int,inout b int)
BEGIN
    set a= a*2;
    set b= b*2;
end

set @num1 = 4;
set @num2 = 5;
call methodInOut2(@num1,@num2);
SELECT @num1,@num2;

-- 删除
drop PROCEDURE if EXISTS method1;

-- 查看 show create PROCEDURE method1;

-- 修改，无法修改sql，可以删了重新添加。

-- 格式化日期
drop PROCEDURE if exists dateFormat;
create PROCEDURE dateFormat(in inDate Datetime,out outDate varchar(20))
BEGIN
    SELECT    DATE_FORMAT(inDate,'%y年%m月%d日') into outDate;
end;

CALL dateFormat( '1989-1-1',@outDate);
SELECT @outDate;