Hi all. I have a procedure where I need to do the following
Where city_id = v_param_city_id into variable_city_name
insert into tbl_zones (col1, col2, col3) Values (v_param1,v_param2,variable_city_name)
I know when doing an insert, you can do 'INTO v_VarName'
Will this work for my select? Basically, I need to get the name of the city, based on the value in the ID column in the city table, which I will be passing into the procedure from my application....Just wonderin if that will work (and if not, what is the correct syntax here, below is my actual code - thank you for your help!
v_zone_ID IN tbl_zone_locations.zone_id%TYPE,
v_txt_city IN tbl_zone_locations.txt_city%TYPE,
v_loc_id IN tbl_zone_locations.loc_id%TYPE
SELECT CITY FROM tbl_Locations WHERE Loc_ID = v_loc_id into v_txt_city;
Insert into tbl_zone_locations (zone_id, txt_city, loc_id) VALUES (v_zone_id, v_txt_city, v_loc_id);
You could also combine the two steps into something like:
( p_zone_id tbl_zone_locations.zone_id%TYPE
, p_loc_id tbl_zone_locations.loc_id%TYPE )
INSERT INTO tbl_zone_locations
, loc_id )
FROM tbl_locations l
WHERE l.loc_id = p_loc_id;
However I'm guessing about some of the business logic - also in the above version, if you supply an invalid location ID it will insert no rows and return successfully, whereas a separate SELECT INTO would raise an exception. Of course you could check SQL%ROWCOUNT immediately after the INSERT and raise an exception if it was not 1.
p_zone_id IN tbl_zone_locations.zone_id%TYPE,
p_loc_id IN tbl_zone_locations.loc_id%TYPE
SELECT city INTO v_txt_city FROM tbl_Locations WHERE Loc_ID = p_loc_id;
Insert into tbl_zone_locations (zone_id, txt_city, loc_id) VALUES (p_zone_id, l_txt_city, p_loc_id);
I change the prefixes on the parameters and variable so that you can easily see what is a parameter and what is a local variable.
You wouldn't need the SELECT at all if your design was properly normalized: stroing the txt_city value in tbl_zone_locations is redundant since it can be derived by joining to tbl_locations.
Thank you for your responses. The application logic wil theoretically prevent the possibility of providing an invalid location ID to the procedure - however probably a good idea to trap that anyway.