Oracle8 PL SQL编程风格与系统性能的提高.doc

Oracle8 PL/SQL编程风格与系统性能的提高杨延广曹素丽摘要：介绍了Oracle 8 PL/SQL程序设计中，提高系统性能的编程风格的几种方法，和存储过程及触发器的使用、错误处理等。 关键词：Oracle；SQL；存储过程；触发器；错误处理 中图分类号：TP311.11文献标识码：B 文章编号：1001-9081(2000)02-0073-02 Oracle是C/S（Client/Server）结构的大型数据库，主要语言是4GLSQL语言，具有功能强大而简单易学的优点。C/S结构的工作过程是：当客户端（C端）输入并发送一条SQL语句后，便通过网络送到服务器端（S端），在那里被分析执行然后再将结果通过网络返回到客户端，此时客户端可以再发下一条SQL语句。可见，客户端是一条语句一条语句的发送，服务器端是一条一条的分析执行，结果再一次次的返回。但是，这样单个的SQL语句引起了频繁的网络通信，大大降低了系统性能。PL/SQL是扩展SQL后的语言，PL/SQL块作为一个单位发送，使用了较少的网络通信，而且在运行中能根据条件，决定执行或重复执行什么语句，既保留了SQL的强大性，又弥补了不足。 1提高程序的运行速度 1.1使用存储过程 存储过程是Oracle数据库的一种对象，是一种带名的PL/SQL过程程序块，它在创建后，被数据库服务器进行语法和句法分析，以编译了的形式存储在数据库中，可以被有权用户在任何需要的地方调用。当客户端应用程序调用时，只需发送一条调用命令，数据库服务器就会执行该过程。与一般的PL/SQL块主要的不同是：无需在网上传送大量的源程序代码，只传送一条调用命令，这就大大降低了网络通信的负担；而且只在刚创建时分析编译一次，每次调用直接执行编译了的代码，因此运行速度较快。 在实际开发时，对于具有共同特性的功能模块最好使用存储过程，调用时通过使用不同的实际参数值来实现某一具体的处理。如果能充分利用存储过程来完成应用系统的操作与处理，则可大大提高系统的运行性能。 1.2编写可重用共享池中已有语句的SQL语句 共享内存缓冲区和后台进程合称为一个Oracle实例。当启动一个Oracle实例时，会有许多的Oracle后台进程被启动，每个进程都负责运行数据库的不同方面的处理，各进程通过共享内存彼此之间进行通信，该块内存就是系统全局区SGA。SGA被分隔为不同的区域，其中一个称作共享池（Shared Pool）的区域中包含了发送给数据库的SQL语句的正文和PL/SQL块，以及它们经过分析后的表示形式与执行方案，其中执行方案是数据库实际处理该语句的方法，例如，需要访问哪些表和索引、是否需要执行排序操作等等。 因为要执行一条SQL 语句，数据库就必须确定其执行方案。当数据库从客户应用程序接收到一条SQL语句时，它首先检查是否该语句在共享池中。若在，那么不重新对其分析而是重复使用已经在共享池中的形式及执行方案；若不在，则对该语句进行分析，得到新的分析形式与执行方案并进行存储，覆盖共享池中以前的内容。 由此可以看出，编写可重用共享池中语句的SQL语句就显得十分必要，因为避免不必要的重新分析，会很大程度上减小服务器所承担的工作量。要想重复使用共享池中的语句，就应该编写与其格式一致的语句，包括字母的大小写、标点符号、换行的位置等都要一致。下面推荐一种有效实用的方法。 1.2.1SQL语句各部分的格式 一条语句可以一行也可以分多行书写，但最好换行书写，每一子句一行，且每行的第一个关键字与第一行的关键字的尾部对齐，这样做以确保每次使用同一条语句时分行的位置一致，而不要让语句在第80列偶然溢出到下一行，例如下面的格式中字母T、M、E、D、P是对齐的，一子句占了一行： SELECT col1,col2 FROM tablename1 WHERE col1 col2 AND col2 col3 GROUP BY col1; 当刚执行过上述语句后，若又接收到下面的语句： SELECT col1,col2 FROM tablename2 WHERE col1 col2 AND col2 col3 GROUP BY col2; 则通过检查认为与共享池中的语句一致，可重复使用共享池中的执行方案，不必重新分析。 而下面的的语句被认为是不同的，因为分行的位置不同，需要重新进行分析。 SELECT col1,col2 FROM tablename2 WHERE col1 col2 AND col2 col3 GROUP BY col1 ; 1.2.2字母大小写采用一致约定 关键字、保留字大写，用户声明的标识符小写。请看下面的两条语句： SELECT xm FROM student； 与 select xm FROM student； 比较的结果是这两句不匹配或者说不等价，因为第一句中的SELECT是大写的，而第二句的是小写的。 1.2.3其它，如运算符两侧各留一个空格等 总之，设计自己的编写约定并遵守这些约定，使要处理的语句与共享池中的相一致，有助于运行性能的提高。 2提高可维护性 2.1编写触发器 对表中数据进行修改、删除或插入是非常常见的操作。当表被修改时，应该自动给其他需要执行操作的程序发信号。触发器可以完成这一功能。在Oracle8中，触发器是一段程序，但是这段程序是当发生INSERT、UPDATE或DELETE操作时被自动执行的，与过程的调用（是通过调用语句调用执行）不同，因此当某事件的发生引起连环更新或其他的相应操作时，通过自动执行触发器代码实现而不用人工干预，大大减轻了维护工作，同时也很好的保证了数据的一致性。 触发器的优点是自动激发，不管什么引起数据修改（来自程序的或是来自用户的），它们都工作，所以常常用于不同数据表中的相关数据的串接修改。采用这种方法实现数据表间接的数据关联可由数据库集中维护控制，规则变化时只需修改相应的触发器即可，这样系统易于维护，提高了工作效率。 2.2使用%TYPE、%ROWTYPE方式声明变量 程序设计中常常要通过变量来实现程序间的数据传递，即将表中数据赋值给变量，或是把变量值插入到表中。而要完成这些操作的前提就是，表中数据与变量类型要一致。然而在实际中，表中数据或类型、或宽度有时要变化，一旦变化，就必须去修改程序中的变量声明部分，否则程序将不能正常运行。为了减少这部分程序的修改，编程时使用%TYPE、%ROWTYPE方式声明变量，使变量声明的类型与表中的保持同步，随表的变化而变化，这样的程序在一定程度上具有更强的通用性。 3提高程序自检能力 一个好的应用系统不仅要有好的用户界面、齐全的功能处理模块，而且要有很强的错误处理能力。因此要求编程人员要预测可能的各种情况（声明异常情态并引发），并尽可能从错误中恢复过来（编写相应异常情态处理器代码），这就是Oracle8中的异常部分的程序设计内容。然而这部分的设计绝非易事。在异常部分的最后设置OTHERS异常情态处理器是个很好的编程习惯，因为它为运行时刻捕捉到的其它错误指明了处理去向，从而保证了程序的正常运行。格式如下： BEGIN EXCEPTION WHEN excepname1 THEN WHEN excepname2 THEN WHEN OTHERS THEN END; 但是对程序中出现的错误要做到正确的处理（即正确选择异常处理器并执行它），还必须弄清楚异常情态的传播问题。 异常情态的传播指的是当在程序块的声明、执行、异常部分分别出现异常情态时，或在本块中没有相应的异常处理器时会将这个异常情态传播到哪里，会去激发那个块中的处理器。传播规则是这样的： 当一个异常情态是在块的执行部分引发的（最常见的），PL/SQL使用下面的规则确定激活哪个异常处理器。（1）若当前块对该异常情态设置了处理器，则执行它并成功完成该块的执行，然后控制转给包含块。（2）若当前块没有该处理器，则通过在包含块中引发它来传播异常情态。然后对包含块执行步骤1。另外，无论是在声明部分引发了一个异常情态，还是在异常处理部分引发，则该异常情态将立即传播给包含块。在包含块引用上述规则进行异常情态的处理，即使在当前块设置了OTHERS处理器也不会被执行。 4易于阅读 .对于子程序、触发器、包等带名的程序块，使用结束标识。例如： CREATE OR REPLACE PROCEDURE addstud IS BEGIN END addstud;/* 此处的过程名add是可选的，写上较好，与块开始的CREATE相对应 */ .采用统一的标识符命名规则。对于诸如变量名、子程序名、触发器名等数据库对象命名时，应尽量能表示其功能用途或含义。 .对于过程性语句与程序块采用缩进书写风格，会使得程序结构清晰、层次分明、易阅读。 .采用统一的字母大小写。尽管PL/SQL程序中不区分大小写，但是采用统一的字母大小写（如前文叙述的大小写约定）将在很大程度上提高程序的可阅读性。 .加注释。 .一条语句分多行书写，不让其自动分行。 这方面的内容大家都已很熟悉，不再详述了。 总之，良好的程序设计风格，可以在多个方面提高系统的性能，提高开发效率，很值得我们在工作中给以重视。=PL/SQL Developer 使用技巧小结以下的技巧只针对PL/SQL Developer 6以上版本，版本5只有其中的部分特性。1，右键菜单在PL/SQL Developer（下面简称PLD）中的每一个文本编辑窗口，如SQL Window，Command Window和Porgram Window，右键点击某个对象名称，会弹出一个包含操作对象命令的菜单，我们这里称之为右键菜单。如下图：对象类型可以是表，视图，同义词，存储过程和函数等。根据对象类型的不同，弹出的菜单也有区别。表和视图有View, Edit, Rename, Drop, Query data 和Edit data等功能。View和Edit分别是查看和修改表的结构信息，如字段，主键，索引和约束等。Query data相当于新打开一个窗口，并执行select * from 表。Edit data相当于新打开一个窗口，并执行select * from 表 for update。存储过程和函数有Test功能，选中后可以进入调试状态。有时由于PLD识别错误，右键点击对象并不能出来正确的菜单，可以在对象所在的DDL或DML语句的前面，加上分号，这样PLD就能正确的判断出对象的类型2，Select for Update有时我们需要把一些数据导入数据库中，如果用UE拼Insert语句，会比较麻烦，而且操作性不强。PLD的SQL Window可以查询，新增，修改和删除表的内容。查询自不必说，而新增，删除和修改，只需在select语句后加入for update，对表进行行级锁定，然后点击窗口的锁型图标，即可进入编辑状态。下面介绍一下如何从Excel中提取文本插入到数据库中我们的Excel文件中有三列：在数据库中建立临时表：create table t1 (cino varchar2(100), contno varchar2(100), loanno varchar2(100)然后在SQL Window中输入select t1 for update，并点击锁型鼠标，进入编辑状态：用鼠标点击第一行的输入窗口，这时PLD会死锁几秒钟，然后可以见到光标在第一行的输入框中闪动，用鼠标把CINO, CONTNO, LOANNO选中：进入Excel中，把需要插入数据库的内容选中，然后切换到PLD，按Ctrl + V：点击，然后再点击Commit按钮，则数据提交到表t1中，执行select * from t1可以看到内容：3，PL/SQL BeautifierPLD 6以上版本有对DML代码格式化的功能。在SQL Window或Program Window中选中部分代码（如果不选则对整个窗口的代码操作），在菜单中选Edit PL/SQL Beautifier，得到格式化的代码。对于非法的DML语句或DDL语句，PLD将会在下方状态栏提示PL/SQL Beautifier could not parse text。在缺省的状态下，PLD会把DML语句的每一个字段都排在单独的一行，这样不方便查看。在菜单中选Edit PL/SQL Beautifier Options，进入Preferences窗口，选择Edit，进入配置文件编辑界面：在标签栏选DML，在窗口中部的Select, Insert和Update组框中把Fit选中，然后点击Save，把配置文件保存到PLD的安装目录下，点击Close关闭。在Rules file中输入配置文件所在位置，点击OK，完成配置文件切换。这时再对代码进行格式化，就可以使每一个字段尽可能的在一行上了。4，TNS Names菜单Help Support Info TNS Names，可以查看Oracle的tnsnames.ora。5，Copy to Excel在SQL Window中执行Select语句，在结果出来以后，右键点击下面的数据区，选择Copy to Excel，可以把数据区的记录原样拷贝到Excel中。但有两点需要注意：一，field中不能以=开始，否则Excel会误认为是函数；二，数字不要超过17位，否则后面的位数将会置为0，但可以通过在数字前加来使Excel认为该field是文本，同时对于数据库中Numbe类型的字段，最好用to_char输出，不然可能会显示不正常技巧：1、PL/SQL Developer记住登陆密码 在使用PL/SQL Developer时，为了工作方便希望PL/SQL Developer记住登录Oracle的用户名和密码；设置方法：PL/SQL Developer 7.1.2 -tools-Preferences-Oracle-Logon History ， “Store history”是默认勾选的，勾上“Store with password” 即可，重新登录在输入一次密码则记住了。2、执行单条SQL语句 在使用PL/SQL Developer的SQL Window时，按F8键，PL/SQL Developer默认是执行该窗口的所有SQL语句，需要设置为鼠标所在的那条SQL语句，即执行当前SQL语句；设置方法：PL/SQL Developer 7.1.2 -tools-Preferences-Window types ，勾上“AutoSelect Statement” 即可。3、格式化SQL语句 在使用PL/SQL Developer的SQL Window时，有时候输入的SQL语句太长或太乱，希望能用比较通用的写法格式话一下，这样看起来会好看些，也好分析；使用方法：选中需要格式化的SQL语句，然后点击工具栏的PL/SQL beautifier按钮即可.4、查看执行计划 在使用PL/SQL Developer的SQL Window时，有时候输入的SQL语句执行的效率，分析下表结构，如何可以提高查询的效率，可以通过查看Oracle提供的执行计划；使用方法：选中需要分析的SQL语句，然后点击工具栏的Explain plan按钮（即执行计划），或者直接按F5即可。5、调试存储过程 在使用PL/SQL Developer操作Oracle时，有时候调用某些存储过程，或者调试存储过程；调用存储过程的方法：首先，在PL/SQL Developer左边的Browser中选择Procedures，查找需要调用的存储过程；然后，选中调试的存储过程，点