Oracle uses cursor trigger storage practices

Source: Internet
Author: User
Tags oracle cursor

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.

 
 
  1. create or replace procedure Pro_rateCalculate as  
  2. declare  
  3. 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.

 
 
  1. v_allcount6 number;  
  2. v_datediff number;  
  3. v_rate number;  
  4. cursor cur_sc_id is  
  5. select sc_id from dim_service_center;   

Declare a cursor that identifies the service center id

 
 
  1. begin  
  2. open cur_sc_id;  
  3. LOOP  
  4. FETCH cur_sc_id  
  5. 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.

 
 
  1. into v_count3  
  2. from dim_service_center  
  3. inner join fact_repair_sheet on dim_service_center.sc_id =  
  4. fact_repair_sheet.sc_id  
  5. inner join fact_vefpart on fact_vefpart.repid =  
  6. fact_repair_sheet.repair_sheet_id  
  7. 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.

 
 
  1. into v_count3  
  2. from fact_verify  
  3. inner join fact_repair_sheet on fact_verify.repid =  
  4. fact_repair_sheet.repair_sheet_id  
  5. inner join dim_service_center on dim_service_center.sc_id =  
  6. fact_repair_sheet.sc_id  
  7. 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.