oracle 在 Where 子句中使用日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21517074/
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
Using Date in Where Clause
提问by user3109653
I am trying to place a date in a where clause. I want to update all rows in which the date column is before or after a certain date. How do I specify that I only want to update these columns. Here is the coding that I have so far (not including specific column names):
我试图在 where 子句中放置一个日期。我想更新日期列在某个日期之前或之后的所有行。如何指定我只想更新这些列。这是我到目前为止的编码（不包括特定的列名）：
update table1
set column1 = value
where (select date from table2) < date;
Am I on the right track?
我在正确的轨道上吗？
Also, could someone please explain the difference between SQL and PL/SQL. I am taking a class in PL/SQL at the moment. Whenever I post a question on this forum I say that I have a question in PL/SQL, but the people who answer my question say that a certain function - update/if/case/etc. - is a SQL statement and not a PL/SQL statement. What is the difference?
另外，有人可以解释一下 SQL 和 PL/SQL 之间的区别吗？我目前正在学习 PL/SQL 课程。每当我在这个论坛上发布问题时，我都会说我在 PL/SQL 中有一个问题，但是回答我问题的人说某个函数 - update/if/case/etc。- 是 SQL 语句而不是 PL/SQL 语句。有什么不同？
-Neil
-尼尔
回答by y?s??la
Your update statement
您的更新声明
update table1
set column1 = value
where (select date from table2) < date;
is correct and it will work but only if the inner query (select date from table2)returns a single row. If you are trying to compare to specific date you don't need the inner query, for example:
是正确的，它会起作用，但前提是内部查询(select date from table2)返回单行。如果您尝试与特定日期进行比较，则不需要内部查询，例如：
update table1
set column1 = value
where to_date('01/02/2012', 'DD/MM/YY') < date;
You can adjust date format mask to whatever format of data you prefer. to_datewill convert from char to date type, and to_charwill do the opposite.
您可以将日期格式掩码调整为您喜欢的任何数据格式。to_date将从 char 转换为 date 类型，并to_char会做相反的事情。
SQL is a standardized query language that is supported by all compliant relational databases (with some proprietary extensions sometimes). SQL is not a programming language. PL/SQL is a procedural programming language that is supported on Oracle only (Postgres has similar syntax). PL/SQL is SQL + regular programming language features like conditional statements (if/else), loops (for), functions and procedures and such. PL/SQL is used whenever it's too difficult or impossible to get some data using SQL solely.
SQL 是一种标准化查询语言，所有兼容的关系数据库都支持它（有时带有一些专有扩展）。SQL 不是一种编程语言。PL/SQL 是一种过程编程语言，仅在 Oracle 上受支持（Postgres 具有类似的语法）。PL/SQL 是 SQL + 常规编程语言功能，如条件语句 (if/else)、循环 (for)、函数和过程等。当单独使用 SQL 获取某些数据太困难或不可能时，就会使用 PL/SQL。
回答by Fandango68
As Aleksey mentioned, your query is correct but you need to either [1] set conditions around the sub-SQL to only return ONE record or [2] make sure the data in tabl2 only has ONE record when it runs.
正如 Aleksey 提到的，您的查询是正确的，但您需要 [1] 围绕 sub-SQL 设置条件以仅返回一条记录，或 [2] 确保 tabl2 中的数据在运行时只有一条记录。
ie
IE
If you have to refer to data from another table in your WHERE clause consider explicit joins (example in SQL Server) ...
如果您必须在 WHERE 子句中引用另一个表中的数据，请考虑显式连接（SQL Server 中的示例）...
update t1
set t1.column1 = value -- <-- some arbitary value here I assume?
from table1 t1
inner join table2 t2
on (t2.key = t1.key) -- you need to specify the primary keys of the tables here
where t2.date < t1.date
That way you are not assuming table2 has only one record. It can have many records as long as they relate to table1 via their keys/indexes and the WHERE clause simply makes sure you only UPDATE based on data from table2 that has a date LESS THAN the date in table1.
这样你就不会假设 table2 只有一条记录。它可以有很多记录，只要它们通过键/索引与 table1 相关，并且 WHERE 子句只是确保您只根据 table2 中的数据进行更新，该数据的日期小于 table1 中的日期。
