Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Unanswered: Quick PL/SQL Question

    Hi all. I have a procedure where I need to do the following
    [pseudo code]

    Select City_Name
    From tbl_city
    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!

    Code:
     PROCEDURe Location_In_Zone
    (
    	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
    )
    	IS
    	 BEGIN
    	  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);
    	 END;

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    That should work, except it needs to be

    SELECT col INTO var FROM tab;

    You could also combine the two steps into something like:

    Code:
    PROCEDURe location_in_zone
    	( p_zone_id  tbl_zone_locations.zone_id%TYPE
    	, p_loc_id   tbl_zone_locations.loc_id%TYPE )
    IS
    BEGIN
    	INSERT INTO tbl_zone_locations
    	     ( zone_id
    	     , txt_city
    	     , loc_id )
    	SELECT p_zone_id
    	     , l.city
    	     , p_loc_id
    	FROM   tbl_locations l
    	WHERE  l.loc_id = p_loc_id;
    END;
    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.
    Last edited by WilliamR; 12-08-04 at 14:17.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The syntax would be:
    Code:
    PROCEDURE Location_In_Zone
    (
      p_zone_id IN tbl_zone_locations.zone_id%TYPE,
      p_loc_id IN tbl_zone_locations.loc_id%TYPE
    )
    IS
      l_txt_city tbl_zone_locations.txt_city%TYPE,
    BEGIN
      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);
    END;
    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.

  4. #4
    Join Date
    Dec 2003
    Posts
    148

    Great..

    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.
    THanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •