CODEWITHSUNDEEP
oracle-databaseplsqldynamic-sql
Zhamshidbek Abdulkhamidov
Zhamshidbek Abdulkhamidov

Reputation: 45

Are there any ways of creating stored procedure for unknown number of columns?

I have cars table and it has 18 columns.

CREATE TABLE CARS
(
    ID INTEGER PRIMARY KEY,
    Manufacturer VARCHAR2(1000),
    Model VARCHAR2(1000),
    Year INTEGER NOT NULL,
    Category VARCHAR2(1000) NOT NULL,
    Mileage NUMBER,
    FuelType VARCHAR2(1000),
    EngineVolume NUMBER,
    DriveWheels VARCHAR2(1000),
    GearBox VARCHAR2(1000),
    Doors VARCHAR2(1000),
    Wheel VARCHAR2(1000),
    Color VARCHAR2(1000),
    InteriorColor VARCHAR2(1000),
    VIN VARCHAR2(1000),
    LeatherInterior VARCHAR2(1000) NOT NULL,
    Price VARCHAR2(1000) NOT NULL,
    Clearence VARCHAR2(1000) NOT NULL
)

And we going to make an update procedure for this column on the interface. So I can make a dynamic sql procedure for only one column. But we do not know how many columns the user going to update on the interface. So, how I can make a procedure for it?

Upvotes: 0

Views: 47

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132570

Just create a procedure that updates all the columns the user might have changed, whether they actually did or not. There is usually no need to check and only update the specific columns they changed - unless perhaps there is some significant penalty (e.g. performance) in doing so (there isn't usually).

Upvotes: 1

Related Questions