- 浏览: 219941 次
- 性别:
- 来自: 大连
最新评论
-
曾老师:
怎么调用呢 传递的参数是什么格式?
通过一个实际的例子学习Oracle存储过程 -
QQ371496669:
Thank You !有点瑕疵:--参数IN表示输入参数,OU ...
通过一个实际的例子学习Oracle存储过程 -
cheeruplc:
...
为学日益 为道日损 -
cheeruplc:
这里有个问题请教一下,帮我解答一下啊,先谢谢了哈, 我使用 ...
Ajax的思考 -
yanyuening:
不错,学习了
通过一个实际的例子学习Oracle存储过程
无疑在大数据量的情况下使用临时表版本的效率会远远高于使用游标版本。
使用游标版本:
sql 代码
- cursor cur_swcode is
- select code as swcode, name as swname from swcode where flag = 3;
- begin
- for rec_swcode in cur_swcode loop
- --DBMS_OUTPUT.put_line(rec_swcode.swcode||rec_swcode.swname);
- --本期累计批准的核实调查条数
- select count(*)
- into vn_bqljpzdchsts
- from sh_unpass_sub
- left outer join sh_unpass on sh_unpass.MS_ID = sh_unpass_sub.MS_ID
- left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
- where pz_date >= to_date(vs_start_day, 'yyyymmdd')
- and pz_date <= to_date(vs_end_day, 'yyyymmdd')
- and pz_flag = 'R'
- and cpcode.swcode = rec_swcode.swcode;
- --上期批准上期未办结的调查核实条数
- select count(*)
- into vn_sqpzsqwbjdchsts
- from sh_unpass_sub
- left outer join sh_unpass on sh_unpass.MS_ID = sh_unpass_sub.MS_ID
- left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
- where pz_date >= to_date(vs_ln_start_day, 'yyyymmdd')
- and pz_date <= to_date(vs_ln_end_day, 'yyyymmdd')
- and pz_flag = 'R'
- and jzhs_flag <> 'R'
- and cpcode.swcode = rec_swcode.swcode;
- --期末未到期的调查核实数
- select count(*) into vn_qmwdqdchsts
- from sh_unpass_sub
- left outer join sh_unpass on sh_unpass.ms_id = sh_unpass_sub.ms_id
- left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
- where pz_date >= to_date(vs_start_day, 'yyyymmdd')
- and pz_date <= to_date(vs_end_day, 'yyyymmdd')
- and pz_flag = 'R'
- and cpcode.swcode = rec_swcode.swcode
- and FLOOR(to_date(vs_end_day, 'yyyymmdd')-pz_date)
- <
- case sh_unpass_sub.hsjg
- when '3' then 69
- else
- case sh_unpass_sub.hsfs
- when '1' then 7
- when '2' then 36
- when '3' then 72
- end
- end;
- --到期已审核无信息的调查核实条数
- select count(*) into vn_dqyshwxxdchsts
- from sh_unpass_sub
- left outer join sh_unpass on sh_unpass.ms_id = sh_unpass_sub.ms_id
- left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
- where pz_date>=to_date(vs_start_day,'yyyymmdd')
- and pz_date<=to_date(vs_end_day,'yyyymmdd')
- and up_reason = '3'
- and pz_flag = 'R'
- and cpcode.swcode = rec_swcode.swcode ;
- --到期未核实完结条数
- select count(*) into vn_dqwwjhsts
- from sh_unpass_sub
- left outer join sh_unpass on sh_unpass.ms_id = sh_unpass_sub.ms_id
- left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
- where pz_date>=to_date(vs_start_day,'yyyymmdd')
- and pz_date<=to_date(vs_end_day,'yyyymmdd')
- and pz_flag = 'R'
- and jzhs_flag<>'R'
- and cpcode.swcode = rec_swcode.swcode
- and FLOOR(to_date(vs_end_day, 'yyyymmdd')-pz_date)
- >
- case sh_unpass_sub.hsjg
- when '3' then 69
- else
- case sh_unpass_sub.hsfs
- when '1' then 7
- when '2' then 36
- when '3' then 72
- else 7
- end
- end;
- --到期超时核实完结条数
- select
- sum(
- floor
- (
- case sh_unpass_sub.hsjg
- when '3' then
- case
- when jzhs_date-pz_date>69
- then (jzhs_date-pz_date-69)/10+1
- else 0
- end
- else
- case sh_unpass_sub.hsfs
- when '1' then
- case
- when jzhs_date-pz_date>7
- then (jzhs_date-pz_date-7)/7+1
- else 0
- end
- when '2' then
- case
- when jzhs_date-pz_date>36
- then (jzhs_date-pz_date-36)/10+1
- else 0
- end
- when '3' then
- case
- when jzhs_date-pz_date>72
- then (jzhs_date-pz_date-72)/10+1
- else 0
- end
- else 0
- end
- end
- )
- ) into vn_dqcshswjts
- from sh_unpass_sub
- left outer join sh_unpass on sh_unpass.ms_id = sh_unpass_sub.ms_id
- left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
- where pz_date>=to_date(vs_start_day,'yyyymmdd')
- and pz_date<=to_date(vs_end_day,'yyyymmdd')
- and pz_flag = 'R'
- and jzhs_flag = 'R'
- and cpcode.swcode = rec_swcode.swcode;
- end loop;
不使用游标版本:
sql 代码
- insert into dchsjsltjb_tmp(swcode,swname,num1,num2,num3,num4,num5,num6)
- select code,name,v1,v2,v3,v4,v5,v6 from swcode
- left outer join
- --本期累计批准的核实调查条数
- --insert into dchsjsltjb_tmp(swcode,num1)
- (
- select cpcode.swcode as swcode ,count(*) as v1
- --into vn_bqljpzdchsts
- from sh_unpass_sub
- left outer join sh_unpass on sh_unpass.MS_ID = sh_unpass_sub.MS_ID
- left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
- --left outer join swcode on swcode.code = cpcode.swcode
- where pz_date >= to_date(vs_start_day, 'yyyymmdd')
- and pz_date <= to_date(vs_end_day, 'yyyymmdd')
- and pz_flag = 'R'
- group by swcode
- --and cpcode.swcode = rec_swcode.swcode;
- ) sel1 on sel1.swcode = swcode.code
- left outer join
- (
- --上期批准上期未办结的调查核实条数
- --insert into dchsjsltjb_tmp(swcode,num2)
- select cpcode.swcode as swcode ,count(*) as v2
- --into vn_sqpzsqwbjdchsts
- from sh_unpass_sub
- left outer join sh_unpass on sh_unpass.MS_ID = sh_unpass_sub.MS_ID
- left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
- --left outer join swcode on swcode.code = cpcode.swcode
- where pz_date >= to_date(vs_ln_start_day, 'yyyymmdd')
- and pz_date <= to_date(vs_ln_end_day, 'yyyymmdd')
- and pz_flag = 'R'
- and jzhs_flag <> 'R'
- group by swcode
- --and cpcode.swcode = rec_swcode.swcode;
- ) sel2 on sel2.swcode = swcode.code
- --期末未到期的调查核实数
- left outer join
- (
- --insert into dchsjsltjb_tmp(swcode,num3)
- select cpcode.swcode as swcode ,count(*) as v3
- --into vn_qmwdqdchsts
- from sh_unpass_sub
- left outer join sh_unpass on sh_unpass.ms_id = sh_unpass_sub.ms_id
- left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
- --left outer join swcode on swcode.code = cpcode.swcode
- where pz_date >= to_date(vs_start_day, 'yyyymmdd')
- and pz_date <= to_date(vs_end_day, 'yyyymmdd')
- and pz_flag = 'R'
- --and cpcode.swcode = rec_swcode.swcode
- and FLOOR(to_date(vs_end_day, 'yyyymmdd')-pz_date)
- <
- case sh_unpass_sub.hsjg
- when '3' then 69
- else
- case sh_unpass_sub.hsfs
- when '1' then 7
- when '2' then 36
- when '3' then 72
- else 7
- end
- end
- group by swcode
- ) sel3 on sel3.swcode = swcode.code
- --到期已审核无信息的调查核实条数
- left outer join
- (
- --insert into dchsjsltjb_tmp(swcode,num4)
- select cpcode.swcode as swcode ,count(*) as v4
- --into vn_dqyshwxxdchsts
- from sh_unpass_sub
- left outer join sh_unpass on sh_unpass.ms_id = sh_unpass_sub.ms_id
- left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
- --left outer join swcode on swcode.code = cpcode.swcode
- where pz_date>=to_date(vs_start_day,'yyyymmdd')
- and pz_date<=to_date(vs_end_day,'yyyymmdd')
- and up_reason = '3'
- and pz_flag = 'R'
- --and cpcode.swcode = rec_swcode.swcode ;
- group by swcode
- ) sel4 on sel4.swcode = swcode.code
- --到期未核实完结条数
- left outer join
- (
- --insert into dchsjsltjb_tmp(swcode,num5)
- select cpcode.swcode as swcode ,count(*) as v5
- --into vn_dqwwjhsts
- from sh_unpass_sub
- left outer join sh_unpass on sh_unpass.ms_id = sh_unpass_sub.ms_id
- left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
- --left outer join swcode on swcode.code = cpcode.swcode
- where pz_date>=to_date(vs_start_day,'yyyymmdd')
- and pz_date<=to_date(vs_end_day,'yyyymmdd')
- and pz_flag = 'R'
- and jzhs_flag<>'R'
- --and cpcode.swcode = rec_swcode.swcode
- and FLOOR(to_date(vs_end_day, 'yyyymmdd')-pz_date)
- >
- case sh_unpass_sub.hsjg
- when '3' then 69
- else
- case sh_unpass_sub.hsfs
- when '1' then 7
- when '2' then 36
- when '3' then 72
- else 7
- end
- end
- group by swcode
- ) sel5 on sel5.swcode = swcode.code
- --到期超时核实完结条数
- left outer join
- (
- --insert into dchsjsltjb_tmp(swcode,num6)
- select cpcode.swcode as swcode ,
- sum(
- floor
- (
- case sh_unpass_sub.hsjg
- when '3' then
- case
- when jzhs_date-pz_date>69
- then (jzhs_date-pz_date-69)/10+1
- else 0
- end
- else
- case sh_unpass_sub.hsfs
- when '1' then
- case
- when jzhs_date-pz_date>7
- then (jzhs_date-pz_date-7)/7+1
- else 0
- end
- when '2' then
- case
- when jzhs_date-pz_date>36
- then (jzhs_date-pz_date-36)/10+1
- else 0
- end
- when '3' then
- case
- when jzhs_date-pz_date>72
- then (jzhs_date-pz_date-72)/10+1
- else 0
- end
- else 0
- end
- end
- )
- ) as v6
- --into vn_dqcshswjts
- from sh_unpass_sub
- left outer join sh_unpass on sh_unpass.ms_id = sh_unpass_sub.ms_id
- left outer join cpcode on cpcode.CODE = sh_unpass.CPCODE
- --left outer join swcode on swcode.code = cpcode.swcode
- where pz_date>=to_date(vs_start_day,'yyyymmdd')
- and pz_date<=to_date(vs_end_day,'yyyymmdd')
- and pz_flag = 'R'
- and jzhs_flag = 'R'
- --and cpcode.swcode = rec_swcode.swcode;
- group by swcode
- ) sel6 on sel6.swcode = swcode.code
- where swcode.flag='3'
- ;
发表评论
-
通过一个实际的例子学习SQLServer存储过程
2007-03-30 09:50 4525前面写过一篇 通过一个实际的例子学习Oracle存储过程,现在 ... -
SQLServer你都用哪些函数
2007-03-28 18:02 2257下面是我在SQLServer中用的比较多的函数,你还用些什么函 ... -
Oracle 临时表的用法
2007-03-14 17:11 2167在Oracle中的临时表和SqlServer有些区别。 在Sq ... -
主流数据库分页的SQL语句
2007-03-13 10:04 4016主流数据库对分页查询都有不同程度的支持,下面分别是SQLSer ... -
通过一个实际的例子学习Oracle存储过程
2007-03-08 14:04 58082--创建存储过程 CREATE OR ... -
SQLServer和Oracle常用函数对比
2007-03-07 08:53 1186数学函数 1.绝对值 S:select abs( ... -
DTS增量导出方案汇总
2007-03-06 19:00 29351.触发器 这是一个 ...
相关推荐
取代游标的方案:使用min()函数或max()函数;用表函数;使用临时表
使用游标更新数据库
oracle 用SQL通过使用游标实现批量更新表中某个字段的值表名和字段名需要修改,字符串的截取的方法需要根据实际情况修改
在oracle数据库中,使用游标与过程实现排名。
jdbcTemplate分页彻底解决,使用游标滚动jdbcTemplate分页彻底解决,使用游标滚动
VB链接sql使用游标和存储,可以参考参考。。。
sql游标的使用,其中包含虚拟表的使用
SQL语句通常都可产生或处理一组记录,但是许多应用程序,尤其是T-SQL嵌入的主语言,通常不能把整个结果集作为一个单元来处理,这些应用程序就需要用一种机制来保证每次处理结果集中的一行或几行,游标(cursor)就提供...
什么是游标?游标有什么作用?Oracle游标的类型?Oracle游标的状态有哪些,怎么使用游标属性?如何使用显示游标,?如何遍历循环游标?
本文将介绍利用使用表变量和游标实现数据库中表的遍历。 表变量来实现表的遍历 以下代码中,代码块之间的差异已经用灰色的背景标记。 代码如下: DECLARE @temp TABLE ( [id] INT IDENTITY(1, 1) , [Name] VARCHAR(10...
计算机等考三级数据库基础:临时表和游标的使用小总结.docx
数据库测试中,常常需要对数据库中的表进行填充或者批量更改数据的操作,可以通过游标来实现对每一个查询记录的操作,通过rand()函数的使用获得随机数,将随机数插入到表中,即可更新或填充数据表。
本资源结合实例实现一个复杂的存储过程,存储过程中有用到游标、临时表、循环、递归等知识,sql文件附有实例数据表创建的sql语句。
oracle存储过程使用游标对多表操作例子
约瑟夫问题的游标实现,可以自己设置杀人的个数
使用游标浏览学生记录,创建存储过程,包含游标,浏览学生记录
存储过程使用游标、临时表实现动态SQL查询 2020.3.24 用能第二周周二上午,任务需求:编写存储过程查询监测设备状态以及目标监测设备状态。(任务完成) 改存储过程包含了大量相关知识,特此记录,以便此后使用! 为...
此资料可以帮助没有用过数据库游标和函数的朋友学会去用它,只要跟着做一遍就会使用啦,这样就在设计数据库时可以充分灵活运用游标和函数
事务和游标的概述,使用游标的步骤,及相关联系,如何创建事务,理解事务的概念,掌握如何创建事务,掌握如何创建索引,掌握如何创建并使用视图。