PL/SQL程序设计.

Presentation on theme: "PL/SQL程序设计."— Presentation transcript:

1 PL/SQL程序设计

2 什么是PL/SQL 对SQL的扩充 过程化 后台技术

3 PL/SQL 程序结构 Declare begin exception end /

4 an_sal :=mon_sal *12 + nvl(comm,0) ;
变量类型 v_ename char(10) ; married boolean :=true ; v_ename emp.ename %TYPE ; emp_rec emp % ROWTYPE ; 变量赋值 an_sal :=mon_sal *12 + nvl(comm,0) ; Select ename INTO v_ename from emp where eno=` e01 ` ;

5 程序控制结构 EXIT [WHEN e] ; IF e1 THEN ELSIF e2 THEN ELSIF e3 THEN ELSE
END IF; WHILE e LOOP END LOOP ; FOR i IN LOOP END LOOP ; EXIT [WHEN e] ;

6 例 外 EXCEPTION WHEN 例外名1 THEN … WHEN 例外名2 THEN … … WHEN OTHERS THEN … …
… … WHEN OTHERS THEN … … END ；

7 系统预定义例外 dup_val_on_index no_data_found too_many_rows Select * into …

8 用户自定义例外 例外名定义 ex1 exception ; 引起例外 raise ex1 ；

9 例 一 用户注册检查 tin : tout : userlog : Ud ps msg Userid passwd …

10 例 一 DECLARE tin_rec tin % rowtype ; v_passwd userlog.passwd % type ;
errps EXCEPTION ; BEGIN select * into tin_rec from tin ; select passwd into v_passwd from userlog where userid = tin_rec.ud ;

11 例 一 insert into tout values(`login ok` ); else raise errps ; end if ;
if tin_rec.ps = v_passwd then insert into tout values(`login ok` ); else raise errps ; end if ; exception when errps then insert into tout values(`password error`〕； when no_data_found then insert into tout values(`userid error’〕； end;

12 光 标 Oracle 使用Private SQL Area的工作区执行SQL语句，并保存语句执行结果和相关的状态信息。
光标是一个PL/SQL结构，利用光标可以命名这些工作区，并通过光标访问工作区中的信息。

13 隐式光标 隐式光标名：SQL 访问： SQL％ROWCOUNT SQL%FOUND SQL%NOTFOUND

14 显式光标 定义 delcare cursor c1 is select … 打开 open c1 读 fetch c1 into 变量/记录
close c1

15 显式光标 读取状态信息 c1%found c1%rowcound 用光标定义记录 temp c1%rowtype

16 例 二 问题： 为职工长工资。从最低工资长，工资总额限制在50万元。 Declare
cursor c1 is select eno,sal from emp order by sal for update of sal ; emp_num number :=0 ; s_sal number ; e_sal number ; e_eno char(3);

17 例 二 Begin open c1; select sum(sal) into s_sal from emp ;
while s_sal<500000 loop fetch c1 into e_eno,e_sal ; exit when c1%notfound ; update emp set sal=sal*1.1 where eno=e_eno ; s_sal:=s_sal + e_sal*0.1; emp_num:=emp_num+1 ; end loop ; where current of c1 ;

18 例 二 Close c1; insert into msg values(emp_num , s_sal) ; commit ; end;

19 光标for循环 For r in c1 loop r.sal end loop; 如何打开光标 如何读光标区 如何判断读完 如何关闭光标

20 建立数据库对象－触发器 Create trigger emp_check
before/after insert or update or delete on emp begin if to_char(sysdate,`dy`)=`sun` then raise_application_error(…,`… … `) ; end if , end ;

21 建立存储过程 Create procedure raise_salary (emp_id integer,increase real) is
begin update emp set sal=sal+increase where eno=emp_id ; end ;

22 存储过程调用 在SQL*Plus中 execute raise_salary(12,46.50) 在PL*SQL中 begin
end ;

23 分布式数据库功能

24 分布式DB 物理上分布在网络不同节点上的数据，从逻辑上可看作是一个整体（DB）。 每个节点 具有 场地自治 跨节点 的 全局操作

25 访问远程数据库 本地(北京) orcale1 远程(上海) oracle2 ora1 ora2 数据库链 S2

26 访问远程数据库上的表 create database link L1 connect to 用户名 identified by 口令
using `s2`; select * from ; select ename,tel from emp, where emp.dno=dep.dno ; update set tel=`2233` ;

27 建立数据库别名 create synonym dep for dep@L1 ;
select ename,tel from emp, dep where emp.dno=dep.dno ;

28 分片

29 分片 - 视图 水平分片 垂直分片 create view emp as select emp1.ename,emp2.sal
select * from union select * from 垂直分片 create view emp as select emp1.ename,emp2.sal from emp1,emp2 where emp1.eno=emp2.eno ;

30 Create snapshot log on emp
复制 － 快照 Create snapshot emp refresh fast complete force start with sysdate next next_day(sysdate,`Monday`) as select * from ; 触发器

31 并发控制

32 事务的概念 事务是一个操作序列，一个事务中的操作要么都做，要么都不做。事务是一个不可分割的工作单元（原子性）。 事务是并发控制的基本单位。
事务开始－ 事务结束（commit / rollback) DDL语句 － 单语句事务 set autocommit on (sql*plus)

33 事务回滚 rollback ； savepoint a rollback to a 人为回滚 系统回滚

34 并发存取数据库 R：读用户 W：写用户 R W R W

35 W - W 10 8 7 8 10 写丢失 10 7

36 防止写丢失－加锁 自动加行锁 insert、update、delete 读提前加锁 select * … For update of …
锁语句 LOCK 在事务中加的锁在事务结束时自动打开。

37 死锁 事务A 事务B UPDATE emp SET sal=sal+500 WHERE ename='张珊';

38 如何解决死锁 Oracle系统在检测到一个死锁时，它会给引起死锁的其中一个事务发出一个错误信息，然后回滚该事务的当前语句。并告之用户应显式地回滚他的事务，而其他用户就会因获得资源而完成事务，死锁就被解开了。

39 Set transaction read only 只读事务
实现读一致性－只读事务 读事务 Set transaction read only 只读事务 commit 写 事 务

40 防止读脏数据 写事务 READ

41 防止读脏数据 write read

42 并发控制演示 5 5 3 3 1 3 1 1 Select num ...for update Update … num=1 ?
cummit 3 Update … num=1 1 Select num 3 cummit 1 Select num 1

43 Set transaction read only
并发控制演示 5 Set transaction read only Select num 5 Update num=3 3 3 commit Select num 5 Update num=1 1 commit Select num 3 commit 1 Select num 1