我的第一个oracle存储过程，麻烦大家帮帮忙谢谢拉

Question

基本过程是这样的
create or replace procedure sp_test
As
begin
   execute immediate 'create  Global temporary Table t1(ip Varchar2(10),Time Date)
   ON COMMIT DELETE ROWS';
   execute immediate 'Select ip_addr,Max(indb_time) a Into t1 From t2 Group By ip_addr';
    
     update t3
      Set t3.ctime=t1.time
      where t3.ip_addr=t1.ip;
    commit;
  
  exception
   when others then
   DBMS_OUTPUT.PUT_LINE(to_char(sqlerrm));end;就是想实现从t2中把ip和max(time)取出来，然后根据对应的ip分别把值插入t3中，
但是调试老出错，麻烦大家帮忙指点一下，谢谢

Answer 1

把
execute immediate 'Select ip_addr,Max(indb_time) a Into t1 From t2 Group By ip_addr';
改成
execute immediate 'insert into t1 Select ip_addr,Max(indb_time) From t2 Group By ip_addr';

Answer 2

又改了一下，在Oracle 9i下通过：
create or replace procedure sp_test
As
begin
   execute immediate 'create  Global temporary Table t1(ip Varchar2(10),Time Date)
   ON COMMIT DELETE ROWS';
   execute immediate 'Select ip_addr,Max(indb_time) a Into t1 From t2 Group By ip_addr';   EXECUTE immediate 'update t3 Set t3.ctime=t1.time where t3.ip_addr=t1.ip';
    commit;  exception
   when others then
   DBMS_OUTPUT.PUT_LINE(to_char(sqlerrm));
end;

Answer 3

编译成功。感谢海原。为什么也要加 EXECUTE immediate这个啊？而且我执行后如果去SQL窗口查询t3的话，表应该被update过了才是啊？

Answer 4

为什么也要加 EXECUTE immediate这个啊？
存储过程里面执行动态sql必须这样写的

Answer 5

刚才没有注意，你的语句中是有错误的，我重新测试了一下，新过程如下：
create or replace procedure sp_test
As
  v_num number;
BEGIN
  SELECT count(*) into v_num from user_tables where table_name='T_TEMP';
  if v_num<1 then
   execute immediate 'create  Global temporary Table t1(ip Varchar2(10),Time Date) ON COMMIT delete ROWS';
  END IF;
  execute immediate 'insert into t1 Select ip_addr,Max(indb_time) From t2 Group By ip_addr';  EXECUTE immediate 'update t3 Set t3.ctime=(select t1.time from t1 where t3.ip_addr=t1.ip)';
  commit;
end;

Answer 6

你原来的过程中，错在以下几个地方：
1、oracle中，向某表insert插入select的语法是：
　　insert into t1 select field_list from t2;
　　在SQL Server中的语法是：
　　select field_list into t1 from t2
2、Oracle中，从某表或子查询Update数据的语法是：
    update table1 set (field1,field2...)=(select expr1,expr2... from table2 where table1.id_field=table2.id_field)
　　跟SQL　Server中是大不一样的；
3、错误处理中，最好不要使用：
　　DBMS_OUTPUT.PUT_LINE(to_char(sqlerrm));
　　它可能不会把错误给显示出来，结果就成了运行时不报错，但是执行结果不正确的情况。

Answer 7

wawxj(海原):  刚接触过程，还不是很能理解，能帮我讲一下为什么这样写么？谢谢

Answer 8

为什么要定义v_num  这个变量啊？？

Answer 9

create or replace procedure sp_lin_test
As
  v_num number;
BEGIN
  SELECT count(*) into v_num from user_tables where table_name='T_TEMP';
  if v_num<1 then
   execute immediate 'create  Global temporary Table t1(ip Varchar2(10),Time Date) ON COMMIT delete ROWS';
  END IF;
  execute immediate 'insert into t1 Select ip_addr,Max(indb_time) From t2 Group By ip_addr';  EXECUTE immediate 'update t3 Set t3.ctime=(select t1.time from t1 where t3.ip_addr=t1.ip)';
  commit;
end;我把刚才的过程修改成这样重新编译，但是最终t3还是没有被更新.

Answer 10

create or replace procedure sp_test
As
  --v_num:判断是否已经有了临时表（若无，则建立，否则不处理）
  v_num number;
BEGIN
  --判断临时表是否存在（昨天把表名写错了）
  SELECT count(*) into v_num from user_tables where table_name='T1';
  if v_num<1 then--系统中没有该临时表，需要建立
   execute immediate 'create  Global temporary Table t1(ip Varchar2(10),Time Date) ON COMMIT delete ROWS';
  END IF;
  --从t2表提取数据到临时表
  execute immediate 'insert into t1 Select ip_addr,Max(indb_time) From t2 Group By ip_addr';
  --更新t3表
  EXECUTE immediate 'update t3 Set t3.ctime=(select t1.time from t1 where t3.ip_addr=t1.ip)';
  commit;
end;

Answer 11

你试试直接用一个SQL来更新t3表：
update t3 set ctime=(select a.sj from (ip_addr,max(indb_time) as sj from t2 group by ip_addr) a where t3.ip_addr=a.ip_addr)

Answer 12

就一个update的sql我已经试过了。。是可以更新的奇怪啊。。 过程执行了之后还是没有更新。。 这是为什么？？？天、、、

Answer 13

Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0 
Connected as ipnms
SQL> 
SQL> create or replace procedure sp_lin_test2
  2  As
  3       --v_num:判断是否已经有了临时表（若无，则建立，否则不处理）
  4    v_num number;
  5  BEGIN
  6         --判断临时表是否存在
  7    SELECT count(*) into v_num from user_tables where table_name='t1';
  8    if v_num<1 then --系统中没有该临时表，需要建立
  9     execute immediate 'create  Global temporary Table t1(ip Varchar2(10),Time Date) ON COMMIT delete ROWS';
 10    END IF;
 11    --从iptpc_host_cpu表提取数据到临时表
 12    execute immediate 'insert into t1 Select ip_addr,Max(indb_time) From iptpc_host_cpu Group By ip_addr';
 13    --更新wh_info表
 14    EXECUTE immediate 'update wh_info Set wh_Info.w_cpu_time=(select t1.time from t1 where wh_info.ip_addr=t1.ip)';
 15    commit;
 16  end;
 17  /Procedure createdSQL> exec sp_lin_test2;begin sp_lin_test2; end;ORA-00947: not enough values
ORA-06512: at "IPNMS.SP_LIN_TEST2", line 12
ORA-06512: at line 1SQL> 你看是这样是不是哪里有错啊？

Answer 14

你在sql plus中执行一下：
desc t1
可能原来已经有t1，但是结构与你想要的（ip Varchar2(10),Time Date）不一样。

Answer 15

另外，第7行中：
SELECT count(*) into v_num from user_tables where table_name='t1';
应该是：
SELECT count(*) into v_num from user_tables where table_name='T1';
即：表名需要大写，或者是用lower(table_name)='t1'

Answer 16

你好。执行结果是这样的真的已经存在了啊倒。。
SQL> desc t1;
Name          Type         Nullable Default Comments 
------------- ------------ -------- ------- -------- 
INT_ID        NUMBER(15)   Y                         
IP_ADDR       VARCHAR2(15) Y                         
ORG_TIME      DATE         Y                         
COL_TIME      DATE         Y                         
INDB_TIME     DATE         Y                         
CPU_ID        VARCHAR2(32) Y                         
CPU_IDLE_UTIL NUMBER(6,2)  Y                         
CPU_SYS_UTIL  NUMBER(6,2)  Y                         
CPU_USR_UTIL  NUMBER(6,2)  Y                         
CPU_WAIT_UTIL NUMBER(6,2)  Y                         
NUM_RUNNING   NUMBER(15)   Y                         SQL>

Answer 17

晕死！
insert into table1 select field1,field2...fieldn from table2
这种格式的语句要求：
table1的字段顺序（包括类型、数量）要与子查询中的一致或者兼容，你可以试试把t1先drop后再执行过程看看。

Answer 18

SQL> 
SQL> create or replace procedure sp_lin_test2
  2  As
  3       --v_num:判断是否已经有了临时表（若无，则建立，否则不处理）
  4    v_num number;
  5  BEGIN
  6         --判断临时表是否存在
  7    SELECT count(*) into v_num from user_tables where table_name='LIN_T1';
  8    if v_num<1 then --系统中没有该临时表，需要建立
  9     execute immediate 'create  Global temporary Table lin_t1(ip Varchar2(15),Time Date) ON COMMIT delete ROWS';
 10    END IF;
 11    --从iptpc_host_cpu表提取数据到临时表
 12    execute immediate 'insert into lin_t1 Select ip_addr,Max(indb_time) From iptpc_host_cpu Group By ip_addr';
 13    --更新wh_info表
 14    EXECUTE immediate 'update wh_info Set wh_Info.w_cpu_time=(select lin_t1.time from lin_t1 where wh_info.ip_addr=lin_t1.ip)';
 15    commit;
 16  end;
 17  /Procedure createdSQL> exec sp_lin_test2;begin sp_lin_test2; end;ORA-01031: insufficient privileges
ORA-06512: at "IPNMS.SP_LIN_TEST2", line 9
ORA-06512: at line 1SQL> desc LIN_T1;
Object LIN_T1 does not exist.SQL>我没有把T1drop掉，重新命名了临时表。类型数量完全匹配...现在变成了这样滴。

Answer 19

是你建立sp_lin_test2过程的Oracle帐户权限不够，用system帐户连接后，将“create any table”权限授予该帐户即可。语法如下：
grant create any table to 建立过程的帐户名称