Oracle练习[尚学堂]

//超级管理员

sqlplus sys/cdxs2 as sysdba;

 

//更改账户

alter user scott account unlock;

 

conn scott/tiger;

 

clear scr;

 

//显示表结构

desc emp;

 

//只显示一行

select 2*3 from dual;

 

//显示多行

select 2*3 from emp;

 

//显示系统当前时间

select sysdate from dual;

 

//给字段名起个别名

select ename,sal*12 anuual_sal from emp;

 

//""能保持住字段名大小写不变

select ename,sal*12 "anuual sal" from emp;

 

任何含有空值null的表达式的结果都是空值null;

 

//字符串连接符 "||"

select ename || comm from emp;

 

select ename || 'liushaobo' from emp;

 

//如果字符串中有单引号，那么在单引号上再添加一个单引号，注意不是一个双引号

select ename || 'liu''shao''bo' from emp;

 

//查询中去掉重复的

select distinct deptno from emp;

 

//查询中去掉（字段组合）重复的

select distinct deptno,job from emp;

 

//注意：字符串用单引号括起来，不括的话，就会出错；

//数据库中的字符串数据是区分大小写的，查询时，一定要注意,但是字段名称不区分大小写

select * from emp where ename = 'JAMES';

 

select * from emp where sal between 800 and 1500;

相当于

select * from emp where sal >= 800 and sal <= 1500;

 

//处理空字符串（不要写成“……comm = null”哦）

select ename,sal,comm from emp where comm is null;

select ename,sal,comm from emp where comm is not null;

 

//in的使用

select ename,sal,comm from emp where sal in (800,1000,1500,2000);

select ename,sal,comm from emp where ename in ('JAMES','KING','CBA');

 

select ename,sal,hiredate from emp where hiredate > '20-2月 -81';

或

select ename,sal,hiredate from emp where hiredate > '20-2月 -1981';

但不能这样写：

select ename,sal,hiredate from emp where hiredate > '20-2 -81';

 

//and\or\not

select ename,sal from emp where deptno = 10 and sal > 1500;

select ename,sal from emp where deptno = 10 or sal > 1500;

select ename,sal from emp where sal not in (800,1500);

select sal from emp where sal not between 800 and 1500;

 

//like(%表示0个或n个字符，_表示一个字符)

select ename from emp where ename like '%ALL%';

select ename from emp where ename like '_A%';

select ename from emp where ename not like '_A%';

这样写是查不出想要的：

select ename from emp where ename like 'ALL';

 

//如果字符串中本身含有%的话(用默认转义字符：\)

select ename from emp where ename like '%\%%';

不能是这样写：

select ename from emp where ename like '%%%';

//如果不想用默认转义字符\，想用$作为转义字符呢?

select ename from emp where ename like '%$%%' escape '$';

 

//排序，默认是asc

select deptno,ename from emp order by deptno (asc);

select deptno,ename from emp order by deptno desc;

select deptno,ename from emp where deptno <> 10 order by deptno desc;

//下面是这样执行的：先按照deptno升序排序，deptno相同的，按照ename降序排序

select deptno,ename from emp order by deptno asc,ename desc;

 

//小写lower和大写upper

select lower(ename) name from emp;

//查询员工的名字第二字母是a或A

select ename from emp where lower(ename) like '_a%';

相当于

select ename from emp where ename like '_a%' or ename like '_A%';

 

//substr，以下指的是从ename中第一个字符开始，截取4个字符

select substr(ename,1,4) from emp;

 

//chr:把数字转换成ACSII码

select chr(65) from dual;

 

//ascii:把ACSII码转换成数字

select ascii('A') from dual;

 

//round：四舍五入

select round(23.562) from dual;  

相当于

select round(23.562,0) from dual;——>结果：24

select round(23.562,2) from dual;——>结果：23.56

select round(23.562,-1) from dual;    ——>结果：20

 

//to_char:转换成相应的格式

select to_char(sal,'$99,999.9999') from emp;

select to_char(sal,'L99,999.9999') from emp;  ￥……

select to_char(sal,'$00,000.0000') from emp;

select to_char(sal,'L00,000.0000') from emp;

 

//日期的转换

select to_char(hiredate,'yyyy-MM-dd HH:mm:ss') from emp;

select to_char(hiredate,'yyyy-MM-dd HH24:mm:ss') from emp;

 

//to_date:把字符串转换成日期

select ename,hiredate from emp where hiredate > to_date('1981-09-02 12:00:12','yyyy-MM-dd HH:mi:ss');

 

//to_number:把字符串转换成数字

select ename,sal from emp where sal > to_number('$1,250.00' , '$9,999.99');

这样也行

select ename,sal from emp where sal > to_number('$1,250.00' , '$0,000.00');

 

//函数nvl(处理空值null)：当comm为null时，当成0来计算

select ename,sal*12+nvl(comm,0) from emp;

 

//组函数(五个多行函数)

select max(sal),min(sal),avg(sal) from emp;

 

//截取到小数点后两位

select to_char(avg(sal),'99999999.99') from emp;

 

//四舍五入到小数点后两位

select round(avg(sal),2) from emp;

 

//总和

select sum(sal) from emp;

 

//总的记录

select count(*) from emp where deptno = 10;

 

//计算comm字段不是空值的个数

select count(comm) from emp;

 

//计算有几个部门

select count(distinct deptno) from emp;

 

//分组函数,计算各部门的平均薪水

select deptno,avg(sal) from emp group by deptno;

 

//按照多个字段组合进行分组

select deptno,job,avg(sal) from emp group by deptno,job;

 

//找出薪水最高的那个人的名字

select ename from emp where sal = (select max(sal) from emp);

 

//找出各部门中薪水最高的

select deptno,max(sal) from emp group by deptno;

注意：在使用group by的时候，有一个规则：出现在select列表中的字段，如果没有出现在组函数里头，必须出现在group by子句里头。

 

//找出各部门的平均薪水大于2000的

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

注意：where是对单条记录进行过滤的，having是对分组进行过滤的，所以这里只能使用having

 

总结：select……from……where……group by……having……order by……

例如：

select deptno,avg(sal) from emp where sal > 1200 group by deptno having avg(sal) > 2000 order by avg(sal) desc;

 

select ename,sal from emp where sal > (select avg(sal) from emp);

 

//查找按照部门进行分组之后，每个部门的薪水最高的那个人

select t.deptno,ename,sal from emp join (select deptno,max(sal) max_sal from emp group by deptno) t on (emp.sal = t. max_sal and emp.deptno = t.deptno);

 

//查询每个部门的平均薪水的薪水等级

select t.deptno, s.grade from salgrade s join (select deptno, avg(sal) avg_sal from emp group by deptno) t on t. avg_sal between s.losal and s.hisal;

 

//自连接（找出每个员工对应的经纪人）

select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;

 

//交叉连接

select ename, dname from emp cross join dept;

交叉连接会产生一个笛卡尔乘积

 

//1999年新规定的等值连接(也成内连接,inner可有可无)

select ename, dname from emp inner join dept on (emp.deptno = dept.deptno);

等值连接的简单用法（不推荐用）

select ename, dname from emp join dept using(deptno);

 

//这样写就可以把表连接和过滤条件区分开

select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);

//三张表连接

select ename, dname, grade from emp e join dept d on (e.deptno = d.deptno) join salgrade s on (e.sal between s.losal and s.hisal) where ename not like '_A%';

 

//1999年新规定的自连接

select e1.ename, e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno);

 

//左(外)连接+自连接

select e1.ename, e2.ename from emp e1 left outer join emp e2 on (e1.mgr = e2.empno);

注：left和join之间的out可有可无

 

//右(外)连接

select e.ename, d.dname from emp e right join dept d on (e.deptno = d.deptno);

 

//全(外)连接(1992年规定的不支持)

select e.ename, d.dname from emp e full join dept d on (e.deptno = d.deptno);

 

//求部门平均的薪水等级

select t.deptno, avg(t.grade) from (select e.deptno, s.grade from emp e join salgrade s on (e.sal between s.losal and s.hisal)) t group by t.deptno;

 

//哪些人是经理人

select ename from emp where empno in (select distinct mgr from emp);

 

//求薪水的最高值(不准用组函数)

select distinct e.sal from emp e where e.sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));

错误的求法：

select distinct e.sal from emp e, (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal)) t where e.sal not in t.sal;

 

//求平均薪水最高的部门的编号、名称和平均薪水。

select d.deptno, d.dname, s.avg_sal from dept d join (

select deptno, avg_sal from

(select deptno, avg(sal) avg_sal from emp group by deptno)

where avg_sal = (

     select max(avg_sal) from

     (select deptno, avg(sal) avg_sal from emp group by deptno)

)

) s on (d.deptno = s.deptno);

以上的红色部分可以简写成：

 (select max(avg(sal)) from emp group by deptno)

注：组函数可以嵌套，但最多只能嵌套两层,并且嵌套分组函数一定要有group by

 

//求平均薪水的等级最低的部门的部门名称

第一种：

select d.deptno, d.dname, a.avg_sal, a.grade

from dept d join

(

select t.deptno, t.avg_sal, s.grade from salgrade s join (select deptno, avg(sal) avg_sal from emp group by deptno) t on (t.avg_sal between s.losal and a.hisal)

) a on (d.deptno = a.deptno)

where a. grade =

(

select min(s.grade) min_grade from salgrade s join (select deptno, avg(sal) avg_sal from emp group by deptno) t on (t.avg_sal between s.losal and a.hisal)

);

第二种：

select d.deptno, d.dname, a.avg_sal, a.grade

from dept d join

(

select deptno, avg_sal, grade from

(

select t.deptno, t.avg_sal, s.grade from salgrade s join (select deptno, avg(sal) avg_sal from emp group by deptno) t on (t.avg_sal between s.losal and a.hisal)

)

) a on (d.deptno = a.deptno)

where a. grade =

(

select min(grade) min_grade from

(

select t.deptno, t.avg_sal, s.grade from salgrade s join (select deptno, avg(sal) avg_sal from emp group by deptno) t on (t.avg_sal between s.losal and a.hisal)

)

);

//创建视图来简化上面的sql

create view v$_dept_avg_sal_info as (select t.deptno, t.avg_sal, s.grade from salgrade s join (select deptno, avg(sal) avg_sal from emp group by deptno) t on (t.avg_sal between s.losal and a.hisal));

简化为：

select d.deptno, d.dname, a.avg_sal, a.grade

from dept d join

(

select deptno, avg_sal, grade from v$_dept_avg_sal_info

) a on (d.deptno = a.deptno)

where a. grade =

(

select min(grade) min_grade from v$_dept_avg_sal_info

);

 

//比普通员工的最高薪水还要高的经理人名称

select ename from emp where empno in (select distinct mgr from emp where mgr is not null) and sal > (

select max(sal) from emp where empno not in (select distinct mgr

from emp where mgr is not null)

)

 

//求部门经理人中平均薪水最低的部门名称（思考题）

select d.deptno, d.dname, t. avg_sal from dept d join

(

select deptno, avg(sal) avg_sal from emp where empno in (select

mgr from emp where mgr is not null) group by deptno

) t

on (d.deptno = t. deptno)

where avg_sal =

(

   select min(avg(sal)) min_avg_sal from emp where empno in (select mgr from emp where mgr is not null) group by deptno

);

 

//面试题：比较效率

select * from emp where deptno = 10 and ename like ‘%A%’;

select * from emp where ename like ‘%A%’ and deptno = 10;

理论上第一种效率比较高，但是在数据库中，会对sql进行优化，很有可能优化成第二种sql

 

//把一个用户所拥有的数据导入到另一个新的用户中（DBA知识）

1.   backup

exp（cmd     cd\     cd temp        del *.*(删除目录temp下的所有文件)          exp）

2．创建用户并分配表空间（只有数据库管理员才有创建用户的权限）

create user liushaobo identified by liushaobo default tablespace daxue quota 20M on daxue;

3．给用户授予相关权限

grant connect, resource to liushaobo;

4．imp

注：在连接新建的用户时，要加上数据库名，例如：

liushaobo/liushaobo@shaobo 结尾不能带分号

 

DML语句

1>insert into dept values(50, ‘game’, ‘bj’);

rollback(回滚)

备份一张表

create table dept2 as select * from dept;

1>insert into dept2 values(50, ‘game’, ‘bj’);

2>insert into dept2 (deptno, dname) values (60, ‘game2’);

3>insert into dept2 select * from dept;

 

//求工资的前五名的员工信息（按工资从高到底）rownum

select empno, ename,sal from (select empno, ename, sal from emp order by sal desc) where rownum <= 5;

注：rownum只能用 <或者<=，不能用 >、=或者>=

或者

select empno, ename, sal from

(select empno, ename, sal, rownum r from

(select empno, ename, sal from emp order by sal desc)

)

where r <= 5;

//求工资的第六名到第十名的员工信息（按工资从高到底）rownum

select empno, ename, sal from

(select empno, ename, sal, rownum r from

(select empno, ename, sal from emp order by sal desc)

)

where r >= 6 and r<= 10;

//求工资的后五名的员工信息（按工资从高到底）rownum

select empno, ename, sal from

(select empno, ename, sal, rownum r from

(select empno, ename, sal from emp order by sal desc)

)

where r > (select (count(1) - 5) from emp);

注：以上在分页查询的时候经常遇到，所以很重要

 

S (sno, sname) (学号，姓名)

C (cno, cname, cteacher) (课号，课名，教师)

SC (sno, cno, scgrade) (学号，课号，成绩)

//找出没有选过“郭仁”老师的所有学生姓名

Select sname from s join

(select sno from sc join c on (sc.cno = c.cno) where cteacher <> ’郭仁’) t on (s.sno = t.sno);

//列出两门以上（包括两门）不及格学生姓名及平均成绩

select sname, avg_scgrade

  from s

  join (select sno, avg(scgrade) avg_scgrade

          from sc

         where scgrade < 60

         groupby sno

        havingcount(*) >= 2) t

 on (s.sno = t.sno);

//列出既学过课程2000又学过课程2004的所有学生姓名

select sname

  from s

  join (select sno

          from sc

         where cno = 2000

           and sno in (select sno from sc where cno = 2004)) t

 on (s.sno =t.sno);

 

update emp1 set sal = sal*2, ename = ename || '——' where deptno = 10;

 

transaction起始于第一条DML语句,结束于:

1.用户显示地执行commit语句(提交操作)或rollback操作(回退操作)；

2.执行DDL或DCL语句(事务控制语句)的时候，transaction自动commit；

3.当用户正常断开连接的时候，transaction自动commit，例：exit命令;

4.当用户非正常断开连接的时候，transaction自动rollback，例：强制关闭sqlplus窗口、强制关机、电脑断电等等;

 

1. SQL语句分类（五类）：

DQL: select

DML：insert \ update \ delete \ merge (Oracle 独有，用于数据库同步)

DDL：create \ drop \ alter \ truncate \ rename \ comment

DCL：grant \ revoke

Transaction：commit \ rollback \ savepoint(保存点，和rollback一起使用)

 

定长字符串（char）和变长字符串（varchar2）说明

例如：char(5)和varchar2(5),他们各自在数据库中最多只能占5个字节的空间，如果存 abc到char(5)，那么 abc 还是占5个字节的空间，如果存 abc到varchar2(5)，那么 acb 占3个字节的空间。为什么有了变长字符串还要定长字符串呢？是效率问题，比如：你想要这个字段的数据存取效率比较高，用定长字符串比较合适，因为定长字符串的长度是固定的，比如你想要找第3条数据，3*5就可以直接定位，所以定长字符串比变长字符串的的效率要高，但是他也带来不好的地方：浪费空间；这就是拿空间换时间来换取效率，hash表也是。

 

long是变长字符串，最大字节数达到2G，适用于存储一篇文章，而varchar2最大字节数达到4K

 

oracle五个约束条件：

1．        非空（not null）

2．        唯一（unique）

3．        主键（primary key）

4．        外键（foreign key）

5．        check

约束条件本身在数据库中是一个对象，可以为约束条件起一个新名字，不起的话，数据库会自动给起个名字。

 

create table stu

(

     id number(6),

name varchar2(20) constraint stu_name_nn not null,

sex number(1),

age number(3),

adate date,

grade number(2) default 1,

class number(4),

email varchar2(50) constraint stu_email_uni unique

);

这种写法叫做字段级约束,它有些不能完成的任务，比如：要求name和email的组合不能重复，这种写法就没办法写了。注意：当一个字段的取值是唯一的时候，可以给它往里头随便插空值，两个null值之间不认为是重复的。可以插入多个null，这是null值比较特殊的地方，那么，要求name和email的组合不能重复，则使用表级约束，例如：

create table stu

(

     id number(6),

name varchar2(20) constraint stu_name_nn not null,

sex number(1),

age number(3),

adate date,

grade number(2) default 1,

class number(4),

email varchar2(50),

constraint stu_name_email_uni unique(name, email)

);

 

主键约束：

主键（primary key）:可以唯一标示整条记录。例如：表stu中id可以标示这张表中一整条记录，但是email不行，因为email可以为null值，而id不可以为null值，另外，email为varchar2型，而id为number型，当建立一个主键的时候，会随着这个主键建立一个索引，查询的速度更快，而number型比varchar2型建立一个索引查询的速度要快，所以id比email更适合做主键，

create table stu

(

     id number(6) constraint stu_id_pk primary key,

name varchar2(20) constraint stu_name_nn not null,

sex number(1),

age number(3),

adate date,

grade number(2) default 1,

class number(4),

email varchar2(50),

constraint stu_name_email_uni unique(name, email)

);

从语法上来说，主键相当于这个字段不能为空（not null），并且必须是唯一的（unique），但从逻辑意义上来说通常代表着单独不同的记录，主键约束也可以加在表级上，例：

create table stu

(

     id number(6),

name varchar2(20) constraint stu_name_nn not null,

sex number(1),

age number(3),

adate date,

grade number(2) default 1,

class number(4),

email varchar2(50),

constraint stu_id_pk primary key(id),

constraint stu_name_email_uni unique(name, email)

);

也可以用两个字段的组合作主键，这叫做复合主键，例：修改constraint stu_id_pk primary key(id),为constraint stu_id_pk primary key(id,class),

 

外键约束：

外键约束是加在一张表的两个字段上或两张表的两个字段上，例：

create table classes

(

     id number(4) primary key,

     classname varchar2(20) not null

)

 

create table stu

(

id number(6),

name varchar2(20) constraint stu_name not null,

sex number(1),

age number(3),

adate date,

grade number(2) default 1,

class number(4) references classes(id),

email varchar2(50),

constraint stu_id_pk primary key(id),

constraint stu_name_email_uni unique(name, email)

)

或者

create table stu

(

id number(6),

name varchar2(20) constraint stu_name not null,

sex number(1),

age number(3),

adate date,

grade number(2) default 1,

class number(4),

email varchar2(50),

constraint stu_id_pk primary key(id),

constraint stu_name_email_uni unique(name, email),

constraint stu_class_fk foreign key(class) references classes(id)

)

注：外简约束被参考的字段必须是主键

 

check约束条件不常用，一般在编程中就对数据进行check

 

修改表名：

alter table 旧表名 rename to新表名;

修改字段名：

alter table 表名 rename column 旧字段 to 新字段;

注：9.0.1.1.1的oracle版本不能用，9.2.0.1.0的oracle版本可以用

修改字段的数据类型：

alter table表名modify (字段名数据类型);

如果同时需要修改列名和数据类型，则可以先修改数据类型再来修改列名，即执行上述两条sql语句。

增加字段：

alter table 表名add (字段名数据类型);

删除字段：

alter table 表名drop column 字段名;

添加约束条件（主键）

alter table 表名 add constraint 约束名 primary key(字段名);

删除约束条件

alter table 表名 drop constraint 约束名;

注：约束条件一般不直接修改，而是先删除原先的，再添加新的约束条件

修改一个字段的缺省值

alter table 表名 modify 字段名 default 默认值;

添加一个字段的缺省值和修改一个字段的缺省值sql一样

删除一个字段的缺省值

alter table 表名 modify 字段名 default null;

添加一个表的注解

comment on table 表名 is ‘’;

添加一个表字段名的注解

comment on column 表名.字段名 is ‘’;

 

当前用户下有哪些表，有哪些约束，有哪些视图等等这样一些信息，被oracle装在了一张单独的表里面，这样的表被称作“数据字典表”，例：

1、当前用户下有哪些表

select table_name from user_tables;

user_talbles里面存的是当前用户所拥有表的信息

2、当前用户下有哪些视图

select view_name from user_views;

3、当前用户下有哪些约束

select constraint_name, table_name from user_constraints;

4、oracle数据库中一共有多少张数据字典表

select table_name,comments from dictionary;

dictionary表中存的是所有的数据字典表信息

 

索引（index）

假如，在stu这张表里面，将来会经常访问每个学生的email地址，那怎么才能更快地访问每个学生的email地址呢? 那就是为这个email字段建立一个索引，例：

create index idx_stu_email on stu(email);

注：索引也可以建立在两个字段上，例如：建立在email和class字段上面，create index idx_stu_email_class on stu(email, class);意思是为这两个字段的组合建立一个索引，也就是说，将来查询email和class字段组合的时候，这时候它的效率会更高。

删除索引：

drop index idx_stu_email;

当前用户下有哪些索引

select index_name from user_indexes;

注：当为一张表中的某个字段加了“主键”约束或“唯一”约束的时候，oracle会为这个字段自动建立一个索引。

当为某个字段建立索引之后，在读取这个字段里面的数据的时候效率会更高，但是为这个字段插入数据的时候效率会大大降低，因为插入数据的时候，不光是把数据插入这个字段中，同时还要把这个字段对应的索引插入到索引表中，所以为某个字段建立索引之后，读取的效率提高了，但更新的效率会降低。那什么时候建立索引比较合适呢？一般情况下，某个字段访问量比较大、感觉效率比较低的时候，可以考虑建立索引。

     不要轻易建立索引

 

视图（view）

create view v$_……

视图是一张虚表，严格意义上是一个子查询。视图能简化查询，视图也有不好的地方，比如，在一张表上建立了很多视图，某一天这个表结构突然修改了，那么视图也得跟着修改，所以视图建的多了，会增加维护的负担。视图也有其他的好处，比如可以隐藏一张表中不想让别人看到的数据。

更新视图实际上是更新基于它们的表，视图是完全可以更新的，但不常用。

 

序列（sequence）

这是oracle中所特有的东西，序列是用来产生唯一的、不间断的这样一个数字序列，一般是用在主键上。

建立序列：create sequence sequence_article_id;

create table article

(

     id number constraint article_id_pk primary key,

     title varchar2(1024),

     content long

)

insert into article values(sequence_article_id.nextval, ‘ssss’, ‘dasd’);

序列内部本身已经做好了线程的同步，不管有多少个人同时发表帖子，每个帖子通过序列自动生成的id一定是唯一的，从而保证主键的唯一性。

删除序列：drop sequence sequence_article_id;

创建序列的语法如下：

create sequence 序列名 [

     [increment by 1 ]

     [start with 1 ]

     [maxvalue 999999999999 | nomaxvalue]

     [minvalue 1 | nominvalue]

     [nocycle | cycle]

     [cache 20 | nocache]

     [order | noorder]

]

说明：

increment by 增量

start with 起始数字

maxvalue 最大值

minvalue 最小值

cycle 循环使用

cache 缓存

order 是否按顺序

 

三范式

三范式所追求的目标往往是这一种原则：不存在冗余数据。就是说同样的数据不存第二遍。

第一范式的要求：设计表要有主键，列不可分。（实际问题实际分析，很有可能打破这个设计要求）

第二范式的要求：不能存在部分依赖，就是说不是主键的字段不能部分依赖主键（在设计多对多表之间关系的时候，往往有两个字段作为复合主键）

第三范式的要求：不能存在传递依赖。（如：学号（主键）<-班级编号<-班级信息）

 

PL/SQL(procedure language/struture query language)

过程语言/结构化查询语言

 

一个简单的程序：(在命令窗口中执行，最后添加：/)

set serveroutput on;

begin

dbms_output.put_line('hello world');

end;

 

一个简单的程序块：

declare

v_name varchar2(20);

begin

v_name := 'liushaobo';

dbms_output.put_line(v_name);

end;

 

一个完整的简单程序

declare

v_num number :=0;

begin

v_num := 2/v_num;

dbms_output.put_line(v_num);

exception

    whenothersthen

    dbms_output.put_line('error!');

end;

 

变量声明的规则

1.变量名不能够使用保留字,如from、select等

2.第一个字符必须是字母, 尽量以v_ 开头

3.变量名最多包含30个字符

4.不要与数据库的表或者列同名

5.每一行只能声明一个变量

 

常用变量类型

binary_integer ：整数，主要用来计数而不是用来表示字段类型

number :数字类型

char ：定长字符串类型

varchar2 ：变长字符串类型最大4K

date： 日期

long：长字符串，最长2G

boolean：布尔类型，可以取值为 false，true，null。null参与运算值不确定，所以最好声明变量时就给它赋值。

 

declare

    v_temp number;

    v_count binary_integer :=0;

    v_sal number(7,2) :=8000.00;

    v_date date := sysdate;

    v_pi constantnumber(3,2) := 3.14;

    v_valid boolean := false;  --不能打印出布尔值

    v_num varchar2(20) notnull := 'hello';

begin

    dbms_output.put_line('v_temp is' || v_temp); --||为字符串连接符

end;

 

变量声明，使用%type属性

声明变量的数据对应表的某个字段的数据，当表结构改变时（如把number(7,2)，改为number(8,3)时，必须修改相应程序），用%type属性可以解决该问题。

例：

declare

--empno2数据类型和emp表中的empno字段相同

v_empno2 emp.empno%type;

--v_empno3数据类型和v_empno2 数据类型相同

v_empno3 v_empno2%type;

begin

    dbms_output.put_line('test');

end;

 

自定义变量

1、table变量类型(相当于java中的数组)

declare

    type type_table_emp_empno istableof emp.empno%typeindexbybinary_integer;

    v_empnos type_table_emp_empno;

begin

    v_empnos(0) := 422;

    v_empnos(-1) := 24;

    v_empnos(2) := 111;

    dbms_output.put_line(v_empnos(-1));

end;

说明：table变量类型命名规则：type（自定义变量）-table（table类型变量）-emp（表emp）-empno（empno字段）；

注：下标值可以为负值；

 

2、record 变量类型（类似于java中的类）

declare

    type type_record_dept isrecord

    (

         deptno dept.deptno%type,

         dname dept.dname%type,

         loc dept.loc%type

    );

    v_temp type_record_dept;

begin

    v_temp.deptno := 50;

    v_temp.dname := 'liushaobo';

    v_temp.loc := 'dad2qa';

    dbms_output.put_line(v_temp.deptno || '   ' || v_temp.dname);

end;

但上述dept表变动时，此程序也得手动做相应改动，可以用下述方法自动改动：

 

使用%rowtype声明record变量

declare

    v_temp dept%rowtype;

begin

    v_temp.deptno := 50;

    v_temp.dname := 'liushaobo';

    v_temp.loc := 'dad2qa';

    dbms_output.put_line(v_temp.deptno || '   ' || v_temp.dname);

end;

 

1.1、select语句(1)

有且只有一条返回值，且必须加into

declare

    v_ename emp.ename%type;

    v_sal emp.sal%type;

begin

    select ename, sal into v_ename, v_sal from emp where empno = 7369;

    dbms_output.put_line(v_ename || '   ' || v_sal);

end;

 

1.2、select语句(2)

有且只有一条返回值，且必须加into

declare

    v_emp emp%rowtype;

begin

    select * into v_emp from emp where empno = 7369;

    dbms_output.put_line(v_emp.sal);

end;

 

2、update 、delete、insert语句和sql中相同，可能和变量混用；

declare

    v_deptno emp2.deptno%type := 10;

    v_count number;

    v_emp2 emp2%rowtype;

begin

    update emp2 set sal = sal/2where deptno = v_deptno;

    select * into v_emp2 from emp2 where deptno = v_deptno and empno = 7839;

dbms_output.put_line(v_emp2.sal);

commit;

end;

 

declare

v_deptno emp2.deptno%type:=10;

v_count number;

begin

update emp2 set sal =sal/2where deptno =v_deptno;

--select deptno into v_deptno from emp2 where empno = 7369;

--select count(*) into v_count from emp2;

   dbms_output.put_line(sql%rowcount||'条记录被修改'); --sql%rowcount 返回记录数

    --sql表示刚刚被执行的sql语句

commit;

end;

 

3、执行DDL或DCL语句--（记住有execute immediate）

begin

  execute immediate 'create table t(nn varchar2(3) default ''abc'')';

end;

注：abc两边的各是两个单引号，不是一个双引号

 

4、if语句

declare

    v_sal emp.sal%type;

begin

    select sal into v_sal from emp where empno = 7839;

    if v_sal <= 800then

       dbms_output.put_line('low');

    elsif v_sal >=2800then   --不是elseif

       dbms_output.put_line('high');

    else

       dbms_output.put_line('middle');

    endif;   --注意有这条语句

end;

 

5、while语句

declare

    i binary_integer := 1;

begin

    while i<11loop

          dbms_output.put_line(i);

          i := i+1;

    endloop;

end;

 

6、do…while语句

declare

    i binary_integer := 1;

begin

    loop

          dbms_output.put_line(i);

          i := i+1;

          exitwhen(i > 10);

    endloop;

end;

 

7、for语句

declare

    i binary_integer := 1;

begin

    for i in1..10 loop   --中间是两点

        dbms_output.put_line(i);

endloop;

for i inreverse1..10 loop   --加上reverse为逆序打印

        dbms_output.put_line(i);

endloop;

end;

 

8、异常处理

declare

    v_empno emp.empno%type;

begin

    select empno into v_empno from emp where deptno = 10;

    exception

    when too_many_rows then

         dbms_output.put_line('太多记录！');

    whenothersthen

         dbms_output.put_line('error!');

end;

 

declare

    v_empno emp.empno%type;

begin

    select empno into v_empno from emp where empno = 1011;

    exception

    when no_data_found then

         dbms_output.put_line('没有数据！');

end;

 

9、错误信息处理

①创建存储错误信息的表

createtable error_log

(

       id number primary key,

       errorcode number,

       errormsg varchar(1024),

       errordate date

)

②创建序列

create sequence seq_error_log_id startwith1 increment by1;

③创建处理错误的pl

declare

    v_deptno dept.deptno%type := 10;

    v_errorcode number;

    v_errormsg varchar2(1024);

begin

    deletefrom dept where deptno = v_deptno;

    commit;

    exception

    whenothersthen

         rollback;

         v_errorcode := SQLCODE;

         v_errormsg := SQLERRM;

         insertinto error_log values(seq_error_log_id.nextval, v_errorcode, v_errormsg, sysdate);

         commit;

end;

 

游标

1、plsql中select语句只能返回一条语句，要想返回多条语句，用游标。

declare

    cursor c isselect * from emp;   --声明游标

    v_emp c%rowtype;

begin

    open c;   --打开游标

         fetch c into v_emp;   --fetch完后自动定位到下一条记录

         dbms_output.put_line(v_emp.ename);

    close c;

end;

注：声明游标，plsql并不会真正从数据库中取数据，只有当打开游标的时候，plsql才会执行select语句，然后把结果集放到内存里面

 

2．简单循环

(1)do…while语句

declare

    cursor c isselect * from emp;

    v_emp c%rowtype;

begin

    open c;

         loop

           fetch c into v_emp;

           exitwhen(c%notfound);

           dbms_output.put_line(v_emp.ename);   --不能放在exit前，否则最后一条记录会打印两次，但不会出错。

         endloop;

    close c;

end;

 

(2)while语句

declare

    cursor c isselect * from emp;

    v_emp c%rowtype;

begin

    open c;

          fetch c into v_emp;

          while (c%found) loop

                dbms_output.put_line(v_emp.ename);

                fetch c into v_emp;

         endloop;

    close c;

end;

 

(3)for语句

declare

    cursor c isselect * from emp;

    --v_emp c%rowtype;

begin

    for v_emp in c loop

       dbms_output.put_line(v_emp.ename);

    endloop;

end;

注：不用定义v_emp，不用打开关闭游标，for开始时会自动打开游标，for结束时会自动关闭游标。

 

3．带参数的游标

declare

    cursor c (v_deptno emp.deptno%type, v_job emp.job%type)

    isselect ename, sal from emp where deptno = v_deptno and job = v_job;

    --v_emp c%rowtype;

begin

    for v_emp in c (30, 'CLERK') loop

       dbms_output.put_line(v_emp.ename || '  ' || v_emp.sal);

    endloop;

end;

 

4. 可更新的游标

游标一般是作为记录集读取数据用的，但有时候用游标修改记录，这就是可更新游标；

declare

    cursor c isselect * from emp1 forupdate;

begin

    for v_emp in c loop

        if (v_emp.sal < 2000) then

           --修改定位到的当前记录，注意形式

           update emp1 set sal = sal + 20wherecurrentof c;

        elsif(v_emp.sal >= 2000) then

           deletefrom emp1 wherecurrentof c;

        endif;

    endloop;

    commit;   --提交

end;

 

存储过程(把过程的declare变成 create or replace produce p is 就行)

createorreplaceprocedure p

is

    cursor c isselect * from emp1 forupdate;  

begin

    for v_emp in c  loop

        if (v_emp.sal < 2000) then

           --修改定位到的当前记录，注意形式

           update emp1 set sal = sal + 20wherecurrentof c;

        elsif(v_emp.sal >= 2000) then

           deletefrom emp1 wherecurrentof c;

        endif;

    endloop;

    commit;   --提交

end;

注：存储过程创建成功，并不代表存储过程执行了

 

执行存储过程

方式一 exec p;

方式二

begin

p;

end;

 

带参数的存储过程

in 相当于程序里的参数，供传入用，在存储过程不能改变其值；

out 相当于程序里的返回值，在存储过程中可以为其赋值传出；

in out 既可以当参数又可以当返回值用；

不带上述说明符默认为in类型；

 

下例中v_a v_b 为in类型

v_c 为out类型

v_d 为in out 类型

createorreplaceprocedure p

       (v_a innumber, v_b number, v_ret outnumber, v_temp inoutnumber)

is

begin

       if (v_a > v_b) then

          v_ret := v_a;

       else

          v_ret := v_b;

       endif;

       v_temp := v_temp +1;

end;

 

执行调用存储过程

declare

    v_a number := 3;

    v_b number := 4;

    v_ret number;

    v_temp number := 5;

begin

    p(v_a, v_b, v_ret, v_temp);

    dbms_output.put_line(v_ret);

    dbms_output.put_line(v_temp);

end;

注：如果存储过程有语法错误，存储过程依然会被创建，会出现警告信息，不会显示错在什么地方，要查出错在什么地方，用show errors 命令可以显示出错在什么地方。

 

函数

①创建函数

createorreplacefunction sal_tax(v_sal number) returnnumber

is

begin

       if(v_sal < 2000) then

                return0.10;

       elsif(v_sal < 2750) then

                return0.15;

       else

                return0.20;

       endif;

end;

②调用函数

例如：

select lower(ename), sal_tax(sal) from emp

 

触发器

(1)

①创建一张用于存储用户操作emp1历史记录的表

createtable emp1_log

(

       uname varchar2(20),

       action varchar2(20),

       atime date

)

②创建触发器

createorreplacetrigger trig afterinsertorupdateordeleteon emp1 foreachrow

begin

       if inserting then

             insertinto emp1_log values(USER, 'insert', sysdate);

       elsif updating then

             insertinto emp1_log values(USER, 'update', sysdate);

       elsif deleting then

             insertinto emp1_log values(USER, 'delete', sysdate);

       endif;

end;

③测试触发器

deletefrom emp1 where deptno = 30;

select * from emp1_log;

 

(2)

createorreplacetrigger trig afterupdateon dept1 foreachrow

begin

       update emp1 set deptno = :NEW.deptno where deptno = :OLD.deptno;

end;

测试

update dept1 set deptno = 50where deptno = 10

 

通过存储过程展示树形数据

①创建表

createtable article

(

       id number primary key,

       contect varchar(200),

       pid number,

       isleaf number(1),

       alevel number(3)

)

②插入数据

insertinto article values(1,'蚂蚁大战大象',0,0,0);

insertinto article values(2,'大象被打趴下',1,0,1);

insertinto article values(3,'蚂蚁也不好过',2,1,2);

insertinto article values(4,'瞎说',2,0,2);

insertinto article values(5,'没有瞎说',4,1,3);

insertinto article values(6,'怎么可能',1,0,1);

insertinto article values(7,'怎么没有可能',6,1,2);

insertinto article values(8,'可能性很大的',6,1,2);

insertinto article values(9,'大象进医院了',2,0,2);

insertinto article values(10,'蚂蚁是护士',9,1,3);

④创建存储过程

createorreplaceprocedure p (v_pid article.pid%type, v_level binary_integer)

is

       cursor c isselect * from article where pid = v_pid;

       v_preStr varchar2(200) := '';

begin

       for i in0..v_level loop

           v_preStr := v_preStr || '*****';

       endloop;

      

       for v_article in c loop

           dbms_output.put_line(v_preStr || v_article.contect);

           if(v_article.isleaf = 0) then

                p(v_article.id, v_level+1);   --使用递归

           endif;

       endloop;

end;

⑤测试

exec p(0,0);

⑥结果

*****蚂蚁大战大象

**********大象被打趴下

***************蚂蚁也不好过

***************瞎说

********************没有瞎说

***************大象进医院了

********************蚂蚁是护士

**********怎么可能

***************怎么没有可能

***************可能性很大的