Tag Archives: ORACLE

按照SQL的方式操作PLSQL中的集合变量

直接上程序代码,唯一感觉不爽的地方就是集合类型要在数据库级别定义。根据ORACLE PL/SQL Programming书上的介绍,在Oracle 12c中,table语句支持在package中定义的集合变量,因无12c的环境尚未实际验证。 定义数据库级别的对象和集合类型 使用table语句,让oracle将集合在系统内容转换为虚拟的数据库表,然后可以用sql的方式操作集合变量

Oracle存储中的集合类型测试

主要测试了Associative array和Nested Table两种类型。

Oracle存储过程中的ORA-01031错误分析

在调用存储过程中,需要使用EXECUTE IMMEDIATE的方式动态创建临时表,运行的时候发生了下面的错误: 但奇怪的是,同样的语句,在SQL窗口执行正常。 这种现象的原因,涉及到Oracle对用户权限的处理,在Oracle中用户的权限分为三类: 1. 系统权限:对于数据库某一类对象的权限,如创建表、更改表等操作,具体的权限清单可以在SYSTEM_PRIVILEGE_MAP表中查询到。 说明:权限的名称中如含有ANY,表示权限不限当前的Schema。 2. 对象权限:对于特定数据库对象(如表、视图、、触发器、函数、存储过程等)的权限(如SELECT、INSERT、UPDATE、DELETE等),授权方式为: 3. 角色权限:用户的系统权限和对象权限都是直接赋予用户的,为了简化权限的管理,可以把相同的权限赋予某个角色,然后把角色赋予用户,这样用户就间接的拥有了角色的权限。Oracle默认已经设置了几个角色,常用的是CONNECT、RESOURCE、DBA等。 调用存储过程中的权限机制,根据网上的资料分析总结,在存储过程的内部,用户所具有的权限是存储过程的Owner的系统权限和对象权限,不包括角色权限。 在最前面所说的问题,就是用户创建表的权限是在角色权限中,所以在SQL窗口中可以执行,但在存储过程中无法执行。 处理方法: 1. 在存储过程的声明语句增加AUTHID CURRENT_USER语句,Oracle判断存储过程权限变为调用者的所有权限,不再是存储过程Owner的权限。 2. 给用户增加CREATE TABLE的系统权限 权限相关的表: 用户拥有的系统权限 USER_SYS_PRIVS 用户拥有的对象权限 USER_TAB_PRIVS 用户拥有的角色 USER_ROLE_PRIVS 系统内的角色 DBA_ROLES 角色拥有的系统权限 ROLE_SYS_PRIVS 角色拥有的角色权限 ROLE_TAB_PRIVS 权限清单 SYSTEM_PRIVILEGE_MAP 参考资料: Oracle 用户、对象权限、系统权限

Sqlserver版本的wm_concat

表定义: 按照日期的年月日汇总,flag列用逗号分隔,类似Oracle中的wm_concat和listagg功能,可以使用下面的语句:

Oracle登录触发器应用

项目使用一个新建的用户开发应用,但应用的SQL语句中没有写SCHEMA的名字。 项目上线后这个用户权限太大,能够创建和修改表和结构和数据,因此新建了一个查询用户,使用后发现原有的程序因为没有SCHEMA的名字无法正常运行。为了不对已经完工的程序、视图、存储过程等做大范围的修改,在登录后将用户当前的SCHEMA进行了修改。 创建查询用户 更改登录后的SCHEMA

Oracle分析函数三(first,last,first_value,last_value)

一、创建表,初始化数据 二、取部门工资最小和最大的人 运行结果: DEPT_NO MIN_SALARY_PERSON MAX_SALARY_PERSON MIN_SALARY_PERSON MAX_SALARY_PERSON 10 王五 田丰 王五 赵六,田丰 20 王麻子 陈中华 王麻子 陈中华 三、列出部门所有的人,并增加本部门工资最小和最大人的名字 注意last_value函数,使用order by会加上一个默认窗口RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,必须修改为窗口的全部记录,否则 运行结果: DEPT_NO EMP_NAME EMP_SALARY MIN_SALARY_PERSON MAX_SALARY_PERSON 10 王五 1500 王五 赵六 10 张三 2000 王五 赵六 10 李四 3000 王五 赵六 10 田丰 5000 王五 赵六 10 赵六 [...]

把Oracle数据库表内容导出文件

1、运行日志表和序列 JOB_STATUS字段说明: I: 初始化状态,存储过程执行后立即记录 P: 准备好查询SQL后,准备执行SQL语句 S: 数据导出成功,SQL_TEXT字段中有运行的SQL语句 E: 数据导出失败,ERROR_MESSAGE为出错的文本 2、创建导出文件夹、并分配权限 3、存储过程代码 按照表的修改日期提出数据,假定所有表的最后修改日期字段为ETL_DT

Oracle存储过程中异常处理测试

总结如下: 1、如果SELECT … INTO …语句取不到数据,将触发NO_DATA_FOUND异常,判断SQL%NOTFOUND的语句不生效 2、EXCEPTION语句必须和BEGIN/END块一起使用 测试相关的表: 测试的存储过程:

Oracle 11g的Pivot和Unpivot函数简单测试

Oracle对行列转换提供了Pivot和Unpivot两个函数,下面是个简单的测试 一、创建表和测试数据 二、行转列的Pivot函数 运行结果: STU_NAME 语文_SCORE 数学_SCORE 英语_SCORE 张三 100 98 86 王五 88 李四 99 85 90 三、列转行Unpivot函数 运行结果: STU_NAME CLASS_TYPE SCORE 张三 语文_SCORE 100 张三 数学_SCORE 98 张三 英语_SCORE 86 王五 语文_SCORE 88 李四 语文_SCORE 99 李四 数学_SCORE 85 李四 英语_SCORE 90 四、注意事项 行转列中,为转置的列起别名的时候,名称必须在双引号中;如果不写别名,生成列的名字是带单引号的。 同样,在列转行IN语句中的字段名称,也必须在双引号中。

AIO引发的惨案

oracle的参数配置中,大部分人都建议开启AIO功能,以提高IO的性能。 公司最近根据oracle维保商的建议,开启的这个功能,没想到引发了大问题,整个数据库的性能急剧下降,关联的多个应用系统都无法正常运行,最好不得已进行了回退。 后来分析原因,发现开启AIO主要是提高了数据库对存储写的吞吐量,但未考虑到对存储的压力。按照经验估算,磁盘只有在75%负载的情况下,响应时间才会在预期范围内。根据下面存储性能和负载的关系曲线,如果存储已经负载比较高,开启AIO增加负载后性能会出现大幅的下降,悲剧就这样发生了。