问在编译时抛出错误的存储过程: Oracle sql developer
EN

Stack Overflow用户

提问于 2020-03-30 21:09:59

回答 1查看 245关注 0票数 0

我已经使用SQL Server多年了。但是在我的当前项目中有一个要求，我必须将用Server编写的过程转换为Oracle (在Server工具中)。

但是似乎有很多分钟的语法变化，我无法用抛出的错误消息来判断。

这是我的存储过程：

CREATE OR REPLACE PROCEDURE PROC_MyPROC
(
  PAGENUMBER IN NUMBER 
, PAGESIZE IN NUMBER 
, SEARCHTERM IN VARCHAR2 
, TOTAL IN NUMBER 
) AS 
BEGIN
  IF(PAGENUMBER=1)  
    THEN
          SELECT COUNT(distinct mastraccnt.T1Column1) INTO TOTAL    
          FROM Table1  mastraccnt  
          JOIN Table2 req on LTRIM(RTRIM(req.T2Column1)) = LTRIM(RTRIM(mastraccnt.T1Column1))
          WHERE LOWER(RTRIM(LTRIM(mastraccnt.T1Column1))||' - '||RTRIM(LTRIM(req.T2Column2))) like 
          '%'||LOWER(SEARCHTERM)||'%';
    END IF;

  SELECT distinct RTRIM(LTRIM(mastraccnt.T1Column1)) as MasterAccountId,
  RTRIM(LTRIM(mastraccnt.T1Column1))||' - '|| RTRIM(LTRIM(req.T2Column2)) as MasterAccountName,
  TOTAL AS TotalRows  
  FROM Table1 mastraccnt 
  JOIN Table2 req on LTRIM(RTRIM(req.T2Column1)) = LTRIM(RTRIM(mastraccnt.T1Column1))  
  WHERE LOWER(RTRIM(LTRIM(mastraccnt.T1Column1))||' - '||RTRIM(LTRIM(req.T2Column2))) like 
  '%'||LOWER(SearchTerm)||'%'   
  ORDER BY MasterAccountName    
  OFFSET PAGESIZE * (PAGENUMBER - 1) ROWS   
  FETCH NEXT PAGESIZE ROWS ONLY; 

END PROC_MyPROC;

在编译过程中，我得到了这些错误，不确定它们的含义。有人能帮我吗?我的手术怎么了？

plsql

oracle

stored-procedures

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-03-30 21:27:57

代码存在多个问题，如下所示：

您的变量TOTAL是一个输入参数，不能更改。您正在尝试使用INTO TOTAL更改它的值。
过程中的第二个SELECT语句需要一个INTO子句，或者需要成为某个游标的一部分。您不能这样在过程中写入任何SELECT查询。可以用query?
LTRIM(RTRIM(REQ.T2COLUMN1))值替换SELECT的预期值必须是静态整数。

我想你需要这样的东西：

CREATE OR REPLACE PROCEDURE PROC_MYPROC (
    PAGENUMBER   IN           NUMBER,
    PAGESIZE     IN           NUMBER,
    SEARCHTERM   IN           VARCHAR2,
    TOTAL        IN OUT       NUMBER,
    OUT_RESULT   OUT          SYS_REFCURSOR
) AS
BEGIN
    IF PAGENUMBER = 1 THEN
        SELECT
            COUNT(DISTINCT MASTRACCNT.T1COLUMN1)
        INTO TOTAL
        FROM
            TABLE1 MASTRACCNT
            JOIN TABLE2 REQ ON LTRIM(RTRIM(REQ.T2COLUMN1)) = LTRIM(RTRIM(MASTRACCNT.T1COLUMN1))
        WHERE
            LOWER(RTRIM(LTRIM(MASTRACCNT.T1COLUMN1))
                  || ' - '
                  || RTRIM(LTRIM(REQ.T2COLUMN2))) LIKE '%'
                                                       || LOWER(SEARCHTERM)
                                                       || '%';

    END IF;

    OPEN OUT_RESULT FOR SELECT
                           MASTERACCOUNTID,
                           MASTERACCOUNTNAME,
                           TOTALROWS
                       FROM
                           (
                               SELECT
                                   MASTERACCOUNTID,
                                   MASTERACCOUNTNAME,
                                   TOTALROWS,
                                   ROW_NUMBER() OVER(
                                       ORDER BY
                                           MASTERACCOUNTNAME
                                   ) AS RN
                               FROM
                                   (
                                       SELECT DISTINCT
                                           RTRIM(LTRIM(MASTRACCNT.T1COLUMN1)) AS MASTERACCOUNTID,
                                           RTRIM(LTRIM(MASTRACCNT.T1COLUMN1))
                                           || ' - '
                                           || RTRIM(LTRIM(REQ.T2COLUMN2)) AS MASTERACCOUNTNAME,
                                           TOTAL   AS TOTALROWS
                                       FROM
                                           TABLE1 MASTRACCNT
                                           JOIN TABLE2 REQ ON LTRIM(RTRIM(REQ.T2COLUMN1)) = LTRIM(RTRIM(MASTRACCNT.T1COLUMN1))
                                       WHERE
                                           LOWER(RTRIM(LTRIM(MASTRACCNT.T1COLUMN1))
                                                 || ' - '
                                                 || RTRIM(LTRIM(REQ.T2COLUMN2))) LIKE '%'
                                                                                      || LOWER(SEARCHTERM)
                                                                                      || '%'
                                   )
                           )
                       WHERE
                           RN > PAGESIZE * ( PAGENUMBER - 1 )
                           AND RN <= PAGESIZE * ( PAGENUMBER - 1 ) + PAGESIZE
                       ORDER BY
                           MASTERACCOUNTNAME;

END PROC_MYPROC;
/