淡忘的Oracle知识点
7月部门来了校招的新人,我负责指导一位,首先他们要学Oracle,在指导的过程中发现,由于Oracle某些东西几乎没有在实际工作中使用过,已经淡忘了。下面记录一下容易忘记的点。
1. Oracle中客户端的安装
常用的oracle客户端工具有sqldeveloper和PLSQL Developer,PLSQL Developer需要安装,而sqldeveloper不需要安装,点击sqldeveloper.exe就可以直接运行。
2.级联删除和级联更新
级联删除
在添加foreing key约束时,可以指定级联操作的类型,主要用于当删除(on delete) 父表中的一条记录时,如何处理子表中的外键字段,有如下三种引用类型。
Oracle在外键的删除上有NO ACTION(类似RESTRICT)、CASCADE和SET NULL三种行为。
(1)NO ACTION
NO ACTION指当删除主表中被引用列的数据时,如果子表的引用列中包含该值,则禁止该操作执行。
建表时建立的外键如下时,没有指定级联的操作类型,但是默认的操作是NO ACTION。
CONSTRAINT FK_TB_STUDENT_CLASS_ID FOREIGN KEY (CLASS_ID) REFERENCES TB_CLASS (ID);
删除父表的一条记录时删除失败,原因是子表中的栏位有该值:
(2)SET NULL
SET NULL指当删除主表中被引用列的数据时,将子表中相应引用列的值设置为NULL值。SET NULL有个前提就是外键引用列必须可以设置为NULL。
把学生表(TB_STUDENT)的外键删除行为改为SET NULL。ORACLE似乎没有MODIFY CONSTRAINT操作,只能先删除外键,然后创建新的。
--删除学生表(TB_STUDENT)表的外键 ALTERTABLE TB_STUDENT DROPCONSTRAINT FK_TB_STUDENT_CLASS_ID; --删除添加ON DELETE SET NULL外键 ALTERTABLE TB_STUDENT ADDCONSTRAINT FK_TB_STUDENT_CLASS_ID FOREIGNKEY (CLASS_ID) REFERENCESTB_CLASS (ID) ONDELETE SET NULL; --删除一班 DELETEFROM TB_CLASS WHEREID=1;
(3)CASCADE
CASCADE指当删除主表中被引用列的数据时,级联删除子表中相应的数据行。
级联更新
Oracle本身并不支持外键的级联更新,不过可以使用trigger来达到级联更新的效果。
同样的,级联删除也可以使用trigger来达到目的,但是使用外键自带的方法更加方便。
3. ALL和ANY关键字
ALL()和ANY()括号里带的是一个集合。配合运算符使用。
例如:
>ALL(10,15,20) 表示大于子查询结果中的所有值,也就是查询大于20的值
>ANY(10,15,20) 表示大于子查询结果中的某个值,也就是查询大于10的值
4. 序列
https://blog.csdn.net/ethan_10/article/details/80690003
5. 插入日期时间类型的数据
create table datetest1 ( d DATE, t TIMESTAMP(9) ); insert into datetest1(d,t) values(date'2020-7-23',timestamp'2020-7-23 13:24:52.234123211'); insert into datetest1(d,t) values(to_date('2020-7-23 10:20:30','YYYY-MM-DD HH24:MI:SS'), to_timestamp('2020-7-23 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
6. DBMS输出
目前使用的oracle工具是SQL Developer。
(1) 想要DBMS输出,首先需要点击 查看—DBMS输出
(2) 打开后发现界面是灰色的,还是不能显示输出
(3) 点击绿色加号,选择要连接的DB,发现界面变成白色,就可以显示了
7. Oracle查询默认排序问题
Oracle的一道题目:
3.写一个PL/SQL程序块,循环调用500次,循环INSERT到图书books表中使书号递增(书号规则Z00001开始一直到Z00500,其他栏位自定义)
当循环递增的书号为Z00050时,UPDATE 编号为Z00040的书号的价格为0.
检查新人答题写的SQL语句时发现,用select查询出来的结果不是按顺序的,想到之前自己做题查询结果都是显示是递增的,以为是写的语句有逻辑错误。经过自己新增一个序列的字段测试,发现实际上是递增的,只不过使用没有条件的查询时,select * from的结果显示是乱序的。百度上有人说select的结果如果有主键是按主键排序的,没有的话就是乱序。这个回答我觉得不正确,因为这个table是有主键的,也没有按照主键排序。要是说是乱序的,虽然说查询结果不是正序,但每次查询结果都是一样的,可见select的结果有一定的规则。
以下是新增一个序列,和BOOKS_TEST新增一个栏位放序列值。
CREATE SEQUENCE BOOKS_TEST_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE ORDER NOCYCLE CACHE 10 / DECLARE BN BOOKS_TEST.BNO%TYPE; BEGIN FOR A IN 1..500 LOOP IF A<10 THEN BN := 'Z0000'||A; INSERT INTO BOOKS_TEST(BNO,PRICE,SEQ)VALUES(BN,A,BOOKS_TEST_SEQ.NEXTVAL); ELSIF A < 50 THEN BN := 'Z000'||A; INSERT INTO BOOKS_TEST(BNO,PRICE,SEQ)VALUES(BN,A,BOOKS_TEST_SEQ.NEXTVAL); ELSIF A = 50 THEN UPDATE BOOKS_TEST SET PRICE='0' WHERE BNO='Z00040'; ELSIF A < 100 THEN BN := 'Z000'||A; INSERT INTO BOOKS_TEST(BNO,PRICE,SEQ)VALUES(BN,A,BOOKS_TEST_SEQ.NEXTVAL); ELSE BN := 'Z00'||A; INSERT INTO BOOKS_TEST(BNO,PRICE,SEQ)VALUES(BN,A,BOOKS_TEST_SEQ.NEXTVAL); END IF; END LOOP; END;
查询结果如下图,虽然BNO不是从1开始显示,但是根据序列判断是递增的。