从Oracle数据库中读取数据，自动生成INSERT语句

来源：互联网发布：c语言中使用时钟陷阱编辑：程序博客网时间：2024/05/21 12:40

--Create Table：

DROP TABLE Advance_Query;
CREATE TABLE Advance_Query
(
Tbl   VARCHAR2(30) NOT NULL, --表名称 PK
Form   VARCHAR2(30) NOT NULL, --所属窗体名称 pk
Field   VARCHAR2(30) NOT NULL, --字段名称 pk
By_Name   VARCHAR2(30) NOT NULL, --字段别名
Type   VARCHAR2(1) NOT NULL, --字段类型名称
Flag   VARCHAR2(1) NOT NULL, --标示 0：利用下拉框 1：用文本框
AreaSQL   VARCHAR2(150),  --当前值对应的查询语句适合于：flag=0时使用
CONSTRAINT PK_Text PRIMARY KEY(Tbl,Form,Field)
);

--Execute SQL Query:

select 'INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES(' || '''' || Tbl ||'''' || ','
|| '''' || Form || '''' || ','
|| '''' || Field || '''' || ','
|| '''' || By_Name || '''' || ','
|| '''' || Type || '''' || ','
|| '''' || Flag || '''' || ','
|| '''' || AreaSQL || '''' || ');'
From Advance_Query order by form

Result:

INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES('AD_Manage','AD_Manage','Input_Date','录入时间','D','1','');
  INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES('AD_Manage','AD_Manage','Charge_Money','费用','N','1','');
  INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES('AD_Manage','AD_Manage','Provide_Company','代理公司','S','1','');
  INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES('AD_Manage','AD_Manage','Linkman','联系人','S','1','');
  INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES('AD_Manage','AD_Manage','Play_Media','投放媒体','S','1','');
  INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES('AD_Manage','AD_Manage','AD_Topic','广告主题','S','1','');
  INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES('Dict_AD_Media_Type','AD_Manage','Media_Type','媒体类型','S','0','SELECT AD_Media_Type_Name as name,AD_Media_Type_Code as code FROM Dict_AD_Media_Type WHERE If_Using=1 ');
  INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES('Sale_Chance_Info','Compete_Adversary_Manage','Chance_Topic','销售机会主题','S','1','');
  INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES('Compete_Adversary','Compete_Adversary_Manage','Input_Date','录入时间','D','1','');
  INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES('Compete_Adversary','Compete_Adversary_Manage','Price','竞争对手价格','N','1','');
  INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES('Compete_Adversary','Compete_Adversary_Manage','Company_Name','竞争公司名称','S','1','');
  INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES('V_Customer_Info','Customer_Charge','Customer_Name','客户名称','S','1','');
  INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES('Customer_Charge','Customer_Charge','Reg_User_Name','经手人','S','1','');
  INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES('Customer_Charge','Customer_Charge','Charge_Money','费用金额','N','1','');
  INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES('Customer_Charge','Customer_Charge','Ticket_Counts','票据张数','N','1','');
  INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES('Dict_Customer_Charge_Type','Customer_Charge','Charge_Type','票据类型','S','0','SELECT Charge_Type_Name as name,Charge_Type_Code as code FROM Dict_Customer_Charge_Type WHERE If_Using=1 ');
  INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES('Customer_Charge','Customer_Charge','Charge_Date','费用发生日期','D','1','');
  INSERT INTO Advance_Query (Tbl,Form,Field,By_Name,Type,Flag,AreaSQL)
VALUES('Customer_Law','Customer_Law','Law_Limit_Date','投诉时间限制','D','1','');

......