学习oracle数据库的总结
大多时候我都是在偷懒的，学过的知识也不会去一一总结，所有导致需要用的时候就只剩一点印象。对于写学习oracle的这份总结，也是拖了好久，现在才终于马马虎虎的总结出来，写这份总结一来是觉得自己记性不好，肯定容易忘记，方便回顾使用；二呢，是希望能给大家学习oracle的时候带来一点帮助。
一、数据库的三个用户
1、普通用户：scott用户的密码：tiger
2、普通管理员：system用户的密码：manager
3、超级管理员：sys用户的密码：change_on_install
二、创建用户及赋权
1、创建一个名为sunflower的用户，密码为sun
creater user sunflower identified by sun;
2、给sunflower用户赋予所有权限
grant all privileges to sunflower;
权限：dba、resource、connect
dba:拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构
resource：拥有resource权限的用户只可以创建实体，不可以创建数据库结构
connect：拥有connect权限的用户只可以登录oracle，不可以创建实体，不可以创建数据库结构
3、修改sunflower用户的密码为123456
alter user sunflower indentified by 123456;
4、强制删除sunflower用户
drop user sunflower cascade;
三、事务处理
事务处理：就是保证数据操作的完整性，所有的操作要么同时成功，要么同时失败。
1、提交事务：commit;
2、回滚事务：rollback;
3、设置回滚点：savepoint 回滚点名
三、sql语句
sql(Structured Quety Language,机构化查询语言)分为：
1、DML(Data Manipulation Language,数据操作语言)：用于检索或者修改数据；
2、DDL（Data Definition Language,数据定义语言）：用于定义数据的结构，如创建、修改、删除数据库对象；
3、DCL（Data Control Language,数据控制语言）：用于定义数据库用户的权限。
3.1、简单查询语句
1、查询emp表
select * from emp;
2、查询emp表中员工编号、姓名、薪水
select empno,ename,sal from emp;
3、查询emp表中的所有工作，要求去重复
select distinct job from emp;
4、要求查询出雇员的编号、姓名、工作，以 编号是：7369 的雇员，姓名是：SMITH，工作是：CLERK 的格式显示
select '编号是：'||empno||' 的雇员，姓名是：'||ename||'，工作是：'||job from emp;
5、查询所有员工的姓名、工作、年薪
select ename,job,sal*12 as yearsal from emp;
3.2、限定查询
1、在emp表中查询出所有工资大于2000的员工
select ename,sal from emp where sal>2000;
2、查询每个月可获得奖金的雇员
select ename,comm from emp where comm is not null;
3、要求查询出基本工资大于1500且可以获得奖金的雇员
select ename,sal,comm from emp where sal>1000 and comm is not null;
4、要求查询出基本工资大于1500或者可以获得奖金的雇员
select ename,comm from emp where sal>1000 or comm is not null;
5、查询基本工资大于2000，小于3000的员工
select ename,sal from emp where sal>2000 and sal<3000;
或者
select ename,sal from emp where sal between 2000 and 3000;
6、查询出在1981年雇员的所有员工
select ename,hiredate from emp where hiredate between '1-1月-81' and '31-12月-81';
7、要求查询SMITH的个人信息
select * from emp where ename='SMITH';
8、查询出编号是7499、7566、7844、7900雇员的信息
select * from emp where empno in (7499,7566,7844,7900);
9、查询出编号不是7499、7566、7844、7900雇员的信息
select * from emp where empno not in (7499,7900);
10、要求查询所有姓名中带‘A’的员工
select ename from emp where ename like '%A%';
11、要求查询所有姓名中第二个字母是‘A’的员工
select ename from emp where ename like '_A%';
3.3、对查询结构排序
1、要求按工资由低到高的顺序排序
select * from emp order by sal;
2、要求查询出10部门的员工信息，并按工资的降序排序，如果共阿紫相等，则按雇佣日期先后排序
select * from emp where deptno=10 order by sal desc,hiredate asc;
4、要求查询部门名称、员工编号、员工姓名、员工工资，用row_numbe()函数将每个部门的工资按降序排序。（row_number():同一组中返回连续的排序号）
select d.dname,e.deptno,e.ename,e.sal,row_number()
over(partition by e.deptno order by e.sal desc) as step
from emp e,dept d where e.deptno = d.deptno;
5、要求查询部门名称、员工编号、员工姓名、员工工资，用rank()函数将每个部门的工资按降序排序。（rank(): 同一组中，具有相等值的行排序号相同，随后排序号会跳跃）
select d.dname,rank()
over (partitin by e.deptno order by e.sal desc) as step
from emp e,dept d were e.deptno = d.deptno;
5、要求查询部门名称、员工编号、员工姓名、员工工资，用dense_rank()函数将每个部门的工资按降序排序。（dense_rank()：同一组中，具有相等值的行排序号相同，随后排序号是连续的）
select d.dname,dense_rank()
over (partition by e.deptno order by e.sal desc) as step
from emp e,dept d where e.deptno = d.deptno;
3.4、函数
3.4.1、日期函数
1、获取当前日期及时间:sysdate、systimestamp、current_date
select sysdate from dual;
select systimestamp from dual;
select current_date from dual;
2、日期加减
a、获取明天的当前时间（以天为单位）
select sysdate+1 from dual;
b、取当前时间前一小时(以天为单位，需要将天转换为小时)
select sysdate-1/24 from dual;
3、增加/减去月份add_months(date,month),能够自动处理大小月及润月
a、查询日期2016-12-12增加一个月后的日期
select add_months(date'2016-12-12',1) month from dual;
b、查询日期2016-12-12减少一个月后的日期
select add_months(date'2016-12-12',-1) month from dual;
c、查询当前日期增加一个月后的日期
select add_months(sysdate,1) month from dual;
4、months_between(date1,date2):日期1与日期2相差的月数
a、查询系统当前时间与2016-03-03之间相差的月数
select months_between(sysdate,date'2016-03-03')from dual;
select months_between(sysdate,to_date('2016-03-03 12:30:56','yyyy-mm-dd HH24:MI:SS'))from dual;
b、查询日期2016-02-03与日期2016-03-03之间相差的月数
select months_between(date'2016-02-03',date'2016-03-03')from dual;
c、在emp表中计算出各雇员从入职到现在的年月日各是多少
select ename,trunc(Months_between(sysdate,hiredate)/12) year,
trunc(MOD(Months_between(sysdate,hiredate),12)) months,
trunc(MOD(sysdate-hiredate,30)) day from emp ;
5、next_day(date,day):根据指定日期，加上day天，day不可为0或者负数
a、查询日期2016-11-02加两天后的日期
select next_day(to_date('201601102','yyyymmdd'),2) from dual;
select next_day(date'2016-11-02',2) from dual;
6、last_day(date):根据指定日期类型，获得月最后一天日期
a、获取当前系统时间中该月的最后一天
select last_day(sysdate) from dual;
b、变通：获得当月第一天日期select add_months(last_day(sysdate)+1,-1) firstDay from dual;
7、trunc(date1,'DD')-trunc(date2,'DD'):以天为单位计算两个日期差
select trunc(sysdate,'dd')-trunc(date'2016-04-03','dd') from dual;
8、trunc(date):截取date类型中的日期部分（即去掉了时间部分）
select trunc(SYSDATE) from dual;
9、extract(date)：返回日期中某一部分的内容,可选的参数为data类型、timestamp，如果为date类型，只支持year、month、day,如果要支持hour，则需要使用timestamp
a|获得年份
select extract(year from (sysdate) from dual;
b、获得月份
select extract(month from (sysdate) from dual;
c、获得日
select extract(day from (sysdate) from dual;
如果要获得小时、分钟、秒，则需要用到timestamp
a、获得小时
select extract(hour from systimestamp) from dual;
注意：由于小时是按时区取的，所以会与当前系统小时数不符，需要作进一步处理：
select extract(hour from cast(systimestamp as timestamp)) from dual;
b、获得分钟
select extract(minute from systimestamp) from dual;
c、获得秒
select extract(second from systimestamp) from dual;
二、字符函数
1、initcap(char) ：将字符串中每个单词首字母改成大写，单词与单词之间以空格或非字母字符分隔
a、将emp中所有员工姓名的首字母大写
select initcap(ename) from emp;
2、lower(char):将大写字母转换成小写
a、将 HOLLE WORLD 转换成小写
select lower('HOLLE WORLD') from dual;
3、upper(char)：将小写字母转换成大写
a、将hello转换成大写
select upper('hello') from dual;
4、ltrim(char,set) ：去掉char中左侧所包含的set内容，如果不写set参数，则去掉char左侧的空格
a、去掉字符串左侧的空格
select ltrim(' hello') from dual;
b、去掉hello中的he字符串
select ltrim('hello','he') from dual;
5、rtrim(char,set):用法同ltrim(),去掉char中右侧所包含的set内容，如果不写set参数，则去掉char左右则的空格
a、去掉字符串右侧的空格
select ltrim('hello ') from dual;
b、去掉hello中的llo字符串
select ltrim('hello','llo') from dual;
6、trim(char): 去掉字符串char前后两段的空格
select trim(' hello ' ) from dual;
7、translate(char,from,to):将char按照from、to的关系进行位置调换
select translate('jack','j','b') from dual; --返回 back
8、replace(char,searchstring,[rep string]): 字符串替换
参数：char：原字符串
searchstring:需要替换的字符串
[rep string]:替换过后的字符串,如果不写该参数，则表示char中去掉searchstring对应的字符串
a、去掉字符串'jack and jue'中的'j'
select replace('jack and jue','j') from dual; --返回ack and ue
b、将字符串'jack and jue'中的'j'替换成'b'
select replace('jack and jue','b') from dual;--返回back and bue
9、instr (char,char1)： 取得子串char1在字符串char的位置
select instr ('worldwide','d') from dual; --返回5 ，索引从1开始
select instr ('worldwide','d',-1) from dual; --返回8
10、substr(char,m[,n])字符串截取。
参数：char：表示要截取的字符串
m：表示截取的开始位置。
n:表示截取的个数
select substr('abcdefg',3,2) from dual; --返回cd
11、concat (expr1,expr2)：连接两个字符串,作用同||
select concat('good','morning') from dual;
select 'good'||'morning' from dual;
12、ascii(char): 返回字符串首字母ASCII码
select ascii('A') from dual; --返回65
select ascii('a') from dual; --返回97
select ascii('&') from dual; --返回38
13、chr(n):返回ASCII码所对应的字符
select chr(65) from dual; --返回A
14、length(char): 返回字符串长度
select length('good') from dual; --返回4
15、decode(char1,char2,res1,res2):比较函数,如果char1==char2,则返回res1,否则返回res2
select decode('good','good','相等','不相等') from dual; --返回相等
select decode('good','good2','不相等') from dual; --返回不相等
a、要求查询出雇员姓名、工作，将工作替换成相应的中文含义
select ename 姓名,decode(job,'CLERK','业务员','SALESMAN','销售员','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁') 职位 from emp;
16、lpad/rpad(char,len,str):在字符串char前面/后面添加str，添加过后的字符串长度不能大于len
select LPAD('abc',4,'trr') from dual; --返回 tabc
select LPAD('abc',10,'trr') from dual; --返回 trrtrrtabc
select RPAD('abc','trr') from dual; --返回 abct
select RPAD('abc','trr') from dual; --返回 abctrrtrrt
三、数字函数
1、abs(n):取绝对值
select abs(-1) from dual;
2、ceil(n):取大于等n的最小整数
select ceil(102) from dual; --返回102
select ceil(102.5) from dual; --返回103
3、cos(n):返回n的余弦值
select cos(180) from dual;
4、sin(n):返回n的正弦值
select sin(0) from dual;
5、floor(n):返回<=n的最大整数
select floor(100.9) from dual; --返回100
6、power(m,n):返回m的n次幂
select power(2,3) from dual; --返回8
7、mod(m,n):取模
select mod(10,3) from dual; --返回1
8、round(m,n)：四舍五入,参数n表示保留的小数
select round(203.56,1) from dual; --返回 203.6
9、trunc(m,n) ：截取数字，n表示保留的小数
select trunc(203.56,1) from dual; --返回203.5
10、sqrt(n):开根号
select sqrt(4) from dual; --返回2
11、sign(n):返回数字n的正弦值
select sign(-30) from dual; --返回-1
四、转换函数
1、to_char(date[,fmt])：将日期转换为字符串
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy-mm-dd HH24:MI:SS') from dual;
select to_char(date'2016-12-12','MM') from dual; --返回月份：12
select to_char(date'2016-12-12','day') from dual; --返回英文星期几
select to_char(date'2016-12-12','w') from dual; --返回一月中第几周
select to_char(date'2016-12-12','ww') from dual; --返回一年中第几周
2、to_date(char[,fmt]):将字符串转换为日期
select to_date('2016-12-12 23:14:20','yyyy-mm-dd hh24:mi:ss') from dual; --hh24表示24小时制，hh12表示12小时制
3、to_number(char):将字符串转换为数字
select to_number('23.5') from dual;
五、其它函数
1、nvl(expr1,expr2):用来将null转换为指定的值，即如果expr1为null，则返回expr2，否则就返回expr1
a、将奖金列中值为空时将其值设置为0
select comm,nvl(comm,'0') from emp;
2、nvl(expr1,expr2,expr3):作用同nvl,如果expr1不为null,返回expr2,否则返回expr3
b、如果comm列中，值不为空时返回1，为空时返回0
select comm,nvl2(comm,'1','0') from emp;
3、nullif(expr1,expr2):用于比较两个表达式的值是否相等，如果相同，则返回expr1
select nullif(sysdate,sysdate) from dual; --返回null
select nullif(50,50) from dual; --返回null
六、分组函数
1、count()：统计行数
select count(*) from emp;
select count(1) from emp; --统计第一列的个数
select count(empno) from emp;
2、avg()：求各行的平均值
select avg(sal) from emp; --求平均工资
3、sum(): 求和
select sum(sal) from emp; --求总工资
4、min():求各行最小值
select min(sal) from emp; --求所有员工中工资最低的是多少
5、max()：求各最大值
select max(sal) from emp; --求所有员工中工资最高的是多少
6、分组案例
a、统计各部门有多少员工
select deptno,count(deptno) from emp group by deptno;
b、统计各部门最高工资是多少
select deptno,max(sal) from emp group by deptno;
c、统计各部门最低工资是多少
select deptno,min(sal) from emp group by deptno;
d、统计各部门最高工资,并且只显示>2850的工资
select deptno,max(sal) from emp group by deptno having max(sal)>2850
四、多表查询
4.1、等值连接
1、要求查询除雇员的编号、姓名、部门编号、部门名称
select e.empno,d.deptno,d.dname from emp e,dept d
where e.deptno = d.deptno;
2、要求查询每个雇员的姓名、工作、雇员的直接上级领导的姓名和雇员的部门名称
select e.ename,e.job,m.ename,emp m,dept d
where e.mgr = m.empno and d.deptno = e.deptno;
3、要求查询每个雇员的姓名、员工薪水等级、雇员的直接上级领导的姓名、领导的薪水等级和雇员的部门名称
select e.ename,s.grade,m.sal,ms.grade
from emp e,salgrade s,salgrade ms,dept d
where e.mgr = m.empno and e.deptno = d.deptno and
(e.sal between s.losal and s.hisal )and (m.sal between ms.losal and ms.hisal);
4.2、左、右连接
1、要求查询每个雇员的编号、姓名、工作、雇员的直接上级领导的姓名、编号
--用左连接查询出最高领导king
select e.ename,e.empno,m.empno
from emp e,emp m
where e.mgr = m.empno(+）；
2、要求查询员工的部门编号、部门名称及姓名
--用右连接查询出没有员工的部门
select e.ename,d.dname
from emp e,dept d
where e.deptno(+) = d.deptno;
4.3、sql:1999语法
1、用交叉连接查询emp表及dept表的所有信息(产生笛卡尔积）
select * from emp cross join dept;
2、用自然连接查询emp表及dept表的所有信息(自动进行关联字段的匹配，不会产生笛卡尔积）
select * from emp natural join dept;
3、用USING子句根据部门编号查询部门编号为10的所有员工的信息（直接关联操作列）
select * from emp join dept using(deptno)
where deptno=10;
4、用ON子句根据部门编号查询部门编号为10的所有员工的信息
select * from emp e join dept d on(e.deptno = d.deptno)
where d.deptno = 10;
五、子查询
1、要求查询出工资比7566高，同时与7788从事相同工资的全部雇员信息
select * from emp where sal >
(select sal from emp where empno =7566) and job =
(select job from emp where empno =7788);
2、要求查询部门名称、部门平均工资、部门人数、及部门中收入最高的雇员姓名及工资
select distinct e.ename,d.dname，p.c,p.s,p.m
from (select count(deptno) c,trunc(avg(sal),2) s,max(sal) m,deptno from emp group by deptno) p,emp e,dept d
where e.deptno = d.deptno and e.deptno = p.deptno and e.sal = p.m;
3、要求查询每个部门工资最低的雇员信息
select * from emp where sal in (select min(sal) from emp group by deptno);
4、要求查询出工资比2300，3500，4500任意一个大的员工信息
select * from emp where sal >any(2300,3500,4500);
5、要求查询出比员工7788，7566，7698工资大的所有员工信息
select * from emp where sal>all(select sal from emp where empno in(7788,7698));
六、数据库更新操作
将emp表的表结构及数据复制到表emp_copy中
create table emp_copy as select * from emp;
6.1、添加数据
1、新增一个雇员（1234，“sunflower”，“students”，null，sysdate，0，0，40）到emp_copy表中
insert into empCopy(empno,mgr,hiredate,comm,deptno)
values(1234,'sunflower','student',null,sysdate,40);
6.2、修改数据
1、将sunflower雇员的工资修改为最高工资
update emp_copy set sal = (select max(sal) sal from emp_copy );
6.3、删除数据
1、删除编号是1234的雇员
delete from emp_copy where empno = 1234;
七、常用的数据类型
1、varchar、varchar2:表示一个字符串，最大长度255
2、number：number（n)表示一个整数，数字长度为n，可使用int；
number（m，n）表示一个小数，小数点后的数字长度为n，整数长度为m-n，可以使用float。
3、date：日期类型
4、clob：大对象，表示大文本数据，可放4G文本
5、blob：大对象，表示二进制数据，可放4G二进制数据
八、建表
1、请新建一个students的学生表，属性：id、name、age、sex、class_id
a、创建学生表
create table students(
id number(2),
name varchar2(10),
age number(3),
sex varchar2(10),
class_id number(2)
)
b、创建班级表
create table c_class(
id number(2) primary key,
)
c、创建成绩表
create table s_grade(
sid number(2),
cid number(2),
lunguage varchar2(10),
math varchar2(10)
)
2、向students表中插入数据
insert into students(id,name,age,sex,class_id) values(10,'小白'，12，'男',1);
insert into students values(20,'小黑'，13，'男',2);
3、复制表
a、将students表结构和内容一起复制到copy_emp1表
create table copy_students1 as (select * from students);
b、将emp表的表结构复制到copy_emp2表
create table copy_students2 as (select * from students where 1=2);
4、修改表
a、将students表中学号为20的同学的班级改为1
update students set class_id =1 where id=20;
b、为students表增加address列，加入默认值
alter table students add(address varchar2(100) default '湖南');
c、将students表中class_id字段的长度改为4
alter table students modify(class_id number(4));
d、将students表中class_id字段删除
alter table students drop column class_id ;
5、重命名表
a、将students表重命名为s_student
rename students to s_student;
6、删除表
a、执行delete操作删除表时，指定where条件则是删除表中一行数据；
delete from s_student where id=20;
b、不指定where子句则是删除表中所有记录,表结构还在
delete from s_student;
c、执行truncate操作删除表时，会隐士提交和删除表中所有记录但不删除表结构
truncate table s_student;
d、执行drop操作删除表时，会删除表数据及表结构，是否表所占用的空间
drop table s_student;
7、截断表
a、如果相应清空一张表的数据且不需回滚时，可以执行truncate操作释放资源
truncate table s_student;
七、约束
1、主键约束
a、将s_student表中id设为主键
alter table s_student add constraint pk_id primary key(id);
b、给s_student表中class_id 添加外键约束
alter table s_student add constraint FK_classid foreign key(class_id) references c_class(id);
c、将s_grade表中sid、cid设置为联合主键
alter table s_student add constraint PK_sgrade primary key (sid,cid)；
2、非空约束
a、将s_student表中name设为非空约束
alter table s_student modify(name not null)；
3、唯一约束
a、将s_student表中id设为唯一约束
alter table s_student add constraint UK_student unique(id)；
b、去除唯一约束
alter table s_student drop constraint UK_student cascade；
4、检查约束
a、为s_student表中age设置检查约束
alter table s_student add constraint CK_student check(age between 0 and 100)
5、删除约束(删除指定的约束)
alter table s_student drop constraint CK_student;
注意：在子表中设置的外键在父表中必须时主键、删除时应先删除字表，再删除父表
6、强制删除表的方法
drop table s_student cascade constraint;
八、行号：rownum
1、查询emp表中前5条记录
select rownum,empno,emane,job from emp where rownum <= 5;
2、查询emp表中前6—10条记录
select * from (select rownum,job from emp where rownum <= 10) t where t.rownum>5;
2、查询emp表中后5条记录
select * from (select rownum,job from emp where rownum <= 15) t where t.rownum>=10;
九、集合操作
1、并 union：将多个查询的结果组合到一个查询之中，不包含重复值
create table emp1 as select * from emp wehre deptno = 10;
select * from emp union select * from emp1;
2、union all：将多个查询的结果组合到一个查询之中，包含重复值
select * from emp union all select * from emp1;
3、交 intersect：返回多个查询结果中相同的部分
select * from emp intersect select * from emp1;
4、差 minus：返回两个查询结果的差集
select * from emp minus select * from emp1;
十、视图
1、创建一个简单视图，要求包含10部门员工的信息
create view emp_view as select empno,job from emp where deptno =10;
2、创建复杂视图，数据来emp表和dept表
create or replace view v_emp as
select e.empno,e.hiredate,dept d where e.deptno = d.deptno;
3、查询视图
select * from emp_view;
4、修改视图
update emp_view set deptno = 20 where empno = 7782
5、向视图中插入数据
insert into emp_view values(1234,'大白','学生');
6、删除视图
drop view emp_view;
7、创建视图不能更新的约束
create or replacle view emp_v as select * from emp where deptno = 20 with check option;
8、创建只读视图
create or replacle view emp_1 as select * from emp where deptno = 20 with read only;
十一、序列
1、创建序列
create sequence seq_s --序列名字
start with 1 --生成的序列号从1开始
maxvalue 99 --生成的序列号最大值99
minvalue 1 --最小值1
increment by 1 --增长值1
nocycle --（默认）不循环使用（cycle循环使用）
cache 10; --缓存10个序列号，默认20个
2、插入学生信息
insert into students values(seq_s.nextval,'小米');
insert into students values(seq_s.nextval,'小红');
insert into students values(seq_s.nextval,'小花');
3、使用序列有两种方法
a、查询序列生成的下一个值
select seq_s.nextval from dual;
b、查看序列当前的值
select seq_s.currval from dual;
4、删除序列
drop sequence seq_s;
十二、同义词
1、创建私有同义词
语法：create synonym 同义词名 for 用户名.表名;
create synonym e for scott.emp;
查表
select * from e;
2、创建公共同义词
create public synonym e for scott.emp;
修改数据
update e set sal = 2222 where ename = 'SMITH';
3、删除同义词
drop public synonym e;
4、替换同义词
creat or replace synonym e for scott.dept;
十三、PL/sql
PL/sql是过程语言与结构化查询语言的一个整合，是对sql的一个扩展。
特性：
1.支持多种数据类型，大对象等等，还可以使用循环，条件控制。
2.可以创建存储过程，触发器，程序包，可以给sql语句的执行添加程序逻辑。
3.具备可移植性，灵活性和安全性。
4.支持面向对象。
5.支持sql语言。数据操纵语言，事务控制语言，游标控制，sql函数和sql运算符。
6.性能更佳,提前编译，直接执行。
7、扩展类型
a、行类型：%rowtype，一个表中一行的类型
b、列类型：%type,一个表中某一列的类型
1、用if语法，查询smith的薪资，如果薪资是一千元以下涨薪500，如果是一千以上涨200
declare
--声明变量
mysal emp.sal%type;
myempno emp.empno%type :=&empno;
begin
select sal into mysal from emp
where empno = myempno;
if mysal < 1000 then
update emp set sal = mysal+500
where empno = myempno;
else
update emp set sal = mysal+200
where empno = myempno;
end if;
end;
2、用case语法，用户输入abcd，依次输出优秀，良好，及格，要努力哦
begin
case '&grade'
when 'A' then dbms_output.put_line ('优秀');
when 'B' then dbms_output.put_line ('良好');
when 'C' then dbms_output.put_line ('及格');
when 'D' then dbms_output.put_line ('要努力哦');
end case;
end;
3、用loop循环输出1-100
declare
num1 number(3) :=1;
begin
loop
dbms_output.put_line(num1);
num1 := num1+1;
exit when num1>100;
end loop;
end;
4、用while循环输出1-100
declare
num1 number(3) :=1;
sum1 number(4) :=0;
begin
while num1<=100 loop
sum1:=sum1+num1;
num1:=num1+1;
end loop;
dbms_output.put_line(sum1);
end;
5、用for循环求6的阶层
declare
res number(3) :=1;
begin
for i in 1..6 loop
res:=res*i;
end loop;
dbms_output.put_line(res);
end;
6、顺序控制
a、练习，找员工号为7369的员工，如果薪资是一千元以下涨薪200
declare
mysal emp.sal%type;
myempno emp.empno%type := &empno;
begin
select sal into mysal from emp
where empno = myempno;
if mysal >1000 then
goto updation; --GOTO 跳转到哪里
else
goto quit;
end if;
<<updation>>
update emp set sal = mysal+200
where empno = myempno;
update emp set sal = mysal-500
where empno = myempno;
<<quit>>
null; --null 什么都能不做
end;
7、动态sql语句
语法： execute immediate sql语句 using 参数列表
a、练习，查询用户输入的员工编号，看此员工是否存在
declare
sql_str varchar2(200);
myename emp.ename%type;
mysal emp.sal%type;
myempno emp.empno%type :=&empno;
begin
execute immediate
'create table stu2(id number(2),name varchar(10))';
sql_str := 'select ename,sal from emp where empno = :id';
execute immediate sql_str into myename,mysal using myempno;
dbms_output.put_line(myename||'薪资'||mysal);
end;
查询
select * from stu2;
8、异常
a、练习，查询emp表中薪资为三千的员工，打印员工信息
declare
emp_row emp%rowtype; --用来存储一行数据
mysal emp.sal%type :=&sal;--用户输入查询的薪资
begin
select * into emp_row from emp where sal = mysal;
dbms_output.put_line(emp_row.ename||'薪资'||emp_row.sal);
exception
when too_many_rows then
dbms_output.put_line('你查询的行太多');
when no_data_found then
dbms_output.put_line('没有薪资为'||mysal||'的员工');
end;
9、自定义异常
a、要在声明部分定义异常
b、在业务处理时，业务逻辑不满足条件，则显示抛出异常 raise 异常的变量名
c、Exception 写出针对此异常的处理
d、练习、查询部门编号为用户输入的部门编号 50
select * from dept;
declare
myname dept.dname%type; --存储查询出来的部门名称
mydeptno dept.deptno%type :=&deptno;--用来接收用户输入的部门编号
my_exception Exception;--当部门不存在的时候抛出多的异常变量
begin
if mydeptno not in(10,20,30,40) then
raise my_exception;
else
select dname into myname from dept where dept.deptno = mydeptno;
dbms_output.put_line(myname);
end if;
Exception
when my_exception then
dbms_output.put_line('没有此部门');
end;
10、游标：存储数据的临时结果集。
1.隐式游标 oracle自动创建，Dml数据操纵语言时，自动创建4个属性：sql%found、sql%notfound、sql%rowcount、sql%isopen ——false关闭
2.显示游标：用来存储多行数据的一个临时表三种特殊的表现形式：带参数，参数列表跟在游标名后。for循环，不用打开关闭游标。where curren of 游标名 作用，修改游标中的数据，返回到表中
3.引用游标 ：ref 游标主要用来与动态sql联合在一起用，不确定sql语句，使用时绑定sql语句
11、显示游标
--显示游标必须要显示的定义在pl/sql块的声明部分
--显示游标当中可以存储多条记录在内存中
--如果要操作显示游标 ，必须要执行如下四步
--1.声明游标 cursor 游标名字 is 查询语句
--2.打开游标 open 游标名字
--3.通过fetch 关键字移动指针
--4.关闭游标 close 游标名字
a、定义一个显示游标，包含数据为部分编号为10的emp表数据
declare
--定义游标
cursor cur_dept_emp is select ename,sal from emp where deptno = 10;
myname emp.ename%type;
mysal emp.sal%type;
begin
--打开游标
open cur_dept_emp;
fetch cur_dept_emp into myname,mysal;--移动游标指针至开始行
while cur_dept_emp%found loop --游标里是否有数据
dbms_output.put_line(myname||'薪资'||mysal);--打印数据
--移动游标
fetch cur_dept_emp into myname,mysal;--移动游标指针至下一行
end loop;
--关闭游标
close cur_dept_emp;
end;
b、用loop循环查询部门名为10 的所有员工的薪资及姓名
declare
cursor cur_emp is select * from emp where deptno = 10;--声明游标
myrow emp%rowtype; --声明变量存储游标中一行数据
begin
open cur_emp;--打开游标
fetch cur_emp into myrow;
loop
dbms_output.put_line(myrow.ename||' 薪资 '||myrow.sal);
fetch cur_emp into myrow;
exit when cur_emp%notfound;--当游标所有的数据全部取出时推出循环
end loop;
close cur_emp;--关闭游标
end;
c、使用for 循环输出10部门员工名字和薪资
--for循环不用显示等待打开游标，关闭游标，也不用移动指针
declare
cursor cur_emp is select * from emp
where deptno = 10;
begin
for myrow in cur_emp loop
dbms_output.put_line(myrow.ename||'薪资'||myrow.sal);
end loop;
end;
d、使用带有参数的游标
--语法：cursor 游标名（参数名 参数类型）is 查询语句
--如果使用for 变量名 in 游标名（参数值）
--如果是loop循环 open 游标名（参数值）
declare
cursor cur_emp(mydeptno emp.deptno%type) is select * from emp
where deptno = mydeptno;
begin
for myrow in cur_emp(&deptno) loop --或者直接传参数值 如 10
dbms_output.put_line(myrow.ename||'薪资'||myrow.sal);
end loop;
end;
e、显示游标对应修改数据
--练习：用户输入部门编号，将对应部门中所有员工薪资提高200
declare
cursor cur_emp(mydeptno emp.deptno%type) is select * from emp
where deptno = mydeptno for update ;
myrow emp%rowtype;
begin
open cur_emp(&deptno);
fetch cur_emp into myrow;--移动指针将第一行数据取出存到myrow变量中
loop
update emp set sal = myrow.sal + 200
where current of cur_emp;
fetch cur_emp into myrow;
exit when cur_emp%notfound;
end loop;
close cur_emp;
end;
f、使用for循环修改数据
declare
cursor cur_emp (mydeptno emp.deptno%type) is select * from emp
where deptno = mydeptno for update;
begin
for myrow in cur_emp(&mydeptno) loop
update emp set sal = myrow.sal +200
where current of cur_emp;
dbms_output.put_line(myrow.ename||' '||(myrow.sal+200));
end loop;
if sql%rowcount >0 then
dbms_output.put_line('修改成功');
end if;
end;
12、引用游标 ref 游标
引用游标的作用：不确定执行的sql语句的内容，针对动态执行sql语句的一种游标，可以多次绑定sql语句。
语法：a，b两部分必须声明在declare当中，作为参数传递到过程或者函数当中
a，声明游标类型 语法：type 游标名 is ref cursor；
b，声明游标类型变量，语法：变量名 游标名；
declare
type cur_emp is ref cursor;--声明引用游标
my_cur cur_emp;
begin
open my_cur for 'select * from emp where deptno =:mydeptno'
using &mydeptno;
for myrow in my_cur loop
dbms_output.put_line(myrow.ename||' '||myrow.sal);
end loop;
end;
13、存储过程
注意点：存储过程中参数有3种类型
a，in（输入类型）--需要用户传入参数，可省略不写
b，out（输出类型）--返回值，不能使用return返回
c，inout（输入输出类型）--包含输入输出功能
1、创建一个过程，输入一个部门编号，将部门名字返回
create or replace procedure pro_deptno
(mydeptno in dept.deptno%type,mydname out dept.dname%type)
is
begin
select dname into mydname from dept where deptno = mydeptno;
end;
调用：
declare
mydname dept.dname%type;
begin
pro_deptno(&deptno,mydname);
dbms_output.put_line(mydname);
end;
删除存储过程
drop procedure pro_deptno;
赋权
grant execute on 过程名 to 用户名（单个人）/public（所有人）
drop procedure pro_emp;
14、函数：必须有返回值，返回值不是参数形式，必须通过return关键字返回
a、输入一个工资范围，打印 在此工资范围的所有员工名字，如果打印出来了则打印输出成功，否则输出未查询到数据
create or replace function fun_emp
(
minsal emp.sal%type,
maxsal emp.sal%type
)
return varchar2
is
--声明部分
cursor cur_temp is select * from emp where sal > minsal and sal < maxsal;
flag boolean := false;--假设没有查询数据
begin
for myrow in cur_temp loop
flag := true;
dbms_output.put_line(myrow.ename||' '||myrow.sal);
end loop;
if flag then
return '输出成功';
else
return '查询失败';
end if;
end ;
调用：
select fun_emp(2000,3000) from dual;
15、程序包
1.包规范
主要用来声明这个包当中的对象，以及包中需要使用的变量
语法：create or replace package 包名
is
--对象的定义/变量的定义
end；
2.包主体
用来定义对象，做逻辑处理
语法：create or replace package body 包名(mypackage)
is
--变量声明部分
begin
--定义函数及过程等等数据库对象
end；
练习，创建包规范，要求包规范中必须包含两个对象
1.过程，输入用户编号，打印用户姓名，薪水
2.函数，输入一个部门编号，返回部门名称
--1.包规范
create or replace package mypackage
is
procedure pro_my(myempno emp.empno%type);
function fun_my(mydeptno emp.deptno%type)
return varchar2;
end ;
--2.包主体
create or replace package body mypackage
is
--定义过程
procedure pro_my(myempno emp.empno%type)
is
myrow emp%rowtype;
begin
select * into myrow from emp where empno = myempno;
dbms_output.put_line(myrow.ename);
end pro_my;
function fun_my(mydeptno emp.deptno%type)
return varchar2
is
mydname dept.dname%type;
begin
select dname into mydname from dept where empno = myempno;
retuen mydname;
end fun_my;
end mypackage;
16、触发器：当特定的事件发生时自动触发的一个存储过程。
优点：不需要显示的调用、可以记录日志，提供审计和日志功能、
提供数据库对象的安全性、可以解决逻辑比较复杂的业务。
触发器的组成：
触发条件、触发器的逻辑业务、激活触发器的条件
oracle中触发器大体分为三种：
1、DDL触发器（模式触发器）：修改表、新建表、删除表时触发的触发器。
2、数据库触发器：登录、退出、打开窗口、关闭窗口时触发的触发器。
3、DML触发器：数据库表或者视图对象的DML操作时触发的触发器。
a、行级触发器：每修改一行数据执行一次触发器【for each row】
b、语句级触发器：执行一条DML操作语句触发一次
c、inste of 视图触发器：主要针对视图建立的，视图触发器必定是一个行处理器
1、同一张表建立的触发器不能超过12个
2、触发器越多执行sql语句的性能越差
3、触发器的逻辑部分只能有DML操作语句，不能是DDL定义语句
4、触发器中不允许有事物提交或者回滚的语句
触发器的启动和禁止
alter trigger 触发器名 disable --禁止
alter trigger 触发器名 enable --启动
删除触发器
drop trigger 触发器名
触发器获取数据有两个对象
：new 指新插入/修改的数据对象，行对象。 如 :new.ename
：old 指修改前的数据对象。 如 ld.ename
a、练习，在emp表上建立触发器，一旦新增一条数据打印新增雇员名和薪资
create or replace trigger tri_emp
after
insert
on emp
for each row
begin
dbms_output.put_line(:new.ename||' '||:new.sal);
end;
调用：
select * from emp;
insert into emp(empno,sal) values(1113,5000);
b、练习，创建emplog表，在emp表上建立一个触发器，一旦删除一条记录，
触发器在删除的记录自动加入到emplog表中
--创建emplog表
create table emplog as select * from emp where 1=2;
SELECT * FROM EMPLOG;
--创建触发器
create or replace trigger tri_emplog
after
delete
on emp
for each row
begin
insert into emplog(empno,sal) values(:old.empno,:old.ename,:old.job,:old.sal);
end;
测试：
delete from emp where empno=1234;
删除
drop trigger tri_emplog;
c、练习，创建一个触发器，禁止休息时间修改emp表中雇员信息
create or replace trigger tri_time
before
update or insert or delete
on emp
begin
if to_char(sysdate,'day') in ('星期六','星期天') then
raise_application_error(-20001,'休息时间禁止修改数据');
end if;
end;
d、练习，创建一个触发器，不许降低员工的薪资
create or replace trigger tri_update
after
update
on emp
for each row
begin
if :new.sal <:old.sal then
raise_application_error(-20002,'不许降低员工的薪资');
end if;
end;
17、视图触发器：instead of
--创建视图
create or replace view v_emp
as select ename,dname from emp left join dept
on emp.deptno = dept.deptno;
select * from v_emp;
--练习，在v_emp视图上创建触发器，一旦修改视图上雇员薪资将雇员名称和薪资打印出来
create or replace trigger tri_vemp
instead of update
on v_emp
for each row
begin
dbms_output.put_line(:old.ename||:old.sal||'修改后：'||:new.ename||:new.sal);
end;
测试：
update v_emp set sal = 1000 where ename = 'SMITH';
十四、表空间：放的对象是表
语法：create tablespace 表空间名 datafile '文件路径1','文件路径2' size 50M (表空间大小);
注意：一个表空间可以对应一个或者多个文件
1、练习，创建一个名为mytest的表空间，表空间大小为10m
create tablespace mytest datafile 'e:/oracle上课数据表/表空间/mytest.dbf' size 10m;
2、练习，创建一个用户名叫jack，密码rose，将他使用的表空间默认设置为mytest
create user jack identified by rose default tablespace mytest;
--赋权给jack
grant all privileges to jack;
3、删除表空间
drop tablespace mytest including contents and datafiles;
4、删除jack用户
drop user jack cascade;
十五、数据库备份： 1、备份指定表空间 exp 用户名/密码@数据库名 tablespaces=表空间名 file=指定的文件路径 eg.C:\Users\asus>exp jack/rose@orcl tablespace=mytest file=e:/oracle上课数据表/表空间/mytest.dmp 2、备份单个用户下所有的数据库对象 exp 用户名/密码@数据库名 file=指定的文件 owner=用户 eg.C:\Users\asus>exp scott/tiger@orcl file=e:/oracle上课数据表/表空间/scott.dmp owner=scott 3、备份单个表数据 exp 用户名/密码@数据库名 tables=（表1，表2……） file=指定的文件路径 eg.C:\Users\asus>exp scott/tiger@orcl tables=(emp) file=e:/oracle上课数据表/表空间/emp.dmp 4、导入scott.dmp这个文件到jack/rose这个用户下面所在的表空间中去 导入语法： imp 用户名/密码@数据库 file=导入文件的地址 ignore=y full=y; eg.C:\Users\asus>imp jack/rose@orcl file=e:/oracle上课数据表/表空间/scott.dmp ignore=y full=y
