Oracle EBS - SQL&PL/SQL: 动态SQL和动态游标

最新推荐文章于 2025-02-27 16:26:45 发布

原创最新推荐文章于 2025-02-27 16:26:45 发布 · 366 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#动态游标 #拼接游标字符串 #动态SQL

SQL & PL/SQL 专栏收录该内容

12 篇文章

订阅专栏

动态SQL

/*SELECT msi.inventory_item_id, msi.segment1
  FROM mtl_system_items_b msi
 WHERE msi.organization_id = 142
   AND msi.inventory_item_id = 26505;*/

DECLARE
  p_organization_id   NUMBER := 142;
  p_inventory_item_id NUMBER := 26505;
  l_item_number       VARCHAR2(240);
BEGIN
  EXECUTE IMMEDIATE 
'select segment1
   from mtl_system_items_b msi
  where msi.organization_id = :1 and msi.inventory_item_id = :2'
    INTO l_item_number
    USING p_organization_id, p_inventory_item_id;
  dbms_output.put_line('l_item_number ：' || l_item_number);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(dbms_utility.format_error_backtrace);
    dbms_output.put_line(dbms_utility.format_error_stack);
    dbms_output.put_line('no data found');
END;

动态游标

可在游标字符串里拼接指定定字符串，

或者通过”：“指定这是一个被拼接的字符串，最后在打开游标是使用using把确定的字符串拼接进去。

FUNCTION get_old_character_value(l_plan_id       IN NUMBER,
                                 l_organization  IN NUMBER,
                                 l_wip_entity_id IN NUMBER,
                                 l_char_name     IN VARCHAR2) RETURN VARCHAR2 IS
  TYPE result_cur_type IS REF CURSOR;
  result_cur           result_cur_type;
  l_sql                VARCHAR2(500);
  l_character_number   VARCHAR2(20);
  l_character_value    VARCHAR2(4000);
  BEGIN
    l_character_number := get_character_number(l_plan_id,
                                               l_char_name);
    l_sql := 'SELECT DISTINCT qr.' || l_character_number ||
             ' FROM qa_results qr 
              WHERE qr.plan_id = :1 
              AND qr.organization_id = :2 
              AND qr.wip_entity_id = :3';
    --log(l_sql);
    OPEN result_cur FOR l_sql
      USING l_plan_id, l_organization, l_wip_entity_id;
    FETCH result_cur
    INTO l_character_value;
    CLOSE result_cur;
    
    RETURN l_character_value;
  END;