PLSQL程序设计.ppt

PL/SQL程序设计,什么是PL/SQL,对SQL的扩充过程化后台技术,PL/SQL 程序结构,Declarebeginexceptionend/,变量类型,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 ;,程序控制结构,IF e1 THENELSIF e2 THENELSIF e3 THENELSEEND IF;,WHILE eLOOPEND LOOP ;,FOR i IN 1.10LOOPEND LOOP ;,EXIT WHEN e ;,例 外,EXCEPTIONWHEN 例外名1 THEN WHEN 例外名2 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,例 一,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 ;,例 一,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访问：SQLROWCOUNTSQL%FOUNDSQL%NOTFOUND,显式光标,定义 delcare cursor c1 is select 打开 open c1读 fetch c1 into 变量/记录close c1,显式光标,读取状态信息c1%found c1%rowcount用光标定义记录temp c1%rowtype,例 二,问题：为职工长工资。从最低工资长，工资总额限制在50万元。,Declare cursor c1 is select eno,sal from emp order by sal for update ; 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 c1loop r.salend loop;,如何打开光标如何读光标区如何判断读完如何关闭光标,带参数光标,cursor c1 (depno char(3) is select * from emp where dno=depno ; open c1(v_depno) ;,例 三,例题： 收发室收文件。文件分类，每类中文件依收到日期先后顺序编号。值班人员收到文件后，要在系统中登录文件类别，编号，收到日期。 （注：一天中收到的文件可能不止一份）编一PL/SQL 程序，查询每一类中最后一天收到的文件）表clog(kno 类别 , pno 编号 , pdate 收发日期) 练习要点：光标FOR循环，带参数的光标,例 三,Declare cursor c1 is select distinct kno from clog; cursor c2 (kindno char(3) is select * from clog where kno=kindno order by day desc ; i number ; l_d date ;begin,例 三,For r1 in c1loop i := 0 ; for r2 in c2(r1.kno) loop i := i + 1 ; if i=1 then insert into tout values(r2.kno,r2.pno,r2.pdate); l_d:=r2.pdate; elsif r2.pdate = l_d then insert into tout values(r2.kno, r2.pno,r2.pdate); else exit; end if ; end loop;end loop ;,记录多域单行结构,Declaretype time_type is record (second integer(2) :=0 , minute integer(2) :=0, hour integer(2) :=0 ) ; time_rec time_type ;,PL/SQL表(数组)多行单列结构,Declare type ename_type is table of char(10) index by binary_integer ;ename_array ename_type ;I binary_integer :=0 ;begin for rec in (select ename from emp) loop I:=I+1 ; ename_array(I):=rec.ename ;end loop ;,PL/SQL表(数组)多行多列结构,Declare Type t_studentTab is TABLE of students%ROWTYPEstudent_array t_studentTalbe ;I binary_integer :=0 ;begin for rec in (select * from students) loop I:=I+1 ; student_array(I):=rec ;end loop ;,PL/SQL表属性,student_array.countstudent_array.delete(I);student_array.delete(I , j );student_array.existsstudent_array.first student_array.laststudent_array.nextstudent_array.prior,例外信息捕获,Exception when others then err_msg:=substr(sqlerrm,1,50); err_code:=sqlcode; insert into tout values(err_code,err_msg) ;end ;,PL/SQL块的嵌套及例外转移,D01部门没有职工属正常情况 begin select eno into v_eno from emp where dno=d01 ; if sql%found then raise too_many_rows ; end if ;exception when no_data_found then null ;end ;,PL/SQL块中特殊的例外转移,Declare段发生例外V_Number NUMBER(3) :=ABC;立刻传播到包含块Exception段发生例外 Excepionwhen a thenraise b;when b then 立刻传播到包含块,利用PL/SQL块中特殊的例外转移,Declarea exception;Beginraise a;Exceptionwhen a theninsert into log_able.;raise;End;,触发器,存储在数据库里的带名的PL/SQL块，当表被修改时，如果满足条件，自动执行主要用处维护复杂的完整性约束审计修改表被修改时，给其他需运行的程序发信号,触发器创建语法,Create or Replace trigger emp_checkbefore/after insert or update or delete for each rowon emp when beginend ;,建立数据库对象触发器,Create trigger emp_checkbefore/after/instead of insert or update or delete on empbeginif to_char(sysdate,dy)=星期日 then raise_application_error(-20000, today is holiday ') ;end if ;end ;/,建立数据库对象触发器,Insert into emp values(. );错误信息：错误位于第1行：ORA-20000: today is holidayORA-06512: 在 "WXY.EMP_CHECK", line 3ORA-04088: 在执行触发子 'WXY.EMP_CHECK' 时出错,触发器类型,语句级触发器行级触发器（for each row）:old:newInstead-of触发器,触发器触发顺序,Before 语句级Before 行级DMLAfter 行级After 语句级,触发器限制,不能有事务控制语句调用的过程和函数也不能有事务控制语句不能声明long, long raw列语句级触发器不能使用:old, :new行级触发器存取变化表与限制表,Create trigger emp_tbefore update of sal on empfor each rowwhen (old.dno is not null)begin if (:new.sal < = :old.sal) then raise_application_error( ); end if ;end ;,建立数据库对象触发器,Create or replace trigger generateidBefore insert on students For each rowBeginSelect student_seq.nextval into :new.id from dual;End;,行触发器例,触发器调试,temp(a,b,c) tempbakcreate or replace trigger t1after inserton tempfor each rowbegin insert into tempbak values(new.a,:new.b,:new.c);end;/,触发器调试,警告：已创建的触发程序出现编译错误。SQL> show errorTRIGGER T1出现错误：LINE/COL ERROR- -2/2 PL/SQL: SQL Statement ignored2/25 PLS-00201: 标识符 'NEW.A' 必须说明,存储过程,Create or replace procedure raise_salary (emp_id integer,increase real)is begin update emp set sal=sal+increase where eno=emp_id ;end ;,存储过程创建语法,Create or replace procedure procedure_name (argument in|out|in out type, ) is|as 说明部分Begin 执行部分Exception 例外处理部分end procedure_name;,函数,Create or replace function raise_salary (emp_id integer,increase real)RETURN BOOLEANis begin update emp set sal=sal+increase where eno=emp_id ; RETURN TRUE;end ;,函数创建语法,Create or replace function function_name (argument in|out|in out type, )Return type is|as beginExceptionend function_name;,子程序参数模式,三种模式IN传入，只读OUT传出，只写IN OUT传入传出，可读写,子程序参数限制,1. OUT, IN OUT只能用变量调用p(in, in, out)p(a,b,c); -错误2. 实参限制p(v1 out varchar2, v2 out number, v3 out char)a1 varchar2(10); a2 number(8); a3 char(6);p(a1, a2, a3);3.特殊情况形参限制p (v1 out students.name%type),子程序参数标识,1. 位置标识p(v1, v2, v3)p(a1, a2, a3)2. 带名标识p(v1 => a1, v3 => a3, v2 => a3);3.参数缺省值p (v1 in varchar2, v2 in varchar2 v3 in varchar2 default aaaa)p(a1, a2, a3); p(a1, a2);,子程序权限,1.执行权限子程序在其拥有者的权限下运行2. 依赖对象的权限直接授予拥有者，不能通过角色授予3. 原因a. oracle早期联编b.角色是会话级别的,子程序例外返回,1.未处理例外子程序返回调用者或调用环境2. Out 及in out 型变量值 保留调用时的值,子程序调用,在SQL*Plus中 execute raise_salary(12,46.50)在PL*SQL中 begin raise_salary(12,46.50)； end ;,子程序相关数据字典,USER_OBJECTS所有对象，包含子程序USER_SOURCE存储过程，函数，触发器，包的源码USER_ERRORS编译过程中的错误信息,包（package),包是一个可以将相关对象存储在一起的PL/SQL结构。包提供了PL/SQL的全局变量包中过程可重载不同会话使用包的不同拷贝,包结构图,应用程序,包规范和主体,包规范/接口(specification) 变量，常量，过程，函数，光标，例外 。 为PL/SQL程序提供了全局对象。包主体(body)过程，函数的实现包依赖性(user_dependencies)主体依赖接口，接口不依赖主体，减少重编译,例四包规范,CREATE OR REPLACE PACKAGE ClassPackage AS - Add a new student into the specified class. PROCEDURE AddStudent( p_StudentID IN students.id%TYPE, p_Department IN classes.department%TYPE, p_Course IN classes.course%TYPE); - Removes the specified student from the specified class. PROCEDURE RemoveStudent( p_StudentID IN students.id%TYPE, p_Department IN classes.department%TYPE,包规范,p_Course IN classes.course%TYPE); - Exception raised by RemoveStudent. e_StudentNotRegistered EXCEPTION; - Table type used to hold student info. TYPE t_StudentIDTable IS TABLE OF students.id%TYPE INDEX BY BINARY_INTEGER; - Returns a PL/SQL table containing the students currently in the specified class. PROCEDURE ClassList( p_Department IN classes.department%TYPE,例四包规范,p_Course IN classes.course%TYPE, p_IDs OUT t_StudentIDTable, p_NumStudents IN OUT BINARY_INTEGER);END ClassPackage;,例四包体,CREATE OR REPLACE PACKAGE BODY ClassPackage AS - Add a new student for the specified class. PROCEDURE AddStudent( p_StudentID IN students.id%TYPE, p_Department IN classes.department%TYPE, p_Course IN classes.course%TYPE) IS BEGIN INSERT INTO registered_students (student_id, department, course) VALUES (p_StudentID, p_Department, p_Course);,例四包体,COMMIT; END AddStudent; - Removes the specified student from the specified class. PROCEDURE RemoveStudent( p_StudentID IN students.id%TYPE, p_Department IN classes.department%TYPE, p_Course IN classes.course%TYPE) IS BEGIN DELETE FROM registered_students WHERE student_id = p_StudentID and,例四包体,department = p_Department AND course = p_Course; IF SQL%NOTFOUND THEN RAISE e_StudentNotRegistered; END IF; COMMIT; END RemoveStudent; - Returns a PL/SQL table containing the students currently - in the specified class.,例四包体,PROCEDURE ClassList( p_Department IN classes.department%TYPE, p_Course IN classes.course%TYPE, p_IDs OUT t_StudentIDTable, p_NumStudents IN OUT BINARY_INTEGER) IS v_StudentID gistered_students.student_id%TYPE; - Local cursor to fetch the registered students. CURSOR c_RegisteredStudents IS SELECT student_id FROM registered_students WHERE department = p_Department AND course = p_Course;,例四包体,BEGIN /* p_NumStudents will be the table index. It will start at 0, and be incremented each time through the fetch loop. At the end of the loop, it will have the number of rows fetched, and therefore the number of rows returned in p_IDs. */ p_NumStudents := 0;,例四包体,OPEN c_RegisteredStudents; LOOP FETCH c_RegisteredStudents INTO v_StudentID; EXIT WHEN c_RegisteredStudents%NOTFOUND; p_NumStudents := p_NumStudents + 1; p_IDs(p_NumStudents) := v_StudentID; END LOOP; END ClassList;END ClassPackage;,系统包,位于$ORACLE_HOME/rdbms/admin/DBMS_OUTPUTDBMS_SQLDBMS_PIPEDBMS_JOB,在开发语言里使用Oracle,常用开发语言（工具）OCI接口: C COBOL FORTRAN, 嵌入SQL语句: Pro*C FORTRAN PASCALCOBOL PL/I Ada图形化开发：Develop 2000, Delphi, PowerBuilder, C+ BuilderWeb 应用开发PL/SQL Web工具包PHP, Perl, Java,分布式数据库功能,分布式DB,物理上分布在网络不同节点上的数据，从逻辑上可看作是一个整体（DB）。,每个节点具有场地自治,跨节点的全局操作,访问远程数据库,本地(北京)orcale1,远程(上海)oracle2,ora1,ora2,数据库链,S2,$HOME/network/admin/tnsname.ora,访问远程数据库上的表,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=2233 ;,建立数据库别名,create synonym dep for depL1 ;select ename,tel from emp, dep where emp.dno=dep.dno ;,分片,create view emp as select emp1.ename,emp2.sal from emp1,emp2 where emp1.eno=emp2.eno ;,分片 - 视图,Create view emp as select * from emp1L1 union select * from emp2L2；,水平分片,垂直分片,复制 快照,Create snapshot emp refresh fast complete force start with sysdate next next_day(sysdate,Monday) as select * from empL1 ;,触发器,Create snapshot log on emp,时间表示,下周的同一天sysdate + 7每星期五上午9点next_day( trunc(sysdate) , 星期五 ) + 9/24每一小时sysdate +1/24每10秒sysdate + 10/(24*60*60),过程中的分布处理与远程过程调用,Create procedure raise_salary (emp_id integer,increase real)is begin update empL2 set sal=sal+increase where eno=emp_id ;end ;begin raise_salaryL1(12,46.50)；end ;,并发控制,事务的概念,事务是一个操作序列，一个事务中的操作要么都做，要么都不做。事务是一个不可分割的工作单元（原子性）。事务是并发控制的基本单位。事务开始 事务结束（commit / rollback)DDL语句 单语句事务set autocommit on (sql*plus),事务回滚,人为回滚 系统回滚,rollback ；,savepoint a,rollback to a,并发存取数据库,R：读用户 W：写用户,W - W,10,10,8,8,10,7,7,写丢失,防止写丢失加锁,自动加行锁 insert、update、delete 读提前加锁 select * For update of 锁语句 LOCK 在事务中加的锁在事务结束时自动打开。,死锁,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,如何解决死锁,Oracle系统在检测到一个死锁时，它会给引起死锁的其中一个事务发出一个错误信息，然后回滚该事务的当前语句。并告之用户应显式地回滚他的事务，而其他用户就会因获得资源而完成事务，死锁就被解开了。,实现读一致性只读事务,Set transaction read only 只读事务commit,写事务,包过程：dbms_transaction.read_only,只读事务几点注意,SET TRANSACTION READ ONLY必须是事务的第一条语句。在只读事务里，只允许用户执行读语句（SELECT），而不能执行写语句。 COMMIT语句、BOLLBACK语句或一个DDL语句都会结束只读事务。在只读事务中所有的查询都是引用数据库的一个只读版本，即只读事务开始那一时刻数据库中的数据。也就是说，只读事务开始之后，其他事务对数据库的修改（而且是提交了的），在只读事务中看不到。 只读事务不影响其他事务的操作，其他事务依然可以对数据库的数据进行查询或更新。就是说，读一致性并没有以限制其他用户的写为代价。,防止读脏数据,READ,写事务,防止读脏数据,write,read,并发控制演示,5,Select num .for update,5,Update num=3,cummit,Update num=1 ?,3,3,Update num=1,1,1,Select num,3,cummit,Select num,1,并发控制演示,Set transaction read only,5,Select num,5,Update num=3,Select num,commit,commit,commit,3,5,3,Select num,Select num,1,Udate num=1,Oracle 事务的种类,读写事务（缺省）set transaction read write ;只读事务set transaction read only 查询结果是事务开始时刻数据库中的数据。离散事务推迟了许多耗时的修改并记录SQL语句所做的修改的内部系统操作。延迟并集中事务的修改，直到应用提交一个离散事务。减少事务开销，会提高联机事务处理的系统性能定义离散事物执行系统内部包过程execute dbms_transaction.begin_discrete_transaction,事务设计,事务设计的好坏直接影响数据库的完整性及应用的性能（一）工作单元事务的含义是将一些紧密相关的SQL语句包含起来。完成一项任务，形成一个工作单元。一个事务不能包含多个工作单元，也不能是一个工作单元的部分工作。（1）事务包含多个工作单元 ？当一个事务含有多个工作单元时，数据库必须长时间地维护代表事务的内部系统信息。这会降低系统性能。特别是当oracle同时承担多个这样的不良事务设计时更糟。例 (事务含有3个独立单元，不相干的事）insert into emp; insert into part; insert into orders;,事务设计,（2）一些不必要的小事务提交会降低服务器的性能。例：insert into dep ; commit ; insert into emp commit ; insert into emp. commit ;部分化的事务会影响数据库数据的完整性如果输入部分职工记录就提交。另一用户（会计）看部门有职工了，就去读不完整的数据，根据不完整的职工清单做了工资报表。,事务设计,（二）约束检查时间（1）在执行插入、修改、删除记录后立即执行约束检查（2）延迟到应用提交事务时再执行约束检查特定的应用也许会更新许多表，在事务结束之前会暂时违反完整性约束延迟约束指定：create table temptable (a number primary key deferrable initially deferred);create table temptable (a number primary key deferrable);set constraint all / 约束名 deferred / immediate,