存儲(chǔ)過(guò)程

        1、創(chuàng)建

        delimiter // --修改分隔符，否則將以;作為結(jié)束符 create procedure myProc() –創(chuàng)建存儲(chǔ)過(guò)程 begin

        select * from salary ; end

        //

        delimiter ; --考慮使用習(xí)慣，將分隔符改為;

        2、執(zhí)行存儲(chǔ)過(guò)程

        3、創(chuàng)建帶IN參數(shù)的存儲(chǔ)過(guò)程

        delimiter //

        create procedure myProc1(IN id int ) —用IN表示該參數(shù)為輸入?yún)?shù)

        begin

        select * from one where oneId=id; end

        //

        delimiter ;

        set @id=13 ;--設(shè)置參數(shù)值

        call myproc1(@id);--調(diào)用存儲(chǔ)過(guò)程

        4、IN參數(shù)不能修改參數(shù)值 delimiter // create procedure myProc2(IN p_in int)

        begin

        select p_in ; set p_in=2;--設(shè)置輸入?yún)?shù)值 select p_in; end

        //

        delimiter ;

        call myProc2(@p_in);--在存儲(chǔ)過(guò)程中p_in參數(shù)值會(huì)改變

        存儲(chǔ)過(guò)程執(zhí)行完后，參數(shù)值p_in 并沒(méi)有改變。

        5、帶OUT參數(shù)的存儲(chǔ)過(guò)程

        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參數(shù)一起使用

        delimiter //

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

        begin

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

        end

        //

        delimiter ;

        設(shè)置輸入?yún)?shù)id

        SET @id=13;

        調(diào)用參數(shù)過(guò)程

        call myProc4(@id,@rowCount);

        7、INOUT參數(shù)

        delimiter //

        create procedure myProc5(INOUT p_inout int)

        begin

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

        //

        delimiter ;

        --執(zhí)行存儲(chǔ)過(guò)程之前

        call myProc5(@p_inout);

        --執(zhí)行存儲(chǔ)過(guò)程之后

        8、存儲(chǔ)過(guò)程的應(yīng)用

        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