MySql存储过程

1.基本语法

CREATE PROCEDURE sel_emp(参数列表)
    BEGIN
     --操作
  END;

2.使用

无参的存储过程:
CREATE PROCEDURE sel_emp()
    BEGIN
     select * from emp where deptno = 10;
  END;
--存储过程的调用
call sel_emp();

有参的存储过程:
--根据部门编号查询员工信息
  CREATE PROCEDURE sel_emp2(dno int)
  BEGIN
    select * from emp where deptno=dno;
  END;
 --调用存储过程
  call sel_emp2(30);

--根据员工编号查询员工的名称
  CREATE PROCEDURE sel_emp3(eno int,OUT name varchar(20))
  BEGIN
    select ename into name from emp where empno=eno;
  end;
  --调用
  call sel_emp3(7788,@name);
  select @name;

--根据员工编号查询所在部门的编号
  CREATE PROCEDURE sel_emp4(INOUT eno int)
  BEGIN
    select deptno into eno from emp where empno = eno;
  END;
    
  --调用
  set @eno = 7788;
  call sel_emp4(@eno);
  select @eno;

--分支语句
CREATE PROCEDURE cal_score(score int)
        BEGIN
        -- 声明变量
        DECLARE levels varchar(20);
        -- 分支
                IF score >= 90 THEN
          -- 赋值
          set levels = '优秀';
        ELSEIF score >= 80  THEN
          set levels = '良好';
        ELSE
          set levels = '不通过';
        END IF;
        -- 输出
        select levels;
END;

--while循环
create PROCEDURE calc()
  BEGIN
    -- 声明两个变量
    DECLARE sum int;
    DECLARE count int;
    -- 初始化
    set sum = 0;
    set count = 1;
    -- 循环
    while count <=100 DO
      set sum = sum + count;
      set count = count + 1;
    END WHILE;

    SELECT sum;
  END;

  --LOOP
  create PROCEDURE calc1()
  BEGIN
    -- 声明两个变量
    DECLARE sum int;
    DECLARE count int;
    -- 初始化
    set sum = 0;
    set count = 1;
    -- 循环
   lip:LOOP
      set sum = sum + count;
      set count = count + 1;
      IF count > 100 THEN
        LEAVE lip;
      END IF;
   END LOOP;
    SELECT sum;
  END;
    
call calc1();

create PROCEDURE calc3()
  BEGIN
    -- 声明两个变量
    DECLARE sum int;
    DECLARE count int;
    -- 初始化
    set sum = 0;
    set count = 1;
    -- 循环
   REPEAT
      set sum = sum + count;
      set count = count + 1;
      UNTIL count > 100
   END REPEAT;
    SELECT sum;
  END;

  

3.参数模式

in:外部传进存储过程

out:传出

inout:传进传出

4.游标

--查询所有员工的姓名
  create PROCEDURE emp_cursor4()
  BEGIN
    DECLARE name varchar(20);
    DECLARE DONE boolean default 0;
    -- 声明游标类型变量存储所有员工的名称
    DECLARE emp_cursor CURSOR for select ename from emp;  
    -- 结束设置状态码为1  
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET DONE = 1;  
    -- 打开游标
    open emp_cursor;
    -- 获取游标中维护的值
    lip:LOOP
      FETCH emp_cursor into name;
      IF DONE THEN
        leave lip;
      END IF;
      select name;
    END LOOP;
    -- 关闭游标
    close emp_cursor;
  END;