oracle终极总结ITeye - 娱乐之横扫全球

oracle终极总结ITeye

2019-01-10 15:35:28 | 作者: 鸿德 | 标签: 查询,回来,部分 | 浏览: 914

学习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(date2016-12-12,1) month from  dual;

b、查询日期2016-12-12削减一个月后的日期
select add_months(date2016-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,date2016-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(date2016-02-03,date2016-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(date2016-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(date2016-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(date2016-12-12,MM) from dual; --回来月份:12
select to_char(date2016-12-12,day) from dual; --回来英文星期几
select to_char(date2016-12-12,w) from dual; --回来一月中第几周
select to_char(date2016-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

2、查询emp表中前6—10条记载
select *  from (select rownum,empno,emane,job from emp where  rownum = 10) t where t.rownum

2、查询emp表中后5条记载
select *  from (select rownum,empno,emane,job from emp where  rownum = 15) t where t.rownum

九、调集操作
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
  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,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
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表娱乐之横扫全球立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1

    oracle终极总结ITeye

    查询,回来,部分
  • 2

    [MySQL]ITeye

    一个,或许,这个
  • 3

    hdfs常用命令ITeye

    文件,目录,途径
  • 4

    orace11gR2 启用日志归档ITeye

    备份,数据库,需求
  • 5

    Mysql高可用架构ITeye

    可用,架构,计划
  • 6

    hbase 全体介绍ITeye

    存储,经过,文章
  • 7

    db2move 指令无法导出表ITeye

    导出,数据,字符
  • 8
  • 9
  • 10