我是这样做的:
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR; PROCEDURE get ( p_rc OUT myrctype);
END pkg_test;
/
CREATE OR REPLACE PACKAGE BODY pkg_test
IS
PROCEDURE get ( p_rc OUT myrctype)
IS
BEGIN
open p_rc for select * from addauto;
END get;
END pkg_test;
/
以上没有问题。可是我怎样在jsp页面中使用这个存储过程,并将结果显示在页面呢?
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR; PROCEDURE get ( p_rc OUT myrctype);
END pkg_test;
/
CREATE OR REPLACE PACKAGE BODY pkg_test
IS
PROCEDURE get ( p_rc OUT myrctype)
IS
BEGIN
open p_rc for select * from addauto;
END get;
END pkg_test;
/
以上没有问题。可是我怎样在jsp页面中使用这个存储过程,并将结果显示在页面呢?
解决方案 »
- ora-12560 协议适配器错误
- 表或视图不存在
- 关于oracle安装后运行的问题
- ALL和IN是不是意思一样啊?
- 关于物化视图的快速刷新问题
- 请问如何把“用PowerDesigner建好的数据模型结构”导入到oracle92中?请高手指教!
- conn.open()时发生例外:ORA-00162: external dbid length 17 is greater than maximum (16) 什么原因?
- 急急 ,数据库没有归档日志,请问如何恢复一个表的数据(表的内容发生了变化)
- Oracle 816 表空间问题! ORCL使用User表空间,在SQL plus中不能创建新表!提示空间不够!
- 怎么把oracle里面建好的表变成sql语句啊
- 执行一条SQL语句后,想获取该语句影响了几行数据。
- 我想安装Oracle9i在我的计算机上,为什么安装时提示 areasqueries 错误?
TYPE MyCursor IS REF CURSOR; --my cursor
queryDayCur MyCursor; --my cursor object
--TYPE MyDataRow IS TABLE OF myCr1%ROWTYPE;
strTabName VARCHAR2(20 CHAR);
strDate VARCHAR2(8 CHAR); --date of totaling.
strDayDisp CHAR(5); --day of the month in DB newly created.
strPartition1 CHAR(5); --day of a partition.
strPartition2 CHAR(5);
strPartition3 CHAR(5);
strPartition4 CHAR(5);
strPartition5 CHAR(5);
rec_Hour CHAR(2);
rec_CntID VARCHAR2(1024 CHAR);
rec_CntName VARCHAR2(260 CHAR);
rec_PubCD CHAR(32 CHAR);
rec_AvgTm NUMBER(8,2);
rec_Cnt NUMBER(8);
i PLS_INTEGER;
j PLS_INTEGER;
n PLS_INTEGER;
nDays PLS_INTEGER;
strSql VARCHAR2(32767 CHAR); --sql clause
data_exception EXCEPTION; --exception obj
BEGIN--test if table is already existing
IF intAggItem = 0 THEN
strTabName := 'CNTDAY_' || strMonth;
ELSIF intAggItem = 1 THEN
strTabName := 'USERDAY_' || strMonth;
ELSE
RAISE data_exception;
END IF; strSql := 'select count(*) from user_tables where UPPER(table_name) = ''' || strTabName || '''';
EXECUTE IMMEDIATE strSql INTO i; --debug
--print('SQL:' || strSql);
--print('TABLE EXIST FLAG: ' || i);
--debug endIF i = 1 THEN -- drop table
EXECUTE IMMEDIATE 'DROP TABLE ' || strTabName;
--print ('table ' || strTabName || ' dropped.');
END IF;--Get days
nDays := EXTRACT(DAY FROM LAST_DAY(TO_DATE(strMonth || '01')));strPartition1 := SUBSTR(strMonth, 5, 2) || '/' || '05';
strPartition2 := SUBSTR(strMonth, 5, 2) || '/' || '10';
strPartition3 := SUBSTR(strMonth, 5, 2) || '/' || '15';
strPartition4 := SUBSTR(strMonth, 5, 2) || '/' || '20';
strPartition5 := SUBSTR(strMonth, 5, 2) || '/' || '25';IF intAggItem = 0 THEN --Content
strSql := 'CREATE TABLE ' || strTabName || '(RID NUMBER PRIMARY KEY, DAY CHAR(5), CNT_PATH VARCHAR2(260), CNT_ID VARCHAR2(1024), ';
ELSE --User
strSql := 'CREATE TABLE ' || strTabName || '(RID NUMBER PRIMARY KEY, DAY CHAR(5), PUB_CD CHAR(32), ';
END IF;
strSql := strSql || 'T_AVT NUMBER, T_TNA NUMBER';i := 0;
LOOP
EXIT WHEN i > 23;
strSql := strSql || ', AVT' || i || ' NUMBER ' || ', TNA' || i || ' NUMBER ';
i := i + 1;
END LOOP;
/*
IF intAggItem = 0 THEN --Content
strSql := strSql || ', CONSTRAINT PK_CONTENT_DAY PRIMARY KEY (RID))';
ELSE --User
strSql := strSql || ', CONSTRAINT PK_USER_DAY PRIMARY KEY (RID))';
END IF;
*/
strSql := strSql || ')';
strSql := strSql || ' PARTITION BY RANGE (DAY)';
strSql := strSql || ' (PARTITION Partition_1 VALUES LESS THAN (''' || strPartition1 || '''),';
strSql := strSql || ' PARTITION Partition_2 VALUES LESS THAN (''' || strPartition2 || '''),';
strSql := strSql || ' PARTITION Partition_3 VALUES LESS THAN (''' || strPartition3 || '''),';
strSql := strSql || ' PARTITION Partition_4 VALUES LESS THAN (''' || strPartition4 || '''),';
strSql := strSql || ' PARTITION Partition_5 VALUES LESS THAN (''' || strPartition5 || '''),';
strSql := strSql || ' PARTITION Partition_Default VALUES LESS THAN (MAXVALUE))';
EXECUTE IMMEDIATE strSql;
print('Create table ' || strTabName);--Create indexs for table
EXECUTE IMMEDIATE 'CREATE INDEX VODMSS.IDX_' ||strTabName || '_DAY' || ' ON VODMSS.' || strTabName || '(DAY)';
IF intAggItem = 0 THEN --Content
EXECUTE IMMEDIATE 'CREATE INDEX VODMSS.IDX_' ||strTabName || '_CNTID' || ' ON VODMSS.' || strTabName || '(CNT_ID)';
EXECUTE IMMEDIATE 'CREATE INDEX VODMSS.IDX_' ||strTabName || '_CNTPATH' || ' ON VODMSS.' || strTabName || '(CNT_PATH)';
ELSE --User
EXECUTE IMMEDIATE 'CREATE INDEX VODMSS.IDX_' ||strTabName || '_PUBCD' || ' ON VODMSS.' || strTabName || '(PUB_CD)';
END IF; n := 1; -- number of record counts.
j := 1; -- days of the month.
LOOP
EXIT WHEN j > nDays; --all the month is handled.
strDate := '';
IF j < 10 THEN
strDate := strMonth || '0' || j;
ELSE
strDate := strMonth || j;
END IF; strDayDisp := SUBSTR(strDate, 5, 2) || '/' || SUBSTR(strDate, 7, 2);
j := j + 1;
--query SQL sentence
IF intAggItem = 0 THEN --Content
strSql := 'SELECT SUBSTR(STR_TM, 1, 2) AS SHOUR, CNT_PATH, CNT_ID, AVG(VIW_TIM), COUNT(CNT_ID) AS TTLCNT ';
ELSE --User
strSql := 'SELECT SUBSTR(STR_TM, 1, 2) AS SHOUR, PUB_CD, AVG(VIW_TIM), COUNT(*) AS TTLCNT ';
END IF;
strSql := strSql || 'FROM TRN_FOR_BILL WHERE STR_DT = ''' || strDate || ''' ';
strSql := strSql || 'AND CNT_ID IS NOT NULL AND CNT_PATH IS NOT NULL AND VIW_TIM > 0 ';
strSql := strSql || 'AND PUB_CD IS NOT NULL AND LOG_STS = ''0000'' ';
IF intAggItem = 0 THEN --Content
strSql := strSql || 'GROUP BY CUBE((CNT_PATH, CNT_ID), SUBSTR(STR_TM, 1, 2)) ';
strSql := strSql || 'ORDER BY CNT_PATH NULLS LAST, CNT_ID NULLS LAST, SHOUR NULLS FIRST';
ElSE --User
strSql := strSql || 'GROUP BY CUBE(PUB_CD, SUBSTR(STR_TM, 1, 2)) ';
strSql := strSql || 'ORDER BY PUB_CD NULLS LAST, SHOUR NULLS FIRST';
END IF; OPEN queryDayCur FOR strSql; IF intAggItem = 0 THEN --Content
FETCH queryDayCur INTO rec_Hour, rec_CntName, rec_CntID, rec_AvgTm,rec_Cnt;
ELSE --User
FETCH queryDayCur INTO rec_Hour, rec_PubCD, rec_AvgTm,rec_Cnt;
END IF;
--n := 1;
LOOP
EXIT WHEN queryDayCur%NOTFOUND; strSql := 'INSERT INTO ' || strTabName || ' VALUES (' || n || ',''' || strDayDisp || '''';
n := n + 1; IF intAggItem = 0 THEN --Content
strSql := strSql || ',''' || TRIM(rec_CntName) || ''', ''' || TRIM(rec_CntID) || ''',' || rec_AvgTm || ',' || rec_Cnt;
ELSIF intAggItem = 1 THEN --User
strSql := strSql || ',''' || TRIM(rec_PubCD) || ''',' || rec_AvgTm || ',' || rec_Cnt;
END IF; IF intAggItem = 0 THEN --Content
FETCH queryDayCur INTO rec_Hour, rec_CntName, rec_CntID, rec_AvgTm,rec_Cnt;
ELSE --User
FETCH queryDayCur INTO rec_Hour, rec_PubCD, rec_AvgTm,rec_Cnt;
END IF; i := 0;
LOOP
EXIT WHEN i > 23; -- For every day
IF queryDayCur%NOTFOUND Or (rec_Hour IS NULL) THEN
strSql := strSql || ', 0, 0';
ELSIF TO_NUMBER(rec_Hour) <> i THEN
strSql := strSql || ', 0, 0';
ELSE
strSql := strSql || ',' || rec_AvgTm || ',' || rec_Cnt; -- has value
IF intAggItem = 0 THEN --Content
FETCH queryDayCur INTO rec_Hour, rec_CntName, rec_CntID, rec_AvgTm,rec_Cnt;
ELSE --User
FETCH queryDayCur INTO rec_Hour, rec_PubCD, rec_AvgTm,rec_Cnt;
END IF;
END IF;
i := i + 1; END LOOP; /* skip the invalid value */
LOOP
EXIT WHEN queryDayCur%NOTFOUND OR (rec_Hour IS NULL);
IF intAggItem = 0 THEN --Content
FETCH queryDayCur INTO rec_Hour, rec_CntName, rec_CntID, rec_AvgTm,rec_Cnt;
ELSE --User
FETCH queryDayCur INTO rec_Hour, rec_PubCD, rec_AvgTm,rec_Cnt;
END IF;
END LOOP;
EXECUTE IMMEDIATE strSql || ')'; END LOOP;
END LOOP; COMMIT; --commit
--print ('Commited. ' || TO_CHAR(n - 1) || ' Records Inserted into table ' || strTabName ); CLOSE queryDayCur;EXCEPTION
WHEN data_exception THEN
print('Unknow type!');
WHEN OTHERS THEN
print('System error!');
CLOSE queryDayCur;
ROLLBACK;END;