Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.5k views
in Technique[技术] by (71.8m points)

sql - Pass a list of integers from C# into Oracle stored procedure

I have a oracle stored procedure which updates a table with the following statement.

update boxes 
set    location = 'some value'
where  boxid = passed value

I have a page where the user selects 100+ boxes and updates them with a new location value. Currently, I have to call the stored procedure 100+ times to update each box(by passing a boxid each time).

I want to know how I can pass a list of boxids from C# into the stored procedure so that I have to call the stored procedure just one time.

I am hoping to use a where in(boxids) kind of where clause in the update statement.

Please let know how can I achieve this. Thanks in advance!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Oracle allows you to pass arrays of values as parameters. Borrowing from this SO question and this one you can define an INT_ARRAY type like this:

create or replace type CHAR_ARRAY as table of INTEGER;

Then define your stored procedure as:

CREATE OR REPLACE PROCEDURE product_search(
        ...
        myIds IN CHAR_ARRAY,
        ...)
AS  
    SELECT ...
    ...
    WHERE SomeIdField IN (Select column_value FROM TABLE(myIds))
    ...

You can then pass the list of values by setting the OracleParameter.CollectionType property like this:

OracleParameter param = new OracleParameter();
param.OracleDbType = OracleDbType.Int32;
param.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...