第一课：客户端
1. Sql Plus(客户端），命令行直接输入：sqlplus，然后按提示输入用户名，密码。
2. 从开始程序运行:sqlplus，是图形版的sqlplus.
3. http://localhost:5560/isqlplus
Toad：管理， PlSql Developer:
第二课：更改用户
1. sqlplus sys/bjsxt as sysdba
2. alter user scott account unlock;(解锁)
第三课：table structure
1. 描述某一张表：desc 表名
2. select * from 表名
第四课：select 语句：
1.计算数据可以用空表：比如：.select 2*3 from dual
2.select ename,sal*12 annual_sal from emp;与select ename,sal*12 "annual sal" from emp;区别，加双引号保持原大小写。不加全变大写。
3. select ename || "abcd" 如果连接字符串中含有单引号，用两个单引号代替一个单引号。
第五课：distinct
select deptno from emp;
select distinct deptno from emp;
select distinct deptno from emp;
select distinct deptno ,job from emp
去掉deptno,job两者组合的重复。更多的项，就是这么多项的组合的不重复组合。
第六课：Where
select * from emp where deptno =10;
select * from emp where deptno <>10;不等于10
select * from emp where ename ='bike';
select ename,sal from emp where sal between 800 and 1500 (>=800 and <=1500)
空值处理:
select ename,sal,comm from emp where comm is (not) null;
select ename,sal,comm from emp where ename ( not)in ('smith','king','abc');
select ename from emp where ename like '_A%';_代表一个字母,%代表0个或多个字母. 如果查询%
可用转义字符./%. 还可以用escape '$'比如:select ename from emp where ename like '%$a%' escape '$';
第七课: orderby
select * from dept;
select * from dept order by dept desc;(默认:asc)
select ename,sal,deptno from emp order by deptno asc,ename desc;
第八课: sql function1:
select ename,sal*12 annual_sal from emp
where ename not like '_A%' and sal>800
order by sal desc;
select lower(ename) from emp;
select ename from emp
where lower(ename) like '_a%';等同于
select ename from emp where ename like '_a%' or ename like '_A%';
select substr(ename,2,3) from emp;从第二字符截,一共截三个字符.
select chr(65) from dual 结果为:A
select ascii('a') from dual 结果为:65
select round(23.652,1) from dual; 结果为: 23.7
select round(23.652,-1) from dual; 20
select to_char(sal,'$99_999_999') from emp;
select to_char(sal,'L99_999_999') from emp;人民币符号,L:代表本地符号
这个需要掌握牢:
select birthdate from emp;
显示为:
BIRTHDATE
----------------
17-12月-80
----------------
改为:
select to_char(birthdate,'YYYY-MM-DD HH:MI:SS') from emp;
显示:
BIRTHDATE
-------------------
1980-12-17 12:00:00
-------------------
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; //也可以改为:HH12
TO_CHAR(SYSDATE,'YY
-------------------
2007-02-25 14:46:14
to_date函数:
select ename,birthdate from emp where birthdate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
如果直接写 birthdate>'1981-2-20 12:34:56'会出现格式不匹配,因为表中的格式为: DD-MM月-YY.
select sal from emp where sal>888.88 无错.但
select sal from emp where sal>$1,250,00;
会出现无效字符错误.
改为:
select sal from emp where sal>to_number('$1.250.00','$9,999,99');
把空值改为0
select ename,sal*12+nvl(comm,0) from emp;
这样可以防止comm为空时,sal*12相加也为空的情况.
第九课: Group function 组函数
max,min,avg ,count,sum函数
select to_char(avg(sal),'99999999,99') from emp;
select round(avg(sal),2) from emp;
结果:2073.21
select count(*) from emp where deptno=10;
select count(ename) from emp where deptno=10; count某个字段,如果这个字段不为空就算一个.
select count(distinct deptno) from emp;
select sum(sal) from emp;
第十课: Group by语句
需求:现在想求,求每个部门的平均薪水.
select avg(sal) from emp group by deptno;
select deptno avg(sal) from emp group by deptno;
select deptno,job,max(sal) from emp group by deptno,job;
求薪水值最高的人的名字.
select ename,max(sal) from emp;出错,因为max只有一个值,但等于max值的人可能好几个,不能匹配.
应如下求:
select ename from emp where sal=(select max(sal) from emp);
Group by语句应注意,
出现在select中的字段,如果没出现在组函数中,必须出现在Group by语句中.
第十一课: Having 对分组结果筛选
Where是对单条纪录进行筛选,Having是对分组结果进行筛选.
select avg(sal),deptno from emp
group by deptno
having avg(sal)>2000;
查询工资大于1200雇员,按部门编号进行分组,分组后平均薪水大于1500,按工薪倒充排列.
select * from emp
where sal>1200
group by deptno
having avg(sal)>1500
order by avg(sal) desc;
第十二课:字查询
谁挣的钱最多(谁:这个人的名字, 钱最多)
select 语句中嵌套select 语句,可以在where,from后.
问那些人工资,在平均工资之上.
select ename,sal from emp where sal>(select avg(sal) from emp);
查找每个部门挣钱最多的那个人的名字.
select ename ,deptno from emp where sal in(select max(sal) from ename group by deptno) 查询会多值.
应该如下:
select max(sal),deptno from emp group by deptno;当成一个表.语句如下:
select ename, sal from emp join(select max(sal) max_sal,deptno from emp group
by deptno) t on(emp.sal=t.max_sal and emp.deptno=t.deptno);
每个部门的平均薪水的等级.
分析:首先求平均薪水(当成表),把平均薪水和另外一张表连接.
第十四课:self_table_connection
把某个人的名字以及他的经理人的名字求出来(经理人及这个人在表中同处一行)
分析:首先求出这个人的名字,取他的编号,然后从另一张表与其相对应编号,然后找到经理的名字.
select e1.ename ,e2.ename from emp e1,emp e2 where e1.mgr= e2.empno.
empno编号和MGR都是编号.
第十15课: SQL1999_table_connections
select ename,dname,grade from emp e,dept d, sqlgrade s
where e.deptno = d.deptno and e.sql between s.losal and s.hisal and
job<>'CLERK';
有没有办法把过滤条件和连接条件分开来? 出于这样考虑,Sql1999标准推出来了.有许多人用的还是
旧的语法,所以得看懂这种语句.
select ename,dname from emp,dept;(旧标准).
select ename,dname from emp cross join dept;(1999标准)
select ename,dname from emp,dept where emp.deptno=dept.deptno (旧)
select ename,dname from emp join dept on(emp.deptno = dept.deptno); 1999标准.没有Where语句.
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);
join 连接语句, on过滤条件。连接，条件一眼分开。如果用Where语句较长时，连接语句和过滤语句混在一起。
三张表连接：
slect 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%';
把每张表连接 条件不混在一起，然后数据过滤条件全部区分开来。读起来更清晰，更容易懂一点。
select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.emptno);
左外连接：会把左边这张表多余数据显示出来。
select e1.ename,e2,ename from emp e1 left join emp e2 on(e1.mgr =e2.empno);left 后可加outer
右外连接：
select ename,dname from emp e right outer join dept d on(e.deptno =d.deptno); outer可以取掉。
即把左边多余数据，也把右边多余数据拿出来，全外连接。
select ename,dname from emp e full join dept d on(e.deptno =d.deptno);
16-23 课：求部门平均薪水的等级
A.求部门平均薪水的等级。
select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno)t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
B.求部门平均的薪水等级
select deptno,avg(grade) from
(select deptno,ename, grade from emp join salgrade s on(emp.sal between s.losal and
s.hisal)) t
group by deptno
C.那些人是经理
select ename from emp where empno in(select mgr from emp);
select ename from emp where empno in(select distinct mgr from emp);
D.不准用组函数，求薪水的最高值（面试题）
select distinct sal from emp where sal not in(
select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal));
E.平均薪水最高的部门编号
select deptno,avg_sal from
(select avg(sal)avg_sal,deptno from emp group by deptno)
where avg_sal=
(select max(avg_sal)from
(select avg(sal) avg_sal,deptno from emp group by deptno)
)
F.平均薪水最高的部门名称
select dname from dept where deptno=
(
select deptno from
(select avg(sal)avg_sal,deptno from emp group by deptno)
where avg_sal=
(select max(avg_sal)from
(select avg(sal) avg_sal,deptno from emp group by deptno)
)
)
G.求平均薪水的等级最低的部门的部门名称
组函数嵌套
如：平均薪水最高的部门编号，可以E.更简单的方法如下：
select deptno,avg_sal from
(select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal =
(select max(avg(sal)) from emp group by deptno)
组函数最多嵌套两层
分析：
首先求
1.平均薪水： select avg(sal) from group by deptno;
2.平均薪水等级： 把平均薪水当做一张表，需要和另外一张表连接salgrade
select deptno,grade avg_sal from
( select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
上面结果又可当成一张表。
DEPTNO GRADE AVG_SAL
-------- ------- ----------
30 3 1566.66667
20 4 2175
10 4 2916.66667
3.求上表平均等级最低值
select min(grade) from
(
select deptno,grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno)t
join salgrade s on(t.avg_sal between s.losal and s.hisa)
)
4.把最低值对应的2结果的那张表的对应那张表的deptno, 然后把2对应的表和另外一张表做连接。
select dname ,deptno,grade,avg_sal from
(
select deptno,grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno)t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
) t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select deptno,grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
)
)
结果如下：
DNAME DEPTNO GRADE AVG_SAL
-------- ------- -------- --------
SALES 30 3 1566.6667
H: 视图（视图就是一张表，一个字查询）
G中语句有重复，可以用视图来简化。
conn sys/bjsxt as sysdba;
grant create table,create view to scott;
conn scott/tiger
创建视图：
create view v$_dept_avg-sal_info as
select deptno,grade,avg_sal from
( select deptno,avg(sal) avg_sal from emp group by deptno)t
join salgrade s on 9t.avg_sal between s.losal and s.hisal)
然后
select * from v$_dept_avg-sal_info
结果如下：
DEPTNO GRADE AVG_SAL
-------- ------- ----------
30 3 1566.66667
20 4 2175
10 4 2916.66667
然后G中查询可以简化成：
select dname,t1.deptno,grade,avg_sal from
v$_dept_avg-sal_info t1
join dept on9t1.deptno =dept.deptno)
where t1.grade=
(
select min(grade) from v$_dept_avg-sal_info t1
)
全是我自己做的笔记，供大家一起分享和探讨：
第一课：客户端
1.Dos命令行下：sqlplus 用户名：scott 密码：tiger,sqlplus scott/tiger
2.开始－运行－sqlplus 图形版的SQLPLUS
3.http://localhost:5560/isqlplus
Toad:管理 Plsql Developer
第二课：更改用户
1.sqlplus sys/bjsxt as sysdba
2.alter user scott account unlock;(解锁)
第三课：table_structures
1.desc 表名
2.select * from 表名
第四课：select语句
1.select ename, sal*12 annual_sal from emp;
select ename sal*12 "annual sal" from emp;双引号可以保持原大小写，并且中间可以加空 格，不加全变为大写
2.desc dual，select 2*3 from dual，dual是系统自带的一张空表，计算数据时可以使用该表
3.任何含有空值的算术表达式的计算结果是空值
4.select ename||sal from emp;
5.select ename || 'abcdefg' from emp;
6.select ename || 'abcd''efg' from emp;当字符串中含有单引号时，可用两个单引号代表一 个单引号
7.set linesize 200;--用于设定每行显示的宽度
8.set pagesize 30;--设置显示的页数
第五课：distinct
1.select distinct deptno from emp;
2.select distinct deptno,job from emp;
第六课：where
1.select * from emp where empno = 10;
2.select * from emp where empno <> 10;
3.select * from emp where ename = 'hebe';
4.select * from emp where sal (not) between 800 and 1500;
5.select * from emp where comm is (not) null;
6.select * from emp where ename (not) in ('smith','philip','jay');
7.select * from emp where ename like '_A%';
8.select * from emp where ename like '_/%a%';系统默认转义符是/,可以自己指定转义符
select * from emp where ename like '_$%a%' escape '$';
第七课：order by
1.select * from dept;
2.select * from dept order by dept desc;(默认为asc)
3.select ename, sal, deptno from emp where sal > 2000 order by deptno asc,ename desc;
第八课：sql_function
1.select ename,sal*12 annual_sal from emp where ename not like '_A%' and sal > 1500
order by sal desc;
2.select lower(ename) from emp;
3.select ename from emp where lower(ename) like '_a%';
4.select substr(ename,2,3) from emp;从第二字符截，一共截三个字符。
5.select chr(65) from dual;结果为A
6.select ascii('a') from dual;结果为65
7.select round(35.572) from dual;结果为36
8.select round(35.572,2) from dual;结果为35.57
9.select round(35.572,-1) from dual;结果为40
10.select to_char(sal,'$99,999.9999') from emp;to_char函数主要用于对日期和数字进行格 式化
11.select to_char(sal,'L99,999.9999') from emp;人民币符号，L代表本地符号。
12.select birthdate from emp;
显示为：BIRTHDATE
---------------------
22-3月-87
改为：select to_char(birthdate,'YYYY-MM-DD HH24:MI:SS') from emp;
13.to_data函数
select ename,birthdate from emp where birthdate > to_date('1987-3-22 11:22:33','YYYY-MM-DD HH24:MI:SS');不能直接写birthdate>'1987-2-22 11:22:33'会出现格 式不匹配，因为表中格式为DD-MM月－YY,
14.select sal from emp where sal > to_number('$12,444.99','$99,999.99');
15.select ename, sal*12+nvl(comm,0) from 这样防止comm为空时，sal*12相加也为空的情况
第九课：Group function 组函数
1.select max(sal) from emp;
2.select min(sal) from emp;
3.select to_char(avg(sal), '$999,999,999.99') from emp;
4.select round(sum(sal),2) from emp;
5.select count(*) from emp where sal > 1500;
6.select count(comm) from emp;
7.select count(distinct deptno) from emp;
第十课：Group by语句
1.select avg(sal) from emp group by deptno;
2.select deptno,avg(sal) from emp group by deptno;
3.select deptno,job,max(sal) from emp group by deptno,job;
4.求薪水值最高的人的名称select ename,max(sal) from emp;出错，因为max只能有一个值，但 是等于max的值可能有好几个，不能匹配。
可以写成select ename from emp where sal=(select max(sal) from emp);
Group by 语句应注意，出现在select中的字段，如果没有出现在组函数中，必须出现在Group by语句中。
第十一课：Having对分组结果筛选
1.where是对单条记录进行筛选,Having是对分组结果进行筛选
select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;
2.查询工资大于2000的雇员,按照部门编号进行分组,分组后平均薪水大于1500,按工资倒序排列
select deptno,avg(sal) from emp where sal > 2000 group by deptno having avg(sal) > 1500 order by avg(sal) desc;
第十二课:子查询
1.select 语句中嵌套select 语句,求哪些人工资在平均工资之上.
select ename,sal from emp where sal > (select avg(sal) from emp);
2.查找每个部门挣钱最多的那个人的名字.
select ename, deptno from emp where sal in (select max(sal) from emp group by deptno) 查询会多值.正确写法是:
应把select max(sal),deptno from emp group by deptno当成一个表,语句如下:
select ename,sal from emp join (select max(sal) max_sal,deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno);
第十三课:self_table_connection
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;
第十四课:SQL1999_table_connection
1.select ename,dname,grade from emp e,dept d,sqlgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal and job <> 'CLERK';
这种写法没有把过滤条件和连接条件分开来,由于这个原因,sql1999标准推出来了.
2.select ename,dname from emp,dept;(旧标准)
select ename,dname from emp cross join dept;(1999标准)
3.select ename,dname from emp,dept where emp.deptno = dept.deptno;(旧)
select ename,dname from emp join dept on (emp.deptno = dept.deptno)
4.select ename,grade from emp e join salgrade s on(s.sal between s.losal and ` s.hisal);
5.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%';
6.select e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr = e2.deptno);
7.select e1.ename,e2.ename from emp e1 left (outer) join emp e2 on (e1.mgr = e2.empno);
8.select ename,dname from emp e right outer join dept d on (e.deptno = d.deptno);
9.select ename,dname from emp e full join dept d on (e.deptno = d.deptno);
第十六至二十三课:求部门平均薪水等级
1.A.求部门平均薪水的等级
select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal)
2.求部门平均的薪水等级
select deptno,avg(grade) from (select deptno,ename,grade from emp e join salgrade s on (emp.sal between s.losal and s.hisal)) t group by deptno;
3.哪些人是经理
select ename2 from (select e1.ename ,e1.mgr mgr1,e2.ename ename2 from emp e1 join emp e2 on (e1.mgr = e2.empno)) t join emp on (t.mgr1 = emp.empno)
select ename from emp where empno in (select distinct mgr from emp)
4.不准用组函数,求薪水的最高值
select distinct sal from emp where sal not in(
select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal) )
5.求平均薪水最高的部门编号
select deptno,avg_sal from (
select deptno,avg(sal) avg_sal from dept group by deptno
)
where avg_sal = (
select max(avg_sal) from (
select deptno,avg(sal) avg_sal from dept group by deptno
)
)
select deptno,avg_sal from
(select deptno,avg(sal) avg_sal from dept group by deptno)
where avg_sal =
(select max(avg(sal)) from dept group by deptno)
6.求平均薪水最高的部门名称
select dname from dept where deptno =
(
select deptno from
(select deptno,avg(sal) avg_sal from dept group by deptno)
where avg_sal =
(
select max(avg_sal) from
(select deptno,avg(sal) avg_sal from dept group by deptno)
)
)
7.求平均薪水等级最低的部门的部门名称
错误写法:
select min(avg_grade),deptno from
(
select deptno,avg(grade) avg_grade
(
select ename,deptno,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal)
) group by deptno
) group by deptno
正确写法:
select dname,t1.deptno,grade,avg_sal from
(
select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)
) t1 join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select min(grade) from
(
select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)
)
)
8.视图:view就是一张虚表,一个子查询
conn sys/change_on_install as sysdba;
grant create table,create view to scott;
conn scott/tiger;
创建视图
create view v$_dept_avg_sal_info as
select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)
使用视图后可以简写
select dname,t1.deptno,grade,avg_sal_info from
v$_dept_avg_sal t1 join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select min(grade) from v$_dept_avg_sal_info
)
9.求比普通员工最高薪水还要高的经理人的名称
先求普通员工的最高薪水
select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null);
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);
)
第二十四课
备份与恢复
DOS环境下:exp导出,imp导入
--drop user yugang cascade;
--cd /
--cd temp
=-delete *.*
--exp
--create user yugang indentified by yugang default tablespace users quota 10M on users
--grant create session,create table,create view to yugang
--imp the data
第一次输入的用户名密码为:yugang/yugang
第二次输入的用户名密码为:导出数据的用户的用户名和密码
--rollback
create table emp2 as select * from emp;
第二十五课:rownum
rownum是在Oracle中在表的后面加的一个尾字段,并且只能使用诸如rownum<n或rownum<=n这样的 形式,不能与>或>=相连接
1.求薪水值最高的前5条记录.
select ename,sal from emp order by sal desc where sal <= 5;
2.求薪水值最高的第6~10条记录.
select ename,sal from
(
select ename,sal,rownum r from
(select ename,sal from emp order by sal desc)
)
where r >=6 and r <= 10;
第二十六课:homework_dml_transaction
SQL面试题
有三张表S、C、SC
S(SNO、SNAME)代表（学号、姓名）
C(CNO、CNAME、CTEACHER)代表（课号、课名、老师）
SC(SNO、CNO、SCGRADE)代表（学号、课号、成绩）
1.求出没选过郭富城老师的所有学生姓名
2.列出2门以上（含2门）不及格的学生姓名及平均成绩
3.既学过1号课程又学过2号课程的所有学生姓名
1.select sname from s join sc on (s.sno = sc.sno) join c on (c.cno = sc.cno)
where c.cteacher <> '郭富城';
2.select sname from s where sno in
(
select sno from sc where scgrade < 60 group by sno having count(*) >= 2
)
3.select sname from s where sno in
(
select sno from sc where cno=1 and sno in
(select distinct sno from sc where cno=2)
)
事务transaction代表一组不可分割的操作，要么全部执行，要么全部不执行，
transaction起始于一条DML语句，结束于commit;语句，或者是DCL、DDL语句，在事务未提交前
可以通过rollback回滚事务，正常退出事务会自动提交，非正常退出事务会自动回滚。
第二十七课：create table
create table student
(
id number(6),
name varchar2(20),
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50)
);
第二十八~三十课 constraint
not null,unique(当某字段有unique约束时，可以插入空值，空值之间不重复)、主键、外键、 check约束
create table student
(
id number(6),
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50) unique
)
/
行级约束（放在字段后面）与表级约束（加在表后面）：
create table student
(
id number(6),
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50),
constraint stu_email_name_uni unique(name,email)
)
/
主键约束
create table student
(
id number(6) primary key,
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50),
constraint stu_email_name_uni unique(name,email)
)
/
create table student
(
id number(6),
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50),
constraint stu_id_pk primary key(id),
constraint stu_email_name_uni unique(name,email)
)
/
外键约束
外键约束被参考的字段必须是主键。
create table class
(
id number(4) primary key,
name varchar2(20) not null
)
/
create table student
(
id number(6),
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4) references class(id),
email varchar2(50),
constraint stu_id_pk primary key(id),
constraint stu_email_name_uni unique(name,email)
)
/
create table student
(
id number(6),
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50),
constraint stu_id_pk primary key(id),
constraint stu_class_fk foreign key (class) references class(id),
constraint stu_email_name_uni unique(name,email)
)
/
第三十一课：alter table drop table
alter table student add(addr varchar2(100));
alter table student drop(addr);
alter table student modify(addr varchar2(50));
若addr字段中有的记录长度大于50，则修改不成功。
alter table student drop constraint stu_class_fk;
alter table student add constraint stu_class_fk foreign key (class)
references class (id);
drop table class;
第32课：Oracle dictionaries
desc user_tables;
select table_name from user_tables;
select view_name from user_views;
select constraint_name from user_constraints;
select constraint_name,table_name from user_constraints;
desc dictionary;
第33课：indexes_and_views
索引可以提高读数据的效率，但会降低修改、写数据的效率,索引还会占用一定的存储空间
create index idx_stu_email on student(email);
drop index idx_stu_email;
视图会带来一些维护的代价，比如表结构改了，那么视图也得跟着改变。视图还可以用来保
护私有数据。
select index_name from user_indexes;
select view_name from user_views;
第34课：sequences and review
序列可以自动增长，在sql server中有identity，mysql中有auto_increment
create table article
(
id number,
title varchar2(1024),
cont long
)
/
如何保证插入数据时id不重复？
select max(id) from article;这样做也不行，如果有多个线程同时访问数据，则可能会出现
数据不一致的现象，比方说第一个用户查询出最大id值是100，第二个用户也查询出最大id是
100，然后第二个用户新插入一条记录：id是101，然后第一个用户也插入了一条记录：id也是
101
可以用Oracle的sequence
create sequence seq_article_id;
多个表之问可以共用一个序列，但是一般情况下为每个字段分别建立一个序列
sequence有两个属性CurrVal、NextVal
select seq.nextvalue from dual;
insert into article values(seq.nextval,'y','yy');
insert into article values(seq.nextval,'x','xx');
insert into article values(seq.nextval,'z','zz');
insert into article values(seq.nextval,'q','qq');
第35课：三范式
第一范式：
1.每个表都要有主键
2.列不可分，比如：
create table stu
(
id number,
name varchar2(20),
age number
)
/
insert into stu values(1,'yu',21);
create table stu2
(
inf long
)
/
insert into stu2 values('1_yu_21');
虽然表stu2可以字符串解析后可以达到和表stu一样的效果，但是显然第一种方法更好，查询数
据更加方便，而表stu2违反了第一范式的列不可分原则。
第二范式：
当有两个以上字段作主键时，非主键字段不能部分依赖于主键字段，如有一个需求，一门老师教 多名学生，一名学生可以选多个老师的课。然后设计了一张表，有以下字段（老师编号、学生编 号、老师姓名、学生姓名等），其中以老师编号和学生编号作联合主键，则些表就存在部分依赖 ，老师姓名部分依赖于老师编号，不满足第二范式，有数据冗余。要解决这个问题可以设计三张 表。
第三范式：
不能存在传递依赖，如有一张表有以下字段：（学号、姓名、班级号、班级名称、班级位置）， 其中学号为主键，则班级号依赖于学号，每个学生都有相应的班级号，但是班级名称、班级位置 是依赖于班级号，即它们通过班级号传递依赖于学号，不满足第三范式。
第38课：PL_SQL
set serveroutput on;
begin
dbms_output.put_line('Hello World!!');
end;
declare
v_name varchar2(20);
begin
v_name:='xiaoyu';
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
when others then
dbms_output.put_line('error');
end;
--变量声明的规则
1.变量名不能使用保留字
2.第一个字符必须是字母
3.变量名最多包含30个字母
4.不要与数据库或表名同名
5.每一行只能声明一个变量
/*
这是多行注释
*/
--常用变量类型
1.binary_integer：整数，主要用来计数而不是用来表示字段类型
2.number数字类型
3.char定长字符串
4.date日期
5.long长字符串，最长2GG
6.boolean类型：可以取值true、false、null，默认为null，另外boolean类型值不可直接打印
--变量声明
declare
v_temp number(1);
v_count binary_integer:=0;
v_sal number(7,2):=8888.88;
v_date date:=sysdate;
v_pi constant number(3,2):=3.14;
v_valid boolean:=false;
v_name varchar2(20) not null:='geniusxiaoyu';
begin
dbms_output.put_line('v_temp value:' || v_temp);
end;
--变量声明：使用%type属性
declare
v_empno number(4);
v_empno2 emp.empno%type;
v_empno3 v_empno2%type;
begin
dbms_output.put_line('test for type');
end;
--Table变量类型,相当于数组类型
declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
v_empnos typa_table_emp_empno;
begin
v_empnos(0):=7777;
v_empnos(1):=7778;
v_empnos(-1):=6666;
dbms_output.put_line(v_empnos(-1));
end;
--Record变量类型，相当于类
declare
type type_record_dept is record
(
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:='yugang';
v_temp.loc:='beijing';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname || ' ' || v_temp.loc);
end;
--使用%rowtype声明record变量
declare
v_temp dept%rowtype;
begin
v_temp.deptno:=50;
v_temp.dname:='yugang';
v_temp.loc:='beijing';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname || ' ' || v_temp.loc);
end;
--SQL语句的运用
--select语句必须和into语句一块使用并且只能返回一条记录
--sql%rowcount
declare
v_name emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_name,v_sal from emp where empno=7369;
dbms_output.put_line(v_name || ' ' || v_sal);
end;
declare
v_temp emp%rowtype;
begin
select * into v_temp from emp where empno=7369;
dbms_output.put_line(v_temp.ename || ' ' || v_temp.eno);
end;
declare
v_deptno dept.deptno%type:=50;
v_dname dept.dname%type:='mm';
v_loc dept.loc%type:='bj';
begin
insert into dept values(v_deptno,v_dname,v_loc);
commit;
end;
declare
v_deptno dept.deptno%type:=50;
v_count number;
begin
--update emp set sal:=sal/2 where deptno=v_deptno;
--select deptno into v_deptno from emp where deptno=7369;
select count(*) into v_count from emp;
dbms_output.put_line(sql%rowcount || '条记录被影响！');
commit;
end;
--DDL语句，在PLSQL中使用DDL语句要加上execute immediate,两个单引号代表一个单引号
begin
execute immediate 'create table tt(name varchar2(20) default ''Army'')';
end;
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=7369;
if(v_sal<1200) then
dbms_output.put_line('low');
elsif(v_sal<2000) then
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if;
end;
--循环
declare
i binary_integer:=1;
begin
loop
dbms_output.put_line(i);
i:=i+1;
exit when (i >= 11);
end loop;
end;
declare
j binary_integer:=1;
begin
while j<11 loop
dbms_output.put_line(j);
j:=j+1;
end loop;
end;
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
for k in reverce 1..10 loop
dbms_output.put_line(k);
end loop;
end;
--错误处理
declare
v_temp number;
begin
select empno into v_temp where deptno=10;
exception
when too_many_rows then
dbms_output.put_line('太多记录了');
when others then
dbms_output.put_line('error');
end;
declare
v_temp number;
begin
select deptno into v_temp from emp where empno=2222;
exception
when no_data_found then
dbms_output.put_line('没数据');
end;
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
);
create sequence seq_errorlog_id start with 1 increment by 1;
declare
v_deptno dept.deptno%type:=10;
v_errcode number;
v_errmsg varchar2(1024);
begin
delete from dept where deptno=v_deptno;
commit;
exception
when others then
rollback;
v_errcode:=SQLCODE;
v_errmsg:=SQLERRM;
insert into errlog values(seq_errorlog_id.nextVal,v_errcode,v_errmsg,sysdate);
commit;
end;
第47~48课：cursor（重点）
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
dbms_output.put_line(v_emp.ename);
close c;
end;
--简单循环
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;
exit when(c%notfound);
dbms_output.put_line(v_emp.ename);
end loop;
end;
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;
--下面两行顺序改变后，将会把最后一条记录打印两遍
dbms_output.put_line(v_emp.ename);
exit when(c%notfound);
end loop;
end;
--while循环
declare
cursor c is
select * from emp;
v_emp emp%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;
end loop;
close c;
end;
--for循环
declare
cursor c is
select * from emp;
begin
open c;
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
close c;
end;
--带参数的游标
declare
cursor c(v_deptno emp.deptno%type,v_job emp.job%type) is
select ename,sal from emp where deptno=v_deptno and job=v_job;
--v_emp emp%rowtype;
begin
for v_emp in c(30,'JAY') loop
dbms_output.put_line(v_emp.ename);
end loop;
end;
--可更新的游标
declare
cursor c is
select * from emp for update;
--v_temp c%rowtype;
begin
for v_temp in c loop
if(v_temp.sal < 2000) then
update emp set sal=sal*2 where current of c;
elsif(v_temp.sal=5000) then
delete from emp where current of c;
end if;
end loop;
commit;
end;
第49～50课：procedure
create or replace procedure p
is
cursor c is
select * from emp for update;
begin
for v_temp in c loop
if(v_temp.deptno=10) then
update emp set sal=sal+10 where current of c;
elsif(v_temp.deptno=20) then
update emp set sal=sal+20 where current of c;
else
update emp set sal=sal+50 where current of c;
end if;
end loop;
commit;
end;
--带参数的存储过程
create or replace procedure p
(v_a in number,v_b number,v_ret out number,v_temp in out number)
is
begin
if(v_a<v_b) then
v_ret:=v_b;
else
v_ret:=v_a;
end if;
v_temp:=v_temp+1;
end;
declare
v_a number:=10;
v_b number:=20;
v_ret number;
v_temp number:=99;
begin
p(v_a,v_b,v_ret,v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
/*
存储过程在创建过程中如果出现错误，仍然会创建，并且不会提示错误在哪儿，可以使用
show error来查看错误出现在哪儿。
*/
create or replace function tax_sal
(v_sal number)
return number
is
begin
if(v_sal<2000) then
return 0.10;
elsif(v_sal<3000) then
return 0.20;
else
return 0.30;
end if;
end;
--触发器
create table emp_log
(
uname varchar2(20);
action varchar2(10);
atime date
);
create or replace trigger trig
after/before insert or update or delete on emp2 for each row
begin
if inserting then
insert into emp_log values(USER,'insert',sysdate);
elsif updating then
insert into emp_log values(USER,'update',sysdate);
else
insert into emp_log values(USER,'delete',sysdate);
end if;
end;
update dept set deptno=99 where deptno=10;
--违反约束条件
create or replace trigger trig
after update on dept for each row
begin
update emp set deptno=:NEW.deptno where deptno=:OLD.deptno;
end;
第一课：客户端
1. Sql Plus(客户端），命令行直接输入：sqlplus，然后按提示输入用户名，密码。
2. 从开始程序运行:sqlplus，是图形版的sqlplus.
3. http://localhost:5560/isqlplus
Toad：管理， PlSql Developer:
第二课：更改用户
1. sqlplus sys/bjsxt as sysdba
2. alter user scott account unlock;(解锁)
第三课：table structure
1. 描述某一张表：desc 表名
2. select * from 表名
第四课：select 语句：
1.计算数据可以用空表：比如：.select 2*3 from dual
2.select ename,sal*12 annual_sal from emp;与select ename,sal*12 "annual sal" from emp;区别，加双引号保持原大小写。不加全变大写。
3. select ename || "abcd" 如果连接字符串中含有单引号，用两个单引号代替一个单引号。
第五课：distinct
select deptno from emp;
select distinct deptno from emp;
select distinct deptno from emp;
select distinct deptno ,job from emp
去掉deptno,job两者组合的重复。更多的项，就是这么多项的组合的不重复组合。
第六课：Where
select * from emp where deptno =10;
select * from emp where deptno <>10;不等于10
select * from emp where ename ='bike';
select ename,sal from emp where sal between 800 and 1500 (>=800 and <=1500)
空值处理:
select ename,sal,comm from emp where comm is (not) null;
select ename,sal,comm from emp where ename ( not)in ('smith','king','abc');
select ename from emp where ename like '_A%';_代表一个字母,%代表0个或多个字母. 如果查询%
可用转义字符./%. 还可以用escape '$'比如:select ename from emp where ename like '%$a%' escape '$';
第七课: orderby
select * from dept;
select * from dept order by dept desc;(默认:asc)
select ename,sal,deptno from emp order by deptno asc,ename desc;
第八课: sql function1:
select ename,sal*12 annual_sal from emp
where ename not like '_A%' and sal>800
order by sal desc;
select lower(ename) from emp;
select ename from emp
where lower(ename) like '_a%';等同于
select ename from emp where ename like '_a%' or ename like '_A%';
select substr(ename,2,3) from emp;从第二字符截,一共截三个字符.
select chr(65) from dual 结果为:A
select ascii('a') from dual 结果为:65
select round(23.652,1) from dual; 结果为: 23.7
select round(23.652,-1) from dual; 20
select to_char(sal,'$99_999_999') from emp;
select to_char(sal,'L99_999_999') from emp;人民币符号,L:代表本地符号
这个需要掌握牢:
select birthdate from emp;
显示为:
BIRTHDATE
----------------
17-12月-80
----------------
改为:
select to_char(birthdate,'YYYY-MM-DD HH:MI:SS') from emp;
显示:
BIRTHDATE
-------------------
1980-12-17 12:00:00
-------------------
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; //也可以改为:HH12
TO_CHAR(SYSDATE,'YY
-------------------
2007-02-25 14:46:14
to_date函数:
select ename,birthdate from emp where birthdate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
如果直接写 birthdate>'1981-2-20 12:34:56'会出现格式不匹配,因为表中的格式为: DD-MM月-YY.
select sal from emp where sal>888.88 无错.但
select sal from emp where sal>$1,250,00;
会出现无效字符错误.
改为:
select sal from emp where sal>to_number('$1.250.00','$9,999,99');
把空值改为0
select ename,sal*12+nvl(comm,0) from emp;
这样可以防止comm为空时,sal*12相加也为空的情况.
第九课: Group function 组函数
max,min,avg ,count,sum函数
select to_char(avg(sal),'99999999,99') from emp;
select round(avg(sal),2) from emp;
结果:2073.21
select count(*) from emp where deptno=10;
select count(ename) from emp where deptno=10; count某个字段,如果这个字段不为空就算一个.
select count(distinct deptno) from emp;
select sum(sal) from emp;
第十课: Group by语句
需求:现在想求,求每个部门的平均薪水.
select avg(sal) from emp group by deptno;
select deptno avg(sal) from emp group by deptno;
select deptno,job,max(sal) from emp group by deptno,job;
求薪水值最高的人的名字.
select ename,max(sal) from emp;出错,因为max只有一个值,但等于max值的人可能好几个,不能匹配.
应如下求:
select ename from emp where sal=(select max(sal) from emp);
Group by语句应注意,
出现在select中的字段,如果没出现在组函数中,必须出现在Group by语句中.
第十一课: Having 对分组结果筛选
Where是对单条纪录进行筛选,Having是对分组结果进行筛选.
select avg(sal),deptno from emp
group by deptno
having avg(sal)>2000;
查询工资大于1200雇员,按部门编号进行分组,分组后平均薪水大于1500,按工薪倒充排列.
select * from emp
where sal>1200
group by deptno
having avg(sal)>1500
order by avg(sal) desc;
第十二课:字查询
谁挣的钱最多(谁:这个人的名字, 钱最多)
select 语句中嵌套select 语句,可以在where,from后.
问那些人工资,在平均工资之上.
select ename,sal from emp where sal>(select avg(sal) from emp);
查找每个部门挣钱最多的那个人的名字.
select ename ,deptno from emp where sal in(select max(sal) from ename group by deptno) 查询会多值.
应该如下:
select max(sal),deptno from emp group by deptno;当成一个表.语句如下:
select ename, sal from emp join(select max(sal) max_sal,deptno from emp group
by deptno) t on(emp.sal=t.max_sal and emp.deptno=t.deptno);
每个部门的平均薪水的等级.
分析:首先求平均薪水(当成表),把平均薪水和另外一张表连接.
第十四课:self_table_connection
把某个人的名字以及他的经理人的名字求出来(经理人及这个人在表中同处一行)
分析:首先求出这个人的名字,取他的编号,然后从另一张表与其相对应编号,然后找到经理的名字.
select e1.ename ,e2.ename from emp e1,emp e2 where e1.mgr= e2.empno.
empno编号和MGR都是编号.
第十15课: SQL1999_table_connections
select ename,dname,grade from emp e,dept d, sqlgrade s
where e.deptno = d.deptno and e.sql between s.losal and s.hisal and
job<>'CLERK';
有没有办法把过滤条件和连接条件分开来? 出于这样考虑,Sql1999标准推出来了.有许多人用的还是
旧的语法,所以得看懂这种语句.
select ename,dname from emp,dept;(旧标准).
select ename,dname from emp cross join dept;(1999标准)
select ename,dname from emp,dept where emp.deptno=dept.deptno (旧)
select ename,dname from emp join dept on(emp.deptno = dept.deptno); 1999标准.没有Where语句.
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);
join 连接语句, on过滤条件。连接，条件一眼分开。如果用Where语句较长时，连接语句和过滤语句混在一起。
三张表连接：
slect 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%';
把每张表连接 条件不混在一起，然后数据过滤条件全部区分开来。读起来更清晰，更容易懂一点。
select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.emptno);
左外连接：会把左边这张表多余数据显示出来。
select e1.ename,e2,ename from emp e1 left join emp e2 on(e1.mgr =e2.empno);left 后可加outer
右外连接：
select ename,dname from emp e right outer join dept d on(e.deptno =d.deptno); outer可以取掉。
即把左边多余数据，也把右边多余数据拿出来，全外连接。
select ename,dname from emp e full join dept d on(e.deptno =d.deptno);
16-23 课：求部门平均薪水的等级
A.求部门平均薪水的等级。
select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno)t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
B.求部门平均的薪水等级
select deptno,avg(grade) from
(select deptno,ename, grade from emp join salgrade s on(emp.sal between s.losal and
s.hisal)) t
group by deptno
C.那些人是经理
select ename from emp where empno in(select mgr from emp);
select ename from emp where empno in(select distinct mgr from emp);
D.不准用组函数，求薪水的最高值（面试题）
select distinct sal from emp where sal not in(
select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal));
E.平均薪水最高的部门编号
select deptno,avg_sal from
(select avg(sal)avg_sal,deptno from emp group by deptno)
where avg_sal=
(select max(avg_sal)from
(select avg(sal) avg_sal,deptno from emp group by deptno)
)
F.平均薪水最高的部门名称
select dname from dept where deptno=
(
select deptno from
(select avg(sal)avg_sal,deptno from emp group by deptno)
where avg_sal=
(select max(avg_sal)from
(select avg(sal) avg_sal,deptno from emp group by deptno)
)
)
G.求平均薪水的等级最低的部门的部门名称
组函数嵌套
如：平均薪水最高的部门编号，可以E.更简单的方法如下：
select deptno,avg_sal from
(select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal =
(select max(avg(sal)) from emp group by deptno)
组函数最多嵌套两层
分析：
首先求
1.平均薪水： select avg(sal) from group by deptno;
2.平均薪水等级： 把平均薪水当做一张表，需要和另外一张表连接salgrade
select deptno,grade avg_sal from
( select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
上面结果又可当成一张表。
DEPTNO GRADE AVG_SAL
-------- ------- ----------
30 3 1566.66667
20 4 2175
10 4 2916.66667
3.求上表平均等级最低值
select min(grade) from
(
select deptno,grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno)t
join salgrade s on(t.avg_sal between s.losal and s.hisa)
)
4.把最低值对应的2结果的那张表的对应那张表的deptno, 然后把2对应的表和另外一张表做连接。
select dname ,deptno,grade,avg_sal from
(
select deptno,grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno)t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
) t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select deptno,grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
)
)
结果如下：
DNAME DEPTNO GRADE AVG_SAL
-------- ------- -------- --------
SALES 30 3 1566.6667
H: 视图（视图就是一张表，一个字查询）
G中语句有重复，可以用视图来简化。
conn sys/bjsxt as sysdba;
grant create table,create view to scott;
conn scott/tiger
创建视图：
create view v$_dept_avg-sal_info as
select deptno,grade,avg_sal from
( select deptno,avg(sal) avg_sal from emp group by deptno)t
join salgrade s on 9t.avg_sal between s.losal and s.hisal)
然后
select * from v$_dept_avg-sal_info
结果如下：
DEPTNO GRADE AVG_SAL
-------- ------- ----------
30 3 1566.66667
20 4 2175
10 4 2916.66667
然后G中查询可以简化成：
select dname,t1.deptno,grade,avg_sal from
v$_dept_avg-sal_info t1
join dept on9t1.deptno =dept.deptno)
where t1.grade=
(
select min(grade) from v$_dept_avg-sal_info t1
)
--变量声明，使用%type属性
declare
v_empno number(4);
v_empno2 emp.empno%type;
v_empno3 v_empno2%type;
begin
dbms_output.put_line('test');
end;
--table变量类型(数组)
declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
v_empnos type_table_emp_empno;
begin
v_empnos(0) := 7369;
v_empnos(2) := 7839;
v_empnos(-1) := 9999;
dbms_output.put_line(v_empnos(-1));
end;
--record变量类型（近似java中的类）
declare
type type_record_dept is record
(
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 := 'aaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
--使用%rowtype声明record变量
declare
v_temp dept%rowtype;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
--SQL语句的运用
--select语句
declare
v_name emp.ename%type;
v_sal emp.sal%type;
begin
select ename, sal into v_name, v_sal from emp where empno = 7369;
dbms_output.put_line(v_name || ' ' || v_sal);
end;
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno = 7369;
dbms_output.put_line(v_emp.ename);
end;
--insert语句
declare
v_deptno dept.deptno%type := 50;
v_dname dept.dname%type := 'aaa';
v_loc dept.loc%type := 'bj';
begin
insert into dept2 values(v_deptno, v_dname, v_loc);
commit;
end;
declare
v_deptno emp2.deptno%type := 10;
v_count number;
begin
--update emp2 set sal = sal/2 where 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 || '条记录被影响');
commit;
end;
DDL语句
begin
execute immediate 'create table t (nnn varchar2(20) default ''aaa'')';
end;
--if语句
取出7369的薪水，如果<1200，输出'low'，如果<2000输出'middle'，否则'high'
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp
where empno = 7369;
if (v_sal < 1200) then
dbms_output.put_line('low');
elsif (v_sal < 2000) then
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if;
end;
--练习
--循环
declare
i binary_integer := 1;
begin
loop
dbms_output.put_line(i);
i := i + 1;
exit when (i >= 11);
end loop;
end;
---------
declare
j binary_integer := 1;
begin
while j < 11 loop
dbms_output.put_line(j);
j := j + 1;
end loop;
end;
-----------
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
--错误处理
declare
v_temp number(4);
begin
select empno into v_temp from emp where empno = 10;
exception
when too_many_rows then
dbms_output.put_line('太多纪录了');
when others then
dbms_output.put_line('error');
end;
----------
declare
v_temp number(4);
begin
select empno into v_temp from emp where empno = 2222;
exception
when no_data_found then
dbms_output.put_line('没有数据');
end;
---------
--创建事件日志表
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
)
--创建序列
create sequence seq_errorlog_id start with 1 increment by 1
--实验
declare
v_deptno dept.deptno%type := 10;
v_errcode number;
v_errmsg varchar2(1024);
begin
delete from dept where deptno = v_deptno;
commit;
exception
when others then
rollback;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
insert into errorlog values (seq_errorlog_id.nextval, v_errcode, v_errmsg, sysdate);
commit;
end;
--游标
declare
cursor c is
select * from emp;
v_temp c%rowtype;
begin
open c;
fetch c into v_temp;
dbms_output.put_line(v_temp.ename);
close c;
end;
------------------
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;
exit when (c%notfound);
dbms_output.put_line(v_emp.ename);
end loop;
close c;
end;
----------------------
declare
cursor c is
select * 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;
end loop;
close c;
end;
-----------------
declare
cursor c is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;
--带参数的游标
declare
cursor c (v_deptno emp.deptno%type, v_job emp.job%type)
is
select ename, sal from emp where deptno = v_deptno and job = v_job;
begin
for v_temp in c(30,'CLERK') loop
dbms_output.put_line(v_temp.ename);
end loop;
end;
--可更新的游标
declare
cursor c
is
select * from emp2 for update;
begin
for v_temp in c loop
if (v_temp.sal < 2000) then
update emp2 set sal = sal * 2 where current of c;
elsif (v_temp.sal = 5000) then
delete from emp2 where current of c;
end if;
end loop;
commit;
end;
----------------
--存储过程
create or replace procedure p
is
cursor c
is
select * from emp2 for update;
begin
for v_temp in c loop
if (v_temp.deptno = 10) then
update emp2 set sal = sal + 10 where current of c;
elsif (v_temp.deptno = 20) then
update emp2 set sal = sal + 20 where current of c;
else
update emp2 set sal = sal + 50 where current of c;
end if;
end loop;
commit;
end;
--执行
exec p;
或
begin;
p;
end;
--带参数的存储过程
create or replace procedure p
(v_a in number, v_b number, v_ret out number, v_temp in out number)
is
begin
if (v_a > v_b) then
v_ret := v_a;
else
v_ret := v_b;
end if;
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;
-------------------
--函数
create or replace function sal_tax
(v_sal number)
return number
is
begin
if (v_sal < 2000) then
return 0.10;
elsif (v_sal < 2750) then
return 0.15;
else
return 0.20;
end if;
end；
--触发器
create table emp2_log
(
uname varchar2(20),
action varchar(10),
atime date
)
-----------
create or replace trigger trig
after insert or update or delete on emp2
begin
if inserting then
insert into emp2_log values (USER, 'insert', sysdate);
elsif updating then
insert into emp2_log values (USER, 'update', sysdate);
elsif deleting then
insert into emp2_log values (USER, 'delete', sysdate);
end if;
end;
----------
update emp2 set sal = sal * 2 where deptno = 30;
--------
create or replace trigger trig
after insert or update or delete on emp2 for each row
begin
if inserting then
insert into emp2_log values (USER, 'insert', sysdate);
elsif updating then
insert into emp2_log values (USER, 'update', sysdate);
elsif deleting then
insert into emp2_log values (USER, 'delete', sysdate);
end if;
end;
-------------
--不提倡使用
create or replace trigger trig
after update on dept for each row
begin
update emp2 set deptno = :NEW.deptno where deptno = :OLD.deptno;
end;
----------------------
--树状结构的存储与展现
drop table article;
create table article
(
id number primary key,
cont varchar2(4000),
pid number,
isleaf number(1), --0代表非叶子节点，1代表叶子节点
alevel number(2)
)
-------------
insert into article values (1, '蚂蚁大战大象', 0, 0, 0);
insert into article values (2, '大象被打趴下了', 1, 0, 1);
insert into article values (3, '蚂蚁也不好过', 2, 1, 2);
insert into article values (4, '瞎说', 2, 0, 2);
insert into article values (5, '没有瞎说', 4, 1, 3);
insert into article values (6, '怎么可能', 1, 0, 1);
insert into article values (7, '怎么没可能', 6, 1, 2);
insert into article values (8, '可能性是很大的', 6, 1, 2);
insert into article values (9, '大象进医院了', 2, 0, 2);
insert into article values (10, '护士是蚂蚁', 9, 1, 3);
commit;
---------
蚂蚁大战大象
大象被打趴下了
蚂蚁也不好过
瞎说
没有瞎说
大象进医院了
护士是蚂蚁
怎么可能
怎么不可能
可能性是很大的
--------------------------
create or replace procedure p (v_pid article.pid%type, v_level binary_integer) is
cursor c is select * from article where pid = v_pid;
v_preStr varchar2(1024) := '';
begin
for i in 1..v_level loop
v_preStr := v_preStr || '****';
end loop;
for v_article in c loop
dbms_output.put_line(v_preStr || v_article.cont);
if (v_article.isleaf = 0)
then
p (v_article.id, v_level + 1);
end if;
end loop;
end;
sqlplus sys/sodi as sysdba
解锁用户： alter user scott account unlock
查看表结构：desc [表名]
特殊表：dual
查询当前日期：select sysdate from dual;
别名: 一：selct [字段] bbb_ccc from [表] ----别名会大写
二: selct [字段] "bbb ccc" from [表]
字符串合并：select [字段1]||[字段2] from [表]
去除重复数据：select distinct [字段] from [表] ---distinct可以修饰多个字段
like语句 _表示一个字符
查找字段为空值的数据：select * from [表] where [字段] is null
常用函数:一：lower()--转换为小写
select lower([字段]) from [表]
二：substr()--截取字符串
select substr([字段]，2，3) from [表]---从第二个字母开始截取，截三个字符
三:chr()--ASCII转换为字母
select chr(65) from dual
ascii()--字母转ASCII
select ascii('A') from dual
四:round()
select round(23.652) from dual--结果为24
select round(23.652，2) from dual--结果为24.65 四舍五入到小数点后两位
select round(23.652，-1) from dual--结果为20
五:to_char() ---转换格式
select to_char([字段]，‘$99，999.999’) from [表]
select to_char([字段]，‘L99，999.999’) from [表] --L代表本地货币符
select to_char([日期字段]，‘YYYY-MM-DD HH:MI:SS’) from [表]
select to_char([日期字段]，‘YYYY-MM-DD HH24:MI:SS’) from [表]
六:to_date()
select * from [表] where [字段]>to_date（‘1981-2-20 12：34：56’，‘YYYY-MM-DD HH24:MI:SS’）;
七:to_number()
select * from [表] where [字段] to_number('$1,250.00','$9.999.99')
八:nvl()--处理空值
select [字段1]*12+nvl([字段2]，0) from table--如果[字段2]为空值就用0表示
九:max(),min(),avg(),sum(),count()
count（[字段]）--字段不为空值就算一个
本套代码由北风狂D网制作，www.beifengkd.com，希望大家喜欢，北风狂D网是一个专业学习
资源下载的网站，希望大家看了资料也能去看看我们的网站，谢谢大家合作！
1.desc(描述） emp 描述emp这张表
2.desc dept 部门表
3.desc salgrade 薪水等级
4.select *from table 查找表中的元素
5.dual 是系统中的一张空表
6.select *from dual
7.select sysdate from dual 取出系统时间
8.select ename,sal*12 "annul sal"(取的别名） from emp; 查找用户姓名和用户的年薪
9.任何含有空值的数学表达式的值都是空值
select ename,sal*12+comm from emp;
10.select ename||sal from emp 其中的||相当于将sal全部转化为字符串
11.表示字符串的方法
select ename ||'ajjf' from emp;
12.如果其中有一个单引号就用2个单引号来代替他
select ename||'sakj' 'lds'from emp;
13.select distinct deptno from emp (去除部门字段中重复的部分,关键字distinct)
14.select distinct deptno,job from emp;(去除这2个字段中重复的组合)
15.select *from dept where deptno=10; 取出条件(取出部门编号为10的记录)
16.select * from emp where ename='CLIRK'; 取出部门中姓名为clirk的记录(注意取出过程中ename用单引号隔开)
17.select ename,sal from emp where sal>1500; 取出部门中薪水大于1500的人的姓名
18.select ename,sal,deptno from emp where deptno<> 10 取出部门中的部门号不等于10的
19.select ename,sal,deptno from emp where ename>'CBA' 取出部门中员工名字大于CBA的员工(实际比较的是ACIIS码)
20.select ename,sal from emp where sal between 800 and 1500
select ename,sal from emp where sal>=800 and sal<=1500; (取出800和1500之间的数)
21.select ename,sal,comm from emp where comm is null (选出其中的空值)
select enmae,sal,comm from emp where comm is not null(选出其中的非空值)
22.select ename,sal,comm from emp where sal in (800,1500,2000);取出这3者之中的
select ename,sal,comm from emp where ename in('simth');
23.select ename,sal,hiredate from emp where hiredata>'3-04月-81';宣传符合条件的日期
24.select ename,sal,from emp where sal>1000 or deptno=10; 找出工资薪水大于1000或者部门号等于10的员工
25.select ename,sal from emp where sal not in(500,1000); 查找薪水不在500到1000的员工姓名和月薪
26.select ename,sal from emp where ename like '%ALL%';
select ename,sal from emp where ename like '_%A%'; 查找姓名中含有ALL的客户信息，一个横线代表一个通配符
27.select ename,sal from emp where ename like '_%$%%' escape '$'; 自己指定转易字符
select ename,sal from emp where ename like '_%/%%'; 查找中间含有%相匹配的客户信息，运用转易字符
28.select * from dept order by deptno 对表中元素按部门号排序
select *from dept order by deptno desc 默认为升序，可以用desc按降序
29.select ename,sal from emp where sal <>1000 order by sal desc 按照查询条件来查询，并排序（asc升序排列）
30.select ename,sal*12 from emp where ename not like '_%A%' and sal>800 order by sal desc
31.select lower(ename) from emp 将ename都转化为小写 lower是函数能将字母转化为小写
32.select ename from emp where lower(ename) like '_%a%'; 找出ename 中所有的含有a的字符
33.select substr(ename,2,3) form emp 从第2个字符开始截取3个字符
34.select chr(65) from dual; 将65转化为字符
35.select ascii('A') from dual 将ACSII码转化为字符串
36.select round(23.565)from dual 四舍五入
36.select round(23,4565,2)from dual 四舍五入到第二位
37.select to_char(sal,'$99.999.9999') from emp 按指定格式输出
select to_char(sal,'L99,999,9999') form emp L代表本地字符
38.select hiredate from emp
select to_char(hiredate,'YYYY-MM-DD HH:MI:SS) from emp; 时间格式的显示
select to_char(sysdate,'YYYY-MM-DD HH:MI:ss) from dual; 十二小时制显示系统时间
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS) from dual 二四小时制显示系统时间
39.select ename,hiredate from emp where hiredate > to_date('2005-2-3 12:32:23','YYYY-MM-DD HH:MI:SS');
40 select sal from emp where sal>to_number('$1,250.00','$9,999.99'); 取出比它大的一切字符串（把特定格式的数字转化成字符）
41 select ename,sal+nvl(comm,0) from emp; 讲comm值为空的用0来替换，单行函数（以一条记录为条件）一条对一条
42.select Max(sal) from emp;
select Min(sal) from emp;
select avg(sal) from emp;
select sum(sal) from emp;
select count(*) from emp; 查看表中一共有多少条记录
select count(*) from emp where deptno=10; 查找部门10一共有多少人；
43.select avg(sal),deptno from emp group by deptno; 按部门号进行分组
select deptno,job,max(sal) from emp group by job,deptno; 按工作和部门号进行分组；
44.select ename from emp where sal=(select max(sal) from emp); 子查询，查找部门中薪水最高的员工姓名
45.group by 注意：出现在select列表中的字段，如果没有出现在组函数中必须出现在group by子句中
46.select avg(sal),deptno from emp group by deptno having avg(sal)>2000; 选出部门中平均薪水大于2000的部门，
47.select * from emp where sal>100 group by deptno having ..........order by........
先取数据--过滤数据------分组----对分组限制-------排序
48.select avg(sal) from emp where sal>2000 group by deptno having avg(sal)>1500 order by avg(sal) desc;
查找部门中平均薪水打印2000的员工并按部门号进行排序，查询分组后的平均薪水必须大于1500，查询结果按平均薪水从低到高排列
49.select ename from emp where sal>(select avg(sal) from emp);
查找出员工中薪水位于部门平均薪水之上的所有员工
50.select ename,sal from emp join(select max(sal) max_sal from emp group by deptno) t on(emp.sal=t,max_sal and emp.deptno=t.deptno);
查找每个部门中薪水最高的
51.select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno; 表的自连接
52.select dname,ename from emp cross join dept 交叉连接，笛卡尔
SQL99中的新语法
53.select ename,dname from emp join dept on(emp.deptno=dept.deptno);
54.select ename,dname from emp join dept using(deptno); 查找emp和dept表中deptno相同的部分。
55.select ename,dname,grade from emp e join dept d on(e.deptno=d.depno)
join salgrade s(e.sal between s.losal and s.hisal) (三表查找）
where ename not like '_%A%';
56.select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr=e2.deptno); 表的自连接
57.select e1.ename,e2.ename from emp e1 left join emp e2 on(e1.mgr=e2.deptno) 左外表连接
select ename,dname from emp e right join dept d on(e.deptno=d.deptno)右外连接
select ename,dname from emp e full join dept d on(e.deptno=d.deptno)全连接
58.求部门中薪水最高的
select ename,sal from emp join (select max(sal) max_sal, deptno from emp group by deptno) t
on (emp.sal=t.max_sal and emp.deptno=t.deptno);
59.求部门中薪水等级的平均值
select deptno,avg(grade) from(select deptno,ename,grade,from emp join salgrade s on(emp.sal between s.losal and s.hisal))t group by deptno;
60.查找雇员中哪些是经理人
select ename from emp where empno in(select mgr from emp);
61.select distinct e1.sal from emp e1 join emp e2 on(e1.sal<e2.sal); 自连接(不用组函数求出最高薪水）
select distinct sal from emp where not in (select ename from e1.sal from emp e1 join emp e2 on(e1.sal<e2.sal));
62.select deptno from (select avg(sal) max_sal deptno from emp group by deptno) where max_sal=(select max(avg_sal) from (select
avg(sal) avg_sal deptno from emp group by deptno)); 查找部门中部门薪水最大的部门号
63.求平均薪水最大的部门的部门编号
select deptno,avg_sal from(select avg(sal) avg_sal,deptno from emp group by
deptno)where avg_sal=(select max(avg(sal)) from emp group by deptno);
DML语句：更、删、改、查
创建权限， conn sys/admin as sysdba
grant create table,create view to scott;
首先在C:下面建个文件夹备份文件
1.createNewUser方法
1.--backup scott
exp
2.create user(创建用户）用超级管理员模式进入
create user yun identified by kang1234 default tablespace users quota 10M on users;
grant create session,create table,create view to kafei（给kafei这个用户授予权限）
3.import the data(导入备份数据）
imp
2.insert
insert into dept values (50,'game','bj') 插入一条记录
insert into dept2 (deptno,dname) values (78,'games'); 插入指定的几条记录
insert into dept2 select *from dept 插入指定的表（表结构要一样）
rollback; 回退
create table emp2 as select * from emp; 创建数据库表2来备份emp这张表
3.update emp2 set sal=sal*12 where deptno=10; update的用法
4.delete from dept2 where deptno<25 ; 删除语句的用法
DDL语言
1.创建表:create table t(a varchar2(10));
2.drop table t 删除表
3.commit 所有的提交，所有修改都结束了。对于rollback无效，一个事务开始于第1条DML语句
碰到执行DDL DCL语句事务自动提交 对于rollback无效
建表语句
建学生信息表：
create table stu
(id number(6),
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50) unique (唯一约束)
);
非空 唯一 主键 外键 chick
create table stu
(id number(6) primary key,(主键约束)
name varchar2(20) constraint stu_name_nn not null,(非空约束)
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50),
constraint stu_name_uui unique(email,name) 组合性约束
);
主键约束方法二
create table stu
(id number(6),
name varchar2(20) constraint stu_name_nn not null,(非空约束)
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4) references class(id),(参考class 这张表，参考字段)
email varchar2(50),
constraint stu_id_pk primary key(id),
constraint stu_name_uui unique(email,name) 组合性约束
);
外键约束
create table class
(id number(4) primary key,(id为被参考字段,被参考的字段必须是主键)
name varchar2(20) not null
)
create table stu
(
id number(6),
name varchar2(20) constraint stu_name_nn not null,(非空约束)
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4)
email varchar2(50),
constraint stu_class_fk foreign key(class) references class(id),
constraint stu_id_pk primary key(id),
constraint stu_name_uui unique(email,name) 组合性约束
);
像外键中插入关键字，
1.insert into class values(1000,'c1');
2.insert into stu(id,name,class,email) values(1,'a',1000,'a');
3.alter table stu add(addr varchar(20));添加表的结构
4.alter table stu drop(addr); 删除表结构
5.alter table stu modify(addr varchar2(150));修改精度
6.alter table stu drop constraint stu_class_fk; 删除约束条件
7.alter table stu add constraint stu_class_fk forengn key(class) references class(id),添加约束条件
查找当前用户下有哪些表和哪些视图及哪些约束
8.select table_name from user_names
9.select view_name from view_names
10.select constraint_name,table_name from user_constraints;
desc dictionary数据字典表
desc user_tables当前用户下面有多少张表
select table_name from user_tables; 查找当前用户有多少张表
索引：
创建索引
create index idx_stu_email on stu(email);
drop index idx_stu_email;
查找索引
select index_name from user_indexes;
索引读的速度快了，插入速度变慢
view 视图
视图赠加了维护的量
序列：
create table arcticle
(id number,
title varchar2(1024),
cont long
);
序列的创建sequence产生独一无二的序列，而且是oracle独有的
create sequence seq;
select seq.nextval from dual; 查找序列号
insert into arcticle values(seq.nextval,'a','b');往表中插入序列
数据库设计的3范式
第一范式： 设计任何表都要有主键，列不可分
第二范式： 如果有2个主键的话，不能存在部分依赖
第三范式， 不能存在传递依赖
PL-sql
例子1：
SQL> set serveroutput on;
SQL> begin(必要的--程序开始执行)
2 dbms_output.put_line('hello world');
3 end;(结束)
4 /
例子2：
SQL> declare
2 v_name varchar2(20);
3 begin
4 v_name:='myname';
5 dbms_output.put_line(v_name);
6 end;
7 /
myname
例子3：
SQL> declare
2 v_num number:=0;
3 begin
4 v_num:=2/v_num;
5 dbms_output.put_line(v_num);
6 end;
7 /
declare
*
ERROR 位于第 1 行:
ORA-01476: 除数为 0
ORA-06512: 在line 4
例子4：
declare
v_num number:=0;
begin
v_num:=2/v_num;
dbms_output.put_line(v_num);
exception
when others then
dbms_output.put_line('error');
end;
/
变量声明的规则
1.变量名不能够使用保留字，如from,select等
2.第一字符必须是字母。
3.变量名最多包含30个字符
4.不要与数据库的表或者列同名
5.每一行只能声明一个变量
常用变量类型
1. binary_interger,整数，主要用来计数，而不是用来表示字段类型
2. number 数字类型
3. char 定长字符串
4. varchar2 变长字符串
5. date 日期
6.long 长字符串，最长2GB
7.boolean 布尔类型，可以取true false 和null的值
例5：
declare
v_temp number(1);
v_count binary_integer:=0;
v_sal number(7,2):=4000.00;
v_date date:=sysdate;
v_pi constant number(3,2):=3.14;
v_valid boolean:=false;
v_name varchar2(20) not null:='myname';
begin
dbms_output.put_line('v_temp value:'||v_temp);
end;
用--可以注释一行
例6：
declare
v_empno number(4);
v_empno2 emp.empno%type;
v_empno3 v_empno2%type;
begin
dbms_output.put_line('test');
end;
例7
table变量类型
set serveroutput on;
declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
v_empnos type_table_emp_empno;
begin
v_empnos(0):=7369;
v_empnos(2):=7869;
v_empnos(-1):=9999;
dbms_output.put_line(v_empnos(-1));
end;
例8
Record 变量类型
set serveroutput on;
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_temp type_record_dept;
begin
v_temp.deptno:=50;
v_temp.loc:='aaaa';
v_temp.loc:='bj';
dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end;
例9：
使用%rowtype声明record变量（表结构的变化同时也能代理储存过程的变化）
set serveroutput on;
declare
v_temp dept%rowtype;
begin
v_temp.deptno:=50;
v_temp.loc:='aaaa';
v_temp.loc:='bj';
dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end;
例10；
declare
v_name emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_name,v_sal from emp where empno=7369;（将ename和sal的值放在v_name和v_sal里面）
例11：
declare
v_name emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_name,v_sal from emp where empno=7369;
dbms_output.put_line(v_name||' '||v_sal);
end;
dbms_output.put_line(v_name||' '||v_sal);
end;
例12：
declare
v_deptno dept.deptno%type:=50;
v_dname dept.dname%type:='aaaa';
v_loc dept.loc%type:='bj';
begin
insert into dept2 values(v_deptno,v_dname,v_loc);
commit;
end;
例13：
declare
v_deptno emp2.deptno%type:=50;
v_count number;
begin
update emp2 set sal=sal/2 where deptno=v_deptno;
dbms_output.put_line(sql%rowcount ||'条记录被影响');(sql为关键字，代表上一条语句
commit;
end;
/
例14：
declare
v_deptno emp2.deptno%type:=50;
v_count number;
begin
--update emp2 set sal=sal/2 where deptno=v_deptno;
select deptno into v_deptno from emp2 where empno=7369;
dbms_output.put_line(sql%rowcount ||'条记录被影响');(sql为关键字，代表上一条语句
commit;
end;
/
例15
declare
v_deptno emp2.deptno%type:=50;
v_count number;
begin
--update emp2 set sal=sal/2 where deptno=v_deptno;
--select deptno into v_deptno from emp2 where empno=7369;
select count(*) into v_count from emp2; （select必须和into一起使用）
dbms_output.put_line(sql%rowcount ||'条记录被影响');
commit;
end;
/
PL/SQL里面执行DDL语句
begin
execute immediate 'create table T(nnn varchar2(20) default ''aaa'')';
end;
PL/SQL的分支语句：
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp
where empno=7369;
if(v_sal<1200) then
dbms_output.put_line('low');
elsif(v_sal<2000) then
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if;
end;
pL/Sql循环
declare
i binary_integer:=1;
begin
loop
dbms_output.put_line(i);
i:=i+1;
exit when(i>=11);
end loop;
end;
PL/SQL for循环
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
exception 捕获异常
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno=10;
exception
when too_many_rows then
dbms_output.put_line('太多记录了');
when others then
dbms_output.put_line('error');
end;
没有数据错误
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno=2222;
exception
when no_data_found then
dbms_output.put_line('没数据');
when others then
dbms_output.put_line('error');
end;
/
错误处理
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
);
create sequence seq_errorlog_id start with 1 increment by 1;
declare
v_deptno dept.deptno%type:=10;
v_errcode number;
v_errmsg varchar2(1024);
begin
delete from dept where deptno=v_deptno;
exception
when others then
rollback;
v_errcode:=SQLCODE;
v_errmsg:=SQLERRN;
insert into errorlog values(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);
commit;
end;
游标
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp; (取游标的第一个值插入v_emp，在不断的循环)
dbms_output.put_line(v_emp.ename);
close c;
end;
例子
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;
exit when(c%notfound)
dbms_output.put_line(v_emp.ename);
end loop;
close c;
end;
declare
cursor c is
select * 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;
end loop;
close c;
end;
declare
cursor c is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;
带参数的游标
declare
cursor c(v_deptno emp.deptno%type,v_job emp.job%type)
is
select ename,sal from emp where deptno=v_deptno and job=v_job;
begin
for v_temp in c(30,'chick') loop
dbms_output.put_line(v_temp.ename);
end loop;
end;
可更新的游标
存储过程
create or replace procedure p
is
cursor c is
select * from emp2 for update;
begin
for v_emp in c loop
if (v_emp.deptno=10) then
update emp2 set sal=sal+10 where current of c;
elsif(v_emp.deptno=20) then
update emp2 set sal=sal+20 where current of c;
else
update emp2 set sal=sal+50 where current of c;
end if;
end loop;
commit;
end;
exec p执行存储过程
begin
p:
end;
带参数的存储过程
create or replace procedure p
(v_a in number,v_b number,v_ret out number, v_temp in out number)
is
begin
if(v_a>v_b) then
v_ret:=v_a;
else
v_ret:=v_b;
end if;
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 error返回错误信息
删除存储过程
存储过程中的函数
create or replace function sal_tax
(v_sal number)
return number
is
begin
if(v_sal<2000) then
return 0.10;
elsif(v_sal<2750) then
return 0.5;
else
return 0.20;
end if;
end;
/
触发器
create or replace trigger trig
after update on dept
for each row
begin
update emp set deptno=:NEW.deptno where deptno=:OLD.deptno;
end;
/
扫一扫
599
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
