PL/SQL程序设计

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

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

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 ` ;

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

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

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

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

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

例 一 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 ;

例 一 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;

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

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

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

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

例 二 问题： 为职工长工资。从最低工资长，工资总额限制在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);

例 二 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 ;

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

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

建立数据库对象－触发器 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 ;

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

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

分布式数据库功能

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

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

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

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

分片

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

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

并发控制

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

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

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

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

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

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

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

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

防止读脏数据 写事务 READ

防止读脏数据 write read

并发控制演示 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

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