数据库## oracle的数据类型有：
（1）number
number表示数字类型。经常被定义成number（P，S）形式，其中：
P：表示数字的总位数
S：表示小数点后面的位数
eg:
sal number(6,2)
表示sal列中的数据，整数位最大为4位，小数位最大为2位，也就是最大取值：9999.99
（2）char
表示固定长度的字符类型。经常被定义成CHAR（N）形式，N表示占用的字节数。最大长度是2000字节。
eg：
ename char(20)
表示ename列中最多可储存20个字节的字符串，并且占用的空间是固定的20个字节。
（3）varchar2（Oracle特有的数据类型）
表示变长的字符类型。定义格式是VARCHAR2（N），N表示最多可占用的字节数。
最大长度是4000字节。
eg:
job varchar(100)
表示job列中最多可存储长度为100个字节的字符串。根据其中保存的数据长度，占用的空间是变化的，最大占用空间为100个字节。
（4）date
用于定义日期时间的数据。长度是7个字节，默认格式是：DD-MON-RR，例如：11-APR-17
eg:
hiredate DATE
表示hiredate列中存放的是日期数据
第一：表空间_用户_权限.~SQL
SQL：结构化查询语言(脚本语言)
DDL:数据定义语言（create 创建，alter 更改， drop 删除、truncate删除不能回滚）—应用对象是表
DML:数据操作语言（insert 增，delete 删，update 改, select 查) --应用对象是表里面的内容
DCL:数据控制语言 (grant 授予，revoke 回收)
TCL:事务控制语言 (commit 提交 rollback 回滚)
DQL:数据查询语言(order by 排序，group by 分组)
–创建表空间表空间表：DBA_DATA_FILES
create tablespace 表空间名 datafile '文件地址.dbf' size 大小
autoextend on next 大小 maxsize unlimited
删除表空间(不删除数据文件，需要手动删除)
drop tablespace abc ;
删除表空间(删除数据文件)
drop tablespace 表空间名including contents and datafiles ；
创建用户锁定状态
用户信息表：DBA_USERS
create user 用户名 identified by 密码 account lock
default tablespace 表空间名;
defualt 默认
-修改用户密码
alter user 用户名 identified by 密码;
–解锁用户–
alter user 用户名 account unlock;
授予登录权限
grant create session to test12;
grant dba to joker;
--connect角色主要应用在临时用户
--Resource更可靠和正式的数据库用户
-- DBA role拥有所有的系统权限
--grant 权限 or 角色 to joker;
回收权限
revoke dba from 用户名
查看当前用户
select user from dual;
删除用户
drop user 用户名 cascade;
drop user 用户名； 仅仅是删除用户，
drop user ×× cascade ；会删除此用户名下的所有表和视图
drop user xx including contents and datafiles 删除用户名(会删除此用户名下的所有表和视图)
第二：建_写_改_删表_.~SQL
创建表
create table 表名(字段名 数据类型,stname varchar2(10) not null ,stsex char(3),stdate date
stgrade varchar(5) default '一年级');
查询当前用户下的所有表
表：user_tables
select * user_tables
查看表结构
表：user_tab_cols
select * from user_tab_cols
复制表结构以及数据（不会复制约束）
create table emp_form as select * from emp;
复制部分数据
create table 表1 as select 字段1, 字段2 from 表2；
复制数据
insert into 表1(select * from 表2)
insert into 表1(字段1，字段2) (select 字段1，字段2 from 表2)
修改表名
alter table TEST rename to TEST1;
查看创建表的列
表：user_tab_columns
select * from user_tab_columns where table_name = 'STUDENT';
给表添加备注comment
comment on table 表名 is '学生信息表';
给表的字段添加备注（column）
comment on column 表名.字段名 is '学生编号';
改变表结构（添加列）
alter table student add (stwy number(11));
修改列名(column)
alter table student rename column stwy to st_class;
修改表结构 (字段) modify
alter table student modify (stsex char(4),+);
删除表结构（字段）
alter table abc drop (b,+);
alter table abc drop column a;
删除表数据内容，能回滚
delete from 表名 where 条件;
删除表数据内容，不能回滚
truncate table emp_form;
表(慎用)
drop table emp_form;
注： 新建用户TEST,并赋予权限Resource和connect，并用test用户进行下列操作
管理员用户建的表，不能删除
第三：约束_序列.SQL
约束类型
主键约束 :Primary Key Constraint 注：要求主键列数据唯一，并且不允许为空,不重复
唯一约束 :Unique Constraint 注：要求该列唯一，允许为空
检查约束 :Check Constraint 注：某列取值范围限制、格式限制等，如有关年龄的约束
外键约束 :Foreign Key Constraint 注：用于两表间建立关系，需要指定引用主表的那列，建立
非空约束：Not Null 注：字段的值不能为空
默认值约束：Default 注：用于给数据表中的字段指定默认值，即当在表中插入一条新记录时若未给该字段赋值，那么，数据库系统会自动为这个字段插人默认值；
添加约束
通过字典表查看约束 表：user_constraints
select * from user_constraints where table_name ='STUDENT';
主键约束
（主键列值必须唯一，不能为空）
语法：
alter table 表名 add constraint PK_名 primary key (需约束字段名)；
alter table grade add constraint pk_a primary key (stno);
select * from test1;
delete from test1;
alter table test1 add constraint PK_sal primary key (sal);
select * from user_constraints;
唯一约束：
唯一键列值必须唯一，允许为空
–语法 alter table 表名 add constraint UK_名 unique (需约束字段名);
alter table student add constraint uq_stwy unique (stwy);
alter table test1 add constraint UK_job unique (job);
检查约束：
–语法 alter table 表名 add constraint CK_名 check (约束字段名+约束条件)
alter table student add constraint ck_stsex check (stsex='男' or stsex ='女');
alter table student add constraint ck_stage check (stage between 10 and 20);
alter table test1 add constraint ck_comm check (comm='男' or comm ='女' or comm = '未知');
外键约束：
建立表跟表之间的关系，需要明确指定引用主表那一列：(被引用的列必须为主键，或者唯一键)
–语法：alter table 表名 add constraint FK_名 foreign key (需约束字段名) references 引入外表(外表约束)
alter table student add constraint fk_stno foreign key (stno) references grade(stno) ;
非空约束:
指的是字段的值不能为空 字段名 数据类型 NOT NULL;(一般在建表的时候用)
–语法： 字段名 数据类型 NOT NULL;
create table student02
(id int,
name varchar(20) not null);
默认值约束：
DEFAULT用于给数据表中的字段指定默认值
–语法： 字段名 数据类型 DEFAULT 默认值；
create table student03
(id int,
name varchar(20),
gender varchar(10) default 'male');
删除约束
–语法：alter table 表名 drop constraint 约束名
alter table student drop constraint ck_stage;
拓展：–序列
*–序列*：生成唯一的值，自动增长
*–创建默认的序列*
create sequence xulie;
*–查看当前的序列*
select xulie.currval from dual;
*–查询下一个序列*
select xulie.nextval from dual;
*–创建指定参数的序列*
create sequence xu_l
start with 10 --开始值
maxvalue 10000 --最大值
increment by 5 --增量值
cache 50;--缓存值(预先存五十个值)
*–删除序列*
drop sequence xu_l;
*–插入语法*
insert into 表名 values (列值1,列值2....); (tno number default 1) 默认值
*–修改语法*
update 表名 set 列名=新的值 where 条件
第四：基础查询.SQL
基础查询
–语法:
select * from 表 where 条件 order by 排序；
–别名和连接符
–算数运算符：+，-，*，/
–查询所有员工的转正日期。（入职日期+90天）
–查询员工的工作年限. 函数：trunc sysdate
–比较运算符：<, >, =, <=, >=, <> , !=
–查询入职年限大于20年的员工信息；
–删除奖金低于100元的员工信息；
–将薪资大于3000的员工奖金设置为1500；
–逻辑运算符：and , or , not
–between and 关键查询
–例：查询薪资在1000到3000之间（包括边界）的员工信息；
–例：将部门编号为30且入职日期位1981年2月20日的员工薪资改为1100； 时间函数：to_char(hiredate,‘yyyy-mm-dd’)
附加内容：
1.使用LIMIT限制查询结果的数量
当执行查询数据时可能会返回很多条记录，而用户需要的数据可能只是其中的一条或者几条
select * from student order by age asc limit 3;
2.使用GROUP BY进行分组查询
GROUP BY 子句可像切蛋糕一样将表中的数据进行分组，再进行查询等操作。换言之，可通俗地理解为：通过GROUP BY将原来的表拆分成了几张小表。
- 1 GROUP BY和聚合函数一起使用
select count(*), departmentnumber from employee group by departmentnumber;
2.2 GROUP BY和聚合函数以及HAVING一起使用
select sum(salary),departmentnumber from employee group by departmentnumber having sum(salary)>8000;
3.使用ORDER BY对查询结果排序
从表中査询出来的数据可能是无序的或者其排列顺序不是我们期望的。为此，我们可以使用ORDER BY对查询结果进行排序
其语法格式如下所示：
SELECT 字段名1,字段名2,…
FROM 表名
ORDER BY 字段名1 [ASC 丨 DESC],字段名2 [ASC | DESC];
在该语法中：字段名1、字段名2是查询结果排序的依据；参数 ASC表示按照升序排序，DESC表示按照降序排序；默认情况下，按照ASC方式排序。通常情况下，ORDER BY子句位于整个SELECT语句的末尾。
select * from student order by age asc;--升序
select * from student order by age desc;--降序
第五：关联_子查询_.SQL
内连接（等值连接、不等值连接、自连接）
–语法1（等值连接）：显示多表均满足关联条件的数据
select * from 表1 inner join 表2 on 表1.字段 = 表2.字段;
–语法2（等值连接）：
select * from 表1,表2 where 表1.字段 = 表2.字段;
范围连接（不等值连接）inner jion
–查询所有员工信息，以及薪资等级信息
select *from emp t1 inner join salgrade t2 on t1.sal between t2.losal and t2.hisal;
自然连接(找到共同的列名，如果未找到就会出现笛卡尔积）natural jion
–语法：
select * from 表1 natural join 表2;
select * from emp t1 natural join dept t2 ;
外连接(左连接，右连接，全连接，自连接):
*–左连接(left)* ：查询出左边表所有的数据，如果右边表没有相关联的数据，就会空着
select * from dept left join emp on dept.deptno = emp.deptno;
select * from dept,emp where dept.deptno = emp.deptno(+);
*-**-右连接(right)* ：查询出右边表所有的数据，如果左边表没有相关联的数据，就会空着
select * from dept right join emp on dept.deptno = emp.deptno;
select * from dept,emp where dept.deptno(+) = emp.deptno;
*–全连接(full) ：查询出左右表所有的数据*
select * from dept full join emp on dept.deptno = emp.deptno;
*–自连接：把本身看成多张表，自己跟自己进行连接*
–查询员工编号，姓名，上级的编号，上级的姓名
select t1.empno,t1.ename,t1.mgr,t2.ename from emp t1,emp t2 where t1.mgr = t2.empno;
*–交叉连接*(别名：笛卡尔积)
select * from emp cross join dept;
子查询：（不相关联子查询、相关联子查询）
在查询里面嵌套的查询
*–不相关联子查询*
select * from emp where deptno =(select deptno from dept where dname = 'SALES');
select * from emp where deptno = (select deptno from emp where ename = 'SCOTT') and ename <> 'SCOTT';
*–相关联子查询*
select empno,ename,deptno,(select dname from dept where deptno = t1.deptno) from emp t1;
--exists:判断子查询是否返回结果，有：TRUE 没有：FALSE
****查询出不存在员工的部门信息
select * from dept where exists (select 1 随意写 from emp where deptno = dept.deptno);
带EXISTS关键字的子查询
EXISTS关键字后面的参数可以是任意一个子查询， 它不产生任何数据只返回TRUE或FALSE。当返回值为TRUE时外层查询才会 执行
–例：
select * from class where exists (select * from student where sname='王五');
–总结
重要（从关键字分析）：
查询语句的书写顺序和执行顺序
select ===> from ===> where ===> group by ===> having ===> order by ===> limit
查询语句的执行顺序
from ===> where ===> group by ===> having ===> select ===> order by ===> limi
联合查询（并集，交集，补集）
*–并集：*
--union all :把两个查询结果连接到一起，会有重复记录
--union ：把两个查询结果连接到一起，重复记录会删掉
*–交集：*
--intersect：把两个查询结果相交的显示出来
*–补集：*
minus:减去查询结果B的数据，并且减去A跟B相交的结果，显示剩余的结果
–例：
select * from emp
union all
select * from emp_form;
模糊查询 :
“_”:写几个 ，代表空几位（位置）
“%”：模糊数据
[ ]：在某范围内的字符
[^]：不在某范围内的字符
–in（一列数据）,>all(),>any()
第六：分页查询_数据备份.SQL
分页查询–
--伪列 (每个表都有，但不能修改，只能用于查询)
-- rowid：每一行数据的地址，自动生成，且不会重复
-- rownum：单表查询结果，数据行的序号，自动生成，且不会重复
--查询出第一个的员工信息
select t1.*,rownum from emp t1 where rownum =1;
--查询第三个，第四个员工信息
select t2.* from(select t1.*,rownum r from emp t1 ) t2
where r =3 or r =4;
--根据员工薪资排序,查询第三个，第四个员工信息
select t3.*from (select t2.*, rownum r from (select t1.* from emp t1 order by sal desc) t2) t3
where r between 3 and 4;
--例：查询emp表中薪资排名前五名的员工信息
--例：查询emp表中薪资排名第三的员工信息
--例：查询emp表中薪资排名第1到第五名的员工信息
--备份
create user bak identified by 123456;
grant Resource to bak;
revoke dba from bak;
命令方式：
CMD exp 导出用户/密码@数据库名称 file = "导出地址+文件名.dmp"
CMD imp 导入用户/密码@数据库名称 fromuser = "导出用户名" touser = "导入用户名" file = "D:/scott.dmp"
--用工具导入导出
tools----export tables-----import tables
--工具导出SQL文件
export query resules..
第七：函数.SQL
单行函数：
针对每一行都会返回一个结果
1、字符函数
2、数字函数
3、日期函数
4、转换函数
5、通用函数
–1、字符函数
–1、1字符串大小写转换函数
--initCap(待转换的字符串) ：将字符串首字母转换成大写，其余都转换成小写
select initCap(ename) from emp;
select * from emp;
--lower（待转换的字符串） ：将参数里面的字符串，转换成小写
select lower(ename) from emp;
select * from emp where lower(ename)='scott';
--upper(待转换的字符串) : 将参数里面的字符串，转换成大写
select upper(ename) from emp;
–1、2字符串控制函数
--concat(字符串1,字符串2) ：将字符串1和字符串2连接到一起获得一个新的字符串
select '姓名：'||ename||'薪资：'||sal from emp;
select '姓名：'||ename||'薪资：'||sal as name_sal from emp;
select e.ename,e.sal from emp e;
select concat('姓名：',ename) from emp;
select concat(concat('姓名：',ename),'薪资：') from emp;
select concat(concat(concat('姓名：',ename),'薪资：'),sal) from emp;
select concat(concat(concat(concat('姓名：',ename),'薪资：'),sal),'元') from emp;
--substr(列名（类型字符串），开始位置，保留数量)：拆分字符串
select substr(ename,1,3) from emp;
select ename from emp;
--lpad(列名（类型字符串），整体补齐的位数，不够位数的用指定的字符补齐)：左补齐
select lpad(ename,8,'Z') from emp;
--rpad(列名（类型字符串），整体补齐的位数，不够位数的用指定的字符补齐)：右补齐
select rpad(ename,8,'z') from emp;
--instr(字符串，查找字符，开始位置(默认为1，可以不写))：返回该字符在字符串中的第一个出现的位置
select instr(ename,'S',3) from emp;
select ename from emp where empno = 7369;
select instr(ename,'T') from emp where empno = 7369;
--length：返回字符串的长度
select length(ename) from emp where empno = 7369;
–2、数字函数
insert into emp values(7935,'JOKER','MANAGER',NULL,to_date('1990-10-10','yyyy-mm-dd'),1500.45,null,20);
insert into emp values(7936,'SHERRY','MANAGER',NULL,to_date('1990-8-15','yyyy-mm-dd'),1100.43,null,20);
select * from emp;
--ceil(待向上取整的值)：比数大，且距离最近的整数
select ceil(sal) from emp where empno = 7935;
--floor(待向下取整的值)：比数小，且距离最近的整数
select floor(sal) from emp where empno = 7935;
--mod(值1，值2)：% 取余
select mod(sal,200) from emp where empno = 7936;
--round(待四舍五入的值，保留小数点的位数)：
select round(sal,1) from emp where empno in(7935,7936);
--trunc(待阶段的值，保留小数位)：不会四舍五入
select trunc(sal,1) from emp where empno = 7935;
–3、日期函数
--add_months(待增加的日期，要增加的月份数)：把增加月份数后的日期返回
select add_months(sysdate,1) from dual;
select sysdate from dual;
select * from emp where empno ='7935';
select add_months(hiredate,2) from emp where empno = 7935;
--next_day(指定的日期，星期几)：返回指定日期的下一个星期几
select next_day(sysdate,'星期二') from dual;
--last_day(指定的日期)：返回指定日期所在月份的最后一天
select last_day(sysdate) from dual;
–trunc 用法
--trunc用法：
--trunc(指定的日期)：截断时分秒，返回年月日
select trunc(sysdate) from dual;
--trunc(指定的日期，'yyyy')：返回指定日期所在年份的第一天
select trunc(sysdate,'yyyy') from dual;
--trunc(指定的日期，'mm')：返回指定日期所在月份的第一天
select trunc(sysdate,'mm') from dual;
--trunc(指定的日期，'day')：返回指定日期所在星期的第一天
select trunc(sysdate,'day') from dual;
trunc(指定的日期)：截断时分秒，返回年月日
select trunc(sysdate) from dual;
–round 用法
--round用法：
--round(指定的日期)：当天时间已过12小时，四舍五入到下一天，舍弃时分秒
select round(sysdate) from dual;
--trunc(指定的日期，'yyyy')：当年时间已过6个月，四舍五入到下一年初，舍弃时分秒
select round(sysdate,'yyyy') from dual;
--trunc(指定的日期，'mm')：当月时间已过一半（例如15天），四舍五入到下一月初，舍弃时分秒
select round(sysdate,'mm') from dual;
--trunc(指定的日期，'day')：当前星期已过一半，四舍五入到下一星期开始，舍弃时分秒
select round(sysdate,'day') from dual;
--round（列名，保留几位小数）
--例：round(stco,2) 在emp表中的学生成绩那一列保留两位小数
–4、转换函数
year：y表示年最后一位，yy表示年最后两位，yyy表示年最后三位，yyyy四位表示年
m、onth：mm表示月 --MySQL用MM表示
day:dd表示当月第几天，ddd表示当年第几天，dy表示当周第几天
hour:hh表示12小时制，hh24表示24小时 --MySQL 用HH表示
minute:mi表示分钟 --MySQL 用mm表示
second:ss表示秒
--to_date(日期的字符串，转换格式)
select * from emp where hiredate = to_date('1981-02-20','yyyy-mm-dd');
select to_date('2018-10-11 16:51:52','yyyy-mm-dd hh24:mi:ss') from dual;
--to_char(日期的字符串，截取时间)
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm') from dual;
–5、通用函数
--nvl(查询值，补全值)：如果查询值没有数据，则显示补全值
select nvl(mgr,1),t1.* from emp t1;
--decode(列名，判断1,输出1，判断2,输出2···)
select * from emp;
select ename,decode(comm,300,'你好',500,'你很好',null,'不好') from emp;
多行函数：
接收多个输入值，返回一个输出值
1、聚合函数
2、分组函数/分析函数
–1、聚合函数
--avg(值)：平均值
select avg(sal) from emp;
select * from emp;
--min(值)：最小值
select min(sal),min(hiredate),min(ename) from empbak;
--max(值)：最大值
select max(sal),max(hiredate),max(ename) from emp;
--sum(值)：求和
select sum(sal) from emp;
--count(值)：统计有数值的数量（理解为统计行数，如果没有数据则不统计）
select count(*) from emp where deptno = '20';
–group by与聚合函数的运用
--group by 分组
--这两种要求都与SQL语句的执行优先级有关：from > on > join > where > group by > having > select > distinct > order by
--求每个部门的人数，平均薪资，最大薪资，最小薪资，薪资总和
select deptno,count(1),trunc(avg(sal)),max(sal),min(sal),sum(sal) from emp
group by deptno having deptno < 30 order by deptno asc;
--having 分组条件 类似于where，不过只能用在group by 里面
--筛选出最小薪资大于1000元的数据的部门
select deptno from emp group by deptno having min(sal)>1000;
select * from emp where deptno =10;
–练习题:
–例：查询所有员工的人数和平均薪资
select avg(sal),count(1) from emp;
–例：查询各部门的员工人数和平均薪资 函数：trunc
select trunc(avg(sal)),count(1) from emp group by deptno ;
–例：查询各部门薪资高于1000的员工人数和平均薪资
select avg(sal),count(1) from (select * from emp where sal > 1000) group by deptno ;
–例：查询各部门薪资高于1000的员工人数和平均薪资，在此基础上只查看平均薪资高于3000的部门
select * from (select avg(sal) s,count(1) from (select * from emp where sal > 1000) group by deptno ) t where t.s > 3000;
–2、分组函数/分析函
数 –
–语法：
--row_number() over() ：默认从第一行到最后一行排名
--row_number() over(order by 排序)：根据某一列排序，然后排名
--row_number() over(partition by 分组 order by 排序)：根据某个值分组，然后某一列排序，然后排名
--row_number：排名（值重复，会选择一个排名靠前，一个靠后，然后依次排名）
--rank：排名（值重复，排名并列，然后跳过原有排名，总排名数不变）
--dense_rank：排名（值重复，排名并列，不跳过原有排名，依次排名）
select row_number() over(partition by deptno order by sal desc),
rank() over(partition by deptno order by sal desc),
dense_rank() over(partition by deptno order by sal desc),
t1.
from emp t1 ;
--例：将每个部门员工按照薪资降序排序,展示部门编号、员工姓名、薪资、序号；
select * from emp;
select deptno,ename,sal,
row_number() over(partition by deptno order by sal desc)
from emp;
–例：将每个部门员工按照薪资降序排序，展示部门编号、员工姓名、薪资、平均薪资
select t.s,e.deptno,e.ename,e.sal,
row_number() over( partition by e.deptno order by e.sal desc)
from emp e,(select avg(sal) s,deptno from emp group by deptno) t
where e.deptno = t.deptno;
–去重：distinct
–例：查询emp表中不重复的部门；
select distinct deptno from emp;
–例：查询emp表中不完全重复的数据
select distinct * from emp;
第八：plsql 循环、判断：
–PL/SQL:是sql语言的拓展，过程化sql语言
PL（procedual language）:过程语言
SQL：结构化查询语言
分为三个部分：声明部分、可执行部分、异常处理部分
语法：
declare
声明部分：声明变量、常量、类型、游标
begin
执行部分：存储过程以及sql语句（逻辑代码）
exception（可选）
异常部分：处理异常
end;
–参数的声明方法
--方法一：
declare
v_ename varchar2(10);
v_sal number(7,2);
begin
select ename ,sal into v_ename,v_sal from emp where empno=&empno;
dbms_output.put_line('员工姓名：'|| v_ename || '员工薪资:' || v_sal);
if (v_sal between 1000 and 2000)then
update emp set comm = nvl (comm,0) +200 where ename =v_ename;
elsif(v_sal between 2000 and 3000 )then
update emp set comm =nvl(comm,0) +300 where ename = v_ename;
else
--null （若没有其他额外条件则打，null，不改变任何数据）
update emp set comm = nvl (comm ,0 )+50 where ename =v_ename;
end if;
end;
–方法二
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=&empno;
dbms_output.put_line ('员工姓名：'||v_ename||'员工薪资;'||v_sal);
if (v_sal<=1000)then
update emp set comm=nvl(comm,0) + 200 where ename =v_ename;
elsif (v_sal <=2000)then
update emp set comm=nvl(comm,0)+ 300 where ename=v_ename;
else
null;
end if;
end;
–方法三
declare
v_emp emp%rowtype;
begin
select ename,sal into v_emp.ename,v_emp.sal from emp where empno=&empno;
dbms_output.put_line ('员工姓名：'||v_emp.ename||'员工薪资：'||v_emp.sal);
if (v_emp.sal between 1000 and 2000 )then
update emp set comm=nvl(comm ,0)+200 where ename=v_emp.ename;
elsif(v_emp.sal between 2000 and 3000)then
update emp set comm =nvl(comm,0)+300 where ename=v_emp.ename;
else
null;
end if;
end;
–方法四
declare
type v_canshu is record(
v_sal number (7,2),
v_ename emp.ename%type,
v_emp emp%rowtype);
v_canshu_new v_canshu;
begin
select sal,ename into v_canshu_new.v_sal,v_canshu_new.v_ename from emp where empno=&empno;
dbms_output.put_line('薪资：'||v_canshu_new.v_sal||'姓名：'||v_canshu_new.v_ename);
end;
–判断 在SQL语句中的判断
–语法：
case的用法
when 条件 then
执行事件;
when 条件 then
执行事件;
else
剩余条件下执行事件;
end case;
select empno,
ename,
sal,
(case
when sal between 1 and 1000 then
'初级'
when sal between 1000 and 2000 then
'中级'
when sal between 2000 and 3000 then
'高级'
else
'有钱人'
end)
from emp;
–添加游标
declare
cursor v_youbiao is
select * from emp;
begin
for v_abc in v_youbiao loop
case
when v_abc.sal < 1000 then
dbms_output.put_line('初级'||v_abc.ename);
when v_abc.sal between 1000 and 2000 then
dbms_output.put_line('中级'||v_abc.ename);
when v_abc.sal between 2000 and 3000 then
dbms_output.put_line('高级'||v_abc.ename);
else
dbms_output.put_line('有钱人'||v_abc.ename);
end case; end loop;
end;
–PL/SQL 循环
–1、while循环
declare
--声明变量
v_ano number := 1;
begin
--while循环（v_ano>10进入循环）
while (v_ano < 10) loop
--打印次数
dbms_output.put_line('while循环次数：' || v_ano); --9
--循环条件
v_ano := v_ano + 1;
--结束循环
end loop;
end;
–2、loop循环
declare
--声明变量
v_bno number := 1;
begin
--loop循环
loop
--打印循环次数
dbms_output.put_line('loop循环次数：' || v_bno);
--执行操作
v_bno := v_bno + 1;
--循环退出条件
exit when v_bno > 10;--有退出的循环条件
--结束循环
end loop;
end;
–3、for in 循环
declare
begin
--设置循环条件 v_cno 是变量，但是在for循环里面不需要声明
for v_cno in 1 .. 10 loop
dbms_output.put_line('for循环次数：' || v_cno);
end loop;
end;
–示例：goto forxh ------<>
declare
v_dno declare
v_dno number := 1;
begin
--goto表示无条件跳转到指定的标记位置 注：不区分大小写
/*goto forxh;*/
while (v_dno < 10) loop
dbms_output.put_line('while循环：' || v_dno);
v_dno := v_dno + 1;
end loop;
<<forxh>>
loop
exit when v_dno > 20;
dbms_output.put_line('loop循环：' || v_dno);
v_dno := v_dno + 1;
end loop;
--<<标签分隔符>>
/*<<forxh>> --定义标记名称*/
for v_eno in 1 .. 10 loop
dbms_output.put_line('for循环' || v_eno);
end loop;
end;number := 1;
begin
--goto表示无条件跳转到指定的标记位置 注：不区分大小写
/*goto forxh;*/
while (v_dno < 10) loop
dbms_output.put_line('while循环：' || v_dno);
v_dno := v_dno + 1;
end loop;
<<forxh>>
loop
exit when v_dno > 20;
dbms_output.put_line('loop循环：' || v_dno);
v_dno := v_dno + 1;
end loop;
*--<<标签分隔符>>*
*/\*<> --定义标记名称\*/
for v_eno in 1 .. 10 loop
dbms_output.put_line('for循环' || v_eno);
end loop;
end;
----退出循环----
–*************************
退出循环: continue 结束单次循环，跳出循环
declare
/* i number := 1;*/
begin
for i in 1 .. 3 loop
if i = 2 then
dbms_output.put_line('我不会被执行！');
continue;
end if;
dbms_output.put_line(i);
end loop;
end;declare
/* i number := 1;*/
begin
for i in 1 .. 3 loop
if i = 2 then
dbms_output.put_line('我不会被执行！');
continue;
end if;
dbms_output.put_line(i);
end loop;
end;
退出循环: exit 终止后面所有循环
declare
i integer;
begin
for i in 1 .. 3 loop
for j in 1 .. 2 loop --1 2
if j = 1 then
exit;
end if;
dbms_output.put_line('我不会被执行!');
end loop;
dbms_output.put_line('i = ' || i);
end loop;
end;
退出循环: return 终止所有循环
– 直接退出程序（过程、方法、匿名块）
declare
i integer;
begin
for i in 1 .. 3 loop /*i=1 2 3*/
for j in 1 .. 2 loop /*j=1 2*/
if j = 1 then
return;
end if;
dbms_output.put_line('我不会被执行!');
end loop;
dbms_output.put_line('我也不会被执行!');
end loop;
dbms_output.put_line('我还是不会被执行!');
end;
–课堂练习题
– 键盘输入一个年份,判断是否是闰年
–第一种方法
declare
v_year number(4):=&请输入年份;
begin
if mod (v_year,4)=0 and mod(v_year,100)!=0 or mod(v_year,400)=0 and mod(v_year,100)=0 then
dbms_output.put_line('恭喜你'||'该年份：'||'是润年');
else
dbms_output.put_line('很抱歉'||'该年份：'||'不是润年');
end if;
end;
–第二种方法
select
case
when mod (&_year,4)=0 and mod(&_year,100)!=0 or mod(&_year,400)=0 then
'恭喜你'||'该年份：'||'是润年'
else
'很抱歉'||'该年份：'||'不是润年'
end
from dual
–pl/sql练习题：
select * from emp
select * from dept
–1、编写一个PL/SQL块，从emp表中显示名为“SMITH”的雇员薪水和职位
declare
v_job emp.job%type;
v_sal emp.sal%type;
begin
select job,sal into v_job,v_sal from emp where ename ='&ename';
dbms_output.put_line('员工职位：'||v_job||'员工薪水：'||v_sal);
end;
–2、编写一个PL/SQL块，接收用户输入的一个部门编号，从dept表中显示该部门的名称与所在位置
declare
v_dname dept.dname%type;
v_loc dept.loc%type;
begin
select dname,loc into v_dname,v_loc from dept where deptno=&deptno;
dbms_output.put_line('部门名称：'||v_dname||'部门所在位置：'||v_loc);
end;
–3、编写一个PL/SQL块，利用%type属性，接收一个员工编号，从emp表中显示该员工的薪资和奖金
declare
v_sal emp.sal%type;
v_comm emp.comm%type;
begin
select sal,comm into v_sal,v_comm from emp where empno=&empno;
dbms_output.put_line('员工薪资：'||v_sal||'奖金：'||v_comm);
end;
–4、编写一个PL/SQL块，利用%rowtype属性，接收一个员工编号，从emp表中显示该员工的个人信息
declare
v_emp emp%rowtype;
begin
select empno,ename,job,mgr,hiredate,sal,comm,deptno
into v_emp.empno,v_emp.ename,v_emp.job,v_emp.mgr,v_emp.hiredate,v_emp.sal,v_emp.comm,v_emp.deptno
from emp where empno=&empno;
dbms_output.put_line('员工编号：'||v_emp.empno||'员工姓名：'||v_emp.ename||'员工职位：'||v_emp.job||'领导编号：'||v_emp.mgr
||'入职日期：'||to_char(v_emp.hiredate,'yyyy.mm.dd')||'薪资：'||v_emp.sal||'奖金：'||v_emp.comm||'部门编号：'||v_emp.deptno);
end;
–5、编写一个PL/SQL块，公司要求根据职位来加薪，公司觉得如下，接收一个员工编号，从emp表中实现如下加薪处理
declare
v_sal emp.sal%type;
v_job emp.job%type;
v_ename emp.ename%type:=&empno;
begin
select sal,job into v_sal,v_job from emp where empno=v_ename;
if (v_job ='CLERK')then
update emp set sal=sal+500 where empno=v_ename ;
elsif (v_job ='SALESMAN')then
update emp set sal=sal+1000 where empno=v_ename;
elsif (v_job='ANALYST')then
update emp set sal=sal+1500 where empno=v_ename;
elsif (v_job='OTHERWISE')then
update emp set sal=sal+2000 where empno=v_ename;
else
null;
end if;
end;
select * from emp
–6、编写一个程序块,接受一个雇员名,从emp表中显示该雇员的工作岗位与薪水,若输入的雇员名不存在,显示“该雇员不存在”信息
declare
v_ename emp.ename%type;
v_job emp.job%type;
v_sal emp.sal%type;
begin
select ename,job,sal into v_ename,v_job,v_sal from emp where ename='&ename';
dbms_output.put_line('员工姓名：'||v_ename||'员工职位：'||v_job||'员工薪资：'||v_sal);
exception
when no_data_found then
dbms_output.put_line('该雇员不存在');
end;
–7、接受两个数相除并且显示结果,如果第二个数为0,则显示消息“除数不能为0”
declare
v_num1 number(4):=&请输入除数;
v_num2 number(4):=&请输入被除数;
begin
if v_num1!=0 and v_num2!=0 or v_num1=0 and v_num2!=0 then
dbms_output.put_line(v_num1/v_num2);
elsif v_num2=0 then
dbms_output.put_line('除数不能为0');
else
null;
end if;
end;
–8、查询姓为“SMITH”的员工信息，并输出其员工号、姓名、工资、部门号
–如果该员工不存在，则插入一条新记录，员工号为2012，员工姓名为“Smith”,工资为7500元，
–入职日期为“2002年3月5日”，部门号为10
declare
v_ename emp.ename%type;
v_empno emp.empno%type;
v_sal emp.sal%type;
v_deptno emp.deptno%type;
begin
select ename ,empno,sal,deptno into v_ename,v_empno,v_sal,v_deptno from emp where ename='&ename';
dbms_output.put_line('员工号：'||v_empno||'员工姓名：'||v_ename||'薪资：'||v_sal||'部门号：'||v_deptno);
exception
when no_data_found then
insert into emp (empno,ename,sal,hiredate,deptno)values(2012,'SMITH',7500,to_date('2002-03-05','yyyy.mm.dd'),10);
end;
select * from emp
–9、打印求1~100之间的所有偶数
declare
begin
for i in 1..100 loop
if mod (i,2)=0 then
dbms_output.put_line(i);
end if;
end loop;
end;
–10、对名字以"A"或"H"开始的所有雇员按他们的基本薪水的10%加薪
declare
cursor v_youbiao is select * from emp where (ename like'A%'or ename like 'H%');
begin
for v_emp in v_youbiao loop
--update emp set sal=sal/1.1 where ename= v_emp.ename;
update emp set sal=sal*0.1+sal where ename= v_emp.ename;
end loop;
end;
select * from emp;
–11、编写存储过程检查如果已经雇佣该雇员超过60个月(五年)，则给他额外加薪3000.
create or replace procedure proc11(v_empno in number ,v_sal out number)
is
cursor v_youbiao is select * from emp where empno=v_empno ;
begin
for v_emp in v_youbiao loop
if sysdate-v_emp.hiredate>5*365 then
update emp set sal=sal+3000 where empno = v_emp.empno;
v_sal:=v_emp.sal+3000;
else
null;
end if;
end loop;
exception
when no_data_found then
dbms_output.put_line('查无此人');
when others then
dbms_output.put_line('系统错误！');
end;
select * from emp for update
declare
v_sal number;
v_empno number;
begin
proc11(&员工编号,v_sal);
dbms_output.put_line('员工薪资：'||v_sal);
end;
–12、编程实现对所有雇员按他们基本薪水的10%加薪，如果所增加的薪水大于1000，则取消加薪.
declare
cursor v_youbiao is select * from emp ;
v_empedeclare
cursor v_youbiao is select * from emp ;
begin
for v_emp in v_youbiao loop
if (v_emp.sal>1000)then
dbms_output.put_line('该员工薪资为基本工资：'||v_emp.sal);
else
dbms_output.put_line('该员工工资为：'||v_emp.sal*1.1);--错误试法：v_emp.sal=v_emp.sal*1.1
end if;
end loop;
end;
-- 键盘介入三个值，并按照从大到小依次打
declare
a number(3):=&请输入第一个值;
b number(3):=&请输入第二个值;
c number(3):=&请输入第三个值;
begin
if a>b and b>c then
dbms_output.put_line('输出值：'||a||b||c);
elsif a>=b and b>=c then
dbms_output.put_line('输出值：'||a||b||c);
elsif b>a and a>c then
dbms_output.put_line('输出值：'||b||a||c);
elsif b>=a and a>=c then
dbms_output.put_line('输出值：'||b||a||c);
elsif c>b and b>a then
dbms_output.put_line('输出值：'||c||b||a);
elsif c>=b and b>=a then
dbms_output.put_line('输出值：'||c||b||a);
else
null;
end if;
end;
– 功能: 循环输出 1 ~ 3 (for)
declare
begin
for i in 1..3 loop
dbms_output.put_line('输出值：'||i);
end loop;
end;
– 功能: 循环输出 1 ~ 3 (while)
declare
i integer:=1;
begin
while (i<=3) loop
dbms_output.put_line('输出值：'||i);
i:=i+1;
end loop;
end;
–*************************
– 功能: 循环输出 1 ~ 3 (loop)
declare
i number :=1;
begin
loop
dbms_output.put_line('输出值：'||i);
i:=i+1;
exit when i>3;
end loop;
end;
–*************************
– 计算s=1x2+2x3+…+N*(N+1),当N=50的值
declare
n number(2):=1;
s number(10):=0;
begin
loop
s:=s+n*(n+1);
if n=50 then
exit;
end if;
n:=n+1;
end loop;
dbms_output.put_line('输'||s);
end;
–
– 打印99乘法表
第一种方法：
declare
begin
for i in 1..9 loop
for j in 1..i loop
--dbms_output.put(i||'x'||j||'='||i*j||' ');
dbms_output.put (rpad (i||'x'||j||'='||i*j,8,' '));
end loop;
dbms_output.put_line(' ');
end loop;
end;
第二种方法：
declare
begin
for i in 1..9 loop
for j in i..9 loop
dbms_output.put(rpad(i||'x'||j||'='||i*j,8,' '));
end loop;
dbms_output.put_line(' ');
end loop;
end;
–*************************
–数字有1-20，但是不打印10 ，且最后结束打印 hello
declare
begin
for i in 1..20 loop
if i=10 then
continue;
end if;
dbms_output.put_line(i);
end loop;
dbms_output.put_line('hello');
end;
–*************************
–数字有1-20，但是不打印10，遇到10跳出循环体 ，且最后结束打印 hello
declare
begin
for i in 1..20 loop
if i=10 then
exit;
end if;
dbms_output.put_line(i);
end loop;
dbms_output.put_line('hello');
end;
–*************************
–数字有1-20，但是不打印10，遇到10跳出循环体 ，且最后结束不打印 hello
declare
begin
for i in 1..20 loop
if i=10 then
return;
end if;
dbms_output.put_line(i);
end loop;
dbms_output.put_line('hello');
end;
第九：异常处理：
–异常处理
–1、预定义错误：系统已经定义好了一些异常如下几种：
–no_data_found:无数据返回
–too_many_rows:返回数据过多，返回多行数据
–dup_val_on_index:唯一索引插入重复值，即违反了主键约束
–zero_divide:除数为零
–value_error:赋值异常，比如数值型数据类型的变量，强行赋值字符型数字类型
–invalid_cursor:失效的游标
–1.预定义错误
一、no-data_found:无数据返回
declare
v_emp emp%rowtype;
begin
select empno,ename into v_emp.empno,v_emp.ename
from emp where empno='2222';--信息是不存在的
dbms_output.put_line(v_emp.empno||'和'||v_emp.ename);
–异常处理
exception
when no_data_found then
--接什么动作都可以
dbms_output.put_line('员工信息不存在!');--执行语句，可以打印，也可以增（插入一条员工信息）删改查数据
end;
二、too_many_rows:返回数据过多
declare
v_emp emp%rowtype;
begin
select empno,ename into v_emp.empno,v_emp.ename from emp;--信息数据返回多行
dbms_output.put_line(v_emp.empno||'和'||v_emp.ename);
–异常处理
exception
when too_many_rows then
dbms_output.put_line('返回数据过多！');
end;
三、dup_val_on_index:唯一索引插入重复值
declare
begin
update emp set empno =1000 where deptno = 20;
exception
when dup_val_on_index then
dbms_output.put_line('唯一索引插入重复值！');
end;
2.非预定义错误
–是指违反了Oracle的规则，有报错编号和报错信息，但系统并未定义异常名称，那么我们将这种错误定义
–一个名字，并将名字和错误代码绑定
declare
FK exception;–定义异常
–错误跟定义好的错误变量关联
pragma exception_init (FK,-02292);--当报错-02292错误码的时候，定义错误为“FK”
begin
delete from dept where deptno = 30;--如果没有异常处理，将-02292，事实上是违反了外键约束
exception
when FK then
--错误处理
dbms_output.put_line('违反了外键约束！');--当出现'v_deptno_error'错误时，报'没有该部门编号'
end;
–3.自定义错误：即出现某种情况，我就可以定义其为异常
–自定义一个异常，并抛出
declare
v_sal emp.sal%type;
v_sal_error exception;
begin
select sal into v_sal from emp where empno=&empno;
if (v_sal<3000)then
--2、定义抛出异常编码值raise_application_error(code错误码，'错误信息')
raise_application_error(-20000,'薪资问题');
end if;
end;
–自定义一个异常，并处理
declare
v_sal emp.sal%type;
v_sal_error exception;
begin
select sal into v_sal from emp where empno=&empno;
if (v_sal<3000) then
–1,抛出异常
raise v_sal_error
;–抛出异常名就一定要处理，不然会报错
–2.定义抛出异常编码值
raise_application_error(-20000,'薪资问题');
end if;
exception
when v_sal_error then
dbms_output.put_line('薪资少于3000');
when others then --其他任意异常
dbms_output.put_line('系统异常');
end;
/*注意：三种异常的区别，前两种异常是违反了oracle的规则，用户自定义异常是违反了
用户定义的规则*/
第十、游标
–游标–理解为存储数据的一个集合
/*
1、静态游标
1.1、隐式游标
在PL/SQL程序中执行dml语句时，会自动创建隐式游标，名称SQL
隐式游标属性：
%found SQL语句影响了一行或多行是为TRUE
%notfound SQL语句没有影响任何行是为TRUE
%rowcount SQL语句影响的行数
%isopen 游标是否打开，始终为FALSE(隐式游标是关闭状态的)
1.2、显示游标
用户自定义的查询
2、动态游标
REF游标：REF游标用于处理运行时才能确定的动态SQL查询的结果
–隐式游标
检测最近执行的语句
declare
begin
update emp set comm = 400 where deptno = 10;
--%found SQL语句影响了一行或多行是为TRUE
if(sql%found) then
dbms_output.put_line('found修改成功');
else
dbms_output.put_line('found修改失败');
end if;
--%notfound SQL语句没有影响任何行是为TRUE
if(sql%notfound) then
dbms_output.put_line('notfound修改失败');
else
dbms_output.put_line('notfound修改成功');
end if;
--%rowcount SQL语句影响的行数
dbms_output.put_line('影响行数：'||sql%rowcount);
--%isopen 游标是否打开，始终为FALSE(隐式游标是关闭状态的)
if(sql%isopen) then
dbms_output.put_line('游标是打开');
else
dbms_output.put_line('游标是关闭');
end if;
end;
select * from emp where deptno=10;
–显示游标
declare
--声明游标（关闭状态，是一个结果集,使用时需要打开）
cursor v_youbiao is select * from emp;
--声明行类型的变量
v_emp emp%rowtype;
begin
--打开游标
open v_youbiao;
从游标里面提取一行数据（可多次）
fetch v_youbiao into v_emp;
--打印
dbms_output.put_line('员工姓名：'||v_emp.ename||'员工薪资：'||v_emp.sal);
--关闭游标
close v_youbiao;
end;
--loop循环
declare
cursor v_youbiao is select * from emp;
v_emp emp%rowtype;
begin
open v_youbiao--打开游标
loop
fetch v_youbiao into v_emp;--从游标里提取一行数据
--循环退出条件
exit when v_youbiao%notfound;--判断有没有在游标里面找到结果
dbms_output.put_line('员工姓名：'||v_emp.ename||'员工薪资：'||v_emp.sal);
end loop;
close v_youbiao;
end;
--while 循环
declare
cursor v_youbiao is select * from emp;
v_emp emp%rowtype;
begin
open v_youbiao
fetch v_youbiao into v_emp;
while (v_youbiao%found) loop
fetch v_youbiao into v_emp;
exit when v_youbiao%notfound;
dbms_output.put_line('员工姓名：' || v_emp.ename || '员工薪资：' || v_emp.sal);
end loop;
close v_youbiao;
end;
–for循环
–1、自动打开游标,并提取第一行游标数据
–2、程序处理完当前所提取的数据而进入下一次循环时，
– 游标for循环语句自动提取下一行数据
–3、提取完结果集合中所有数据行后结束
–4、并自动关闭游标
declare
cursor v_youbiao is select * from emp;
begin
for a in v_youbiao loop
dbms_output.put_line('员工姓名：' || a.ename || '员工薪资：' || a.sal);
end loop;
end;
----------------拓-----展-----------------------------------------
–动态游标
/ref动态游标可以在运行的时候与不同的语句进行关联，他是动态的。
ref动态游标被用于处理多行的查询结果集，ref动态游标是ref类型的变量，类似于指针。
定义ref动态游标类型：type<类型名> is ref cursor return <返回类型>;
声明ref动态游标：<游标名> <类型名>;
打开ref动态游标：OPEN<游标名> FOR <查询语句>;/
declare
type v_leixing is ref cursor return emp%ROWTYPE;
v_youbiao v_leixing;
v_emp emp%rowtype;
begin
open v_youbiao for select * from emp;
loop
fetch v_youbiao into v_emp;
exit when v_youbiao%notfound;
dbms_output.put_line(v_youbiao%rowcount||'==='||v_emp.ename);
end loop;
close v_youbiao;
end;
强类型ref动态游标：带有RETURN语句的REF动态游标。
弱类型ref动态游标：不带有RETURN语句的REF动态游标。
declare
type v_leixing is ref cursor;
v_youbiao v_leixing;
v_emp emp%rowtype;
begin
open v_youbiao for select * from emp;
loop
fetch v_youbiao into v_emp;
exit when v_youbiao%notfound;
dbms_output.put_line(v_youbiao%rowcount || '===' || v_emp.ename);
end loop;
close v_youbiao;
end;
–通过游标进行如下练习
–1、给emp表里面的数据加福利，
–如果薪资小于1000，福利加100，
–如果薪资大于等于1000小于2000，福利加50，
–其余的福利加10
declare
cursor v_youbiao is select * from emp;
v_emp emp%rowtype;
v_fuli number (4);
begin
open v_youbiao;
loop
fetch v_youbiao into v_emp;
--exit when v_youbiao%notfound;
if (v_emp.sal<1000) then
v_fuli:=100;
elsif (v_emp.sal> 1000 and v_emp.sal< 2000) then
v_fuli:=50;
else
v_fuli:=10;
end if;
exit when v_youbiao%notfound;
--dbms_output.put_line('福利是：'||v_fuli);
update emp set comm = nvl(comm,0)+v_fuli where empno=v_emp.empno;
end loop;
close v_youbiao;
end;
select * from emp
–练习用for循环再做一次这个题–
declare
cursor v_youbiao is select * from emp;
v_emp emp%rowtype;
v_fuli number(3);
begin
for v_emp in v_youbiao loop
if (v_emp.sal<1000) then
v_fuli:=100;
elsif (v_emp.sal>1000 and v_emp.sal<2000) then
v_fuli:=50;
else
v_fuli:=10;
end if;
update emp set comm=nvl(comm,0) +v_fuli where empno=v_emp.empno;
end loop;
end;
select * from emp;
–2、为所有雇员增加工资，
–工资在1000以内的增加30%，
–在1000-2000增加20%，
–2000以上增加10%。
–并打印出来：员工的姓名、原工资、新工资
declare
cursor v_youbiao is select * from emp;
v_emp emp%rowtype;
v_wellfare number(4,2);
begin
open v_youbiao ;
fetch v_youbiao into v_emp;
while (v_youbiao%found)loop
if(v_emp.sal<1000) then
v_wellfare:=1.3;
elsif (v_emp.sal>1000 and v_emp.sal<2000) then
v_wellfare:=1.2;
else
v_wellfare:=1.1;
--exit when v_youbiao%notfound;
end if;
dbms_output.put_line('员工姓名：'||v_emp.ename||'旧工资：'||v_emp.sal||'新工资：'||v_emp.sal*v_wellfare);
update emp set comm=nvl(comm,0)*v_wellfare where empno=v_emp.empno;
fetch v_youbiao into v_emp;
end loop;
end;
–3、打印20部门的所有员工的工资
declare
cursor v_youbiao is select * from emp;
--v_emp emp%rowtype;
begin
for v_emp in v_youbiao loop
if (v_emp.deptno=20) then
dbms_output.put_line('员工工资：'||v_emp.sal);
end if;
end loop;
end;
----for练习用for循环再做一次这个题 ----
declare
cursor v_youbiao is select * from emp;
v_emp emp%rowtype;
begin
open v_youbiao;
fetch v_youbiao into v_emp;
while (v_youbiao%found) loop
if (v_emp.deptno=20) then
dbms_output.put_line('员工薪资：'||v_emp.sal);
end if;
fetch v_youbiao into v_emp;
end loop;
end;
–存储过程
–存储过程是一个预编译的SQL语句
–优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。
–如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。
–可以用一个命令对象来调用存储过程。
存储过程语法：
create [or replace] procedure < procedure name> --or replace 作用：名字相同的存储过程，新建的覆盖原先的
is|as --固定语法
begin --开始
<executable statements> --存储过程中的逻辑动作
end;
执行存储过程方法：
最常用的方法：
begin
存储过程名
end;
第十一、存储过程
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。
–1、创建无参存储过程
–or replace 创建可以不写，修改加上即可
create or replace procedure proc1
as
begin
dbms_output.put_line('我是存储过程!!!!!');
dbms_output.put_line('Hello Everyone!');
end;
存储过程写完，执行保存时，即使存储过程写错了，也不会报错，
但是
文件夹中看出来，有数错误标记“×”，或者在调试模式中编写存储过程
–调用无参的存储过程
begin
proc1;
end;
–删除存储过程
/*语法：
drop procedures + 存储过程名
存放存储过程的表为：user_source
例：drop procedure proc1;
–2、创建带输入参数的存储过程（入参）
存储过程名字后（入参名字 in 约束入参的数据类型，既可以用%type,也可以直接用number，char）
多个入参：(入参一 in 数据类型，入参二 in 数据类型)
create or replace procedure proc2
(v_empno in emp.empno%type)
as
begin
--根据员工编号，删除指定员工信息
delete from emp where empno=v_empno;
--判断是否删除成功
if (sql%notfound) then
raise_application_error(-20008,'删除失败，员工不存在！');
else
dbms_output.put_line('删除成功');
end if ;
end;
注：创建存储过程时，即使存储过程代码有误，也会被创建成功，
但是procedures文件夹下存储过程名字会有叉叉，可以打开sql窗口中的program windows-procedures窗口
写存储过程
–2.1、调用带有输入参数的存储过程
begin
proc2(&输入员工编号);
end;
select * from emp where empno = '7499';
–3、创建带输出入参数的存储过程（出out入in参）
create or replace procedure proc3
(v_deptno in number,
v_sal out number,
v_cnt out number)
--in 表示入参；
--out 表示出参；
as
begin
select avg(sal),count(*) into v_sal,v_cnt from emp where deptno=v_deptno;
exception
when no_data_found then
dbms_output.put_line('没有找到部门');
when others then
dbms_output.put_line('系统异常');
end;
–3.1、调用带有输出参数的存储过程
注意，调用带有出参数据的存储过程要另外定义两个变量接收存储
过程的出参，整体：创建存储过程时创建两个出参v_sal，v_cnt 接收emp表中查到的数据，
调用proc3时，还要在定义两个变量v_avgsal，v_count来接收v_sal，v_cnt的数据
declare
v_avgsal number;
v_count number;
begin
proc3(&输入部门编号,v_avgsal,v_count);
dbms_output.put_line(trunc(v_avgsal)||'====='||v_cou2nt);
end;
–存储过程时如何实现批量插入数据
–创建存储过程
-----------往数据库中插入1000条数据---------------------------------
create or replace procedure test
is
begin
for i in 1..1000 loop
insert into empbak
values
(
i+1000,
dbms_random.string('z',5),
'CLERK',
i+1,
trunc(sysdate-i),
1000+i,
1000,
20
);
end loop;
end;
----执行test----
declare
begin
test;
end;
select * from empbak;
第十二、存储函数
–把程序存储在数据库中，并可以在任何地方运行他
–存储过程和存储函数唯一的区别就是存储函数总向调用者返回数据，而过程则不返回数据
存储函数语法：
create [or replace] function function name
–or replace 作用：名字相同的存储过程，新建的覆盖原先的
(v_deptno in number,
v_empno in emp.empno%type)
return number
is/ as --固定语法
```mysql
--函数使用过程中，需要声明变量，记录类型，cursor
```
begin --开始
--存储函数中的执行体
exception --异常处理
--处理函数执行过程中的异常
end;
–创建无参存储函数
create or replace function func2
return varchar2
is
begin
return 'hello world!';
end;
select func1,func2 from dual;
----------
create or replace function func1
(name varchar2)
return varchar2
is
begin
return 'hello world!'||name;
end;
select func1('zhang') from dual;
–执行函数
–执行无参存储函数
declare
v_h varchar2(200);
begin
v_h := func1;
dbms_output.put_line(v_h);
end;
---
declare
begin
dbms_output.put_line(func1('zhang'));
end;
–创建输入参数的存储函数
–根据部门编号，返回部门的总工资
select sum(sal) from emp where deptno=20;
create or replace function func2
(v_deptno in number)
return number
is --as
v_sumsal number;
begin
select sum(sal) into v_sumsal from emp where deptno = v_deptno;
return v_sumsal;
exception
when no_data_found then
dbms_output.put_line('没有此部门');
when others then
dbms_output.put_line(sqlerrm);
end;
–执行输入参数的存储函数
begin
dbms_output.put_line(func2(&部门编号));
end;
select func2(20) from dual;
–创建输出参数的存储函数
–根据员工的编号输出员工的姓名和工资，并且返回员工的年收入
create or replace function func3
(v_empno in emp.empno%type,
v_ename out emp.ename%type,
v_sal out emp.sal%type )
return number
is
v_sumsal number;
begin
select ename,sal,(sal+nvl(comm,0))*12 into v_ename,v_sal,v_sumsal from emp where empno = v_empno;
return v_sumsal;
exception
when no_data_found then
dbms_output.put_line('没有找到部门编号');
when others then
dbms_output.put_line(sqlerrm);
end;
–执行输出参数的存储函数
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
v_sumsal number;
begin
v_sumsal := func3(&empno,v_ename,v_sal);
dbms_output.put_line(v_sumsal||'====='||v_sal||'===='||v_ename);
end;
select * from emp where empno = '7499';
–创建带有输入输出单数的存储函数
–求两个数的平方和，并输出两个数的平方
create or replace function func4
(n1 in out number,
n2 in out number)
return number
is
begin
n1 := n1*n1;
n2 := n2*n2;
return n1+n2;
end;
--执行输入输出参数的存储函数
declare
n1 number :=&请输入第一个数;
n2 number :=&请输入第二个数;
nsum number;
begin
nsum := func4(n1,n2);
dbms_output.put_line(nsum||'====='||n1||'===='||n2);
end;
–删除存储函数
--语法：drop function + 存储函数名
drop function v_func;
–存储过程、函数练习题
（1）创建一个存储过程，以员工编号为参数，输出该员工的工资。
create or replace procedure proc7
(v_empno in number,
v_sal out number)
as
begin
select sal into v_sal from emp where empno=v_empno;
dbms_output.put_line('员工工资：'||v_sal);
exception
when no_data_found then
dbms_output.put_line('没有找到该员工编号！！！');
when others then
dbms_output.put_line('系统异常！！！');
end;
declare
v_empno number;
v_sal number;
begin
proc7(&empno,v_sal);
end;
create or replace function func4
(v_empno in number ,
/*v_ename out varchar2,*/
v_sal out number)
return number
as
begin
select sal/* ,ename*/ into v_sal/*,v_ename */from emp where empno = v_empno;
return v_sal;
exception
when no_data_found then
dbms_output.put_line('找不到该员工编号！');
when others then
dbms_output.put_line('系统异常！');
end;
declare
v_sal emp.sal%type;
/*v_ename emp.ename%type;*/
v_empno emp.empno%type;
a emp.sal%type;
begin
a:=func4(&empno,v_sal);
dbms_output.put_line( a);
end;
/（2）创建一个存储过程，以员编号为参数，修改该员工的工资。/
–若属于10号部门，则工资增加150；
–若属于20号部门，则工资增加200；
–若属于30号部门，则工资增加250；
–若属于其他部门，则增加300。
–第一种方法：
create or replace procedure proc5
(v_empno in number,
v_sal out number)
is
cursor v_youbiao is select * from emp where empno=v_empno;
begin
for v_emp in v_youbiao loop
if (v_emp.deptno=10)then
update emp set sal=sal+150 where deptno=v_emp.deptno;
v_sal:=v_emp.sal+150;
elsif (v_emp.deptno=20)then
update emp set sal=sal+200 where deptno=v_emp.deptno;
v_sal:=v_emp.sal+200;
elsif (v_emp.deptno= 30 )then
update emp set sal=sal+250 where deptno =v_emp.deptno;
v_sal:=v_emp.sal+250;
else
update emp set sal=sal+300 where deptno =v_emp.deptno;
end if;
end loop;
exception
when no_data_found then
dbms_output.put_line('没有找到员工编号！');
when others then
dbms_output.put_line('系统异常！！！');
end;
declare
v_sal number;
v_empno number;
begin
proc5(&v_empno,v_sal);
dbms_output.put_line('员工编号：'||'&v_empno'||' '||'薪资：'||v_sal);
end;
select * from emp for update;
–第二种方法：
create or replace procedure pronc 9
(v_empno in number,v_sal out number,v_deptno out number)
return number
is
begin
select sal,empno,deptno into v_sal,v_empno,v_deptno from emp where empno=v_empno;
if (v_deptno=10)then
update emp set sal=sal+150 where deptno=v_deptno;
v_sal:=v_sal+150;
elsif (v_deptno=20)then
update emp set sal=sal+200 where deptno=v_deptno;
v_sal:=v_sal+200;
elsif (v_deptno= 30 )then
update emp set sal=sal+250 where deptno =v_deptno;
v_sal:=v_sal+250;
else
update emp set sal=sal+300 where deptno =v_deptno;
end if;
exception
when no_data_found then
dbms_output.put_line('没有找到员工编号！');
when others then
dbms_output.put_line('系统异常！！！');
end;
（3）创建一个存储过程，以员工编号为参数，返回该员工的工作年限（以参数式返回）。
select * from emp;
create or replace procedure proc6
(v_empno in number,
v_hiredate out number )
is
begin
select trunc((sysdate-hiredate)/365 )into v_hiredate from emp where empno=v_empno;
dbms_output.put_line('该员工的工作年限：'||v_hiredate);
exception
when no_data_found then
dbms_output.put_line('没有找到该员工编号：');
when others then
dbms_output.put_line('系统异常：');
end;
declare
v_empno number;
v_hiredate number;
begin
proc6(&empno,v_hiredate);
end;
（4）创建一个函数，以员工编号为参数，返回该员工的工资。
create or replace function func5
(v_empno in number,
v_sal out number)
return number
is
begin
select sal into v_sal from emp where empno=v_empno;
return v_sal;
exception
when no_data_found then
dbms_output.put_line('没有找到该员工编号！！！');
return null;
when others then
dbms_output.put_line('系统异常！！！！');
end;
declare
v_empno number;
v_sal number;
begin
dbms_output.put_line(func5(&empno,v_sal));
end;
（5）创建一个存储过程，以员工编号和部门编号作为参数，修改员工所在的部门为所输入的部门编号。
–如果修改成功，则显示“员工由……号部门调入调入……号部门”；
–如果不存在该员工，则显示“员工号不存在，请输入正确的员工号。”；
–如果不存在该部门，则显示“该部门不存在，请输入正确的部门号。”。
create or replace procedure proc22
(p_empno in emp.empno%type, p_deptno in emp.deptno%type)
is
v_empno number := 0;
v_deptno number := 0;
m_deptno emp.deptno%type;
begin
select count(*) into v_empno from emp where empno = p_empno;
select deptno into m_deptno from emp where empno = p_empno;
select count(distinct deptno) into v_deptno from emp where deptno = p_deptno;
if v_empno = 0 then
dbms_output.put_line('员工号不存在,请输入正确的员工号。');
end if;
if v_deptno = 0 then
dbms_output.put_line('该部门不存在，请输入正确的部门号。');
end if;
if v_empno = 1 and v_deptno = 1 then
dbms_output.put_line('该员工由'||m_deptno||'号部门调入,调入到'||p_deptno||'号部门');
update emp set deptno = p_deptno where empno = p_empno;
end if;
end;
begin
proc22 (7499,20);
end;
select * from emp for update;
第十三、视图
—数据库对象
—从表中抽出的逻辑上相关的数据集合
—是一个虚表，建立在已有表的基础上
—赖余建立这些表叫基表
—用来存储select语句，控制数据访问，简化查询，避免重复访问数据
–创建单表视图
create view empview(视图名)
as
--视图里面的数据来源
select empno,ename,sal from emp where deptno = '20';
--查看视图
select * from empview;
select * from emp where empno = '7935';
--对视图数据进行增删改
insert into empview values(6666,'shadows',888.88);
update empview set sal = 2000 where empno = '7935';
delete from empview where empno = '7935';
–创建多表视图
create view emp_dept_view
as
----视图里面的数据来源
select e.empno,e.ename,e.sal,d.dname from emp e,dept d
where e.deptno = d.deptno;
select * from emp_dept_view;
–修改视图or replace
create or replace view empview
as
--视图里面的数据来源
select empno,ename,sal from emp where deptno = '10';
insert into empview values(6666,'shadows',888.88);
select * from emp;
–视图加上关键字（最后with read only）
–只可以读，不可以进行增删改
create or replace view empview
as
--视图里面的数据来源
select empno,ename,sal from emp where deptno = '30'
with read only;
–删除视图
drop view emp_dept_view;
索引 ：
创建索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：
create index indexName on table_name (column_name)
–如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
修改表结构(添加索引)
alter table tableName add index indexname(columnName)
创建表的时候直接指定
create table mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
index [indexName] (username(length)));
删除索引的语法
DROP INDEX [indexName] ON mytable;
唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
使用ALTER 命令添加和删除索引
有四种方式来添加数据表的索引：
-
alter table tbl_name add primary key (column_list)该语句添加一个主键，这意味着索引值必须是唯一的，且不能为null。
-
alter table tbl_name add unique idex_name (column_list):这条语句创建索引的值必须是唯一的（除了null外，mnull可能会出现多次）。
-
alter table tbl_name add index index_name (column_list):添加普通索引，索引值可出现多次。
-
alter table tbl_name add fulltext index_name (cloumn_list):该语句指定了索引为 fulltext ，用于全文索引
以下实例为在表中添加索引。
mysql> alter table testalter_tbl add index (c);
你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:
mysql> alter table testalter_tbl drop index c;
使用 ALTER 命令添加和删除主键
主键作用于列上（可以一个列或多个列联合主键），添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
你也可以使用 ALTER 命令删除主键：
mysql> alter table testalter_tbl DROP PRIMARY KEY;
删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。
显示索引信息
你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。
尝试以下实例:
mysql> show index from table_name\G
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
