今天继续讲了存储过程!老师领着在数据库里写存储过程!
存储过程:一次性执行成批的SQL语句
一、定义
例如:
客户----服务器 进行信息交流(数据库)
1) insert into
2) update
3) select
优点:1、批处理,减少客户和服务器之间的交流次数;
2、服务器端运行,移植性好;
3、安全要求高的行业;
4、包含流程控制结构的SQL语句的集合
二、创建存储过程
1、基本应用
语法格式:
create database
create table
存储函数
;---每条语句的结束
声明新的结束符(任选,避免在MySQL环境下有特殊含义)
delimiter(分隔符) $$
delimiter //
delimiter ;//
create procedure(过程) p_name(in c_name 数据类型)
[特征描述1、使用什么语言创建存储过程2、结果的确定性3、4、权限
5、注释]
SQL语句集合(一条,多条)
例如:
Create procedure p1() select * from t2;
Create procedure p1()
Begin
流程控制结构;
SQL语句集合;
end //
练习1:创建存储过程,显示“Hello,world!”;
步骤:1、检验mysql版本
Select version()
Select @@VERSION
2、存储过程是属于数据库
Show databases;
Create database db1;
Use db1;
3、声明结束符
Delimiter //
4、创建存储过程
Create procedure p1()select “hello,world!”; //
5、调用存储过程
Call p1()//
6、恢复结束符
Delimiter ;
练习2:
创建数据库db1,创建表t1(s1 int)
Insert 数据:5
根据以上内容创建存储过程,显示t1当中的记录信息
2、参数的应用
IN 输入形参参数的使用
set @x=3;
select @x;
调用必须有对应的实参(类型、数量)将信息带入到存储过程
create procedure p10(IN para1 int) set @x=para1;
create procedure p10(para1 int) set @x=para1;
call p10(18);
select @x;
用户定义变量:声明开始set @x=3;
到断开连接结束
不能和其他用户共享
作用域:
存储过程的局部变量:临时结果
作用范围:从声明位置开始,到end结束
语法格式
1) 分步
declare(声明) var_name type;
var_name初值是null
set var_name=值;
2) Declare var_name type default 值;
3) Declare v1,v2 int;
Set v1=5,v2=8;
OUT 输出形参的使用\
用一个变量(实参位置)保存存储过程中返回的结果
Create procedurep14(OUT para1 int)
begin
set para1=10;
end//
错误的:
call p14(x);
select x;
正确的:
call p14(@x);
select @x;
select@y:=@x+1;
例如:函数
Function fun1()
{
。。。。
Return 32;
}
$name=fun1();
$a=$name+1;
3、嵌套
Create procedure p15()
Begin
Declare v1 char(10) default’ ‘outer;
Begin
Declare v1 char(10) default ‘inner’;
Select v1;
End;
Select v1;
End//
三、流程控制结构
第一组:条件分支结构
IF语句
CASE 语句
语法:
IF 条件1 THEN 语句集合
【ELSEIF 条件2 THEN 语句集合】
【ELSE 语句集合】
END IF; 注意有空格
注意:
形参中的变量如果和存储过程中的局部变量同名,会“覆盖”;
形参变量在存储过程中无大小写的要求;
练习:创建存储过程,如果参数<60分,给t1表插入数据0;否则给t1
表插入2学分;
Create procedure t1_add(para1int)
BEGIN
IF para1>=60 THEN
Insert into t1 values(2);
ELSE
Insert into t1 values(0);
END IF;
END//
CALL t1_add(70);//
Select * from t1;//
CASE
语法
Case 变量
When 变量取值1 then 语句集合
When 变量取值2 then 语句集合
Else 语句集合
End case;
Case
When 变量与值1表达式 then 语句集合
When 变量与值2表达式 then 语句集合
Else 语句集合
End case;
。。。。。。
IF str = ‘M’ then
set sex=‘男’;
ELSEIF str=‘F’then
Set sex=‘女’;
ELSE
Set sex=‘无’;
END IF;
。。。。。
循环结构
While….END WHILE
LOOP…END LOOP
REPEAT…END REPEAT
GOTO(不推荐使用,流程混乱,淘汰)
Createprocedure p16()
Begin
Declarev int;
Setv=0;
While v<5 do ------------循环的入口(必须满足的条件)
Insert into t1 values(v);
Set v=v+1; -------------循环的出口(退出循环的条件)
End while;
End//
Call p16();//
Select * fromt1;//
提醒:执行结束后的系统返回是针对最后一条insert语句;
Create procedure p17()
Begin
Declare v int;
Set v=0;
Repeat
Insert into t1 values(v);
Set v=v+1; ----------出口条件
Until v>5; ----------入口条件
End repeat;
End//
Create procedure p18()
Begin
Declare v int;
Set v=0;
Loop_label:LOOP
Insertinto t1 values(v);
Set v=v+1;
If v>5 then
Leave loop_label;
Endif;
Endloop;
End//
删除存储过程
Drop procedure 名;
四、select …into 变量列表
只能应用在存储过程
Createprocedure p19(out para1 char(20))
Begin
Select s1 into para1 from t1;
End//
Select 返回结果:表、列、行、值
游标(指针)?
步骤:
创建游标;declare
打开游标;open
获取记录;fetch
关闭游标;close
数据库显示的
mysql>use db1;
Databasechanged
mysql>delimiter //
mysql>create procedure p9() select "hello,world!";//
Query OK,0 rows affected (0.05 sec)
mysql>call p9();//
+--------------+
| hello,world!|
+--------------+
|hello,world! |
+--------------+
1 row inset (0.00 sec)
Query OK,0 rows affected (0.01 sec)
mysql>use t1;
ERROR1049 (42000): Unknown database 't1;'
mysql>use db1;
ERROR1049 (42000): Unknown database 'db1;'
mysql>show databases;
-> //
+--------------------+
|Database |
+--------------------+
|information_schema |
|cpxs |
|db1 |
|db2 |
|db3 |
|db_test_1 |
|demo |
| mail |
|mysql |
|news |
|test_2 |
|test_5 |
|testmail |
|text_1 |
| th |
| w |
|wtt |
|xscj |
+--------------------+
18 rowsin set (0.01 sec)
mysql>delimiter ;
mysql>use db1;
Databasechanged
mysql>create table t1(s1 int);
ERROR1050 (42S01): Table 't1' already exists
mysql>drop table t1;
Query OK,0 rows affected (0.05 sec)
mysql>create table t1(s1 int);
Query OK,0 rows affected (0.05 sec)
mysql>insert into t1 values(5);
Query OK,1 row affected (0.01 sec)
mysql>select *from t1;
+------+
| s1 |
+------+
| 5 |
+------+
1 row inset (0.00 sec)
mysql>delimiter //
mysql>create procedure p10() select * from t1;//
Query OK,0 rows affected (0.00 sec)
mysql>call p10;
-> //
+------+
| s1 |
+------+
| 5 |
+------+
1 row inset (0.00 sec)
Query OK,0 rows affected (0.01 sec)
mysql>delimiter ;
mysql>create procedure p11(para1 int) set @x=para1;//
Query OK,0 rows affected (0.00 sec)
callp11('a')' at line 1
mysql>call p11('a');//
Query OK,0 rows affected, 1 warning (0.00 sec)
mysql>select @x;
+------+
| @x |
+------+
| 0 |
+------+
1 row inset (0.00 sec)
mysql>show tables;
+---------------+
|Tables_in_db1 |
+---------------+
| t1 |
| t2 |
+---------------+
2 rows inset (0.04 sec)
mysql>delimiter //
mysql>create procedure p12()
-> begin
-> declare v1 int;
-> declare v2 int;
-> set v1=5;
-> set v2=7;
-> insert into t1 values(v1);
-> select * from t1;
-> end//
Query OK,0 rows affected (0.00 sec)
mysql>call p12();//
+------+
| s1 |
+------+
| 5 |
| 5 |
+------+
2 rows inset (0.01 sec)
Query OK,0 rows affected (0.02 sec)
mysql>create procedure p14()
-> begin
-> declare v1,v2 int default 5;
-> insert into t1 values(v2);
-> select *from t1;
-> end//
Query OK,0 rows affected (0.00 sec)
mysql>call p14();
-> //
+------+
| s1 |
+------+
| 5 |
| 5 |
| 5 |
+------+
3 rows inset (0.00 sec)
Query OK,0 rows affected (0.02 sec)
mysql>
mysql>create procedure p15(para1 int)
-> begin
-> declare v1 int;
-> set v1=para1;
-> insert into t1 values(v1);
-> select *from t1;
-> end//
Query OK,0 rows affected (0.00 sec)
mysql>call p15();
-> //
ERROR1318 (42000): Incorrect number of arguments for PROCEDURE db1.p15; expect
d 1, got0
mysql>call p15(19);
-> //
+------+
| s1 |
+------+
| 5 |
| 5 |
| 5 |
| 19 |
+------+
4 rows inset (0.01 sec)
Query OK,0 rows affected (0.03 sec)
mysql>create procedure p16()
-> begin
-> declare v1 char(10) default 'outer';
-> begin
-> declare v1 char(10) default 'inner';
-> select v1;
-> end;
-> select v1;
-> end//
Query OK,0 rows affected (0.01 sec)
mysql>call p16;
-> //
+-------+
| v1 |
+-------+
| inner |
+-------+
1 row inset (0.00 sec)
+-------+
| v1 |
+-------+
| outer |
+-------+
1 row inset (0.01 sec)
Query OK,0 rows affected (0.03 sec)
mysql>