sql存储过程初学

最新推荐文章于 2023-07-18 11:00:35 发布

zzn的进阶笔记

最新推荐文章于 2023-07-18 11:00:35 发布

阅读量271

点赞数

分类专栏： mysql 文章标签：数据结构

本文链接：https://blog.csdn.net/weixin_49092494/article/details/121505496

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

谈谈啥是存储过程

1.存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升

#1.存储过程 简单查询
delimiter $$
CREATE PROCEDURE hello_procedure()
BEGIN
	SELECT 'hello proced';
END $$
#调用存储过程
CALL hello_procedure()

#2.存储过程局部变量+查询
delimiter $$
CREATE PROCEDURE sp_var01()
begin
	#局部变量
	DECLARE nickName VARCHAR(32) DEFAULT 'SF';   #局部变量
	
	SELECT nickName;
	
	set nickName ='UG';
	
	SELECT nickName;
end $$
#3.删除
drop PROCEDURE sp_var01;
CALL sp_var01()

#4.全局变量 + 查询
delimiter //
CREATE PROCEDURE sp_var02()
BEGIN
	#全局变量
	set @nick_name = 'WR';
end //
call sp_var02();
select @nick_name

#5.关联表 实现存储过程查询
delimiter //
CREATE PROCEDURE sp_var_into()
begin
	select d.dname into @deptnames from dept d where d.deptno = 2;
end //
call sp_var_into()

select @deptnames
#6. in:入参  VARCHAR(32)类型 这里注意就是入参in 参数 参数类型
delimiter $$
CREATE PROCEDURE sp_param01(in nickname VARCHAR(32))
BEGIN
	set @user_age = nickname;
end $$

call sp_param01('zhuyidana')

SELECT @user_age

#7.根据入参(in)和出参(out)显示  into是将dname导入道出参的dept_name
delimiter $$
CREATE PROCEDURE sp_param02(IN dept_loc VARCHAR(64),OUT dept_name VARCHAR(32))
BEGIN
	SELECT dname into dept_name from dept d where d.db_source = dept_loc;
end $$
call sp_param02('db03',@dept_name);
select @dept_name;
#8.inout 入参出参查询  set进去之后 查询出来的就是set进去的值
DROP PROCEDURE sp_param01_inout;
delimiter $$
CREATE PROCEDURE sp_param01_inout(in dept_loc VARCHAR(64),inout dept_name VARCHAR(32))
BEGIN
	SELECT dept_name;
	SELECT dname into dept_name FROM dept d where db_source = dept_loc;
END	$$
set @dept_name = 'HELLO ';
CALL sp_param01_inout('db03',@dept_name);
SELECT @dept_name
#9 函数
CREATE table emp(
empno int,
ename VARCHAR(64),
job VARCHAR(64),
mgr VARCHAR(64),
hiredate date,
sal DOUBLE(6,2),
comm DOUBLE(6,2),
deptno int
)
INSERT INTO emp VALUES 
(7369,'smith','clerk',7902,'1980-12-17',800.00,250.00,20),
(7499,'allen','salesman',7698,'1981-12-17',1600.00,300.00,30),
(7521,'ward','salesman',7698,'1982-12-17',1250.00,400.00,30),
(7566,'jones','manager',7839,'1981-02-04',2975.00,400.00,30),
(7654,'martin','salesman',7698,'1981-09-28',1250.00,500.00,30),
(7698,'blake','manager',7839,'1981-05-01',2850.00,500.00,30),
(7782,'clark','manager',7839,'1981-06-09',2450.00,500.00,10),
(7839,'king','president',7839,'1971-11-17',5000.00,500.00,10),
(7844,'turner','salesman',7698,'1981-09-08',1500.00,0.00,30),
(7900,'james','clerk',7698,'1981-12-03',950.00,0.00,30),
(7902,'ford','analyst',7566,'1981-12-03',3000.00,0.00,20),
(7934,'miller','clerk',7782,'1982-01-23',1300.00,0.00,10)
SELECT * from emp
#入职年限<=38是新手 >38并且<=40老员工>40元老
select timestampdiff(year,e.hiredate,now()) from emp e where e.empno = '7499'

#if 和 else 和 elseif判断
delimiter $$
CREATE PROCEDURE sp_hire_if()
BEGIN
	declare result VARCHAR(32);
	declare years int;
	select timestampdiff(year,e.hiredate,now()) into years from emp e where e.empno = '7499';
	if timestampdiff(year,'2001-01-01',now())>40
		then set result = '元老';
	ELSEIF timestampdiff(year,'2001-01-01',now())>38
			then set result = '老员工';
	ELSE
			set result = '新手';
	end if;
	SELECT result;
end $$
call sp_hire_if()
#上面那个例子的加强版
delimiter $$
CREATE PROCEDURE sp_hire_if2()
BEGIN
	declare result VARCHAR(32);
	declare years int;
	select timestampdiff(year,e.hiredate,now()) into years from emp e where e.empno = '7499';
	if years > 40
		then set result = '元老';
	ELSEIF  years > 38
			then set result = '老员工';
	ELSE
			set result = '新手';
	end if;
	SELECT result;
end $$
call sp_hire_if2();

# loop循环语法
DROP PROCEDURE sp_flow_loop
delimiter //
CREATE PROCEDURE sp_flow_loop()
BEGIN
	declare c_index int default 1;
	num_loop:loop
  SELECT c_index;	#这一步很重要必须先查询哦
	if c_index >= 10
	then leave num_loop; #离开  类似于break
	end if;
	set c_index = c_index+1;
 end loop num_loop;	
end //
delimiter ;
call sp_flow_loop();
# 10.loop语法2
delimiter //
create PROCEDURE sp_flow_loop02()
BEGIN
	declare c_index int default 1;
	DECLARE result_str VARCHAR(256) default '1';
	cnt:loop
		set c_index = c_index + 1;
		set result_str = concat(result_str,',',c_index);
		if c_index < 10 then
		iterate cnt; #约等于Java的continue
		end if;
		#这句代码是循环结束才会执行到
		leave cnt;
		end loop cnt;
		SELECT result_str;
end //
delomiter ;
call sp_flow_loop02();
#repeat和until循环
DROP PROCEDURE sp_flow_repeat
delimiter //
CREATE PROCEDURE sp_flow_repeat()
BEGIN
	declare c_index int default 1;
	# 收集结果字符串
	declare result_str varchar(256) default '1';
	count_lab:repeat #循环一段代码，值到符合until条件时才退出循环
		set c_index = c_index+1;
		set result_str = CONCAT(result_str,',',c_index);
		until c_index>=10 #直到什么为止
		end repeat count_lab;
		SELECT result_str;
end //
delimiter ;
call sp_flow_repeat();
# repeat和until循环
delimiter //
CREATE PROCEDURE sp_flow_repeat2()
BEGIN
	declare c_index int default 1;
	#收集结果字符串
	declare result_str varchar(256) default '1';
	count_lab:repeat
		set c_index = c_index + 1;
		set result_str = CONCAT(result_str,',',c_index);
		until c_index>=10
	end repeat count_lab;
	SELECT result_str;
end //
call sp_flow_repeat2();
#while循环
delimiter //
CREATE procedure sp_flow_while()
BEGIN
	declare c_index int default 1;
	#收集结果字符串
	declare result_str VARCHAR(256) DEFAULT '1';
	
	WHILE c_index<10 DO
	set c_index = c_index +1;
	set result_str = CONCAT(result_str,',',c_index);
	end while;
	select result_str;
end //
call sp_flow_while();

#按照部门名称查询员工，通过select查看员工的编号，姓名，薪资
CREATE TABLE dept2(
deptno VARCHAR(64),
dname VARCHAR(64),
loc varchar(64)
)
INSERT into dept2() VALUES
(10,'ACCOUNTING','NEW YOKE'),
(20,'RESEARCE','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON')
#ACCOUNTING
select e.empno,e.ename,e.sal from emp e,dept2 d where e.deptno = d.deptno and d.dname = 'ACCOUNTING'

#
DROP PROCEDURE sp_show_emp
delimiter //
CREATE PROCEDURE sp_show_emp(in dept_name VARCHAR(32))
BEGIN
	declare e_no int;
	declare e_name VARCHAR(32);
	declare e_sal decimal(7,2);
	declare emp_cursor cursor for   #emp_cursor游标  cursor类型
	select e.empno,e.ename,e.sal
	from emp e,dept2 d
	where e.deptno = d.deptno and d.dname = dept_name;
	
	open emp_cursor; #打开游标
	fetch emp_cursor into e_no,e_name,e_sal; #fetch:取值
	select e_no,e_name,e_sal; 
	fetch emp_cursor into e_no,e_name,e_sal; #fetch:取值   fetch只能取一行值，下面展示一个循环的取值
	select e_no,e_name,e_sal; 
	close emp_cursor; #结束游标
end //	 
delimiter ;
call sp_show_emp('ACCOUNTING');

#游标进行循环取值
DROP PROCEDURE sp_show_emp2
delimiter //
CREATE PROCEDURE sp_show_emp2(in dept_name VARCHAR(32))
BEGIN
	declare e_no int;
	declare e_name VARCHAR(32);
	declare e_sal decimal(7,2);
	declare lp_flag boolean default true;

	declare emp_cursor cursor for   #emp_cursor游标  cursor类型
	select e.empno,e.ename,e.sal
	from emp e,dept2 d
	where e.deptno = d.deptno and d.dname = dept_name;
	
	#handler 句柄 抓取异常处理
	declare continue handler for 1329 set lp_flag = false;  #当什么什么异常时执行这段代码 
	
	open emp_cursor; #打开游标
	emp_loop:loop
	fetch emp_cursor into e_no,e_name,e_sal; #fetch:取值
	if lp_flag then
		SELECT e_no,e_name,e_sal;
	else
		leave emp_loop;
	end if;	
	end loop emp_loop;
	set @end_falg='end';
	close emp_cursor; #结束游标
end //	 
delimiter ;
call sp_show_emp2('ACCOUNTING');
SELECT @end_falg

#
DROP PROCEDURE high_sal3;
delimiter //
CREATE PROCEDURE high_sal3(in dept_name VARCHAR(32))
BEGIN
	declare e_no int;
	declare e_name VARCHAR(32);
	declare e_sal decimal(7,2);
	declare flag boolean default true;
	
	declare emp_cursor cursor for
		select e.empno,e.ename,e.sal
		from emp e,dept2 d
		where e.deptno = d.deptno and d.dname = dept_name;
	#handler 句柄
 declare continue HANDLER for not found set flag=FALSE;
	open emp_cursor;
	emp_loop:loop
	fetch emp_cursor into e_no,e_name,e_sal;
			#SELECT * from emp;
	if flag then
		if e_name = 'king' then
			iterate emp_loop; #iterate 继续循环
		else
			update emp e set e.sal = e.sal + 100 where e.empno = e_no; #否则更新一下这个表
		end if;
	else
		leave emp_loop;
	end if;

 end loop emp_loop;
 set @end_falg = 'exit_flag';
 close emp_cursor;
end //
delimiter ;
call high_sal3('ACCOUNTING');

zzn的进阶笔记

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
sql存储过程初学

谈谈啥是存储过程1.存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升#1.存储过程简单查询delimiter $$CREATE PROCEDURE hello_procedure()BEGIN SELECT 'hello proced';
复制链接

扫一扫

专栏目录