访问ORACLE

最新推荐文章于 2022-06-23 14:52:39 发布

hx05760576

最新推荐文章于 2022-06-23 14:52:39 发布

阅读量692

点赞数

分类专栏： ORACLE学习文章标签： sql delete insert table null exception

本文链接：https://blog.csdn.net/hx05760576/article/details/5483171

版权

ORACLE学习专栏收录该内容

19 篇文章 0 订阅

订阅专栏

访问ORACLE

(一)检索单行数据

1、当在PL/SQL块中嵌入SELECT语句时，必须要带有INTO子句。该语句必须且只能返回一条数据，否则会触发NO_DATE_FOUND或TOO_MANY_ROWS例外。

SELECT...INTO...语法：

SELECT select_list INTO (variable_name1[,......] | record_name)

FROM table|view

WHERE condition;

例1、使用标量变量接收数据

DECLARE
v_no dept.deptno%type not null default 10;
v_name dept.dname%type;
v_loc dept.loc%type;
BEGIN
select deptno,dname,loc into v_no,v_name,v_loc from dept where deptno=20;
dbms_output.put_line('部门号: '||v_no||' 部门名: '||v_name||' 部门位置: '||v_loc);
End;

输出：部门号: 20 部门名: RESEARCH 部门位置: DALLAS

例2、使用记录变量接收数据

输出：部门号: 30 部门名: SALES 部门位置: CHICAGO

或

DECLARE
TYPE record1 IS RECORD
(
v_no dept.deptno%type not null default 10,
v_name dept.dname%type,
v_loc dept.loc%type
);
r1 record1;
BEGIN
select deptno,dname,loc into r1 from dept where deptno=30;
dbms_output.put_line('部门号: '||r1.v_no||' 部门名: '||r1.v_name||' 部门位置: '||r1.v_loc);
End;

输出：部门号: 30 部门名: SALES 部门位置: CHICAGO

2、例外处理

①NO_DATA_FOUND --SELECT ...INTO...语句没有返回任何数据时，会触发NO_DATA_FOUND 例外。

例：

DECLARE
TYPE record1 IS RECORD
(
v_no dept.deptno%type not null default 10,
v_name dept.dname%type,
v_loc dept.loc%type,
v char
);
r1 record1;
BEGIN
select deptno,dname,loc into r1.v_no,r1.v_name,r1.v_loc from dept where deptno=50;
dbms_output.put_line('部门号: '||r1.v_no||' 部门名: '||r1.v_name||' 部门位置: '||r1.v_loc);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('不存在该部门号，请检查');
End;

输出：不存在该部门号，请检查

②TOO_MANY_ROWS --SELECT ...INTO...语句返回多条数据时，会触发TOO_MANY_ROWS 例外。

例：

DECLARE
TYPE record1 IS RECORD
(
v_no dept.deptno%type not null default 10,
v_name dept.dname%type,
v_loc dept.loc%type,
v char
);
r1 record1;
BEGIN
select deptno,dname,loc into r1.v_no,r1.v_name,r1.v_loc from dept where deptno>=30;
dbms_output.put_line('部门号: '||r1.v_no||' 部门名: '||r1.v_name||' 部门位置: '||r1.v_loc);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('不存在该部门号，请检查');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.put_line('返回了多行数据');
End;

输出：返回了多行数据

3、WHERE子句使用注意事项

在WHERE子句中使用变量时注意，变量名不能与列名相同，否则会触发TOO_MANY_ROWS例外。

例：

declare
deptno dept.deptno%type :=10;
v_name dept.dname%type;
begin
select dname into v_name from dept where deptno=deptno;
exception
when too_many_rows then
dbms_output.put_line('返回了多条数据——例外！');
end;

输出：返回了多条数据——例外！

(二)操纵数据

在pl/sql中不仅可以使用SELECT...INTO...,还可以使用INSERT、UPDATE、DELETE、游标等。

1、INSERT --必须为表的主键列和NOT NULL列提供数据。

使用INSERT...VALUES...插入数据，语法：

INSERT INTO table|view [coumn1[,......]] VALUES (column1[,......]);

例：

declare
v_name varchar2(10) not null default 'china';
begin
insert into v_dept values(50,v_name,'beijing'); --使用变量
end;

使用子查询插入数据，语法：

INSERT INTO table|view [coumn1[,......]] subquery;

例：

begin
insert into v_dept select * from dept;
end;

2、UPDATE

使用表达式更新列值

语法：UPDATE table|view SET <column>=<value>[,<column>=<value>,......] [WHERE conditions];

例：

DECLARE
V char(10) not null :='项目部';
BEGIN
UPDATE V_DEPT SET DNAME=V WHERE DEPTNO=&N; --使用变量
END;

使用子查询更新列值

语法： UPDATE table|view SET (column1,column2,...)=(select column1,column2,... from table|view)

[WHERE conditions];

例：

begin
update v_dept set (deptno,dname,loc)=(select * from dept where deptno=10) ;
end;

3、DELETE

使用变量或数值删除数据

语法：DELETE FROM table|view [WHERE condition];

例：

declare
v_no constant number(2):=10;
begin
delete from v_dept where deptno=v_no;
end;

使用子查询删除数据

语法：DELETE FROM table|view [WHERE (column1,... )=(select column1,... from table|view)];

例：

begin
delete from v_dept where dname=(select dname from v_dept where deptno=10);
end;

4、游标

(三)使用事务控制语句

1、事务的作用：事务用于确保数据库数据的一致性，他由一组相关的DML语句组成。该组DML语句所执行的操作要么全部成功，要么全部取消。

2、数据库事务主要由INSERT、UPDATE、DELETE和SELECT...FOR UPDATE语句组成。当在应用程序中执行第一条SQL语句时，开始事务，当执行COMMIT或ROLLBACK语句时，结束事务。

3、事务和锁

当执行事务操作（DML语句）时，ORACLE会在被作用表上加表锁，以防止其他用户改变表结构。同时会在被作用行上加行锁，以防止其他事务在相应行上执行DML操作。

例1：加表锁

错误操作方法：

会话1：

SQL> update v_dept set dname='售货员' WHERE DEPTNO=10;

已更新 1 行。

会话2：

SQL> ALTER TABLE V_DEPT ADD AAA VARCHAR2(20);
ALTER TABLE V_DEPT ADD AAA VARCHAR2(20)
*
第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源

正确操作方法：

会话1：

SQL> update v_dept set dname='售货员' WHERE DEPTNO=10;

已更新 1 行。

SQL> commit;

提交完成。

会话2：

SQL> ALTER TABLE V_DEPT ADD AAA VARCHAR2(20);

表已更改。
例2：加行锁

会话1：

SQL> update v_dept set dname='研究员' where deptno=20;

已更新 1 行。

会话2：

SQL> update v_dept set dname='操作员' where deptno=40;

已更新 1 行。--立即执行

SQL> delete v_dept where deptno=20; --一直等待，直到会话1提交后才执行完成

例3：读脏数据

错误操作：

会话1：

SQL> DELETE V_DEPT;

已删除4行。

会话2：

SQL> SELECT * FROM V_DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

正确操作：

会话1：

SQL> DELETE V_DEPT;

已删除4行。

SQL> commit;

提交完成。

会话2：

SQL> SELECT * FROM V_DEPT;

未选定行

4、提交事务

使用commit语句可以提交事务。当执行commit语句之后，会确认事务变化，结束事务、删除保存点、释放锁。当使用commit语句结束事务之后，其他会话将可以查看到事务变化后的新数据。

当出现以下情况时会自动提交事务：

①当执行DDL语句时会自动提交事务。例如：CREATE TABLE、ALTER TABLE、DROP TABLE等语句。

②当执行DCL语句（GRANT、REVOKE）时。

③当退出SQL*PLUS时。

5、回退事务

保存点(savepoint)：保存点是事务中的一点，它用于取消部分事务。当结束事务时，会自动删除该事务所定义的所有保存点。在执行rollback命令时，通过指定保存点可以取消部分事务。

①设置保存点

|-savepoint a；

|-exec dbms_transaction.savepoint('a');

②取消部分事务

|-rollback to a；

|-exec dbms_transaction.rollback_savepoint('a');

③取消全部事务

|-rollback；

|-exec dbms_transaction.rollback;

注：当使用rollback取消全部事务时，会取消所有事务变化，结束事务，删除所有保存点并释放锁。

当出现系统灾难或应用程序地址例外时，会自动回退其事务变化。

6、只读事务

①设置只读事务

SET TRANSACTION READ ONLY
EXEC dbms_transaction.read_only

注：当设置只读事务时，该语句必须是事务开始的第一条语句。

②只读事务是指只允许执行查询操作，而不允许执行任何DML操作的事务。

例1：在只读事务中执行查询、更新、插入、删除操作

SQL> set transaction read only --设置只读事务
2 /

事务处理集。

SQL> select * from v_dept where deptno=10; --查询操作

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING 纽约

SQL> update v_dept set dname='上海' where deptno=10; --更新操作
update v_dept set dname='上海' where deptno=10
*
第 1 行出现错误:
ORA-01456: 不能在 READ ONLY 事务处理中执行插入/删除/更新操作

SQL> insert into v_dept values(50,'北京','中国'); --插入操作
insert into v_dept values(50,'北京','中国')
*
第 1 行出现错误:
ORA-01456: 不能在 READ ONLY 事务处理中执行插入/删除/更新操作

SQL> delete v_dept where deptno=20; --删除操作
delete v_dept where deptno=20
*
第 1 行出现错误:
ORA-01456: 不能在 READ ONLY 事务处理中执行插入/删除/更新操作

SQL> select * from v_dept for update; --SELECT...FOR UPDATE
select * from v_dept for update
*
第 1 行出现错误:
ORA-01456: 不能在 READ ONLY 事务处理中执行插入/删除/更新操作

③当使用只读事务时，可以确保用户取得特定时间点的数据。在设置了只读事务后，尽管其它会话可能会提交新事务，但只读事务将不会取得新的数据变化，从而确保取得特定时间点的数据信息。

例3：

会话1：

SQL> exec dbms_transaction.read_only; --设置只读事务，也就是指定了时间点，该事务不会取到其他会话在该操作之后提交的新事务

PL/SQL 过程已成功完成。

SQL> select * from v_dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     纽约
        20 RESEARCH       达拉斯
        30 SALES          芝加哥
        40 OPERATIONS     波士顿

会话2：

SQL> insert into v_dept values(50,'项目部','杭州'); --其他会话

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from v_dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     纽约
        20 RESEARCH       达拉斯
        30 SALES          芝加哥
        40 OPERATIONS     波士顿
        50 项目部         杭州

会话1：

SQL> exec dbms_transaction.read_only;

PL/SQL 过程已成功完成。

SQL> select * from v_dept;

SQL> select * from v_dept; --未取到其他会话提交的新事务所带来的数据变化

7、顺序事务

声明顺序事务语法：

set transaction isolation level serializable;

作用：顺序事务可以确保用户取得特定时间点的数据，并且允许执行DML操作。

例1：

会话1：

SQL> set transaction isolation level serializable; --声明顺序事务

事务处理集。

SQL> select * from v_dept;

会话2：

SQL> delete v_dept where deptno=50;

已删除 1 行。

SQL> commit;

提交完成。

SQL> select * from v_dept;

会话1：

SQL> select * from v_dept; --在会话2提交新事务后执行的查询操作

SQL> delete v_dept where deptno=10; --执行DELETE语句

已删除 1 行。

SQL> update v_dept set dname='研发部' where deptno=20; --执行UPDATE语句

已更新 1 行。

SQL> insert into v_dept values (60,'服务部','苏州'); --执行INSERT语句

已创建 1 行。

SQL> select * from v_dept; --经过以上操作后的结果

    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 研发部         达拉斯
        30 SALES          芝加哥
        40 OPERATIONS     波士顿
        50 项目部         杭州
        60 服务部         苏州

8、在PL/SQL中使用事务控制语句

当编写PL/SQL程序时，不仅可以直接嵌入SELECT...INTO和DML语句，也可以直接嵌入事务控制语句。

例1：在PL/SQL块中使用COMMIT和ROLLBACK语句

begin
    update v_dept set dname='bbb' where deptno=20;
   commit;
exception
   when others then
    rollback;
end;
例2：在PL/SQL块中使用ROLLBACK和SQVEPOINT语句

begin
   update v_dept set dname='ccc' where deptno=30;
   savepoint c;
update v_dept set dname='ddd' where deptno=40;
rollback to c;
commit;
exception
   when others then
    rollback;
end;