Oracle笔记
数据库开始
启动Oracle
安装好后，打开sql命令行
输入
conn sys as sysdba启动，然后输入口令
select 11 from dual;检验是否安装好
用户的创建
超级管理员登录
conn sys/口令 as sysdba以dba进入数据库
创建表空间
create tablespace scott_tb_space datafile'D:\Programming\oracle\note\tbspace/scott_tb_space.dbf' size 200m;在D:\Programming\oracle\note\tbspace路径下创建名为scott_tb_space的数据文件，dbf为数据类型，表空间为200m.
create user scott identified by tiger default tablespace scott_tb_space;创建用户
create user 用户名 identified by 密码 default tablespace 表空间;grant dba to scott;授权给用户权限
测试创建成功是否
select * from dual;
如果使用19c,及一开始要用管理员运行cmd,然后使用管理员登录
sqlplus / as sysdba
然后创建用户从12c开始 oracle添加了CDB，默认连接的都是CDB，在不切换到pdb的情况下，创建用户名需要加C##的前缀
所以创建用户命令为：
create user c##root IDENTIFIED BY sy670422;
然后在进行授权：
grant connect,resource,unlimited tablespace to c##root container=all;
然后运行sqlplus;
表设计
员工表：名称，年龄，性别
| name | age | gender |
|---|---|---|
| 张三 | 18 | 男 |
| 李四 | 20 | 男 |
select语法
select 查询信息 from 数据来源
在分组查询中，select子句中非统计函数列应该全部出现在group by 子句中
SELECT DEPTNO,EMPNO ,count(*) FROM emp GROUP BY DEPTNO,EMPNO;
查询部门信息
select * from dept;
查询员工的信息
select * from emp;
查询所有部门的部门编号和部门名称
select deptno,dname from dept;
查询所有员工名称，工资，所在部门，编号
select ename,sal,deptno from emp;
去重
查询所有员工的名称，所在的部门编号
select ename deptno from emp; ##查询存在员工的部门编号 select deptno from emp; ##去重 distinct select distinct deptno from emp; ##查询所有员工的名称，所在的部门编号 select distinct ename,deptno from emp;
给列取别名
##查询员工姓名，员工工资 select ename,sal from emp; select ename 姓名,sal 工资 from emp; select ename as 姓名 ,sal as 工资 from emp;
排序
--order by select ename,sal from emp; 查询出所有的员工的姓名，工资，按照员工工资降序排序 select ename,sal from emp order by sal desc;##降序 select ename,sal from emp order by sal asc;##升序
多字段排序
--查询所有员工姓名，工资，所在部门编号，按照部门编号升序，同一部门的员工，按照工资降序排序 select ename,sal,deptno from emp order by deptno asc,sal desc;
伪列和虚表
注释：查询不存在的列及为伪列，当需要的结果不能直接从表中得到需要经过计算来展示则可以使用伪列+表达式实现
##查询用户姓名，工资 select ename, sal,1 from emp; ##查询用户姓名，月工资，年薪（月薪*12） select ename,sal,sal*12 from emp; select ename,sal,sal*12 as 年薪 from emp;
nul处理
查询员工姓名，月收入 工资+提成
select * from emp; select ename,sal,comm,sal+comm as 月收入 from emp; ##nvl(expl表达式，res结果) select ename,sal,comm,sal+nvl(comm,0) as 月收入 from emp; ##排序 ##按照员工的工资进行排序 select * from emp order by sal; ##降序 select * from emp order by comm desc; ##将提出空的放最后null first,null last select * from emp order by comm desc nulls first; select * from emp order by comm desc nulls last;
字符串拼接（||）
select ename from emp; select ename,ename||'a' as 别名 from emp; ##null select ename,comm,ename||comm test from emp;
虚表
dual 虚表 ，主要构建select语法规则，Oracle保证dual表里面永远只有一条记录。
求表达式的值和选择系统变量。
--计算999*666 select 999*666 from dual;
select 语句执行顺序
null,当null进行运算时，结果为null
select 查询字段1 别名，查询字段2 as 别名，表达式 别名 from 数据来源 order by 排序字段 desc
语法主体
select ....from...order by
解析顺序
1.from
2.select
3.ordre by
查询员工姓名，员工工资 别名 工资并且结果按照工资降序排序
select ename,sal 工资 from emp order by sal desc;
语法顺序
select，from，where，group by,having,order by
编译顺序
from，where，group by,having,，select，order by
条件查询
条件运算
=，<,><=,>=,<>,!=,between and, in 查询10部门的员工的信息
查询10部门以外的员工信息
查询工资在2000以上的员工名称，工种，所属部门编号
查询工资在1800到2500之间的员工信息
查询10，30，40部门的员工信息
select * from emp where deptno=10; select * from emp where deptno<>10； select ename,job,deptno,sal from emp where sal>2000 select * from emp where sal between 1800 and 2500
条件连接运算
and ,or,not
查询不在部门，而且工资也不大于1500的员工信息
select * from emp deptno!=20 and sal<1500;
null运算nvl
--查询出所有困难获得奖金的员工信息 --查询数据 员工信息 --数据来源 emp --筛选条件 comm is not null select ename, comm from emp where comm is not null; select ename, comm from emp where comm is null; select * from emp where not comm is null; --nvl() 当某个值为空时给一个特定的的值，否则该值就为他自己 select nvl(null,100) from dual; --查询获取到奖金的员工信息，查询奖金大于0的员工信息 select * from emp where comm>0;
插入
--向emp表插入一条信息 insert into emp(empno,ename,job,deptno) values(1111,'afbi','aaa',200); commit;
模糊查询特殊处理
--查询含有‘%a’的员工 select * from emp where ename like '%a%%' escape ()
where子句
查询销售部（sales）的员工信息
select * from emp where deptno=(select deptno from dept where dname='sales') --1)from --2)where --21)from --22)where --23)select --3)select --查询工资等级2的员工 --数据 员工信息 --来源 员工表 --条件 工资等级2 select * from emp where sal between (select losal from salgrade where grade=2 ) and (select hisal from salgrade where grade=2);
函数
字符函数
concat(x,y)连接字符串x和y
instr(x,str,start,n).在x中查找str，可以指定从start开始， 也可以指定从第n次开始
length(x)返回x的长度
lower(x) x转换为小写
upper(x) X 转换为大写
ltrim(x,trim_ str)把x左边截去trim. str字符串，缺省截去空格
rtrim(x,trim_ str)把x右边截去trim_ str字符串，缺省截去空格
replace(x, old,new)在x中查找old, 并替换为new
substr(x, start,length)返回x的字符窜，从start处开始， 截取length个字符， 缺省length, 默认到结尾
concat函，select数
--查询所有员工的姓名和工种 select ename,job from emp; --连接在一起 select ename||job namejob from emp; select concat (ename,job) namejob from emp;
--找子串
--加上有一个字符串“helloworld”请找出“e”
select instr ('holloword','a') from dual;
--查看所有员工的姓名当中是否包含‘A’，如果包含，则显示位置
select ename,instr(ename,'a' from emp);
--加上有一个字符串“helloworld”请找出“l”
select instr ('holloword','l') from dual;
select instr ('holloword','l',6) from dual;
select instr ('holloword','l',1,2) from dual;
从第一个位置开始找，找第二个l的位置
ltrim去除字符串左边的空格
rtrim去除字符串右边的空格
select ltrim (' abc abc ')||'a' from dual;
select rtrim (' abc abc ')||'a' from dual;
select ltrim(rtrim (' abc abc '))||'a' from dual;
sleect concat(rtrim(ltrim(' abc abc ')),'a') from dual;
--去掉左边的aaa
select ltrim ('aaabsdbdb','aaa')from dual;
replace 替换
select replace ('aaabsdbdb','a','_')from dual;
substr 截取
select substr('abc',2,2) from dual;
--显示出所有员工的名称的第一个字符
select substr(ename,1,1) from emp;
--显示最后一个字符
select ename,substr(ename,length(ename),1) from emp;
日期函数
sysdate 当前系统时间 current_ date 返回当前系统日期，sel，selectect add_ months(d1,n1) 返回在日期d1基础上再加n1个月后新的日期 last, day(d1) 返回日期d1所在月份最后-天的日期 months, _between(d1,d2) 返回8 期d1到日期d2之间的月数 next. day(d1[c1]) 返回日期d1在下周，星期几(参数c1)的日期
--获取当前系统日期 select sysdate from dual; select current_date from dual; --获取系统后一天的时间 select sysdate+1 from dual; --跳到三个月之后,查询所有员工的转正日期，3个月试用期 select empno, ename, hiredate from emp; select empno, ename, hiredate,add_months(hiredate,3) from emp; --返回当月最后一天 select last_day(sysdate) from dual; --查看每个员工入职月的最后一天 select last_day(hiredate) 最后一天，hiredate from emp; --查询每个员工工作到现在，员工上了几个月的班 select sysdate,hiredate,months_between(sysdate,hiredate) from emp; --获取当前时间点下一个的星期几 select next_day(sysdate,'星期一') from dual; --查看每个员工的上班时间 select hiredtae,next_day(hiredate,'星期三') from dual;
转换函数
to_ char(x,c) 将日期或数据x按照c的格式转换为char数据类型 to_ date(x,c) 将字符 串x按照c的格式转换为日期 to_ _number(x) 将字符串x转化为数字型
--to_char 日期转换字符
select hiredate from emp;
select to_char (hiredate,'mm/dd/yyyy') from emp;
select to_char (hiredate,'mm"月"/dd"日"/yyyy"年"') from emp;
--to_date(chars,charFormat) 字符转换日期
--1990/01/01
select to_date('1990/01/01','yyyy/mm/dd') from dual;
--to_number() 字符转换数字
--"11"
select '11' from dual;
select '11'+1 from dual;
select to_munber('11')+1 from dual;
select to_number ('11','xx') from dual;
常用组函数
组函数同时对多条记录进行操作，并返回一个结果 avg() 平均值 sum() 求和select next_day(sysdate,'星期一') from dual; min() 最小值select next_day(sysdate,'星期一') from dual; max() 最大值 count() 统计 注意: null不参与运算
--求总人数 count()本公司的人数 select count(emptno) from emp; --所有员工最高工资 select max(sal) from emp; --最小的 select min(sal) from emp; --求和 一个月需要发出的工资 select sum(sal) from emp; --求平均 平均工资 select avg(sal) from emp; --统计10部门员工人数 select count(empno) from emp where deptno=10; --统计整个公司的comm 空不参与运算 select count(comm) fromm emp; --统计有几个部门里面有员工 select count(distinct deptno) from dept; --统计记录数 select count(*) from emp; select count(*) from dept; --统计整个公司有几个部门 select count(deptno) from dept;
group by 分组
group by分组，将满足条件的记录进一步 按照某特性进行分组。提取每组记录中的共性
--求每个部门的平均工资 select deptno, avg(sal) from emp group by deptno; --求出每个job平均工资 select job ,avg(sal) from emp group by job; --求出有员工的每个部门的人数 select count(empno) ,deptno from emp group by deptno;
having过滤组信息
要获取的组信息也许要满足一定条件
--求每个部门的平均工资，大于2000的部门 select deptno ,avg(sal) from emp group by deptno having avg(sal)>2000; --查看部门里面员工数大于3个人的部门编号和人数； select deptno,count(deptno) from emp group by deptno having count(deptno)>3;
过滤行记录和组信息
行记录的过滤是针对每条记录的筛选，组信息的过滤是针对组的筛选，是可以同时出现的，先筛选行，再过滤组。 where筛选行，只能出现行信息 having过滤组，只能出现组信息 结构: select .. from ..where .group by .. having...
执行顺序：
from
where
group by
having
select
--查询部门编号，部门里的员工数，工资大于2000的员工，部门员工数大于2的部门 select deptno,count(deptno) from emp where sal>2000 group by deptno having count(deptno)>=2;
分页和去重
实现分页的解决方案有两种: (1)一次查询出数据库中的所有记录，然后在每页中显示指定的记录。假分页 (2)对数据库进行多次查询，每次只获得本页的数据并显示 真分页
如今网站建设中的数据都是海量的，若按方案1执行:无疑会加大服务器内存的负载，降低系统运行 速度;若使用方案2执行，则可能回频繁操作数据库，也会影响响应效率;因而大家都会使用方案1+方案2来实现。 分页的核心就是计算每页多少记录和总页数以及第几页。每一页的数据则只需计算起始的记录和结束 记录即可。
真假分页
先真分页查询，后假分页
先在数据库查询到500条（真分页），然后利用假分页显示100条（假分页）
效率和占用内存较优
rownum
rownum不是一个真实存在的列，它是用于从查询返回的行的编号,返回的第一行分配的是1，第二行是2,依此类推，这个伪字段可以用于限制查询返回的总行数。 由于rownum总是结果集的编号，所以无法直接查询rownum>1的任何记录，因为总是从1开始的
--真分页 --rownum 是对每一个结果集中的每一条记录的编号 从1开始 select ename,sal,deptno,rownum from emp; --进行分页，每一页显示5条记录，查询每一个也的数据 select enmae,sal,deptno,rownum from emp where rownum<=5; --查询第二页的数据 不能使用大于，因为每一行都会变成第一行 select enmae,sal,deptno,rownum from emp where rownum<=10 ; --查询所有员工信息，并且加上伪列 rownum select enmae,sal,deptno,rownum from emp; --将查询出来的结果1集作为临时的数据来源，刚才临时的那个结果集rownum普通字段 select * from (select ename,sal,deptno,rownum from emp) where rownum<=10;
第一条不满足大于5，然后第一天相当被删除，然后第二行就变成第一行了，以此类推，到最后第十行也变成第一行。
select ename,sal,deptno,rownum from emp; select ename,sal,deptno,rownum from emp where rownum<=5; select * from (select ename,sal,deptnno,rownum rw from emp) where rw >5 and rw<=10; --查询第二页的数据 select ename,sal,deptno,rownum from (ename,sal,deptno,rownum rw from emp) where rw>5 and rw<=10; --查询员工的信息，姓名，工资，部门编号，工资降序，实现分页，每一页显示3条记录，查询第一页的数据 select ename,sal,deptno, rwonum r1 from emp order by sal desc; select ename,sal,deptno, r1, rwonum r2 from (select ename,sal,deptno,rownum r1 from emp order by sal desc); select ename,sal,deptno,r1,r2 from (select ename,sal,deptno, r1, rownum r2 from (select ename,sal,deptno,rownum r1 from emp order by sal desc)) where r2<=3; select ename,sal,deptno,r1,r2 from (select ename,sal,deptno, r1, rownum r2 from (select ename,sal,deptno,rownum r1 from emp order by sal desc)) where r2>(2-1)*3 and r2<=2*3;
rowid去重
ROWID是ORACLE中的一个重要的概念。用于定位数据库中一条记录的-一个相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一 。ROWID它是一个伪列，它并不实际存在于表中。它是ORACLE在读取表中数据行时，根据每-行数据的物理地址信息编码而成的一个伪列。所以根据一行数据的ROWID能找到一行数据的物理地址信息。从而快速地定位到数据行。数据库的大多数操作都是通过ROWID来完成的，而且使用ROWID来进行单记录定位速度是最快的。 有时繁杂的数据检索时，普通检索条件不能达到要求，可以利用rowid来精确检索的结果oracle中如果要查询某张表中多个字段，又只对某个字段去重的时候用distinct或者group by都不行。distinct和group by会对要查询的字段一起进行去重， 也就是当查询的所有字段都相同, oracle才认 为是重复的。这时用rowid是个不错的选择。
--准备数据 create table copy as select * from dept; ##拷贝一个表 select * from copy; select deptno, dname,loc,rowid from copy; insert into copy select * from dept;##插入 commit; --将所有相同的记录只保留一份 --1.将数据分组，按照重复的信息进行分组 --2.在每一组中，选取一条记录进行保存，找到特性（roeid） --3.删除时，找那些不在保留范围内的数据 select * from copy group by deptno,dname,loc; select min(rowid) from copy group by deptno,dname,loc; select * from copy where roeid not in(select min(rowid) from copy group by deptno,dname,loc); delete from copy where rowid not in (select min(rowid) from copy group by deptno,dname,loc); ##删除重复的记录 commit; select * from copy;
表连接
连接查询: 1)即查询的时候同时需要多张表(特别是存在外键关系的)，此时需要多张表之间的值进行连接; 2)目前SQL标准提出过两种连接查询，第一种是较早的SQL92标准,第二种是目前使用广泛的较新的SQL99标准;
3）92形式简单，但编写较为冗长，99不仅在底层得到优化，而且形式看.上去更加一目了然，逻辑性更强，一般建议使用99标准;
92语法
多张表需要全部放在from之后，所有的连接条件都放在where当中，因此SQL92中的等值连接、非等值链接、外连接等等其实只是whgre条件的筛选 结构: select .. from table1, table2 ,table3 .. where... 很多时候需要为表取别名(1、 简化表名2、 可能存在自连接的情况) 连接的原理:按照from后面表的出现顺序，前面的表作为内存的for循环，后出现的表作为外层的for循环
--查询emp表和dept表当中的数据(笛卡尔积)emp12条记录*dept4条记录=48 select * from emp e,dept d order by empno; select * from emp e,dept d where 1=1 order by empno;
等值连接
在笛卡尔积的基础上取条件列相同的值 查询员工信息和部门信息 查询员工姓名，部门名称
--查询每一个员工的信息以及所在的部门的信息 --数据：员工信息 部门信息 --来源：emp,dept --连接条件：emp,deptno=dept,deptno select * from emp e,dept d where e.deptno=d.deptno; --查询出每一个有员工部门的信息和部门的人数 --查询数据：部门信息，对应部门人数 --数据来源：dept,emp经过计算的 --连接条件：dept,deptno=deptno人数对应的 --查询出有员工的部门的员工人数 select count(*),deptno from emp group by deptno; select * from dept d,(select count(*),deptno from emp group by deptno) c where d.deptno=c.deptno
非等值连接
!=、>、<、<>、between and 查询员工姓名，工资及等级
--查询每一个员工的姓名，工资金额，入职时间，对应工资等级 --查询数据：ename,sal,hiredate,grade --数据来源：dept，salgrade --连接条件：emp,sal between losal and hisal select ename,sal,hiredate,grade from emp e,salgrade s where e.sal between losal and hisal;
外连接
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。 外联接可以是左向外联接、右向外联接或完整外部联接。 左向外联接的结果集包括LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。 右向外联接是左向外联接的反问联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。 看'+'，带'+'的表为从表，对立面的表为主表。 找出所有员工的姓名以及他上级的名称 找出所有上级的名称以及其手下员工的名称 找出所有部门的员工数及部门名称. 找出所有有员工的部门名称及员工数
--内连接 --查询每一个部门的信息和部门的人数 select * from dept; select * from emp; select count(*),deptno from emp group by deptno; select * from dept d,(select count(*),deptno from emp group by deptno) c where d.deptno=c.deptno; --外连接 主表（只要在主表中查询的记录，就必须出现在结果中） --查看每一个部门的信息，对应的员工数 --查询数据：部门信息，部门人数 --数据来源：dept d,(select count(*),deptno from emp group by deptno) c --连接条件：d.deptno=c.deptno(+) select * from dept d,(select count(*),deptno from emp group by deptno) c where d.deptno=c.deptno(+); --nvl 当某个值为空时给一个特定的的值，否则该值就为他自己 select d.deptno,dname,loc,nvl(cc,0) from dept d, (select count(*) cc,deptno from emp group by deptno) c where d.deptno=c.deptno(+); --列出每一个有上级的员工的信息和对应的上级信息 --列出所有的员工信息以及其上级的信息 --查询数据：员工信息，上级信息 --数据来源：emp e,emp m --连接条件:e.mgr=m.deptno(+)
自连接
特殊的等值连接(来自于同一张表) 找出存在.上级的员工姓名.
--查询出每一个员工（有上级存在的员工）自己的信息和上级的信息 --查询的数据：员工都是数据，上级的数据 --数据来源：emp e,emp m --连接条件：e.mgr=m.empno select * from emp e,emp m where e.mgr=m.empno;
99语法
cross join
交叉连接，实现笛卡尔积
--99标志中实现笛卡尔积 select * from dept cross join emp;
natural join
需要有(同名列、主外建)
自然连接，做等值连接
查询所有员工姓名及所在部门的名称
必须要有同名列，或者存在主外键关系：
--查看所有员工的名称，员工编号，所属的部门编号，部门名称 --查询数据：员工名称，员工编号，所属部门编号，部门名称 --数据来源：emp,dept --连接条件：emp.deptno=dept.deptno --99 可以使用自然连接实现 natural join select ename,empno,deptno,dname from emp natural join dept; --查询10部门的员工名称，部门名称，部门编号 select ename,deptno,dname from emp natural join dept where deptno=10
join using(同名列)
using连接，等值连接 查询所有员工姓名及所在部门的名称
必须要有同名列：等值连接
--99 可以使用using连接实现（等值） select ename,empno,deptno,dname from emp join dept using(deptno); --查询10部门的员工名称，部门名称，部门编号 select ename,deptno,dname from emp join dept using(deptno) where deptno=10;
join on
on连接，可做等值连接、非等值连接、自连接，可以解决-切连接，关系列必须要区分 查询所有员工姓名及所在部门的名称 查询所有员工的姓名，工资以及工资等级
注：同名列前是否需要加限定词，如果使用的自然连接using连接，不能加，如果不是，则需要加
--查看所有员工的姓名，编号，以及所属的部门编号，部门名称 --同名列前是否需要加限定词，如果使用的自然连接using连接，不能加，如果不是，则需要加 select e.ename,e.deptno,e.empno,d.dname from emp e join dept d on e.deptno=d.deptno; --查看每一个员工的姓名，工资，所属的部门编号，工资等级 --查询数据：员工的姓名，工资，所属的部门编号，工资等级 --数据来源;emp,slagrade --连接条件：sal between losal and hisal select ename,sal,deptno,grade from emp e join salgrade s on sal between losal and hisal; --查询30部门员工姓名，工资，部门编号，工资等级，部门名称 --查询的数据:员工姓名，工资，部门编号，工资等级， 部门名称 --数据来源: emp e, salgrade s, dept d --连接条件:sal between losal and hisal e.deptno=d.deptno select ename,sal,e.deptno,dname,grade from emp e join dept d on e.deptno=d.deptno join salgrade s on sal between losal and hisal where e.deptno=30;
outer join
外连接，有主表和从表一说 left [outer] join on left [outer] join using right [outer] join on right [outer] join using
--查询员工的编号，员工名称，员工对应的上级名称，上级编号 select e.empno 员工编号,e.ename 员工名称，e.mgr 上级编号,m.ename 上级名称 from emp e left outer join emp m on e.mgr=m.empno
表连接总结
--表连接 当查询的数据来自多于一张表时 --1.笛卡尔积 --2.等值连接 --3.非等值连接 --4.自连接 -- 92标准
select ... from table1, table2 where table1. xxItable2. xx and table1.xx=1;
--99标准
select.....from tablel cross join table2 where .. select......from table1 natural join table2 where ... ; select ...from table1 join table2 using (同名字段) where..... select....from table1 join table2 on连接条件 where... select...from table1 right outer join table2 on 连接条件where.....
全连接
select ...from tablel full join table2 on 连接条件 where...
ddl语法
ddl：数据定义语言
dml:数据操纵语言
dcl:数据控制语言
表操作
创建表
create table 表名（
字段名 类型（长度），
...其他字段...
);
修改表结构
修改表名
rename 原表名 to 新表名
rename tb_txt to tb_txt_new;
修改列名
alter table 表名 rename column 列名 to
alter table tb_txt_new rename column txtid to tid;
修改字段类型
alter table 表名 modify (字段 类型)
alter table tb_txt_new modify(tid vachar2(20))
添加列
alter table 表名 add 字段 类型
alter table table tb_txt_new add col_test_name varchar2(30)
删除列
alter table 表名 drop column 字段
alter table table tb_txt_new drop column col_test_name
删除表
drop table tb_txt_new;
PLSQL
语法：
--plsql分为三个部分：声明部分，可执行部分，异常处理部分
declare
--声明变量，游标
i integer;
begin
--执行部分
--[异常处理]
end;
set serveroutput on --打开输出选项
declare
i integer;
begin
--执行部分
DBMS.output.put_line('HELLO WORLD') --打印
--[异常处理]
end;
/
变量
PL SQL编程中常见的变量分两大类:
-
普通数据类型（char,varchar2,date,number,boolean.long）
-
特殊变量类型（引用型变量，记录型变量）
声明变量的方式为
-
变量名 变量类型（变量长度）例如：v_name varchar2(20);
普通变量
变量赋值的方式有两种：
-
直接赋值 ：=
-
语句赋值，使用select ...into...赋值：（语法select 值 into 变量）
【示例】打印人员个人信息，包括：姓名，薪水，地址
--打印人员个人信息，包括：姓名，薪水，地址
DECLARE
--姓名
V_NAME VARCHAR2(20):='张三';--声明变量直接赋值
--薪水
V_SAL NUMBER;
--地址
V_ADDR VARCHAR2(200);
BEGIN
--在程序中直接赋值
v_sal :=1580;
--语句赋值
SELECT '四川' INTO v_addr from dual;
dbms_output.put_line('姓名：'||v_name||'，薪水：'
||v_sal||'，地址:'||
v_addr);
END;
引用型变量
变量的类型和长度取决于表中字段的类型和长度
通过表名，列名%type制定变量的类型和长度，例如：
v_name emp.ename%type
【示例】查询emp表中7839员工的个人信息，打印姓名和薪水
--普通类型
--查询emp表中7839员工的个人信息，打印姓名和薪水
DECLARE
--姓名
V_NAME VARCHAR2(20);--声明变量直接赋值
--薪水
V_SAL NUMBER;
BEGIN
select ename,sal into v_name,v_sal
from emp where empno=7839;
--打印输出
dbms_output.put_line('姓名：'||v_name||'，薪水：'
||v_sal);
END;
--引用型变量
DECLARE
--姓名
V_NAME emp.ename%type;--声明变量直接赋值
--薪水
V_SAL emp.sal%type;
BEGIN
select ename,sal into v_name,v_sal
from emp where empno=7839;
--打印输出
dbms_output.put_line('姓名：'||v_name||'，薪水：'
||v_sal);
END;
记录型变量
接受表中的一整行记录
语法：变量名称 表名%rowtype 例如：v_emp emp%rowtype;
【示例】查询并打印7839号员工的姓名和薪水
--查询并打印7839号员工的姓名和薪水
DECLARE
--记录型变量
v_emp emp%rowtype;
BEGIN
select * into v_emp
from emp where empno=7839;
--打印输出
dbms_output.put_line('姓名：'||v_emp.ename||'，薪水：'
||v_emp.sal);
END;
流程控制
-
条件分支
语法：
BEGIN IF 条件1 THEN 执行1： ELSIF 条件2 THEN 执行2： ELSE 执行3 END IF; END;【示例】判断emp表中记录是否超过20条，10-10之间，或者10条以下
--判断emp表中记录是否超过20条，10-10之间，或者10条以下 DECLARE --声明变量接收emp表中的数量 v_count NUMBER; BEGIN SELECT COUNT(1) INTO V_COUNT FROM EMP; --count(1)=count(*) IF V_COUNT>20 THEN DBMS_OUTPUT.PUT_LINE ('表中记录超过20条为：'||v_count); ELSIF V_COUNT>=10 THEN DBMS_OUTPUT.PUT_LINE ('表中记录在10-20条为：''||v_count); ELSE DBMS_OUTPUT.PUT_LINE ('表中记录在10条以下为：''||v_count); END IF; END; -
循环
语法：
BEGIN LOOP EXIT WHEN 退出循环条件 END LOOP; END;【示例】打印数字1-10
DECLARE --声明循环变量并赋初值 V_NUM NUMBER :=1; BEGIN LOOP EXIT WHEN V_NUM>10; DBMS_OUTPUT.PUT_LINE(v_NUM); --循环变量自增 v_num:=v_num+1; END LOOP; END;
游标
游标：用于临时行储一个查询返回的多行数据，通过遍历数据，可以逐行访问处理改结果集的数据。
游标使用方法：声明-->打开-->读取-->关闭
语法
-
游标声明：
CORSOR 游标名[(参数列表)] is 查询语句；
-
游标的打开：
OPEN 游标名；
-
游标的取值：
FETCH 游标名 INTO 变量列表；
-
游标的关闭
CLOSE 游标名；
游标的属性
| 游标的属性 | 返回值类型 | 说明 |
|---|---|---|
| %ROWCOUNT | 整型 | 获得FETCH语句返回的数据行数 |
| %FOUND | 布尔型 | 最近的FETCH语句返回一行数据则为真，否则为假 |
| %NOTFOUND | 布尔型 | 与%FOUND属性返回值相反 |
| %ISOPEN | 布尔型 | 游标已经打开时值为真，否则为假 |
其中%NOTFOUND是在游标中找不到元素的时候返回TRUE，通常用来判断是否退出循环。
无参游标
--使用游标查询emp表中所有的员工的姓名和工资，并依次打印出来
DECLARE
--声明游标
CURSOR C_EMP IS SELECT ENAME,SAL FROM EMP;
--声明变量接收游标中的数据
v_ename emp.ename%type;
v_sal emp.sal%type;
BEGIN
--打开游标
open c_emp;
--遍历游标
loop
--获取游标中的数据
fetch c_emp into v_ename,v_sal;
--退出循环
exit when c_emp%notfound;
DBMS_OUTPUT.PUT_LINE(v_ename||'-'||v_sal);
end loop;
--关闭游标
close c_emp;
END;
带参游标
【示例】使用游标查询并打印每个部门的员工的姓名和工资，部门编号手动输入
--使用游标查询emp表中所有的员工的姓名和工资，并依次打印出来
DECLARE
--声明游标,有参与无参的区别
CURSOR C_EMP(v_deptno emp.deptno%type) IS SELECT ENAME,SAL FROM EMP where deptno = v_deptno;
--声明变量接收游标中的数据
v_ename emp.ename%type;
v_sal emp.sal%type;
BEGIN
--打开游标，有参与无参的区别
open c_emp(10);
--遍历游标
loop
--获取游标中的数据
fetch c_emp into v_ename,v_sal;
--退出循环，判断是否有值
exit when c_emp%notfound;
DBMS_OUTPUT.PUT_LINE(v_ename||'-'||v_sal);
end loop;
--关闭游标
close c_emp;
END;
fetch要在判断之前，不然就会多出现一些值，因为fetch默认是存在值的，所以会多出一个他默认的值。
存储过程
语法
CREATE OR REPLACE PROCEDURE 过程名称[(参数列表)] is BEGIN END [过程名称];
根据参数的类型，我们将其划分为3类讲解：
-
不带参数的
-
带输入参数的
-
带输入输出参数（返回值）的
无参存储过程
CREATE or REPLACE PROCEDURE p_hello is
--声明变量
BEGIN
DBMS_OUTPUT.PUT_LINE('hello world');
END P_HELLO;
BEGIN
--plsql调用存储过程
p_hello;
end;
cmd通过exec去调用即exec p_hello;
带输入参数的存储过程
【示例】查询并打印某个员工（如7839员工）的姓名和工资--存储过程；要求：调用的时候传入员工编号，诸佛那个控制台打印。
--查询并打印某个员工（如7839员工）的姓名和工资--存储过程；
--要求：调用的时候传入员工编号，诸佛那个控制台打印。
CREATE OR REPLACE PROCEDURE p_quernameandsal(i_empno IN emp.empno%type) as
--声明变量
v_name emp.ename%type;
v_sal emp.sal%type;
BEGIN
select ename,sal INTO v_name,v_sal from emp where empno =i_empno;
dbms_output.put_line(v_name||'_'||v_sal);
end ;
DECLARE
i INTEGER;
BEGIN
--plsql调用存储过程
p_quernameandsal(7839);
end;
带输出参数的存储过程
一般都是给第三方程序使用
【示例】输出员工查询某个员工（7839号员工）信息，要求将还在作为返回值输出，给调用的程序使用。
--输出员工查询某个员工（7839号员工）信息
--要求将还在作为返回值输出，给调用的程序使用。
CREATE OR REPLACE PROCEDURE p_querysal(i_empno IN emp.empno%type,o_sal OUT emp.sal%TYPE) as
--声明变量
v_name emp.ename%type;
v_sal emp.sal%type;
BEGIN
select sal INTO o_sal from emp where empno =i_empno;
end ;
DECLARE
--声明变量接收存储过程中的输出参数
v_sal emp.sal%TYPE;
BEGIN
--plsql调用存储过程
p_querysal_out(7839,v_sal);
dbms_output.put_line(v_sal);
end;
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
