1. 用游标传递数据
利用游标 REF CURSOR
可将数据集(多行记录)传递到PL/SQL函数:
SELECT *
FROM TABLE
(myfunction (CURSOR (SELECT *
FROM mytab)));
2.
利用两个实体化视图(或表)作为样板数据
CREATE MATERIALIZED VIEW
sum_sales_country_mv
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY
REWRITE
AS
SELECT SUBSTR (s.calendar_month_desc, 1, 4) YEAR,
c.country_id country,
SUM (sum_amount_sold) sum_amount_sold
FROM
sum_sales_month_mv s, customers c
WHERE s.cust_id = c.cust_id
AND
c.country_id IN ('US', 'UK', 'FR', 'ES', 'JP', 'AU')
GROUP BY SUBSTR
(s.calendar_month_desc, 1, 4), c.country_id;
CREATE MATERIALIZED VIEW sum_es_gend_mv
BUILD
DEFERRED
REFRESH FAST
ENABLE QUERY REWRITE
AS
SELECT SUBSTR
(s.calendar_month_desc, 1, 4) YEAR,
s.calendar_month_desc cal_month,
c.cust_gender,
SUM (sum_amount_sold) sum_amount_sold
FROM
sum_sales_month_mv s, customer c
WHERE s.cust_id = c.cust_id
AND
c.country_id = 'ES'
AND sunstr (s.calendar_month_desc, 1, 4) =
'2000'
GROUP BY SUBSTR (s.calendar_month_desc, 1,
4),
s.calendar_month_desc,
c.cust_gender;
3. 定义对象类型和基于对象类型的表类型
定义对象类型并且为进一步引用做好准备。
(1)定义对象类型:TYPE sales_country_t
CREATE TYPE
sales_country_t AS OBJECT (
YEAR VARCHAR2
(4),
country CHAR (2),
sum_amount_sold
NUMBER
);
(2)定义表类型:TYPE SUM_SALES_COUNTRY_T_TAB
CREATE TYPE sum_sales_country_t_tab AS TABLE OF
sales_country_t;
(3)定义对象类型:TYPE sales_gender_t
CREATE TYPE sales_gender_t AS OBJECT (
YEAR
VARCHAR2 (4),
country_id CHAR (2),
cust_gender CHAR
(1),
sum_amount_sold NUMBER
);
(4)定义表类型:TYPE
SUM_SALES_GENDER_T_TAB
CREATE TYPE sum_sales_gender_t_tab
AS TABLE OF sales_gender_t;
(5)定义对象类型:TYPE sales_roll_t
CREATE TYPE sales_roll_t AS OBJECT (
channel_desc VARCHAR2
(20),
country_id CHAR (2),
sum_amount_sold
NUMBER
);
(6)定义表类型:TYPE SUM_SALES_ROLL_T_TAB
CREATE TYPE sum_sales_roll_t_tab AS TABLE OF
sales_roll_t;
(7)检查一下建立的类型
SELECT object_name,
object_type, status
FROM user_objects
WHERE object_type =
'TYPE';
4. 定义包:Create package and define REF
CURSOR
CREATE OR REPLACE PACKAGE
cursor_pkg
IS
TYPE sales_country_t_rec IS RECORD (
YEAR
VARCHAR (4),
country CHAR (2),
sum_amount_sold
NUMBER
);
TYPE sales_gender_t_rec IS RECORD
(
YEAR VARCHAR2 (4),
country_id CHAR
(2),
cust_gender CHAR (1),
sum_amount_sold NUMBER
);
TYPE sales_roll_t_rec IS RECORD (
channel_desc
VARCHAR2 (20),
country_id CHAR (2),
sum_amount_sold
NUMBER
);
TYPE sales_country_t_rectab IS TABLE OF sales_country_t_rec;
TYPE sales_roll_t_rectab IS TABLE OF sales_roll_t_rec;
TYPE strong_refcur_t IS REF CURSOR
RETURN
sales_country_t_rec;
TYPE row_refcur_t IS REF CURSOR
RETURN
sum_sales_country_mv%ROWTYPE;
TYPE roll_refcur_t IS REF CURSOR
RETURN
sales_roll_t_rec;
TYPE refcur_t IS REF CURSOR;
END
corsor_pkg;
5. 定义表函数
(1)定义表函数:FUNCTION Table_Ref_Cur_Week
CREATE OR REPLACE FUNCTION table_ref_cur_week (cur
CURSOR.refcur_t)
RETURN sum_sales_country_t_tab
IS
YEAR
VARCHAR (4);
country CHAR (2);
sum_amount_sold
NUMBER;
objset sum_sales_country_t_tab := sum_sales_country_t_tab
();
i NUMBER := 0;
BEGIN
LOOP
--
Fetch from cursor variable
FETCH cur
INTO YEAR, country,
sum_amount_sold;
EXIT WHEN cur%NOTFOUND;
-- exit when last row is
fetched
-- append to collection
i := i + 1;
objset.EXTEND;
objset
(i) := sales_country_t (YEAR, country, sum_amount_sold);
END LOOP;
CLOSE cur;
RETURN
objset;
END;
/
(2)定义表函数:FUNCTION Table_Ref_Cur_Strong
CREATE OR REPLACE FUNCTION table_ref_cur_strong (cur
cursor_pkg.strong_refcur_t)
RETURN sum_sales_country_t_tab
PIPELINED
IS
YEAR VARCHAR (4);
country CHAR
(2);
sum_amount_sold NUMBER;
i NUMBER :=
0;
BEGIN
LOOP
FETCH cur
INTO YEAR, country,
sum_amount_sold;
EXIT WHEN cur%NOTFOUND; -- exit
when last row fetched
PIPE ROW (sales_country_t (YEAR, country,
sum_amount_sold));
END LOOP;
CLOSE cur;
RETURN;
END;
/
(3)定义表函数:FUNCTION
Table_Ref_Cur_row
CREATE OR REPLACE FUNCTION
table_ref_cur_row (cur cursor_pkg.row_refcur_t)
RETURN
sum_sales_country_t_tab PIPELINED
IS
in_rec cur%ROWTYPE;
out_rec
sales_country_t := sales_country_t (NULL, NULL, NULL);
BEGIN
LOOP
FETCH
cur
INTO in_rec;
EXIT WHEN cur%NOTFOUND; -- exit when
last row is fetched
out_rec.YEAR := in_rec.YEAR;
out_rec.country :=
in_rec.country;
out_rec.sum_amount_sold := in_rec.sum_amount_sold;
PIPE
ROW (out_rec);
END LOOP;
CLOSE cur;
RETURN;
END;
/
(4)定义表函数:FUNCTION
Gender_Table_Ref_Cur_Week
CREATE OR REPLACE FUNCTION
gender_table_ref_cur_week (cur cursor_pkg.refcur_t)
RETURN
sum_sales_gender_t_tab
IS
YEAR VARCHAR2
(4);
country_id CHAR (2);
cust_gender CHAR
(1);
sum_amount_sold NUMBER;
objset sum_sales_gender_t_tab :=
sum_sales_gender_t_tab ();
i NUMBER :=
0;
BEGIN
LOOP
FETCH cur
INTO YEAR, country_id, cust_gender,
sum_amount_sold;
EXIT WHEN cur%NOTFOUND; -- exit when
last row is fetched
i := i + 1;
objset.EXTEND;
objset (i)
:=
sum_sales_gender_t (YEAR, country_id, cust_gender,
sum_amount_sold);
END LOOP;
CLOSE cur;
RETURN objset;
END;
/
6. 调用表函数
下列 SQL 查询语句调用已被定义的表函数。
SELECT *
FROM
TABLE (table_ref_cur_week (CURSOR (SELECT *
FROM
sum_sales_country_mv)));
SELECT *
FROM TABLE (table_ref_cur_strong (CURSOR
(SELECT *
FROM sum_sales_country_mv)));
SELECT *
FROM TABLE (table_ref_cur_row (CURSOR (SELECT
*
FROM sum_sales_country_mv)));
SELECT *
FROM TABLE (table_ref_cur_week (CURSOR
(SELECT *
FROM sum_sales_country_mv
WHERE country = 'AU')));
例子:
首先你要创建一个type类存储结果
create or replace type type_jax_varc2tab is table of varchar2(2000)
然后创建一个如下的function来返回结果
CREATE OR REPLACE FUNCTION f_jax_returntab(p_str IN VARCHAR2
) RETURN type_jax_varc2tab IS
/******************************************************************
Ver1.0 Created by jaxzhang on 2011-01-13
create or replace type type_jax_varc2tab is table of varchar2(2000);
测试用例:SELECT * FROM TABLE(f_jax_returntab('ACCTG_TRANS'));
******************************************************************/
v_numtab type_jax_varc2tab := type_jax_varc2tab(); --返回内存表
BEGIN
FOR rec IN (select rownum r1,table_name from user_tables where table_name like p_str||'%' ) LOOP
v_numtab.EXTEND;
v_numtab(rec.r1) := rec.table_name;
END LOOP;
RETURN v_numtab;
EXCEPTION
WHEN OTHERS THEN
v_numtab.DELETE;
END;
查询样式如下
SELECT * FROM TABLE(f_jax_returntab('ACCTG_TRANS'));
发表评论
-
查看当前Session SQL
2014-07-08 11:51 1044SELECT (SELECT listagg(b.sql_te ... -
CLOB列 XML信息查看
2014-05-28 10:28 986--使用该SQL查询节点情况 SELECT * FROM ... -
【转】ORACLE 临时表空间使用率过高的原因及解决方案
2012-12-25 15:24 935http://www.cnblogs.com/wonder31 ... -
oracle:获取session的IP地址
2012-10-20 02:38 4228方法1 创建触发器: create orreplace ... -
Oracle ora-01002
2012-08-11 02:43 28781ORA-01002:fetch超出序列 ... -
提高Oracle SQL的执行效率的3个方案
2012-08-08 00:57 957如果你想要提高Oracle SQL ... -
Oracle STA
2012-08-06 11:32 0第一步:创建优化任务 ... -
Oracle SQL小技巧
2012-08-06 11:21 01.两个字段可空的判断相等,用decode判断。 例子:d ... -
自动工作负载库(Automatic Workload Repository,AWR)
2012-07-23 22:45 1360自动工作负载库(Automatic Workload Repo ... -
orace的隔离级别
2012-07-21 01:06 1079隔离级别(isoation eve) 隔离级别定义了事务与 ... -
Oracle SQLID 与 Hash_value 之间的相互转化
2012-07-20 00:55 4672一、什么是SQLID SQLID是根据SQL 文本,经过 ... -
Oracle优化器和索引原
2012-07-13 00:34 1247Oracle优化器和索引原理 ============ ... -
Oracle优化器的RBO和CBO方式
2012-07-13 00:25 2375[/size]Or[size=large][size=smal ... -
Oracle 优化器详解
2012-07-13 00:18 1331一、优化器基本知识 Oracle在执行一个SQL之前,首先 ... -
SQL中使用WITH AS提高性能
2012-07-05 23:30 1154摘要:本文结合笔者实 ... -
Ibatis调用Oracle存储过程,以及返回Cursor结果集的问题
2012-07-01 23:46 2084最近开始接触Oracle了,接触的越多越感受到自己的渺小!(o ... -
Oracle表连接操作——Hash Join(哈希连接
2012-05-20 17:05 0连接 http://space.itpub.net/?uid ... -
Oracle hash join
2012-05-20 17:00 876hash join是oracle里面一个 ... -
转--一次HASH JOIN 临时表空间不足的分析和优化思路
2012-05-20 15:36 4481最近遇到一个语句, 只要一执行这个语句就会出现报错临时表空间 ... -
SQL*PLUS SET 变量
2012-05-02 22:46 854SQL*PLUS SET变量 SQL*PLUS维护系 ...
相关推荐
Oracle自定义函数返回一张表。主要用于生成一张等时间间隔的表数据。
postgresql 兼容 oracle 函数, postgresql 兼容 oracle 函数,postgresql 兼容 oracle 函数, postgresql 兼容 oracle 函数
Oracle常用的和表(Table)相关的命令
本文档详细介绍了如何在Oracle数据库中实现像C#等其它编程语言中Split函数来拆分字符的功能。例子简单易懂,并附有详细的实现过程。
oracle表的构建
表函数可接受查询语句或游标作为输入参数,并可输出多行数据。该函数可以平行执行,并可持续输出数据流,被称作管道式输出。应用表函数可将数据转换分阶段处理,并省去中间结果的存储和缓冲表。 1. 用游标传递数据 ...
本博客简单介绍一下oracle分组函数之rollup的用法,rollup函数常用于分组统计,也是属于oracle分析函数的一种 环境准备 create table dept as select * from scott.dept; create table emp as select * from ...
oracle 管道函数 动态游标,自定义类型,转table
oracle split函数,内有两种实现方式,可直接返回多行记录如: 传入参数: SELECT * FROM TABLE(mm_split('中国,be,c,de',',')); 返回四条记录: 中国 be c de
SQL中的单记录函数 1.ASCII 返回与指定的字符对应的十进制数; SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual; A A ZERO SPACE --------- --------- --------- --------- ...
oracle中没有方便分割字符串的的函数,仿照c#中的split方法和网友的方法,并进行了改进,(1)先自定义个类型str_split,(2)分割函数splitstr,返回table;(3)取第n个数组的get_splitstr,用户1,2,3部建好,使用时只需要...
获取汉字首字母拼音 Oracle 拼音检索函数 已对性能和频度优化 调用速度快。select f_pinyin('中国') from dual; select * from table where f_pinyin(field) like '%%';
日常管理维护一个oracle数据库服务器的时,经常会碰到修改view,table结构的情况,而且由于oracle view,函数,存储过程等对象的相互关联的关系,经常会由于一个view,table,fun,proc的修改而导致相关的对象失效。...
Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现,不过使用LAG和LEAD有更高的效率。 代码如下:CREATE TABLE salaryByMonth( employeeNo varchar2(20)...
在Oracle数据库中,如何查找,定位一张表最后一次的DML操作的时间呢? 方式有三种,不过都有一些局限性,下面简单的解析、总结一下。 1:使用ORA_ROWSCN伪列获取表最后的DML时间 ORA_ROWSCN伪列是Oracle 10g开始...
Oracle 自定义split 函数 Oracle没有提供split函数,但可以自己建立一个函数实现此功能。比如“abc defg hijkl nmopqr stuvw xyz”,分隔符是空格,但空格个数不定。 源代码: CREATE OR REPLACE TYPE ty_str_...
ORACLE函数大全 ________________________________________ 作者:[本站编辑] 来源:[CSDN] 浏览:[ ] SQL中的单记录函数 1.ASCII 返回与指定的字符对应的十进制数; SQL> select ascii('A') A,ascii('a') a,...
Oracle日期函数用于对Oracle数据库中的日期及时间进行处理,下面就为您详细介绍Oracle日期函数的用法,希望对您能有所启迪。 (1)SYSDATE和TRUNC 两个Oracle日期函数取Oracle服务器系统的日期和截掉小数部分的功能...
支持自动ORACLE大表分区: 版本进度: 31. 20110420 V2.2 支持任意表任意时间字段分区 以下为安装部署部分: 1.分区相关脚本部署执行顺序,安装前请确保该用户拥有管理员权限, 同时请执行GRANT CREATE ANY TABLE ...