i have problem with store procedure

[vartigo]

CREATE or REPLACE PROCEDURE add_order (vorderid IN number, vstoreid IN number, vcustomerid IN number, vorderdate IN date, vshipperid IN varchar)
AS
Newshipperid varchar(10);
BEGIN
If newshipperid = (select shipperid from RAHorders check (shipperid IN (‘UPS’, ‘USPS’, ‘FEDEX’, ‘CUST’)) )THEN
INSERT INTO RAHorders VALUES (vorderid, vstoreid, vcustomerid, vorderdate, vshipperid);
ELSE
Dbms_output.put _line(‘Shipper must be UPS,USPS,CUST or FEDEX’);
END IF;
COMMIT;
END;

[Sandya]

CREATE or REPLACE PROCEDURE add_order (vorderid IN number, vstoreid IN number, vcustomerid IN number, vorderdate IN date, vshipperid IN varchar)
AS
Newshipperid varchar(10);
BEGIN
If newshipperid = (select shipperid from RAHorders check (shipperid IN (‘UPS’, ‘USPS’, ‘FEDEX’, ‘CUST’)) )THEN
INSERT INTO RAHorders VALUES (vorderid, vstoreid, vcustomerid, vorderdate, vshipperid);
ELSE
Dbms_output.put _line(‘Shipper must be UPS,USPS,CUST or FEDEX’);
END IF;
COMMIT;
END;

U cant u use check in select statement ............... It is used while creating table or altering the table to add constraint

u need to modify like this


CREATE or REPLACE PROCEDURE add_order (vorderid IN number, vstoreid IN number, vcustomerid IN number, vorderdate IN date, vshipperid IN varchar)
AS
Newshipperid varchar(10);
BEGIN
If newshipperid = (select shipperid from RAHorders where shipperid IN (‘UPS’, ‘USPS’, ‘FEDEX’, ‘CUST’)) )THEN
INSERT INTO RAHorders VALUES (vorderid, vstoreid, vcustomerid, vorderdate, vshipperid);
ELSE
Dbms_output.put _line(‘Shipper must be UPS,USPS,CUST or FEDEX’);
END IF;
COMMIT;
END;

[chandu031]

Hi Vartigo,

Looking at your code, i am guessing you want to insert a row into the RAHorders table only if the shipping id is one of the given values. Since you already have v_shipperid , just do a check to see if it's in

[chandu031]

Hi Vartigo,

Looking at your code, i am guessing you want to insert a row into the RAHorders table only if the shipping id is one of the given values. Since you already have v_shipperid , just do a check to see if V_SHIPPERID is in('UPS',USPS', 'FEDEX','CUST') ...

A simple IF condition should work here...

IF(v_shipper_id = 'UPS' OR v_shipperid = 'USPS'....) THEN
INSERT INTO.........
ELSE
DBMS_OUTPUT.... ........

A better way of handling this would be having this check constraint in the CREATE TABLE statement itself or you can add this check using an ALTER TABLE statement...... .........

[debasisdas]

As per restriction u can't use check in select.

use if or case with simple programming before inserting.