PLSQL开发
文章平均质量分 67
观想
Oracle技术,PL/SQL开发,让认知变现!
展开
-
PL/SQL开发命名和编码规范(译)
翻译了一个文档,是PL/Solutions公司编制的一份PL/SQL程序编码规范,总共有20多页,很久以前看到这份文档觉得很有用,就想翻译过来,最近抽空重新学习了一遍,顺便翻译了一遍。文章不大好编排,我放到了下载资源中,默认收5个C币,很丑恶~_~这份文档虽然时间比较久远,但并没有过时。集合了多位PL/SQL大佬的智慧成果,推荐了一套简略的编码准则,总体思想是让代码逻辑更加清晰,更加具有可读性、...原创 2019-08-21 19:09:20 · 926 阅读 · 0 评论 -
用wrap加密自己的源代码
Oracle提供了一种简易的源代码保护方式,WRAP,它能把程序的代码变成一堆看不懂的字符。Oracle很多内置程序都使用WRAP进行加密比如下面是SYS用户下的一个函数:create or replace function CUBE_TABLE wrappeda0000001abcdabcdabcdabcdabcdabcdabcdabcdabcdabc...原创 2018-03-12 21:01:56 · 987 阅读 · 0 评论 -
Oracle分析函数三、基于model函数建设口径统一的统计指标库
口径困境做数据分析的同学经常被一个问题困扰,就是口径统一问题。核对数据、反复取数是很多IT部门永远的主题,造成这一困境有很多原因: 不同的技术人员对同一口径的理解不一样,导致取出数据不一致; 2.技术人员脚本编写随意,导致在不同时间对同一口径取出的数据不一致; 3.业务和技术理解不一致导致取出的数据不符合要求。 如何减少这类无效劳动,从个人的经验看,可以从两...原创 2018-03-22 14:02:44 · 1222 阅读 · 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 · 945 阅读 · 0 评论 -
总结一些PLSQL开发的编程习惯
太多了,想到的一些记录以下,提醒自己。欢迎补充:1、多用包(PACKAGE)引用TomKyte的话就是“什么时候用包?任何时候!”他的理由是实际业务中很少有一个存储过程或函数能独立完成一个业务场景,必定是一堆有逻辑关联的存储过程、函数、类型等才能完整的实现一个业务。2、多用变量绑定这个是共识,好处很多,提升性能(软解析)、防止SQL注入等等。3、多用锚定变量声明(如...原创 2018-03-04 10:31:38 · 727 阅读 · 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 · 7144 阅读 · 0 评论 -
触发器(五、复合触发器)
Oracle11g开始提供了一种复合触发器,简单的说就是支持把语句级和行级触发器组合在一起。实际应用场景不多,这里就做个记录。与单个触发器语法有所区别:1.单个触发器的头部是 BEFORE(AFTER) 【动作】 ON 【对象】 FOR 【触发级别】而组合触发器的头部是 FOR 【动作】 ON 【对象】 COMPOUND TRIGGER2.每个触发条件都各自写一段代码块,有自己的begin和end...原创 2018-02-24 16:53:24 · 599 阅读 · 0 评论 -
触发器(四、执行顺序控制)
Oracle11g开始提供了一个触发器顺序控制的功能,对于同时触发的多个触发器,可以用FOLLOWS语句来控制先后执行顺序。但是FOLLOWS语法有一定的限制:只能用在同类型的多个触发器上(before、after要一样;update、insert、delete要一样;行级、语句级要一样)否则会报错ORA-25022: cannot reference a trigger of a differe...原创 2018-02-24 16:23:00 · 6310 阅读 · 1 评论 -
触发器(三、条件触发)
在使用触发器的过程中,我们经常发现真正关注的只有一部分数据,并不需要对每个操作都进行触发或处理。此时可以用到条件触发,条件触发是有限制的,根据不同的需求,可以分为以下几个应用场景:1.只关注某几列的数据变更,用update of只有在update后面才能跟of,在insert和delete中无法只关注某几列,会报错ORA-04073: column list not valid for this ...原创 2018-02-24 12:46:41 · 11440 阅读 · 0 评论 -
触发器(二、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 · 11894 阅读 · 1 评论 -
触发器(一、语句级和行级的区别及使用场景)
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 · 9918 阅读 · 1 评论 -
触发器(七、DDL触发器在OGG维护中的应用)
场景描述有一个业务系统,做了OGG备库用于统计查询。 生产库的维护由系统管理员负责,而备库的日常维护由专职DBA负责。 系统厂商在对生产环境进行升级时,经常会修改数据结构。 DBA很快发现了OGG报错,查看日志是数据结构变更,于是向生产库系统管理员要数据变更清单。 生产库管理员再向系统厂商要,有时候系统厂商没有事先准备脚本,升级时在线的部分修改没有记录,导致DBA不停的跟踪错误信息,两...原创 2018-03-10 14:54:03 · 960 阅读 · 0 评论 -
用PACKAGE封装数据的一个简单演示
读一些接口代码,或者是github上下载一些成熟的PLSQL包,经常会发现对底层数据多少都做了一些封装。数据封装有几个好处:1.代码可读性提高了;2.数据结构发生变化时,代码更新工作可以最小化且集中化处理;3.隐藏了底层数据结构,对商业保密起到一定作用。下面以一个案例演示一下数据封装的过程:在common用户下,我有一个T_LOG表用来记录程序运行日志,其结构如下:为了将写日志的接口开放给其他用户...原创 2018-02-23 22:32:39 · 402 阅读 · 0 评论 -
Oracle12C中With FUNCTION新特性
通过with子句,我们可以把很多原本需要存储过程来实现的复杂逻辑用一句SQL来进行表达,在12C中,with的功能又有所增强,在with里可以直接定义一个函数。比如甚至可以支持自治事务函数,在函数中进行DML操作而在11G中,则直接报错ORA-00905需要注意的是:在function定义的end后面不能跟函数名称这种语法在查询DG备库查询中可以派上用场,在一个STANDBY备库中,我们不能在只读...原创 2018-02-23 17:30:27 · 1965 阅读 · 0 评论 -
细粒度依赖(三)数据库对象失效的处理
由于表结构变更导致的数据库对象失效,需要如何处理。这在Oracle官方文档中有一个专题,叫做Managing Object Dependencies(管理对象依赖关系)。实质上无非是将失效的对象进行重编译,而重编译的方法大概分为3种:1.执行时自动编译失效的对象在下次执行时会自动进行编译:2.手工编译可以用ALTER ...COMPILE...的语句重编译也可以生成重编译的批量脚本后一起执行:SE...原创 2018-02-23 14:03:40 · 424 阅读 · 0 评论 -
PLSQL中的上下文切换(context switch)
PLSQL程序中的上下文切换(context switch)是指SQL和PLSQL编译环境之间的切换,大量的上下文切换会导致程序运行效率低下。PLSQL是一种过程性语言,而SQL不是过程性语言。PLSQL代码会被编译成机器语言,在运行时虚拟机会把这些处理解释成过程性语言。SQL和PLSQL两个环境是相对独立的,可以单独运行PLSQL代码,也可以单独运行SQL代码。但是SQL调用PL...原创 2018-03-12 21:48:38 · 760 阅读 · 0 评论 -
Oracle分析函数model使用时需要注意的几个地方(一)
分析函数中的model函数功能强大,对于做数据分析的人来说绝对是一个利器。 我曾经用model函数改写了数十个用存储过程实现的报表取数,一个SQL语句就完全替代了一个上百行代码的存储过程,而且报表口径也完成了统一,这一点在我另外一篇文章中做了详细说明: 基于model函数建设口径统一的统计指标库虽然model函数很强大,但刚接触的时候还是不免有很多需要留意的地方,一不小心就会发现统计结果与...原创 2018-03-23 12:52:26 · 2572 阅读 · 0 评论 -
在windows环境中安装oracle样例schemas
在windows环境中安装oracle样例schemas本机环境安装过程Oracle安装时会自带一些样例数据库用户,比如hr、pm、sh等,主要为初学者提供一些测试数据。在业务生产数据库安装时,一般会刻意取消这些安装。最近自己做一些测试时,发现没有合适的样例,希望把这些测试schema重新安装回来。本机环境手上的数据库是Oracle11.2.4 win64bit操作系统是Win10专业版...原创 2018-12-04 09:31:07 · 771 阅读 · 1 评论 -
通过角色授权的对象在存储过程中无法使用
在编译一个存储过程时,发生了ORA-00942: table or view does not exist错误,但是这个表是可以查询到数据的。--system用户下建了rolecommon角色,并授权给common用户SQL> show userUser is "system"SQL> create role rolecommon;Role createdSQL>...原创 2018-04-04 17:17:43 · 454 阅读 · 0 评论 -
用forall的save exceptions机制高效处理数据交换中的异常数据
在一个数据交换场景中,对方提供一个远程数据库,我方根据时间戳提取增量数据,经转换处理后存到我方数据库的表中。对方数据有以下特征: 1.增量数据数量较大; 2.数据不规范,部分数据无法直接写入我方数据库。考虑有以下几个方法来处理: 方案一、对每条数据loop循环处理,写入错误则记录后跳过; 方案二、在insert时对数据进行函数校验,符合条件的才写入;仔细...原创 2018-04-03 23:59:36 · 1256 阅读 · 0 评论 -
为什么CONNECT BY查询的结果总是不对
一个项目中涉及到工作流,其中一个流程是在内部人员流转,共3个环节,第一个环节是派发到项目负责人,第二个环节是项目负责人分派任务给其他人处理,第三个环节是处理人传阅给其他工作人员。 前台需要展示一个流程的流转过程,但是要求只能看自己处理环节以下的各个环节。设想的传递路径是这样的: --流程历史表CREATE TABLE t_flowtask(instid varchar2(5...原创 2018-04-03 17:23:32 · 1584 阅读 · 0 评论 -
行列转换之字符串拆分(CLOB版)
上文写到字符串拆分的各种方法 行列转换之字符串拆分(解决方案合集) 性能测试得到了如下结论: 自定义函数是最快的,0.039s,数据量大是加并发会更有优势; 简单SUBSTR方法,次之,0.05s 正则表达式REGEXP_SUBSTR方法,较慢,4.22s XML转换方法,报错,长度不足。 由于字符串长度有限,本文将函数改成CLOB类型,再次进行测试。...原创 2018-03-28 12:57:17 · 1897 阅读 · 1 评论 -
TO_CHAR函数的几个细节
日期和字符串相互转化在PLSQL中经常用到,尤其是TO_CHAR,常用于前台显示日期,Oracle提供的函数功能很强大,但如果我们不了解,可能会使用一些复杂的方法去实现一个很小的功能。我看到过一些开发人员为了获取”2018年3月5日”这样的格式写了以下的实现:select to_char(sysdate,'yyyy')||'年'||ltrim(to_char(sysdate,'mm'),'...原创 2018-04-10 14:39:20 · 13208 阅读 · 2 评论 -
Oracle正则表达式操作样例
数据处理过程中,对于质量不高的输入数据,经常要做一些清洗操作。如果不懂正则表达式,那是非常痛苦的事情。正则表达式还是比较繁杂的,很容易被它的语法绕晕,想要精通更不容易,一般都是用到了去查,去试。 这里有一篇开发人员整理的常用清单,可以参考一下: 最全的常用正则表达式大全——包括校验数字、字符、一些特殊的需求等等在Oracle中,提供了REGEXP_开头的正则表达式函数,包括REGEX...原创 2018-04-10 14:19:11 · 841 阅读 · 0 评论 -
ORACLE行列转换之字符串拆分
ORACLE中将带分隔符的字符串拆分成多行,有很多方法,我将多种常见和不常见的拆分方法进行了收集整理。 通常这个操作被归类为行列转换的范畴。 为了方便测试,我将每一种方法封装成一个函数,返回一个字符串集合。0.创建自定义集合类型SQL> create type t_vchars as table of varchar2(4000); 2 /Type created...原创 2018-03-27 17:32:52 · 2659 阅读 · 0 评论 -
PACKAGE静态缓存数据带来的逻辑隐患
在一个项目中,有一张参数表保存了一些全局变量,比如当前会计周期等。 通过一个包(PACKAGE)来实现业务逻辑,在PACKAGE的初始化程序里,读取相关的全局参数,用于后续处理程序。 但是在调试过程中,发现对参数进行修改后,包的全局变量跟实际库里的值不一致,导致业务逻辑错误。这里重现一下当时的场景:--参数表结构CREATE TABLE T_PARA(PARA_CODE VARCHA...原创 2018-03-27 11:18:46 · 243 阅读 · 0 评论 -
TRIM的用法
TRIM函数用来对字符串进行剪裁操作,包括TRIM,LTRIM,RTRIM三个函数。虽然平常用的比较多,但还是有些用法没有细究过。 1.TRIM可以带语义型参数TRIM(str1),表示去掉两边空格 TRIM(leading str2 from str1),去掉str1左边的str2,相当于LTRIM(str1,str2) TRIM(trailing str2 from str1...原创 2018-04-08 12:45:00 · 5029 阅读 · 0 评论 -
行列转换之字符串拼接(三)、自定义聚合函数
字符串拼接(String Aggregation Techniques)是数据处理时经常需要用到一个技术,比如需要按时间顺序拼装一个快递的运输记录,或者将流程中各个环节的处理人拼装为一个字符串。Oracle中有多种方法来实现这个功能,这里罗列几种,详细用法可以参考下面的文章: WM_CONCAT函数LISTAGG函数自定义聚合函数本文介绍第三种:自定义聚合函数。由于WM_CON...原创 2018-03-25 09:41:57 · 2951 阅读 · 2 评论 -
行列转换之字符串拼接(二)、LISTAGG函数
字符串拼接(String Aggregation Techniques)是数据处理时经常需要用到一个技术,比如需要按时间顺序拼装一个快递的运输记录,或者将流程中各个环节的处理人拼装为一个字符串。Oracle中有多种方法来实现这个功能,这里罗列几种,详细用法可以参考下面的文章: - WM_CONCAT函数 - LISTAGG函数 - 自定义聚合函数本文介绍第二种:LISTA...原创 2018-03-23 22:27:48 · 6933 阅读 · 0 评论 -
行列转换之字符串拼接(一)、WM_CONCAT函数
字符串拼接和分离(String Aggregation Techniques)是数据处理时经常需要用到一个技术,比如需要按时间顺序拼装一个快递的运输记录,或者将流程中各个环节的处理人拼装为一个字符串。Oracle中有多种方法来实现这个功能,这里罗列几种,详细用法可以参考下面的文章: - WM_CONCAT函数 - LISTAGG函数 - 自定义聚合函数0.测试样例这里介...原创 2018-03-23 21:55:36 · 49072 阅读 · 1 评论 -
Oracle分析函数model使用时需要注意的几个地方(二)
接着上文 Oracle分析函数model使用时需要注意的几个地方(一)分析函数中的model函数功能强大,对于做数据分析的人来说绝对是一个利器。 我曾经用model函数改写了数十个用存储过程实现的报表取数,一个SQL语句就完全替代了一个上百行代码的存储过程,而且报表口径也完成了统一,这一点在我另外一篇文章中做了详细说明: 基于model函数建设口径统一的统计指标库虽然model函数很...原创 2018-03-23 13:28:27 · 1147 阅读 · 0 评论 -
细粒度依赖(二)依赖关系导致的对象失效
上次说到可以通过数据字典找到Oracle数据对象的依赖关系,从而做好DDL操作的关联操作,以避免数据库对象失效。在Oracle10g以及更早的版本中,依赖关系是以程序单元的粒度进行跟踪的,比如一张表的一个字段被修改,即使程序中没有用到这个字段,也会失效。从Oracle11g开始,细粒度依赖已经优化到单元中的元素,比如上述的情况中,程序只在引用字段被修改时才会失效。比如我有一张表T_CUR2,有ID...原创 2018-02-23 13:15:21 · 292 阅读 · 1 评论 -
细粒度依赖(一)通过数据字典找到数据库对象之间的引用关系
开发过程中,我们经常碰到这样的问题,一个好好的程序某一天忽然报错了。重新编译,发现程序里引用的某个对象不存在或表结构被改了,往往是在团队合作时,由于业务需要某个成员修改了结构,但是不清楚哪些地方引用了这个对象,因此没有处理好善后工作,最后搞得焦头烂额。事实上,ORACLE提供了细粒度依赖的数据字典*_dependencies,可以找出引用关系来解决这个麻烦。先看下数据字典的结构:从表结构可以看出,...原创 2018-02-23 08:49:46 · 811 阅读 · 0 评论 -
oracle自治事务(autonomous transaction)
(另外的一个账号进不去了,把文章搬过来一下) Thomas Kyte说过自治事务除了用作演示工具外,唯一的用途就是作为一种错误日志机制。使用场景是:想记录异常程序块中的一个错误,只需要将它写入一个表并commit,而不用commit其他任何内容。 接下来做2个试验可以说明自治事务的用途: 试验一、这是Tom在他编程艺术的书里用来演示在一个session有2个并原创 2018-01-30 15:02:10 · 10272 阅读 · 0 评论 -
自治事务pragma autonomous_transaction的使用场景(2、触发器)
除了用于记录日志,自治事务pragma autonomous_transaction另一个常用的使用场景是在触发器里。下面是我在某个工作流系统中创建的触发器,目的是触发在申请人的流程被下一环节受理时、以及流程被退回时提取相关信息发送短信提醒。此时,我在前台进行操作后,收到一个错误:ORA-04091,变异表(mutating),因为在触发器里引用了被触发的表。解决方案就原创 2018-01-30 14:55:44 · 5551 阅读 · 0 评论 -
确定性函数(Deterministic)
确定性函数用关键词Deterministic标识,表示函数的返回值完全由输入参数决定。确定性函数有以下用处:1.可以在基于函数的索引中使用该函数;2.可以在物化视图中调用;3.Oracle11g开始会对其参数及其返回结果进行缓存处理以提升性能。但是,是不是一个确定性函数是需要用户来负责的,就是说对函数进行编译的时候不会检查出这个函数是否是确定性的。来看个例子:这个函数返回当前输入时间+1天,只要输...原创 2018-02-10 14:20:57 · 27408 阅读 · 2 评论 -
自治事务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 · 5198 阅读 · 0 评论 -
动态SQL拼装技巧
存储过程里有时候需要根据不同的条件拼装动态SQL,比如下图是一个简单的sql语句拼装函数:执行结果如下:PLSQL的字符串连接时,需要用2个单引号表示1个单引号,比如上例中的to_date函数里面用到的日期模式。实际应用中可能会拼装比这个复杂很多倍的动态SQL,特别是当引号比较多的时候,对于拼装出来的SQL是否有问题需要调试很多次才能拼正确。那么如何减少拼装的错误呢,这原创 2018-01-30 06:05:41 · 8289 阅读 · 0 评论 -
ORACLE表名的大小写
我们都知道,Oracle数据库对表名、字段名的大小写是不敏感的。比如用以下语句(全部用小写字母)创建一个表,可以看到数据字典里存放的都是大写字母:然而,Oracle也是可以做到对表名、字段名或其他对象名称的大小写敏感,只要在操作的时候加上一对双引号""即可:甚至是可以重名:这种情况下就要特别小心,必须在任何操作中都加上"",否则就会出现ORA-00942:原创 2018-01-30 02:44:19 · 13174 阅读 · 0 评论 -
Oracle PLSQL包和局部模块的区别使用
为了减少重复代码量,需要将存储过程中可以模块化的部分代码封装成一个新的存储过程或函数。刚开始写plsql程序时,每逢遇到这种场景我就会把原来的存储过程改写成一个包(PACKAGE),问题就迎刃而解了。后来得知,实际上不是必须使用包才能解决问题,在原存储过程中把需要封装的代码定义成局部模块(Local or Nested Module),效果是一样的。显然这2种方式都可以剥离重复代码减少代码量,提高...原创 2018-02-10 09:18:26 · 559 阅读 · 0 评论