sql - 如何在一个块中运行我的查询和事务，然后检查运行时并将结果存储在单独的表中？

Question

我有一个游戏创意，并创建了一些虚拟数据，包括一些查询和交易。但是，我很难将我的查询和事务包含在一个块中以进行测量。我正在使用 Oracle 18c 快递。 我的问题是我找不到正确的语法来运行选择整行甚至表的查询，或者 我在文档中找到的块中的事务不起作用。

这是我的代码（我希望查询和交易成为块的一部分） - 以下几行只是我需要的东西，但它们工作正常：

SET SERVEROUTPUT ON

alter system flush buffer_cache;
alter system flush shared_pool;

CREATE OR REPLACE function timestamp_diff(a timestamp, b timestamp) return number is 
begin
  return extract (day    from (a-b))*24*60*60 +
         extract (hour   from (a-b))*60*60+
         extract (minute from (a-b))*60+
         extract (second from (a-b));
end;
/

在这里，我创建了我的块。这个版本是空的，但它可以运行。

DECLARE 
    inde_x number(4);
    temp_be timestamp;
    temp_af timestamp;
    temp_diff_a long;
    temp_diff_b long;
    temp_diff_c long;
    temp_diff_d long;
    temp_diff_e long;
    temp_diff_f long;
    temp_diff_g long;
BEGIN 
   FOR inde_x in 1 .. 1 LOOP 
    temp_be := current_timestamp;

    Rollback;
    
    temp_af := current_timestamp;
    temp_diff_a := timestamp_diff(temp_af, temp_be);
    dbms_output.put_line(temp_diff_a);

    
    temp_be := current_timestamp;


    
    temp_af := current_timestamp;
    temp_diff_b := timestamp_diff(temp_af, temp_be);
    dbms_output.put_line(temp_diff_b);

    
    temp_be := current_timestamp;


    
    temp_af := current_timestamp;
    temp_diff_c := timestamp_diff(temp_af, temp_be);
    dbms_output.put_line(temp_diff_c);  

    
    temp_be := current_timestamp;


    
    temp_af := current_timestamp;
    temp_diff_d := timestamp_diff(temp_af, temp_be);
    dbms_output.put_line(temp_diff_d);  

    
    temp_be := current_timestamp;


    
    temp_af := current_timestamp;
    temp_diff_e := timestamp_diff(temp_af, temp_be);
    dbms_output.put_line(temp_diff_e);

    
    temp_be := current_timestamp;


    
    temp_af := current_timestamp;
    temp_diff_f := timestamp_diff(temp_af, temp_be);
    dbms_output.put_line(temp_diff_f);
    
    
    temp_be := current_timestamp;
    
    
    
    temp_af := current_timestamp;
    temp_diff_g := timestamp_diff(temp_af, temp_be);
    dbms_output.put_line(temp_diff_g);
    INSERT INTO time_in_ms (run_id, query_one, query_two, query_three, transaction_one,  transaction_two, transaction_three, transaction_four)
    VALUES (inde_x, temp_diff_a, temp_diff_b, temp_diff_c, temp_diff_d, temp_diff_e, temp_diff_f, temp_diff_g);
  END LOOP; 
END; 

这里我有一些示例查询和交易。完成的版本会更大。这些查询和事务都像现在一样工作。但是，我希望他们在他们需要的时间也将被测量的街区中。

--query_one

select villager_id from villagers 
inner join families
on villagers.family_id = families.family_id
inner join houses
on families.house_id = houses.house_id
inner join villages
on houses.village_id = villages.village_id
inner join locations
on villages.location_id = locations.location_id
order by villagers.age;

--query_two

select families.family_id, count(animals.animal_id) from animals
left join villagers
on animals.animal_id = villagers.animal_id
left join families
on villagers.family_id = families.family_id
WHERE families.family_happiness 
between 
(select avg(family_happiness) from families)-0.4
and 
(select avg(family_happiness) from families)+0.4
GROUP by families.family_id
ORDER by families.family_id;

--query_three
--something with dates



--transaction_one

update jobs j
set j.wage = j.wage*(10 + dbms_random.value*0.5)
where j.villager_id in (
select v.villager_id from villagers v
inner join families
on v.family_id = families.family_id
inner join houses
on families.house_id = houses.house_id
inner join villages
on houses.village_id = villages.village_id
inner join locations
on villages.location_id = locations.location_id
where j.location_id != villages.location_id);

Rollback;


--transaction_two
--something that changes strings 


--transaction_three


update jobs j
set j.risk_factor = j.risk_factor/2
WHERE j.job_name NOT LIKE '%De%' or j.job_name NOT LIKE '%Po%' or j.job_name NOT LIKE '%En%'
and j.location_id in (
SELECT l.location_id from locations l
left join villages
on l.location_id = villages.location_id
WHERE l.location_name NOT LIKE '%De%' or l.location_name NOT LIKE '%Po%' or l.location_name NOT LIKE '%En%'
AND villages.village_name NOT LIKE '%De%' or villages.village_name NOT LIKE '%Po%' or villages.village_name NOT LIKE '%En%'
AND l.Max_Number_of_Animals > 10
AND l.food_content < (
SELECT avg(ll.food_content) from locations ll
WHERE ll.Max_Number_of_Animals > 10));

Rollback;

--transaction_four


update animals a 
set a.meat_content = CASE 
WHEN a.domesticated = 'true'
THEN a.meat_content*(1.1 + dbms_random.value*0.30)
ELSE a.meat_content*(1.05 + dbms_random.value*0.15)
END
WHERE a.animal_id in (
select an.animal_id from animals an
full join villagers 
on an.animal_id = villagers.animal_id
full join jobs
on villagers.villager_id = jobs.villager_id
left join locations
on jobs.location_id = locations.location_id
WHERE (jobs.ending_on-starting_on > 2)
OR (locations.food_content > 0.3)
OR villagers.age between 15 and 50);

Rollback;

标签： sqloracle

Answer 1

更新了游标示例：

begin
  for c_record in (
         select families.family_id, count(animals.animal_id) cnt
         from animals 
          ... etc ... ) loop
         --do something with the record
         c_record.family_id
         c_record.cnt
         ..etc..
   end loop;

如果要在 PL/SQL 块中选择值，则需要使用 SELECT... INTO... 您需要声明变量以将 SELECT 的结果插入其中。例子：

declare
 vid number;
begin
select villager_id 
into vid
from villagers 
inner join families.. etc;