CODEWITHSUNDEEP
sqloracle-databaseprocedure
Madhushani Hewagama
Madhushani Hewagama

Reputation: 468

oracle sql procedure with in and multi out params error

I created a procedure to get multiple out params. But compilation error came. I couldn't find the issue. please, I need some support to find the issue.............

my code is,

Basic database customer, reservation and service tables are below,

CREATE TABLE CUSTOMER
       (CUSTOMER_ID NUMBER(4) NOT NULL,
        CUSTOMER_NAME VARCHAR2(10),
        GENDER VARCHAR(10),
        ADDRESS VARCHAR(50),
        EMAIL VARCHAR(50),
        DISCOUNT FLOAT(10),
        PRIMARY KEY(CUSTOMER_ID)
        );

CREATE TABLE SERVICE
        (SERVICE_ID NUMBER(4) NOT NULL,
        SERVICE_NAME VARCHAR(100),
        CHARGE FLOAT(10),   
        PRIMARY KEY(SERVICE_ID)             
        );

CREATE TABLE RESERVATION
        (RESERVATION_ID NUMBER(4) NOT NULL,
        RESERVATION_DATE DATE,
        CUSTOMER_ID NUMBER(4),
        SERVICE_ID NUMBER(4),
        EMPLOYEE_ID NUMBER(4),
        PRIMARY KEY(RESERVATION_ID),
        FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID),
        FOREIGN KEY (SERVICE_ID) REFERENCES SERVICE(SERVICE_ID),
        FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)              
        );

I need to get some vales from the reservation table, service table and customer table. my procedure is below,

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE GET_RECEIPT(
    RES_ID IN RESERVATION.RESERVATION_ID%TYPE,
    C_NAME OUT CUSTOMER.CUSTOMER_NAME%TYPE,
    S_NAME OUT SERVICE.SERVICE_NAME%TYPE,
    CHARGE_VAL OUT FLOAT(10),
    DISCOUNT_VAL OUT FLOAT(10),
    TOTAL_VAL OUT FLOAT(10)
    ) 
AS
BEGIN
    SELECT 
        c.CUSTOMER_NAME AS CUS_NAME INTO C_NAME,
        s.SERVICE_NAME AS SER_NAME INTO S_NAME,
        s.CHARGE AS CHAR_VAL INTO CHARGE_VAL,
        c.DISCOUNT INTO DISCOUNT_VAL,
        (s.CHARGE-c.DISCOUNT)AS TOTAL INTO TOTAL_VAL 
    FROM RESERVATION r LEFT OUTER JOIN CUSTOMER c ON r.CUSTOMER_ID=c.CUSTOMER_ID LEFT OUTER JOIN SERVICE s ON r.SERVICE_ID=s.SERVICE_ID 
    WHERE r.RESERVATION_ID=RES_ID;  
END;
/

But this procedure couldn't compile. it says compilation fails. how can I fix this?

Upvotes: 1

Views: 36

Answers (1)

Littlefoot
Littlefoot

Reputation: 142710

Fixed, it looks like this:

SQL> create or replace procedure get_receipt(
  2      res_id in reservation.reservation_id%type,
  3      c_name out customer.customer_name%type,
  4      s_name out service.service_name%type,
  5      charge_val out float,
  6      discount_val out float,
  7      total_val out float
  8      )
  9  as
 10  begin
 11      select c.customer_name, s.service_name, s.charge, c.discount, s.charge - c.discount
 12      into c_name, s_name, charge_val, discount_val, total_val
 13      from reservation r left outer join customer c on r.customer_id=c.customer_id
 14      left outer join service s on r.service_id=s.service_id
 15      where r.reservation_id=res_id;
 16  end;
 17  /

Procedure created.

SQL>

What did you do wrong?

  • parameters don't have size (it is not float(10) but just float)
  • in PL/SQL, select has to have into clause - which is what you did, but in a wrong manner. Should be as in my example.

Upvotes: 2

Related Questions