下面是我的存储过程,
CREATE OR REPLACE PROCEDURE proc_NewUser(
p_inRoute IN VARCHAR2,
p_inStartDT IN VARCHAR2,
p_inEndDT IN VARCHAR2 ) IS
v_Cursor NUMBER;
v_CreateString VARCHAR2(1000);
v_DropString VARCHAR2(100);
v_NumRows INTEGER;
BEGIN
v_Cursor:=DBMS_SQL.OPEN_CURSOR;
v_DropString:='DROP VIEW vw_DayNewUser';
BEGIN
DBMS_SQL.PARSE(v_Cursor,v_DropString,DBMS_SQL.V7);
v_NumRows:=DBMS_SQL.Execute(v_Cursor);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE!=-942 THEN
RAISE;
END IF;
END;
v_CreateString:='
CREATE VIEW vw_DayNewUser AS
select * from ((select a.stat_date date1,a.YH_ZZC,a.YH_RZC,a.YH_HUODONG,a.YH_RZC/(a.YH_ZZC+a.YH_RZC) Rate,'''
|| p_inRoute || ''' routeNum,b.area_Name
from report_yonghu a,
(select area_code,area_name from codesourcetb group by area_code, area_name) b
where (a.stat_date between ''' || p_inStartDT || ''' and ''' || p_inEndDT || ''')
and (a.areaname=b.area_code)) union
(select ''ºÏ¼Æ'' date1 ,sum(YH_ZZC) ,sum(YH_RZC) YH_RZC,sum(YH_HUODONG) YH_HUODONG,sum(YH_RZC/(YH_ZZC+YH_RZC)) Rate,
'''' routeNum,'''' area_Name
from report_yonghu
where stat_date between ''' || p_inStartDT || ''' and '''|| p_inEndDT ||''')) c
order by c.date1,c.area_name';
DBMS_SQL.PARSE(v_Cursor,v_CreateString,DBMS_SQL.V7);
--当执行到上面一句时就会出异常,跳到下面去,这是怎么回事,请高手指教呀
v_NumRows:=DBMS_SQL.Execute(v_Cursor);
DBMS_SQL.CLOSE_CURSOR(v_Cursor);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(v_Cursor);
--RAISE;
END proc_NewUser;
CREATE OR REPLACE PROCEDURE proc_NewUser(
p_inRoute IN VARCHAR2,
p_inStartDT IN VARCHAR2,
p_inEndDT IN VARCHAR2 ) IS
v_Cursor NUMBER;
v_CreateString VARCHAR2(1000);
v_DropString VARCHAR2(100);
v_NumRows INTEGER;
BEGIN
v_Cursor:=DBMS_SQL.OPEN_CURSOR;
v_DropString:='DROP VIEW vw_DayNewUser';
BEGIN
DBMS_SQL.PARSE(v_Cursor,v_DropString,DBMS_SQL.V7);
v_NumRows:=DBMS_SQL.Execute(v_Cursor);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE!=-942 THEN
RAISE;
END IF;
END;
v_CreateString:='
CREATE VIEW vw_DayNewUser AS
select * from ((select a.stat_date date1,a.YH_ZZC,a.YH_RZC,a.YH_HUODONG,a.YH_RZC/(a.YH_ZZC+a.YH_RZC) Rate,'''
|| p_inRoute || ''' routeNum,b.area_Name
from report_yonghu a,
(select area_code,area_name from codesourcetb group by area_code, area_name) b
where (a.stat_date between ''' || p_inStartDT || ''' and ''' || p_inEndDT || ''')
and (a.areaname=b.area_code)) union
(select ''ºÏ¼Æ'' date1 ,sum(YH_ZZC) ,sum(YH_RZC) YH_RZC,sum(YH_HUODONG) YH_HUODONG,sum(YH_RZC/(YH_ZZC+YH_RZC)) Rate,
'''' routeNum,'''' area_Name
from report_yonghu
where stat_date between ''' || p_inStartDT || ''' and '''|| p_inEndDT ||''')) c
order by c.date1,c.area_name';
DBMS_SQL.PARSE(v_Cursor,v_CreateString,DBMS_SQL.V7);
--当执行到上面一句时就会出异常,跳到下面去,这是怎么回事,请高手指教呀
v_NumRows:=DBMS_SQL.Execute(v_Cursor);
DBMS_SQL.CLOSE_CURSOR(v_Cursor);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(v_Cursor);
--RAISE;
END proc_NewUser;
解决方案 »
- oracle用java语言的一个导包问题 求解?
- plsql块报错
- oracle如何获取一个月前的日期,一年前的日期
- 急!不小心把oracle安装程序卸载了,但还有oradata文件夹中的数据,请问如何恢复!
- oracle 排序 order by 多个条件
- 一个关于数据库审计的问题
- ORA-00923: 未找到预期 FROM 关键字 求解答 谢谢!
- oralce 存储过程中使用变量表名,返回字段的值的问题
- 问一个数据库设计的问题
- update gl_wzbm set substr(3,1)='9' where wzbm like '4919%' 这句话该怎样写?
- AIX 4.3 下如何启动OEM ,如何查看已安装了的oracle产品信息(在线等。。)
- 怎么在存储过程中传入大于32k的ftBlob参数?
(select ''ºÏ¼Æ'' date1 ,sum(YH_ZZC) ,sum(YH_RZC) YH_RZC,sum(YH_HUODONG) YH_HUODONG,sum(YH_RZC/(YH_ZZC+YH_RZC)) Rate,
'''' routeNum,'''' area_Name
from report_yonghu
where stat_date between ''' || p_inStartDT || ''' and '''|| p_inEndDT ||''')) c是不是在 c 前 多了一个 ) HQ.Wang 海清
如果是报权限不够的错,你需要把create view权限直接赋给执行存储过程的用户。
在存储过程中建表视图,通过role赋权限是不行的。
给分了