存储过程问题，看看，有什么地方写的不对。谢谢各位。。。

Question

第一次用oracle写存储过程。表和存储过程如下：-- 创建表A
CREATE TABLE testA (
f_id     numeric    NOT NULL,
col1     varchar2(20)     NULL, 
         col2     varchar2(40)     NULL
  );
insert into testA(f_id, col1, col2) values(1, 'QQQQQ', 'iw4424');
insert into testA(f_id, col1, col2) values(1, 'EEEEE', 'iieirw');
insert into testA(f_id, col1, col2) values(1, 'WWWWW', 'kjsier');-- 创建表B
  CREATE TABLE testB (
f_id     numeric    NOT NULL,
col1     varchar2(20)     NULL, 
         col2     varchar2(40)     NULL
  );
insert into testB(f_id, col1, col2) values(1, 'BBBB1', 'CCCC1');
insert into testB(f_id, col1, col2) values(1, 'BBBB2', 'CCCC2');  
-- 创建存储过程
create or replace procedure ora.up_getdetail(currentid in numeric) as
begin
    -- 创建表testA的序列
    create sequence seq_testA increment by 1 start with 1;      
    -- 创建表testB的序列
    create sequence seq_testB increment by 1 start with 1;  
    -- 创建存放表testA的临时表tempA
    CREATE TABLE tempA (
         f_id     numeric    NOT NULL,
         col1     varchar2(20)     NULL, 
           col2     varchar2(40)     NULL,
           new_id   numeric    NOT NULL
    );    -- 创建存放表testB的临时表tempB
    CREATE TABLE tempB (
         f_id     numeric    NOT NULL,
         col1     varchar2(20)     NULL, 
           col2     varchar2(40)     NULL,
           new_id   numeric    NOT NULL
    );
    
    insert into tempA select testA.f_id, testA.col1, testA.col2, seq_testA.nextval from testA where f_id := currentid;
    insert into tempB select testB.f_id, testB.col1, testB.col2, seq_testB.nextval from testB where f_id := currentid;
    
    select nvl(tempA.f_id, tempB.f_id) as f_id, tempA.col1 as COL1A, tempA.col2 as COL2A, tempB.col1 as COL1B, tempB.col2 as COL2B from tempA, tempB where tempA.new_id = tempB.new_id(+);        
    
    DROP sequence seq_testA;
    DROP sequence seq_testB;
    
    DROP table tempA;
    DROP table tempB;
end;
我直接执行: exec up_getdetail(1);时，提示：SQL> exec up_getdetail(1);begin up_getdetail(1); end;ORA-06550: 第 1 行, 第 7 列: 
PLS-00905: 对象 ORA.UP_GETDETAIL 无效
ORA-06550: 第 1 行, 第 7 列: 
PL/SQL: Statement ignored请问是什么地方出问题呐???

Answer 1

在过程里不可以直接创建序列和表
如果要创建需要这样
v_sql varchar2(100);
begin
v_sql := 'create table a (a varchar2(2),b number)';
execute immediate v_sql;

Answer 2

oracle中的临时表应该这样使用,你的用到的序列可以用rownum替代
　　execute immediate 'CREATE GLOBAL TEMPORARY TABLE T_TEMP (
　　COL1 VARCHAR2(10),
　　COL2 VARCHAR2(10)
　　) ON COMMIT delete ROWS';--
select rownum,t.* from t

Answer 3

存储过程改成这样了，还是不行呢create or replace procedure up_getdetail(currentid in numeric) as
begin
    -- 创建存放表testA的临时表tempA
    execute immediate 'CREATE TABLE tempA(f_id numeric NOT NULL, col1 varchar2(20) NULL, col2 varchar2(40) NULL, new_id numeric NOT NULL);';    -- 创建存放表testB的临时表tempB
    execute immediate 'CREATE TABLE tempB(f_id numeric NOT NULL, col1 varchar2(20) NULL, col2 varchar2(40) NULL, new_id numeric NOT NULL);';
    
    insert into tempA select testA.f_id, testA.col1, testA.col2, rownum from testA where f_id := currentid;
    insert into tempB select testB.f_id, testB.col1, testB.col2, rownum from testB where f_id := currentid;
    
    select nvl(tempA.f_id, tempB.f_id) as f_id, tempA.col1 as COL1A, tempA.col2 as COL2A, tempB.col1 as COL1B, tempB.col2 as COL2B from tempA, tempB where tempA.new_id = tempB.new_id(+); 
    
    DROP table tempA;
    DROP table tempB;
end;

Answer 4

execute immediate 'CREATE TABLE tempA(f_id numeric NOT NULL, col1 varchar2(20) NULL, col2 varchar2(40) NULL, new_id numeric NOT NULL)';
引号里边多了分号，还有DROP也需要这样写

Answer 5

参数类型numberic
改成number

Answer 6

好像numeric类型无所谓吧
我又改了点。。最后一个select语句还是出问题create or replace procedure ora.up_getdetail(currentid in numeric) as
begin
    -- 创建存放表testA的临时表tempA
    -- drop table tempA;
    execute immediate 'CREATE TABLE tempA(f_id number NOT NULL, col1 varchar2(20) NULL, col2 varchar2(40) NULL, new_id number NOT NULL)';
   
    -- 创建存放表testB的临时表tempB
    -- drop table tempB;
    execute immediate 'CREATE TABLE tempB(f_id number NOT NULL, col1 varchar2(20) NULL, col2 varchar2(40) NULL, new_id number NOT NULL)';
    -- commit;
    
    insert into tempA (f_id, col1, col2, new_id) select testA.f_id, testA.col1, testA.col2, rownum from testA where testA.f_id = currentid;
    insert into tempB (f_id, col1, col2, new_id) select testB.f_id, testB.col1, testB.col2, rownum from testB where testB.f_id = currentid;
    
    select nvl(tempA.f_id, tempB.f_id) f_id, tempA.col1 COL1A, tempA.col2 COL2A, tempB.col1 COL1B, tempB.col2 COL2B from tempA, tempB where tempA.new_id = tempB.new_id(+);            execute immediate 'DROP table tempA';
    execute immediate 'DROP table tempB';
end;

Answer 7

procedure中不能直接写dml或ddl语句
后面的select也要用execute immediate处理