If you are confused about the stored procedure of Oracle cursor trigger in the actual operation of Oracle cursor trigger storage, you can use the following articles to learn about its practical application and functions. The following is a detailed introduction of the article.
- create or replace procedure Pro_rateCalculate as
- declare
- v_sc_id dim_service_center.sc_id%TYPE;
Declare a temporary variable to store the repair center id
V_count3 number; number of unqualified instances in three months
V_allcount3 number; number of access failures within three months
V_count6 number; Access time difference. If the value is greater than 0, it indicates the service center six months ago.
- v_allcount6 number;
- v_datediff number;
- v_rate number;
- cursor cur_sc_id is
- select sc_id from dim_service_center;
Declare a cursor that identifies the service center id
- begin
- open cur_sc_id;
- LOOP
- FETCH cur_sc_id
- INTO v_sc_id;
In the stored procedure of the Oracle cursor trigger, we need to read a cursor data to v_ SC _id
Exit when cur_ SC _id % NOTFOUND; EXIT if the last read
Select count (*) calculates the number of unqualified data records in the master table for three consecutive months.
- into v_count3
- from dim_service_center
- inner join fact_repair_sheet on dim_service_center.sc_id =
- fact_repair_sheet.sc_id
- inner join fact_vefpart on fact_vefpart.repid =
- fact_repair_sheet.repair_sheet_id
- where ((fact_vefpart.vef_result1 = '2'
The initial check result is unqualified.
And fact_vefpart.MAJOR_FLAG = '2') and does not need to be reviewed.
Or (fact_vefpart.vef_result2 = '2') or the review result is unqualified.
And fact_vefpart.ismajor = '1' and is the main spare part.
And fact_repair_sheet.close_date> Add_months (sysdate,-3) is data within three months.
And dim_service_center. SC _id = v_ SC _id;
V_allcount3: = v_count3; assign the quantity of unqualified spare parts data to the total quantity counter
Select count (*) calculates the number of unqualified data records for three consecutive months during a call return visit.
- into v_count3
- from fact_verify
- inner join fact_repair_sheet on fact_verify.repid =
- fact_repair_sheet.repair_sheet_id
- inner join dim_service_center on dim_service_center.sc_id =
- fact_repair_sheet.sc_id
- where fact_verify.
CALLSTATUS = 'none' unqualified return visit
And fact_repair_sheet.close_date> Add_months (sysdate,-3) within three months
And dim_service_center. SC _id = v_ SC _id;
V_allcount3: = v_allcount3 + v_count3; calculate the total number of unqualified instances for three consecutive months.
The above content introduces the stored procedure of the Oracle cursor trigger. I hope you will find some gains.