1
edit
Changes
YSL
,→1.
''YuJin''
<pre>
v_street_len := LENGTH(v_street);
v_city_letter := SUBSTR(UPPER(v_city), 1, 1);
IF v_city_letter IN ('A', 'B', 'C', 'F') THEN
v_state_province := RPAD(v_state_province, v_street_len, '*');
ELSIF v_city_letter IN ('C', 'D', 'G', 'H') THEN
v_state_province := RPAD(v_state_province, v_street_len, '&');
ELSE
v_state_province := RPAD(v_state_province, v_street_len, '#');
END IF;
UPDATE LOCATIONS
SET state_province = v_state_province
WHERE location_id = :b_loc_id;
DBMS_OUTPUT.PUT_LINE('City ' || v_city || ' has modified its province to ' || v_state_province);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('This country has NO cities listed.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('City ' || v_city || 'has MORE THAN ONE City without province listed.');
END;
/
SELECT *
FROM LOCATIONS
WHERE location_id = :b_loc_id;
ROLLBACK;
</pre>
''Seung Yeon''
