我的Oracle语法有什么问题？

Question

我有一个SQL存储过程，我需要转换为Oracle。我有一些语法问题。

我当前遇到的错误是：

错误（75,1）：PLS-00103：在预期以下情况之一时遇到符号“DROP”：（开始情况声明结束异常退出，如果循环mod null pragma raise return选择更新

我想我还有很多。有人能帮我找出我做错了什么吗？

以下是我所拥有的：

create or replace PROCEDURE "PROCEDURE1" 
(
  IN_FEATURENAME IN NVARCHAR2 
, OUT_O_RC OUT SYS_REFCURSOR 
) AS


BEGIN
execute immediate 'CREATE TABLE t_name_match( ' || 
'KBID       int     NOT NULL, ' || 
'SYMBOLID   int     NOT NULL,' || 
'FEATURE_NAME   varchar(30) NULL';

execute immediate 'CREATE TABLE t_compat_match( ' || 
'KBID       int     NOT NULL, ' || 
'SYMBOLID   int     NOT NULL,' || 
'FEATURE_NAME   varchar(30) NULL';
/*******************************************************************************    ***
** Name matches
    ********************************************************************************    ***/
/*
** Load matches to Name Table
*/
INSERT INTO t_name_match (KBID, SYMBOLID, FEATURE_NAME)
SELECT  KBID, SYMBOLID, FEATURENAME
  from  FEATURE_MASTER
 where  FEATURENAME like IN_FEATURENAME;

/*******************************************************************************    ***
** Compatibility Statement matches
********************************************************************************    ***/
/*
** Load matches to Compatibility table
*/
 INSERT INTO t_compat_match (KBID, CHARID, SYMBOLID)
SELECT  KBID, CHARID, SYMBOLID
  from  FEATURE_COMPAT
 where  INSTR(IN_FEATURENAME, LINE) > 0;

INSERT INTO t_name_match (KBID, SYMBOLID, FEATURE_NAME)
 SELECT cm.KBID, cm.SYMBOLID, NULL
   from (SELECT DISTINCT KBID, SYMBOLID
        from t_compat_match)    cm
  where NOT EXISTS  (SELECT 'X'
               from t_name_match nm
              where nm.KBID     = cm.KBID
                and nm.SYMBOLID = cm.SYMBOLID);


UPDATE  t_name_match

  SET FEATURE_NAME = (SELECT FEATURENAME
  from  FEATURE_MASTER

  where nm.FEATURE_NAME IS NULL
   and  fm.KBID     = nm.KBID
    and fm.SYMBOLID = nm.SYMBOLID);

 OPEN OUT_O_RC FOR
/*******************************************************************************    ***
** Return result set
********************************************************************************     ***/
SELECT  DISTINCT m.MODELNAME, m.VERSION, m.PLANTID, m.FROMDATE, m.TODATE,
     m.BUILD, m.KBID, nm.SYMBOLID, nm.FEATURE_NAME as FEATURENAME
  FROM  t_name_match        nm,
     KB_MASTER      m
  WHERE     m.KBID = nm.KBID 
    AND     m.MODELGROUP    = 'F'
 ORDER BY m.MODELNAME, m.VERSION;

/*
** Clean up
*/

DROP TABLE #t_name_match
DROP TABLE #t_compat_match

RETURN 0;
END PROCEDURE1;

以下是原始SQL代码：

**  Create the stored procedure
*/
ALTER PROCEDURE [dbo].[fcs_feature_usage]
(   @search_string      varchar(50),
    @debug_flag     char(1)     = 'N'
)
as

DECLARE @error          int,
    @search_string_like varchar(51)

SELECT  @search_string_like     = Upper(@search_string) + '%'

IF (@debug_flag = 'Y') OR (@debug_flag = 'X')
    SELECT  GetDate() as "fcs_feature_usage Started"

/*
** Store matching results in intermediate tables (required to avoid 
** JOIN, DISTINCT, and SORT between two, 2 million row tables).
*/
CREATE TABLE #t_name_match
(   KBID        int     NOT NULL,
    SYMBOLID    int     NOT NULL,
    FEATURE_NAME    varchar(30) NULL
)

CREATE TABLE #t_compat_match
(   KBID        int     NOT NULL,
    CHARID      int     NOT NULL,
    SYMBOLID    int     NOT NULL
)
/*******************************************************************************    ***
** Name matches
********************************************************************************    ***/
/*
** Load matches to Name Table
*/
INSERT INTO #t_name_match (KBID, SYMBOLID, FEATURE_NAME)
SELECT  KBID, SYMBOLID, FEATURE_NAME
  from  FCS..T_FEATURE_MASTER
 where  feature_name like @search_string_like

select  @error = @@error
IF (@error <> 0)
   BEGIN
    GOTO error_routine
   END

IF (@debug_flag = 'Y')
    SELECT  GetDate() as "#t_name_match Loaded"

/*******************************************************************************    ***
** Compatibility Statement matches
********************************************************************************    ***/
/*
** Load matches to Compatibility table
*/
INSERT INTO #t_compat_match (KBID, CHARID, SYMBOLID)
SELECT  KBID, CHARID, SYMBOLID
  from  FCS..T_FEATURE_COMPAT
 where  charindex(@search_string, LINE) > 0

select  @error = @@error
IF (@error <> 0)
   BEGIN
    GOTO error_routine
   END

IF (@debug_flag = 'Y')
    SELECT  GetDate() as "#t_compat_match Loaded"

/*
** Add to the name matches table a list of features which had 
** the given string in one of their compatibility statements.
**
** The below join, results in a complete index scan, so broke into
** two statements (INSERT & UPDATE):
**
INSERT INTO #t_name_match (KBID, SYMBOLID, FEATURE_NAME)
SELECT  cm.KBID, cm.SYMBOLID, fm.FEATURE_NAME
  from  FCDB..T_FEATURE_MASTER      fm,
    (SELECT DISTINCT KBID, SYMBOLID
       from #t_compat_match)    cm
 where  fm.KBID     = cm.KBID
   and  fm.SYMBOLID = cm.SYMBOLID
   and  NOT EXISTS  (SELECT 'X'
               from #t_name_match nm
              where nm.KBID     = cm.KBID
                and nm.SYMBOLID = cm.SYMBOLID)
*/
INSERT INTO #t_name_match (KBID, SYMBOLID, FEATURE_NAME)
SELECT  cm.KBID, cm.SYMBOLID, NULL
  from  (SELECT DISTINCT KBID, SYMBOLID
       from #t_compat_match)    cm
 where  NOT EXISTS  (SELECT 'X'
               from #t_name_match nm
              where nm.KBID     = cm.KBID
                and nm.SYMBOLID = cm.SYMBOLID)

select  @error = @@error
IF (@error <> 0)
   BEGIN
    GOTO error_routine
   END

UPDATE  #t_name_match
   set  FEATURE_NAME = fm.FEATURE_NAME
  from  #t_name_match       nm,
    FCS..T_FEATURE_MASTER   fm
 where  nm.FEATURE_NAME IS NULL
   and  fm.KBID     = nm.KBID
   and  fm.SYMBOLID = nm.SYMBOLID

select  @error = @@error
IF (@error <> 0)
   BEGIN
    GOTO error_routine
   END

IF (@debug_flag = 'Y')
    SELECT  GetDate() as "Added #t_compat_match to #t_name_match"

/*******************************************************************************    ***
** Return result set
    ********************************************************************************        ***/
SELECT  DISTINCT m.Model_Name, m.Version, m.plant_id fac_cd,     m.FROM_DATE,           m.TO_DATE,
        m.build, m.KBID, nm.SYMBOLID, left(nm.FEATURE_NAME,12) featureName
      FROM  #t_name_match       nm,
       T_MODEL_MASTER       m
         WHERE  m.KBID      = nm.KBID 
       AND  m.Model_Group   = 'F'
    ORDER BY m.Model_Name, m.version

    /*
    ** Clean up
    */
DROP TABLE #t_name_match
DROP TABLE #t_compat_match

IF (@debug_flag = 'Y') OR (@debug_flag = 'X')
    SELECT  GetDate() as "fcs_feature_usage Completed"

RETURN 0

/*********************************************************************
** Error Processing Routine. 
**********************************************************************/
error_routine:

DROP TABLE #t_name_match
 DROP TABLE #t_compat_match

RETURN  @error

Answer 1

DROP是在PL/SQL代码中无效的DDL语句。

使用“立即执行”替换它

execute immediate "DROP TABLE t_name_match"

Answer 2

您几乎肯定不希望在Oracle过程中创建和删除表。Oracle临时表与SQLServer临时表非常不同。在SQLServer中，临时表的定义是会话本地的（假设表名以#为前缀）。Oracle没有这种本地临时表的概念。Oracle中的临时表是全局的——定义对所有会话可见，数据仅对本地会话可见。这意味着您将在定义永久表的同时在过程之外创建一次临时表——并且您不会在过程中删除它们。

类似于

CREATE GLOBAL TEMPORARY TABLE t_name_match
(   KBID        int     NOT NULL,
    SYMBOLID    int     NOT NULL,
    FEATURE_NAME    varchar2(30) NULL
)
ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE t_compat_match
(   KBID        int     NOT NULL,
    CHARID      int     NOT NULL,
    SYMBOLID    int     NOT NULL
)
ON COMMIT DELETE ROWS;


create or replace PROCEDURE "PROCEDURE1" 
(
  IN_FEATURENAME IN NVARCHAR2 
, OUT_O_RC OUT SYS_REFCURSOR 
) AS


BEGIN
/*******************************************************************************    ***
** Name matches
    ********************************************************************************    ***/
/*
** Load matches to Name Table
*/
INSERT INTO t_name_match (KBID, SYMBOLID, FEATURE_NAME)
SELECT  KBID, SYMBOLID, FEATURENAME
  from  FEATURE_MASTER
 where  FEATURENAME like IN_FEATURENAME;

/*******************************************************************************    ***
** Compatibility Statement matches
********************************************************************************    ***/
/*
** Load matches to Compatibility table
*/
 INSERT INTO t_compat_match (KBID, CHARID, SYMBOLID)
SELECT  KBID, CHARID, SYMBOLID
  from  FEATURE_COMPAT
 where  INSTR(IN_FEATURENAME, LINE) > 0;

INSERT INTO t_name_match (KBID, SYMBOLID, FEATURE_NAME)
 SELECT cm.KBID, cm.SYMBOLID, NULL
   from (SELECT DISTINCT KBID, SYMBOLID
        from t_compat_match)    cm
  where NOT EXISTS  (SELECT 'X'
               from t_name_match nm
              where nm.KBID     = cm.KBID
                and nm.SYMBOLID = cm.SYMBOLID);


UPDATE  t_name_match

  SET FEATURE_NAME = (SELECT FEATURENAME
  from  FEATURE_MASTER

  where nm.FEATURE_NAME IS NULL
   and  fm.KBID     = nm.KBID
    and fm.SYMBOLID = nm.SYMBOLID);

 OPEN OUT_O_RC FOR
/*******************************************************************************    ***
** Return result set
********************************************************************************     ***/
SELECT  DISTINCT m.MODELNAME, m.VERSION, m.PLANTID, m.FROMDATE, m.TODATE,
     m.BUILD, m.KBID, nm.SYMBOLID, nm.FEATURE_NAME as FEATURENAME
  FROM  t_name_match        nm,
     KB_MASTER      m
  WHERE     m.KBID = nm.KBID 
    AND     m.MODELGROUP    = 'F'
 ORDER BY m.MODELNAME, m.VERSION;

END PROCEDURE1;

可能有用。注释块前面有“OPEN OUT\u O\u RC FOR”（打开代码），后面有“SELECT”（选择代码），这使得这段代码有点难以理解。我还没有查找其他语法错误。

当然，在Oracle中，首先使用临时表是非常不常见的——在Oracle中为每个在SQL中使用临时表的过程进行直接移植并使用临时表服务器将创建一些非常非惯用的Oracle代码，其效率可能低于预期。最终在您的系统中使用数百或数千个临时表也可能很烦人，特别是如果不同的SQL服务器过程创建具有相同名称和不同列集的临时表。使用本地集合或不首先实现数据而直接查询底层永久表可能会更好。这取决于您为什么首先在SQL服务器中使用临时表。例如，请参阅这个关于临时表替代方案的线程。