英文:
Generate oracle sql script to get all data from table
问题
获取表中的所有数据和所有列，类似以下方式：
从双表中选择1 as id，1 as name，.... --第一行
联合全部
从双表中选择2 as id，2 as name，.... --第二行
...
联合全部
从双表中选择199 as id，199 as name，.... --最后一行
作为一个字符串
输入参数是table_name，这仅适用于小表，如大陆、国家、城市等。
英文:
How get all data and all column from table some like this.
Select 1 as id, 1 as name, .... from dual --first row
union all
Select 2 as id, 2 as name, .... from dual --second row
...
union all
Select 199 as id, 199 as name, .... from dual --last row
as one string
Input parameter is table_name, this is only for small table like continent, country, city....
答案1
得分: 2
是的，您可以这样做，但是：
- 如果表格包含1亿行怎么办？您几乎不可能以这种方式“导出”或加载数据。
- 此外，对于简单的数据类型（如
NUMBERS），执行此操作很简单。字符串（CHAR，VARCHAR2）已经需要单引号，但没问题。DATES- 除了单引号外，可能需要正确格式化。时间组件呢？更不用说例如BLOB列了。 - 这应该是什么类型的（PL /）SQL程序？一个函数吗？它会返回什么数据类型？
CLOB？Refcursor？它将会是流水线化的吗？不要使用DBMS_OUTPUT来执行此操作！
长话短说：使用专为此目的设计的工具，那就是Data Pump（导出和导入）。
为什么要重新发明轮子？如果您还没有使用某些GUI工具访问Oracle数据库，请下载并安装Oracle SQL Developer（它是免费工具）。
建立连接，右键单击源表并选择“导出”选项；设置参数，例如：
完成所有向导步骤（基本上只需点击“下一步”），结果将保存到设计的文件夹中，看起来像这样：
--------------------------------------------------------
-- File created - ponedjeljak-lipnja-19-2023
--------------------------------------------------------
REM INSERTING into SCOTT.DEPT
SET DEFINE OFF;
Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values ('10','ACCOUNTING','NEW YORK');
Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values ('20','RESEARCH','DALLAS');
Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values ('30','SALES','CHICAGO');
Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values ('40','OPERATIONS','BOSTON');
英文:
Well yes, you can do that, but:
- what if table contains 100 million rows? You'll NEVER (virtually) "export" nor load data that way.
- Furthermore, it is a simple task to do that with simple datatypes, such as
NUMBERS. Strings (CHAR,VARCHAR2) already require single quotes, but OK, no problem.DATES- apart from single quotes, might need to be properly formatted. Time component? Not to mention e.g.BLOBcolumns. - what kind of (PL/)SQL program should that be? A function? What datatype will it return?
CLOB? Refcursor? Will it be pipelined? Don't do it withDBMS_OUTPUT!
To cut a long story short: use utility which is designed for such a purpose, and that's Data Pump (export and import).
Why reinventing the wheel? If you don't already use some GUI tool to access your Oracle database, download and install Oracle SQL Developer (it is a free tool).
Establish connection, right-click source table and choose the "Export" option; set parameters to e.g.
Finish all Wizard steps (basically, just click NEXT) and the result is saved into a file in designed folder and looks like
--------------------------------------------------------
-- File created - ponedjeljak-lipnja-19-2023
--------------------------------------------------------
REM INSERTING into SCOTT.DEPT
SET DEFINE OFF;
Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values ('10','ACCOUNTING','NEW YORK');
Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values ('20','RESEARCH','DALLAS');
Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values ('30','SALES','CHICAGO');
Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values ('40','OPERATIONS','BOSTON');
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论