PL/SQL server语法详解

PL/SQL

语法

一、程序注释语句

/*。。。。*/：多行注释。此多行注释中不能包括go语句。

二、变量

１、分类：局部变量@与全局变量@@

２、局部变量使用前要先定义，赋值、再使用

declare @变量名 数据类型——每次可定义多个变量       
set|select @变量名＝常量

set 与select 赋值的区别为：set 只能一次一个变量,select 则可以一次给多个变量赋值。

Declare @x int
Declare @y int
Set @x=10 -- 也可以是select出来的结果

变量赋值时要注意数据类型要匹配。

四、程序结构

1语句组

BEGIN   
语句1 -- 类似于namespace
语句2
END 

2、if …else 结构——条件分支结构

if  逻辑表达式   
单条语句or  begin…end 语句组（多条语句）   else       
单条语句or  begin…end 语句组（多条语句）
-- 如果要多行的话一定要有begin 和 and

例子

declare @w char(20)
declare @ss char(4)
select  @w=sname,@ss=ssex 
      from student  where sno='101'
if (@ss='男')
  begin
        update sc set grade=grade+5 where sno='101'
   end
else
   begin
        update sc set grade=grade+10 where sno='101'
   end

3、case

使用对应值匹配
CASE 测试表达式
    When 简单表达式1 then 结果表达式1
    When 简单表达式2 then 结果表达式2
    ……..
    When 简单表达式n then 结果表达式n
    [else  结果表达式n+1]
END

使用bool条件匹配
CASE 
    When 布尔表达式1 then 结果表达式1
    When布尔表达式2 then 结果表达式2
    ……..
    When布尔表达式n then 结果表达式n
    [else  结果表达式n+1]
END

3、while

语法结构
while 逻辑表达式
       begin 
           T-SQL语句组
    [break]——无条件终止整个循环语句，即跳出循环
    [continue]——结束本次循环，回到while处再判断是否重新开始下一次循环
end

4、return返回语句

作用：在批处理、语句块或存储过程中，使用return可以从当前程序中退出，其后的语句将不会执行。如在上例加入return:

例：
declare @a int ,@b int ,@c int
select @a=1,@b=1,@c=20
-- 使用筛选语句来赋值也是可以的，但是不会打印
while @c>0
  begin 
    set @b＝@b+3
    set @a＝@a+@b
    -- (此后加入 select  @a,@b,@c  return，结果又如何？)
    if @b<10
       continue
     set @c=@c-5
     select @a,@b,@c -- 这个会打印输出
  end

五、游标

1、定义：

交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行作为个性化处理。游标就是提供这种机制的结果集扩展。游标是结果集的逻辑扩展，它使应用程序得以逐行处理结果集。

游标定义：
DECLARE  (游标名字) 
[ INSENSITIVE ] [ SCROLL ] CURSOR 
FOR select_statement  [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

2、请求游标 Microsoft® SQL Server™ 2000 支持两种请求游标的方法：

Transact-SQL及数据库应用程序编程接口（API）游标函数 （我们只讨论后者）对根据 SQL-92 游标语法制定的游标，Transact-SQL 语言支持使用它们的语法

3、标准的SQL-92 语法定义游标：

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,…n ] ] } ]

关键字
INSENSITIVE	定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从 tempdb 中的该临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。使用 SQL-92 语法时，如果省略 INSENSITIVE，（任何用户）对基表提交的删除和更新都反映在后面的提取中。
SCROLL	指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。如果在 SQL-92 DECLARE CURSOR 中未指定 SCROLL，则 NEXT 是唯一支持的提取选项。如果指定 SCROLL，则不能也指定 FAST_FORWARD。
select_statement	定义游标结果集
UPDATE	定义游标中可更新的列

4、 从游标中检索行的操作称为提取。

提取选项如下：

FETCH FIRST ：提取游标中的第一行。     
FETCH NEXT ：提取上次提取行之后的行。    
FETCH PRIOR ：提取上次提取行之前的行。     
FETCH LAST ：提取游标中的最后一行。     
FETCH ABSOLUTE n ：如果 n 为正整数，则提取游标中从第 1 行开始的第 n 行。如果 n 为负整数，则提取游标中的倒数第 n 行。如果 n 为 0，则没有行被提取。  
FETCH RELATIVE n ：提取上次所提取行之后的第 n 行。如果 n 为正数，则提取所上次提取行之后的第 n 行。如果 n 为负数，则提取上次所提取行之前的第 n 行。如果 n 为 0，则同一行被再次提取。 

example:
declare cur for select * from stu
open cur -- 游标定义后要打开
fetch next from cur into @args -- 读取内容存入变量
close cur

-- @@表示着全局或者是系统变量
@@fetch_status = 0 -- 用于判断读取数据读取的状态是否成功，为0则是正常
while @@fetch_status = 0

使用游标修改，example：

declare cur cursor for select * from sc
for update of grade
fetch next from cur into @vars
while @@fetch_status = 0
begin
	update sc set grade = grade + 3
	where current of cur
end
close cur

注意：

1. 打开游标时，游标中当前行的位置逻辑上应位于第一行之前。这使不同的提取选项具有下列行为，如果这是打开游标后的第一次提取操作： FETCH FIRST ：提取游标中的第一行。FETCH NEXT：提取游标中的第一行。FETCH PRIOR ：不提取行。
2. Transact-SQL 游标限于一次只能提取一行。API 服务器游标则支持每次提取时提取一批行。支持一次提取多行的游标称为块状游标。 Transact-SQL 扩展语法增加了很多参数，基本语句是一样的。（有时间的同学可以自己查看SQL的帮助系统）

5、 游标可以按照它所支持的提取选项进行分类：

1.只进

• 必须按照从第一行到最后一行的顺序提取行。FETCH NEXT 是唯一允许的提取操作。
• 在声明游标时未选用scroll参数时建立的游标就是只进游标。

2.可滚动性

• 可以在游标中任何地方随机提取任意行。允许所有的提取操作（但动态游标不支持绝对提取）。
• 可滚动游标对支持联机应用程序特别有用。可将游标映射为应用程序中的表格或列表框。随着用户向上、向下和在整个表格中滚动，应用程序使用滚动提取从游标中检索用户想要查看的行。

6、 游标进程

Transact-SQL 游标和 API 游标有不同的语法，但下列一般进程可用于所有 SQL Server 游标：

1. 把游标与 Transact-SQL 语句的结果集相关联，并且定义游标的特征，如是否能够更新游标中的行。（声明游标）
2. 执行 Transact-SQL 语句以填充游标。（声明游标）
3. 检索想要查看的游标中的行。从游标中检索一行或多行的操作称为提取。执行一系列的提取操作以向前或向后检索行的操作称为滚动。
4. 根据需要，对游标中当前位置的行执行修改操作（更新或删除）
5. 关闭游标。

8、 使用游标取数：

fetch…..from  游标  into @变量（表）

注意：INTO允许将提取的数据放入变量表中，各变量的数据类型要与游标选择列表中的相同。同时变量的数目要与列表中列的数目一样。

9、 与游标相关的系统全局变量：

@@fetch_status：返回FETCH语句的最后状态  0——表示获取成功，-1——表示获取失败或此行不在结果集中，-2表示被提取的行不存在。

@@cursor_rows：返回本次在服务器连接中打开游标取回的数据行的数目。

10、 利用游标修改数据：

更新操作：update 表名 set 列名= 表达式  where current of 游标名
删除操作：  delete from 表名  where current of 游标名

六、事务

1、事务的作用：

transaction是并发控制的基本单位。SQL Server通过事务机制，将逻辑相关的一组操作捆绑在一起，以便服务器保持数据的完整性

2、事务处理控制语句

begin tain  -- 开始一个事务名
commit tain -- 提交一个事务rollback ——回滚一个事务。（在发生情况时，撤消提交）

存储过程

一、存储过程（stored procedure）

• 1、存储过程是一组预先编译好的T-SQL代码。
• 2、存储过程的作用

二、创建存储过程

1、系统存储过程以SP_或DT_开头

2、创建存储过程的语法：

create procedure 存储过程名 [{@参数名 数据类型}[=default][output][{@参数名 数据类型}[=default][output]
AS Select 语句

三、执行存储过程

• 在查询分析器中使用。当是第一条语句时，可以直接用存储过程名，否则之前加上exec 。
• 如：exec xs

-- 实际上很像函数了
create proc get_Info_dept @tarName char(20)
as select
sname, cname, grade 
from student join sc on student.sno = sc.sno
join course on sc.cno = course.cno
and sdept = @tarName

exec get_Info_dept '会计系'

exec get_Info_dept @tarName = '会计系'

drop procedure get_Info_dept

1.默认参数

如果没有指定参数的话，就会使用默认的参数进行操作

drop proc p_student

create proc p_student
  @dept char(20)='计算机系',
  @sex  char(2)='男',
  @age  int =10
as 
 select * from student
        where sdept=@dept
        and ssex=@sex
        and sage>=@age
	

exec p_student 
exec p_student '计算机系','男',2
exec p_student @sex='男'

2.有输出的存储过程

drop proc proc1
go
create proc proc1
  @var1 int,
  @var2  int ,
  @var3  int output
as 
  set @var3=@var2*@var1

declare @result int 
exec   proc1 5,7, @result output
print @result 
-- 需要传入一个参数来接受结果

-- 就是var3在函数内部是形参这里使用了output关键字使得其与result也就是实参进行了关联。修改会反映到result上

-- 再来一个例子
create proc proc2
  @cn char(20),
  @avggrade int output
 as
   select @avggrade=avg(grade) from sc join
     course on sc.cno=course.cno 
	   and cname=@cn
	   
declare @avg_grade int 
exec   proc2 '数据库原理', @avg_grade output
print @avg_grade  

-- 使用return

CREATE proc changeteacher  (@tno1 int)
AS 
begin
declare  @counter int
declare  @returnvalue int
 set @returnvalue=0
 select  @counter=count(*) from teacher where tno=@tno1
 if(@counter>0)
 BEGIN
  set @returnvalue=1
   update teacher set proff='tno1'  where tno=@tno1

 end
   return @returnvalue
end
go

EXECUTE changeteacher 804

四、修改存储过程

• alter procedure 存储过程名 AS select 语句
• 例：alter procedure xs as select 学号,姓名 from 学生表

五、删除存储过程

• drop procedure 存储过程名

触发器

DML(数据操作语言,Data Manipulation Language)触发器

DML触发器是一些附加在特定表或视图上的操作代码，当数据库服务器中发生数据操作语言事件时执行这些操作。SqlServer中的DML触发器有三种：

• insert触发器：向表中插入数据时被触发；
• update触发器：修改表中数据时被触发；
• delete触发器：从表中删除数据时被触发。

触发器的建立必须是批处理中的第一条语句。触发器建立好后，由SQL Server系统自动执行。（与索引一样）

一个表中可以建立很多个触发器。一般一种操作建立一个。

--Insert 触发器
Create TRIGGER [dbo].[Trigger_Insert]
   ON  [dbo].[Person]
   AFTER INSERT
AS 
BEGIN
	
	SET NOCOUNT ON;

	Declare @age int;
	Select @age=Age  From inserted

	--如果年龄小于150正常插入，否则数据回滚
	IF(@age<150)
		Begin
			Insert into PersonLog(PersonID, Name, Age, AddDate)
			Select ID, Name, Age, AddDate From inserted
		End
	ELSE
		Begin
		    print('年龄应小于150')
			rollback transaction     --数据回滚
		END
    
END

内容修改检测

我们使用触发器进行内容修改操纵时，不会马上再原表里面进行修改，而是有一个临时的表进行存储。

我们通过这个临时表进行内容检测

测试insert和update语句是否对指定字段有影响

action	Effected table	effect
insert	Inserted表	插入的内容会暂时存在这里面存在
delect	Deleted表	删除的内容
update	Inserted表，Deleted表	之前的内容在deleted，之后的在inserted
rollback		回退操作

Create trigger tri_grade on sc for update as
-- if update 用于判断是否要
If update(grade) 
   if exists(select * from inserted join deleted on inserted.sno=deleted.sno where inserted.grade>=60 and deleted.grade<60)
rollback

DDL(数据定义语言,Data Definition Language)触发器

DDL触发器是当服务器或者数据库中发生数据定义语言(主要是以create,drop,alter开头的语句)事件时被激活使用，使用DDL触发器可以防止对数据架构进行的某些更改或记录数据中的更改或事件操作。