本帖最后由 唐伯虎(0) 于 2019-7-18 18:46 编辑1.1存储过程、函数介绍- 存储过程和函数区别:存储过程没有返回值,而函数必须有;存储过程的参数可以使用IN,OUT,INOUT类型,而函数的参数只能是IN类型。- 存储过程特点  - 能完成较复杂的判断和运算,而且处理逻辑都封装在数据库端,调用者不需要自己处理业务逻辑,一旦逻辑发生变化,只需要修改存储过程即可,而对调用者程序完全没有影响。  - 可编程性强,灵活  - SQL编程的代码可重复使用  - 执行速度相对快一些  - 减少网络之间数据传输,节省开销1.2创建存储过程-     -- 创建存储过程      DELIMITER $$      CREATE PROCEDURE testa()      BEGIN      SELECT * FROM student WHERE id=2;      END $$      -- 调用存储过程      call testa();1.3存储过程/函数的查、删- 查      -- 查看存储过程或者函数的状态      SHOW PROCEDURE STATUS LIKE 'testa';      -- 查看存储过程或者函数的定义      SHOW CREATE PROCEDURE testa- 删      -- 删除存储过程      DROP PROCEDURE testa1;      -- 删除函数      DROP FUNCTION testa1;1.4存储过程的变量- 需求: 编写存储过程,使用变量取id=2的用户名;变量my_uname      DELIMITER $$      CREATE PROCEDURE testa3()      BEGIN      -- 1.变量的声明使用declare,一句declare只声明一个变量,变量必须先声明后使用      -- 2.变量具有数据类型和长度,与mysql的SQL数据类型保持一致,因此甚至还能指定默认值、字符集和排序规则等      DECLARE my_uname VARCHAR(32) DEFAULT '';      -- 3.变量可以通过set来赋值,也可以通过select into的方式赋值      SET my_uname='itheima';      SELECT NAME INTO my_uname FROM student WHERE id=2;      -- 4.变量需要返回,可以使用select语句,如:select 变量名      SELECT my_uname;      END $$      CALL testa3();- 变量作用域  - 变量是有作用域的,作用范围在begin与end块之间,end结束变量的作用范围即结束。  - .需要多个块之间传递值,可以使用全局变量,即放在所有代码块之前。  - 传参变量是全局,可以在多个块之间起作用1.5存储过程传入参数 IN型- 例      -- 需求:编写存储过程,传入id,返回该用户的name      DELIMITER $$      CREATE PROCEDURE getName(my_uid INT)      BEGIN      DECLARE my_uname VARCHAR(32) DEFAULT '';      SELECT NAME INTO my_uname FROM student WHERE id=my_uid;      SELECT my_uname;      END;      $$      CALL getName(2);  - 传入参数:类型为IN,表示该参数的值必须在调用存储过程时指定,如果不显式指定为IN,那么默认就是IN类型。  - IN类型参数一般只用于传入,在调用存储过程中一般不作修改和返回  - 如果调用存储过程中需要修改和返回值,可以使用OUT类型参数1.6存储过程传出参数OUT型- 例      -- 需求:调用存储过程时,传入uid返回该用户的uname      DELIMITER $$      CREATE PROCEDURE getName22(IN my_uid INT,OUT my_uname VARCHAR(32))      BEGIN      SELECT NAME INTO my_uname FROM student WHERE id=my_uid;      SELECT my_uname;      END;      $$            -- 指定传入参数变量      SET @uname:='';      CALL getName22(2,@uname);      -- 起别名      SELECT @uname AS myName;  - 传出参数:在调用存储过程中,可以改变其值,并可返回  - OUT是传出参数,不能用于传入参数值  - 调用存储过程时,OUT参数也需要指定,但必须是变量,不能是常量  - 如果既需要传入,同时又需要传出,则可以使用INOUT类型参数1.7存储过程可变参数INOUT型- 例:      -- 需求:调用存储过程时,参数my_uid和my_uname,既是传入,也是传出参数      DELIMITER $$      CREATE PROCEDURE getName3(INOUT my_uid INT,INOUT my_uname VARCHAR(32))      BEGIN      SET my_uid=2;      SET my_uname='hxf3';      SELECT id,NAME INTO my_uid,my_uname FROM student WHERE id=my_uid;      SELECT my_uid,my_uname;      END;      $$                  SET @uname:='';      SET @uid:=0;      CALL getName3(@uid,@uname);      SELECT @uname AS myName;  - 可变变量INOUT:调用时可传入值,在调用过程中,可修改其值,同时也可返回值。  - INOUT调用时传入的是变量,而不是常量1.8存储过程条件语句- 例:      -- 需求:编写存储过程,如果用户uid是偶数则就给出uname,其它情况只返回uid      DELIMITER $$      CREATE PROCEDURE getName44(IN my_uid INT )      BEGIN      DECLARE my_uname VARCHAR(32) DEFAULT '';      IF(my_uid%2=0)      THEN      SELECT NAME INTO my_uname FROM student WHERE id=my_uid;      SELECT my_uname;      ELSE      SELECT my_uid;      END IF;      END;      $$                  CALL getName44(1);      CALL getName44(2);  - 条件语句最基本的结构:if() then …else …end if;1.9存储过程循环语句- while循环  - 例:        -- 需求:使用循环语句,向表users(uid)中插入10条uid连续的记录。        DELIMITER $$        CREATE PROCEDURE insertdata()        BEGIN        DECLARE i INT DEFAULT 0;        WHILE(i< 10) DO        BEGIN        SELECT i;        SET i=i+1;        INSERT INTO users(NAME , address) VALUES("孙悟空" , "广州");        END ;        END WHILE;        END;                        $$        CALL insertdata();    - while语句最基本的结构:while() do…end while;    - while判断返回逻辑真或者假,表达式可以是任意返回真或者假的表达式- repeat循环语句  - 例:        -- 需求:使用repeat循环向表users插入10条uid连续的记录                DELIMITER $$        CREATE PROCEDURE insertdata2()        BEGIN        DECLARE i INT DEFAULT 100;        REPEAT        BEGIN        SELECT i;        SET i=i+1;        INSERT INTO users(NAME) VALUES('黑马');        END ;        UNTIL i >= 110        END REPEAT;        END;        $$                CALL insertdata3();    - repeat语句最基本的结构:repeat…until …end REPEAT;    - until判断返回逻辑真或者假,表达式可以是任意返回真或者假的表达式只有当until语句为真时,循环结束。 1.10光标(游标)基本使用- 在存储过程和函数中,可以使用光标(有时也称为游标)对结果集进行循环的处理- 基本使用  - 申明光标:cursor for  - 打开光标:open  - 移动光标:fetch  - 关闭光标:close- 例:      -- 编写存储过程,使用光标,把id为偶数的记录逐一更新用户名。      DELIMITER $$      CREATE PROCEDURE testcursor()      BEGIN      -- 控制光标循环结束标记      DECLARE stopflag INT DEFAULT 0;      DECLARE my_uname VARCHAR(20);      -- cursor for 申明光标      DECLARE uname_cur CURSOR FOR SELECT NAME FROM student WHERE id%2=0 ;      DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;      OPEN uname_cur; -- 打开游标      FETCH uname_cur INTO my_uname; -- 游标向前走一步,取出一条记录放到变量my_uname中。          WHILE( stopflag=0 ) DO -- 如果游标还没有到结尾,就继续          BEGIN          UPDATE student SET NAME=CONCAT(my_uname,'_cur') WHERE NAME=my_uname;          -- 游标向前走一步,取出一条记录放到变量my_uname中。          FETCH uname_cur INTO my_uname;          END ;          END WHILE;      CLOSE uname_cur;      END;      $$      DELIMITER ;            -- 调用      CALL testcursor()  - 注意:变量,条件,处理程序,光标,都是通过DECLARE定义的,它们之间是有先后顺序要求的,变量和条件必须在最前面声明,然后才能是光标的申明,最后才可以是处理程序的申明。 




image.png