Scenario:
============
Three intelligence sensors are used to keep the temperature and humidity of an Area XYZ .
Create a table that will be host sensors results.
Create a procedure to add the temperature (Celsius) and humidity per sensor.
Create a procedure to display temperature (Fahrenheit or Celsius) and humidity per sensor.
Use the TABLE expression to display the table's results
Note : You ust use a NESTED TABLE for your COLLECTION.
create or replace type sensor_measure is object
(
temperature number,
humidity number
);
/
create or replace type sensor_measure_tab as table of sensor_measure;
/
create table sensors
(
sensor_id number primary key,
sensor_name char(1) unique,
location char(3)
);
create table weather
(
sensor_id number,
m_date number(8) not null,
time number(4) not null,
s_measures sensor_measure_tab
)
NESTED TABLE s_measures STORE AS s_measures_storage;
alter table weather add constraint weather_fk foreign key(sensor_id) references sensors ( sensor_id);
insert into sensors values ( 1,'A','XYZ');
insert into sensors values ( 2,'B','XYZ');
insert into sensors values ( 3,'C','XYZ');
commit;
-------------------------------------------------------
CREATE OR REPLACE PROCEDURE ADD_SENSOR_MEASURES
(
SENSOR_ID_ IN NUMBER
, TEMPERATURE_ IN NUMBER
, HUMIDITY_ IN NUMBER
)
AS
sm sensor_measure;
s sensor_measure_tab;
i integer;
m_date_ number;
r_date_ number;
BEGIN
sm:=sensor_measure(TEMPERATURE_,HUMIDITY_);
s:=sensor_measure_tab(sm);
m_date_ := to_number(to_char(sysdate,'YYYYMMDD'));
r_date_ := to_number(to_char(sysdate,'HH24MI'));
insert into weather values (
SENSOR_ID_,
m_date_,
r_date_,
s);
commit;
END ADD_SENSOR_MEASURES;
--------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE PRINT_WEATHER_PER_SENSOR
(
SENSOR_ID_ IN NUMBER
)
AS
cursor data_(sensorid number) is
select w.sensor_id, w.m_date,w.time,w.s_measures
from weather w
where w.sensor_id=SENSORID;
r data_%ROWTYPE;
s sensor_measure_tab;
sm sensor_measure;
BEGIN
dbms_output.put_line('Data for sensor:'||SENSOR_ID_);
For r_data IN data_(sensor_id_) loop
for i in r_data.s_measures.first..r_data.s_measures.last loop
sm:=r_data.s_measures(i);
dbms_output.put_line('Date:'||r_data.m_date||' '||'Time:'||r_data.time||' Temperature:'||sm.temperature||' Humidity:'||sm.humidity);
end loop;
end loop;
END PRINT_WEATHER_PER_SENSOR;
------------------------------------------------------------------------------------------------------------
select w.sensor_id, w.m_date,time,ws.temperature,ws.humidity from weather w,table(w.s_measures) ws
