Oracle PL/SQL 基础教程（9）数据操纵语言（上）

SQL干货分享 2021-08-13

324

（CSDN博主：写代码也要符合基本法）

各位看官老爷大家早上好，又到了周三分享 PL/SQL 基础的时间。上周我们终于探讨完了 SQL 查询语句的全部基本知识，今天我们再来分享一下 SQL 语言的另一大块分类——数据操纵语句（DML，Data Manipulation Language）

相信大家经常能听到“增删改查”这个业内俗语，这是对 SQL 最基本功能的一个概括。至于其中的“查”，即 SELECT 语句，前面几讲我们已经学习完了。剩下的“增、删、改”合起来统称 DML 语言，正是我们今天要一起学习的

总的来说，DML 语句没有那些花里胡哨的东西，规则也都相对简洁，相信即使是初学者的老爷们，也能很快消化的

数据操纵语句（DML）是在数据库上添加、更新,删除、合并数据时使用的，主要包括 INSERT 语句、UPDATE 语句和 DELETE 语句三个大类，今天我们先来学习一下 INSERT 语句

INSERT 语句用于向已有表中插入新数据。具体来说在新插入的行中，要指明表中全部的列或指定的若干列的数据。所以预先了解目标表的数据结构非常重要

DESC 指令

SQL 命令中可以使用 DESC[RIBE] 命令来查看一张表的数据结构，包括字段名、数据类型（含长度）、是否允许空值、默认值和字段备注等信息。这里的“是否允许空值”，是表结构中一种相当常见且重要的字段的数据约束形式，我们一般称之为非空约束。如果表中的某列被设计者添加了非空约束，那么数据库系统将按照这个约束时刻维护着该字段的内容，不允许其中有空值。具有非空约束的列我们习惯上称之为非空列，如果有人试图往非空列中插入 NULL，那么数据库将会阻止这一行为并报错 ORA-01400:无法将 NULL 值插入("USER"."TABLE"."COLUMN")

SQL> DESC EMP
Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
EMPNO    NUMBER(4)                              
ENAME    VARCHAR2(10) Y                         
JOB      VARCHAR2(9)  Y                         
MGR      NUMBER(4)    Y                         
HIREDATE DATE         Y                         
SAL      NUMBER(7,2)  Y                         
COMM     NUMBER(7,2)  Y                         
DEPTNO   NUMBER(2)    Y

上述表结构信息中除了列备注外的关键信息都可在 Oracle 数据字典 USER_TAB_COLUMNS（或 ALL_TAB_COLUMNS 或 DBA_TAB_COLUMNS）视图中检索到。数据字典包含 Oracle 数据库中大量的记录数据库对象信息的视图，它们都非常有用，我们将在课程中合适的时候不断接触到

SQL> SELECT c.column_name
  2        ,c.data_type
  3        ,CASE
  4           WHEN c.data_type IN ('VARCHAR2', 'CHAR', 'DATE') THEN
  5            c.data_length
  6           ELSE
  7            c.data_precision
  8         END data_length
  9        ,c.data_scale
 10        ,c.nullable
 11        ,c.data_default
 12    FROM user_tab_columns c
 13   WHERE c.table_name = 'EMP'
 14   ORDER BY c.column_id;


COLUMN_NAM DATA_TYPE  DATA_LENGTH DATA_SCALE NULLABLE DATA_DEFAULT
---------- ---------- ----------- ---------- -------- ---------------
EMPNO      NUMBER               4          0 N        
ENAME      VARCHAR2            10            Y        
JOB        VARCHAR2             9            Y        
MGR        NUMBER               4          0 Y        
HIREDATE   DATE                 7            Y        
SAL        NUMBER               7          2 Y        
COMM       NUMBER               7          2 Y        
DEPTNO     NUMBER               2          0 Y        


8 rows selected

让我们回到 INSERT 语句，它主要支持两种模式：单行插入和引用插入

单行插入

单行插入语句一次只能往表中插入一行，且需要罗列插入每个字段的值。其一般语法格式为：

INSERT INTO table_name[(column1, column2...)]
VALUES(value1, value2...);

INSERT INTO 指定要被插入数据的表；表名后面可以跟一个用括号括起来的字段列表，列名之间使用逗号分开，用以指定往哪些列里插入数据；VALUES 部分则是写明插入的数据，也要用括号括起来，值与值之间使用逗号分隔

例 1

INSERT INTO emp
  (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
  ('6666', 'YUSUF', 'ANALYST', '7782', '25-7月-21', 3000, NULL, '10');

插入后结果，查询表 EMP 内容

这里需要留意的几点规则有：

VALUES 部分中的列值要和前面指定的列名位置上一一对应。表名后的字段列表是可以省略的，这时表示语句涵盖表中所有的列，后面的 VALUES 部分也应按照全表字段的顺序一一列出值来。请注意，省略列名列表的写法是极不推荐的
VALUES 部分中的列值要和前面指定的列名数据类型一一对应。字符串内容应当以单引号括起来；至于日期类型的值，要么以数据库设置的默认日期时间格式的字符串表示，要么使用 TO_DATE 函数得到

如何知道数据库设置的默认日期时间格式是什么样的呢？这里又可以使用另一个数据字典进行查询，那就是 V$NLS_PARAMETERS 视图，这里面包含了 Oracle 数据库用于支持不同国家和地区语言文字的 NLS（Native Language Support）参数信息

SQL> SELECT *
  2    FROM v$nls_parameters
  3   WHERE parameter IN
  4         ('NLS_DATE_FORMAT', 'NLS_DATE_LANGUAGE', 'NLS_TIME_FORMAT');


PARAMETER            VALUE
-------------------- --------------------
NLS_DATE_FORMAT      DD-MON-RR
NLS_DATE_LANGUAGE    SIMPLIFIED CHINESE
NLS_TIME_FORMAT      HH.MI.SSXFF AM

如上查询中我们看到当前数据库默认的日期格式为“DD-MON-RR”，所以在前面的示例语句中，插入 HIREDATE 列的值写作'25-7月-21'，这种情况下数据库能够将这个字符串隐默地转换成为日期数据

DD-MON-RR 中的三个组成部分，DD 表示日期中的日，标准的说法为月中的第几天；MON 表示的是 NLS 语言习惯的月份表示方法，从上面的查询我们看到 NLS 日期语言为简体中文，所以表示月份使用“7月”这种字样；RR 是使用两位数字表示的年份，遵循“就近原则”，即选用距当前时间更近的那个世纪：比如 1921、2021、2121 末尾都是 21，而此三个离现在最近的是 2021，所以 RR 的 21 代表 2021 年

这里还要强调的是，数据库只是在这些特定情况下将符合默认格式的字符串转换成了日期数据而已，并不代表以默认格式写出的字符串就已经是日期数据了。日期和字符串是两种截然不同的数据类型。我们习惯上写出的任何形式的表明日期、时间的文字，从数据的角度出发，它们只是字符串，仅在我们的头脑中它们可以代表某个日期或时间。在数据库中，日期和时间本身是“无形”的，想要指定或展示，都需要转换成字符串等“有形”格式。另外还有一个小知识点是，关键字 SYSDATE 总是返回（数据库服务器的）当前的日期时间

所以为了保障程序的可迁移性，上述的按数据库 NLS 设置写出日期格式字符串，指望数据库去隐默转换数据类型的做法，并不是非常推荐的。在通常的程序设计中，我们更推荐使用 TO_DATE 函数得到日期时间数据

比如 TO_DATE('2021-07-25', 'yyyy-mm-dd') 能够输出 2021 年 7 月 25 日（零时）这个时刻的日期类型数据

事实上除了 TO_DATE 函数，几乎所有的标量函数在 INSERT 语句的 VALUES 部分中都是允许使用的，仅返回一列的标量子查询也是允许使用的

引用插入

引用插入，又常称为多行插入，或者更直白的称为 INSERT SELECT 插入，因为它的语法为：

INSERT INTO table_a[(column1, column2...)]
SELECT expr1, expr2...
FEOM table_b
[<more SELECT staement components>]

简单来说，就是将一个 SELECT 语句的结果集一次性插入表中，这个结果集可以有多行，也可以一行也没有。至于列与值在位置上、数据类型上的规则仍必须一一对应；省略表名后的列名列表的写法同样是极不推荐的；当然，SELECT 语句部分使用 INSERT 语句要插入的表也是允许的

例 2

INSERT INTO emp
  (empno, ename, job, mgr, hiredate, sal, deptno)
  SELECT empno, ename, job, mgr, hiredate, sal, deptno FROM emp_temp;

插入后结果，查询表 EMP

插入空值

INSERT 语句还涉及到一个需要关注的点，就是插入空值的问题。我们习惯上讲，插入空值有两种办法：显式插入和隐式插入

显式插入空值：指在 VALUES 部分中，对应要插入空值的列位置处，明示 NULL 值。例如前文例 1 中，就明示了要将 NULL 值插入到 COMM 列
隐式插入空值：指在列名列表中故意不提及某些列，以暗示数据库将 NULL 值插入它们。例如前文例 2 中，列名列表中没有 COMM 列，这表示想要将 NULL 值插入其中（更准确的讲，是语句不指示将什么数据插入 COMM 列中）

值得指出的是，表列可以添加默认值设置，或称之为缺省值设置。此处的“缺省”指的正是类似隐式插入这种情况：语句没有指出某些列的情况，才属于这些列缺省的情况。换句话说，对于有默认值设置的列来说，隐式插入空值的最终结果为，插入业已设定的默认值；唯有显式插入空值，才能确保将空值插入到这样的列中（该列没有非空约束的前提下）

注意单行插入时 VALUES 关键字是必须的，其后的值也是不能省略的。所以至于往表中插入空行（所有列都是空值）这种极端情况，要么显式地往所有列中插入空值；要么至少显式地往一列中插入空值，指望数据库往剩下的列里插入空值，当然这就有了带默认值的列最终不是空值的风险

事务

最后要指出的是，不止 INSERT 语句，关乎所有的数据操纵语言，在某个会话中，从执行了第一句 DML 语句开始，数据库会为之建立一个“事务”——这里涉及的众多专业概念以后的课程会专门研究，此处我们只需了解“事务”必须有始有终，在某个“事务”期间发生的数据变动（即所有 DML 语言预期的操作结果）都没有真实地发生在数据库中，直至事务终止，数据库才决定相关数据的最终情况。所以，执行完 DML 语句后，请务必记得及时执行 COMMIT 或R OLLBACK 指令终止事务。COMMIT 表示确定数据落实 DML 语句做出的数据操纵，ROLLBACK 则表示放弃落实（实际数据免受相关影响）

oracle

文章转载自SQL干货分享，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle PL/SQL 基础教程（9）数据操纵语言（上）

评论