函数
存储过程:可以有多个返回。适合做批量插入、批量更新
函数:有且仅有1个返回,适合做数据处理后返回
语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
#使用delimiter来设置结束标记
SELECT 函数名(参数列表)
#有参有返回
CREATE FUNCTION myf1(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0; #用户变量
#DECLARE sal INT DEFAULT 0; #局部变量
SELECT salary INTO @sal #赋值
FROM employees
WHERE last_name = empName;
RETURN @sal;
END $
SELECT myf1('k_ing') $
#查看函数
SHOW CREATE FUNCTION myf1;
#删除函数
DROP FUNCTION myf1;
流程控制函数
#if函数
IF(表达式1,表达式2,表达式3)
#case结构
CASE 变量|表达式|字段
WHEN val THEN 返回值
...
ELSE 返回值
END
#==========>
CREATE PROCEDURE test_case(IN score INT)
BEGIN
CASE
WHEN score>=90 AND score<=100 THEN SELECT 'A'
WHEN score>=80 THEN SELECT 'B'
ELSE SELECT 'D'
END CASE
END $
#=======>
CREATE PROCEDURE test_if(IN score INT)
BEGIN
IF score>=90 AND score<=100 THEN RETURN 'A'
IF score>=80 THEN RETURN 'B'
ELSE RETURN 'D'
END IF
END $
循环结构
while、loop、repeat
iterate 类似于 continue 、 leave类似于 break
#while
CREATE PROCEDURE pro_while(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username,'password') VALUES(concat('Rose',i),'666');
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a
END $
一、表变量
表变量在SQL Server 2000中首次被引入。表变量的具体定义包括列定义,列名,数据类型和约束。而在表变量中可以使用的约束包括主键约束,唯一约束,NULL约束和CHECK约束(外键约束不能在表变量中使用)。定义表变量的语句是和正常使用Create Table定义表语句的子集。只是表变量通过DECLARE @local_variable语句进行定义。
表变量的特征:
- 表变量拥有特定作用域(在当前批处理语句中,但不在任何当前批处理语句调用的存储过程和函数中),表变量在批处理结束后自动被清除。
- 表变量较临时表产生更少的存储过程重编译。
- 针对表变量的事务仅仅在更新数据时生效,所以锁和日志产生的数量会更少。
- 由于表变量的作用域如此之小,而且不属于数据库的持久部分,所以事务回滚不会影响表变量。
表变量可以在其作用域内像正常的表一样使用。更确切的说,表变量可以被当成正常的表或者表表达式一样在SELECT,DELETE,UPDATE,INSERT语句中使用,但是表变量不能在类似"SELECT select_list INTO table_variable"这样的语句中使用。而在SQL Server2000中,表变量也不能用于INSERT INTO table_variable EXEC stored_procedure这样的语句中。
表变量不能做如下事情:
- 虽然表变量是一个变量,但是其不能赋值给另一个变量。
- check约束,默认值和计算列不能引用自定义函数。
- 不能为约束命名。
- 不能Truncate表变量。
- 不能向标识列中插入显式值(也就是说表变量不支持SET IDENTITY_INSERT ON)