Oracle学习0811

07.集合操作

将两个或多个SQL 查询结果合并构成符合查询

常用的集合操作符包括：

• UNION：并运算

  结果等同于集合运算中的并运算

  select employee_id, last_name
  from employees
  where last_name like 'C%' or last_name like 'S%'
  union
  select employee_id, last_name
  from employees
  where last_name like 'S%' or last_name like 'T%';
  

  结果会消除其中重复的行

• UNION ALL：

  包含两个子结果集中重复的行

• INTERSECT：交运算

  UNION基本上是一个OR运算

  INTERSE则比较像AND

  使用intersect集合操作，在查询结果中共集中保留LAST_NAME以S开头的雇员。

  select employee_id, last_name
  from employees
  where last_name like 'C%' or last_name like 'S%'
  intersect
  select employee_id, last_name
  from employees
  where last_name like 'S%' or last_name like'T%';
  

• MINUS：差运算

  可以找到两个给定的集合之间的差集

  将返回LAST_NAME以C开头的那些雇员

  select employee_id, last_name
  from employees
  where last_name like 'C%' or last_name like 'S%'
  minus
  select employee_id, last_name
  from employees
  where last_name like 'S%' or last_name like'T%';
  

在使用集合操作符编写查询时的规则：

1. 各select语句指定的列必须在数量上和数据类型上相匹配
2. 不允许在构成复合查询的各个查询中规定ORDER BY 子句
3. 不允许在BLOB\LONG这样的大数据对象上使用集合操作符

08.子查询

1. IN关键字

   使用子查询查看所有部门在某一地区（1700）的雇员信息

   select employee_id, last_name, department_id
   from employees
   where department_id in (
                           select department_id
                           from departments
                           where locationg_id = 1700);
   

2. EXISTS关键字

   只需要考虑是否满足判断条件，而数据本身并不重要。

   只注重子查询是否返回行，如果子查询返回一个或多个行，那么EXISTS便返回TURE , 否则为FALSE。

   select employee_id, last_name
   from employees em 
   where exists (
                  select * from departments dep
                  where em.department_id = dep.department_id
                  and locationg_id = 1700);
   

3. 比较运算符

   等于(=)，不等于(<>)，小于(<) ，大于、小于等于、大于等于

   将薪资大于本职位平均薪资的雇员信息显示出来。

   select employee_id, last_name, job_id, salary
   from employees
   where job_id = 'PU_MAN' 
   and salary >=(select avg(salary) from employees
                  where job_id = 'PU_MAN');
   

04.数据操控

01.insert语句

1. insert语句

同MySQL

insert into jobs(job_id, job_title, min_salary, max_salary)
values('IT_TEST','测试员'，3000.00,8000.00)

2. 批量insert

   用select语句替换values语句

02.UPDATE语句

语法形式

update employees
set salary = salary*15
where job_id = 'IT_PROG';

未使用where子句限定修改的行，则会更新整个表

可以使用select语句的查询结果来实现更新数据。

update employees
set salary = (select avg(salary)
              from employees
               where job_id = 'IT_PROG')
where employee_id = 104;

03.delete语句

delete from table_name
where employee_id = 107;

04.truncate语句

删除表中所有的记录

还可以使用关键字

reuse storage：表示删除记录后仍然保存记录占用的空间

drop storage：删除记录后立即回收记录占用的空间

delete语句可以使用rollback来恢复数据，而truncate语句不能。

05.数据控制

1.grant语句

SQL用grant语句向用户授予操作权限，grant语句的一般格式为：

grant <权限>[,<权限>]...
[on<对象类型><对象名>]
to<用户>[,<用户>]...
[with grant option]

接受权限的用户可以是一个或多个具体用户也可以是public，即全体用户

如果指定了with grantoption子句，则获得某种权限的用户还可以把这种权限再授予其他的用户。否则，只能使用该权限，不能传播该权限。

• 例子：把查询IT_EMPLOYEES表的权限授给用户User1

grant select 
on table IT_EMPLOYEES
to user ;

• 把查询IT_EMPLOYEES表和修改雇员编号的权限授给用户User4，并允许将此权限在授予其他的用户。

  grant update(employee_id), select 
  on table IT_EMPLOYEES to User4
  with grant option;
  

2.revoke语句

授予的权限可以由DBA或其他授予者用revoke语句收回。

一般格式：

revoke <权限>
on <对象类型><对象名>
from <用户>

1. 把用户User4修改雇员编号的权限收回、

   revoke update(employee_id)
   on table IT_EMPLOYEES
   FROM User4;
   

   收回权限的操作会级联下去

   系统之收回直接或间接从删除权限的用户处获得的权限。

06.Oracle常用函数

01 字符类函数

1. ASCII()

   select ASCII('a') BIG_A, ASCII('a') SMALL_A FROM dual ;
   

2. CHR()

   select chr(97) from dual;
   

3. CONCAT(C1 , C2)

   将C2连接到C1后面

   select concat('oracle', '11g') name from daul ;
   

4. INITCAP(c1)

   将c1中每个单词的第一个字母大写，其他字母小写返回。

   select initcap('oracle universal installer') name from dual;
   

5. INSTR(c1,[c2,[,j]])

   返回c2在c1中第j次出现的位置，搜索从c1的第i个字符开始。

6. LENGTH(c1)

   返回c1的长度

7. LOWER(c1)

   将c1全体变为小写

8. LTRIM(c1, c2)

   将c1中最左边的字符去掉，是其第一个字符不在c2中

9. REPLACE(c1, c2[,c3])

   用c3代替c1中出现的c2后返回。

10. SUBSTR(c1, [,j])

    表示从c1的第i位开始返回长度为j的子字符串。

02.数字类函数

03.日期类函数

04.转换类函数

05.聚集类函数

第四章 Oracle PL/SQL语言及编程

4.1 PL/SQL简介

01.PL/SQL的基本结构

PL/SQL的大体结构如下：

DECLARE
    --声明一些变量、常量、用户定义的数据类型以及游标等
    --这一部分可选，如不需要可以不写
BEGIN
    --主程序体，在这里可以加入各种合法语法
EXCEPTION
    --异常处理程序，当程序中出现错误时执行这一部分
END;--主程序体结束

02.PL/SQL注释

1. 单行注释

   由两个连字符开始，一直到行尾（回车符标志着注释的结束）

2. 多行注释

   有/开头，由*/结尾

03.PL/SQL字符集

1. 合法字符集
2. 分界符

04.PL/SQL数据类型

1. 数字类型

   数字类型	NUMBER	PLS_INTEGER	BINARY_INTEGER
   该类型的变量可存储	整数或浮点数	整数	整数

   number(p,s) ，其中P是精度(数值中所有有效数字的个数)，S是刻度范围（小数点右边数字位的个数）

2. 字符类型

   字符类型	VARCHAR2	CHAR	LONG	NCHAR	NVARCHAR2

   • VARCHAR2可以存储变长字符串，声明语法为：

     VARCHAR2 (MaxLength);
     

   • CHAR 表示定长字符串

     char(Maxlength);
     

3. 日期类型

   DATE

4. 布尔类型

   BOOLEAN—主要用于控制程序流程

   一个布尔类型变量的值可以是TURE、FALSE或NULL。

5. type定义的数据类型

   定义数据类型的语句格式如下：

   type <数据类型名> is <数据类型>;
   

   在Oracle中允许用户定义两种数据类型，RECORD(记录类型)和TABLE(表类型)。

   type teacher_record is RECORD
   (
   TID NUMBER(5) NOT NULL:=0,
   NAME VARCHAR2(50),
   TITLE VARCHAR2(50),
   SEX CHAR(1)
   );
   

   • 定义一个teacher_record类型的记录变量ateacher。

     ateacher teacher_record;
     

     引用这个记录变量时要指明内部变量，如ateacher.id或ateacher.name

     • %TYPE： 单属性的数据类型

     • %ROWTYPE：整个属性列表的结构

       用于声明与表的列相匹配的变量和用户定义数据类型

       type teacher_record is record
       (
       TID TEACHERS.TID%TYPE NOT NULL:=0,
       NAME TEACHERS.NAME%TYPE,
       TITLE TEACHERS.TITLE%TYPE,
       SEX TEACHERS.SEX%TYPE);
       

     定义一个与表TEACHERS的结构类型一致的记录变量

     teacher_record TEACHERS%ROWTYPE;
     

05.PL/SQL 变量和常量

1. 定义常量

   <常量名> constant<数据类型>:=<值>;
   

   Pass_Score constant INTEGER:=60;
   

2. 定义变量

   <变量名><数据类型>[(宽度):=<初始值>];
   

   address VARCHAR2(30);
   

06.PL/SQL语句控制结构

1. 选择结构

   IF语句

   命令格式

   if(条件表达式1) then
      {语句序列1;}
   [elseif(条件表达式2) then
      {语句序列2;}]
   [else 
      {语句序列3;}]
   end if ;
    
   

   CASE语句

   命令格式

   case 检测表达式
   where 表达式1 then 语句序列1
   where 表达式2 then 语句序列2
   ...
   where 表达式n then 语句序列n
   [else 其他语句序列]
   END;
   

   • 根据学生的考试等级获得对应分数范围

     declare
       v_grade VARCHAR2(20):='及格';
       v_score VARCHAR2(50);
     BEGIN
       v_score:= CASE V_grade
       where '不及格' then '成绩<60'
       where '及格' then '60<= 成绩 <70'
       where '中等' then '70<= 成绩 <80'
       where '良好' then '80<= 成绩 <90'
       where '优秀' then '90<= 成绩 <=100'
     else '输入有误'
     end;
     

2. NULL结构

   在于程序块中添加NULL检查。

3. 循环结构

   • LOOP…EXIT…END语句

     control_var:=0;                --初始化control_var为0
     LOOP                           --开始循环
        IF control_var>5 THEN       --如果control_var的值大于5则退出循环
             EXIT;
        END IF;
        contro_var:=control_var+1;  --改变control_var的值
     END LOOP;
       
     

   • LOOP…EXIT WHEN …END语句

     control_var:=0;                --初始化control_var为0
     LOOP                           --开始循环
        EXIT WHEN control_var>5 THEN--如果control_var的值大于5则退出循环
        contro_var:=control_var+1;  --改变control_var的值
     END LOOP;                      --循环尾
     

   • WHILE…LOOP…END语句

     control_var:=0;                --初始化control_var为0
     WHILE control_var<=5 LOOP--如果control_var的值小于或等于5则循环
        contro_var:=control_var+1;  --改变control_var的值
     END LOOP;  
     

   • FOR…IN…LOOP…END语句

     FOR control in 0...5 Loop  --control_var从0到5进行循环
         NULL;                  --因为for语句自动给control_var加1，故这里是一个空操作。
     END LOOP;
     

   • goto语句

     goto label;
     

     无条件转向语句。

07.PL/SQL表达式

1. 字符表达式

   唯一的字符运算符—并置运算符"||"

   作用：把几个字符串连在一起，如：

   ‘Hello’||‘World’||'!‘的值等于’Hello World!’

2. 布尔表达式

   三个布尔运算符：

   AND 、OR 和NOT