Open main menu

CDOT Wiki β

Changes

YSL

1,185 bytes added, 22:13, 9 October 2011
→‎1.
''YuJin''
<pre>
SELECT department_id, department_name,SET SERVEROUTPUT ONSET VERIFY OFF RPAD(TRIM(NVL(city, ACCEPT country_code PROMPT 'Not Assinged YetEnter value for country: ')), 25, VARIABLE b_loc_id NUMBERDECLARE v_country_code COUNTRIES.country_id%TYPE := ' &country_code') "City",; v_city LOCATIONS.city%TYPE; COUNT v_city_letter CHAR(DISTINCT job_id1) "# of Jobs";FROM v_street LOCATIONS.street_address%TYPE; v_street_len employeesNUMBER;RIGHT OUTER JOIN v_state_province departments USING (department_id)LOCATIONS.state_province%TYPE := ' ';BEGINLEFT OUTER JOIN locations USING ( SELECT location_id)GROUP BY department_id, department_namestreet_address, cityINTO :b_loc_id, v_street, v_city FROM LOCATIONS WHERE country_id = v_country_code AND state_province IS NULL;
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''
Yjeong
1
edit
Retrieved from "https://wiki.cdot.senecapolytechnic.ca/wiki/Special:MobileDiff/71352"