一个存储过程报错误,帮忙看一下

Question

create or replace procedure proc_kpi_getinfo (
site_cd varchar2,
v_in_begin_ip number,
v_in_end_ip number,
v_in_begin_dt  varchar2,
v_in_end_dt    varchar2
)
as 
begin
        if v_in_begin_dt is null then
           v_begin_dt := to_date('1901-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss');
        else
           v_begin_dt  := to_date(v_in_begin_dt,'yyyy-mm-dd hh24:mi:ss');
        end if;
        if v_in_end_dt is null then
           v_end_dt := to_date('2999-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss');
        else
           v_end_dt    := to_date(v_in_end_dt,'yyyy-mm-dd hh24:mi:ss');
        end if;
        if v_in_begin_ip is null then
           v_begin_ip := ' ';
        else
           v_begin_ip := v_in_begin_ip;
        end if;
        if v_in_end_ip is null then
           v_end_ip := 'zzzzzzzzzzzzzzzzzzzz';
        else
           v_end_ip   := v_in_end_ip;
        end if;
        if site_cd='GE' then 
           insert into  report.tmp_kpi_info
           select logintime,loginname,server_id,null
           from dstage.ge_gametime_his;
           where (ip_value between v_begin_ip and v_end_ip)
           and (logintime between v_in_begin_dt  and v_in_end_dt) ;
        end if;
        if site_cd='SUN' then
           insert into  report.tmp_kpi_info
           select logintime,loginname,server_id,SERVERGUID
           from dstage.sun_gametime_his;
           where (ip_value between v_begin_ip and v_end_ip)
           and (logintime between v_in_begin_dt  and v_in_end_dt) ;
        end if;
end ; 
这个sql语句报着个错误,帮忙看看Procedure created with compilation errors.

Answer 1

两个from from dstage.ge_gametime_his; 去掉 分号。

Answer 2

改成如下：
CREATE OR REPLACE PROCEDURE PROC_KPI_GETINFO(SITE_CD       VARCHAR2,
                                             V_IN_BEGIN_IP NUMBER,
                                             V_IN_END_IP   NUMBER,
                                             V_IN_BEGIN_DT VARCHAR2,
                                             V_IN_END_DT   VARCHAR2) AS
V_BEGIN_DT DATE;  
V_END_DT DATE;   
V_BEGIN_IP VARCHAR2(30);                                        
V_end_IP VARCHAR2(30);
BEGIN
  IF V_IN_BEGIN_DT IS NULL THEN
    V_BEGIN_DT := TO_DATE('1901-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
  ELSE
    V_BEGIN_DT := TO_DATE(V_IN_BEGIN_DT, 'yyyy-mm-dd hh24:mi:ss');
  END IF;
  IF V_IN_END_DT IS NULL THEN
    V_END_DT := TO_DATE('2999-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
  ELSE
    V_END_DT := TO_DATE(V_IN_END_DT, 'yyyy-mm-dd hh24:mi:ss');
  END IF;
  IF V_IN_BEGIN_IP IS NULL THEN
    V_BEGIN_IP := ' ';
  ELSE
    V_BEGIN_IP := V_IN_BEGIN_IP;
  END IF;
  IF V_IN_END_IP IS NULL THEN
    V_END_IP := 'zzzzzzzzzzzzzzzzzzzz';
  ELSE
    V_END_IP := V_IN_END_IP;
  END IF;
  IF SITE_CD = 'GE' THEN
    INSERT INTO REPORT.TMP_KPI_INFO
      SELECT LOGINTIME, LOGINNAME, SERVER_ID, NULL
        FROM DSTAGE.GE_GAMETIME_HIS
       WHERE (IP_VALUE BETWEEN V_BEGIN_IP AND V_END_IP)
         AND (LOGINTIME BETWEEN V_IN_BEGIN_DT AND V_IN_END_DT);
  END IF;
  IF SITE_CD = 'SUN' THEN
    INSERT INTO REPORT.TMP_KPI_INFO
      SELECT LOGINTIME, LOGINNAME, SERVER_ID, SERVERGUID
        FROM DSTAGE.SUN_GAMETIME_HIS
       WHERE (IP_VALUE BETWEEN V_BEGIN_IP AND V_END_IP)
         AND (LOGINTIME BETWEEN V_IN_BEGIN_DT AND V_IN_END_DT);
  END IF;
END;
/

Answer 3

show err 看下提示错误！

Answer 4

变量写出错了呀Error: PLS-00201: 必须说明标识符 'V_BEGIN_DT'
Line: 8
Text: v_begin_dt := to_date('1901-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');Error: PL/SQL: Statement ignored
Line: 8
Text: v_begin_dt := to_date('1901-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');Error: PLS-00201: 必须说明标识符 'V_BEGIN_DT'
Line: 10
Text: v_begin_dt := to_date(v_in_begin_dt, 'yyyy-mm-dd hh24:mi:ss');Error: PL/SQL: Statement ignored
Line: 10
Text: v_begin_dt := to_date(v_in_begin_dt, 'yyyy-mm-dd hh24:mi:ss');Error: PLS-00201: 必须说明标识符 'V_END_DT'
Line: 13
Text: v_end_dt := to_date('2999-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');Error: PL/SQL: Statement ignored
Line: 13
Text: v_end_dt := to_date('2999-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');Error: PLS-00201: 必须说明标识符 'V_END_DT'
Line: 15
Text: v_end_dt := to_date(v_in_end_dt, 'yyyy-mm-dd hh24:mi:ss');Error: PL/SQL: Statement ignored
Line: 15
Text: v_end_dt := to_date(v_in_end_dt, 'yyyy-mm-dd hh24:mi:ss');Error: PLS-00201: 必须说明标识符 'V_BEGIN_IP'
Line: 18
Text: v_begin_ip := ' ';Error: PL/SQL: Statement ignored
Line: 18
Text: v_begin_ip := ' ';Error: PLS-00201: 必须说明标识符 'V_BEGIN_IP'
Line: 20
Text: v_begin_ip := v_in_begin_ip;Error: PL/SQL: Statement ignored
Line: 20
Text: v_begin_ip := v_in_begin_ip;Error: PLS-00201: 必须说明标识符 'V_END_IP'
Line: 23
Text: v_end_ip := 'zzzzzzzzzzzzzzzzzzzz';Error: PL/SQL: Statement ignored
Line: 23
Text: v_end_ip := 'zzzzzzzzzzzzzzzzzzzz';Error: PLS-00201: 必须说明标识符 'V_END_IP'
Line: 25
Text: v_end_ip := v_in_end_ip;Error: PL/SQL: Statement ignored
Line: 25
Text: v_end_ip := v_in_end_ip;

Answer 5

第一眼，v_begin_dt  这个变量在哪呢

Answer 6

1.以下四个变量没有定义;
V_BEGIN_DT DATE;  
V_END_DT DATE;   
V_BEGIN_IP VARCHAR2(30);                                        
V_end_IP VARCHAR2(30);
2.
        if site_cd='GE' then 
           insert into  report.tmp_kpi_info
           select logintime,loginname,server_id,null
           from dstage.ge_gametime_his;
           where (ip_value between v_begin_ip and v_end_ip)
           and (logintime between v_in_begin_dt  and v_in_end_dt) ;
        end if;
        if site_cd='SUN' then
           insert into  report.tmp_kpi_info
           select logintime,loginname,server_id,SERVERGUID
           from dstage.sun_gametime_his;
           where (ip_value between v_begin_ip and v_end_ip)
           and (logintime between v_in_begin_dt  and v_in_end_dt) ;
        end if;
这两个语句的ＦＲＯＭ　后面都多带了分号；要把分号去掉;

Answer 7

都没有声明变量v_begin_dt,v_end_dt,v_begin_ip,v_end_ip

Answer 8

这个SQL语句里的变量没有定义. 
红色那块,不应该在最后还有一个分号.
这样的问题,都应该有提示,你自己细心的查查都能找到.
insert into  report.tmp_kpi_info
           select logintime,loginname,server_id,SERVERGUID
           from dstage.sun_gametime_his;
           where (ip_value between v_begin_ip and v_end_ip)
           and (logintime between v_in_begin_dt  and v_in_end_dt)