oracle终极总结
发布时间:2020-12-12 15:29:36 所属栏目:百科 来源:网络整理
导读:学习oracle数据库的总结 大多时候我都是在偷懒的,学过的知识也不会去一一总结,所有导致需要用的时候就只剩一点印象。对于写学习oracle的这份总结,也是拖了好久,现在才终于马马虎虎的总结出来,写这份总结一来是觉得自己记性不好,肯定容易忘记,方便回顾
学习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:Usersasus>exp jack/rose@orcl tablespace=mytest file=e:/oracle上课数据表/表空间/mytest.dmp 2、备份单个用户下所有的数据库对象 exp 用户名/密码@数据库名 file=指定的文件 owner=用户 eg.C:Usersasus>exp scott/tiger@orcl file=e:/oracle上课数据表/表空间/scott.dmp owner=scott 3、备份单个表数据 exp 用户名/密码@数据库名 tables=(表1,表2……) file=指定的文件路径 eg.C:Usersasus>exp scott/tiger@orcl tables=(emp) file=e:/oracle上课数据表/表空间/emp.dmp 4、导入scott.dmp这个文件到jack/rose这个用户下面所在的表空间中去 导入语法: imp 用户名/密码@数据库 file=导入文件的地址 ignore=y full=y; eg.C:Usersasus>imp jack/rose@orcl file=e:/oracle上课数据表/表空间/scott.dmp ignore=y full=y (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |