帮媳妇写了个pl/sql存储过程，从256个表中查询出指定字段数据，表名是"chegva.table_000001"这种结构，使用的是Toad for Oracle工具，由于之前都没搞过，从零开始边谷歌边学。搞了差不多三个小时才弄出来，真是想哭，会写pl/sql存储过程，数据库增删改查的效率会高很多。脚本如下：
SET serveroutput ON; DECLARE TYPE cur_type IS REF CURSOR; cu cur_type; s_table VARCHAR2(50) := 'chegva.table_'; s_sql VARCHAR2(200); sign_id VARCHAR2(50); tms VARCHAR2(50); BEGIEN dbms_output.enable(null); FOR n IN 1..256 LOOP s_sql := 'select sign_id,tms from ' || s_table || lpad(n, 6, '0') || ' where ETC_ID = :1 and ar_id = :2 and to_char(tms, :3) > :4'; OPEN cu FOR s_sql USING 'BJ', '110112', 'yyyymmdd', '20200720'; LOOP FETCH cu into sign_id, tms; EXIT WHEN cu%NOTFOUND; dbms_output.put_line(sign_id || ' ' || tms); END LOOP; CLOSE cu; -- execute immediate s_sql; END LOOP; END
具体参考如下：
PL/SQL:open for [using] 语句_学孩无牙哭做粥-CSDN博客_open cursor for using
动态 SQL、EXECUTE IMMEDIATE、using、into、returning_我思，故我在！--My data life-CSDN博客_execute immediate into
