概述

版本

PL/SQL简介

创建账户，连接数据库

要先以管理员身份登录 : ./as sysdba 然后然后创建用户名和密码，授权，连接登录即可

Oracle数据类型

修改表

修改表名，修改列名，添加列，修改列（数据类型，长度，默认值），删除列

修改表名 rename student to students

添加address-列 alter table students add address varchar2(2);

修改address列 alter table students modify address varchar2(200);

修改列名address为stu_address alter table students rename column address to stu_address;

删除列stu_address alter table students drop column stu_address;

DML数据操作语言

插入数据（在这里的数据类型是：number，varchar2,char,date) insert into students values(1,‘xiaoming’,‘f’,1.23,‘01-12月-1997’)； insert into students(id,name)values(2,‘admin’);update修改数据 update students set height =1.45,birth=‘12-12月-1998’ where id=2;delete删除数据 delete from students where id=2;

TCL事务控制语言

commit，rollback

truncate清空表

truncate table students;

select 语句的基本语法

select*from emp;

算术运算符 + - * /

selecte ename,sal*12 from emp;

给列起别名

select ename as name;

distinct:去除重复行

select distinct deptno from emp;

字符串和字符串函数

返回字符串连接的值 select concat(‘hello’,‘students’)from dual; 这里使用的是虚拟表，因为链接字符串并不需要表格 select ‘sdfsa’||'sdfaf’from dual; 这里的||作用和concat的作用是一样的返回字符串的长度； select length(‘sfasfd’)from dual; select length(ename)from emp;//返回表中字段的长度upper，lower，initcap upper所有字符都变成大写，lower所有字符都变成小写，initcap所有单词的首字母变成大写 select upper(ename),lower(ename),initcap(‘i am students’)from emp;截取字符串的函数 trim:截取字符串两侧 select trim(‘e’ from ‘ehelle’)from dual; ltrim:截取字符串左边 select ltrim(‘ehelle’,‘e’)from dual; rtrim:截取字符串右边 select rtrim(‘ehelle’,‘e’)from dual;补位函数：位数不够，就会给补上$符号 左补位：lpad 右补位： rpad select lpad(sal,10,’ $ ‘)from emp; select rpad(sal,10,’ $ ')from emp;截取函数substr(要截取的字符串，从第几位开始，截？取多少个字符) select substr(‘hello’,2,3)from dual;获取字符的索引（啥玩意我也不大懂 ) select instr(‘hello’,‘l’)from dual;

数值类型和数值函数

round(浮点型数据，保留位数）四舍五入函数 select round(23.237,2)from dual; **注意：**如果保留位数是负数，表示截取到整数位的第几位；如果保留位数是0，表示只保留整数截断函数 trunc(浮点型数据，保留位数） select trunc(234.342,2)from dual;mod 两个数取余 select mod(3,2)from dual;ceil 和 floor ceil：返回大于等于一个小数的最小整数 floor：返回小于等于一个小数的最大整数 select ceil(45.234)from dual; select floor(45.234)from dual;

日期类型和日期函数

sysdate 获取当前系统时间 systimestamp 获取的时间格式更加精确，可以显示时区 select sysdate from dual; 这里没有精确到秒，可以通过如下函数进行转换 select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’)from dual; select systimestamp from dual;to_char把日期类型转换成字符串类型 to_char(日期，格式字符串） 格式规范： 例如：将系统时间转换为如下格式 2017年12月1日 select to_char(sysdate,‘yyyy"年"mm"月"dd"日"’）from dual;to_date(字符串，格式字符串)把字符串日期转换成日期类型 select to_date(‘2000-01-01’,‘yyyy-mm-dd’)from dual;last_day 返回当前月份的最后一天 select last_day(sysdate)from dual;next_day 返回一个月之后今天的日期 select add_months(sysdate,1)from dual;months_between 返回两个日期相差的月份数 select months_between(sysdate,hiredate)from emp;next_day 当前日期的下一个周的星期几的日期 select next_day(sysdate,2)from dual; 这是返回下周1的日期

NULL和通用函数

null做算术运算，结果为null 使用通用函数解决该问题 通用函数：nvl nvl2

select nvl(null,2)from dual; 表示第一个值不为空，返回第一个的值，否则返回第二个值

select nvl2(1,2,3)from dual; 表示第一个值不为空，就返回第二个值，否则返回第三个

nvl解决null问题(这里comm可能为null） select ename,sal+nvl(comm,0)from emp; 或者使用nvl2 select ename,sal+nvl2(comm,comm,0)from emp;

导入测试表

imp system/admin fromuser=scott touser=scott file=d:/scott.dmp

运算符

any和all条件运算符 查询工资大于（2500，3000，5000）员工的信息 selec*from emp where sal>any(2500，3000，5000);其他运算符 between… and…,like, is null between… and… like 表示一个占位符 % 表示0或多个

聚合函数

min(),max(),sum(),avg(),count() 所有的聚合函数都是忽略null的计算 例如：select avg(comm)from emp;这里只有不为空的comm行才参与运算

**有个结论（虽然我也不是很理解）：**没有被分组函数包含的列，必须粗现在group by的后边

关联查询

当查询两张表，如果不指定查询条件的话，结果会是两张表的笛卡尔积。 例如：select emp.*,dname,loc from emp,dept;

**内连接查询：**把满足条件的数据查出来 …inner join …on…

例如：查询员工的基本信息，部门名称 select emp.*,dname from emp inner join dept on emp.deptno=dept.deptno

使用表别名 select e.,dname from emp e inner join dept d on emp.deptno=dept.deptno 关于别名规范：要使用别名，那么sql语句的所有地方就都要使用别名，不使用就都不使用 省略inner join的内连接查询 select e.,dname from emp e,dept d where e.deptno=d.deptno(即：默认情况下使用的是内连接查询） **注意：**多表连接查询，链接n个表，至少要有n-1个有效条件

外连接查询

除了把满足条件的数据查出来，还要把不满足条件的数据查出来 **分类：**左外连接查询，右外连接查询，满外连接查询 左外：…left join…on… 右外：…right join…on… 满外连接:…full join…on…

自连接查询

通过将表用别名虚拟成两个表的方式实现，可以使等值或不等值连接 例如：查询员工的姓名和他部门经理的姓名 select e.ename,m.ename from emp e,emp m where e.mgr=m.empno

子查询

按照子查询和主查询执行的顺序分为：标准子查询（先执行子查询）相关子查询（先执行主查询） 相关子查询举例：查询工资超过本部门平均工资的员工信息 select*from emp e where sal>(select avg(sal)from emp where deptno=e.deptno); 这里先执行主查询，然后将主查询的结果用于子查询

select*from emp group by deptno having sal>avg(sal)

分页查询

**rownum:*查询一行添加一个行号，从1开始依次顺序添加 例如： 查询表中第一行的所有数据： select emp. from emp where rownum=1;

查询表中前三行的所有数据： select emo.* from emp where rownum<=3;

查询表中三行之后的所有数据： select emo.* from emp where rownum>=3; **注意：*这里一条数据也查不出来，因为rownum是一个伪列，即查出一条赋予一个行号，第一条数据行号是1，查询不出来，其他的也就没法查 将rownum作为结果集中可以使用的数据，通过行内视图实现 那么上边的查询可以通过如下方式实现： select from (select emp.,rownum rn from emp)where rn>=3 分页功能的实现 selectfrom（select emp.*,rownum rn from emp)where rn between 11 and 20;

decode函数和case语句

例如：查询员工表，按照员工的职位计算奖金的金额 职位是manager：12倍工资； analyst：11倍工资；其他：原始工资 select ename,job,sal, decode(job,‘manager’,sal12, ‘analyst’,sal11, ‘salseman’,sal*5, sal)bonus from emp;

----------------使用case实现 select ename,job,sal,case job when ‘manager’ then sal12 when ‘analyst’ then sal1.1 when ‘salseman’ then sal*5 else sal end bonus from emp;

计算人数，analyst/manager属于vip，其他都属于operation,vip和opeation计数 select decode(job, ‘manager’,‘vip’, ‘analyst’,‘vip’, ‘operation’) job, count(*) from emp group by decode(job, ‘manager’,‘vip’, ‘analyst’,‘vip’, ‘operation’);

排序函数

例如：按照部门分组，组内按照sal排序 **row_number(）: **排序过程中如果遇到相同的数据，排名会依次递增，不会出现相同的排名 select ename,deptno,sal, row_number(） over(partition by deptno order by sal) as emp_no from emp **rank(）: **排序过程中如果遇到相同的数据，排名会并列相同，但是下一个数据的排名会出现跳跃现象 select ename,deptno,sal, rank(） over(partition by deptno order by sal) as emp_no from emp **dense_rank(）: **排序过程中如果遇到相同的数据，排名会并列相同，下一个数据的排名不会出现跳跃现象 select ename,deptno,sal, dense_rank(） over(partition by deptno order by sal) as emp_no from emp

集合操作

UNION并集:对两个集合实现并集操作之后，将两个集合的结果合并到一块并去掉重复部分。 UNION ALL并集:对两个集合实现并集操作之后，不会去掉重复部分。 INTERSECT交集：显示同时存在在两个结果集中的数据 MINUS叉集:只有在第一个结果集中的存在在第二个结果集中不存在的数据 例如：查询工作是manager的员工和工资大于2500的员工 select ename,job,sal from emp where job=‘manager’ union (union all intersect minus) select ename,job,sal from emp where sal>2500

视图

创建视图：create or replace view view_name as select子句 这里加上or replace表示：如果不存在就创建视图，如果存在就修改视图

例如：create or replace view v_emp as select ename,hiredate from emp;

只读视图 read only create or replace view v_emp as select ename,hiredate from emp with read only; 删除视图 drop drop view v_emp;

序列

**什么是序列：**是用来生成唯一数据值的数据库对象 **作用：**用来生成表的主键值 序列的定义： create sequence dept_seq start with 100 increment by 10 使用序列： nextval:获取序列上的下一个值 select dept_seq.nextval from dual; currval：获取序列上的当前值 select dept_seq.currval from dual; 删除序列： drop sequence dept_seq

索引

创建索引 create index index_name on table_name(column):表示基于哪个表的哪个列来创建索引 当我进行如下查询的时候会自动使用到索引来提高查询效率：select*from emp where ename =‘king’; 删除索引 drop index emp_ename;

约束

定义约束的方式 有两种方式，在下边的sql中都有体现 create table test1(tid number(4) primary key, name varchar2(30) not null, //这两个是在创建表字段的时候添加约束 email varchar2(40), gender char(1), constraint email_un unique(email), constraint gender_ck check(gender=‘F’ or gender=‘M’)//这两个是在创建完字段以后再给字段添加约束，并指定约束名称 )

外键

注意：有主键的表叫主键表；有外键的表叫外键表 create table dept1( deptno number(4) primary key, dname varchar2(30), loc varchar2(40) )

create table emp1( empno number(4) primary key, ename varchar2(30), deptno, constraint emp1_dept_fk foreign key(deptno)references dept1(deptno) //这里是给deptno字段添加外键约束 ) **注意：**给表添加了外键约束以后，该字段中值必须要在主键列表中存在，否则会报错 insert into dept1 values(10,‘it’,‘beijing’); insert into emp1 values(1,‘admin’,10); insert into emp1 values(2,‘admin’,20);

给表字段添加约束 alter table 要添加的表名 add constraint t2_pk primary key(要添加的字段名);

PL/SQL —它是一种编程语言

–1.只定义执行部分 begin dbms_output.put_line(‘hello !’); end;

–2.包含声明和执行部分 declare v_ename varchar2(30);–定义变量 begin select ename into v_ename from emp–查出表中的值赋值给变量 where empno=7839; dbms_output.put_line(v_ename);–将变量的值输出 end;

–3.包含声明执行和异常部分的例子 declare v_ename varchar2(30);–定义变量 begin select ename into v_ename from emp–查出表中的值赋值给变量 where empno=123; dbms_output.put_line(v_ename);–将变量的值输出 exception when no_data_found then --当出现这个异常的时候执行下边的命令 dbms_output.put_line(‘查无此人！’);**注意：**这里字符串要使用单引号 end;

标识符

变量

定义基本数据类型 参照表的列类型 **格式：**表名.字段名%type 例如： 记录类型 格式： type 类型名 is record( 变量名1， 变量名2， … ); 例如： 参照记录类型 格式： 表名%rowtype

流程控制语句

IF语句

if举例

--if declare v_age number(3):=18; begin if(v_age>=18) then dbms_output.put_line('可以考取驾照！'); end if; end; --if else declare v_age number(3):=10; begin if(v_age>=18) then dbms_output.put_line('可以考取驾照！'); else dbms_output.put_line('不可以考取驾照！'); end if; end; -- if elseif else declare v_age number(3):=12; begin if(v_age>=18) then dbms_output.put_line('>=18'); elsif(v_age>=16) then dbms_output.put_line('>=16'); elsif(v_age>=12) then dbms_output.put_line('>=12'); else dbms_output.put_line('default！'); end if; end;

while循环语句

--打印1到10 --loop循环 declare v_x number(4):=1; begin loop dbms_output.put_line(v_x); v_x:=v_x+1; exit when v_x>10;--可以把这一行换成if(v_x>10)then exit;end if; end loop; end; --while loop declare v_x number(4):=1; begin while v_x<=10 loop dbms_output.put_line(v_x); v_x:=v_x+1; end loop; end; --for loop begin for i in 1..10 loop dbms_output.put_line(i); end loop; end;

游标

指向查询结果的指针，指针指向哪条记录，提取的就是哪条记录的数据 **注意：**每从游标中抓取一条数据，游标的指针就会下移 游标的使用

--查询10号部门的员工姓名和工资 declare v_ename emp.ename%type; v_sal emp.sal%type; cursor emp_cursor is select ename,sal from emp where deptno<100; begin open emp_cursor; fetch emp_cursor into v_ename,v_sal; while emp_cursor%found loop dbms_output.put_line(v_ename||','||v_sal); fetch emp_cursor into v_ename,v_sal;--这一行的目的是为了手动将指针下移 end loop; end;

参数化游标 查询参数不写死了，传入哪个部门就查询那个部门

declare v_ename emp.ename%type; v_sal emp.sal%type; cursor emp_cursor(p_deptno number) is select ename,sal from emp where deptno<p_deptno;--这里定义游标的时候定义一个形参， begin --在打开游标的时候传入一个实参 open emp_cursor(100); fetch emp_cursor into v_ename,v_sal; while emp_cursor%found loop dbms_output.put_line(v_ename||','||v_sal); fetch emp_cursor into v_ename,v_sal; end loop; end;

游标for循环

declare cursor emp_cursor is select ename,sal from emp where deptno<100; begin for e in emp_cursor loop dbms_output.put_line(e.ename||','||e.sal); end loop; end;

带参数的游标for循环

declare cursor emp_cursor(p_deptno number) is select ename,sal from emp where deptno<p_deptno; begin for e in emp_cursor(1200) loop dbms_output.put_line(e.ename||','||e.sal); end loop; end;

隐式游标的使用

这里的sql是一个隐式游标

异常

预定义异常

这种异常是程序中已经定义好的，有异常名及其对应的编号 –no_data_found

declare v_ename emp.ename%type; begin select ename into v_ename from emp where empno=111; dbms_output.put_line(v_ename); exception --这里表示这是一个处理异常的块 when no_data_found then --这是一个数据没有找到的异常 dbms_output.put_line('没有此人'); end;

–to_many_rows

declare v_ename emp.ename%type; begin select ename into v_ename from emp where empno<10000; dbms_output.put_line(v_ename);--这里是没有办法使用一个变量接受多个类型的值 exception when too_many_rows then dbms_output.put_line('返回的值太多了'); end;

–others可以匹配多种异常类型 如果如果有多个异常需要处理，others一定要放在最后处理

非预定义异常

也是程序中已经定义好的异常，只有编号没有异常名** 非预定义异常如何处理： 因为非预定义异常只有编号没有名称，所以我们在处理之前需要先声明一个异常变量，在调用一个函数把自定义异常名和编号进行绑定，接下来的处理过程就一样了

declare e_exception exception; pragma exception_init(e_exception,-02291);--将异常名和异常编号进行绑定 begin insert into emp values(119,'admin',null,null,null,50); exception when e_exception then dbms_output.put_line('没有50号部门'); dbms_output.put_line('错误编号'||sqlcode||','||'错误信息'||sqlerrm); end;

异常中常用到的两个函数：sqlcode返回当前异常的编号；sqlerrm返回当前异常的描述信息

函数和存储过程

就是给ps/sql块起个名字，如果用function起名字，这个块就是函数，如果用proceder就是过程

函数

–返回系统日期函数（无参），'yyyy-mm-dd’

create or replace function getDate return varchar2 is v_date varchar2(12); begin v_date:=to_char(sysdate,'yyyy-mm-dd'); return v_date; end; --调用函数 declare v_date varchar2(12); begin v_date:=getDate; --调用函数，没有参数不用传参 dbms_output.put_line(v_date); end;

–通过员工的编号返回员工的姓名

create or replace function getName(p_empno number) return varchar2 is v_ename emp.ename%type; begin select ename into v_ename from emp where empno=p_empno; return v_ename; end; --调用函数 declare v_ename varchar2(30); begin v_ename:=getName(1200); dbms_output.put_line(v_ename); end;

–返回两个数据的和，差 – 因为这里要返回两个值，所以这里使用到了输出参数p_x –其他两个都属于默认的输入参数

create or replace function getResult(p_a number,p_b number,p_x out number) return number is v_sum number(4); begin v_sum:=p_a+p_b; p_x:=p_a-p_b; return v_sum; end; **--调用函数** declare v_result number(4); v_x number(4); begin v_result:=getResult(2,1,v_x); dbms_output.put_line(v_result); dbms_output.put_line(v_x); end;

过程

函数和过程的区别是函数是必须要有返回值的，但是过程是没有返回值的，但是过程可以定义输出参数，所以过程也可以完成输出功能 –过程（就是关键字变了，其他都一样） –返回系统日期

create or replace procedure getDate1(p_date out varchar2) is begin p_date:=to_char(sysdate,'yyyy-mm--dd'); end; --调用函数 declare v_date varchar(12); begin getDate1(v_date); dbms_output.put_line(v_date); end;

包

相当于一个集合或者组合，包含了一组相关过程和函数的集合 类似于Java语言中的类的概念，

**注意：**包体名和你定义的包名一定要是一样的

–定义dept表进行添加删除修改和查询的例子，通过包实现 –定义包

create or replace package depttools is function addDept(p_deptno number,p_dname varchar2, p_loc varchar2)return number; function updateDept(p_deptno number,p_dname varchar2, p_loc varchar2)return number; procedure getAll; procedure deleteDept(p_deptno number); end;

–定义包实体

create or replace package body depttools is --addDept的实现 function addDepts(p_deptno number,p_dname varchar2,p_loc varchar2)return number is begin insert into dept values(p_deptno,p_dname,p_loc);--这里使用到了隐式游标 if sql%found then commit; --执行成功就提交 return 1; else return 0; end if end; --updateDept的实现 function updateDept(p_deptno number,p_dname varchar2, p_loc varchar2)return number is begin update dept set dname=p_dname,loc=p_loc where deptno=p_deptno; if sql%found then commit; return 1; else return 0; end if; end; --getAll的实现 procedure getAll is cursor dept_cursor is select*from dept; begin for d in dept_cursor loop dbms_output.put_line(d.deptno||','||d.dname); end loop; end; --deleteDept的实现 procedure deleteDept(p_deptno number) is begin delete from dept where deptno=p_deptno; commit; end; end;

–测试

declare v_x number; begin v_x:=depttools.addDept(50,'HR','BEIJING'); v_x:=depttools.updateDept(50,'IT','HANGZHOU'); depttools.deleteDept(50); end; SELECT*from EMP;