如何使用 sysdate 更新日期列，包括动态 sql 中的时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28314350/
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
How to update a date column with sysdate including timestamp in a dynamic sql
提问by LearnOracle
CREATE OR REPLACE PROCEDURE TESTPROC
IS
l_update_str VARCHAR2 (4000);
BEGIN
l_update_str :=
'UPDATE XX_TABLE
last_update_date = ''' || SYSDATE || ''',
WHERE 1=1';
EXECUTE IMMEDIATE l_update_str;
END;
/
I have tried above but it is not working. I have aslo tried using to_date(to_char(sysate)) but the time stamp is missing
我已经尝试过以上但它不起作用。我也尝试过使用 to_date(to_char(sysate)) 但缺少时间戳
回答by Lalit Kumar B
I have aslo tried using to_date(to_char(sysate)) but the time stamp is missing
我也尝试过使用 to_date(to_char(sysate)) 但缺少时间戳
Makes no sense to do that.
这样做毫无意义。
Why PL/SQL for such a trivial task. Do it in plain SQL. The simple syntax for an UPDATEstatement is -
为什么要使用 PL/SQL 来完成这样一项微不足道的任务。用普通的 SQL 来做。UPDATE语句的简单语法是 -
UPDATE table_name SET column_name = SYSDATE WHERE <conditions>
UPDATE table_name SET column_name = SYSDATE WHERE <conditions>
To display the date column, simply use TO_CHARalong with proper FORMAT MODEL.
要显示日期列，只需使用TO_CHAR和适当的FORMAT MODEL。
SELECT TO_CHAR(column_name, 'MM/DD/YYYY HH24:MI:SS') FROM table_name;
SELECT TO_CHAR(column_name, 'MM/DD/YYYY HH24:MI:SS') FROM table_name;
For example,
例如，
SQL> create table t(a date);
Table created.
SQL> insert into t values(sysdate - 10);
1 row created.
SQL> select * from t;
A
---------
25-JAN-15
SQL> update t set a = sysdate;
1 row updated.
SQL> select to_char(a, 'mm/dd/yyyy hh24:mi:ss') dt from t;
DT
-------------------
02/04/2015 11:29:21
SQL>
UPDATERegarding dynamic SQL.
更新关于动态 SQL。
'UPDATE XX_TABLE last_update_date = ''' || SYSDATE || ''', WHERE 1=1';
'UPDATE XX_TABLE last_update_date = ''' || 系统日期 || ''', 哪里 1=1';
- Firstly, the update syntaxis wrong. The SETkeyword is missing.
Secondly, you can't use SYSDATE like that in dynamic SQL.
Since you are forming the dynamic sql, the sysdate value is appended to the SQL. The best way to test any dynamic sql is to use DBMS_OUTPUTand see whether the dynamic SQL is properly formed.
- 首先，更新语法是错误的。该SET关键字缺少。
其次，您不能像在动态 SQL 中那样使用 SYSDATE。
由于您正在形成动态 sql，因此 sysdate 值将附加到 SQL。测试任何动态 SQL 的最佳方法是使用DBMS_OUTPUT并查看动态 SQL 是否正确形成。
For example,
例如，
SQL> CREATE OR REPLACE
2 PROCEDURE TESTPROC
3 IS
4 l_update_str VARCHAR2 (4000);
5 BEGIN
6 l_update_str := 'UPDATE t
7 SET a = ' || SYSDATE;
8 --EXECUTE IMMEDIATE l_update_str;
9
10 DBMS_OUTPUT.PUT_LINE(l_update_str);
11 END;
12 /
Procedure created.
SQL> exec testproc;
UPDATE t
SET a = 02/04/2015 11:46:53
PL/SQL procedure successfully completed.
SQL>
So, do you see the SQL formed? The SYSDATE value is appended, instead of actually using the keyword SYSDATE.
那么，你看到形成的 SQL 了吗？附加了 SYSDATE 值，而不是实际使用关键字SYSDATE。
So, you need to slightly change the dynamic sql.
所以，你需要稍微改变一下动态sql。
SQL> CREATE OR REPLACE
2 PROCEDURE TESTPROC
3 IS
4 l_update_str VARCHAR2 (4000);
5 BEGIN
6 l_update_str := 'UPDATE t
7 SET a = SYSDATE';
8 --EXECUTE IMMEDIATE l_update_str;
9
10 DBMS_OUTPUT.PUT_LINE(l_update_str);
11 END;
12 /
Procedure created.
SQL> exec testproc;
UPDATE t
SET a = SYSDATE
PL/SQL procedure successfully completed.
SQL>
Now the above would work fine.
现在上面的方法可以正常工作。
回答by PLSQL_007
Simply put systimestamp to set the timestamp in the column.
只需将 systimestamp 设置为列中的时间戳即可。
CREATE OR REPLACE PROCEDURE TESTPROC
IS
l_update_str VARCHAR2 (4000);
BEGIN
l_update_str :=
'UPDATE test1
set name1 = SYSTIMESTAMP(6)
where 1=1';
EXECUTE IMMEDIATE l_update_str;
END;
