Oracle 函数返回游标的方法及应用

Oracle函数返回游标的方法及应用简析

王凤利康俊霞

河北北方学院张家口职业技术学院

【摘要】Oracle函数除了可以返回数值类型和字符类型等常用数据类型的数

据以外，还可以返回游标类型的数据，在某些情况下，返回游标类型的函数为我们

解决一些应用中的难题。本文就是通过一个实例来介绍返回游标函数的创建过程。

【关键字】Oracle，函数，包，游标。

1 问题的提出

在油田信息系统建设过程中，遇到了这样一个问题：输油站泵运行数据表(DHC05)的结构为：时间(SJ)、单位名称(DWMC)、泵号(BH)、泵压(BY1)、电压(DY)、电流(DL)、排量(PL)，主键为：时间、单位名称、泵号，每整点对运行泵取一次数据，不存储不运行泵(运行时间为0)的数据。现要输出如下报表：

报表的查询条件为单位名称和日期(yyyymmdd格式字符串)。运行泵号及泵台数根据各单位实际运行情况而定。

2 系统简介

系统采用B/S三层体系结构，数据库采用Oracle9.2版本，WEB服务器采用IIS6.0版本,客户端采用IE6.0及以上版本。

报表采用了统一的制表解释程序进行输出，该解释程序可以从一个单一的Oracle查询语句中提取数据，报表的输出样式为简单的二维表。

3 解决方案

根据目前的系统现状，要想直接利用当前系统输出本报表是不可能的，经过分析认为共有以下几种解决方案：

a)修改数据表结构

将原始数据表的结构进行调整，把时间、单位名称作为主键，将同一时刻的各个泵的数据逐个列出作为数据列，大致结构为：时间、单位名称、1号泵泵压、1号泵电压、1号泵电流、1号泵排量、2号泵泵压、2号泵电压、2号泵电流、2号泵排量、…。

本方案违背了数据库设计的基本原则，因各个单位的泵数不相等，只能按最大泵数进行数据库结构设计，当站库继续扩大、泵数继续增加时需要对数据库结构和所有用到该数据表

的程序及数据录入界面进行调整，所以本方案不可行。

b)建立中间表

原始数据表不变，增加中间表，中间表以时间、单位名称作为主键，将同一时刻的各个泵的数据逐个列出作为数据列，大致结构为：时间、单位名称、1号泵泵压、1号泵电压、1号泵电流、1号泵排量、2号泵泵压、2号泵电压、2号泵电流、2号泵排量、…。通过存储过程将原始数据导入到中间表中，报表解释程序直接从中间表提取数据。

有一些人采用了本方案，通过建立中间表，制作了一定数量的报表，解决了一些实际问题，但对于本文中提到的问题，本方案有一定的缺陷：随着站库的扩大和泵台数的增加，需要不断调整中间表的结构；当本类需求比较多时，每个报表都要增加一个中间表，没有一个成批处理的解决办法，当这类需求比较少时可以采用本方案来快速完成任务，但当本类需求比较多时则不是一个好的解决办法。

c)单独编写报表输出程序

通过编写一个单独的WEB页面来输出本表。

本方案虽然可以较好地完成本表的输出任务，但不能用本方法来处理大量的同类需求，并且对报表的修改和调试都比较困难，报表制作完成以后不能形成可重复使用的成果，所以本方案也不是一个理想的处理方案。

d)重新编写报表解释程序

重新编写报表解释程序，用以实现多条查询语句结果数据的提取及嵌套报表的输出。

本方案是一个比较彻底地解决报表输出问题的方法，当报表解释程序可以实现多条查询语句结果数据的提取及嵌套报表的输出时，所有的报表制作问题都可以使用该解释程序来处理。但该程序的开发周期长，而且嵌套报表的实现难度大，在经费不增加的前题下不能采用本方案。

e)编写函数并修改报表解释程序

编写一个Oracle函数，将要展示的所有查询结果通过游标返回，将原来在报表解释程序中的查询语句用函数调用来替换，修改报表解释程序中表头处理代码，实现本类报表表头的展示功能，结合返回的结果集，完成报表输出功能。

本方案通过编写一个返回游标类型数据的函数并对报表解释程序进行简单的修改，能够快速圆满地完成本类报表的输出功能，在目前系统环境下算是一个比较好的解决方案。

4 具体实现

根据查询数据的特点，数据查询语句可以使用Left Join语句来实现，查询语名的写法大致为：

SELECT to_char(z.sj, ‘hh:mi’)

a.bh, a.by1, a.dy, a.dl, a.pl,

b.bh, b.by1, b.dy, b.dl, b.pl,

c.bh, c.by1, c.dy, c.dl, c.pl,

…

FROM (SELECT DISTINCT sj, dwdm

FROM dhc05

WHERE dwdm = 数据查询单位代码 and

to_char(sj, ‘yyyymmdd’) = 数据查询日期) z

LEFT JOIN dhc05 a ON a.sj = z.sj and a.dwdm = z.dwdm and a.bh = ‘1号泵’

LEFT JOIN dhc05 a ON b.sj = z.sj and b.dwdm = z.dwdm and b.bh = ‘2

号泵’

LEFT JOIN dhc05 a ON c.sj = z.sj and c.dwdm = z.dwdm and c.bh = ‘3号泵’

…

ORDER BY z.sj;

所以在函数中应首先查询可能的泵号数据，再根据泵号数据，及数据查询条件，拼写出整个查询语句。为了能够实现各种同类应用的需求，将查询语句中的所有可变部份全部以参数的形式进行传递。为了能够实现返回游标的函数，定义了一个包，将函数定义在包中。

5 结论

Oracle游标类型是一个比较特殊的数据类型，该数据类型不仅可以在程序中使用，同其它数据类型一样，游标也可以做为函数返回值的数据类型，通过对游标类型返回值的处理可以为我们解决一些复杂的问题。本文通过一个实际问题的解决过程，介绍了Oracle函数返回游标类型数据的实现及应用方法，为同类问题的解决提供了一种比较有效的解决方法。参考文献

[1] 《Oracle8 PL/SQL 程序计设》机械工业出版社Scott Urman著译友翻译组译

[2] 《PL/SQL User's Guide and Reference 10g Release 1》Oracle Database Documentation 10g

Release 1

Kang Junxia

Department of Computer,Zhangjiakou Technical College

【Abstract】 Oracle function may not only numerical value type and character type ,but also may return to the date of cursor type. In some

case ,the function of returning to cursor type may settle some

difficult subject .This text introduce the course.

【Key word】 Oracle, function, bundle, cursor

oracle函数

许多软件公司都理解开发不依赖于特定数据库类型(例如Oracle、SQL Server、DB2)的应用程序的重要性，它可以让客户们选择自己习惯的平台。一般来说，软件开发人员都能够识别出他们的负责数据库维护的客户，和必须使用现有平台和个性化的客户。关于Oracle和SQL Server之间的区别，已经有很多篇文章从企业的角度和数据库管理员的角度出发描写了两者之间的一般差别。在这篇文章里面，我将会从应用程序的角度向你描述SQL erver 和oracle平台之间的区别，并且将会讨论开发不依赖于数据库环境的应用程序的几种可能的方法。与此同时，我不会再论讨对于应用程序来说，两个平台之间显而易见的区别，例如表的分割和索引。定义通用接口和语言有很少的几种通用语言和接口可以让应用程序不依赖于数据库，想来也可以以同样的方式应用在关系型数据库上面: ANSI是美国国家标准局定义的，这是一家志愿者成员的组织(用私人基金运转)，他们在有关设备和程序等广泛领域内开发了国家承认的标准。在数据库领域， ANSI定义了编写SQL命令的标准，假设命令可以运行在任何的数据库上，而不需要更改命令的语法。 ODBC是开放数据库连接(ODBC)接口，微软定义的，它可以让应用程序访问数据库管理系统(DBMS)中的数据，使用SQL作为访问数据的标准。 ODBC允许最大的互联性，这意味着一个单个的应用程序可以访问不同的数据库管理系统。然后，应用程序终端用户可以添加开放数据库连接(ODBC)数据库驱动来将应用程序与他们所选择的数据库管理系统链接上。 OLE DB 是ODBC的继承者，是一组允许例如基于VB、C++、Access等任何可以连接到类似SQL Server、Oracle、DB2、MySQL等后台的“前台”的软件组件。在许多种情况下，OLE DB组件都比原有的ODBC提供了好得多的性能。 JDBC(Java数据库连接)应用程序接口是Java编程语言和广泛范围的数据库，SQL数据库和其他表列数据源(例如电子表格或者普通文本文件)之间，进行不依赖于数据库的连接的行业标准。JDBD应用程序接口为基于SQL的数据库访问提供了调用级别的应用程序接口。真实世界中的通用接口不幸的是，并不是所有数据库级别的命令都是ANSI，每个数据库平台都有自己的扩展功能。对于ANSI或者通用接口，一般来说都代表着几本功能，因此也可能意味着丧失了性能方面的竞争力。对于小型数据库和小型应用程序来说，要维护对数据库的通用访问是简单的，但是当数据库和/或应用程序变得越来越大，越来越复杂，你就不得不向代码中添加功能。 SQL Server和Oracle的常用函数对比 ---------数学函数 1.绝对值 S:select abs(-1) value O:select abs(-1) value from dual 2.取整(大) S:select ceiling(-1.001) value O:select ceil(-1.001) value from dual

Oracle 常见函数(一)——数值函数

Oracle常见数值函数 ----***特别说明***： x 可以是纯的数值，也可以是数值型表达式/* ABS(x)返回x绝对值 eg. */ selectabs(100),abs(-100) from dual; /* sign(x)判断x的正负，正数返回1，负数返回-1,0返回0； eg. */ selectsign(100),sign(-100),sign(0) from dual;

/* round(x[,n])对x进行四舍五入，保留n位小数，其中n采用其整数部分；没有n时默认四舍五入到整数位，n为负数时，四舍五入保留小数点左边n位(补零)， eg. */ selectround(5555.6666, 2.1), round(5555.6666, -2.6), round(5555.6666) from dual; /* trunc(x)对x进行直接截取，保留n位小数，其中n采用其整数部分；没有n时默认截取到整数位，n为负数时，截取保留小数点左边n位(补零)， eg. */ selecttrunc(5555.66666,2.1), trunc(5555.66666,-2.6), trunc(5555.033333) from dual; /* ceil(x)对x进行向上取整，返回不小于x的最小整数(可以是整数x本身)。

eg. */ selectceil(3.1), ceil(2.8+1.3), ceil(0) from dual; /* floor(x)对x进行向下取整，返回不大于x的最大整数(可以是整数x本身)。eg. */ selectfloor(3.1), floor(2.8+1.3), floor(0) from dual; /* mod(x,y)求x除以y的余数，x，y为数字型表达式。 eg. */ selectmod(23,8),mod(24,8) from dual;

Oracle函数详解(经典)

Oracle常用函数/过程说明主要介绍Oracle的系统函数、过程和包。 SQL常用函数：数值函数： ABS Purpose 返回绝对值 Returns the absolute value of n. Example SELECT ABS(-15) "Absolute" FROM DUAL; Absolute ---------- 15 CEIL Purpose 取最小整数 Returns smallest integer greater than or equal to n. Example SELECT CEIL(15.7) "Ceiling" FROM DUAL;

Ceiling ---------- 16 * MOD Syntax MOD(m,n) Purpose 取余 Returns remainder of m divided by n. Returns m if n is 0. Example SELECT MOD(11,4) "Modulus" FROM DUAL; Modulus ---------- 3 * ROUND Syntax ROUND(n[,m]) Purpose 取四舍五入信息 Returns n rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer.

ORACLE常用函数和一些项目中常用的SQL语句

Oracle笔记实现中英文转换 Alter session set nls_language=‘ADMIN’ 从已知表中复制数据和结构 Create table test as select * from dept；从已知表中复制数据和结构，但是不包括数据 Create table test as select * from dept where 1=2 插入数据 Insert into test select * from dept 二、运算符 +——*/可以在select 语句中使用 ||连接两个字段，select deptno ||dname from dept 比较运算付：> >= = !=< <= 逻辑运算符：not ，or 集合运算符：intersect，union，union all，minus，使用ord er by 的时候必须使用位置序号，不能使用列名 Select *from emp intersect select * from emp where deptno=10； Select *from emp minus select * from emp where deptno=10； ---不包括重复行 Select *from emp where deptno=10 union select * from emp where deptno in（10，20）；----包括重复行 Select *from emp where deptno=10 union all select * from emp where deptno in（10，20）；三、常用ORACLE函数

oracle游标的使用及属性

ｏｒａｃｌｅ游标的使用及属性 oracle游标的使用游标是从数据表中提取出来的数据，以临时表的形式存放到内存中，在游标中有一个数据指针，在初始状态下指向的是首记录，利用fetch语句可以移动该指针，从而对游标中的数据进行各种操作，然后将操作结果写回到数据库中。一：定义游标ｃｕｒｓｏｒ游标名ｉｓｓｅｌｅｃｔ语句；示例：ｓｅｔｓｅｒｖｅｒｏｕｔｐｕｔｏｎｄｅｃｌａｒｅｔｅｍｐｓａｌｓｃｏｔｔ．ｅｍｐ．ｓａｌ％ｔｙｐｅ；ｃｕｒｓｏｒｍｙｃｕｒｓｏｒｉｓｓｅｌｅｃｔ＊ｆｒｏｍｓｃｏｔｔ．ｅｍｐｗｈｅｒｅｓａｌ＞ｔｅｍｐｓａｌ；ｂｅｇｉｎｔｅｍｐｓａｌ：＝８００；ｏｐｅｎｍｙｃｕｒｓｏｒ；ｅｎｄ；二：打开游标语法结构：ｏｐｅｎ游标名打开游标分为两步：１将符合条件的记录送入内存２将指针指向第一条记录三：提取游标数据

语法形式：ｆｅｔｃｈ游标名ｉｎｔｏ变量名１，变量名２，．．．．．；或者ｆｅｔｃｈ游标名ｉｎｔｏ记录型变量名；示例：ｓｅｔｓｅｒｖｅｒｏｕｔｐｕｔｏｎｄｅｃｌａｒｅｔｅｍｐｓａｌｓｃｏｔｔ．ｅｍｐ．ｓａｌ％ｔｙｐｅ；ｃｕｒｓｏｒｍｙｃｕｒｓｏｒｉｓｓｅｌｅｃｔ＊ｆｒｏｍｓｃｏｔｔ．ｅｍｐｗｈｅｒｅｓａｌ＞ｔｅｍｐｓａｌ；ｃｕｒｓｏｒｒｅｃｏｒｄｍｙｃｕｒｓｏｒ％ｒｏｗｔｙｐｅ；ｂｅｇｉｎｔｅｍｐｓａｌ：＝８００；ｏｐｅｎｍｙｃｕｒｓｏｒ；ｆｅｔｃｈｍｙｃｕｒｓｏｒｉｎｔｏｃｕｒｓｏｒｒｅｃｏｒｄ；ｄｂｍｓ＿ｏｕｔｐｕｔ．ｐｕｔ＿ｌｉｎｅ（ｔｏ＿ｃｈａｒ（ｃｕｒｓｏｒｒｅｃｏｒｄ．ｄｅｐｔｎｏ））；ｅｎｄ；四：关闭游标ｃｌｏｓｅ游标名； Oracle游标的属性之一------%isopen %isopen 属性----测试游标是否打开，没打开的情况下使用fetch语句将提示错误。示例：

oracle中常用函数大全

oracle中常用函数大全 1、数值型常用函数函数返回值样例显示 ceil(n) 大于或等于数值n的最小整数select ceil(10.6) from dual; 11 floor(n) 小于等于数值n的最大整数select ceil(10.6) from dual; 10 mod(m,n) m除以n的余数,若n=0,则返回m select mod(7,5) from dual; 2 power(m,n) m的n次方select power(3,2) from dual; 9 round(n,m) 将n四舍五入,保留小数点后m位select round(1234.5678,2) from dual; 1234.57 sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 select sign(12) from dual; 1 sqrt(n) n的平方根select sqrt(25) from dual ; 5 2、常用字符函数 initcap(char) 把每个字符串的第一个字符换成大写select initicap('mr.ecop') from dual; Mr.Ecop lower(char) 整个字符串换成小写select lower('MR.ecop') from dual; mr.ecop replace(char,str1,str2) 字符串中所有str1换成str2 select replace('Scott','s','Boy') from dual; Boycott substr(char,m,n) 取出从m字符开始的n个字符的子串select substr('ABCDEF',2,2) from dual; CD length(char) 求字符串的长度select length('ACD') from dual; 3 || 并置运算符select 'ABCD'||'EFGH' from dual; ABCDEFGH 3、日期型函数 sysdate当前日期和时间select sysdate from dual;

Oracle 游标使用全解

-- 声明游标；CURSOR cursor_name IS select_statement --For 循环游标 --（1）定义游标 --（2）定义游标变量 --（3）使用for循环来使用这个游标 declare --类型定义 cursor c_job is select empno,ename,job,sal from emp where job='MANAGER'; --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型 c_row c_job%rowtype; begin for c_row in c_job loop dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal); end loop; end; --Fetch游标 --使用的时候必须要明确的打开和关闭 declare --类型定义 cursor c_job is select empno,ename,job,sal from emp where job='MANAGER'; --定义一个游标变量 c_row c_job%rowtype; begin open c_job; loop --提取一行数据到c_row fetch c_job into c_row; --判读是否提取到值，没取到值就退出 --取到值c_job%notfound 是false --取不到值c_job%notfound 是true exit when c_job%notfound; dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);

Oracle常用函数及使用案例(珍藏版)

Oracle常用函数及使用案例（珍藏版）一：sql函数： lower（char）:将字符串转化为小写的格式。 upper（char）:将字符串转化为大写的格式。 length（char）：返回字符串的长度。 substr（char，m，n）：取字符串的字串。案例1.将所有员工的名字按小写的方式显示 select lower(ename),sal from emp; 案例2.将所有员工的名字按大写的方式显示。 select upper(ename),sal from emp; 案例3.显示正好为五个字符的的员工的姓名。 select * from emp where length(ename)=5; 案例4.显示所有员工姓名的前三个字符。 select substr(ename,1,3) from emp;//从名字的第一个字符开始取，向后取三个字符。案例5.以首字母为大写的方式显示所有员工的姓名。（1）首字母大写：select upper(substr(ename,1,1)) from emp; （2）完成后面字母小写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp; 案例6.以首字母为小写的方式显示所有员工的姓名。（需要有较高的灵活度，细心分析和清晰思路）（1）首字母小写：select upper(substr(ename,1,1)) from emp; （2）完成后面字母大写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select lower(substr(ename,1,1))||upper(substr(ename,2,length(ename)-1)) from emp; 案例7.函数（替换）：replace(char1,search_string,replace_string); 显示所有员工的姓名，用“我要替换A”替代所有“A”。 select replace(ename,'A','我是老鼠')from emp; 案例8.以首字母为小写的方式显示所有员工的姓名。 select replace(ename,substr(ename,1,1),lower(substr(ename,1,1)))from emp; 案例9.以首字母为大写的方式显示所有员工的姓名。 Select replace(ename,substr(ename,2,length(ename)-1),lower(substr(ename,2,length(ename) -1)))from emp; 二：数学函数：(在财务中用的比较多) ronud（sal）用于四舍五默认取整； ronud（sal，1）用于四舍五留一位小数。 trunc（sal）取整，忽略小数。截去小数部分。 trunc（sal，1）截取；小数点留一位，之后的右边的省去。 trunc（sal，-1）截取；只留整数，个位数取零。 floor（sal）向下最接近取整；比如1.1值为1.

Oracle显式游标和隐式游标

Oracle显式游标和隐式游标 SQL是用于访问Oracle数据库的语言，PL/SQL扩展和加强了SQL的功能，它同时引入了更强的程序逻辑, 下面在本文中将对游标的使用进行一下讲解,希望可以和大家共同学习进步。游标字面理解就是游动的光标。游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。用数据库语言来描述游标就是映射在结果集中一行数据上的位置实体，有了游标，用户就可以访问结果集中的任意一行数据了，将游标放置到某行后，即可对该行数据进行操作，例如提取当前行的数据等。游标有两种类型：显式游标和隐式游标。游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个处理操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。游标的属性有四种,分别是 SQL %ISOPEN，SQL %FOUND，SQL %NOTFOUND，SQL %ROWCOUNT。 SQL%ISOPEN返回的类型为布尔型,判断游标是否被打开，如果打开%ISOPEN等于true,否则等于false,即执行过程中为真,结束后为假。 SQL%NOTFOUND返回值为布尔型,判断游标所在的行是否有效，如果有效，则%FOUNDD等于true，否则等于false,即与%FOUND属性返回值相反。 SQL%FOUND返回值的类型为布尔型，值为TRUE代表插入删除更新或单行查询操作成功。 SQL%ROWCOUNT返回值类型为整型，返回当前位置为止游标读取的记录行数,

oracle常用函数列表速查 (1)

Oracle常用函数列表速查 PL/SQL单行函数和组函数详解函数是一种有零个或多个参数并且有一个返回值的程序。在SQL中Oracle内建了一系列函数，这些函数都可被称为SQL或PL/SQL语句，函数主要分为两大类：单行函数组函数本文将讨论如何利用单行函数以及使用规则。 SQL中的单行函数 SQL和PL/SQL中自带很多类型的函数，有字符、数字、日期、转换、和混合型等多种函数用于处理单行数据，因此这些都可被统称为单行函数。这些函数均可用于SELECT,WHERE、ORDER BY 等子句中，例如下面的例子中就包含了TO_CHAR,UPPER,SOUNDEX等单行函数。 SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')FROM empWhere UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename) 单行函数也可以在其他语句中使用，如update的SET子句，INSERT的V ALUES子句，DELET 的WHERE子句,认证考试特别注意在SELECT语句中使用这些函数，所以我们的注意力也集中在SELECT语句中。 NULL和单行函数在如何理解NULL上开始是很困难的，就算是一个很有经验的人依然对此感到困惑。NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为提个NULL值,这个规则也适合很多函数，只有CONCAT,DECODE,DUMP,NVL,REPLACE在调用了NULL参数时能够返回非NULL值。在这些中NVL函数时最重要的，因为他能直接处理NULL值，NVL有两个参数：NVL(x1,x2),x1和x2都式表达式，当x1为null时返回X2,否则返回x1。下面我们看看emp数据表它包含了薪水、奖金两项，需要计算总的补偿 column name emp_id salary bonuskey type pk nulls/unique nn,u nnfk table datatype number number numberlength 11.2 11.2 不是简单的将薪水和奖金加起来就可以了，如果某一行是null值那么结果就将是null，比如下面的例子： update empset salary=(salary+bonus)*1.1 这个语句中，雇员的工资和奖金都将更新为一个新的值，但是如果没有奖金，即salary + null,那么就会得出错误的结论，这个时候就要使用nvl函数来排除null值的影响。所以正确的语句是： update empset salary=(salary+nvl(bonus,0)*1.1

oracle11g游标及触发器相关知识

oracle11g 游标： 1. 当在PL/SQL中使用SQL语句时，Oracle会为其分配上下文区域，这是一段私有的内存区域，用于暂时保存SQL语句影响到的数据。游标是指向这段内存区域的指针。 2. Oracle中主要有两种类型的游标: (1) 隐式游标:所有的DML语句和PL/SQL SELECT 语句都有; (2) 显式游标:由开发人员声明和控制。 3. 可以使用的游标属性包括四种：%ROWCOUNT、%FOUND、%NOTFOUND、 %ISOPEN，这四种属性对于显式游标和隐式游标都有用，但是含义和使用方法略有不同。游标在使用属性时，需要以游标名称作为前缀，以表明该属性是哪个游标的，隐式游标没有名称，所以在使用隐式游标时采取了统一的一个名称SQL。 4. 在PL/SQL中的SELECT语句只能且必须取出一行数据，取出多行或者零行都被认为是异常，所以在对多行数据进行操作时，必须使用显式游标来实现。 5. 使用显式游标的步骤： (1)声明游标：CURSOR cursor_name is select_statement; (2)打开游标：OPEN cursor_name; (3)取游标中的数据：FETCH cursor_name INTO variable1,variable2,...; (4)关闭游标：CLOSE cursor_name; 6.用变量接收游标中的数据 sql> declare v_name emp.ename%TYPE; v_sal emp.sal%TYPE; cursor emp_cursor is select ename,sal from emp where deptno=10; begin open emp_cursor; loop fetch emp_cursor into v_name,v_sal; exit when emp_cursor%NOTFOUND; dbms_output.put_line(v_name || ‘的薪水是’ || v_sal);

Oracle 函数返回游标的方法及应用

Oracle函数返回游标的方法及应用简析王凤利康俊霞河北北方学院张家口职业技术学院【摘要】Oracle函数除了可以返回数值类型和字符类型等常用数据类型的数据以外，还可以返回游标类型的数据，在某些情况下，返回游标类型的函数为我们解决一些应用中的难题。本文就是通过一个实例来介绍返回游标函数的创建过程。【关键字】Oracle，函数，包，游标。 1 问题的提出在油田信息系统建设过程中，遇到了这样一个问题：输油站泵运行数据表(DHC05)的结构为：时间(SJ)、单位名称(DWMC)、泵号(BH)、泵压(BY1)、电压(DY)、电流(DL)、排量(PL)，主键为：时间、单位名称、泵号，每整点对运行泵取一次数据，不存储不运行泵(运行时间为0)的数据。现要输出如下报表：报表的查询条件为单位名称和日期(yyyymmdd格式字符串)。运行泵号及泵台数根据各单位实际运行情况而定。 2 系统简介系统采用B/S三层体系结构，数据库采用Oracle9.2版本，WEB服务器采用IIS6.0版本,客户端采用IE6.0及以上版本。报表采用了统一的制表解释程序进行输出，该解释程序可以从一个单一的Oracle查询语句中提取数据，报表的输出样式为简单的二维表。 3 解决方案根据目前的系统现状，要想直接利用当前系统输出本报表是不可能的，经过分析认为共有以下几种解决方案： a)修改数据表结构将原始数据表的结构进行调整，把时间、单位名称作为主键，将同一时刻的各个泵的数据逐个列出作为数据列，大致结构为：时间、单位名称、1号泵泵压、1号泵电压、1号泵电流、1号泵排量、2号泵泵压、2号泵电压、2号泵电流、2号泵排量、…。本方案违背了数据库设计的基本原则，因各个单位的泵数不相等，只能按最大泵数进行数据库结构设计，当站库继续扩大、泵数继续增加时需要对数据库结构和所有用到该数据表

【黑马程序员】Oracle 游标使用全解

【黑马程序员】Oracle 游标使用全解有很多同学在使用oracle 数据库的时候对游标这个东西不知道如何处理，感觉使用起来很难，今天我们就讨论一下游标的使用，满满的都是干货，以下代码几乎包含了oracle 游标使用的方方面面，全部通过了测试！ -- 声明游标； 1 C URSOR cursor_name IS select_statement --For 循环游标 --（1）定义游标 --（2）定义游标变量 --（3）使用for 循环来使用这个游标 01 02 03 04 05 06 07 08 09 10 11 12 13 14 declare --类型定义 cursor c_job is select empno ,ename ,job ,sal from emp where job ='MANAGER'; --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp 中的一行数据类型 c_row c_job%rowtype; begin for c_row in c_job loop dbms_output.put_line (c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal ); end loop; end ; --Fetch 游标 --使用的时候必须要明确的打开和关闭 01 02 03 declare --类型定义 cursor c_job

04 05 06 07 08 09 10 11 12 13 14 15 16 is select empno,ename,job,sal from emp where job='MANAGER'; --定义一个游标变量 c_row c_job%rowtype; begin open c_job; loop --提取一行数据到c_row fetch c_job into c_row; --判读是否提取到值，没取到值就退出 --取到值c_job%notfound 是false --取不到值c_job%notfound 是true exit when c_job%notfound; dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||' -'||c_row.sal); end loop; --关闭游标 close c_job; end; --1：任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。 begin update emp set ENAME='ALEARK' WHERE EMPNO=7469; if sql%isopen then dbms_output.put_line('Openging'); else dbms_output.put_line('closing'); end if; if sql%found then dbms_output.put_line('游标指向了有效行');--判断游标是否指向有效行 else dbms_output.put_line('Sorry'); end if; if sql%notfound then dbms_output.put_line('Also Sorry'); else dbms_output.put_line('Haha'); end if;

oracle常用函数习题及答案

--1、选择部门30中的雇员 select * from emp where deptno=30; --2、列出所有办事员的姓名、编号和部门 select ename,empno,dname from emp e inner join dept d on e.deptno = d.deptno where job=upper('clerk?); --3、找出佣金高于薪金的雇员 select * from emp where comm>sal; --4、找出佣金高于薪金60%的雇员 select * from emp where comm>sal*0.6 --5、找出部门10中所有经理和部门20中的所有办事员的详细资料 select * from emp where (deptno=10 and job=upper('manager')) or (deptno=20 and job=upper('clerk ')); --6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料 select * from emp where (deptno=10 and job=upper('manager')) or (deptno=20 and job=upper('clerk ')) or (job<>upper(…manager?) and job<>upper(…clerk?) and sal>=2000) --7、找出收取佣金的雇员的不同工作 select distinct job from emp where comm>0; --8、找出不收取佣金或收取的佣金低于100的雇员 select * from emp where nvl(comm,0)<100; --9、找出各月最后一天受雇的所有雇员 select * from emp where hiredate= last_day(hiredate); --10、找出早于25年之前受雇的雇员

Oracle存储过程学习_游标CURSOR使用

游标CURSOR的使用学习游标的类型： 1，隐式游标：在 PL/SQL 程序中执行DML SQL 语句时自动创建隐式游标，名字固定叫sql。 2，显式游标：显式游标用于处理返回多行的查询。 3，REF 游标：REF 游标用于处理运行时才能确定的动态 SQL 查询的结果一、隐式游标在PL/SQL中使用DML语句时自动创建隐式游标 q隐式游标自动声明、打开和关闭，其名为 SQL q通过检查隐式游标的属性可以获得最近执行的DML 语句的信息q隐式游标的属性有： q%FOUND – SQL 语句影响了一行或多行时为 TRUE q%NOTFOUND – SQL 语句没有影响任何行时为TRUE q%ROWCOUNT – SQL 语句影响的行数 q%ISOPEN - 游标是否打开，始终为FALSE begin update student s set s.sage = s.sage + 10; if sql %FOUND then dbms_output.put_line('这次更新了' || sql% rowcount); else dbms_output.put_line('一行也没有更新'); end if; end; 在select中有两个中比较常见的异常： 1. NO_DATA_FOUND 2. TOO_MANY_ROWS declare sname1 student.sname%TYPE; begin

select sname into sname1 from student; if sql%found then dbms_output.put_line(sql%rowcount); else dbms_output.put_line('没有找到数据'); end if; exception when too_many_rows then dbms_output.put_line('查找的行记录多于1行'); when no_data_found then dbms_output.put_line('未找到匹配的行'); end; 显式游标： sqlserver与oracle的不同之处在于：最后sqlserver会deallocate 丢弃游标，而oracle只有前面四步：声明游标、打开游标、使用游标读取记录、关闭

oracle常用函数介绍及其使用

Oracle函数 Oracle SQL提供了用于执行特定操作的专用函数，这些函数大大增强了SQL语言的功能。函数可以接受零个或者多个输入参数，并返回一个输出结果。Oracle数据库中主要使用两种类型的函数： 1.单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果，比如：MOD(x,y)返回x除以y的余数（x和y可以是两个整数，也可以是表中的整数列）。常用的单行函数有：字符函数：对字符串操作。数字函数：对数字进行计算，返回一个数字。转换函数：可以将一种数据类型转换为另外一种数据类型。日期函数：对日期和时间进行处理。 2.聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。比如SUM(x) 返回结果集中x列的总合。目录大纲 Oracle函数 (1) ?字符函数 (2) ?数字函数 (3) ?日期函数 (4) ?转换函数 (6) ?其他单行函数 (8) ?聚合函数 (9)

字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。下表列出了常用的字符函数。表1 字符函数表2 字符函数示例

数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。表3 数字函数说明： 1.ROUND(X[,Y])，四舍五入。在缺省y时，默认y=0；比如：ROUND(3.56)=4。 y是正整数，就是四舍五入到小数点后y位。ROUND(5.654,2)=5.65。 y是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。 2.TRUNC(x[,y])，直接截取，不四舍五入。在缺省y时，默认y=0；比如：TRUNC (3.56)=3。 y是正整数，就是四舍五入到小数点后y位。TRUNC (5.654,2)=5.65。 y是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。

Oracle数据库游标在包中的使用

--创建学员信息表 create table stuInfo ( stuId varchar2(15) not null, --学员Id,主键 stuName varchar2(10) not null, --学员姓名 stuNo varchar2(10) not null, --学号,外键应用stuMarks的stuNo stuAge int not null, --年龄 stuAddress varchar2(100) default('中国') not null,--家庭住址 stuEmail varchar2(100) not null --电子邮箱 ); alter table stuInfo add constraint PK_stuId primary key(stuId); alter table stuInfo add constraint CK_stuAge check(stuAge between 18 and 40); alter table stuInfo add constraint CK_stuEmail check(stuEmail like '%@%'); --创建序列 create sequence SQ_ID increment by 1 start with 10000; --为学员信息表创建触发器TG_STUID create or replace trigger TG_STUID before insert on stuInfo for each row begin select 'SID'||SQ_ID.Nextval into :new.stuId from dual; end; --向学员信息表中添加数据 insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values ('张飞','s1t0102',30,'三国',''); insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values ('关羽','s1t0830',35,'蜀国',''); insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values ('马超','s2t1326',25,'三国',''); insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values ('刘备','s3t0403',40,'蜀国',''); insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values ('诸葛亮','s2t1521',21,'蜀国',''); insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values ('刘翔','s3t0706',29,'上海',''); insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values ('曹操','s3t0915',34,'魏国',''); insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values ('孙权','s1t1123',32,'东吴',''); insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values ('董卓','s2t0507',35,'三国',''); insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values

Oracle常用函数

数值函数

日期函数 SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') FROM dual; SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual; 其他函数

5.高级查询（多表联合查询）例子： SELECT job 职务 , SUM(sal) 工资总和 FROM emp WHERE job != 'PRESIDENT' GROUP BY job HAVING SUM(sal)>4500 ORDER BY SUM(sal); 分析函数以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始 ROW_NUMBER返回连续的排位，不论值是否相等 RANK具有相等值的行排位相同，序数随后跳跃 DENSE_RANK 具有相等值的行排位相同，序号是连续的例子： SELECT deptno, ename, sal, comm, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) RANK FROM emp; SELECT ename, job, deptno, sal, ROW_NUMBER() OVER (ORDER BY sal DESC) AS SAL_RANK FROM SCOTT.EMP; SELECT d.dname, e.ename, e.sal, DENSE_RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) AS DENRANK FROM emp e, dept d WHERE e.deptno = d.deptno;

Oracle10.2并发条件下更新游标数据的研究

Oracle10.2并发条件下更新游标数据的研究本文测试在PL/SQL编程中，更新游标数据的2种方式以及并发条件下各种方式的实际表现。2种方式的效率问题不在此文讨论之列！一、环境准备数据库：Oracle10.2.0.4 测试工具：PL/SQL Developer9 二、数据准备我们使用oracle自带的演示用户scott登录数据库，为清楚看到数据变化，执行以下语句：查询emp表：结果如图1所示：

图1 Sal字段已经全部更新为1000 三、创建存储过程ps_cursor_for_update

四、测试ps_cursor_for_update PL/SQL Developer工具具有很强大的plsql调试功能，我们使用两个test窗口进行模拟并发执行的情况在编辑存储过程的界面，在打开游标的代码行加入一个断点：图2 在存储过程ps_cursor_for_update上点击右键，打开两个test窗口：窗口1中，输入参数填写2000 窗口2中，输入参数填写3000，如图3、4所示：图3 图4 两个窗口分别点击start bugger按钮，开始调试，并点击run按钮，分别运行到打开游标的一行，并在窗口2中进行单步调试，运行到如图5所示位置：

图5 此时窗口1中，开始单步调试，发现状态栏处于运行中（图7），但调试光标始终停在断点行（图6）图6 图7 说明游标打开的数据已经被窗口2的进程锁定，所以窗口1的进程无法打开数据下面把窗口2的断点去掉，并点击run按钮使此过程执行完毕，可以发现，此时窗口1中，代码已经执行到了原断点位置的下一行：图8 结论：窗口2执行了commit语句，PL/SQL过程结束，并解锁操作的数据，使窗口1的过程得以打开游标。查询emp表的数据：