PL/SQL开发
文章平均质量分 67
介绍基于Oracle11g及以上版本的PL/SQL开发,总结开发案例、分享学习心得!
观想
Oracle技术,PL/SQL开发,让认知变现!
展开
-
PL/SQL开发命名和编码规范(译)
翻译了一个文档,是PL/Solutions公司编制的一份PL/SQL程序编码规范,总共有20多页,很久以前看到这份文档觉得很有用,就想翻译过来,最近抽空重新学习了一遍,顺便翻译了一遍。文章不大好编排,我放到了下载资源中,默认收5个C币,很丑恶~_~这份文档虽然时间比较久远,但并没有过时。集合了多位PL/SQL大佬的智慧成果,推荐了一套简略的编码准则,总体思想是让代码逻辑更加清晰,更加具有可读性、...原创 2019-08-21 19:09:20 · 934 阅读 · 0 评论 -
用forall的save exceptions机制高效处理数据交换中的异常数据
在一个数据交换场景中,对方提供一个远程数据库,我方根据时间戳提取增量数据,经转换处理后存到我方数据库的表中。对方数据有以下特征: 1.增量数据数量较大; 2.数据不规范,部分数据无法直接写入我方数据库。考虑有以下几个方法来处理: 方案一、对每条数据loop循环处理,写入错误则记录后跳过; 方案二、在insert时对数据进行函数校验,符合条件的才写入;仔细...原创 2018-04-03 23:59:36 · 1259 阅读 · 0 评论 -
为什么CONNECT BY查询的结果总是不对
一个项目中涉及到工作流,其中一个流程是在内部人员流转,共3个环节,第一个环节是派发到项目负责人,第二个环节是项目负责人分派任务给其他人处理,第三个环节是处理人传阅给其他工作人员。 前台需要展示一个流程的流转过程,但是要求只能看自己处理环节以下的各个环节。设想的传递路径是这样的: --流程历史表CREATE TABLE t_flowtask(instid varchar2(5...原创 2018-04-03 17:23:32 · 1593 阅读 · 0 评论 -
ORACLE表名的大小写
我们都知道,Oracle数据库对表名、字段名的大小写是不敏感的。比如用以下语句(全部用小写字母)创建一个表,可以看到数据字典里存放的都是大写字母:然而,Oracle也是可以做到对表名、字段名或其他对象名称的大小写敏感,只要在操作的时候加上一对双引号""即可:甚至是可以重名:这种情况下就要特别小心,必须在任何操作中都加上"",否则就会出现ORA-00942:原创 2018-01-30 02:44:19 · 13187 阅读 · 0 评论 -
动态SQL拼装技巧
存储过程里有时候需要根据不同的条件拼装动态SQL,比如下图是一个简单的sql语句拼装函数:执行结果如下:PLSQL的字符串连接时,需要用2个单引号表示1个单引号,比如上例中的to_date函数里面用到的日期模式。实际应用中可能会拼装比这个复杂很多倍的动态SQL,特别是当引号比较多的时候,对于拼装出来的SQL是否有问题需要调试很多次才能拼正确。那么如何减少拼装的错误呢,这原创 2018-01-30 06:05:41 · 8294 阅读 · 0 评论 -
自治事务pragma autonomous_transaction的使用场景(1、程序日志)
PRAGMA在PLSQL里有4个关键词的用法:(1)autonomous_transaction-自治事务,该程序块的commit或rollback不影响外层事务;(2)exception_init--错误代码与declare的标识符关联;(3)restrict_references--程序包的纯度级别;(4)serially_reusable--程序包级别的数据在引用之间不保留。原创 2018-01-30 07:13:00 · 5211 阅读 · 0 评论 -
自治事务pragma autonomous_transaction的使用场景(2、触发器)
除了用于记录日志,自治事务pragma autonomous_transaction另一个常用的使用场景是在触发器里。下面是我在某个工作流系统中创建的触发器,目的是触发在申请人的流程被下一环节受理时、以及流程被退回时提取相关信息发送短信提醒。此时,我在前台进行操作后,收到一个错误:ORA-04091,变异表(mutating),因为在触发器里引用了被触发的表。解决方案就原创 2018-01-30 14:55:44 · 5554 阅读 · 0 评论 -
oracle自治事务(autonomous transaction)
(另外的一个账号进不去了,把文章搬过来一下) Thomas Kyte说过自治事务除了用作演示工具外,唯一的用途就是作为一种错误日志机制。使用场景是:想记录异常程序块中的一个错误,只需要将它写入一个表并commit,而不用commit其他任何内容。 接下来做2个试验可以说明自治事务的用途: 试验一、这是Tom在他编程艺术的书里用来演示在一个session有2个并原创 2018-01-30 15:02:10 · 10278 阅读 · 0 评论 -
PLSQL标签的3个使用场景
PLSQL标签是对程序中特定的部分代码进行命名的方法,格式是>;其中标签名的命名规则和其他PLSQL对象命令类似,最长30位。标签放在需要命名的代码前面(标签后面必须有代码,实在没有也要写一句NULL;)标签的作用有3个:1.提高代码可读性;比如可以对程序中的一段匿名块进行命名2.程序逻辑跳转;一种是goto跳转无论如何方便,还是建议尽量不要使用goto原创 2018-02-07 01:28:39 · 1430 阅读 · 0 评论 -
用Oracle PLSQL做彩票开奖数据爬虫(一、ACL配置)
oracle PLSQL也可以访问http,意味着可以用oracle来做网络爬虫,之前用python写过彩票开奖历史爬虫,这次也爬一下彩票数据。需要用到PLSQL中UTL_HTTP这个包,用这个包首先需要配置ACL(Access Control List,访问控制列表)。否则就会报错,比如SQL> select utl_http.request('http://www.500.com'原创 2018-02-01 21:41:38 · 939 阅读 · 0 评论 -
用Oracle PLSQL做彩票开奖数据爬虫(二、读取网页数据)
用Oracle PLSQL做彩票开奖数据爬虫(二、读取网页数据)通过配置ACL,我们已经可以获取到网页数据。 可是,我们发现utl_http.request函数返回的数据并不全,最多只能返回1999个字符。 幸运的是Oracle当然已经准备了一套完整的工具来保证满足我们的需求,主要是提供了几个函数,可以把网页数据存入lob类型的字段里。我这里写了一个存储过程,用来爬某期双色球的开奖结果原创 2018-02-01 22:53:55 · 1491 阅读 · 0 评论 -
VARCHAR2的最大长度是32767还是4000
VARCHAR2的最大长度是32767还是4000?截止目前的答案应该是这样的:1.Oracle12c之前的版本,表字段类型最大长度是4000个字节;(Oracle12c通过设置初始化参数MAX_SQL_STRING_SIZE为EXTENDED可以支持到32767个字节)2.PLSQL环境中VARCHAR2变量的最大长度是32767个字节;可以通过几个测试观察一下VARCHAR2变量类型的特性:1...原创 2018-02-07 00:50:33 · 9664 阅读 · 0 评论 -
用RAW类型解决不同字符集数据库中文乱码问题
在跟外单位做数据交换时,对方提供了一个默认字符集(WE8MSWIN1252)的数据库我这边是ZHS16GBK的数据库,我需要每天从对方库里抽取增量数据,跨字符集后查询是乱码:对方仅给我方开放了一个查询用户,可以查询一个视图。我方通过DBLINK方式获取对方数据,此时考虑了一下只能通过RAW类型中转的方式进行处理,要求对方修改视图,对中文字段转换成raw类型。现在我方原创 2018-02-07 14:24:05 · 3028 阅读 · 0 评论 -
短路估算
短路估算很简单的一个逻辑,但是经常会被忽略,对于开发人员来说就像是养成的一个节俭的生活习惯,一点一滴的节省社会资源。短路估算主要用于多条件判断语句,比如IF,CASE WHEN,比如:IF (条件A) AND (条件B) THEN...ELSE....END IF;或者CASE WHEN (条件A) THEN ...WHEN (条件B) THEN ...END原创 2018-02-07 01:44:31 · 234 阅读 · 0 评论 -
PLSQL中的重载
跟其他高级语言一样,PLSQL也支持重载,包括存储过程和函数都可以重载,实际上PLSQL自身的很多函数或过程也都是具有重载的。比如dbms_output.put_line过程可以打印数字、字符、日期等各种类型;instr函数对VARCHAR2、Clob类型都支持等等。下面举个简单的存储过程重载的例子:在一个包的声明中,定义了print函数,可以打印布尔型、数字型、字符型和日期型变量,其中字符型是2...原创 2018-02-08 21:08:47 · 983 阅读 · 0 评论 -
Oracle PLSQL包和局部模块的区别使用
为了减少重复代码量,需要将存储过程中可以模块化的部分代码封装成一个新的存储过程或函数。刚开始写plsql程序时,每逢遇到这种场景我就会把原来的存储过程改写成一个包(PACKAGE),问题就迎刃而解了。后来得知,实际上不是必须使用包才能解决问题,在原存储过程中把需要封装的代码定义成局部模块(Local or Nested Module),效果是一样的。显然这2种方式都可以剥离重复代码减少代码量,提高...原创 2018-02-10 09:18:26 · 567 阅读 · 0 评论 -
确定性函数(Deterministic)
确定性函数用关键词Deterministic标识,表示函数的返回值完全由输入参数决定。确定性函数有以下用处:1.可以在基于函数的索引中使用该函数;2.可以在物化视图中调用;3.Oracle11g开始会对其参数及其返回结果进行缓存处理以提升性能。但是,是不是一个确定性函数是需要用户来负责的,就是说对函数进行编译的时候不会检查出这个函数是否是确定性的。来看个例子:这个函数返回当前输入时间+1天,只要输...原创 2018-02-10 14:20:57 · 27444 阅读 · 2 评论 -
前置声明解决相互递归时的PLS-00313
碰到了一个互相递归的情况,在一个存储过程中,函数A需要调用B,同时函数B也需要调用A,此时发生了PLS-00313: XXXX not declared in this scope的错误。原因很直接,在编译函数F_A时,发现F_B函数没有声明过,报错!此时陷入了一个死循环,先放F_B函数,也会因为F_A函数未声明而报错,幸好PLSQL已经考虑到了这种偶发情况,解决的方法是“前置声明”。即在F_A前...原创 2018-02-11 23:33:11 · 1437 阅读 · 1 评论 -
PLSQL自定义EXCEPTION异常
PLSQL开发经常需要用到异常处理(exception)来做一些逻辑处理或者规避一些异常情况,PLSQL中专门有一个异常处理模块的语法来处理这个事情。异常可以分成几类:1.可以预期的数据错误 开发人员已经预料到自己的程序中可能发生的错误,比如主键重复、除数为0等,此时用异常处理来捕捉这些错误,并进行相应的处理,规避或者纠正错误。2.不可预期的错误开发人员并没有预料到的一些错误,比如应用程序端做了...原创 2018-02-12 19:12:48 · 5350 阅读 · 0 评论 -
PLS_INTEGER类型
PLS_INTEGER是PLSQL环境中的一个特殊的数字类型,这种类型的值是通过硬件平台原生整数格式来表示的。它存在的意义完全是为了加速运算,具有以下特点1.是一个整数类型2.支持范围-2147483648到2147483647(正负2的31次方)3.未声明的loop循环索引默认是用pls_integer类型的2147483647没有问题,但2147483648就溢出了,从下面图中可以看出,在lo...原创 2018-02-13 10:26:40 · 5471 阅读 · 0 评论 -
用SQL%ROWCOUNT返回SQL语句的影响数量
早前在做一个短信接口时,客户端通过webservice接口发送短信请求,我用一个PLSQL存储过程接收后进行一些处理,然后返回一个处理结果,其中有一段是这样的:大致意思是把短信申请中号码有效的部分进行发送,然后返回总共成功了多少条。当查询的基表数据量比较大,或者SQL的处理逻辑本身比较慢的时候,这种做法效率就显得太低了。比如另外一个更常见的场景是根据输入文本模糊查找企业清单,同时返回结果数:一样的...原创 2018-02-13 13:38:28 · 5474 阅读 · 0 评论 -
PLSQL中的隐含声明
PLSQL总体来说是一种比较严谨的语言,但是相对C、JAVA之类传统语言又显得随意多了,隐含声明(implicitly declare)就是一个体现。这种隐含声明出现在for循环中,作为for循环的循环索引变量,无需事先声明其数据类型就可以直接使用。比如:1.数值型循环索引,比如这个for语句中的idx是个PLS_INTEGER类型的本地变量2.记录类型循环索引,比如下面的rec是%ROWTYPE...原创 2018-02-17 09:24:31 · 194 阅读 · 0 评论 -
where current of语句
PLSQL为循环游标提供了where current of语句的功能,用来避免二次定位游标记录,以提高程序效率。在此之前,我们已经在做类似的事情,使用的方法是用rowid来定位,因此看到有where current of语法的时候,就想到一个问题:WHERE CURRENT OF与ROWID定位的性能哪个高?做个实验看看。100万+的数据,分别用where current of和rowid做upd...原创 2018-02-18 17:42:43 · 11938 阅读 · 4 评论 -
ORACLE中的自定义聚合函数(User-Defined Aggregate Functions)
聚合函数也叫分组函数,是对多行进行计算的一种函数,比如SUM、COUNT、AVG、MAX、MIN等等。Oracle中为了增强扩展性,提供了一个自定义聚合函数的接口,ODCIAggregate(),用户可以用Oracle支持的各类编程语言(PLSQL、C、C++、JAVA等)定义一个聚合函数,并在程序中按照一样的语法进行使用。实际应用中,使用到自定义聚合函数的场景极少,理由是:1.PLSQL语言已经...原创 2018-02-20 21:47:25 · 2147 阅读 · 0 评论 -
自治事务pragma autonomous_transaction的使用场景(3、解决ORA-14551错误)
有时候,我们想在一个函数中做一些复杂的处理,比如更新一个临时表数据,然后做一些UPDATE处理,最后返回一个处理结果。想必大部分开发人员在初期都试过这样的操作,最终的结果是啥?PLSQL函数中不能做DML操作,报错ORA-14551: cannot perform a DML operation inside a query此后,我们可能转而用带out参数的存储过程来间接实现业务需求。其实,另一个...原创 2018-02-20 22:28:36 · 812 阅读 · 0 评论 -
Oracle12C中With FUNCTION新特性
通过with子句,我们可以把很多原本需要存储过程来实现的复杂逻辑用一句SQL来进行表达,在12C中,with的功能又有所增强,在with里可以直接定义一个函数。比如甚至可以支持自治事务函数,在函数中进行DML操作而在11G中,则直接报错ORA-00905需要注意的是:在function定义的end后面不能跟函数名称这种语法在查询DG备库查询中可以派上用场,在一个STANDBY备库中,我们不能在只读...原创 2018-02-23 17:30:27 · 1974 阅读 · 0 评论 -
用PACKAGE封装数据的一个简单演示
读一些接口代码,或者是github上下载一些成熟的PLSQL包,经常会发现对底层数据多少都做了一些封装。数据封装有几个好处:1.代码可读性提高了;2.数据结构发生变化时,代码更新工作可以最小化且集中化处理;3.隐藏了底层数据结构,对商业保密起到一定作用。下面以一个案例演示一下数据封装的过程:在common用户下,我有一个T_LOG表用来记录程序运行日志,其结构如下:为了将写日志的接口开放给其他用户...原创 2018-02-23 22:32:39 · 406 阅读 · 0 评论 -
触发器(一、语句级和行级的区别及使用场景)
Oracle触发器分为语句级(STATEMENT)和行级(ROW)两种,从字面意思上理解,语句级触发器是指一条SQL语句触发一次;行级触发器是指一条SQL语句影响的每一行触发一次。看个案例就很明显了先建2张测试表from_table和to_table,from_table插入5条记录接着在to_table上建2个触发器,分别是语句级和行级,其中行级触发器有for each row的声明下面执行一条...原创 2018-02-24 09:57:14 · 9935 阅读 · 1 评论 -
触发器(二、BEFORE和AFTER的区别及使用场景)
DML操作的行级触发器,分为before和after两类。比如下面在同一张表上分别建了2个触发器然后用一个insert语句进行触发结果发现,这2类触发器捕捉的:OLD和:NEW数据实际是一样的。那么什么时候用before,什么时候用after呢?我们先要知道这2种触发器的特性区别:before触发器(针对insert和update操作)可以修改NEW的值那么before的应用场景就是:需要修改NE...原创 2018-02-24 10:35:09 · 11930 阅读 · 1 评论 -
触发器(三、条件触发)
在使用触发器的过程中,我们经常发现真正关注的只有一部分数据,并不需要对每个操作都进行触发或处理。此时可以用到条件触发,条件触发是有限制的,根据不同的需求,可以分为以下几个应用场景:1.只关注某几列的数据变更,用update of只有在update后面才能跟of,在insert和delete中无法只关注某几列,会报错ORA-04073: column list not valid for this ...原创 2018-02-24 12:46:41 · 11453 阅读 · 0 评论 -
触发器(四、执行顺序控制)
Oracle11g开始提供了一个触发器顺序控制的功能,对于同时触发的多个触发器,可以用FOLLOWS语句来控制先后执行顺序。但是FOLLOWS语法有一定的限制:只能用在同类型的多个触发器上(before、after要一样;update、insert、delete要一样;行级、语句级要一样)否则会报错ORA-25022: cannot reference a trigger of a differe...原创 2018-02-24 16:23:00 · 6329 阅读 · 1 评论 -
触发器(五、复合触发器)
Oracle11g开始提供了一种复合触发器,简单的说就是支持把语句级和行级触发器组合在一起。实际应用场景不多,这里就做个记录。与单个触发器语法有所区别:1.单个触发器的头部是 BEFORE(AFTER) 【动作】 ON 【对象】 FOR 【触发级别】而组合触发器的头部是 FOR 【动作】 ON 【对象】 COMPOUND TRIGGER2.每个触发条件都各自写一段代码块,有自己的begin和end...原创 2018-02-24 16:53:24 · 604 阅读 · 0 评论 -
触发器(六、instead of触发器实现视图增删改)
场景描述开发中遇到一个情况:前台页面是一个角色对用户的批量授权,其中用户是多选,多选的结果是控件里会填入逗号分隔的用户ID字符串,比如“TOM,JERRY”,最终要分别插入2张一对多的主从表中。--主表CREATE TABLE T_MAIN(GUID VARCHAR2(32) primary key,GRANTOR VARCHAR2(30),ROLEID VARCHA...原创 2018-03-04 10:15:22 · 7149 阅读 · 0 评论 -
触发器(七、DDL触发器在OGG维护中的应用)
场景描述有一个业务系统,做了OGG备库用于统计查询。 生产库的维护由系统管理员负责,而备库的日常维护由专职DBA负责。 系统厂商在对生产环境进行升级时,经常会修改数据结构。 DBA很快发现了OGG报错,查看日志是数据结构变更,于是向生产库系统管理员要数据变更清单。 生产库管理员再向系统厂商要,有时候系统厂商没有事先准备脚本,升级时在线的部分修改没有记录,导致DBA不停的跟踪错误信息,两...原创 2018-03-10 14:54:03 · 966 阅读 · 0 评论 -
触发器(八、用SERVERERROR触发器开展数据库错误维护工作)
SERVERERROR 触发器是一种数据库(database)级别的触发器,在Oracle错误发生后触发,但是不包含以下ORA错误: ORA-00600 Oracle internal error ORA-01034 Oracle not available ORA-01422 Exact fetch returns more than requested number o...原创 2018-03-12 17:22:19 · 947 阅读 · 0 评论 -
总结一些PLSQL开发的编程习惯
太多了,想到的一些记录以下,提醒自己。欢迎补充:1、多用包(PACKAGE)引用TomKyte的话就是“什么时候用包?任何时候!”他的理由是实际业务中很少有一个存储过程或函数能独立完成一个业务场景,必定是一堆有逻辑关联的存储过程、函数、类型等才能完整的实现一个业务。2、多用变量绑定这个是共识,好处很多,提升性能(软解析)、防止SQL注入等等。3、多用锚定变量声明(如...原创 2018-03-04 10:31:38 · 732 阅读 · 0 评论 -
用wrap加密自己的源代码
Oracle提供了一种简易的源代码保护方式,WRAP,它能把程序的代码变成一堆看不懂的字符。Oracle很多内置程序都使用WRAP进行加密比如下面是SYS用户下的一个函数:create or replace function CUBE_TABLE wrappeda0000001abcdabcdabcdabcdabcdabcdabcdabcdabcdabc...原创 2018-03-12 21:01:56 · 994 阅读 · 0 评论 -
PLSQL中的上下文切换(context switch)
PLSQL程序中的上下文切换(context switch)是指SQL和PLSQL编译环境之间的切换,大量的上下文切换会导致程序运行效率低下。PLSQL是一种过程性语言,而SQL不是过程性语言。PLSQL代码会被编译成机器语言,在运行时虚拟机会把这些处理解释成过程性语言。SQL和PLSQL两个环境是相对独立的,可以单独运行PLSQL代码,也可以单独运行SQL代码。但是SQL调用PL...原创 2018-03-12 21:48:38 · 767 阅读 · 0 评论 -
Oracle分析函数(一、用row_number做后台分页)
提到Oracle的分析函数,最常用的就是row_number、rank、dense_rank这3个函数,具体的用法有无数的文章写过,就不重复了,这里只描述一个row_number的使用场景。大家都知道,分页是个比较复杂的问题,如果数据量比较大,前台一次性缓存的性能就很差,一般都会考虑后台分页取数,这时就需要数据库做分页支持。然后oracle数据库并没有MYSQL里面的limit语法,所以分页一原创 2018-02-04 18:34:28 · 1556 阅读 · 0 评论 -
Oracle分析函数三、基于model函数建设口径统一的统计指标库
口径困境做数据分析的同学经常被一个问题困扰,就是口径统一问题。核对数据、反复取数是很多IT部门永远的主题,造成这一困境有很多原因: 不同的技术人员对同一口径的理解不一样,导致取出数据不一致; 2.技术人员脚本编写随意,导致在不同时间对同一口径取出的数据不一致; 3.业务和技术理解不一致导致取出的数据不符合要求。 如何减少这类无效劳动,从个人的经验看,可以从两...原创 2018-03-22 14:02:44 · 1228 阅读 · 0 评论