oracle版本是8.0.5,想在存储过程中创建表用完之后在删除,代码如下CREATE OR REPLACE PACKAGE PKG_rptAmountOverdueComp
AS
type rc_result is ref cursor;
PROCEDURE SP_GetResult(ResultCursor out rc_result);
END PKG_rptAmountOverdueComp;create or replace package body PKG_rptAmountOverdueComp
as
PROCEDURE SP_GetResult(ResultCursor out rc_result)
is
v_num number;
begin
select count(*) into v_num from user_tables where table_name='rptAmountOverdueComp';
if v_num then
drop table rptAmountOverdueComp
end if;
CREATE TABLE rptAmountOverdueComp
(
FunctionID varchar2(20),
FunctionName varchar2(100),
FunctionType varchar2(50),
FunctionGroup varchar2(50),
FunctionTitle varchar2(100),
SQLText varchar2(500),
ReportFileName varchar2(100),
OrderBy varchar2(200),
IsVisible varchar2(1)
); --select * into rptAmountOverdueComp from MainFunctions;
open ResultCursor for
select * from rptAmountOverdueComp;
end;
end PKG_rptAmountOverdueComp;执行后报“无法在调用之前找到程序单元”,
AS
type rc_result is ref cursor;
PROCEDURE SP_GetResult(ResultCursor out rc_result);
END PKG_rptAmountOverdueComp;create or replace package body PKG_rptAmountOverdueComp
as
PROCEDURE SP_GetResult(ResultCursor out rc_result)
is
v_num number;
begin
select count(*) into v_num from user_tables where table_name='rptAmountOverdueComp';
if v_num then
drop table rptAmountOverdueComp
end if;
CREATE TABLE rptAmountOverdueComp
(
FunctionID varchar2(20),
FunctionName varchar2(100),
FunctionType varchar2(50),
FunctionGroup varchar2(50),
FunctionTitle varchar2(100),
SQLText varchar2(500),
ReportFileName varchar2(100),
OrderBy varchar2(200),
IsVisible varchar2(1)
); --select * into rptAmountOverdueComp from MainFunctions;
open ResultCursor for
select * from rptAmountOverdueComp;
end;
end PKG_rptAmountOverdueComp;执行后报“无法在调用之前找到程序单元”,
v_sqlstring:=' CREATE TABLE rptAmountOverdueComp
(
FunctionID varchar2(20),
FunctionName varchar2(100),
FunctionType varchar2(50),
FunctionGroup varchar2(50),
FunctionTitle varchar2(100),
SQLText varchar2(500),
ReportFileName varchar2(100),
OrderBy varchar2(200),
IsVisible varchar2(1)
)';
execute immediate v_sqlstring;
v_sqlstring varchar2(1024);
2 ssql VARCHAR2(1000);
3 begin
4 ssql :='create table TESTTAB
5 (
6 VALUE VARCHAR2(20)
7 )';
8 EXECUTE IMMEDIATE ssql;
9 ssql :='drop table testtab';
10 EXECUTE IMMEDIATE ssql;
11
12 end p_test;
13 /Procedure createdSQL> execute p_test;PL/SQL procedure successfully completed