Oracle数据库基础学习DAY4

1、插入数据-insert
·语法
insert into tablename [column1 ...] values (value1 ...);
//不写列名为空要对应写null


2、数据更新-update
·语法
update tablename set column = value [where ...];


3、删除数据-delete
·语法
delete from tablename [where ...];
注：delete删除数据，truncate清空表数据，drop删除表


4、事务的概念
·Oracle提供了一种被称为“事务”的控制机制，来对数据进行有效、
安全的控制，是数据库中的数据始终处于一个数据一致性的状态。


5、事务控制的基本语句及其功能
·Oracle提供的事务控制语句有：
·提交事务（commit）
·回滚事务（rollback）
·设置保存点（savepoint）
·回滚到保存点（rollback to savepoint）
·设置事务的属性（set transaction）
·设置可延迟约束的检验时机（set constrants）
·事务的开始与结束
开始
·连接到数据库，并开始执行第一条DML语句时
·当前一个事务结束或者执行一条自动提交事务的语句时
结束
·执行一条commit语句或者rollback语句
·执行一条会自动提交事务的语句
·执行一条DML语句失败时
·会自动提交的事务
·执行一条DDL语句
·执行一条DCL语句
·断开与数据库的连接
·设置事务
就是对即将开始的事务的性质进行一种控制
·设置事务有如下几种指令
set transaction read only
用于设置只读事务，只读事务中只能进行数据查询
·提交事务
commit命令来完成事务的提交
·回滚事务
rollback命令来完成事务的回滚


6、事务的性质与状态
·原子性
·一致性
·隔离性
·持久性


7、事务的性质与状态-原子性
·一个事务是一个不可分割的逻辑单位，一个事务中的所有操作
要么都被成功完成，都不做要么


8、事务的性质与状态-一致性
·一致性是指：一个语句，一个事务操作后的结果必须要使数据库中的
所有数据处于逻辑上的一致性的状态


9、事务的性质与状态-隔离性
·一个事务内部的操作与使用的数据，对其他事务的隔离的，并发执行的
各个事务之间不能互相干扰


10、事务的性质与状态-持久性
·持久性是指一个事务一旦提交成功，它对数据库数据的修改也就是永久性的保存下来


11、创建表
-- Create table
create table order
(
  orderid       number not null,
  ordername     varchar2(20),
  orderdate     date,
  ordercategroy number
)
;
-- Create/Recreate primary, unique and foreign key constraints 
alter table order
  add constraint PK_id primary key (ORDERID);
alter table order
  add constraint FK_category foreign key (ORDERCATEGROY)
  references dept (DEPTNO) on delete set null;
注：级联删除cascade，比如你删除某个表的时候后面加这个关键字
    会在删除这个表的同时删除和该表有关系的其他对象
12、修改表的列名
-- Add/modify columns 
alter table CUSTOMER modify ORDERNAME VARCHAR2(40);


13、视图简介
·视图（View）实际上是一张或多张表上的预定义查询，这些表称为基表。
从视图中查询信息与从表中查询信息的方法完全相同。只需要简单的SELECT...FROM即可


14、创建视图
语法结构：创建视图
CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name AS SELECT 查询
[WITH READ ONLY CONSTRAINT]
语法解析：
·OR REPLACE:如果视图已经存在，则替换旧视图。
·FORCE:即使基表不存在，也可以创建该视图，但是该视图不能正常使用，
       当基表创建成功后，视图才能正常使用。
·NOFORCE:如果基表不存在，无法创建视图，该项是默认选项。
·WITH READ ONLY:默认可以通过视图对基表执行增删改查操作，
但是有很多在基表上的限制，设置后说明视图为只读视图。
例子：
CREATE OR REPLACE VIEW EMPDETAIL
AS
SELECT EMPNO,ENAME,JOB,HIREDATE,EMP.DEPTNO,DNAME
FROM scott.EMP JOIN scott.DEPT ON EMP.DEPTNO=DEPT.DEPTNO
WITH READ ONLY;


15、创建序列
CREATE SEQUENCE sequence_name [STAET WITH num]
[INCRENENT BY increment][MAXVALUE num|NOMAXVALUE]
[MINVALUE num|NOMINVALUE][CYCLE|NOCYCLE]
[CACHE num|NOCACHE]
① START WITH：从某一个整数开始，升序默认值是1，降序默认值是-1。


② INCREMENT BY：增长数。如果是正数则升序生成，如果是负数则降序生成。
升序默认值是1，降序默认值是-1。


③ MAXVALUE：指最大值。


④ NOMAXVALUE：这是最大值的默认选项，升序的最大值是：1027，降序默认值是-1。


⑤ MINVALUE：指最小值。


⑥ NOMINVALUE：这是默认值选项，升序默认值是1，降序默认值是-1026。


⑦ CYCLE：表示如果升序达到最大值后，从最小值重新开始;如果是降序序列，
达到最小值后，从最大值重新开始。


⑧ NOCYCLE：表示不重新开始，序列升序达到最大值、降序达到最小值后就报错。
   默认NOCYCLE。


⑨ CACHE：使用CACHE选项时，该序列会根据序列规则预生成一组序列号。
保留在内存中，当使用下一个序列号时，可以更快的响应。
当内存中的序列号用完时，系统再生成一组新的序列号，
并保存在缓存中，这样可以提高生成序列号的效率。
Oracle默认会生产20个序列号。


⑩ NOCACHE：不预先在内存中生成序列号。
例子：
-- Create sequence 
create sequence seq_test
start with 1
increment by 1
cache 30;


16、索引概述
Oracle对象教程：索引(Index)创建使用，
索引：对数据库表中的某些列进行排序，便于提高查询效率。


当我们在某本书中查找特定的章节内容时，可以先从书的目录着手，
找到该章节所在的页码，然后快速的定位到该页。
这种做法的前提是页面编号是有序的。
如果页码无序，就只能从第一页开始，一页页的查找了。


数据库中索引(Index)的概念与目录的概念非常类似。
如果某列出现在查询的条件中，而该列的数据是无序的，
查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特定列中的数据排序，
生成独立的索引表。在某列上创建索引后，如果该列出现在查询条件中，
Oracle会自动的引用该索引，先从索引表中查询出符合条件记录的ROWID，
由于ROWID是记录的物理地址，因此可以根据ROWID快速的定位到具体的记录，
表中的数据非常多时，引用索引带来的查询效率非常可观。


·如果表中的某些字段经常被查询并作为查询的条件出现时，
就应该考虑为该列创建索引。


·当从很多行的表中查询少数行时，也要考虑创建索引。
有一条基本的准则是：当任何单个查询要检索的行少于或者等于整个表行数的10%时，
索引就非常有用。


Oracle数据库会为表的主键和包含唯一约束的列自动创建索引。
索引可以提高查询的效率，但是在数据增删改时需要更新索引，
因此索引对增删改时会有负面影响。


语法结构：创建索引


CREATE [UNIQUE] INDEX index_name ON table_name(column_name[,column_name…])


语法解析：


1. UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引。


2. index_name：指定索引名。


3. tabl_name：指定要为哪个表创建索引。


4. column_name：指定要对哪个列创建索引。我们也可以对多列创建索引;
   这种索引称为组合索引。
例子：
-- Create/Recreate indexes 
create index UK_AA on CUSTOMER (ORDERNAME)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255;


17、PL/SQL简述
PL/SQL简介，Oracle PL/SQL语言(Procedural Language/SQL)是
结合了结构化查询与Oracle自身过程控制为一体的强大语言，
PL/SQL不但支持更多的数据类型，拥有自身的变量声明、赋值语句，
而且还有条件、循环等流程控制语句。
过程控制结构与SQL数据处理能力无缝的结合形成了强大的编程语言，
可以创建过程和函数以及程序包。


PL/SQL是一种块结构的语言，它将一组语句放在一个块中，
一次性发送给服务器，PL/SQL引擎分析收到PL/SQL语句块中的内容，
把其中的过程控制语句由PL/SQL引擎自身去执行，
把PL/SQL块中的SQL语句交给服务器的SQL语句执行器执行。


PL/SQL块发送给服务器后，先被编译然后执行，
对于有名称的PL/SQL块(如子程序)可以单独编译，
永久的存储在数据库中，随时准备执行。PL/SQL的优点还有：


·支持SQL
SQL是访问数据库的标准语言，通过SQL命令，
用户可以操纵数据库中的数据。PL/SQL支持所有的SQL数据操纵命令、
游标控制命令、事务控制命令、SQL函数、运算符和伪列。同时PL/SQL和SQL语言紧密集成，PL/SQL支持所有的SQL数据类型和NULL值。


·支持面向对象编程
PL/SQL支持面向对象的编程，在PL/SQL中可以创建类型，
可以对类型进行继承，可以在子程序中重载方法等。


·更好的性能
SQL是非过程语言，只能一条一条执行，
而PL/SQL把一个PL/SQL块统一进行编译后执行，
同时还可以把编译好的PL/SQL块存储起来，以备重用，
减少了应用程序和服务器之间的通信时间，PL/SQL是快速而高效的。


·可移植性
使用PL/SQL编写的应用程序，可以移植到任何操作系统平台上的Oracle服务器，
同时还可以编写可移植程序库，在不同环境中重用。


·安全性
可以通过存储过程对客户机和服务器之间的应用程序逻辑进行分隔，
这样可以限制对Oracle数据库的访问，
数据库还可以授权和撤销其他用户访问的能力。


18、PL/SQL块
pl/sql块的语法，PL/SQL是一种块结构的语言，
一个PL/SQL程序包含了一个或者多个逻辑块，
逻辑块中可以声明变量，变量在使用之前必须先声明。
除了正常的执行程序外，PL/SQL还提供了专门的异常处理部分进行异常处理。
每个逻辑块分为三个部分，语法是：


语法结构：PL/SQL块的语法
[DECLARE
     --declaration statements]  ①
BEGIN
     --executable statements  ②
[EXCEPTION
     --exception statements]  ③
END;


语法解析：
① 声明部分：声明部分包含了变量和常量的定义。这个部分由关键字DECLARE开始，
    如果不声明变量或者常量，可以省略这部分。


② 执行部分：执行部分是 PL/SQL块的指令部分，由关键字BEGIN开始，
    关键字END结尾。所有的可执行PL/SQL语句都放在这一部分，
    该部分执行命令并操作变量。其他的PL/SQL块可以作为子块嵌套在该部分。
    PL/SQL块的执行部分是必选的。注意END关键字后面用分号结尾。


③ 异常处理部分：该部分是可选的，
该部分用EXCEPTION关键字把可执行部分分成两个小部分，
之前的程序是正常运行的程序，一旦出现异常就跳转到异常部分执行。


PL/SQL是一种编程语言，与Java和C#一样，除了有自身独有的数据类型、
变量声明和赋值以及流程控制语句外，PL/SQL还有自身的语言特性：


PL/SQL对大小写不敏感，为了良好的程序风格，开发团队都会选择一个合适的编码标准。
比如有的团队规定：关键字全部大些，其余的部分小写。


PL/SQL块中的每一条语句都必须以分号结束，SQL语句可以是多行的，
但分号表示该语句结束。一行中可以有多条SQL语句，他们之间以分号分隔，
但是不推荐一行中写多条语句。


点击新建测试窗口
-- Created on 2018/3/21 by XIONG 
declare 
  -- Local variables here
  i integer;
begin
  -- Test statements here
  i := 1; --赋值使用:=
  dbms_output.put_line(i);
end;


19、PL/SQL变量声明
Oracle培训教程：PL/SQL变量声明，PL/SQL支持SQL中的数据类型，
PL/SQL中正常支持NUMBER,VARCHAR2,DATE等Oracle SQL数据类型。
声明变量必须指明变量的数据类型，也可以声明变量时对变量初始化，
变量声明必须在声明部分。声明变量的语法是：


语法格式：声明变量
变量名 数据类型[ :=初始值]
语法解析：
数据类型如果需要长度，可以用括号指明长度，比如：varchar2(20)。
代码演示：声明变量
SQL> DECLARE
  2       sname VARCHAR2(20) :='jerry';  ①
  3  BEGIN
  4       sname:=sname||' and tom';  ②
  5       dbms_output.put_line(sname);  ③
  6  END;
  7  /jerry
PL/SQL procedure successfully completed


代码解析：
① 声明一个变量sname，初始化值是“jerry”。字符串用单引号，
  如果字符串中出现单引号可以使用两个单引号(’’)来表示，
  即单引号同时也具有转义的作用。
② 对变量sname重新赋值，赋值运算符是“:=”。
③ dbms_output.put_line是输出语句，可以把一个变量的值输出，
  在SQL*Plus中输出数据时，可能没有结果显示，可以使用命令：
  set serveroutput on设置输出到SQL*Plus控制台上。


对变量赋值还可以使用SELECT…INTO 语句从数据库中查询数据对变量进行赋值。
但是查询的结果只能是一行记录，不能是零行或者多行记录。


代码演示：变量赋值
SQL> DECLARE
  2       sname VARCHAR2(20) DEFAULT 'jerry';  ①
  3  BEGIN
  4       SELECT ename INTO sname FROM emp WHERE empno=7934;  ②
  5       dbms_output.put_line(sname);
  6  END;
  7  /
MILLER
PL/SQL procedure successfully completed


代码解析：
① 变量初始化时，可以使用DEFAULT关键字对变量进行初始化。
② 使用select…into语句对变量sname赋值，要求查询的结果必须是一行，
  不能是多行或者没有记录。

20、PL/SQL查询赋值
DECLARE
sal scott.emp.sal%TYPE; --①
mysql number(4) := 3000;
totalsal mysal%TYPE; --②
BEGIN
SELECT SAL INTO sal FROM scott.emp WHERE empno = 7934;
totalsal := sal + mysal;
dbms_output.put_line(totalsal);
END;


21、PL/SQL流程控制
·条件控制
·IF语句格式一
IF 条件 THEN
--条件结构体
END IF;
例子：
DECLARE
newSal scott.emp.sal%TYPE;
BEGIN
SELECT sal INTO newSal FROM scott.emp WHERE ename = 'JAMES';
IF newSal > 900 THEN
UPDATE scott.emp SET comm = 800 WHERE ename = 'JAMES';
END IF;
COMMIT;
END;

·IF语句格式二
IF 条件 THEN
--条件1成立结构体
ELSIF 条件2 THEN
--条件2成立结构体
ELSE
--以上条件都不成立结构体
END IF

·CASE语句
CASE [selector]
WHEN 表达式1 THEN 语句序列1;
WHEN 表达式2 THEN 语句序列2;
WHEN 表达式3 THEN 语句序列3;
...
[ELSE 语句序列N];
END CASE;
·循环控制
·LOOP循环
LOOP
--循环体
END LOOP;
注：可以使用EXIT或者EXIT WHEN 条件终止循环，否则为死循环
例子：
-- Created on 2018/3/21 by XIONG 
declare
  -- Local variables here
  counter   number(3) := 0;
  sumResult number := 0;
begin
  -- Test statements here
  LOOP
    counter   := counter + 1;
    sumResult := sumResult + counter;
    IF counter >= 100 THEN
      EXIT;
    END IF;
  END LOOP;
  dbms_output.put_line('result is :' || to_char(sumResult));
end;

·WHILE循环
WHILE 条件 LOOP
--循环体
END LOOP;
注：先判断条件再进入循环体
例子：
-- Created on 2018/3/21 by XIONG 
declare
  -- Local variables here
  counter   number(3) := 0;
  sumResult number := 0;
begin
  -- Test statements here
  WHILE counter < 100 LOOP
    counter   := counter + 1;
    sumResult := sumResult + counter;
  END LOOP;
  dbms_output.put_line('result is :' || to_char(sumResult));
end;

·FOR循环
FOR 变量 IN 变量范围 LOOP
--循环体
END LOOP;
例子：
-- Created on 2018/3/21 by XIONG 
declare
  -- Local variables here
  counter   number(3) := 0;
  sumResult number := 0;
begin
  -- Test statements here
  FOR counter IN 1 .. 100 LOOP
    sumResult := sumResult + counter;
  END LOOP;
  dbms_output.put_line('result is :' || to_char(sumResult));
end;

·GOTO语句
GOTO 标签 --GOTO目的地必须同一块或以上
注：不推荐使用。

·NULL语句


22、PL/SQL游标
·游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。
 
游标属性 描述
%FOUND 布尔型属性，如果最近的FETCH语句取到了记录，属性值为TRUE
%NOTFOUND 布尔型属性，如果最近的FETCH语句没有返回一条记录，
属性值为TRUE，与%FOUND相反。
%ISOPEN 布尔型属性，用来代表游标是否打开，当游标已打开时返回TRUE；
%ROWCOUNT 数字型属性，用来代表目前FETCH到的记录的总行数，
即返回已从游标中读取的记录数。


·利用游标可以单独操纵结果集中的每一行。
游标在定义以后存在两种状态：关闭和打开。

·显式游标（Explicit Cursor）：
在PL/SQL程序中定义的、用于查询的游标称作显式游标。

·隐式游标（Implicit Cursor）：
是指非PL/SQL程序中定义的、而且是在PL/SQL中使用UPDATE/DELETE语句时，
Oracle系统自动分配的游标。

·显式游标使用步骤：
·定义
declare
cursor c_name is select ename form emp;
·打开
open c_name
·使用
FETCH c_name INTO v_enamel;
·关闭
close c_name;

·隐式游标使用步骤：
BEGIN
UPDATE SCOTT.emp SET sal=sal+100 WHERE empno=7876;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('成功修改雇员工资！');
ELSE
DBMS_OUTPUT.PUT_LINE('修改雇员工资失败！');
END IF;
END;


i、for循环游标


循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。
……
for 变量名 In 游标名
loop
数据处理语句；
end loop；


ii、loop循环游标


……
loop
fetch 游标名 into 临时记录或属性类型变量；
exit when 游标名%notfound；
end loop；


iii、while循环


……
open 游标名
fetch 游标名 into 临时记录或属性类型变量；
while 游标名%foundloop
-- do something
fetch 游标名 into 临时记录或属性类型变量；
end loop;
……
close 游标名