oracle - 它是一个 oracle 存储过程，我得到“PLS-00403：”

Question

我正在尝试为我的项目执行 Oracle 存储过程。但我遇到了一个错误

PLS-00403：表达式 <> 不能用作 SELECT/FETCH 语句的 INTO 目标

声明的所有输入参数都会出现此错误。

这是我的代码

CREATE PROCEDURE CASHPROJECTIONS_ACC_STG
(
   P_INVESTMENT_OFFICER IN VARCHAR2,
    P_ADMINISTRATOR_NAME IN VARCHAR2,
    P_ACCOUNT_NUMBER IN INTEGER,
    P_ACCOUNT_ODFLAG IN VARCHAR2,
    P_USD_PT_ODFLAG_NEW IN VARCHAR2,
    P_ACCT_SWEEP_COVERED IN VARCHAR2,
    P_USD_PT_SWEEP_COVERED IN VARCHAR2,
    P_RUNDATE OUT DATE,
    P_RELATIONSHIP_NAME OUT VARCHAR2,
    P_PORTFOLIO_NUMBER OUT INTEGER,
    P_PORTFOLIO_NAME OUT VARCHAR2,
    P_NETTING_FOR_OVERDRAFTS OUT VARCHAR2,
    P_NETTING_FOR_SWEEP OUT VARCHAR2,
    P_SUMMARY_DESCRIPTION OUT VARCHAR2,
    P_INCOME_BASE OUT FLOAT,
    P_USD_INC_DAILY_BAL OUT FLOAT,
    P_PRINCIPAL_BASE OUT FLOAT,
    P_USD_PRIN_DAILY_BAL OUT FLOAT,
    P_ACCT_BALANCE OUT FLOAT,
    P_SWEEP_PT1 OUT FLOAT,
    P_SWEEP_TOTAL OUT FLOAT,
    P_SWEEP_RESTRICTED OUT FLOAT,
    P_SWEEP_AVAILABLE_BALANCE OUT FLOAT,
    P_SWEEP_PT1_ASSET_NM OUT VARCHAR2

)
AS
BEGIN
   SELECT  
   RUNDATE
,INVESTMENT_OFFICER
,ADMINISTRATOR_NAME
,RELATIONSHIP_NAME
,ACCOUNT_NUMBER
,PORTFOLIO_NUMBER
,PORTFOLIO_NAME
,NETTING_FOR_OVERDRAFTS
,NETTING_FOR_SWEEP
,SUMMARY_DESCRIPTION
,INCOME_BASE
,USD_INC_DAILY_BAL
,PRINCIPAL_BASE
,USD_PRIN_DAILY_BAL
,ACCT_BALANCE
,SWEEP_PT1
,SWEEP_TOTAL
,SWEEP_RESTRICTED
,SWEEP_AVAILABLE_BALANCE
,ACCOUNT_ODFLAG
,ACCT_SWEEP_COVERED
,USD_PT_ODFLAG_NEW
,USD_PT_SWEEP_COVERED
,SWEEP_PT1_ASSET_NM
into
P_RUNDATE
,P_INVESTMENT_OFFICER
,P_ADMINISTRATOR_NAME
,P_RELATIONSHIP_NAME
,P_ACCOUNT_NUMBER
,P_PORTFOLIO_NUMBER
,P_PORTFOLIO_NAME
,P_NETTING_FOR_OVERDRAFTS
,P_NETTING_FOR_SWEEP
,P_SUMMARY_DESCRIPTION
,P_INCOME_BASE
,P_USD_INC_DAILY_BAL
,P_PRINCIPAL_BASE
,P_USD_PRIN_DAILY_BAL
,P_ACCT_BALANCE
,P_SWEEP_PT1
,P_SWEEP_TOTAL
,P_SWEEP_RESTRICTED
,P_SWEEP_AVAILABLE_BALANCE
,P_ACCOUNT_ODFLAG
,P_ACCT_SWEEP_COVERED
,P_USD_PT_ODFLAG_NEW
,P_USD_PT_SWEEP_COVERED
,P_SWEEP_PT1_ASSET_NM



   FROM FTCI_CASHPROJECTIONS_ACC_STG CPAS 
   WHERE CPAS.INVESTMENT_OFFICER = P_INVESTMENT_OFFICER
   AND CPAS.ADMINISTRATOR_NAME = P_ADMINISTRATOR_NAME
   AND CPAS.ACCOUNT_NUMBER = P_ACCOUNT_NUMBER
   AND CPAS.ACCOUNT_ODFLAG = P_ACCOUNT_ODFLAG
   AND CPAS.USD_PT_ODFLAG_NEW = P_USD_PT_ODFLAG_NEW
   AND CPAS.ACCT_SWEEP_COVERED = P_ACCT_SWEEP_COVERED
    AND CPAS.USD_PT_SWEEP_COVERED = P_USD_PT_SWEEP_COVERED;
END;

谁能帮我解决这个问题？

谢谢

标签： oraclestored-proceduresplsql

Answer 1

SELECT 语句的几列映射到这些参数：

P_INVESTMENT_OFFICER IN VARCHAR2,
P_ADMINISTRATOR_NAME IN VARCHAR2,
P_ACCOUNT_NUMBER IN INTEGER,
P_ACCOUNT_ODFLAG IN VARCHAR2,
P_USD_PT_ODFLAG_NEW IN VARCHAR2,
P_ACCT_SWEEP_COVERED IN VARCHAR2,
P_USD_PT_SWEEP_COVERED IN VARCHAR2,

您已将它们定义为 IN 参数，因此您无法为它们分配值。这是错误消息的含义：

PLS-00403：表达式不能用作 SELECT/FETCH 语句的 INTO 目标

有两种解决方案可供选择。

1. 不要分配给这些参数。
2. 将它们更改为 IN OUT 参数。

在这种情况下，第一个解决方案是正确的选择。调用程序将这些值传入，以限制查询。所以它已经知道这些值并且不需要被调用的程序来反映它们。将参数定义为 IN OUT 意味着被调用程序可能会更改传递的值。

---

你能详细说说第一点吗？但我需要 in 参数

真的不难。您保留用于过滤查询的 IN 参数，但将它们从查询的投影和匹配的 INTO 子句中删除。像这样：

CREATE PROCEDURE CASHPROJECTIONS_ACC_STG
(
   P_INVESTMENT_OFFICER IN VARCHAR2,
    P_ADMINISTRATOR_NAME IN VARCHAR2,
    P_ACCOUNT_NUMBER IN INTEGER,
    P_ACCOUNT_ODFLAG IN VARCHAR2,
    P_USD_PT_ODFLAG_NEW IN VARCHAR2,
    P_ACCT_SWEEP_COVERED IN VARCHAR2,
    P_USD_PT_SWEEP_COVERED IN VARCHAR2,
    P_RUNDATE OUT DATE,
    P_RELATIONSHIP_NAME OUT VARCHAR2,
    P_PORTFOLIO_NUMBER OUT INTEGER,
    P_PORTFOLIO_NAME OUT VARCHAR2,
    P_NETTING_FOR_OVERDRAFTS OUT VARCHAR2,
    P_NETTING_FOR_SWEEP OUT VARCHAR2,
    P_SUMMARY_DESCRIPTION OUT VARCHAR2,
    P_INCOME_BASE OUT FLOAT,
    P_USD_INC_DAILY_BAL OUT FLOAT,
    P_PRINCIPAL_BASE OUT FLOAT,
    P_USD_PRIN_DAILY_BAL OUT FLOAT,
    P_ACCT_BALANCE OUT FLOAT,
    P_SWEEP_PT1 OUT FLOAT,
    P_SWEEP_TOTAL OUT FLOAT,
    P_SWEEP_RESTRICTED OUT FLOAT,
    P_SWEEP_AVAILABLE_BALANCE OUT FLOAT,
    P_SWEEP_PT1_ASSET_NM OUT VARCHAR2

)
AS
BEGIN
   SELECT  
   RUNDATE
,RELATIONSHIP_NAME
,PORTFOLIO_NUMBER
,PORTFOLIO_NAME
,NETTING_FOR_OVERDRAFTS
,NETTING_FOR_SWEEP
,SUMMARY_DESCRIPTION
,INCOME_BASE
,USD_INC_DAILY_BAL
,PRINCIPAL_BASE
,USD_PRIN_DAILY_BAL
,ACCT_BALANCE
,SWEEP_PT1
,SWEEP_TOTAL
,SWEEP_RESTRICTED
,SWEEP_AVAILABLE_BALANCE
,SWEEP_PT1_ASSET_NM
into
P_RUNDATE
,P_RELATIONSHIP_NAME
,P_PORTFOLIO_NUMBER
,P_PORTFOLIO_NAME
,P_NETTING_FOR_OVERDRAFTS
,P_NETTING_FOR_SWEEP
,P_SUMMARY_DESCRIPTION
,P_INCOME_BASE
,P_USD_INC_DAILY_BAL
,P_PRINCIPAL_BASE
,P_USD_PRIN_DAILY_BAL
,P_ACCT_BALANCE
,P_SWEEP_PT1
,P_SWEEP_TOTAL
,P_SWEEP_RESTRICTED
,P_SWEEP_AVAILABLE_BALANCE
,P_SWEEP_PT1_ASSET_NM
FROM FTCI_CASHPROJECTIONS_ACC_STG CPAS 
   WHERE CPAS.INVESTMENT_OFFICER = P_INVESTMENT_OFFICER
   AND CPAS.ADMINISTRATOR_NAME = P_ADMINISTRATOR_NAME
   AND CPAS.ACCOUNT_NUMBER = P_ACCOUNT_NUMBER
   AND CPAS.ACCOUNT_ODFLAG = P_ACCOUNT_ODFLAG
   AND CPAS.USD_PT_ODFLAG_NEW = P_USD_PT_ODFLAG_NEW
   AND CPAS.ACCT_SWEEP_COVERED = P_ACCT_SWEEP_COVERED
    AND CPAS.USD_PT_SWEEP_COVERED = P_USD_PT_SWEEP_COVERED;
END;