PLSQL程序编写杂烦数据表信息编写批量排版

--PLSQL程序编写杂烦数据表信息编写批量排版
SELECT 'cra.' || lower(t.column_name) ||','
  FROM dba_tab_columns t
 WHERE t.table_name = 'CUX_REBATE_APPLY'
 ORDER BY t.column_id;
 --结果
cra.apply_id,
cra.header_id,
cra.line_number,
cra.apply_amount,
cra.apply_method_code,
cra.apply_source_id,
cra.apply_line_num,
cra.oms_flag,
cra.remark,
cra.description,
cra.source_code,
cra.source_line_id,
cra.source_reference,
cra.process_group_id,
cra.process_date,
cra.process_message,
cra.comments,
cra.row_version_number,
cra.creation_date,
cra.created_by,
cra.last_updated_by,
cra.last_update_date,
cra.last_update_login,
cra.program_application_id,
cra.program_id,
cra.program_update_date,
cra.request_id,
cra.attribute_category,
cra.attribute1,
cra.attribute2,
cra.attribute3,
cra.attribute4,
cra.attribute5,
cra.attribute6,
cra.attribute7,
cra.attribute8,
cra.attribute9,
cra.attribute10,
cra.attribute11,
cra.attribute12,
cra.attribute13,
cra.attribute14,
cra.attribute15,
 
 SELECT  'l_CUX_INV_LOT_NUMBER.'||lower(t.column_name) || ':= NULL;'
  FROM dba_tab_columns t
 WHERE t.table_name = 'CUX_INV_LOT_NUMBER'
 ORDER BY t.column_id;
 --结果
 l_CUX_INV_LOT_NUMBER.id:= NULL;
l_CUX_INV_LOT_NUMBER.lot_number:= NULL;
l_CUX_INV_LOT_NUMBER.lot_date:= NULL;
l_CUX_INV_LOT_NUMBER.remark:= NULL;
l_CUX_INV_LOT_NUMBER.description:= NULL;
l_CUX_INV_LOT_NUMBER.source_code:= NULL;
l_CUX_INV_LOT_NUMBER.source_line_id:= NULL;
l_CUX_INV_LOT_NUMBER.source_reference:= NULL;
l_CUX_INV_LOT_NUMBER.process_group_id:= NULL;
l_CUX_INV_LOT_NUMBER.process_status:= NULL;
l_CUX_INV_LOT_NUMBER.process_date:= NULL;
l_CUX_INV_LOT_NUMBER.process_message:= NULL;
l_CUX_INV_LOT_NUMBER.comments:= NULL;
l_CUX_INV_LOT_NUMBER.row_version_number:= NULL;
l_CUX_INV_LOT_NUMBER.creation_date:= NULL;
l_CUX_INV_LOT_NUMBER.created_by:= NULL;
l_CUX_INV_LOT_NUMBER.last_updated_by:= NULL;
l_CUX_INV_LOT_NUMBER.last_update_date:= NULL;
l_CUX_INV_LOT_NUMBER.last_update_login:= NULL;
l_CUX_INV_LOT_NUMBER.program_application_id:= NULL;
l_CUX_INV_LOT_NUMBER.program_id:= NULL;
l_CUX_INV_LOT_NUMBER.program_update_date:= NULL;
l_CUX_INV_LOT_NUMBER.request_id:= NULL;
l_CUX_INV_LOT_NUMBER.attribute_category:= NULL;
l_CUX_INV_LOT_NUMBER.attribute1:= NULL;
l_CUX_INV_LOT_NUMBER.attribute2:= NULL;
l_CUX_INV_LOT_NUMBER.attribute3:= NULL;
l_CUX_INV_LOT_NUMBER.attribute4:= NULL;
l_CUX_INV_LOT_NUMBER.attribute5:= NULL;
l_CUX_INV_LOT_NUMBER.attribute6:= NULL;
l_CUX_INV_LOT_NUMBER.attribute7:= NULL;
l_CUX_INV_LOT_NUMBER.attribute8:= NULL;
l_CUX_INV_LOT_NUMBER.attribute9:= NULL;
l_CUX_INV_LOT_NUMBER.attribute10:= NULL;
l_CUX_INV_LOT_NUMBER.attribute11:= NULL;
l_CUX_INV_LOT_NUMBER.attribute12:= NULL;
l_CUX_INV_LOT_NUMBER.attribute13:= NULL;
l_CUX_INV_LOT_NUMBER.attribute14:= NULL;
l_CUX_INV_LOT_NUMBER.attribute15:= NULL;
 

 SELECT lower(t.column_name)||' '||'cux_inv_txn_lot.' || lower(t.column_name)||'%type,'
  FROM dba_tab_columns t
 WHERE t.table_name = 'CUX_INV_TXN_LOT'
 ORDER BY t.column_id;
--结果
detail_id cux_inv_txn_lot.detail_id%type,
line_id cux_inv_txn_lot.line_id%type,
header_id cux_inv_txn_lot.header_id%type,
organization_id cux_inv_txn_lot.organization_id%type,
inventory_item_id cux_inv_txn_lot.inventory_item_id%type,
lot_number cux_inv_txn_lot.lot_number%type,
transaction_quantity cux_inv_txn_lot.transaction_quantity%type,
lot_expiration_date cux_inv_txn_lot.lot_expiration_date%type,
transaction_id cux_inv_txn_lot.transaction_id%type,
remark cux_inv_txn_lot.remark%type,
group_id cux_inv_txn_lot.group_id%type,
description cux_inv_txn_lot.description%type,
source_code cux_inv_txn_lot.source_code%type,
source_line_id cux_inv_txn_lot.source_line_id%type,
source_reference cux_inv_txn_lot.source_reference%type,
process_group_id cux_inv_txn_lot.process_group_id%type,
process_status cux_inv_txn_lot.process_status%type,
process_date cux_inv_txn_lot.process_date%type,
process_message cux_inv_txn_lot.process_message%type,
comments cux_inv_txn_lot.comments%type,
row_version_number cux_inv_txn_lot.row_version_number%type,
creation_date cux_inv_txn_lot.creation_date%type,
created_by cux_inv_txn_lot.created_by%type,
last_updated_by cux_inv_txn_lot.last_updated_by%type,
last_update_date cux_inv_txn_lot.last_update_date%type,
last_update_login cux_inv_txn_lot.last_update_login%type,
program_application_id cux_inv_txn_lot.program_application_id%type,
program_id cux_inv_txn_lot.program_id%type,
program_update_date cux_inv_txn_lot.program_update_date%type,
request_id cux_inv_txn_lot.request_id%type,
attribute_category cux_inv_txn_lot.attribute_category%type,
attribute1 cux_inv_txn_lot.attribute1%type,
attribute2 cux_inv_txn_lot.attribute2%type,
attribute3 cux_inv_txn_lot.attribute3%type,
attribute4 cux_inv_txn_lot.attribute4%type,
attribute5 cux_inv_txn_lot.attribute5%type,
attribute6 cux_inv_txn_lot.attribute6%type,
attribute7 cux_inv_txn_lot.attribute7%type,
attribute8 cux_inv_txn_lot.attribute8%type,
attribute9 cux_inv_txn_lot.attribute9%type,
attribute10 cux_inv_txn_lot.attribute10%type,
attribute11 cux_inv_txn_lot.attribute11%type,
attribute12 cux_inv_txn_lot.attribute12%type,
attribute13 cux_inv_txn_lot.attribute13%type,
attribute14 cux_inv_txn_lot.attribute14%type,
attribute15 cux_inv_txn_lot.attribute15%type,

原文地址:https://www.cnblogs.com/wanghang/p/6299524.html