CODEWITHSUNDEEP
oracle-databaseplsql
Toronto23
Toronto23

Reputation: 319

Stored procedure in PL/SQL to insert new record in table

I need to create a PL/SQL stored procedure to add a record to the tblCity2 table. Sample of tblCity2:

+-------------+--------+-------------+------------+
|     NAME    | CAPITAL|  POPULATION | STATE_CODE |
+-------------+--------+-------------+------------+
| Monterrey   |   Y    |   2015000   |      MX19  |
| Mazatlan    |   N    |    199830   |      MX25  |
| Guadalajara |   Y    |   2325000   |      MX14  |
+-------------+--------+-------------+------------+

The procedure receives 4 parameters: the name of the city, if the city is a capital or not, the city’s population and the name of the state. The name of the state is being taken from the table tblState2:

+------+-----------------------+---------+-----------+
| CODE |         NAME          | POP1990 | AREA_SQMI |
+------+-----------------------+---------+-----------+
| MX02 | Baja California Norte | 1660855 | 28002.325 |
| MX03 | Baja California Sur   |  317764 | 27898.191 |
| MX18 | Nayarit               |  824643 | 10547.762 |
+------+-----------------------+---------+-----------+

The procedure calls a stored function passing to it the state name, and the function returns the state’s code, which is then used along with the other parameters to insert the new record in tblCity2.

I have started some code but I'm not sure of the next step:

PROCEDURE question2
(i_StateName IN TBLSTATE2.NAME%TYPE,
i_CityName IN TBLCITY2.NAME%TYPE,
i_CityCapital IN TBLCITY2.CAPITAL%TYPE,
i_CityPopulation IN TBLCITY2.POPULATION%TYPE);

FUNCTION create_city
(i_StateName IN TBLSTATE2.NAME%TYPE)
RETURN [something??]
INSERT INTO TBLCITY2 VALUES;
END question2;

I also need to insert an anonymous program block to call the procedure. Is this correct? Can I run this code in the same code as above or does it have to be run separately?

BEGIN
question2(i_StateName, i_CityName, i_CityCapital, i_CityPopulation);
END;

Upvotes: 0

Views: 9654

Answers (1)

GolezTrol
GolezTrol

Reputation: 116200

You can write a function with a SELECT .. INTO statement to get the state code based on the state name. The INTO clause allows you to specify variables in which the return values of the select statement are stored.

FUNCTION getStateCode
  (i_StateName IN TBLSTATE2.NAME%TYPE) 
RETURN tblState2.CODE%TYPE IS
  V_CODE tblState2.CODE%TYPE;
BEGIN
    SELECT CODE
    INTO   V_CODE
    FROM   tblState2
    WHERE  NAME = i_StateName;

    RETURN V_CODE;
END;

Note that the query must return exactly one row. If it returns 0 or more than 1 row, it will fail with No Data Found or Too Many Rows.

A No Data Found error can be caught by embedding the query in a SELECT FROM DUAL query:

FUNCTION getStateCode
  (i_StateName IN TBLSTATE2.NAME%TYPE) 
RETURN tblState2.CODE%TYPE IS
  V_CODE tblState2.CODE%TYPE;
BEGIN
    SELECT (SELECT CODE
            FROM   tblState2
            WHERE  NAME = i_StateName)
    INTO   V_CODE
    FROM   DUAL

    RETURN V_CODE;
END;

That function returns the code, which can then be used in an insert statement in a procedure:

PROCEDURE insertCity
  (i_StateName      IN TBLSTATE2.NAME%TYPE,
   i_CityName       IN TBLCITY2.NAME%TYPE,
   i_CityCapital    IN TBLCITY2.CAPITAL%TYPE,
   i_CityPopulation IN TBLCITY2.POPULATION%TYPE) IS

BEGIN

    INSERT INTO TBLCITY2 (NAME, CAPITAL, POPULATION, STATE_CODE)
    VALUES(
      i_CityName,
      i_CityCapital,
      i_CityPopulation,
      getStateCode(i_StateName)
    );

END;

You can then call the procedure exactly like you do:

BEGIN
  insertCity(i_StateName, i_CityName, i_CityCapital, i_CityPopulation);
END;

Like @APC has mentioned in the comments, it's bad practice to modify data inside a function. The reason for this, is functions can be called from queries as well, so that means you can accidentally modify data when you just want to query the data.

So in the setup above, there is a function that just queries data. The function can be used in SQL or (in this case) in a procedure. The procedure can safely modify data, since it cannot be used in a select query anyway.

Upvotes: 2

Related Questions