//超级管理员
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
约束条件本身在数据库中是一个对象，可以为约束条件起一个新名字，不起的话，数据库会自动给起个名字。
(
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的组合不能重复，则使用表级约束，例如：
(
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），但从逻辑意义上来说通常代表着单独不同的记录，主键约束也可以加在表级上，例：
(
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);
⑥结果
*****蚂蚁大战大象
**********大象被打趴下
***************蚂蚁也不好过
***************瞎说
********************没有瞎说
***************大象进医院了
********************蚂蚁是护士
**********怎么可能
***************怎么没有可能
***************可能性很大的
相关推荐
文档部分内容如下, 全部是尚学堂课堂原笔记.. 第一课：客户端 1. Sql Plus(客户端），命令行直接输入：sqlplus，然后按提示输入用户名，密码。 2. 从开始程序运行:sqlplus，是图形版的sqlplus. 3. ...
这个是尚学堂马士兵老师讲解Oracle时用到的一些，希望对大家有帮助...
NULL 博文链接：https://firefly1022.iteye.com/blog/724912
本人在中软国际ETC培训时老师发送的ORACLE数据库教程课件，希望能给学习oracle的人一些帮助，个人觉得编排很好，也很实用。
尚学堂Spring学习笔记.马士兵的，看了很受用，摘录了一些
尚学堂Java笔记.pdf
drp尚学堂培训日志，详细的过程。一天天的添加。大家看看啊！
系统功能包括： 个人办公：1.我的办公桌，2.我的便签，3.我的任务，4....公文管理：1.公文维护，2....公共信息：1....行政办公：1....工作流程：1....组织管理：1....权限管理：1....系统管理：1....人事档案：1....数据库采用MYSQL（可以自己...
喜欢老马的人都知道，尚学堂的javascript，很强大
我网上找的尚学堂书籍推荐，希望对自学的朋友有帮助
尚学堂hibernate学习笔记(原版整理)，配合视频教程使用
本人自己总结的尚学堂oracle笔记，希望能派上用场，本资源不收资源分，大家可以随意下载
尚学堂马世兵oracle课堂笔记一，尚学堂马世兵oracle课堂笔记。
尚学堂_Hibernate3.3.2_项目源码，尚学堂_Hibernate3.3.2_项目源码
尚学堂马士兵hibernate视频笔记，很详细的，各种配置都有的......
ssh2的OA管理项目完全代码，仿尚学堂的ssh，采用新技术，通过详细的代码展现OA系统
尚学堂学习uml的笔记，希望给初学者指导迷津
本人初学java时根据尚学堂的视频一步一步所写，最后又自己稍加修改得来的程序代码。注意：本程序不是一个完整的坦克游戏，适用于初学java者
此资料是对尚学堂高琪老师所讲解的GOF23设计模式的专门整理，每个设计模式的资料+高琪老师编写的入门code都是分类存储，并且包括UML设计图遇到需要的设计模式可以在相关的文件夹查找。
25.尚学堂SQL简单讲解 70 Oracle常用函数 73 (1)trunc(for date) 73 (2)trunc(number) 73 (3)to_char 73 (4)to_date 74 (5)to_number 74 (6)instr 74 (7)substr 74 (8)trim 75 (9)translate 75 (10)replace 76 (11)...