码三狼-CSDN博客

原创 PLSQL入门与精通（第82章：尽量避免使用复杂的SQL）

本章不是技术性的话题，而是针对“很长很复杂的SELECT文”的一些说明和解释。为打到我们复杂的查询目的，Oracle提供了丰富的语法,我么在写SQL的时候，有的时候写了又长又复杂又的SELECT语句，反复会出问题:・如果一个SELECT语句里实现了很多的需求，变得特别复杂的话，修改很痛苦。如果我们后边维护的时候需要修改查询条件，就很麻烦。・其次，随着时间的推移数据量也会不停地增加，经常会发生性能恶化的问题。复杂SELECT文性能调优也变得很困难。复杂的SELECT大概发生在如下3种情况：首先

2021-04-06 20:40:56 337

原创 PLSQL入门与精通（第81章：利用游标进行递归调用的时候游标数超标问题）

这次我们重点说明一下递归调用的注意点。递归调用，因为循环处理次数很多，所以他是非常容易消耗资源的，这是我们要注意的。特别是在进行显示的游标处理时，如果关闭游标是在递归调用前进行的话，随着调用的传递，打开的游标数量会不停地增加，若超过游标数量的上限，则会出错：“ORA-01000：超过了开启的最大游标数”。因此，要注意显式游标和递归调用组合使用的时候的这个问题。接下来我们针对这个问题举例说明：我们接着使用第79章提到的例子：“用递归调用进行阶层查询”，我们用递归调用+显式游标的方式进行处理。程

2021-04-06 20:15:47 409

原创 PLSQL入门与精通（第80章：利用递归调用进行层级关系数据的更新）

上次，介绍了阶层查询SQL的使用，也介绍了使用递归调用实现递归查询的逻辑。这次我们介绍使用递归调用进行数据更新的例子。我们继续用职员表（EMP表）的例子来说明。比如有这样的一个例子：我们假设把一定数量钱（奖金？）交给某个员工（A）。A把这个钱的一半作为自己的奖励，加在自己的工资（SAL）里，剩下的钱均等地分给自己的下级。每个下级都会重复做类似以上的事情。直到员工再没有下级的时候，那个员工会把拿到的钱全部作为自己奖励加在自己的工资（SAL）里。处理就结束了以下是这个处理过程的代码例子。1

2021-04-05 21:08:46 331

原创 PLSQL入门与精通（第79章：递归调用与层级关系的例子）

上次，解说了一下递归调用的简单的例子。上次的例子函数只是单纯的返回计算结果的，这次递归调用例子改用数据库的表。这个例子是关于阶层查询相关内容。表中包含阶层（上下级）关系，我们通过递归调用把阶层关系显示出来。例如，员工表（EMP表）有三个字段：员工编号EMPNO，员工姓名ENAME，该员工所属的上级员工编号MGR。这个表中包含了上下级的关系,一个上级有多个下级。用以下的是阶层查询的SQL就可以吧他们的阶层关系查询出来：SELECT LEVEL , EMPNO, ENAME, MGRFROM E

2021-04-05 09:29:58 343

原创 PLSQL入门与精通（第78章：递归调用）

大家知道“递归调用”吗？所谓递归调用，是指在程序中“自己调用自己”。递归调用有很多语言支持，但并不是所有语言都可以使用。例如，COBOL语言不能实现递归调用。当然PL/SQL语言是可以的实现递归调用的。一般情况下，重复（循环）相同的处理内部要进行相同某个处理时，可以进行递归调用，例如，作为例子，一个函数：求1到N的和。通常在程序中定义一个变量，循环使用该变量进行加算，直到N为止。例子如下：CREATE OR REPLACE FUNCTION FUNC1 ( N IN NUMBER) RETU

2021-04-04 12:31:39 594

原创 PLSQL入门与精通（第77章：表行数与游标相结合的使用例子）

前几次我们针对表·函数进行了说明。表函数是像表一样可以查询的行的集合的返回函数。可以在SELECT语句的FROM语句后边，作为一个表进行查询。还有一个经常的用法就是，该表函数作为游标变量当做函数的一个参数进行使用。具体来说，将其他查询（SELECT语句）的结果用表函数的形式进行使用。在表函数中，通过游标变量参照其他查询的结果，根据该值进行某个程序处理，结果通过PIPE ROW处理从表函数中返回数据。例如，为了说明极端简单的例子，有以下的查询（SELECT*FROM TEST02）。SELEC

2021-04-04 12:01:30 175

原创 PLSQL入门与精通（第76章：表行数的异常处理NO_DATA_NEEDED）

上次介绍了“表·函数”，在使用表·函数时，有些注意事项要注意。那就是使用“WHERE ROWNUM<=n”想取前几条数据相关的注意事项。例如，请看以下我们用“WERE ROWNUM<=n”对表·函数进行查询。SELECT * FROM TABLE (pac1.f1(5)) WHERE ROWNUM <= 2;COL1 COL21 ABC12 ABC2上面的表函数pac1.f1（5）是处理5次循环,每次循环返回一条数据，总共返回五条数据。也就是说这个函数如果没有查询条件

2021-03-28 20:57:44 305 1

原创 PLSQL入门与精通（第75章：函数作为表来使用）

从这次开始就新的主题“表·函数”进行解说。大家有没有想过，如果在SELECT文的FROM后边跟的是表或者试图。如果这个表或者试图如果是函数，这个函数返回的是一个结果list，那我们是不是会很方便呢？如果这样可以的话，就不需要每个SQL两边写一堆返回大量数据结果的PLSQL块了，直接用放回数据结果的函数替代就方便了很多。或者是，程序中表和表结合的时候，其中部分表也可以用返回多条数据的函数来替代，那也很方便。或者是，游标访问你的表和返回结果集的函数结合起来，也很方便。也就是说，如果能把返回多条数据

2021-03-28 18:08:38 231

原创 PLSQL入门与精通（第74章：视图的更新-监听器）

上次介绍了通过视图更新数据的通常的方式和限制条件。一般来说，通过视图进行数据更新（INSERT、UPDATE、DELETE），只要该视图是单纯的SELECT语句定义的视图即可。也就是说，如果是不汇总或数据被加工过而是直接显示数据的视图，一般可以通过该视图来更新数据。在这种情况下，在数据库侧，将针对视图的DML语句（INSERT、UPDATE、DELETE）自动转换成表的DML语句，来更新数据。但是，对于定义的复杂视图，DML语句无法在数据库侧转换成表的DML语句，因此会发生错误。这种情况下如果在数

2021-03-28 16:40:04 407 1

原创 PLSQL入门与精通（第73章：视图的更新）

本次我们讲解通过视图来更新数据的相关内容。视图本来是一个查询询的。假如通过视图可以直接对查询对象的数据进行的话，那么查询的对象和更新的对象都在一个视图里边的话，构建应用程序就变得简单了。Oracle就可以实现这个我们想要的功能。通过视图更新数据，有的时候是可以更新的，有的时候不能更新数据。通过视图更新数据是有前提条件的。如果不符合这个条件，原则上无法通过视图更新数据。但是，即使在这种情况下，我们也可以通过“INSTEADOF触发”的触发器，通过视图进行数据更新，这个是我们下一节要说明的内容。这

2021-03-28 11:12:31 720 1

原创 PLSQL入门与精通（第72章：LOGOFF触发器）

上一次我们介绍了登录触发器，本次我们介绍注销触发器。我们这里的注销指的是退出回话或者退出回话的意思。注销触发器是用户退出回话或者数据库数据库时启动的触发器。他是会话结束前的最后一步处理。登录触发器和注销触发器都是属于同一类别的触发器，他们有以下2个最大的差别：1.登录触发器在SYS用户下是不能被启动的，注销触发器在SYS用户下是可以被启动的。2.在登录触发器中，如果发生例外，例外没有处理的话，则无法登录，但是在注销触发器中发生未处理的例外的话，用户照样能够退出回话。首先，关于1.的部分，登

2021-03-28 00:35:09 255 1

原创 PLSQL入门与精通（第71章：数据库登录触发器）

上一次我们介绍了DDL触发器。使用DDL触发器，不管操作什么样的对象类型（表，视图等），都可以在CREATE、ALTER、DROP的时机执行某些处理。例如，执行的操作可以是保留DDL操作的历史记录、限制特定对象的删除等。从这次开始，我们介绍一下另外类型的的触发器：登录（Login)触发器和注销触发器。我么先介绍一下登录触发器。正如名字所示，这是登录数据库时启动的触发器。触发器的一般结构已经解说完毕，下面就以例子，解说一下登录触发器特有的部分。SQL> show user用户是“SYST

2021-03-27 10:54:55 216

原创 PLSQL入门与精通（第70章：复合触发器的例子：操作其他表）

上次解说了DDL触发器。DDL触发器是通过DDL文（CREATE、ALTER、DROP）启动的触发器，在触发器中，可以使用事件属性函数（ora_xxxx）获取DDL的事件和DDL操作的对象的信息的详细内容。上次介绍的例子是：为了防止误删除SCOTT.EMP表DDL触发器的例子。如果存在该触发器，SCOTT.EMP表就不会被删除。这次介绍一下DDL触发器的另外一个例子。这次在DDL触发器中可以操作其他的一张表。具体来说，就是在日志表中插入 DDL操作的历史记录。例如，谁、什么时候、哪个用户、操

2021-03-26 21:46:21 135 1

原创 PLSQL入门与精通（第69章：DDL触发器）

前两次我们解说了“复合触发器”。总结一下，复合触发器简单来说是将4种DML触发器（BEFORE的SQL文、BEFORE的行、AFTER的行、AFTER的SQL文）整理一块的东西。这些都属于DML触发器,是最常见的触发器,是DML语句（INSERT、UPDATE、DELETE）执行的时候自动启动的PL/SQL程序。但是，除了DML触发器之外其他种类的触发器也有很多。例如，“DDL语句”启动的触发器、在“登录”或“注销”数据库时启动的触发器、在某个“错误发生的时候”启动的触发器、在“启动”数据库、“停

2021-03-26 11:20:18 524 1

原创 PLSQL入门与精通（第68章：复合触发器的例子）

上一次介绍了复合触发器的语法。今天介绍一下复合触发器的应用例子。首先，简单的复习一下上次的内容。如果不是复合触发器，而是使用普通的触发器定义，如果在触发器之间共享变量，则需要使用使用包变量。这种情况下，需要在包里边定义变量，变量的使用要在各个触发器内用各自的形式去使用用的，代码比较分散，系统变得非常难以理解。这时候，如果使用复合触发器的话，无论是变量定义还是多个触发器定义，代码都在一个复合触发器内部，非常容易理解。我们介绍复合触发器的例子，分为2部分。一部分是简单例子，第二部分是实践的例子。★

2021-03-25 23:48:34 235

原创 PLSQL入门与精通（第67章：复合触发器的语法）

从这次开始介绍新课题：“复合触发器”。之前我们介绍过触发器了。触发器是在发生某个事件时自动启动的PL/SQL程序。根据事件的种类和触发器的时机有好几种触发器。一般来说，对于数据库的表的操作一般是DML文（INSERT，UPDATE，DELETE），DML文执行的时候自动启动的触发器最具有代表性。表的DML触发器可以分为以下4种・BEFORE的SQL文触发器・BEFORE的行触发器・AFTER的行触发器・AFTER的SQL文触发器“BEFORE”、“AFTER”的意思是触发器启动时机是DM

2021-03-25 21:14:00 211

原创 PLSQL入门与精通（第66章：批量绑定异常处理）

本次介绍一下“批量绑定”发生异常的时候的处理。使用数组数据对DML进行FORALL统一处理时，数据内容可能会违反制约等而导致的执行错误。这种情况下，FORALL处理失败后会全部被回滚。但是，发生错误的时候提示了一个简单的错误信息，具体时间数组中的哪个数据发生了什么样的错误是无法判断的。但是，实际上我们调查异常的时候，需要知道是哪条数据出什么样的错误了，系统也提供了相应的方法。我们准备一个出错的FORALL的例子：TEST01表中设定一些限制。先删除TEST01表。SQL> DROP TA

2021-03-25 09:17:25 348

原创 PLSQL入门与精通（第65章：批量绑定FORALL 使用数组部分索引的用法）

上一次，我们介绍了配列的索引不连续的情况下FORALL语句的用法。但是，在通常情况下，配列的数据都是连续的，或者其中部分数据使我们想试用的数据，一部分是不想用的数据，这时候也可以通过FORALL语法进行批量绑定。方法是我们可以将需要的的配列的索引拿出来，放到另外一个配列中使用ForALL。例如，配列的索引为“1、2、3”时，我们只想用其中的“1、3”2个索引进行FORALL，我们就可以吧“1、3”的值保存在其他配列中，通过这个中间的配列的值，从而在FORALL中去使用配列原来的数据。我们把要处理

2021-03-25 07:50:39 204

原创 PLSQL入门与精通（第64章：批量绑定FORALL 不连续数组的用法）

我们继续解说批量绑定相关内容。本章针对是“FORALL语句中批量绑定变量的配列索引值是不连续做法”进行解释。在批量绑定中处理DML（INSERT、UPDAATE、DELETE）时，之前已经说明了FORALL的语法结构。但是之前介绍的FORALL句法只能使用配列的索引值是连续的情况。如果配列的索引不连续就会出错。FORALL语法中，配列不连续的时候语法结构如下：–配列索引是连续的时候—FORALL 索引变量 IN 下限值…上限值使用配列数组的一个DML语句；–配列索引是不连续的时候—FORAL

2021-03-24 22:09:25 203

原创 PLSQL入门与精通（第63章：批量绑定FETCH INTO COLLECT INTO LIMIT）

上一次，介绍了FETCH BULK COLLECCT INTO语句，可以用于显示游标的结果集所有数据一次性批量取得的处理中。也就是说，可以一次性操作，将结果集所有数据转换成配列数组。优点是：结果集不能随意利用某一行数据，但是配列可以自由访问任意节点的数据。本次接着上次继续解说LIMIT语句用法。LIMIT语句是FETCH BULK COLLECCT INTO语句的可选语句。没有LIMIT语句的时候，结果集全部数据一次性取得并保存到数组变量中。因此，结果集的数据非常多的时候，将大量的数据一次性放到

2021-03-24 18:28:21 1050

原创 PLSQL入门与精通（第62章：批量绑定FETCH INTO COLLECT INTO）

上一次，我们说明了一下用SELECT BULK COLLECCT INTO的语句进行批量绑定的方式进行取得数据。本章节说明一下显示游标的FETCH INTO文的批量绑定方式：FETCH BULK COLLEC INTO。使用FETCH BULK COLLECCT INTO语句的话，可以把FETCH到的所有行一起放到结果集中，也成为了批量绑定方式。因此，比一行一行地FETCH，性能要好得多。接下来，我们分别就普通FETCH INTO文的处理和FETCH BULK COLLECCT INTO文的处理进行

2021-03-24 17:18:10 767

原创 PLSQL入门与精通（第61章：批量绑定SELECT BULK COLLECT INTO）

上次我们验证了批量绑定的速度非常快。用循环处理的话，50万行INSERT需要15秒，用批量绑定的方式仅仅是0.6秒的，快了20多倍，效果惊人。上一章说的是DML文的INSERT语句，如果是SELECT INTO和FETCH INTO该怎么做呢。今天我们解释一下SELECT INTO的批量绑定的用法。（下次解说FETCH INTO的批量绑定用法）如前所述，SELECT INTO文，只能返回1行结果，想将多行保存成数组时，如果使用SELECT INTO语句，必须对每一行的SELECT INTO语句进

2021-03-24 14:18:33 919

原创 PLSQL入门与精通（第60章：批量绑定变量提高效率的测试效果）

PL/SQL是由PL/SQL引擎执行的，块内的SQL语句是由SQL引擎执行的，所以循环处理中反复执行SQL的话，引擎会发生多次切换。切换次数多的话开销就会很大。因此，按数值配列的方式向SQL引擎发送SQL语句，然后统一执行，引擎切换只需要发生一次，执行速度会大幅提高。让我们我么实际测试一下效果吧。接着上一章的例子，上次使用的TEST01表，我们删除后从头开始。SQL> DROP TABLE TEST01;表已删除。SQL> CREATE TABLE TEST01 ( A NUMBE

2021-03-23 20:37:58 165

原创 PLSQL入门与精通（第59章：批量绑定变量提高效率）

从本章开始一个新的主题“批量绑定”。通常在SQL语句中给PL/SQL变量赋值叫做绑定。一次绑定一个完整的集合称为批量绑定。在PL/SQL块中，使用数组值在循环处理中反复执行SQL语句时，如果改写成批量绑定的语法，性能会大幅度提高。要理解批量绑定，我们需要知道plsql的2个引擎：一个是PL/SQL引擎，一个是SQL引擎。PL/SQL引擎：无名块,存储程序等PL/SQL块先被送入到PL/SQL引擎，然后按顺序执行块内的处理。SQL引擎：如果PLSQL块内的处理遇到SQL文（INSERT、UP

2021-03-23 20:06:28 295

原创 PLSQL入门与精通（第58章：外键约束错误触发的After行触发器）

本次介绍外键约束错误的时候，触发器执行的情况。大家都知道，操作数据库的时候，会发生外键约束错误，如果我们定义了AFTER行触发器的话，改行触发器在发生外键约束错误也会被触发执行的。这个细节需要注意。让我们回忆一下，触发器触发触发的过程：在一个DML文（例UPDATE）中更新多行时首先在第一行上启动BEFORE的行触发，接着更新行数据，，如果该行更新成功，则在该行上启动AFTER的行触发器，如果该行行触发器执行成功，则再启动下一个行的更新处理。以上处理反复执行。如果上述处理过程中，不管是

2021-03-23 19:12:00 429

原创 PLSQL入门与精通（第56章：用PLSQL读写文件）

从本章开始，我们开始介绍用PLSQL怎么进行文件操作。文件操作主要用到UTL_FILE包本章简单介绍一下用UTL_FILE包进行文件的读写。UTL FILE包是Oracle数据库导入的一个标准的包。这种包装叫做内部包。通过使用内部包，实现了一些基本的PL/SQL不容易实现的一些基本功能。以下是用UTL FILE包写文件的简单例子：SQL> DECLARE2 FH UTL_FILE.FILE_TYPE;3 BEGIN4 FH := UTL_FILE.FOPEN(‘DATA_PUMP_DI

2021-03-22 18:59:44 728

原创 PLSQL入门与精通（第57章：自治事务结合触发器保留历史的操作记录）

上次介绍了“自治事务管理”的基本概念和例子：简单回顾一下吧。该功能可以从主事务中调用自主事务的子程序，该子程序内的处理的事务式独立进行的（COMMIT）。即使主事务被取消（ROLLBACK），自治性事务处理独自提交，不会因为主程序的取消而被取消。这个功能在需要保留所有记录，以备审计的时候很方便。我们用自治事务和数据库触发器结合来举例说明。一个测试用的表，在对该表进行UPDATE的时候，需要将“何时、谁、哪一列、做了怎样的操作”等详细情报，要记录到审计表里。让我们开始吧：首先，制作测试用表（T

2021-03-14 18:17:09 199

原创 PLSQL入门与精通（第56章：子程序里的自治事务处理）

这次介绍一下“自治事物”相关内容。所谓“自治事物”，就是自己的子程序内部自行管理事务，和调用方的事务无关。也就是在一系列长处理中一部分处理进行自己的独立的事务管理。即使主交易被回滚，子处理的部分是作为独立的处理进行管理的，与原来的处理是分开的。我们用简单的例子来演示一下吧。首先要准备一张名为“TEST01表”的简单的表。针对这个表进行4行INSERT处理，其中途中的2行用自治事务来进行插入。并且，进行ROLLBACK的话，4行中只有自治事务的那2行INSERT的有效，其他2行处理可以取消。S

2021-03-14 17:23:39 291

原创 PLSQL入门与精通（第55章：通过包和角色简化权限管理流程）

上次说了，执行者权限过程需要很细的权限管理，这样有对系统安全是有小的。但是也很复杂，本次在简化安全管理方面，进行详细说明。这个可以使用包的特性，简化管理。首先让我们看一下不使用PL/SQL包的例子：例如，如果有如下安全管理的要求：用户1（SELECT USER）：只拥有对SCOTT.EMP表的SELECT权限用户2（UPDATE USER）：拥有对SCOTT.EMP表的SELECT权限，也需要UPDATE权限。–例子开始–SQL> show userSYSTEM --需要管理员用户–

2021-03-14 16:36:47 274

原创 PLSQL入门与精通（第54章：执行者权限的细节问题）

这次，我们继续关注执行者权限过程的另一个细节，即“执行者的权限”所执行的内容，进行进一步解释。例如，如果在执行权限的过程中引用的表中明确指定了模式（用户），会是什么情况呢？具体来说，在程序内不是“EMP”，而是“SCOTT.EMP”的时候，会怎么样呢。这种情况下，即使存储过程是执行者权限，该过程制定了明确的用户的前提下，该表不是执行者的模式，而是SCOTT模式的EMP表。但是，与定义者权限不同，执行者需要对该表的特定的权限。因为执行者权限过程是由执行者的权限执行的。“EMP”表没有指定用户的话，没有

2021-03-14 15:34:58 156

原创 PLSQL入门与精通（第53章：执行者权限）

上次解说了定义者权限（默认状态）。总结一下：定义者权限的程序在定义者的权限和模式中执行的。上次的例子，用户ALLEN执行用户SCOTT的过程（PROC1）时，在那个过程中参照的EMP表不是作为执行者的ALLEN的EMP表，而是定义者的SCOTT的EMP表。如果想要让用户ALLEN执行户SCOTT的过程（PROC1），参照的是执行者ALLEN的EMP表的话，可以将那个程序设为“执行者权限”。以下就是队一行的方法：（决定执行者权限的方法）。首先，PROC1过程的定义者和上次一样都是SCOTT，所以在SC

2021-03-14 14:47:04 139

原创 PLSQL入门与精通（第52章：定义者权限）

今天介绍一下关于定义者权限和执行者权限的一些知识内容。PL/SQL的程序、函数、包有“定义者权限”或“执行者权限”两种权限的区别。默认为“定义权限”。“定义者权限”（默认）的定义的存储过程，不管是哪个用户去执行那个存储过程，都会在“定义者的权限和模式”中执行的（即与执行者无关）。例如，SCOTT数据库用户有一个PROC1程序程序，这个程序对EMP表和EMP表进行SELECT查询。下边是具体的例子内容，因为这个过程没有做任何特殊的设置，所以这个存储过程默认的是“定义者权限”。–例子开始–SQL&g

2021-03-14 12:35:06 187

原创 PLSQL入门与精通（第51章：用PLSQL定制小工具脚本）

我们在调查和开发中，如何挺高开发效率，是经常遇到的问题。本次我们关于使用plsql制作自己想要的工具，从而提高作业效率，举例说明。假如，写程序时，必须事先掌握表中的键（主键、外部键）等的情报。如果手头没有表定义的文档，或者没有什么方便的工具，需要自己查询数据，这很麻烦。例如，在USER_CONSTRAINTS这个系统提供的视图中，可以知道表名称和类型。通过USER_CONS_COLUMNS这个系统视图，可以知道列的名称和情报。假如有外键约束的话，还想知道知道外键所参照的父表的名字和父键，必须按照如

2021-03-14 12:04:06 457

空空如也

空空如也