PLSQL程序设计

《PLSQL程序设计》由会员分享，可在线阅读，更多相关《PLSQL程序设计（43页珍藏版）》请在装配图网上搜索。

1、 1PL/SQL程序设计程序设计 2什么是什么是PL/SQL对对SQL的扩充的扩充过程化过程化后台技术后台技术 3PL/SQL 程序结构程序结构Declarebeginexceptionend/4变量类型变量类型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程序控制结构程序控制结构IF e1 THENELSI

2、F e2 THENELSIF e3 THENELSEEND IF;WHILE eLOOPEND LOOP;FOR i IN 1.10LOOPEND LOOP;EXIT WHEN e;6例例 外外EXCEPTIONWHEN 例外名1 THEN WHEN 例外名2 THEN WHEN OTHERS THEN END；7系统预定义例外系统预定义例外 dup_val_on_index no_data_found too_many_rowsSelect *into 8用户自定义例外用户自定义例外例外名定义例外名定义 ex1 exception;引起例外引起例外 raise ex1；9例例 一一用户注册检

3、查tin :tout:userlog :Ud psmsgUserid 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例例 一一 if tin_rec.ps=v_passwd then insert into tout values(login ok);else rai

4、se 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结构，利用光标可以命名这些工作区，结构，利用光标可以命名这些工作区，并通过光标访问工作区中的

5、信息。并通过光标访问工作区中的信息。13隐式光标隐式光标隐式光标名：SQL访问：SQLROWCOUNTSQL%FOUNDSQL%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 e

6、mp 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_sal500000 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_n

7、um+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 c1loop r.salend loop;如何打开光标如何读光标区如何判断读完如何关闭光标 20建立数据库对象触发器建立数据库对象触发器Create trigger emp_checkbefore/after insert or update or delete on empbeginif to_char(sysdate,dy)=sun then rai

8、se_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 raise_salary(12,46.50)；end;23分布式数据库功能分布式数据库功能 24分布式分布式D

9、B物理上分布在网络不同节点上的数据，从逻辑上可看作是一个整体（DB）。每个节点具有场地自治跨节点的全局操作 25访问远程数据库访问远程数据库本地(北京)orcale1远程(上海)oracle2ora1ora2数据库链数据库链S2 26访问远程数据库上的表访问远程数据库上的表 create database link L1 connect to 用户名用户名 identified by 口令口令 using s2;select*from depL1;select ename,tel from emp,depL1 where emp.dno=dep.dno;update depL1 set tel

10、=2233;27建立数据库别名建立数据库别名create synonym dep for depL1;select ename,tel from emp,dep where emp.dno=dep.dno;28分片 29分片分片-视图视图Create view emp as select*from emp1L1 union select*from emp2L2；create view emp as select emp1.ename,emp2.sal from emp1,emp2 where emp1.eno=emp2.eno;水平分片垂直分片 30复制复制 快照快照Create snapsh

11、ot emp refresh fast complete force start with sysdate next next_day(sysdate,Monday)as select *from empL1;触发器触发器 Create snapshot log on emp 31并发控制并发控制 32事务的概念事务的概念事务是一个操作序列，一个事务中的操作要么都做，要么都不做。事务是一事务是一个操作序列，一个事务中的操作要么都做，要么都不做。事务是一个不可分割的工作单元（原子性）。个不可分割的工作单元（原子性）。事务是并发控制的基本单位。事务是并发控制的基本单位。事务开始事务开始 事务结束（

12、事务结束（commit/rollback)DDL语句语句 单语句事务单语句事务set autocommit on (sql*plus)33rollback ；savepoint arollback to a事务回滚人为回滚 系统回滚 34并发存取数据库并发存取数据库R：读用户 W：写用户 RRWWRW 35W-W1010 8810 77写丢失写丢失 36防止写丢失加锁防止写丢失加锁自动加行锁自动加行锁 insert、update、delete 读提前加锁读提前加锁 select *For update of 锁语句锁语句 LOCK 在事务中加的锁在事务结束时自动打开。在事务中加的锁在事务结束时

13、自动打开。37死锁死锁UPDATE empSET sal=sal+500WHERE ename=张珊;UPDATE empSET sal=sal+700WHERE ename=李禾;UPDATE empSET sal=sal+300WHERE ename=李禾;UPDATE empSET sal=sal+200WHERE ename=张珊;事务A事务B 38如何解决死锁如何解决死锁OracleOracle系统在检测到一个死锁时，它会给引起死锁的其中系统在检测到一个死锁时，它会给引起死锁的其中一个事务发出一个错误信息，然后回滚该事务的当前语句一个事务发出一个错误信息，然后回滚该事务的当前语句。并

14、告之用户应显式地回滚他的事务，而其他用户就会因。并告之用户应显式地回滚他的事务，而其他用户就会因获得资源而完成事务，死锁就被解开了。获得资源而完成事务，死锁就被解开了。39读事务实现读一致性只读事务实现读一致性只读事务Set transaction read only 只读事务commit 写事务 40防止读脏数据防止读脏数据READ写事务 41防止读脏数据防止读脏数据writeread 42并发控制演示并发控制演示5Select num .for update5Update num=3CommitUpdate num=1?33Update num=111Select num3commitSelect num1 43并发控制演示并发控制演示Set transaction read only5Select num 5Update num=3 Select num commit commit commit 353Select num Select num 1Update num=1 1