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;
4 1827
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;
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
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...............
As per restriction u can't use check in select.
use if or case with simple programming before inserting.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: DarthMacgyver |
last post by:
Hello,
I recently wrote a survey application. Each question is very similar.
The first questions gives me a problem when there are multiple people
taking the survey (The Database connection...
|
by: Mullin Yu |
last post by:
i have a stored procedure at sql server 2k. which will update records and
select result from temp table.
if i use SqlConnection class, and i do both. but, if i use OleDbConnection
class, i can...
|
by: Kevin Yu |
last post by:
hi all
since the DateTime can't be assign null, the min value is set to 1901 or
something, if in a application design, the date field can be null, so in
between the UI and the DB, the business...
|
by: Dabbler |
last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the
following error when trying to update (ExecuteNonQuery):
System.Data.SqlClient.SqlException: Procedure or Function...
|
by: c676228 |
last post by:
Hi everyone,
I wrote a store procedure that fetch one row data in the database based on
the parameter value I entered. After I created the store procedure,
the store procedure code looks like...
|
by: rxding |
last post by:
Hello,
Performance reason we need to move some of our code into database. Java Store Procedure is given the first choice. However, while investigating some sample code of Java store procedure, ...
|
by: eRTIS SQL |
last post by:
hi,
I want to use a stored procedure inside a stored procedure simulteanously changing the database.
this is my base store procedure
alter PROCEDURE create_file @dbname sysname
AS
declare...
|
by: sun919 |
last post by:
hi there... i ve a little question asking concerning saving data into
database
Basically, I have stored procedure name InsertquestionnaireList and the
argument for this is both correct
... I...
|
by: Light |
last post by:
Hi all,
I posted this question in the sqlserver.newusers group but I am not getting
any response there so I am going to try it on the fine folks here:).
I inherited some legacy ASP codes in my...
|
by: guangxiren |
last post by:
Hi, there
I have this data binding in the C code:
char szCounter;
....
pSql->BindCharParam(12,"@Counter",szCounter,TRUE,1024);
I got a problem regarding the length of the datalen of...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |