ORACLE Develop Example 1
Description:
Time series analysis
Requirement: below is the sturcture of the tables
Table ABI_DATA
Name
|
Type
|
Nullable
|
Default
|
ID
|
INTEGER
|
No
|
N/A
|
PRICE_DATE
|
DATE
|
Yes
|
N/A
|
PRICE
|
NUMBER
|
Yes
|
N/A
|
Table ABI_MT
Name
|
Type
|
Nullable
|
Default
|
ID
|
INTEGER
|
Yes
|
N/A
|
MT
|
NUMBER
|
Yes
|
N/A
|
Table ABI_QTET
Name
|
Type
|
Nullable
|
Default
|
ID
|
INTEGER
|
Yes
|
N/A
|
QTET
|
NUMBER
|
Yes
|
N/A
|
Table ABI_QT
Name
|
Type
|
Nullable
|
Default
|
ID
|
INTEGER
|
Yes
|
N/A
|
QT
|
NUMBER
|
Yes
|
N/A
|
Table ABI_MTET
Name
|
Type
|
Nullable
|
Default
|
ID
|
INTEGER
|
Yes
|
N/A
|
MTET
|
NUMBER
|
Yes
|
N/A
|
Table ABI_RESULT
Name
|
Type
|
Nullable
|
Default
|
ID
|
INTEGER
|
Yes
|
N/A
|
PRICE
|
NUMBER
|
Yes
|
N/A
|
Oracle Procedure
Use PL/SQL Developer tool and command to trigger the oracle procedure ABI_DATA_PROCESS. The oracle procedure will clear the pervious data and information which saved in the several internal and result tables: ABI_MT, ABI_QTET, ABI_QT, ABI_MTET and ABI_RESULT.
Oracle Function 1
The oracle procedure ABI_DATA_PROCESS will call the function ABI_PROCESS_1. This function will generate the MT result following the algorithms
. In this function, we define the moving average H as 3. Using the method is
. It will calculation will start from the second value of ABI_DATA to the last but one. And insert the result into the table ABI_MT. If something got problem, it will reply “FALSE” to the procedure, the program will stop. Otherwise it will return “TRUE”, the program will continue.
. In this function, we define the moving average H as 3. Using the method is. It will calculation will start from the second value of ABI_DATA to the last but one. And insert the result into the table ABI_MT. If something got problem, it will reply “FALSE” to the procedure, the program will stop. Otherwise it will return “TRUE”, the program will continue.
Oracle Function 2
If the ABI_PROCESS_1 function finishes without problem, it will send “TRUE” value to the procedure ABI_DATA_PROCESS. It will trigger the function ABI_PROCESS_2 to start. This function will generate the QTET result following the algorithms. In this function, it will get data from ABI_DATA which starting from the second one and ABI_MT which starting from the first one. After that, it will use the value from ABI_DATA divided by that from ABI_MT until the last number in ABI_MT. And insert the result into the table ABI_QTET. If something got problem, it will reply “FALSE” to the procedure, the program will stop. Otherwise it will return “TRUE”, the program will continue.
Oracle Function 3
If the ABI_PROCESS_2 function finishes without problem, it will send “TRUE” value to the procedure ABI_DATA_PROCESS. It will trigger the function ABI_PROCESS_3 to start. This function will generate the QT result following the algorithms. In this function, it will get the data from MT_QTET starting from the first one, the fourth one, the seventh one and go on. It means the values with interval three. Then it calculates the average value of these values. After this, it will start from the second one, the fifth one, the eighth one and go on, calculating the average value of these values. Same process again. After getting the three values, it uses these three values to insert into the table MT_QT. If something got problem, it will reply “FALSE” to the procedure, the program will stop. Otherwise it will return “TRUE”, the program will continue.
Oracle Function 4
If the ABI_PROCESS_3 function finishes without problem, it will send “TRUE” value to the procedure ABI_DATA_PROCESS. It will trigger the function ABI_PROCESS_4 to start. This function will generate the MTET result following the algorithms. In this function, it will get the data from ABI_QT starting from the first one and the data from ABI_DATA starting from the second one. After that, it will use the value from ABI_DATA divided by that from ABI_QT until the last number in ABI_QT. And insert the result into the table ABI_MTET. If something got problem, it will reply “FALSE” to the procedure, the program will stop. Otherwise it will return “TRUE”, the program will continue.
Oracle Function 5
If the ABI_PROCESS_4 function finishes without problem, it will send “TRUE” value to the procedure ABI_DATA_PROCESS. It will trigger the function ABI_PROCESS_5 to start. This function will generate the program result following the algorithms. In this function, it will get the data from ABI_MTET, and using the method following to generate the value of a and b of the linear function
.
.
After that, using the function 
to calculate the value and insert into table ABI_RESULT. If something got problem, it will reply “FALSE” to the procedure, the program will stop. Otherwise it will return “TRUE”, the program will continue.
to calculate the value and insert into table ABI_RESULT. If something got problem, it will reply “FALSE” to the procedure, the program will stop. Otherwise it will return “TRUE”, the program will continue.
Procedure
create or replace procedure ABI_DATA_PROCESS is
v_result varchar(5);
BEGIN
delete from abi_mt;
delete from abi_qtet;
delete from abi_qt;
delete from abi_mtet;
delete from abi_result;
commit;
v_result := abi_process_1;
IF v_result = 'TRUE' THEN
v_result := abi_process_2;
IF v_result = 'TRUE' THEN
v_result := abi_process_3;
IF v_result = 'TRUE' THEN
v_result := abi_process_4;
IF v_result = 'TRUE' THEN
v_result := abi_process_5;
END IF;
END IF;
END IF;
END IF;
end ABI_DATA_PROCESS;
|
Function 1
create or replace function ABI_PROCESS_1 return varchar is
incorrect exception;
v_abi_data_1 number;
v_abi_data_2 number;
v_abi_data_3 number;
v_abi_data_result number;
v_abi_data_sum number;
v_id number;
v_h number;
v_total number;
v_count number;
BEGIN
v_id:=1;
v_abi_data_1:=0;
v_abi_data_2:=0;
v_abi_data_3:=0;
v_abi_data_result:=0;
v_abi_data_sum:=0;
v_h:=3;
v_count:=(v_h-1)/2 + 1;
select count(*) into v_total from abi_data;
while (v_count < v_total)
LOOP
select price into v_abi_data_1 from abi_data where id=v_count+(v_h-1)/2;
select price into v_abi_data_2 from abi_data where id=v_count+(v_h-1)/2-1;
select price into v_abi_data_3 from abi_data where id=v_count-(v_h-1)/2;
v_abi_data_sum:=v_abi_data_1+v_abi_data_2+v_abi_data_3;
v_abi_data_result:=v_abi_data_sum / 3;
insert into abi_mt(ID,MT) values(v_id, v_abi_data_result);
v_id:=v_id+1;
v_count:=v_count+1;
END LOOP;
commit;
RETURN 'TRUE';
EXCEPTION
WHEN INCORRECT THEN
RETURN 'FALSE';
WHEN OTHERS THEN
RETURN 'FALSE';
END ABI_PROCESS_1;
|
Function 2
create or replace function ABI_PROCESS_2 return varchar2 is
incorrect exception;
v_abi_mt number;
v_abi_price number;
v_abi_data_result number;
v_total number;
v_count number;
v_id number;
BEGIN
v_abi_mt:=0;
v_abi_price:=0;
v_abi_data_result:=0;
v_count:=1;
v_id:=1;
select count(*) into v_total from abi_mt;
while (v_count <= v_total)
LOOP
select mt into v_abi_mt from abi_mt where id=v_count;
select price into v_abi_price from abi_data where id=v_count+1;
v_abi_data_result:=v_abi_price / v_abi_mt;
insert into abi_QTET(ID,QTET) values(v_id, v_abi_data_result);
v_id:=v_id+1;
v_count:=v_count+1;
END LOOP;
commit;
RETURN 'TRUE';
EXCEPTION
WHEN INCORRECT THEN
RETURN 'FALSE';
WHEN OTHERS THEN
RETURN 'FALSE';
end ABI_PROCESS_2;
|
Function 3
create or replace function ABI_PROCESS_3 return varchar2 is
incorrect exception;
v_abi_qtet number;
v_total number;
v_count_1 number;
v_count_2 number;
v_sum number;
v_abi_data_result number;
v_abi_data_result_1 number;
v_abi_data_result_2 number;
v_abi_data_result_3 number;
v_abi_data_sum number;
BEGIN
v_abi_qtet := 0;
v_abi_data_sum := 0;
v_abi_data_result := 0;
v_count_1 := 1;
v_count_2 := 0;
select count(*) into v_total from ABI_QTET;
v_sum := trunc(v_total / 3);
WHILE (v_count_1 <= 3) LOOP
WHILE (v_count_2 < v_sum) LOOP
select QTET
into v_abi_QTET
from ABI_QTET
where id = v_count_2 * 3 + v_count_1;
v_abi_data_sum := v_abi_data_sum + v_abi_qtet;
v_count_2 := v_count_2 + 1;
END LOOP;
v_abi_data_result := v_abi_data_sum / v_sum;
insert into abi_QT (ID, QT) values (v_count_1, v_abi_data_result);
IF v_count_1 = 1 THEN
v_abi_data_result_1 := v_abi_data_result;
ELSE
IF v_count_1 = 2 THEN
v_abi_data_result_2 := v_abi_data_result;
ELSE
v_abi_data_result_3 := v_abi_data_result;
END IF;
END IF;
v_count_1 := v_count_1 + 1;
v_count_2 := 0;
v_abi_data_sum := 0;
END LOOP;
WHILE (v_count_1 <= v_total) LOOP
IF MOD(V_COUNT_1, 3) = 1 THEN
insert into abi_QT (ID, QT) values (v_count_1, v_abi_data_result_1);
ELSE
IF MOD(V_COUNT_1, 3) = 2 THEN
insert into abi_QT
(ID, QT)
values
(v_count_1, v_abi_data_result_2);
ELSE
IF MOD(V_COUNT_1, 3) = 0 THEN
insert into abi_QT
(ID, QT)
values
(v_count_1, v_abi_data_result_3);
END IF;
END IF;
END IF;
v_count_1 := v_count_1 + 1;
END LOOP;
COMMIT;
RETURN 'TRUE';
EXCEPTION
WHEN INCORRECT THEN
RETURN 'FALSE';
WHEN OTHERS THEN
RETURN 'FALSE';
end ABI_PROCESS_3;
|
Function 4
create or replace function ABI_PROCESS_4 return varchar2 is
incorrect exception;
v_abi_qt number;
v_abi_price number;
v_total number;
v_count_1 number;
v_abi_data_result number;
BEGIN
v_abi_qt := 0;
v_abi_price := 0;
v_abi_data_result := 0;
v_count_1 := 1;
select count(*) into v_total from ABI_QT;
WHILE (v_count_1 <= v_total) LOOP
select price into v_abi_price from abi_data where id = v_count_1 + 1;
select qt into v_abi_qt from abi_qt where id = v_count_1;
v_abi_data_result := v_abi_price / v_abi_qt;
insert into abi_mtet values (v_count_1, v_abi_data_result);
v_count_1:=v_count_1+1;
END LOOP;
COMMIT;
RETURN 'TRUE';
EXCEPTION
WHEN INCORRECT THEN
RETURN 'FALSE';
WHEN OTHERS THEN
RETURN 'FALSE';
end ABI_PROCESS_4;
|
Function 5
create or replace function ABI_PROCESS_5 return varchar2 is
incorrect exception;
v_sum_x number;
v_sum_y number;
v_sum_x2 number;
v_sum_xy number;
v_sum_x_2 number;
v_total number;
v_a number;
v_b number;
v_id number;
BEGIN
select sum(id) into v_sum_x from ABI_MTET;
select sum(MTET) into v_sum_y from ABI_MTET;
select sum(id * id) into v_sum_x2 from ABI_MTET;
select sum(id * MTET) into v_sum_xy from ABI_MTET;
select sum(id) * sum(id) into v_sum_x_2 from ABI_MTET;
select count(*) into v_total from ABI_MTET;
v_a := (v_total * v_sum_xy - v_sum_x * v_sum_y) /
(v_total * v_sum_x2 - v_sum_x_2);
v_b := (v_sum_y * v_sum_x2 - v_sum_x * v_sum_xy) /
(v_total * v_sum_x2 - v_sum_x_2);
v_id := 1;
WHILE (v_id <= v_total) loop
insert into abi_result values (v_id, v_id * v_a + v_b);
v_id := v_id + 1;
end loop;
commit;
RETURN 'TRUE';
EXCEPTION
WHEN INCORRECT THEN
RETURN 'FALSE';
WHEN OTHERS THEN
RETURN 'FALSE';
end ABI_PROCESS_5;
|
CREATE OR REPLACE PROCEDURE VER_COMPARE
AS
cursor mycur is --声明游标
select * from a;--a为表名
myrec a%rowtype; --声明与表字段相同的记录
begin
open mycur;--打开游标
fetch mycur into myrec;
while mycur%found loop
dbms_output.put_line(myrec.col1||':'||myrec.col2);
fetch mycur into myrec;
--处理语句
end loop;
close mycur;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('记录未找到');
ROLLBACK;
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('sqlerrm : ' ||sqlerrm);
ROLLBACK;
END;
Comments
Post a Comment