Oracle uses cursor trigger storage practices

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.