Scribd
Upload
2 views2 pages

Container Booking Procedure SQL Code

The document defines a SQL procedure named Prc_container_booking that handles the booking of containers between customers. It includes various checks for container availability, customer status, and booking amount, returning appropriate messages for different scenarios. The procedure also demonstrates sample calls with expected outcomes based on the input parameters.

Uploaded by

vrajasekaran0404
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
2 views2 pages

Container Booking Procedure SQL Code

The document defines a SQL procedure named Prc_container_booking that handles the booking of containers between customers. It includes various checks for container availability, customer status, and booking amount, returning appropriate messages for different scenarios. The procedure also demonstrates sample calls with expected outcomes based on the input parameters.

Uploaded by

vrajasekaran0404
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

create or replace procedure Prc_container_booking ( p_cont_id

Varchar,p_from_cust_id varchar,p_to_cust_id varchar , p_amount number)


returns varchar
language sql
as
$$
declare
v_from_cont_id varchar;
v_from_cust_id varchar;
v_from_custstatus varchar;
v_to_cust_id varchar;
v_to_custstatus varchar;

BEGIN
select cont_id into v_from_cont_id from container_master where cont_id
= :p_cont_id;
if (v_from_cont_id is null) then
return ' Container is not available' ;
End if ;
select cust_id,cust_status into v_from_cust_id, v_from_custstatus from
customer_master where cust_id = :p_from_cust_id;

if(v_from_cust_id is null) then


return ' from customer is not available' ;
End if ;

if(v_from_custstatus = 'B') then


return ' from customer is a Blocklisted customer, we cant book it ' ;
End if ;

select cust_id, cust_status into v_to_cust_id, v_to_custstatus from customer_master


where cust_id = :p_to_cust_id;

if (v_to_cust_id is null ) then


return ' To customer is not available' ;
End if ;
if (v_to_custstatus = 'B') then
return ' To customer is a Blocklisted customer, we cant book it ' ;
End if ;

if (:p_amount < 1) then


return ' amount cannot be leess than 1' ;
End if ;

if(v_from_cust_id = v_to_cust_id) then


return ' From customer and to customer cannot be same ' ;
End if ;

insert into id_booking_dtls ( BK_ID,


BK_DT,BK_CONT_ID,BK_FROM_CUST_ID,BK_TO_CUST_ID,BK_AMT)
Values ( Bk_seq.nextval,
current_date(),:p_cont_id,:p_from_cust_id,:p_to_cust_id,:p_amount);

return ' Container Booking done';


end ;
$$;

select * from customer_master;


select * from container_master;
select * from id_booking_dtls;

call Prc_container_booking ('CONT001','CUST003','CUST004',500); -- from customer is


a Blocklisted customer, we cant book it
call Prc_container_booking('CONT002','CUST002','CUST002',200);--- From customer and
to customer cannot be same
call Prc_container_booking('CONT002','CUST002','CUST001',200);---- Container
Booking done
call Prc_container_booking ('CONT001','CUST001','CUST004',0);------ amount cannot
be leess than 1

You might also like