CREATE OR REPLACE PACKAGE GLOBALPKG
AS
TYPE RCT1 IS REF CURSOR;
IDENTITY INTEGER;
END;
/CREATE OR REPLACE FUNCTION monthnumber
(
time IN VARCHAR2 DEFAULT NULL
)
RETURN GLOBALPKG.RCT1
IS
REFCURSOR GLOBALPKG.RCT1;
BEGIN
OPEN REFCURSOR FOR SELECT
user_id,
COUNT(s_id) as number
FROM t_access_user,
infotest
WHERE user_id = s_creater
AND SUBSTR(s_create_time, 0, 7) = monthnumber.time
GROUP BY user_id
UNION ALL
SELECT
user_id,
0 * NULL as number
FROM t_access_user,
infotest
WHERE NOT user_id = s_creater
AND NOT SUBSTR(s_create_time, 0, 7) = monthnumber.time
GROUP BY user_id
;
RETURN REFCURSOR;END;
/
AS
TYPE RCT1 IS REF CURSOR;
IDENTITY INTEGER;
END;
/CREATE OR REPLACE FUNCTION monthnumber
(
time IN VARCHAR2 DEFAULT NULL
)
RETURN GLOBALPKG.RCT1
IS
REFCURSOR GLOBALPKG.RCT1;
BEGIN
OPEN REFCURSOR FOR SELECT
user_id,
COUNT(s_id) as number
FROM t_access_user,
infotest
WHERE user_id = s_creater
AND SUBSTR(s_create_time, 0, 7) = monthnumber.time
GROUP BY user_id
UNION ALL
SELECT
user_id,
0 * NULL as number
FROM t_access_user,
infotest
WHERE NOT user_id = s_creater
AND NOT SUBSTR(s_create_time, 0, 7) = monthnumber.time
GROUP BY user_id
;
RETURN REFCURSOR;END;
/
解决方案 »
- oracle数据库保存时间变成了12小时制的,请教!!!!!在线等待。,。。。!!!!!
- 对查询结果可能为空的表的统计处理
- Oracle企业管理器中关闭数据库需要填主机用户和密码,vista homepremium,请问怎么填?我以管理员登陆,并填了信息,结果说主机密码错误,怎么解决
- Oracle10g删除表格之后出现乱名
- A存储过程调用B存储过程,要得到B存储过程的某个指定输出参数,怎么做?
- 怎么创建oracle11g 的 http server
- 在本地计算机,无法启动OracleXETNSListener服务。
- 怎样在同一台机器中设置双数据库进行实时备份?
- 求SQL语句,高手请进`````
- oracle11g 函数执行与单独执行脚本结果不一致
- Job创建出错,ORA-01008 并非所有变量都已关联,怎么回事?
- 关于自动编号的问题
还有就是请问,我在oracle中如何判断某一个函数是否存在那??
看它的功能可以用游标实现
declare
time varchar2(10);
Cursor cr is select user_id,count(s_id) as number
from t_access_user,infotest
where user_id = s_creater
and left(s_create_time,7) = time
group by all user_id;
begin
....
end;-------------------------------------------
你说的判断是否存在某个函数,可以用如下方法
create or replace function IsExists
(objType in varchar2,
objName in Varchar2,
objValid in int default 1)
return integer is
Result integer;
begin
select Decode(count(*),0,0,1) into Result
from user_objects
where object_type = upper(objType)
and status = decode(objValid,1,'VALID',status)
and object_name = upper(objName);
return (Result);
end IsExists;objectType: 对象类型FUNCTION,INDEX,JAVA CLASS,JAVA RESOURCE,LOB,PACKAGE,
PACKAGE BODY,PROCEDURE,SEQUENCE,TABLE,TYPE,VIEW
objectName: 对象名称
objectValid: 默认为1,表示对象存在并且编译正确才认为存在,
其它值 表示只判断对象存在情况,不考虑是否有编译错误
em: select isExists('function','isExists') from dual;
select isExists('view','all_objects',2) from dual;
你写得创建函数的语句CREATE OR REPLACE FUNCTION monthnumber
(
time IN VARCHAR2 DEFAULT NULL
)
RETURN GLOBALPKG.RCT1
IS
REFCURSOR GLOBALPKG.RCT1;
BEGIN
OPEN REFCURSOR FOR* SELECT
user_id,
COUNT(s_id) as number
FROM t_access_user,
infotest
WHERE user_id = s_creater
AND SUBSTR(s_create_time, 0, 7) = monthnumber.time
GROUP BY user_id
UNION ALL
SELECT
user_id,
0 * NULL as number
FROM t_access_user,
infotest
WHERE NOT user_id = s_creater
AND NOT SUBSTR(s_create_time, 0, 7) = monthnumber.time
GROUP BY user_id
;
RETURN REFCURSOR;END;
在创建时总是报出*的那一行编译过不去,请受累看一下是什么原因?
帮帮小弟呀!
因为你要看的是user_id和COUNT(s_id)!当user_id为null的时候,你想让COUNT(s_id)是什么值呢?
另外!把你的错误提示贴上来,我看看。