精华内容
下载资源
问答
  • 存储函数
    千次阅读
    2022-03-20 15:22:45

    MySQL存储函数(自定义函数),函数一般用于计算和返回一个值,可以将经常需要使用的计算或功能写成一个函数。

    存储函数和存储过程一样,都是在数据库中定义一些 SQL 语句的集合。

    存储函数与存储过程的区别

    1.存储函数有且只有一个返回值,而存储过程可以有多个返回值,也可以没有返回值。

    2.存储函数只能有输入参数,而且不能带in, 而存储过程可以有多个in,out,inout参数。

    3.存储过程中的语句功能更强大,存储过程可以实现很复杂的业务逻辑,而函数有很多限制,如不能在函数中使用insert,update,delete,create等语句;

    4.存储函数只完成查询的工作,可接受输入参数并返回一个结果,也就是函数实现的功能针对性比较强。

    5.存储过程可以调用存储函数、但函数不能调用存储过程。

    6.存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用.

    create function func_name ([param_name type[,...]])
    returns type
    [characteristic ...] 
    begin
        routine_body
    end;
    

    参数说明:
    (1)func_name :存储函数的名称。
    (2)param_name type:可选项,指定存储函数的参数。type参数用于指定存储函数的参数类型,该类型可以是MySQL数据库中所有支持的类型。
    (3)RETURNS type:指定返回值的类型。
    (4)characteristic:可选项,指定存储函数的特性。
    (5)routine_body:SQL代码内容。

    create database mydb9_function;
    -- 导入测试数据
    use mydb9_function;
    set global log_bin_trust_function_creators=TRUE; -- 信任子程序的创建者
     
    -- 创建存储函数-没有输输入参数
    drop function if exists myfunc1_emp;
     
    delimiter $$
    create function myfunc1_emp() returns int
    begin
      declare cnt int default 0;
        select count(*) into  cnt from emp;
      return cnt;
    end $$
    delimiter ;
    -- 调用存储函数
    select myfunc1_emp();
    
    -- 创建存储过程-有输入参数
     
    drop function if exists myfunc2_emp;
    delimiter $$
    create function myfunc2_emp(in_empno int) returns varchar(50)
    begin
        declare out_name varchar(50);
        select ename into out_name from emp where  empno = in_empno;
        return out_name;
    end $$
    delimiter ;
     
     
    select myfunc2_emp(1008);
    

    每文一语

    适合自己的才是最好的

    更多相关内容
  • MySQL存储函数

    千次阅读 2021-06-09 11:25:16
    MySQL存储函数(自定义函数),函数一般用于计算和返回一个值,可以将经常需要使用的计算或功能写成一个函数。 1、创建存储函数

    MySQL存储函数(自定义函数),函数一般用于计算和返回一个值,可以将经常需要使用的计算或功能写成一个函数。

    1、创建存储函数

    在MySQL中,创建存储函数使用CREATE FUNCTION关键字,其基本形式如下:

    CREATE FUNCTION func_name ([param_name type[,...]])
    RETURNS type
    [characteristic ...] 
    BEGIN
    	routine_body
    END;
    

    参数说明:

    (1)func_name :存储函数的名称。

    (2)param_name type:可选项,指定存储函数的参数。type参数用于指定存储函数的参数类型,该类型可以是MySQL数据库中所有支持的类型。

    (3)RETURNS type:指定返回值的类型。

    (4)characteristic:可选项,指定存储函数的特性。

    (5)routine_body:SQL代码内容。

    2、调用存储函数

    在MySQL中,存储函数的使用方法与MySQL内部函数的使用方法基本相同。用户自定义的存储函数与MySQL内部函数性质相同。区别在于,存储函数是用户自定义的。而内部函数由MySQL自带。其语法结构如下:

    SELECT func_name([parameter[,…]]);
    

    示例:创建存储函数,实现根据用户编号,获取用户姓名功能。

    (1)先创建tb_user(用户信息表),并添加数据。

    -- 创建用户信息表
    CREATE TABLE IF NOT EXISTS tb_user
    (
    	id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
    	name VARCHAR(50) NOT NULL COMMENT '用户姓名'
    ) COMMENT = '用户信息表';
     
    -- 添加数据
    INSERT INTO tb_user(name) VALUES('jenrey`s csdn');
    INSERT INTO tb_user(name) VALUES('jenrey');
    INSERT INTO tb_user(name) VALUES('zhangsan');
    INSERT INTO tb_user(name) VALUES('lisi');
    INSERT INTO tb_user(name) VALUES('wangwu');
    INSERT INTO tb_user(name) VALUES('maliu');
    INSERT INTO tb_user(name) VALUES('https://blog.csdn.net/jenrey');
    

    查询数据结果:

    (2)创建存储函数

    -- 创建存储函数
    DROP FUNCTION IF EXISTS func_user;
    CREATE FUNCTION func_user(in_id INT)
    RETURNS VARCHAR(50)
    BEGIN
    	DECLARE out_name VARCHAR(50);
     
    	SELECT name INTO out_name FROM tb_user
    	WHERE id = in_id;
     
    	RETURN out_name;
    END;
    

    (3)调用存储函数

    -- 调用存储函数
    SELECT func_user(1);
    SELECT func_user(2);
    SELECT func_user(3);
    SELECT func_user(4);
    SELECT func_user(5);
    SELECT func_user(6);
    SELECT func_user(7);
    

    执行结果:

    3、修改存储函数

    MySQL中,通过ALTER FUNCTION 语句来修改存储函数,其语法格式如下:

    ALTER FUNCTION func_name [characteristic ...]
    characteristic:
        COMMENT 'string'
      | LANGUAGE SQL
      | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
      | SQL SECURITY { DEFINER | INVOKER }
    

    上面这个语法结构是MySQL官方给出的,修改的内容可以包含SQL语句也可以不包含,既可以是读数据的SQL也可以是修改数据的SQL还有权限。此外在修改function的时候还需要注意你不能使用这个语句来修改函数的参数以及函数体,如果你想改变这些的话你就需要删除掉这个函数然后重新创建。

    4、删除存储函数

    MySQL中使用DROP FUNCTION语句来删除存储函数。

    示例:删除存储函数。

    DROP FUNCTION IF EXISTS func_user;
    

    其它补充:

    如果你在创建存储函数时提示以下错误:

    [Err] 1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is
    enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

    这是我们开启了bin-log, 我们就必须指定我们的函数是否是
    1 DETERMINISTIC 不确定的
    2 NO SQL 没有SQl语句,当然也不会修改数据
    3 READS SQL DATA 只是读取数据,当然也不会修改数据
    4 MODIFIES SQL DATA 要修改数据
    5 CONTAINS SQL 包含了SQL语句

    其中在function里面,只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

    解决方法:

    解决办法也有两种, 第一种是在创建子程序(存储过程、函数、触发器)时,声明为DETERMINISTIC或NO SQL与READS SQL DATA中的一个, 例如: CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`()     DETERMINISTIC BEGIN #Routine body goes here... END;;

    第二种是信任子程序的创建者,禁止创建、修改子程序时对SUPER权限的要求,设置log_bin_trust_routine_creators全局系统变量为1。

    设置方法有三种:

    (1)在客户端上执行 SET GLOBAL log_bin_trust_function_creators = 1。

    (2)MySQL启动时,加上--log-bin-trust-function-creators选贤,参数设置为1。

    (3)在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加

    vi /etc/my.cnf

    log-bin-trust-function-creators=1

    再执行查看binlog参数的命令:show global variables like "%log_bin%"

    关于上面的配置解释可以参考:

    MySQL参数log_bin_trust_function_creators介绍

     

     

     

     

     

     

     

    展开全文
  • SQL存储过程、存储函数

    千次阅读 2021-11-25 11:03:15
    存储过程和函数存储过程和函数是事先经过编译并存储在数据库中的一SQL语句的集合 存储过程和函数的好处: 存储过程和函数可以重复使用,减轻开发人员的工作量。类似于java中方法可以多次调用 减少网络流量,...

    概念:

    存储过程和函数:

    存储过程和函数是事先经过编译并存储在数据库中的一SQL语句的集合

    存储过程和函数的好处:

    1. 存储过程和函数可以重复使用,减轻开发人员的工作量。类似于java中方法可以多次调用
    2. 减少网络流量,存储过程和函数位于服务器上,调用的时候只需要传递名称和参数即可
    3. 减少数据在数据库和应用服务器之间的传输,可以提高数据处理的效率
    4. 将一些业务逻辑在数据库层面来实现,可以减少代码层面的业务处理

    存储过程和函数的区别:

    1. 函数必须有返回值
    2. 存储过程没有返回值

    存储过程:

    语法:
    /*
    	DELIMITER该关键字用来声明sql语句的分隔符,告诉MySQL该段命令已经结束!
    	sql语句默认的分隔符是分号,但是有的时候我们需要一条功能sql语句中包含分号,但是并不作为结束标识。
    	这个时候就可以使用DELIMITER来指定分隔符了!
    */
    -- 修改分隔符为$
    DELIMITER $  
    
    -- 标准语法
    CREATE PROCEDURE 存储过程名称(参数...)
    BEGIN
    	sql语句;
    END$
    
    -- 修改分隔符为分号
    DELIMITER ;-- 存储过程从这里结束
    
    -- 调用存储过程
    CALL 存储过程名称(实际参数);
    

    数据准备:

    -- 创建学生表
    CREATE TABLE student(
    	id INT PRIMARY KEY AUTO_INCREMENT,	-- 学生id
    	NAME VARCHAR(20),					-- 学生姓名
    	age INT,							-- 学生年龄
    	gender VARCHAR(5),					-- 学生性别
    	score INT                           -- 学生成绩
    );
    -- 添加数据
    INSERT INTO student VALUES (NULL,'张三',23,'男',95),(NULL,'李四',24,'男',98),
    (NULL,'王五',25,'女',100),(NULL,'赵六',26,'女',90);
    
    -- 按照性别进行分组,查询每组学生的总成绩。按照总成绩的升序排序
    SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
    
    创建存储过程:
    -- 创建存储过程
    DELIMITER $
    CREATE PROCEDURE stu_group()
    BEGIN
    	SELECT gender,SUM(score) getSum FROM student GROUP BY gender  ORDER BY getSum ASC;
    END$
    DELIMITER ;
    
    -- 调用存储过程
    CALL stu_group();
    
    存储过程的查看和删除:
    -- 查询数据库中所有的存储过程 标准语法
    show procedure status;
    
    -- 查看创建时SQL语句
    SHOW CREATE PROCEDURE stu_group;
    
    -- 删除存储过程标准语法
    DROP PROCEDURE [IF EXISTS] 存储过程名称;
    DROP PROCEDURE IF EXISTS stu_group;
    
    变量的使用:

    定义变量:

    -- 标准语法
    DECLARE 变量名 数据类型 [DEFAULT 默认值];
    -- 注意: DECLARE定义的是局部变量,只能用在BEGIN END范围之内
    
    DELIMITER $
    CREATE PROCEDURE pro_test1 ()
    BEGIN
    	DECLARE num INT DEFAULT 10;-- 定义变量
    	SELECT num;-- 使用变量
    END$
    DELIMITER;
    
    CALL pro_test1 ();-- 调用
    

    变量赋值方式1:

    -- 标准语法
    SET 变量名 = 变量值;
    
    -- 定义字符串类型变量,并赋值
    DELIMITER $
    
    CREATE PROCEDURE pro_test2()
    BEGIN
    	DECLARE NAME VARCHAR(10);   -- 定义变量
    	SET NAME = '存储过程';       -- 为变量赋值
    	SELECT NAME;                -- 查询变量
    END$
    
    DELIMITER ;
    
    -- 调用pro_test2存储过程
    CALL pro_test2();
    

    变量赋值方式2:

    -- 标准语法
    SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];
    
    -- 定义两个int变量,用于存储男女同学的总分数
    DELIMITER $
    
    CREATE PROCEDURE pro_test3()
    BEGIN
    	DECLARE men,women INT;  -- 定义变量
    	SELECT SUM(score) INTO men FROM student WHERE gender='男';    -- 计算男同学总分数赋值给men
    	SELECT SUM(score) INTO women FROM student WHERE gender='女';  -- 计算女同学总分数赋值给women
    	SELECT men,women;           -- 查询变量
    END$
    
    DELIMITER ;
    
    -- 调用pro_test3存储过程
    CALL pro_test3();
    
    if语句的使用:
    -- 标准语法
    IF 判断条件1 THEN 执行的sql语句1;
    [ELSEIF 判断条件2 THEN 执行的sql语句2;]
    ...
    [ELSE 执行的sql语句n;]
    END IF;
    
    /*
    	定义一个int变量,用于存储班级总成绩
    	定义一个varchar变量,用于存储分数描述
    	根据总成绩判断:
    		380分及以上    学习优秀
    		320 ~ 380     学习不错
    		320以下       学习一般
    */
    DELIMITER $
    
    CREATE PROCEDURE pro_test4()
    BEGIN
    	-- 定义总分数变量
    	DECLARE total INT;
    	-- 定义分数描述变量
    	DECLARE description VARCHAR(10);
    	-- 为总分数变量赋值
    	SELECT SUM(score) INTO total FROM student;
    	-- 判断总分数
    	IF total >= 380 THEN 
    		SET description = '学习优秀';
    	ELSEIF total >= 320 AND total < 380 THEN 
    		SET description = '学习不错';
    	ELSE 
    		SET description = '学习一般';
    	END IF;
    	
    	-- 查询总成绩和描述信息
    	SELECT total,description;
    END$
    
    DELIMITER ;
    
    -- 调用pro_test4存储过程
    CALL pro_test4();
    
    参数的传递:

    @变量名: 这种变量要在变量名称前面加上“@”符号,叫做用户会话变量,代表整个会话过程他都是有作用的,这个类似于全局变量一样。
    @@变量名: 这种在变量前加上 “@@” 符号, 叫做系统变量

    语法:

    DELIMITER $
    
    -- 标准语法
    CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
    BEGIN
    	执行的sql语句;
    END$
    /*
    	IN:代表输入参数,需要由调用者传递实际数据。默认的
    	OUT:代表输出参数,该参数可以作为返回值
    	INOUT:代表既可以作为输入参数,也可以作为输出参数
    */
    DELIMITER ;
    

    输入参数IN:

    /*
    	输入总成绩变量,代表学生总成绩
    	输出分数描述变量,代表学生总成绩的描述
    	根据总成绩判断:
    		380分及以上  学习优秀
    		320 ~ 380    学习不错
    		320以下      学习一般
    */
    DELIMITER $
    
    CREATE PROCEDURE pro_test6(IN total INT,OUT description VARCHAR(10))
    BEGIN
    	-- 判断总分数
    	IF total >= 380 THEN 
    		SET description = '学习优秀';
    	ELSEIF total >= 320 AND total < 380 THEN 
    		SET description = '学习不错';
    	ELSE 
    		SET description = '学习一般';
    	END IF;
    END$
    
    DELIMITER ;
    
    -- 调用pro_test6存储过程
    CALL pro_test6(310,@description);
    
    -- 查询总成绩描述
    SELECT @description;
    

    输出参数:

    /*
    	输入总成绩变量,代表学生总成绩
    	输出分数描述变量,代表学生总成绩的描述
    	根据总成绩判断:
    		380分及以上  学习优秀
    		320 ~ 380    学习不错
    		320以下      学习一般
    */
    DELIMITER $
    
    CREATE PROCEDURE pro_test6(IN total INT,OUT description VARCHAR(10))
    BEGIN
    	-- 判断总分数
    	IF total >= 380 THEN 
    		SET description = '学习优秀';
    	ELSEIF total >= 320 AND total < 380 THEN 
    		SET description = '学习不错';
    	ELSE 
    		SET description = '学习一般';
    	END IF;
    END$
    
    DELIMITER ;
    
    -- 调用pro_test6存储过程
    CALL pro_test6(310,@description);
    
    -- 查询总成绩描述
    SELECT @description;
    
    case语句:

    语法1:

    -- 标准语法
    CASE 表达式
    WHEN1 THEN 执行sql语句1;
    [WHEN2 THEN 执行sql语句2;]
    ...
    [ELSE 执行sql语句n;]
    END CASE;
    

    语法2:

    -- 标准语法
    CASE
    WHEN 判断条件1 THEN 执行sql语句1;
    [WHEN 判断条件2 THEN 执行sql语句2;]
    ...
    [ELSE 执行sql语句n;]
    END CASE;
    

    演示:

    /*
    	输入总成绩变量,代表学生总成绩
    	定义一个varchar变量,用于存储分数描述
    	根据总成绩判断:
    		380分及以上  学习优秀
    		320 ~ 380    学习不错
    		320以下      学习一般
    */
    DELIMITER $
    
    CREATE PROCEDURE pro_test7(IN total INT)
    BEGIN
    	-- 定义变量
    	DECLARE description VARCHAR(10);
    	-- 使用case判断
    	CASE
    	WHEN total >= 380 THEN
    		SET description = '学习优秀';
    	WHEN total >= 320 AND total < 380 THEN
    		SET description = '学习不错';
    	ELSE 
    		SET description = '学习一般';
    	END CASE;
    	
    	-- 查询分数描述信息
    	SELECT description;
    END$
    
    DELIMITER ;
    
    -- 调用pro_test7存储过程
    CALL pro_test7(390);
    CALL pro_test7((SELECT SUM(score) FROM student));
    
    while循环:

    语法:

    -- 标准语法
    初始化语句;
    WHILE 条件判断语句 DO
    	循环体语句;
    	条件控制语句;
    END WHILE;
    

    演示:

    /*
    	计算1~100之间的偶数和
    */
    DELIMITER $
    
    CREATE PROCEDURE pro_test8()
    BEGIN
    	-- 定义求和变量
    	DECLARE result INT DEFAULT 0;
    	-- 定义初始化变量
    	DECLARE num INT DEFAULT 1;
    	-- while循环
    	WHILE num <= 100 DO
    		-- 偶数判断
    		IF num%2=0 THEN
    			SET result = result + num; -- 累加
    		END IF;
    		
    		-- 让num+1
    		SET num = num + 1;         
    	END WHILE;
    	
    	-- 查询求和结果
    	SELECT result;
    END$
    
    DELIMITER ;
    
    -- 调用pro_test8存储过程
    CALL pro_test8();
    
    repeat循环:

    repeat循环是条件满足则停止。while循环是条件满足则执行

    语法:

    -- 标准语法
    初始化语句;
    REPEAT
    	循环体语句;
    	条件控制语句;
    	UNTIL 条件判断语句
    END REPEAT;
    
    -- 注意:repeat循环是条件满足则停止。while循环是条件满足则执行
    -- UNTIL只能写在最下面
    

    演示:

    /*
    	计算1~10之间的和
    */
    DELIMITER $
    
    CREATE PROCEDURE pro_test9()
    BEGIN
    	-- 定义求和变量
    	DECLARE result INT DEFAULT 0;
    	-- 定义初始化变量
    	DECLARE num INT DEFAULT 1;
    	-- repeat循环
    	REPEAT
    		-- 累加
    		SET result = result + num;
    		-- 让num+1
    		SET num = num + 1;
    		
    		-- 停止循环
    		UNTIL num>10
    	END REPEAT;
    	
    	-- 查询求和结果
    	SELECT result;
    END$
    
    DELIMITER ;
    
    -- 调用pro_test9存储过程
    CALL pro_test9();
    
    loop循环:

    loop可以实现简单的循环,但是退出循环需要使用其他的语句来定义。可以使用leave语句完成!
    如果不加退出循环的语句,那么就变成了死循环

    语法:

    -- 标准语法
    初始化语句;
    [循环名称:] LOOP
    	条件判断语句
    		[LEAVE 循环名称;]
    	循环体语句;
    	条件控制语句;
    END LOOP 循环名称;
    
    -- 注意:loop可以实现简单的循环,但是退出循环需要使用其他的语句来定义。我们可以使用leave语句完成!
    --      [循环名称:] LOOP循环名称要加 如果不加退出循环的语句,那么就变成了死循环。
    
    /*
    	计算1~10之间的和
    */
    DELIMITER $
    
    CREATE PROCEDURE pro_test10()
    BEGIN
    	-- 定义求和变量
    	DECLARE result INT DEFAULT 0;
    	-- 定义初始化变量
    	DECLARE num INT DEFAULT 1;
    	-- loop循环
    	l:LOOP
    		-- 条件成立,停止循环
    		IF num > 10 THEN
    			LEAVE l;
    		END IF;
    	
    		-- 累加
    		SET result = result + num;
    		-- 让num+1
    		SET num = num + 1;
    	END LOOP l;
    	
    	-- 查询求和结果
    	SELECT result;
    END$
    
    DELIMITER ;
    
    -- 调用pro_test10存储过程
    CALL pro_test10();
    
    游标:
    • 游标可以遍历返回的多行结果,每次拿到一整行数据
    • 在存储过程和函数中可以使用游标对结果集进行循环的处理
    • 简单来说游标就类似于集合的迭代器遍历
    • MySQL中的游标只能用在存储过程和函数中
    含义语法
    创建游标DECLARE 游标名称 CURSOR FOR 查询sql语句;
    打开游标OPEN 游标名称;
    使用游标获取数据FETCH 游标名称 INTO 变量名1,变量名2,…;
    关闭游标CLOSE 游标名称;

    演示:

    -- 创建stu_score表
    CREATE TABLE stu_score(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	score INT
    );
    
    /*
    	将student表中所有的成绩保存到stu_score表中
    */
    DELIMITER $
    
    CREATE PROCEDURE pro_test11()
    BEGIN
    	-- 定义成绩变量
    	DECLARE s_score INT;
    	-- 创建游标,查询所有学生成绩数据
    	DECLARE stu_result CURSOR FOR SELECT score FROM student;
    	
    	-- 开启游标
    	OPEN stu_result;
    	
    	-- 使用游标,遍历结果,拿到第1行数据
    	FETCH stu_result INTO s_score;
    	-- 将数据保存到stu_score表中
    	INSERT INTO stu_score VALUES (NULL,s_score);
    	
    	-- 使用游标,遍历结果,拿到第2行数据
    	FETCH stu_result INTO s_score;
    	-- 将数据保存到stu_score表中
    	INSERT INTO stu_score VALUES (NULL,s_score);
    	
    	-- 使用游标,遍历结果,拿到第3行数据
    	FETCH stu_result INTO s_score;
    	-- 将数据保存到stu_score表中
    	INSERT INTO stu_score VALUES (NULL,s_score);
    	
    	-- 使用游标,遍历结果,拿到第4行数据
    	FETCH stu_result INTO s_score;
    	-- 将数据保存到stu_score表中
    	INSERT INTO stu_score VALUES (NULL,s_score);
    	
    	-- 关闭游标
    	CLOSE stu_result;
    END$
    
    DELIMITER ;
    
    -- 调用pro_test11存储过程
    CALL pro_test11();
    
    -- 查询stu_score表
    SELECT * FROM stu_score;
    
    
    -- ===========================================================
    /*
    	出现的问题:
    		student表中一共有4条数据,我们在游标遍历了4次,没有问题!
    		但是在游标中多遍历几次呢?就会出现问题
    */
    DELIMITER $
    
    CREATE PROCEDURE pro_test11()
    BEGIN
    	-- 定义成绩变量
    	DECLARE s_score INT;
    	-- 创建游标,查询所有学生成绩数据
    	DECLARE stu_result CURSOR FOR SELECT score FROM student;
    	
    	-- 开启游标
    	OPEN stu_result;
    	
    	-- 使用游标,遍历结果,拿到第1行数据
    	FETCH stu_result INTO s_score;
    	-- 将数据保存到stu_score表中
    	INSERT INTO stu_score VALUES (NULL,s_score);
    	
    	-- 使用游标,遍历结果,拿到第2行数据
    	FETCH stu_result INTO s_score;
    	-- 将数据保存到stu_score表中
    	INSERT INTO stu_score VALUES (NULL,s_score);
    	
    	-- 使用游标,遍历结果,拿到第3行数据
    	FETCH stu_result INTO s_score;
    	-- 将数据保存到stu_score表中
    	INSERT INTO stu_score VALUES (NULL,s_score);
    	
    	-- 使用游标,遍历结果,拿到第4行数据
    	FETCH stu_result INTO s_score;
    	-- 将数据保存到stu_score表中
    	INSERT INTO stu_score VALUES (NULL,s_score);
    	
    	-- 使用游标,遍历结果,拿到第5行数据
    	FETCH stu_result INTO s_score;
    	-- 将数据保存到stu_score表中
    	INSERT INTO stu_score VALUES (NULL,s_score);
    	
    	-- 关闭游标
    	CLOSE stu_result;
    END$
    
    DELIMITER ;
    
    -- 调用pro_test11存储过程
    CALL pro_test11();
    
    -- 查询stu_score表,虽然数据正确,但是在执行存储过程时会报错
    SELECT * FROM stu_score;
    

    游标的优化使用(配合循环使用)

    /*
    	当游标结束后,会触发游标结束事件。我们可以通过这一特性来完成循环操作
    	加标记思想:
    		1.定义一个变量,默认值为0(意味着有数据)
    		2.当游标结束后,将变量值改为1(意味着没有数据了)
    */
    -- 1.定义一个变量,默认值为0(意味着有数据)
    DECLARE flag INT DEFAULT 0;
    -- 2.当游标结束后,将变量值改为1(意味着没有数据了)
    DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;
    
    
    /*
    	将student表中所有的成绩保存到stu_score表中
    */
    DELIMITER $
    
    CREATE PROCEDURE pro_test12()
    BEGIN
    	-- 定义成绩变量
    	DECLARE s_score INT;
    	-- 定义标记变量
    	DECLARE flag INT DEFAULT 0;
    	-- 创建游标,查询所有学生成绩数据
    	DECLARE stu_result CURSOR FOR SELECT score FROM student;
    	-- 游标结束后,将标记变量改为1,判断,如果没有数据就把flag改为1
    	DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;
    	
    	-- 开启游标
    	OPEN stu_result;
    	
    	-- 循环使用游标
    	REPEAT
    		-- 使用游标,遍历结果,拿到数据
    		FETCH stu_result INTO s_score;
    		-- 将数据保存到stu_score表中
    		INSERT INTO stu_score VALUES (NULL,s_score);
    	UNTIL flag=1
    	END REPEAT;
    	
    	-- 关闭游标
    	CLOSE stu_result;
    END$
    
    DELIMITER ;
    
    -- 调用pro_test12存储过程
    CALL pro_test12();
    
    -- 查询stu_score表
    SELECT * FROM stu_score;
    

    存储函数:

    存储函数和存储过程是非常相似的。存储函数可以做的事情,存储过程也可以做到!
    存储函数有返回值,存储过程没有返回值(参数的out其实也相当于是返回数据了)

    创建存储函数

    DELIMITER $
    
    -- 标准语法
    CREATE FUNCTION 函数名称([参数 数据类型])
    RETURNS 返回值类型
    BEGIN
    	执行的sql语句;
    	RETURN 结果;
    END$
    
    DELIMITER ;
    

    调用存储函数

    -- 标准语法
    SELECT 函数名称(实际参数);
    

    删除存储函数

    -- 标准语法
    DROP FUNCTION 函数名称;
    

    演示:

    /*
    	定义存储函数,获取学生表中成绩大于95分的学生数量
    */
    DELIMITER $
    
    CREATE FUNCTION fun_test1()
    RETURNS INT
    BEGIN
    	-- 定义统计变量
    	DECLARE result INT;
    	-- 查询成绩大于95分的学生数量,给统计变量赋值
    	SELECT COUNT(*) INTO result FROM student WHERE score > 95;
    	-- 返回统计结果
    	RETURN result;
    END$
    
    DELIMITER ;
    
    -- 调用fun_test1存储函数
    SELECT fun_test1();
    
    -- 删除存储函数
    DROP FUNCTION fun_test1;
    
    展开全文
  • 数据库存储过程与存储函数

    千次阅读 2020-09-01 10:33:13
    存储过程是一组为了完成特定功能的SQL语句集,经过编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它,叫做存储过程、存储函数。 2、存储过程与存储函数的区别 存储...

    数据库存储过程与存储函数_伱糸淂忄-CSDN博客

    MySQL数据库之存储过程与存储函数 - 奥辰 - 博客园

    1、数据库存储过程与存储函数

            存储过程是一组为了完成特定功能的SQL语句集,经过编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它,叫做存储过程、存储函数。

    2、存储过程与存储函数的区别

    1.  存储函数的限制比较多,例如不能用临时表,只能用表变量,而存储过程的限制较少,存储过程的实现功能要复杂些,而函数的实现功能针对性比较强。
    2. 返回值不同。存储函数必须有返回值,且仅返回一个结果值;存储过程可以没有返回值,但是能返回结果集(out,inout)。
    3. 调用时的不同。存储函数嵌入在SQL中使用,可以在select 存储函数名(变量值);存储过程通过call语句调用 call 存储过程名。
    4. 参数的不同。存储函数的参数类型类似于IN参数,没有类似于OUT和INOUT的参数。存储过程的参数类型有三种,IN、out和INOUT:

       a. in:数据只是从外部传入内部使用(值传递),可以是数值也可以是变量

       b. out:只允许过程内部使用(不用外部数据),给外部使用的(引用传递:外部的数据会被先清空才会进入到内部),只能是变量

       c. inout:外部可以在内部使用,内部修改的也可以给外部使用,典型的引用 传递,只能传递变量。

    3、存储过程与存储函数的优缺点

    1.  存储过程只在创建时进行编译,以后每次执行存储过程都不需要再重新编译;而一般的SQL语句每执行一次就需要编译一次,所以使用存储过程可以提高数据库执行速度。
    2. 当地数据库进行复杂操作(如对多个表进行update、insert、delete、query)时,可以将此复杂操作用存储过程封装起来与数据库提供的事务处理结合起来一起使用。
    3. 存储过程可以重复使用可以减少数据库开发人员的工作量。
    4. 安全性高,可以设定只有某指定用户才具有对指定的存储过程使用的权限。

    MySQL

    1、存储过程

    CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...]
    BEGIN
        过程体
    END
    
    delimiter //
    create procedure param_pro(in id int , out num int, inout p_sal int)
             begin
                       delete from emp where empno = id ;
    
                       select max(sal) from emp into num;
    
                       select count(*)  into p_sal from emp where sal >P_sal ;
             end //
    delimiter ;

    调用

    set @p_sal = 1250 ;
    
    call param_pro(7369 , @num , @p_sal);
    
    select @num , @p_sal ;

    2、存储函数

    CREATE FUNCTION  函数名([ 参数名 数据类型 [, …]]) RETURNS返回类型
    BEGIN
      过程体
    END
    
    delimiter //
    create function fun1(id int) returns int
             begin
                       return (select sal from emp where empno=id);
             end //
    delimiter ;

    调用:

    select fun1(7698);

    3、修改

    ALTER {PROCEDURE | FUNCTION} sp_name [characteristic …]

      其中,sp_name表示存储过程或函数的名称,characteristic参数指定存储过程或函数的特性,可能取值有:

      CONTAINS SQL:子程序包含SQL语句,但不包含读或写数据的语句。

      NO SQL:子程序不包含SQL语句。

      READS SQL DATA:子程序包含读数据的语句。

      MODIFIES SQL DATA:子程序包含写数据的语句。

      SQL SECURITY { DEFINER | INVOKER}:指明谁有权限执行。

      DEFINER:只有定义者自己才能1执行。

      INVOKER:调用者可以执行。

      COMMENT ‘string’ :注释。

    ALTER PROCEDURE param_pro
    MODIFIES SQL DATA
    SQL SECURITY INVOKER ;

    oracle

    1、存储过程

    CREATE [OR REPLACE] PROCEDURE procedure_name[(parameter_name in|out|in out parameter_type, [, ...])]
    {IS | AS}
      [columnName1 tableName.columnName1%type;
       columnName2 tableName.columnName2%type;
       ...
      ]
    BEGIN
      < procedure_body >
    END;
     
    -- 语法说明:
    -- ① 用[]包含的内容为可有可无,根据实际情况而定;
    -- ② procedure_name:存储过程名称;
    -- ③ parameter_name:参数名称;
    -- ④ 参数模式:
    --   in: 是参数的默认模式,这种模式就是在程序运行的时候已经具有值,在程序体中值不会改变;即,可以传入参数;
    --   out:该模式定义的参数只能在过程体内部赋值,标识该参数可以将某个值传递回调用它的过程;即,可以返回值;
    --   in out:表示该参数可以向过程中传递值,也可以将某个值传出去;即,既可以传入参数,也可以返回值;
    -- ⑤ parameter_type:参数数据类型;
    -- ⑥ is、as:在存储过程中,两者没有任何区别;但是在视图中只能用as,在游标中只能用is;
    -- ⑦ procedure_body:PL/SQL子程序体;即该存储过程要执行的操作内容;
    -- ⑧ 创建存储过程时,可以在is或者as后面添加对类型或变量的说明;

    备注,创建存储过程时需要注意的一些细节问题: 

    ① 定义无参存储过程时,过程名称后面不能加括号,否则将会创建失败;

     ② 无参存储过程调用时,可以加括号也可不加,可通过PL/SQL调用,也可以通过execute关键字调用;

    ③ 有参存储过程的参数类型一定不能指定精度,否则将会报错,或创建失败;

    ④ 利用“表名.字段名%TYPE” 可以获取到表中字段的具体类型,以便声明变量时设置其类型;

    ⑤ 当需要通过:DBMS_OUTPUT.PUT_LINE('... ...'); 向SQL Developer控制台输出脚本内容时,注意需要先开启serveroutput,开启命令为如下:

    set serveroutput on;

    实例,创建员工信息表存储过程

    create or replace procedure addEmployee(eNo in out number, uName in out varchar2, dNo in out number, sal in out number, com in out number)
    as
      empNo emp.empNo%type;
      usernName emp.username%type;
      deptNo emp.deptno%type;
      salary emp.salary%type;
      comm emp.comm%type;
    begin
      insert into emp(empNo, username, deptNo, salary, comm)values(eNo, uName, dNo, sal, com);
    end;

    调用

    declare
      empNo emp.empno%type := 7777;
      username emp.username%type := 'Hellen';
      deptNo emp.deptno%type := 10;
      salary emp.salary%type := 3800;
      comm emp.comm%type := 700;
    begin
      addEmployee(empNo, username, deptno, salary, comm);
    end;

    无参存储过程

    
    create or replace procedure firstProcedure as
    begin
      执行语句
      DBMS_OUTPUT.PUT_LINE('你好,我是第一个存储过程...');
    end;

    调用

    -- 方式一:
    begin
      firstprocedure();
    end;
     
    -- 方式二:
    begin
      firstprocedure;
    end;
    

    存储函数

    CREATE [OR REPLACE] FUNCTION function_name[(parameter_name in parameter_type, [, ...])]
        RETURN returnValType
    {IS | AS}
      [variable1 type;
       variable2 type;
       ...
      ]
    BEGIN
      < procedure_body >
    END;
     
    -- 语法说明:
    -- ① 用[]包含的内容为可有可无,根据实际情况而定;
    -- ② function_name:存储函数名称;
    -- ③ parameter_name:参数名称;
    -- ④ in: 参数模式,存储函数只有in模式;
    -- ⑤ parameter_type:参数数据类型;
    -- ⑥ returnValType:函数返回值数据类型;
    -- ⑦ is、as:在存储过程中,两者没有任何区别;但是在视图中只能用as,在游标中只能用is;
    -- ⑧ variable、type:分别用于声明函数在执行过程中所需要的变量名称和数据类型; 
    -- ⑨ procedure_body:PL/SQL子程序体;即该存储函数要执行的操作内容;
    -- ⑩ 创建存储过程时,可以在is或者as后面添加对类型或变量的说明;

    备注,使用存储函数需要注意的一些细节问题: 

        ① 存储函数必须包含一个return子句,用于指定函数要返回的数据类型;

        ② 函数定义的时候,无参函数名后面一定不能加括号;否则将创建失败;

        ③ 函数调用时,无参函数方法名后面可以不加括号,但是有参函数必须加括号、并传入参数;

    实例,创建员工加薪存储函数

    create or replace FUNCTION addSalary(eNo in number, addVal in number)
      RETURN number
    AS
      pNo number;         -- 定义变量保存员工编号
      pName varchar2(30); -- 定义变量保存员工用户名
      pDeptNo number;     -- 定义变量保存员工部门编号
      pSal number;        -- 定义变量保存员工的工资
      pComm number;       -- 定义变量保存员工的奖金
      pTotal number;      -- 定义变量保存员工的总收入
      newSalary number;   -- 定义变量保存加薪后的工资
      newComm number;     -- 定义变量保存加薪后的奖金
    BEGIN
      SELECT empNo, username, deptNo, salary, comm INTO pNo, pName, pDeptNo, pSal, pComm FROM EMP WHERE empNo=eNo;
      pTotal := (pSal+pComm)*12;
      DBMS_OUTPUT.PUT_LINE('员工'||pName||':初始工资为='||pSal||';初始奖金为='||pComm||';初始年薪为='||pTotal);
     
      newSalary := pSal+pSal*addVal;
      newComm := newSalary*0.12;
      UPDATE EMP SET username=pName, deptNo=pDeptNo, salary=newSalary, comm=newComm WHERE empNo=pNo;
      RETURN newSalary;
    END;

    调用

    declare
      a number;        -- 定义变量,接受存储函数返回值
    begin
      a := addSalary(1111, 0.3);    -- 调用存储函数
      DBMS_OUTPUT.put_line(a);      -- 打印存储函数返回值
    end;

    常用案例

    增加字段

    -- 创建存储过程
    DROP PROCEDURE IF EXISTS ALTER_TABLE_ADD_KEY;
    DELIMITER $$
    CREATE PROCEDURE ALTER_TABLE_ADD_KEY() BEGIN
        IF NOT EXISTS (SELECT * FROM INFOMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tablename' AND TABLE_SCHEMA = 'dbname' AND COLUMN_NAME='KEY') THEN
            ALTER TABLE dbname.tablename ADD KEY INT(16) NOT NULL DEFAULT 0;
        END IF;
    END $$
    DELIMITER;
    -- 调用存储过程
    CALL ALTER_TABLE_ADD_KEY();
    DROP PROCEDURE ALTER_TABLE_ADD_KEY;

    添加主键 

    -- 添加主键
    DROP PROCEDURE IF EXISTS ALTER_TABLE_ADD_PRIMARY;
    DELIMITER $$
    CREATE PROCEDURE ALTER_TABLE_ADD_PRIMARY() BEGIN
        IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 'tablename' AND TABLE_SCHEMA = 'dbname' AND INDEX_NAME='PRIMARY') THEN
            ALTER TABLE dbname.tablename ADD PRIMARY KEY(KEY1, KEY2, ...);
        END IF;
    END $$
    DELIMITER;
    CALL ALTER_TABLE_ADD_PRIMARY();
    DROP PROCEDURE ALTER_TABLE_ADD_PRIMARY;
    
    
    -- 新增主键
    ALTER TABLE DBNAME.TABLENAME DROP PRIMARY KEY, ADD PRIMARY KEY(KEY1, KEY2, ...)

    展开全文
  • MySQL存储函数,值得一看

    千次阅读 多人点赞 2021-12-18 16:18:39
    前面讲过了存储过程,这篇让卷心菜讲讲存储函数。 参数列表: FUNCTION中总是默认为IN参数。 RETURNS 后的语句表示函数返回数据的类型; RETURNS子句只能对FUNCTION做指定,对函数而言这是强制的。它用来指定函数...
  • 存储过程和存储函数的简单介绍

    千次阅读 2020-04-21 21:07:35
    存储过程和存储函数是事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程和函数可以简化应用开发人员的工作,减少数据在数据库和应用服务器之间的传输,能够提高数据库的处理效率。存储过程在高并发...
  • 存储函数(一)—— 创建存储函数

    千次阅读 2020-07-09 14:21:25
    之前,我们列举不少mysql自带的函数,但是有些时候自带函数并不能很好满足我们的需求,此时就需要自定义存储函数了,存储函数与存储过程有些类似,简单来说就是封装一段sql代码,完成一种特定的功能,并返回结果。...
  • 开始之前 首先创建如下两张表,并初始化一些数据。 创建存储过程(CREATE PROCEDURE) ...基本语法格式如下: ...CREATE PROCEDURE sp_name (parameters) ...sp_name为存储过程的名称(唯一性,没有所谓的重...
  • 存储函数 存储过程 VS 存储函数 JDBC 调用存储过程/函数 存储过程 1、存储过程是自定义的且封装在数据库服务器上的 PLSQL 代码片段,它已经编译好了,可以像调用数据库函数一样来调用存储过程,可以给它传递参数...
  • 存储函数与存储过程的区别

    千次阅读 2019-09-20 13:20:08
    存储函数:设计之初的目的是只读的(但是实际并没有严格限制) 存储过程:可读可写 存储函数的读写权限这里要特殊说明一下: 创建存储函数的时候后,必须声明函数读写权限,且只能在DETERMINISTIC, NO SQL, or READS...
  • 数据库实验5---存储过程和存储函数

    千次阅读 2022-05-03 22:47:48
    存储过程和存储函数实验内容实验步骤及处理结果思考体会参考资料 实验内容 输入以下代码,创建存储过程stu_info,执行时通过输入姓名,可以查询该姓名的学生的各科成绩。 DELIMITER @@ CREATE PROCEDURE stu_info...
  • 存储过程(Stored Procedure)和存储函数(Stored Function)是在数据库中定义的一些完成特定功能的SQL语句集合,其经编译后存储在数据库中。存储过程和存储函数中可包含流程控制语句及各种SQL语句。它们可以接受参数、...
  • 存储过程,函数,触发器
  • 第8章 存储过程和存储函数;本章内容;1.存储过程和存储函数概述;1.存储过程和存储函数概述;2.创建存储过程和存储函数;2.创建存储过程和存储函数;2.创建存储过程和存储函数;2.创建存储过程和存储函数;2.创建存储过程和...
  • mysql存储过程和存储函数练习题

    千次阅读 2020-05-28 09:12:28
    课题 实验五 存储过程和存储函数 目的要求 1. 掌握通过SQL语句CREATE PROCEDURE创建存储过程的方法。 2. 掌握使用SQL语句CALL调用存储过程的方法。 3. 掌握使用SQL语句ALTER PROCEDURE修改存储过程的方法。 4. 掌握...
  • mysql 存储函数

    千次阅读 2018-06-13 23:41:17
    #定义函数 delimiter $$ create function getGameName(gameid int) returns VARCHAR(45) DETERMINISTIC begin declare name VARCHAR(45); set name=(select gamename from cy_game where id=gameid); return (n.....
  • 数据库存储过程及存储函数

    千次阅读 2018-09-14 08:40:32
    存储过程 在java中经常用封装的思想来提高代码的重用性,在java中为什么要定义方法?原因是有一个功能是需要在不同的地方多次使用的,如果在每一个需要调用的地方都写一遍会造成代码量很大,所以我们把他抽取成单独...
  • 从头开始学MySQL-------存储过程与存储函数(1)

    万次阅读 多人点赞 2018-11-03 17:00:19
    10.1.1 创建存储过程 存储过程就是一条或者多条SQL语句的... 创建存储过程的语句为 CREATE PROCEDURE,创建存储函数的语句为CREATE FUNCTION。 调用存储过程的语句为CALL。 调用存储函数的形式就像调用MyS......
  • 存储过程,存储函数(Oracle)存储过程和存储函数指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。存储过程和存储函数的区别?存储函数:可以通过return 语句返回函数值。存储过程:不能除此之外...
  • Mybatis调用存储过程/存储函数

    千次阅读 2022-04-13 21:38:14
    1、存储过程 假设存储过程名称为:p_statisticvalue, 输入参数为整数:a, 输出参数为整数:b 则调用存储过程的一般格式如下: {call p_statisticvalue( #{a,mode = IN,jdbcType=java.lang.Integer}, #{b,...
  • 存储过程和存储函数的区别

    千次阅读 2019-01-21 20:58:04
    语法区别:关键字不一样,存储函数比存储过程多了两个return。 本质区别:存储函数有返回值,可以自定义函数;而存储过程没有返回值。 如果存储过程想实现有返回值的业务,我们就必须使用out类型的参数。 即便是...
  • 存储过程和存储函数的区别 1、语法区别 关键字不一样,存储函数比存储过程多了两个 return。 2、本质区别 存储函数有返回值,而存储过程没有返回值。 如果存储过程想实现有返回值的业务,我们就必须使用 out 类型的...
  • 存储函数

    千次阅读 2015-09-04 00:23:45
    自定义函数 用户自定义函数(user-defined function,UDF)是一种对MySQL扩展的途径,其用法与内置方法相同。 创建自定义函数 CREATE FUNCTION function_name 创建函数名 RETURNS type 返回类型 {STRING|INTEGER|...
  • JDBC调用存储过程与存储函数

    万次阅读 多人点赞 2019-01-26 11:28:33
    在JDBC API中提供了调用存储过程的方法,通过CallableStatement对象进行操作。CallableStatement对象...CallableStatement对象主要用于执行数据库中定义的存储过程和存储函数,其调用方法如下: 调用存储过程:{...
  • MySQL基础-怎么创建存储函数

    千次阅读 2019-05-05 12:50:25
    存储函数是由SQL语句和过程式语句所组成的代码片段,它可以被应用程序和其他SQL语句所调用,在MySQL中可以通过CREATE FUNCTION语句来创建存储函数 存储函数就是返回单个值的特殊类型的存储程序,接下来将在文章中为...
  • 注意:关于示例所讲到的存储过程和函数及数据库表的创建,可通过查看之前的文章有进行介绍。...存储过程和存储函数创建以后,用户可以查看存储过程和存储函数的状态和定义。用户可以通过SHOW STATUS语句查看...
  • MySQL存储过程和存储函数(看不懂你打我系列)

    万次阅读 多人点赞 2020-05-24 08:20:25
    MySQL存储过程和存储函数 MySQL中提供存储过程与存储函数机制,我们先将其统称为存储程序,一般的SQL语句需要先编译然后执行,存储程序是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,当用户通过指定...
  • PL/SQL存储函数,存储过程

    千次阅读 2016-10-30 17:04:05
    存储过程和存储函数 存储过程和存储函数跟我们知道的表、视图、索引、序列、同义词等一样是我们数据中的对象。 笔记教程见:https://github.com/caojx-git/learn/blob/master/notes/oracle/PLSQL.sql 1.1什么是...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,308,377
精华内容 923,350
关键字:

存储函数

友情链接: uvarm_changchang.rar