PLSQL 学习之路（2）分页查询

PROCEDURE proc_query_organization_emps(p_request  CLOB,

                                         x_response OUT CLOB) IS

    v_api             VARCHAR2(100) := ‘proc_query_organization_emps‘;

    v_request         json;

    v_response        pl_json := pl_json;

    v_organization_id NUMBER;

    v_page            NUMBER;

    v_size            NUMBER;

    v_line            pl_json := pl_json;

    v_start_rownum    NUMBER;

    v_end_rownum      NUMBER;

    v_userlist        pl_json := pl_json;

    v_total           NUMBER;

    CURSOR v_orge_cur IS

      SELECT *

        FROM (SELECT dfoe.org_employee_id,

                     dfe.employee_code,

                     dfe.employee_name,

                     dfe.phone,

                     dfe.email,

                     rownum AS rowno

                FROM dfnd.dfnd_employees dfe, dfnd.dfnd_org_employees dfoe

               WHERE dfe.employee_code = dfoe.employee_code

                 AND dfoe.organization_id = v_organization_id

               ORDER BY dfoe.org_employee_id)

       WHERE rowno BETWEEN v_start_rownum AND v_end_rownum;

  BEGIN

    v_request         := json(p_request);

    v_organization_id := v_request.get(‘organizationId‘).get_number;

    v_page            := nvl(v_request.get(‘page‘).get_number, 1);

    v_size            := nvl(v_request.get(‘size‘).get_number, 25);

    IF v_page = 1 THEN

      v_start_rownum := v_page;

    ELSE

      v_start_rownum := (v_page - 1) * v_size;

    END IF;

    v_end_rownum := v_start_rownum + v_size;

    FOR v_orge IN v_orge_cur LOOP

      v_line := pl_json;

      v_line.set_value(‘userId‘, v_orge.org_employee_id);

      v_line.set_value(‘userCode‘, v_orge.employee_code);

      v_line.set_value(‘userName‘, v_orge.employee_name);

      v_line.set_value(‘userPhone‘, v_orge.phone);

      v_line.set_value(‘userEmail‘, v_orge.email);

      v_userlist.add_list_item(‘userList‘, v_line);

    END LOOP;

    SELECT COUNT(*) INTO v_total FROM dfnd.dfnd_org_employees;

    v_response := v_userlist;

    v_response.set_value(‘totalEmp‘, v_total);

    x_response := v_response.to_json;

  EXCEPTION

    WHEN OTHERS THEN

      ROLLBACK;

      v_response.fail(‘接口‘ || v_api || ‘发生错误,错误原因:‘ || SQLERRM);

      x_response := v_response.to_json;

  END proc_query_organization_emps;

PLSQL 学习之路（2）分页查询

标签：from   rom   back   employees   ati   sql   ota   else   and