SQL优化示例：提升查询性能与代码实践

procedure get_risk_to_match(in_match_type  in varchar2,
                                in_rownum      in number,
                                in_by_priority in varchar2,
                                out_cursor     out cur_type) is

        type match_id_type is table of t_carcase_risk_to_match.match_id%type;

        cur           cur_type;
        rec           match_id_type;
        v_cnt         number;
        v_updated_cnt number;
        v_sql         varchar2(2000) := '';
        v_sql_select1 varchar2(50) := 'select match_id ';
        v_sql_select2 varchar2(200) := 'select match_id, damage_id, tache_id, send_status, match_type, notice_no, priority_level, rm_result, company_code, low_user_name, task_no, email_title, mail_content, email_receiver ';

    begin
        if in_by_priority = 'Y' then
            v_sql := v_sql || v_sql_select1;
            v_sql := v_sql || 'from ( ';
            v_sql := v_sql || v_sql_select1;
            v_sql := v_sql || 'from t_carcase_risk_to_match a ';
            v_sql := v_sql || 'where a.send_status = ''0'' ';
            if in_match_type is null then
                v_sql := v_sql || 'and a.match_type is null ';
            else
                v_sql := v_sql || 'and a.match_type = ''' || in_match_type || ''' ';
            end if;
            v_sql := v_sql || 'order by a.priority_level, a.updated_date) b ';
            if in_rownum = 1 then
                v_sql := v_sql || 'where rownum = 1 ';