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;
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.
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);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments