oracle 在 PL/SQL 中创建表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9587022/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create Table As within PL/SQL?
提问by Data-Base
I'm trying to create a table within PL/SQL
我正在尝试在 PL/SQL 中创建一个表
how I can achieve that?
我怎么能做到这一点？
keep getting
不断得到
Error report:
错误报告：
ORA-00933: "SQL command not properly ended"
ORA-00933：“SQL 命令未正确结束”
here is the code that I have error with
这是我有错误的代码
DECLARE
station_id_ms1 NUMBER :=10347;
realtime_start DATE :=to_date('2012-01-01 00:00:00','YYYY-DD-MM HH24:MI:SS');
realtime_end DATE :=to_date('2012-07-01 00:00:00','YYYY-DD-MM HH24:MI:SS');
BEGIN
EXECUTE IMMEDIATE ('
CREATE TABLE new_table_name
AS
SELECT
((realtime - to_date(''01-JAN-1970'',''DD-MON-YYYY'')) * (86400)) AS realtime_ms1,
magnetic_ms_id,
ADC_value_pp_2_mgntc_fld_amp(ch2_value,ch2_gain_value,magnetic_ms_id,2) AS B_x_ms1,
ADC_value_pp_2_mgntc_fld_amp(ch1_value,ch1_gain_value,magnetic_ms_id,1) AS B_y_ms1,
real_nanosecs2*4/3*360/20e6 AS phase_x_ms1,
real_nanosecs1*4/3*360/20e6 AS phase_y_ms1
FROM
raw_mag
WHERE
magnetic_ms_id = '||station_id_ms1||'
AND realtime > '||realtime_start||'
AND realtime < '||realtime_end||'
AND ch1_tune_value = 0
AND realtime < pkg_timezone.change_timezone(gettime,''CET'',''UTC'')
');
END;
回答by René Nyffenegger
You should do the char-to-dateconversion withinthe plsql-string that you excecute immediate.
你应该做的炭最新的转换中的PLSQL串，你excecute立竿见影。
The date you declared will be "back-cast" to a varchar2 in the concatenation and "re-cast" into a date again for the execution of the create table statement. And "all sorts of things" can happen in these two casts, so you want to make sure you're in control how the character-string is interpreted when cast to a date.
您声明的日期将在串联中“反向转换”为 varchar2，并再次“重新转换”为日期以执行 create table 语句。在这两个转换中可能会发生“各种各样的事情”，因此您要确保在转换为日期时控制字符串的解释方式。
DECLARE
station_id_ms1 NUMBER :=10347;
realtime_start VARCHAR2(100) :='2012-01-01 00:00:00';
realtime_end VARCHAR2(100) :='2012-07-01 00:00:00';
BEGIN
EXECUTE IMMEDIATE ('
CREATE TABLE new_table_name
AS
SELECT
((realtime - to_date(''01-JAN-1970'',''DD-MON-YYYY'')) * (86400)) AS realtime_ms1,
magnetic_ms_id,
ADC_value_pp_2_mgntc_fld_amp(ch2_value,ch2_gain_value,magnetic_ms_id,2) AS B_x_ms1,
ADC_value_pp_2_mgntc_fld_amp(ch1_value,ch1_gain_value,magnetic_ms_id,1) AS B_y_ms1,
real_nanosecs2*4/3*360/20e6 AS phase_x_ms1,
real_nanosecs1*4/3*360/20e6 AS phase_y_ms1
FROM
raw_mag
WHERE
magnetic_ms_id = '||station_id_ms1||'
AND realtime > to_date(''' || realtime_start || ''', ''YYYY-DD-MM HH24:MI:SS'')
AND realtime < to_date(''' || realtime_end || ''', ''YYYY-DD-MM HH24:MI:SS'')
AND ch1_tune_value = 0
AND realtime < pkg_timezone.change_timezone(gettime,''CET'',''UTC'')
');
END;
回答by Florin Ghita
I would use binds for station_id_ms1, realtime_start, realtime_end:
我会为 station_id_ms1、realtime_start、realtime_end 使用绑定：
EXECUTE IMMEDIATE '
...
WHERE
magnetic_ms_id = :station_id_ms1
AND realtime > :realtime_start
AND realtime < :realtime_end
...
' USING IN station_id_ms1, realtime_start, realtime_end
