数据库视图、变量、存储过程、变量及函数

最新推荐文章于 2023-06-12 00:52:06 发布

ExtraMile

最新推荐文章于 2023-06-12 00:52:06 发布

阅读量942

点赞数 1

分类专栏：数据库编程语言文章标签：数据库函数存储 select 数据

本文链接：https://blog.csdn.net/Fouse_/article/details/76598508

版权

编程语言同时被 2 个专栏收录

45 篇文章 0 订阅

订阅专栏

数据库

19 篇文章 0 订阅

订阅专栏

学习笔记：2017-8-1

视图：是虚拟的数据表，与数据库中的表不一样，视图只包含使用时的动态检测数据的查询。
创建视图:

create view test_view as select * from student3;
select * fron test_view;
//直接对视图进行select语句查询与之前的对表查询效果一样，但是更简洁

1.创建视图，若存在可以使用drop再用create。

//删除视图：
drop view test_view;
//查看视图：
show table status where conmment='view';

特点：
1.视图可以简化操作
2.视图可以对机密数据提供安全保护
存储过程（Stored Procedure）是在大型数据库系统中，存储过程视为实现某一个特定的功能，将很多sql语句集合在一起去执行。也就是一个sql语句的操作集合。一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。
第一次执行是编译一次，以后在执行不再编译，提高了效率。

delimiter 结束标志
create procedure func(参数）
begin
（语句）
select * from [..]
end
结束标志
example:
delimiter //
create procedure func(n int)
begin
select * from student
end
//
//调用
call func(10)
//

局部变量：一般只用在sql语句中，比如存储过程beging/end，其作用域时仅限于该语句块，在该语句执行完毕后，局部变量就消失了

declare 局部变量
example:
delimiter $$
create procedure pero1(a int,b int)
begin
declare c int default 0;
set c= a+b;//赋值
select c as C;
end 
$$
//调用
delimiter ;
call pro1(1,2);
example:
deliniter $$
create procedure pro2()
begin
declare v_agr int;
declare v_nama varchar(20);
select age,name into v_age,v_name from student where code=1;//赋值
select v_age,v_name;
end
$$
//调用
delimiter ;
call peo2();

用户变量：在客户端连接到数据库实例的整个过程中都有效的变量。
mysql中变量不用实现声明，在用的时候@调用即可
用户变量：在客户端连接到数据库实例整个过程中都有效的变量。
mysql中用户变量不用实现声明，在用的时候直接”@变量名”使用即可
第一种用法：set @num=1;或者　set @num:=10; //使用set语句创建并初始化变量，直接使用@num变量
第二种用法：select @num:=字段名　from 表名　where ….
select语句一般是用来输出用户变量。　如：select @num.
在用set赋值可以使用“＝”／“:＝” .但是使用select赋值必须用“:=”
注：
1.用户变量与数据库连接有关，在连接中声明的变量，在存储过程中创建了用户变量后一直到数据库连接断开，变量才会显示。
2.用户变量的变量名形式为:@varname.

set @num=1;
set @num:=10;//使用set语句创建语句并初始化变量，直接使用@num变量
在使用set赋值时可以用'='或者':='赋值

//2.
select @num:=字段名 from 表名 where ...
//select 一般用来输入用户变量,在使用select 赋值时一定要用':='赋值
selet @num,

创建含有参数的存储过程——out
该值可在存储过程内部被改变，并可返回。（不能接收值的传入，只能接受值的传出）
创建含有参数的存储过程——int
表示该参数的值必须在调用存储过程中指定，在存储过程中修改该参数的值不能被返回，（只能接收值的传入，不能接受值的传出）
创建含有参数的存储过程——inout：可被传入和返回
注：不写参数in/out/inout 则默认为in

example:
create procedure p3()
begin 
declare v_count int default 0;
declare cnt int default 0; 
declare i int default 0;
declare row_id int;
declare row_name varchar(20);
declare row_count int;
declare leveler cursor for select id,name,count from store where name='iphone';
[declare contiue handler for not found set 标志位]
select count(*) into cnt from store where name='iphone'; 
open leveler;
[while(标志位) do]
repeat
set i:=i+1;
fetch leveler into row_id,row_name,row_count;
set v_count:=v_count+row_count;
[end while;]
until i>=cnt end repeat; 
close leveler;
select v_count;
end;
$$

查看存储过程：

show procedure status where db='数据库名';
或者：
show create procedure 数据库名.存储过程名;

删除存储过程：

drop procedure 存储过程名

游标：
一般情况下，我们用select这些查询语句使都是针对一行记录而言的。
游标实际是一种能从包括多条记录的结果集中每次提取一条记录的机制。

游标的类型：
1.静态游标(不检测数据行的变化)
2.动态游标(反应所有数据行的变化)
3.仅向前游标(不支持滚动)
4.键集游标(能反应修改，但不能准确反应插入和删除)

声明或定义游标：

declare 游标名　cursor  [local | global] [forward_only | scroll][static |keyset | dynamic] [read_only | scroll_locks] for select 语句 [for update [列名...]]；

获取游标的数据：

fetch  游标名　[into 变量]

使用游标的步骤：
1.必须先声明/定义游标
declare 游标名　cursor for select_statement;
2.必须先打开游标才能使用
　 open 游标名
3.通过游标根据需要获取数据
　 fetch 游标名 into var1,var2…[…];
4.使用完成之后关闭游标
　close 游标名。

例：
mysql> delimiter $$
mysql> create procedure p1()
    -> begin
    -> declare row_code int;
    -> declare row_name varchar(20);
    -> declare row_age int;
    -> declare row_sex varchar(10);
    -> declare row_score int;
    -> declare row_grade int;
    -> declare row_address varchar(20);
    -> declare row_major varchar(20);
    -> declare leveler cursor for select code,name,age,sex,score,grade,address,major from student3;
    -> open leveler;
    -> fetch leveler into row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major;
    -> select row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major;
    -> close leveler;
    -> end
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter　;
    -> call p1();
如何循环游标来获取出所有的行?(while/for/loop/repeat)
mysql> delimiter $$
         create procedure p3()
         begin
         declare row_code int;
         declare row_name varchar(20);
         declare row_age int;
         declare row_sex varchar(10);
        declare row_score int;
         declare row_grade int;
         declare row_address varchar(20);
         declare row_major varchar(20);
         declare cnt int default 0;
         declare i int default 0;
         declare leveler cursor for select code,name,age,sex,score,grade,address,major from student3;
         select count(*) into cnt from student3;
         open leveler;
         repeat
         set i:=i+1;
         fetch leveler into row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major;
         select row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major;
         until i>=cnt end repeat;
         close leveler;
         end
         $$

或者是：
在mysql游标中，定义continue handler来操作一个越界标志。

declate continue handler for NOT FOUND statement（当没有数据的时候要执行的语句）
         create procedure p4()
         begin
         declare row_code int;
         declare row_name varchar(20);
         declare row_age int;
         declare row_sex varchar(10);
        declare row_score int;
         declare row_grade int;
         declare row_address varchar(20);
         declare row_major varchar(20);
         declare go_on int default 1;
         declare leveler cursor for select code,name,age,sex,score,grade,address,major from student3;
         declare continue handler for NOT FOUND set go_on:=0;
         open leveler;
         repeat
        fetch leveler into row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major;
         select row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major;
         until go_on=0 end repeat;
         close leveler;
         end
         $$

         example:
         create procedure p5()
         begin
         declare row_code int;
         declare row_name varchar(20);
         declare row_age int;
         declare row_sex varchar(10);
        declare row_score int;
         declare row_grade int;
         declare row_address varchar(20);
         declare row_major varchar(20);
         declare go_on int default 1;
         declare leveler cursor for select code,name,age,sex,score,grade,address,major from student3;
         declare continue handler for NOT FOUND set go_on:=0;
         open leveler;
         while(go_on=1) do
        fetch leveler into row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major;
         select row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major;
         end while;
         close leveler;
         end
         $$

例子：
新建一个表：

create table if not exists store(
id int not null auto_increment,
name varchar(20) not null,
count int not null default 1,
primary key(id))default charset=utf8;
//插入数据：
insert into store(id,name,count)  　values(1,'android',15),(2,'iphone',20),(3,'iphone',18),(4,'android',5),(5,'android',13),(6,'iphone',15);
//用存储过程做一个功能，统计iphone的总库存是多少，并输出到控制台？９
 create procedure pox() begin declare c int; declare n varchar(20); declare total int default 0;
    -> declare done int default false;
    -> declare leveler cursor for select name,count from store where name='iphone';
    -> declare continue handler for not found set done=true;
    -> set total=0;
    -> open leveler;
    -> fetch leveler into n,c;
    -> while(not done) do
    -> set total=total+c;
    -> fetch leveler into n,c;
    -> end while;
    -> close leveler;
    -> select total;
    -> end
    -> $$

ExtraMile

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
数据库视图、变量、存储过程、变量及函数

学习笔记：2017-8-1视图：是虚拟的数据表，与数据库中的表不一样，视图只包含使用时的动态检测数据的查询。创建视图:create view test_view as select * from student3;select * fron test_view;//直接对视图进行select语句查询与之前的对表查询效果一样，但是更简洁1.创建视图，若存在可以使用drop再用create。
复制链接

扫一扫