Oracle中的存储过程给出错误PLS-00428 [英] Stored Procedure in Oracle giving error PLS-00428

问题描述

我正在尝试在oracle中创建以下过程:

I am trying to create the following procedure in oracle:

CREATE OR REPLACE PROCEDURE SPBILL (SPCLIENT_ID VARCHAR2) 
AS
BEGIN
   SELECT C.CLIENT_NAME, B.ROOM_ID, R.ROOM_COST, T.TREAT_NAME, T.TREAT_COST, (ROOM_COST*(B_END_DATE-B_START_DATE)+TREAT_COST) AS INVOICE
   FROM CLIENTS C, ROOMS R, TREATMENTS T, BOOKING B, PRESCRIPTION P
   WHERE C.CLIENT_ID=B.CLIENT_ID
   AND R.ROOM_ID=B.ROOM_ID
   AND B.CLIENT_ID=P.CLIENT_ID
   AND P.TREAT_ID=T.TREAT_ID
   AND C.CLIENT_ID=SPCLIENT_ID;
END SPBILL;

我收到程序创建时出现编译错误"，错误是PLS-00428，需要INTO注释，但是我不明白为什么以及在什么地方需要它，因为我的sql语句按照我想要的方式工作它没有程序.但是我需要创建一个过程，以便我可以调用特定的客户端ID，并且仅将其数据作为输出接收.

I am getting a "Procedure created with compilation errors" and the errors is PLS-00428, which required an INTO satement, but i do not understand why and where do i need it as my sql statement works just the way i want it without the procedure. But i need to create a procedure so i can call a specific client id and only recieve their data as an output.

推荐答案

直接使用客户端(SQL Plus或SQL Developer或Toad)运行SQL时，数据将返回到客户端. 当您在PL/SQL中运行相同的查询时，您需要告诉oracle如何处理该数据.通常，程序会将输出存储在Pl/SQL变量中以进行进一步处理.

When you are running the SQL directly using a client (SQL Plus or SQL Developer or Toad) , data is returned to the client. When you run the same query inside PL/SQL, you need to tell oracle what to do with that data. Usually programs store the output in Pl/SQL variables for further processing.

https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/selectinto_statement.htm

因此，在您的情况下，您可能需要遵循以下原则.

So, in your case, you might need something along these lines..

CREATE OR REPLACE PROCEDURE SPBILL (SPCLIENT_ID VARCHAR2) 
AS
  l_client_name clients.client_name%type;
  l_room_id rooms.room_id%type;
  ...
  l_invoice number(5,2);
BEGIN
   SELECT C.CLIENT_NAME, B.ROOM_ID, R.ROOM_COST, T.TREAT_NAME, T.TREAT_COST, (ROOM_COST*(B_END_DATE-B_START_DATE)+TREAT_COST)
   into l_client_name, l_room_id...l_invoice
   FROM CLIENTS C, ROOMS R, TREATMENTS T, BOOKING B, PRESCRIPTION P
   WHERE C.CLIENT_ID=B.CLIENT_ID
   AND R.ROOM_ID=B.ROOM_ID
   AND B.CLIENT_ID=P.CLIENT_ID
   AND P.TREAT_ID=T.TREAT_ID
   AND C.CLIENT_ID=SPCLIENT_ID;

   --further processing here based on variables above.
   dbms_output.put_line(l_invoice);
END SPBILL;

一旦编译没有错误，就可以运行该过程.

Once you compile without errors, you can run the procedure..

set serveroutput on; 
SPBILL(100); 

这篇关于Oracle中的存储过程给出错误PLS-00428的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！