Question

PL/SQL gap-fill question:

For the following stored procedure, please fill in the missing information in the underlined areas:

create or replace procedure get_items_shipped_capt as

cursor list_items(cid in captain.capt_id%type) is
select item.item_no,_____________________ ,weight from item,shipment_line,shipment
where item.item_no = ____________________________ and shipment_line.shipment_id = shipment.shipment_id and shipment.capt_id = ___________;
cursor all_captains is
select capt_id
from captain;
TB constant char(1) := CHR(9);
begin
for cp in _____________ loop

dbms_output.put_line(Captain ID: '||cp.capt_id); dbms_output.new_line;
dbms_output.put_line('Item Number'||TB||'Description'||TB||'Weight');

dbms_output.put_line('=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+ =+=+');

for det in list_items(______________) loop

dbms_output.put_line(det.item_no||TB||det.description||TB|| det.weight);

end loop; dbms_output.new_line; end loop;

end;

The following pictures are for reference only, not sure if it has anything to do with this question.

captain ship ship_no ship class capacity purch date 09 manufacturer_id shipment SHIPMENT ID SHIP_NO CAPT ID SHIPMENT DATE ORIGIN DESTINATION ARRIVAL DATE capt_id license grade fname Iname dob 重新加载此页 manufacturer Manufacturer ID Name City State Rep Id Bid Preference distance ORIGIN DESTINATION MILES DAYS shipment_line SHIPMENT_ID ITEM_NO QUANTITY item item_no item type description weight

Answer 1

The blanks are marked in bold.

create or replace procedure get_items_shipped_capt as

cursor list_items(cid in captain.capt_id%type) is
select item.item_no, item.description , item.weight from item,shipment_line,shipment
where item.item_no = shipment_line.item_no and shipment_line.shipment_id = shipment.shipment_id and shipment.capt_id = cid;

cursor all_captains is
select capt_id
from captain;
TB constant char(1) := CHR(9);
begin
for cp in all_captains loop

dbms_output.put_line('Captain ID: '||cp.capt_id); dbms_output.new_line;
dbms_output.put_line('Item Number'||TB||'Description'||TB||'Weight');

dbms_output.put_line('=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+ =+=+');

for det in list_items(1234) loop

dbms_output.put_line(det.item_no||TB||det.description||TB|| det.weight);

end loop; dbms_output.new_line; end loop;

end;

1. item_no is selected from the table item.
2. Then, in second blank: two tables item and shipment_line are joined using common column 'item_no'. Therefore, item.item_line=shipment_line.item_no
3. In third blank, whatever captain id is accepted by cursor will be assigned to shipment.capt_id. Therefore, shipment.capt_id=cid;
4. Now run a loop for all the data in cursor all_captains. Hence in 4th blank we will write: for cp in all_captains
5. In 5th block pass the(required) value of captain id to the cursor.