MySQL存储过程，触发器，游标

语法：

1.存储过程：
   create PROCEDURE name(argment_list)
              begin
                    sql_statement;
              end

2.触发器：
   create trigger name (before|after) (insert | update | delete) on table_name
              for each row
                    begin
                            sql_statement;
                    end

3.游标:
   declare cursor_name cursor for select statement;


4.其他语法
  declare var_name int; 声明变量
  set var_name = 5; 赋值

  -- 或者
  set @var_name = 5

 

例子：

CREATE PROCEDURE `procedure_name`()
    BEGIN
        Declare found boolean default true;
        Declare name varchar(20);
        Declare names_cursor cursor for SELECT name FROM user;
        DECLARE CONTINUE HANDLER FOR NOT FOUND set found=false;

        open names_cursor;
        
        name_loop:LOOP fetch names_cursor into name;
            if found then
               sql_statement;
            else
                leave name_loop;
            end if;
        end LOOP name_loop;
        close names_cursor;
    END

 

create trigger trigger_name before insert on user
    for each row 
    begin 
        set @userid = NEW.userid; 
        set @name = NEW.name; 
        set @count= (select count(userid) from user where userid=@userid and name = @name); 
        if @count=1 then 
            -- this user already exists,throw exception
            insert into Error_message_ChannelIdToClientMustBeUnique values(1);
        end if; 
    end