1.2自定义函数

• 语法

  delimiter \\ -- 修改结束符
  create function f1( -- 创建函数的命令
      i1 int,
      i2 int) -- 定义参数: 参数的类型必须是表名里面有的
  returns int -- 定义返回值类型
  BEGIN -- 函数体开始
      -- 注意：函数体内不能写select * from table
      declare num int; -- 声名变量
      -- declare num int default 0; --声名变量并设置默认值是一
      set num = i1 + i2;-- 执行相加操作
      return(num);-- 返回num
  END \\-- 函数体结束
  delimiter ; -- 修改回结束符

  • 报错解决方式

• -- MYSQL 函数
  -- 内置函数
  -- 函数的使用，在select后面使用

  -- 显示创建日期
  SELECT CURRENT_DATE()
  
  -- 日期格式化，常用
  -- use prozhjy
  SELECT 时间,count(1) from 任务点途径 GROUP BY DATE_FORMAT(时间,"%Y-%m")
  SELECT DATE_FORMAT(时间,"%Y-%m-%d") FROM `任务点途径`
  
  -- 自定义函数
  
  delimiter \\
  CREATE FUNCTION f1 ( i1 INT, i2 INT ) RETURNS INT BEGIN
  DECLARE
      num INT;
  SET num = i1 + i2;
  RETURN ( num );
  
  END \\ delimiter;
  -- 删除函数
  drop function func_name;
  -- 执行函数
  # 获取返回值
  declare @i VARCHAR(32);
  select UPPER('alex') into @i;
  SELECT @i;
  
  # 在查询中使用
  select f1(11,nid) ,name from tb2;

2、存储过程

2.1 .简单存储过程的创建

-- 存储过程
    -- 个人理解：一堆SQL语句的操作进行集合
-- 分类
    -- 简单创建
-- 创建存储过程

delimiter //
create procedure p1() -- 创建命令
BEGIN
    select * from student; -- 命令体
END//
delimiter ;

-- 执行存储过程
call p1()
-- 无参数存储过程

2.2. 有参数的存储过程

• 参数分类

  • out 仅用于返回值（注：一般作为执行成功返回的标志）
  • in 仅用于传入参数
  • inout 既可以当做传入参数，也可以当做返回值来用

• 删除存储过程：DROP PROCEDURE pro_name

  -- ==============================================
  -- 创建存储过程
  -- delimiter \\
  -- create procedure p2(
  --     in i1 int,-- 传入参数
  --     -- in i2 int,
  --     -- inout i3 int,-- 既可以传入也可以传出的参数
  --     out r1 int -- 传出参数
  -- )
  -- BEGIN
  --         -- 声名变量类型
  -- --     DECLARE n2 int;
  -- --     DECLARE temp2 int default 0;
  --
  --         -- 声名session级别的变量调用是使用@
  --     -- set temp1 = 1;
  --
  --     -- set r1 = i1 + i2 + temp1 + temp2;
  --     SELECT * from student;
  -- --     set i3 = i3 + 100;
  --         set r1 =i1+1
  -- end\\
  -- delimiter ;
  --
  -- -- 执行存储过程
  -- -- 声名session变量
  -- -- set @t1 =4;
  -- set @t2 = 0;
  -- CALL p1 (1,@t2);
  -- SELECT @t2;
  
  -- 有参数的存储过程
  -- **************************************
  -- 有参数的存储过程
  delimiter \\
  CREATE PROCEDURE p3(
      in n1 int,
      out n2 int
  )
  BEGIN
  set n2=123123;
  SELECT * from student WHERE sid>n1;
  END \\
  delimiter ;
  
  set @v1=123;
  call p3(2,@v1);
  SELECT @v1;

2.3事务

-- 事务
delimiter \\
CREATE PROCEDURE p4(
    OUT p_return_code tinyint
)
BEGIN
    DECLARE exit handler for SQLEXCEPTION
    BEGIN
        -- error检测异常，事物回滚
        set p_return_code=1;
        ROLLBACK;
    END

--     DECLARE EXIT handler for SQLWARNING
--     BEGIN
--         -- waring,使用较少
--         set p_return_code=2;
--         ROLLBACK;
--     END

    -- 开始事物
    START TRANSACTION
        DELETE from teacher WHERE tname='程心';
        INSERT into teacher(tname)VALUES('章北海');
        COMMIT;
    -- 提交事物

    -- success
    set p_return_code=0;

END \\
delimiter ;
set @v2=123;
call p4(@v2);
SELECT @v2;
SELECT * from teacher

2.4 游标

delimiter \\
CREATE PROCEDURE p5()
BEGIN
DECLARE ssid int;-- 定义变量
DECLARE ssname VARCHAR(50);-- 自定义变量2
-- 设置标志，用来检测游标内是否还有数据,默认为flase
DECLARE done INT DEFAULT FALSE;

-- 定义游标 语法
DECLARE my_cursor CURSOR FOR SELECT sid,sname FROM student;
-- 当游标内没有数据的时候将done设置为TRUE
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
OPEN my_cursor;
    xxyy:loop
        FETCH my_cursor into ssid,ssname;
        IF done THEN
            LEAVE xxyy;
        END IF;
        INSERT into teacher (tname) VALUES (ssname);
    END loop xxyy;
CLOSE my_cursor;
END\\
delimiter ;

-- =====================
-- 调用游标
call p5()
SELECT * from teacher;
SELECT * from student;

2.5 使用pymysql调用存储过程

• 无参调用

  # -*- coding: utf-8 -*-
  '''
  @Time    : 2021/10/17 19:22
  @Author  : ziqingbaojian
  @File    : sqlcon.py
  '''
  import pymysql
  try:
      conn=pymysql.connect(host='127.0.0.1',user='root',password='1234567',database='testlearn')
  except Exception as e:
      print(e)
  cur=conn.cursor()
  # cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)查询结果以字典的形式进行返回
  # 普通查询
  '''
  sqlstr="select * from student where sid>%s"
  cur.execute(sqlstr,3)
  res=cur.fetchall()
  print(res)
  '''
  # 调用无参的存储过程
  cur.callproc('p1')
  res=cur.fetchall()
  print(res)

• 参数调用

  # -*- coding: utf-8 -*-
  '''
  @Time    : 2021/10/17 19:22
  @Author  : ziqingbaojian
  @File    : sqlcon.py
  '''
  import pymysql
  try:
      conn=pymysql.connect(host='127.0.0.1',user='root',password='1234567',database='testlearn')
  except Exception as e:
      print(e)
  cur=conn.cursor()
  
  # 调用有参存储过程
  # 执行存储过程
  cur.callproc('p3',(2,123))
  # 获取执行完存储的参数
  # 接收存储过程中查询结果
  res=cur.fetchall()
  print(res)
  # 存储过程返回的结果
  # 固定 参数写法：@_pname_0,@_pname_1,.....
  cur.execute("select @_p3_0,@_p3_1;")
  result = cur.fetchall()
  
  conn.commit()
  cur.close()
  conn.close()
  print(result)

今天你学习了吗？