oracle终极总结

2017-01-06 10:08:06来源:作者:Sunflower-13人点击

学习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,sal,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,7566,7844,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,e.deptno, e.ename, e.sal, 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,e.deptno, e.ename, e.sal, 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' ,'j','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',4,'trr') from dual; --返回abct
select RPAD('abc',10,'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,e.ename,d.deptno,d.dname from emp e,dept d
where e.deptno = d.deptno;

2、要求查询每个雇员的姓名、工作、雇员的直接上级领导的姓名和雇员的部门名称
select e.ename,e.job,m.ename ,d.dname from emp e,emp m,dept d
where e.mgr = m.empno and d.deptno = e.deptno;

3、要求查询每个雇员的姓名、员工薪水等级、雇员的直接上级领导的姓名、领导的薪水等级和雇员的部门名称
select e.ename ,e.sal ,s.grade,m.ename,m.sal,ms.grade
from emp e,emp m,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.job,e.empno ,m.ename,m.empno
from emp e,emp m
where e.mgr = m.empno(+);

2、要求查询员工的部门编号、部门名称及姓名
--用右连接查询出没有员工的部门
select e.ename,d.deptno,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,d.deptno,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,7566,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,ename,job,mgr,hiredate,sal,comm,deptno)
values(1234,'sunflower','student',null,sysdate,0,0,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,
name varchar2(10),
)
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,empno,emane,job from emp where rownum <= 10) t where t.rownum>5;

2、查询emp表中后5条记录
select * from (select rownum,empno,emane,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,ename,job from emp where deptno =10;

2、创建复杂视图,数据来emp表和dept表
create or replace view v_emp as
select e.empno,e.ename,e.job,e.hiredate,e.deptno,d.dname
from emp e,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;
begin
loop
dbms_output.put_line(num1);
num1 := num1+1;
exit when num1>100;
end loop;
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,ename,job,sal) values(1113,'sunflower','MANAGER',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,ename,job,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,sal,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

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台