尚学堂马士兵oracle笔记53节完整版

尚学堂马士兵老师oracle笔记 (2008-10-30 10:17:39) 第一课：客户端

1. Sql Plus(客户端），命令行直接输入：sqlplus，然后按提示输入用户名，密码。 2. 从开始程序运行:sqlplus，是图形版的sqlplus. 3. http://localhost:5560/isqlplus

Toad：管理， PlSql Developer:

第二课：更改用户

1. sqlplus sys/密码 as sysdba

2. alter user scott account unlock;(解锁账号) 第三课：table structure

(系统自带的表有emp、salgrade、dept、bonus、dual) 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 \

区别：加双引号保持原大小写，不加全变大写。 任何含有空值的数学表达式结果都为空值。

3. select ename || ‘abcd’

|| 用来连接两个字符串

如果连接字符串中含有单引号，用两个单引号代替一个单引号。

第五课：distinct

select 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');

模糊查询 like ：

%代表任意数量的任意字符 _代表一个任意字符

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

如果要查询含有%的，要用转义字符 \\

转义字符可以自定义： escape '自定义的转义字符' 比如: select ename from emp where ename like '%$a%' escape '$'; 第七课: order by

select * from dept;

默认按升序(asc)排列，要按降序(desc)用如下语句： select * from dept order by dept desc;

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;（用9就可以在没有数字的地方不显示，如果用0的话一定会用0填充满）

select to_char(sal,'L99_999_999') from emp; L:代表本地符号 这个需要掌握牢:

select hiredate from emp; 显示为:

BIRTHDATE ---------------- 17-12月-80 ---------------- 改为:

select to_char(hiredate,'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,hiredate from emp where hiredate > 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为空的地方用0代替，这样可以防止comm为空时,sal*12相加也为空的情况.

第九课: Group function 组函数（即从多行中得到一个输出） 牢记组函数：max(), min(), avg(), sum(), count()

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语句

注意：count() 是计数不是空值的数量

需求:现在想求每个部门的平均薪水. 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 avg(sal) 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, salgrade s

where e.deptno = d.deptno and e.sal 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); PS：所谓的“外”连接，即把多余的数据显示出来。Outer关键字可以省略

16-23 求部门中哪些人的薪水最高： 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)

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

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) ) )

组函数嵌套

如：平均薪水最高的部门编号，可以用更简单的方法如下： 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)

组函数最多嵌套两层

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

分析： 首先求

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 ,t1.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 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.hisal)) )

结果如下：

DNAME DEPTNO GRADE AVG_SAL -------- ------- -------- -------- SALES 30 3 1566.6667

H: 视图（视图就是一张表，一个子查询）

G中语句有重复，可以用视图来简化。

【默认scott账户没有创建视图的权限，可通过如下语句授权： 首先登陆超级管理员：conn sys/bjsxt as sysdba; 授权：grant create table,create view to scott;

再以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)

然后G中查询可以简化成：

select dname,t1.deptno,grade,avg_sal from v$_dept_avg-sal_info t1

join dept on (t1.deptno =dept.deptno) where t1.grade =

(select min(grade) from v$_dept_avg-sal_info t1)

24、-------求比普通员工最高薪水还要高的经理人的名称 ------- select ename, sal 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)

)

25、---面试题：比较效率（理论上前一句效率高，但实际上可能Oracle可能会自动对代码优化，所以不见得后一句就会慢）

select * from emp where deptno = 10 and ename like '%A%'; //效率高，因为将过滤

力度大的放在前面

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

-------------------------------------------以上为select 语句的内容-----------------------------------------

----------------创建新用户--------------- 1、backup scott//备份 exp//导出 2、create user create user guohailong identified(认证) by guohailong default tablespace users quota(配额) 10M on users grant create session(给它登录到服务器的权限),create table, create view to guohailong 3、import data Imp

25、----------取消操作--------

rollback

-----------事务确认语句-------- commit;//此时再执行rollback无效

当正常断开连接的时候例如exit,事务自动提交。 当非正常断开连接，例如直接关闭dos窗口或关机，事务自动提交

-----表的备份

create table dept2 as select * from dept;

-----插入数据

insert into dept2 values(50,'game','beijing'); ----只对某个字段插入数据 insert into dept2(deptno,dname) values(60,'game2');

-----将一个表中的数据完全插入另一个表中（表结构必须一样） insert into dept2 select * from dept;

-----求前五名员工的编号和名称（使用伪字段rownum 只能使用 < 或 = 要使用 > 必须使用子查询） select empno,ename from emp where rownum <= 5;

----求10名雇员以后的雇员名称-------- select ename from (select rownum r,ename from emp) where r > 10;

----求薪水最高的前5个人的薪水和名字--------- select ename, sal from (select ename, sal from emp order by sal desc) where rownum <=5;

----求按薪水倒序排列后的第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

-----面试题： 有3个表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 (sc.cno = c.cno) where cteacher <> '黎明'; 2、 select sname 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 cno in (select distinct sno from sc where cno = 2); )

27、--------------创建表-------------- create table stu (

id number(6),

name varchar2(20) constraint stu_name_mm not null, sex number(1), age number(3), sdate date,

grade number(2) default 1, class number(4),

email varchar2(50) unique );

28、五种约束条件：非空约束、唯一约束、主键约束、外键约束、检查约束（check）

--------------给name字段加入 非空 约束，并给约束一个名字，若不取，系统默认取一个

-------------

create table stu (

id number(6),

name varchar2(20) constraint stu_name_mm not null, sex number(1), age number(3), sdate date,

grade number(2) default 1, class number(4), email varchar2(50) );

--------------给nameemail字段加入 唯一 约束 两个 null值 不为重复------------- create table stu (

id number(6),

name varchar2(20) constraint stu_name_mm not null, sex number(1), age number(3), sdate date,

grade number(2) default 1, class number(4),

email varchar2(50) unique );

--------------两个字段的组合不能重复 约束：表级约束------------- create table stu (

id number(6),

name varchar2(20) constraint stu_name_mm not null, sex number(1), age number(3), sdate date,

grade number(2) default 1, class number(4), email varchar2(50)， constraint stu_name_email_uni unique(email, name) );

29、--------------主键约束------------- create table stu (

id number(6),

name varchar2(20) constraint stu_name_mm 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_name_email_uni unique(email, name) );

--------------外键约束 被参考字段必须是主键 ------------- create table stu (

id number(6),

name varchar2(20) constraint stu_name_mm 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_class_fk foreign key (class) references class(id), constraint stu_id_pk primary key (id), constraint stu_name_email_uni unique(email, name) ); 红色为字段约束的写法，蓝色为表级约束的写法 create table class ( id number(4) primary key, name varchar2(20) not null );

31、修改表结构：

---------------添加字段-------------------------- alter table stu add(addr varchar2(29));

---------------删除字段-------------------------- alter table stu drop (addr);

---------------修改表字段的长度------------------ alter table stu modify (addr varchar2(50));//更改后的长度必须要能容纳原先的数据

----------------删除约束条件---------------- alter table stu drop constraint 约束名

-----------修改表结构添加约束条件--------------- alter table stu add constraint stu_class_fk foreign key (class) references class (id);

32、---------------数据字典表（有user_tables、user_views、user_constraints等等）----------------

---------------查看当前用户下面所有的表、视图、约束-----数据字典表user_tables--- select table_name from user_tables; select view_name from user_views; select constraint_name from user_constraints; 存储数据字典表的信息的表：dictionary; //该表共有两个字段 table_name comments //table_name主要存放数据字典表的名字 //comments主要是对这张数据字典表的描述

33、-------------索引(能优化查询效率)------------------

create index idx_stu_email on stu (email);// 在stu这张表的email字段上建立一个索引：idx_stu_email

---------- 删除索引 ------------------ drop index index_stu_email;

---------查看所有的索引----------------

select index_name from user_indexes; ---------创建视图------------------- create view v$stu as selesct id,name,age from stu; 视图的作用: 简化查询，保护我们的一些隐私数据，通过视图也可以用来更新数据，但是我们一般不这么用

缺点：要对视图进行维护

34、-----------创建序列（sequence，oracle特有的东西，一般用来做主键）------------ create sequence seq;//创建序列 select seq.nextval from dual;// 查看seq序列的下一个值 drop sequence seq;//删除序列

35、------------数据库的三范式-------------- （1）、要有主键，列不可分 （2）、不能存在部分依赖：当有多个字段联合起来作为主键的时候，不是主键的字段不能部分依赖于主键中的某个字段 （3）、不能存在传递依赖

=======================PL_SQL(过程化SQL语言)==========================

38、-------------------在客户端输出helloworld------------------------------- set serveroutput on;//默认是off，设成on是让Oracle可以在客户端输出数据

begin dbms_output.put_line('helloworld'); end; /

----------------pl/sql变量的赋值与输出---- declare v_name varchar2(20);//声明变量v_name变量的声明以v_开头 begin v_name := 'myname'; dbms_output.put_line(v_name); end; /

39、-----------pl/sql对于异常的处理(除数为0)------------- 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; /

40、----------变量的声明---------- binary_integer:整数，主要用来计数而不是用来表示字段类型 比number效率高 number:数字类型 char:定长字符串 varchar2：变长字符串 date：日期 long：字符串，最长2GB boolean：布尔类型，可以取值true，false，null//最好给一初值

----------变量的声明，使用 '%type'属性--------- declare v_empno number(4); v_empno2 emp.empno%type; v_empno3 v_empno2%type; begin dbms_output.put_line('Test'); end; / //使用%type属性，可以使变量的声明根据表字段的类型自动变换，省去了维护的麻烦，而且%type属性，可以用于变量身上 41、组合变量：

---------------Table变量类型(相当于java里面的数组)------------------------------------------- declare type type_table_emp_empno is table of emp.empno%type index by binary_integer; v_empnos type_table type_table_empno; begin v_empnos(0) := 7345; v_empnos(-1) :=9999; dbms_output.put_line(v_empnos(-1)); end; 42、-----------------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:='aaaa'; v_temp.loc:='bj';

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

-----------使用 %rowtype声明record变量，直接参照表来声明record------------------- declare v_temp dept%rowtype; begin v_temp.deptno:=50; v_temp.dname:='aaaa'; v_temp.loc:='bj'; dbms_output.put_line(v temp.deptno || '' || v temp.dname) end;

43、 --------------select语句的运用(必须保证select语句有相应的返回记录)------------------- 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;

---------------------------select语句的应用（record）---------------------------------------- declare v_emp emp%rowtype; begin select * into v_emp from emp where empno=7369; dbms_output_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; ------------- update 语句的应用------------------------------ 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 || ‘条记录被影响’); commit; end;

注：sql%rowcount 统计上一条sql语句更新的记录条数

44、-----------------ddl语言，数据定义语言----------------------- begin execute immediate 'create table T (nnn varchar(30) default ''a'')'; end;

------------------if else语句-------------------------------------- declare v_sal emp.sal%type; begin select sal into v_sal from emp where empno = 7369; if(v_sal < 2000) then dbms_output.put_line('low'); elsif(v_sal > 2000) then dbms_output.put_line('middle'); else dbms_output.put_line('height'); end if; end;

45、-------------------do while循环 --------------------------- declare i binary_integer := 1; begin loop dbms_output.put_line(i); i := i + 1; exit when (i>=11); end loop; end;

---------------------while 循环 --------------------------- declare j binary_integer := 1; begin while j < 11 loop dbms_output.put_line(j); j:=j+1; end loop; end;

---------------------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;

46、-----------------------异常(1) --------------------------- 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;

-----------------------异常(2) --------------------------- 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 sequence seq_errorlog_id start with 1 increment by 1;

创建日志表: create table errorlog ( id number primary key, errcode number, errmsg varchar2(1024), errdate date ); 示例程序： 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;

47、---------------------PL/SQL中的重点cursor(游标)和指针的概念差不多---------------------- declare cursor c is

select * from emp; //此处的语句不会立刻执行，而是当下面的open c的时候，

才会真正去数据库中取数据 v_emp c%rowtype; begin open c; fetch c into v_emp;

dbms_output.put_line(v_emp.ename); //这样会只输出一条数据 134将使用循环的方法输出每一条记录 close c; end;

----------------------使用do while 循环遍历游标中的每一个数据--------------------- declare cursor c is select * from emp; v_emp c%rowtype; begin open c; loop fetch c into v_emp;

(1) exit when (c%notfound); //notfound是oracle中的关键字，作用是判断是

否还有下一条数据

(2) dbms_output.put_line(v_emp.ename); //(1)(2)的顺序不能颠倒，否则会把

最后一条结果再多打印一次。 end loop; close c; 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 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;

49、------------------------------store procedure存储过程(带有名字的程序块) ------------------- create or replace procedure p is--除了这两句替代declare，下面的语句全部都一样 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; else if(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; 执行存储过程的两种方法： （1）exec p;(p是存储过程的名称) （2）begin p; end; /

-------------------------------带参数的存储过程 先创建存储过程：（in标识传入参数，out标识传出参数，默认为传入参数） 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;

------------------删除存储过程--------------------------- drop procedure p;

50、------------------------创建函数计算个人所得税的税率------------------------------------- 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;

-----------------------------创建触发器（trigger） 触发器不能单独的存在，必须依附在某一张表上

写 主语 谓语 宾语 游戏 创建触发器的依附表： create table emp2_log ( ename varchar2(30) , eaction varchar2(20), etime date ); create or replace trigger trig

after insert or delete or update 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;

51、--------------------触发器用法之一：通过触发器更新约束的相关数据------------------- create or replace trigger trig after update on dept for each row begin update emp set deptno =:NEW.deptno where deptno =: OLD.deptno; end; //////只编译不显示的解决办法 set serveroutput on; 52、-------------------------------通过创建存储过程完成递归 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 0..v_leave loop v_preStr := v_preStr || '****'; end loop; for v_article in c loop dbms_output.put_line(v_article.cont); if(v_article.isleaf = 0) then p(v_article.id); end if; end loop; end;

-------------------------------查看当前用户下有哪些表--- 首先，用这个用户登录然后使用语句： select * from tab;

-----------------------------用Oracle进行分页！-------------- 因为Oracle中的隐含字段rownum不支持'>'所以： select * from ( select rownum rn, t.* from ( select * from t_user where user_id <> 'root'

) t where rownum <6 ) where rn >3

------------------------Oracle下面的清屏命令---------------- clear screen; 或者 cle scr;

-----------将创建好的guohailong的这个用户的密码改为abc-------------- alter user guohailong identified by abc 当密码使用的是数字的时候可能会不行

本文来源：https://www.bwwdw.com/article/gi0x.html