PLSQL高级功能培训示例脚本

DECLARE v_body_clob CLOB; v_body_long LONG; /* 定义一个记录类型 */ TYPE mail_content IS RECORD( scheduled_start_date VARCHAR2(30), department_code VARCHAR2(30), wip_entity_name VARCHAR2(30), item_segs VARCHAR2(80), item_name VARCHAR2(400), narrow_extend_kit_status VARCHAR2(10), standard_kit_count VARCHAR2(10), jit_calc_count VARCHAR2(10), narrow_extend_kit_count VARCHAR2(10)); /* 定义一个索引表类型 */ TYPE rec_list IS TABLE OF mail_content INDEX BY BINARY_INTEGER; /* 声明一个索引表变量v_rec_list */ v_rec_list rec_list; /* 声明一个BINARY_INTEGER类型的变量 */ tableindex BINARY_INTEGER; CURSOR c_detail IS SELECT to_char(trunc(t.scheduled_start_date), 'yyyy-mm-dd') scheduled_start_date, /*作业开工日期*/ bd.department_code, /*车间*/ wip_entity_name /*作业*/, item_segs /* 装配件编码*/, substr(item_name, 1, 10) item_name /*装配件*/, t.narrow_extend_kit_status, /*狭义扩展状态*/ to_char(nvl(standard_kit_count, 0)) standard_kit_count /*标准缺料个数*/, to_char(nvl(jit_calc_count, 0)) jit_calc_count /*恰时缺料个数*/, to_char(nvl(narrow_extend_kit_count, 0)) narrow_extend_kit_count /*狭义缺料个数*/ FROM apps.cux_fd_wip_entities_v t, bom.bom_departments bd WHERE t.organization_id = 173 AND t.department_id = bd.department_id AND t.organization_id = bd.organization_id AND to_number(to_char(t.scheduled_start_date, 'yyyymmdd')) = 20071215 AND t.status_type = 3 AND NOT (t.standard_kit_status IS NULL AND t.jit_calc_status IS NULL) ORDER BY trunc(t.scheduled_start_date), bd.department_code, wip_entity_name; CURSOR c_demo IS SELECT table_index, mail_body FROM cux.cux_long_test ORDER BY table_index; BEGIN tableindex := 0; FOR cur IN c_detail LOOP tableindex := tableindex + 1; v_rec_list(tableindex).scheduled_start_date := cur.scheduled_start_date; v_rec_list(tableindex).department_code := cur.department_code; v_rec_list(tableindex).wip_entity_name := cur.wip_entity_name; v_rec_list(tableindex).item_segs := cur.item_segs; v_rec_list(tableindex).item_name := cur.item_name; v_rec_list(tableindex).narrow_extend_kit_status := cur.narrow_extend_kit_status; v_rec_list(tableindex).standard_kit_count := cur.standard_kit_count; v_rec_list(tableindex).jit_calc_count := cur.jit_calc_count; v_rec_list(tableindex).narrow_extend_kit_count := cur.narrow_extend_kit_count; END LOOP; DELETE FROM cux.cux_long_test; COMMIT; /* 假设每封邮件只能发送50条记录 */ FOR i IN 0 .. ceil(v_rec_list.COUNT / 50) - 1 LOOP v_body_clob := ''; FOR j IN i * 50 + 1 .. i * 50 + 50 LOOP IF j <= tableindex THEN /*v_body_clob := v_body_clob || v_rec_list(j) .scheduled_start_date || v_rec_list(j) .department_code || v_rec_list(j) .wip_entity_name || v_rec_list(j) .item_segs || v_rec_list(j).item_name || v_rec_list(j) .narrow_extend_kit_status || v_rec_list(j) .standard_kit_count || v_rec_list(j) .jit_calc_count || v_rec_list(j).narrow_extend_kit_count;*/ v_body_clob := concat(v_body_clob, v_rec_list(j) .scheduled_start_date || v_rec_list(j) .department_code || v_rec_list(j) .wip_entity_name || v_rec_list(j) .item_segs || v_rec_list(j) .item_name || v_rec_list(j) .narrow_extend_kit_status || v_rec_list(j) .standard_kit_count || v_rec_list(j) .jit_calc_count || v_rec_list(j) .narrow_extend_kit_count); END IF; END LOOP; INSERT INTO cux.cux_long_test (table_index, mail_body) VALUES (i + 1, v_body_clob); COMMIT; END LOOP; FOR c IN c_demo LOOP v_body_long := c.mail_body; dbms_output.put_line('第' || to_char(c.table_index) || '封邮件正文的长度为：' || to_char(length(v_body_long))); /* 此处调用发邮件的过程 */ END LOOP; END;