目录
定义
SELECT
INSERT
依次赋值
指定列赋值
默认值
多行插入
with check option操作
UPDATE
DELETE
MERGE
LOCK TABLE
参数说明
EXPLAIN PLAN
参数说明
CALL
定义
数据库操作语言， 用来访问和操作现有用户对象中的数据，这些语句不会隐式提交事务
Data manipulation language (DML) statements access and manipulate data in existing schema objects. These statements do not implicitly commit the current transaction. The data manipulation language statements are
SELECT
ddl中最常用的语句，他可以从一个或多个表、表对象、视图、对象视图、物化视图、分析视图或层次结构中检索数据。
基本用法参照集合运算 The Set Operators、子查询 Sub Query、连接 JOIN
INSERT
它可以向表、视图的基表、分区表的分区或符合分区表的子分区、对象表或者对象视图的基表中添加行
参照emp创建表tb_insert01数据不要
| SQL> conn scott/tiger
Connected.
SQL> create table tb_insert01 as select * from emp where 1=2;
Table created.
SQL> desc tb_insert01;
Name Null? Type
------------------------- -------- -------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select * from tb_insert01;
no rows selected |
依次赋值
向tb_insert01中添加数据并查看
| SQL> insert into tb_insert01 values(1111,'name1','job1',9999,sysdate,1000,10,11);
1 row created.
SQL> select * from tb_insert01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- --------- --------- ----- ------------------- ----- ----- -------
1111 name1 job1 9999 2021-02-01 10:37:05 1000 10 11 |
如果没有指明给表中哪些字段赋值，则表示要给所有字段依次赋值，赋值顺序按照表中字段的定义顺序
指定列赋值
向表中empno,ename,sal,deptno分别赋值
| SQL> insert into tb_insert01(empno,ename,sal,deptno) values(2222,'name2',2000,12);
1 row created.
SQL> select * from tb_insert01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- --------- --------- ----- ------------------- ----- ----- -------
1111 name1 job1 9999 2021-02-01 10:37:05 1000 10 11
2222 name2 2000 12 |
未知定的列将赋予空值，赋值过程中注意字符串和时间需要加单引号，注意类型匹配，避免发生隐式转换(explicit conversion)
默认值
对于未赋值的列默认为空值，如果指定了default值则显示default值
查看表中所有列的默认值
| SQL> SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME='TB_INSERT01';
COLUMN_NAM DATA_TYPE DATA_LENGTH DATA_DEFAU
---------- ---------- ---------------- ----------
EMPNO NUMBER 22
ENAME VARCHAR2 10
JOB VARCHAR2 9
MGR NUMBER 22
HIREDATE DATE 7
SAL NUMBER 22
COMM NUMBER 22
DEPTNO NUMBER 22
8 rows selected. |
修改job列的默认值，并再次查看
|
SQL> alter table tb_insert01 modify(job default '打工人');
Table altered.
SQL> SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME='TB_INSERT01';
COLUMN_NAM DATA_TYPE DATA_LENGTH DATA_DEFAU
---------- ---------- ---------------- ----------
EMPNO NUMBER 22
ENAME VARCHAR2 10
JOB VARCHAR2 9 '打工人'
MGR NUMBER 22
HIREDATE DATE 7
SAL NUMBER 22
COMM NUMBER 22
DEPTNO NUMBER 22
8 rows selected. |
再次向表中插入数据
| SQL> insert into tb_insert01(empno,ename,sal,deptno) values(3333,'name3',3000,13);
1 row created.
SQL> select * from tb_insert01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- --------- --------- ----- ------------------- ----- ----- -------
1111 name1 job1 9999 2021-02-01 10:37:05 1000 10 11
2222 name2 2000 12
3333 name3 打工人 3000 13 |
当然如果想要强制显示null也可以，只要指定列的值即可
| SQL> insert into tb_insert01(empno,ename,job,sal,deptno) values(3333,'name3',null,3000,13);
1 row created.
SQL> select * from tb_insert01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- --------- --------- ----- ------------------- ----- ----- -------
1111 name1 job1 9999 2021-02-01 10:37:05 1000 10 11
2222 name2 2000 12
3333 name3 打工人 3000 13
3333 name3 3000 13 |
hiredate字段属于date格式，如果是字符串的话需要用to_date转换后再进行赋值
| SQL> insert into tb_insert01 (hiredate) values(to_date('2222-12-22','rrrr-mm-dd'));
1 row created.
SQL> select * from tb_insert01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- --------- --------- ----- ------------------- ----- ----- -------
1111 name1 job1 9999 2021-02-01 10:37:05 1000 10 11
2222 name2 2000 12
3333 name3 打工人 3000 13
3333 name3 3000 13
打工人 2222-12-22 00:00:00 |
values的值也可以使用绑定变量bind variables来进行赋值
| SQL> insert into tb_insert01(empno,ename,sal) values(&i,&j,&h);
Enter value for i: 4444
Enter value for j: 'name4'
Enter value for h: 4444
old 1: insert into tb_insert01(empno,ename,sal) values(&i,&j,&h)
new 1: insert into tb_insert01(empno,ename,sal) values(4444,'name4',4444)
1 row created.
SQL> select * from tb_insert01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- --------- --------- ----- ------------------- ----- ----- -------
1111 name1 job1 9999 2021-02-01 10:37:05 1000 10 11
2222 name2 2000 12
3333 name3 打工人 3000 13
3333 name3 3000 13
打工人 2222-12-22 00:00:00
4444 name4 打工人 4444
6 rows selected. |
多行插入
一些时候我们需要往一张表里插入多行数据
比如在表结构相同的情况下，可以将emp表中的一些数据插入到tb_insert01中
例如：将emp中工资最高的三名员工数据插入tb_insert01
| SQL> insert into tb_insert01 select * from (select * from emp order by sal desc) where rownum <= 3;
3 rows created.
SQL> select * from tb_insert01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- --------- --------- ----- ------------------- ----- ----- -------
1111 name1 job1 9999 2021-02-01 10:37:05 1000 10 11
2222 name2 2000 12
3333 name3 打工人 3000 13
3333 name3 3000 13
打工人 2222-12-22 00:00:00
4444 name4 打工人 4444
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
9 rows selected. |
with check option操作
with check option是指在insert的时候将会对数据进行审核，如果不满足条件，则报错，如果满足条件，则成功
参照emp创建表tb_insert02数据不要
| SQL> create table tb_insert02 as select * from emp where 1=2;
Table created.
SQL> desc tb_insert02;
Name Null? Type
------------------------- -------- -------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select * from tb_insert02;
no rows selected |
向表中插入数据并添加check条件
| SQL> insert into( select empno,ename,sal,deptno from tb_insert02 where deptno=88 with check option) values(8888,'boss',8888,88);
1 row created.
SQL> select * from tb_insert02;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- --------- --------- ----- ------------------- ----- ----- -------
8888 boss 8888 88
SQL> insert into( select empno,ename,sal,deptno from tb_insert02 where deptno=88 with check option) values(8888,'boss',8888,99);
insert into(select empno,ename,sal,deptno from tb_insert02 where deptno=88 with check option) values(8888,'boss',8888,99)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation |
效果如同check 约束
UPDATE
它可以更改表中的现有值，视图的基表或物化视图表中的现有值
新建表tb_update01参照emp取前5行
| SQL> create table tb_update01 as select * from emp where rownum<=5;
Table created.
SQL> desc tb_update01;
Name Null? Type
----------------------- -------- ----------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select * from tb_update01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- --------- --------- ----- ------------------- ----- ----- -------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 |
更新smith的comm为200
| SQL> update tb_update01 set comm=200 where ename='SMITH';
1 row updated.
SQL> select * from tb_update01 where ename='SMITH';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- --------- --------- ----- ------------------- ----- ----- -------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 200 20 |
切记在update的时候谨慎查看where条件。如果where条件不填会更新整列信息
| SQL> update tb_update01 set comm=200;
5 rows updated.
SQL> select * from tb_update01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- --------- --------- ----- ------------------- ----- ----- -------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 200 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 200 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 200 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 200 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 200 30 |
| SQL> rollback;
Rollback complete. |
DELETE
delete语句可以删除如下对象中的行
一个没有分区的表
视图中为分区或已分区的表
可写物化视图为分区或已分区的容器表
可更新物化视图为分区或分区主表
创建tb_delete01表参照emp取前5行
| SQL> create table tb_delete01 as select * from emp where rownum<=5;
Table created.
SQL> desc tb_delete01;
Name Null? Type
----------------------- -------- ----------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select * from tb_delete01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- --------- --------- ----- ------------------- ----- ----- -------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 |
将表中的smith行删除
| SQL> delete from tb_delete01 where ename='SMITH';
1 row deleted.
SQL> select * from tb_delete01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- --------- --------- ----- ------------------- ----- ----- -------
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 |
同update一样，delete也需要where条件来限定删除的数据量
如果不加where条件则清空改表
|
SQL> delete from tb_delete01;
4 rows deleted.
SQL> select * from tb_delete01;
no rows selected |
效果通trancate（区别在DDL中讲）
from可省略
MERGE
merge语句可以从一个或多个源中选择行，并插入或更新到表或视图中
可以指定条件来确定更新还是插入到目标表或视图
相当于insert、update和delete同时操作，避免多条语句执行
例
下表为超市总店和分店的价格表
将总店的信息合并的分店中去
| 总店 | 商品ID | 商品名称 | 商品价格 |
| | 01 | mono1 | 11 |
| | 02 | mono2 | 22 |
| | 04 | mono4 | 44 |
| 分店 | 商品ID | 商品名称 | 商品价格 |
| | 01 | mono1 | 11 |
| | 02 | mono2 | 22 |
| | 03 | mono3 | 33 |
创建对应的表tb_zd,tb_fd并插入数据
| SQL> create table tb_zd(main_mono_id number(2),main_mono_name varchar2(5),main_mono_price number(5,2));
Table created.
SQL> insert into tb_zd values(01,'mono1',11);
1 row created.
SQL> insert into tb_zd values(02,'mono2',22);
1 row created.
SQL> insert into tb_zd values(04,'mono4',44);
1 row created.
SQL> create table tb_zf(sub_mono_id number(2),sub_mono_name varchar2(5),sub_mono_price number(5,2));
SQL> insert into tb_fd values(01,'mono1',1);
1 row created.
SQL> insert into tb_fd values(02,'mono2',2);
1 row created.
SQL> insert into tb_fd values(03,'mono3',3);
1 row created.
SQL> select * from tb_zd;
MAIN_MONO_ID MAIN_MONO_NAME MAIN_MONO_PRICE
------------ --------------- ---------------
1 mono1 11
2 mono2 22
4 mono4 44
SQL> select * from tb_fd;
SUB_MONO_ID SUB_MONO_NAME SUB_MONO_PRICE
----------- --------------- --------------
1 mono1 1
2 mono2 2
3 mono3 3 |
目标，将总店和分店的商品信息用商品id关联
总店和分店都有的商品以总店的价格为准
分店没有的商品添加到分店
总店没有的不做处理
|
SQL> merge into tb_fd f using tb_zd z on(f.sub_mono_id=z.main_mono_id) when matched then update set f.sub_mono_price=z.main_mono_price when not matched then insert values(z.main_mono_id,z.main_mono_name,z.main_mono_price) ;
3 rows merged.
SQL> select * from tb_fd;
SUB_MONO_ID SUB_MONO_NAME SUB_MONO_PRICE
----------- --------------- --------------
1 mono1 11
2 mono2 22
3 mono3 3
4 mono4 44 |
LOCK TABLE
出处
使用locak table语句可以指定模式锁定一个或多个表、分区表或子分区。此锁定手动覆盖自动锁定，并允许或拒绝其他用户在操作期间访问表或视图
| LOCK TABLE ... PARTITION FOR (partition_key_value) ... |
参数说明
ROW SHARE
共享允许并发访问被锁定的表，但是禁止用户为独占访问而锁定整个表，ROW SHARE和SHARE UPDATE同义，SHARE UPDATE是为了兼容早期Oracle数据库兼容而提供的。
ROW EXCLUSIVE
ROW EXCLUSIVE与ROW SHARE相同，但它也禁止在共享模式下锁定。在更新、插入或删除时，会自动获得行独占锁。
SHARE
SHARE允许并发查询，但禁止更新锁定的表。
SHARE ROW EXCLUSIVE
SHARE ROW EXCLUSIVE用于查看整个表，允许其他人查看表中的行，但禁止其他人在共享模式下锁定表或更新行。
EXCLUSIVE
如果要在指定的表、分区或表子分区已被其他用户锁定时立即将控制权返回给您，请指定NOWAIT。在这种情况下，数据库返回一条消息，指示表、分区或子分区已被其他用户锁定。
NOWAIT
使用WAIT子句指示LOCK TABLE语句应该等待指定的秒数以获取DML锁。整数的值没有限制。
WAIT
使用WAIT子句指示LOCK TABLE语句应该等待指定的秒数以获取DML锁。整数的值没有限制。
如果既不指定NOWAIT也不指定WAIT，则数据库将无限期地等待，直到表可用、锁定它并将控制权返回给您。当数据库同时执行DDL语句和DML语句时，有时会导致超时或死锁。数据库检测此类超时和死锁并返回错误。
以下语句以独占模式锁定employees表，但如果其他用户已锁定该表，则不会等待
| LOCK TABLE employees
IN EXCLUSIVE MODE
NOWAIT; |
下面的语句锁定可通过远程数据库链接访问的远程employees表
| LOCK TABLE employees@remote
IN SHARE MODE; |
EXPLAIN PLAN
出处
该语句可以确定oracle数据库在执行指定SQL的时候所遵循的执行计划，该语句将描述执行计划的每一个步骤的行插入到指定的表中，此外可以发出explain plan语句作为SQL跟踪工具的一部分
它可以确定该条SQL的执行成本，如果表中定义了任何域索引，那么还将记录用户定义的cou和I/O开销
使用explain plan需要有相应的权限
参数说明
SET STATEMENT_ID子句
为输出表中执行计划行的STATEMENT_ID列指一个值，然后可以使用此值在输出表中的其他行中标识这些行，如果输出表包含许多执行计划的行，需要确保指定语句的ID值，如果省略，则语句的ID默认为空值null
INTO table子句
指定输出表的名称，还可以i指定其用户和数据库，在使用explain plan之前，必须确保表存在
如果省略用户，则数据库默认为当前用户
dblink可以指向输出表所在的远程oracle数据库的链接的完整或部分名称，只有在分布式oracle数据库功能启用的时候才能指定远程输出表。如果省略该项则默认为本地数据库
FOR子句
指定select、insert、update、delete、merger、create table、create index或alter index等为其生成执行计划的重建语句
以下语句确定UPDATE语句的执行计划和开销，并将描述执行计划的行插入到指定的plan表中，语句ID值为“Raise in Tokyo”
| EXPLAIN PLAN
SET STATEMENT_ID = 'Raise in Tokyo'
INTO plan_table
FOR UPDATE employees
SET salary = salary * 1.10
WHERE department_id =
(SELECT department_id FROM departments
WHERE location_id = 1700); |
下面的SELECT语句查询plan_table表并返回执行计划和成本
| SELECT id, LPAD(' ',2*(LEVEL-1))||operation operation, options,
object_name, object_alias, position
FROM plan_table
START WITH id = 0 AND statement_id = 'Raise in Tokyo'
CONNECT BY PRIOR id = parent_id AND statement_id = 'Raise in Tokyo'
ORDER BY id; |
返回如下计划
| ID OPERATION OPTIONS OBJECT_NAME OBJECT_ALIAS POSITION
--- -------------------- -------------------- -------------------- -------------------- --------
0 UPDATE STATEMENT 4
1 UPDATE EMPLOYEES 1
2 INDEX RANGE SCAN EMP_DEPARTMENT_IX EMPLOYEES@UPD$1 1
3 TABLE ACCESS BY INDEX ROWID DEPARTMENTS DEPARTMENTS@SEL$1 1
4 INDEX RANGE SCAN DEPT_LOCATION_IX DEPARTMENTS@SEL$1 1 |
POSITION列中的值显示语句的开销为4
CALL
可以用来执行存储过程
可以通过两种方式执行例程。可以通过名称、routine_clause对例程本身发出调用，也可以使用 object_access_expression 调用表达式类型内的object_access_expression。
routine_clause
指定被调用的函数或过程的名称，或解析为函数或过程的同义词。
当调用类型的成员函数或过程时，如果第一个参数 （SELF） 是 null IN OUT 参数，则 Oracle 数据库将返回错误。如果 SELF 是 null IN 参数，则数据库返回 null。在这两种情况下，不会调用函数或过程。
Restriction on Functions
如果例程是函数，则需要 INTO 子句。.
schema
指定独立例程或包含例程的包或类型的驻留架构。如果未指定架构，则 Oracle 数据库假定例程位于您自己的架构中。
type or package
指定在其中定义例程的类型或包。
@dblink
在分布式数据库系统中，指定包含独立例程的数据库的名称，或包含例程的包或函数的名称。如果省略 dblink，则 Oracle 数据库将查看本地数据库。
argument
如果例程采用参数，则为例程指定一个或多个参数。可以使用位置、命名或混合表示法进行参数。例如，以下所有符号都正确：
| CALL my_procedure(arg1 => 3, arg2 => 4) |
| CALL my_procedure(3, 4)
CALL my_procedure(3, arg2 => 4) |
调用存储过程
| CALL emp_mgmt.remove_dept(162); |
使用对象类型的表达式调用过程
| ALTER TYPE warehouse_typ
ADD MEMBER FUNCTION ret_name
RETURN VARCHAR2
CASCADE;
CREATE OR REPLACE TYPE BODY warehouse_typ
AS MEMBER FUNCTION ret_name
RETURN VARCHAR2
IS
BEGIN
RETURN self.warehouse_name;
END;
END;
/
VARIABLE x VARCHAR2(25);
CALL warehouse_typ(456, 'Warehouse 456', 2236).ret_name()
INTO :x;
PRINT x;
X
--------------------------------
Warehouse 456 |
使用外部函数实现相同的目标
| CREATE OR REPLACE FUNCTION ret_warehouse_typ(x warehouse_typ)
RETURN warehouse_typ
IS
BEGIN
RETURN x;
END;
/
CALL ret_warehouse_typ(warehouse_typ(234, 'Warehouse 234',
2235)).ret_name()
INTO :x;
PRINT x;
X
--------------------------------
Warehouse 234 |
1808
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
