欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-06-04 08:42:00
...
语法:

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 [email protected] and name = @name); 
        if @count=1 then 
            -- this user already exists,throw exception
            insert into Error_message_ChannelIdToClientMustBeUnique values(1);
        end if; 
    end

 

 

 

相关标签: MySQL