Reputation: 319
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
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