第一课:客户端
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
)
求比普通员工最高薪水还要高的经理人的名称
先求普通员工的最高薪水
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;
(转载)尚学堂oracle笔记
最新推荐文章于 2022-02-25 18:19:43 发布