存储过程

        1、创建

        delimiter // --修改分隔符，否则将以;作为结束符 create procedure myProc() –创建存储过程 begin

        select * from salary ; end

        //

        delimiter ; --考虑使用习惯，将分隔符改为;

        2、执行存储过程

        3、创建带IN参数的存储过程

        delimiter //

        create procedure myProc1(IN id int ) —用IN表示该参数为输入参数

        begin

        select * from one where oneId=id; end

        //

        delimiter ;

        set @id=13 ;--设置参数值

        call myproc1(@id);--调用存储过程

        4、IN参数不能修改参数值 delimiter // create procedure myProc2(IN p_in int)

        begin

        select p_in ; set p_in=2;--设置输入参数值 select p_in; end

        //

        delimiter ;

        call myProc2(@p_in);--在存储过程中p_in参数值会改变

        存储过程执行完后，参数值p_in 并没有改变。

        5、带OUT参数的存储过程

        delimiter //

        create procedure myProc3(OUT rowCount int) begin

        select count(*) into rowCount from one ; end

        //

        delimiter ;

        select count(*) from one ;

        select @rowCount ;

        call myProc3(@rowCount);

        6、IN和OUT参数一起使用

        delimiter //

        create procedure myProc4(IN id int,OUT rowCount int)

        begin

        select count(*) from one where oneId=id ;

        end

        //

        delimiter ;

        设置输入参数id

        SET @id=13;

        调用参数过程

        call myProc4(@id,@rowCount);

        7、INOUT参数

        delimiter //

        create procedure myProc5(INOUT p_inout int)

        begin

        select p_inout; set p_inout = 2 ; select p_inout ; end

        //

        delimiter ;

        --执行存储过程之前

        call myProc5(@p_inout);

        --执行存储过程之后

        8、存储过程的应用

        create procedure AddHuman(IN hName varchar(20),IN hAddress varchar(20))

        begin

        DECLARE hId int ;

        set hId=0 ;

        select houseId into hId from house where houseAddress=hAddress ;

        insert into human(humanId,humanName,houseId)

        values(null,hName,hId);

        end

        create procedure AddHuman1(IN hName varchar(20),IN hAddress varchar(20))

        begin

        DECLARE hId int ;

        set hId=0 ;

        select houseId into hId from house where houseAddress=hAddress ;

        if hId >0 then

        insert into human(humanId,humanName,houseId)

        values(null,hName,hId);

        end if ;

        end