Oracle初体验
1.oracle发展（了解）
层次模型 网状模型 关系型模型 对象模型
关系型模型 ：二维表来对数据进行管理
SQL Server(中型):千万级别
Oracle(大型)：11g:grid 8i 9i 10g 12g
Mysql(小型)：百万级别
优势：1.网格计算 2.高并发 3.数据安全性 4.兼容性
2.Oracle启动使用（掌握）
1.监听（XXXXListener）:必须开启
2.打开服务（oracleserviceORCL）:必须开启
scott@orcl 没有登录
3.oracle用户（记住）
Sys:超级管理员（校长）
System:管理员 修改密码 解锁 授权（老师）（orcl）
system@orcl as sysdba
Alter user scott identified by 新密码
--the accout is locked
解锁：Alter user scott account unlock
Scott：普通用户 （学生）
4.操作语言（重点）
1.数据定义语言（DDL）:create drop alter
创建，修改，删除数据库对象（表），操作的是表的结构，不是表的数据
2.数据操作语言（DML）:insert delete update ,操作是表中数据
注意：oracle事务需要手动提交
添加数据
Insert into 表名（列名，列名,..）values（值，值，....）
删除数据
Delete from 表名 【where】
更新数据
Update 表名 set 列名=值，列名=值，.... 【where】
Oracle体系结构(c/s)
1. oracle体系结构（了解）
oracle服务器：oracle实例和oralce数据库
oracle实例：oracle进程和内部结构（SGA）
oralce数据库:物理结构和逻辑结构
物理结构：组成数据库系统文件（数据文件.dbf，日志文件.log，控制文件.ctl）
逻辑结构：创建后逻辑概念之间层次关系
2. 表空间（掌握）(system)
1. system表空间
2. non-system表空间
3. 临时（temp）表空间
1. 创建自己表空间：只有管理员有权限
Create TABLESPACE 名称
Datafile ‘路径+文件名.dbf’
Size 大小；
2. 创建一个用户
Create user 用户名
Identified by 密码
Default trablespace表空间;
注意：刚创建用户没有任何权限的，授权
3. 权限（system）
Grant：授权
Revoke：取消权限
1. connect:登录权限
2. resource:创建实体（表，存储过程）
3. dba:管理员
导入：imp scott/tiger@orcl file=d:\daochu.dmp tables=(table1,table2,….)
(Fromuser= touser=)
导出:exp scott/tiger@orcl file=d:\daochu.dmp tables=(table1,table2,….)
4. 数据建模之三范式(理解)
1. 第一范式：表必须有主键，列具有原子性（不可再分）
例如:编号 姓名 移动电话，固定电话
2. 第二范式：满足第一范式，非主键列完全依赖主键，而不是主键的一部分（多个列来决定一个主键）
3. 第三范式：满足第二范式，非主键列直接依赖主键，不能间接产生依赖关系
5视图（view）
方便我们查询数据，将一个表中经常会被查询数据放到视图中，方便查询
create view emp_view
as
select ename 姓名,sal 基本薪资,comm 奖金,deptno 部门 from emp;
Oracle创建表空间
创建表空间和表
Oracle存储分层概念：
ORACLE物理上是由磁盘上的以下几种文件:数据文件和控制文件和LOGFILE构成的
oracle中的表就是一张存储数据的表。表空间是逻辑上的划分。方便管理的。
数据表空间 (Tablespace)
存放数据总是需要空间， Oracle把一个数据库按功能划分若干空间来保存数据。当然数据存放在磁盘最终是以文件形式，所以一盘一个数据表空间包含一个以上的物理文件
数据表
在仓库，我们可能有多间房子（表空间），每个房子又有多个货架（段），每架又有多层（表）。 我们在数据库中存放数据，最终是数据表的单元来存储与管理的。
数据文件
以上几个概念都是逻辑上的， 而数据文件则是物理上的。就是说，数据文件是真正“看得着的东西”，它在磁盘上以一个真实的文件体现
1.数据库serverName（scott@127.0.0.1:1521/orcl）由一个或多个表空间组成、2.表空间（tablespace）表空间由一个或多个数据文件组成，一个表空间包含多个段、3.方案（schema）user指区分不同用户空间、4.段（segment）段是oracle数据库中的分配单位，对象如表、索引等都是以段为单位进行分配，段由一个或多个区组成、5.区（extent）区是数据文件中一个连续的分配空间，由一个或多个块组成、6.块（block）块是数据库中最小、最基本的单位，是数据库使用的最小的I/O单元，这些都是oracle数据库在数据文件中组织数据的基本单元
操作创建表空间和表：
1.创建表空间
create tablespace SIRM2
datafile 'D:\oracle\product\10.2.0\oradata\orcl\SIRM2.dbf' size 1024M --存储地址 初始大小1G
autoextend on next 10M maxsize unlimited --每次扩展10M，无限制扩展
EXTENT MANAGEMENT local autoallocate
segment space management auto;
创建表空间，名称为SIRM2 ；
2. 表空间有一个数据文件*.dbf，大小为1024MB；
3. 允许表空间自动扩展(autoextends)，每次增长10MB(next 10M)，并且不限制最大大小；
4. 说明表空间本地(local)管理，并自动分配范围(autoallocate)，用户不能指定范围的大小；
5. 段空间(segment)的空间管理上使用bitmaps(auto)来管理数据块。使用AUTO会比使用MANUAL有更好的空间利用率，与效能上的提升。
在Oracle 8i以前,可以有两种选择,一种是在字典中管理(DICTIONARY),另一种是本地管理(LOCAL ),从9I开始,只能是本地管理方式.因为LOCAL 管理方式有很多优点.
在字典中管理(DICTIONARY): 将数据文件中的每一个存储单元做为一条记录,所以在做DM操作时,就会产生大量的对这个管理表的Delete和Update操作.做大量数据管理时,将会产生很多的DM操作,严得的影响性能,同时,长时间对表数据的操作,会产生很多的磁盘碎片,这就是为什么要做磁盘整理的原因.
本地管理(LOCAL): 用二进制的方式管理磁盘,有很高的效率,同进能最大限度的使用磁盘. 同时能够自动跟踪记录临近空闲空间的情况，避免进行空闲区的合并操作。
SEGMENT SPACE MANAGEMENT
磁盘扩展管理方法:
SEGMENT SPACE MANAGEMENT: 使用该选项时区大小由系统自动确定。由于 Oracle 可确定各区的最佳大小，所以区大小是可变的。
2.创建用户
create user test identified by test
default tablespace SIRM2
temporary tablespace TEMP
profile DEFAULT;
3.授权
grant dba to test;
grant connect to test;
grant resource to test;
--查看表空间
select distinct tablespace_name from dba_free_space;
--创建表空间
create tablespace XH_History_WS
datafile 'D:\app\bailongfei123\oradata\orcl\XH_History_WS.dbf'
size 120M --存储地址 初始大小120M
autoextend on next 10M maxsize unlimited --每次扩展10M,无限制扩展
EXTENT MANAGEMENT local autoallocate ---说明表空间本地(local)管理，并自动分配范围(autoallocate)，用户不能指定范围的大小
segment space management auto; --段空间(segment)的空间管理上使用bitmaps(auto)来管理数据块。使用AUTO会比使用MANUAL有更好的空间利用率，与效能上的提升
--删除表空间，包括内容和文件
drop tablespace XH_History_WS including contents and datafiles;
--创建用户
create user XH_Bai_History_WS
identified by tiger
default tablespace XH_History_WS;
-----
alter user 用户 quota unlimited on 表空间A;
alter user 用户 quota unlimited on 表空间B;
--或者放开所有表空间
grant unlimited tablespace to 用户;
--或者索性给所有权限
grant resource,connect,dba to 用户;
--查询用户
select * from all_users;
--授权
grant connect to XH_Bai_History_WS;-- --是授予最终用户的典型权利，最基本的权力，能够连接到ORACLE数据库中，并在对其他用户的表有访问权限时，做SELECT、UPDATE、INSERTT等操作
grant resource to XH_Bai_History_WS;--是授予开发人员的，能在自己的方案中创建表、序列、视图等。
grant dba to XH_Bai_History_WS;--是授予系统管理员的，拥有该角色的用户就能成为系统管理员了，它拥有所有的系统权限
--取消权限revoke
revoke connect,resource from java_user;
grant create view to java_user
--创建视图权限，一般网上找都是说的这句，但是光有这句还是无法创建
grant create view to XH_Bai_History_WS;
--授予查询权限
grant select any table to B;
--授予权限
grant select any dictionary to B;
--以上3项地后就能正常创建视图了。
5.表操作
1.创建表
Create table 表名(
列名 数据类型 【约束】,
列名 数据类型 【约束】,
....
列名 数据类型 【约束】
)
create table stu_b(
stuId int primary key,
stuName varchar2(50) not null,
age int check(age>18)
)
insert into stu_b(stuId,stuname,age) values(1,'小明',19);
insert into stu_b values(2,'张三',20);
select * from stu_b;
create table text_b(
txtId int primary key,
txtName varchar2(20) not null,
txtAge int check(txtAge>18)
)
insert into text_b values(1,'历史',20);
select * from text_b;
数据类型：
字符型：char varchar varchar2
Char:固定长度 char(10):abc 占10
Varchar2:可变（在存空字符时varchar存储的是空字符，varchar2存的是null）
数字型：number(m,n):数字的精度 n:小数点后几位 int float
时间：date
约束：非空（not null）检查（check）默认（default）唯一（unique）
主键（primary key）外键（foreign key）
注意：oracle没有主键自增，需要借助序列（sequence）例：6序列讲解
2.删除表
Drop table 表名 [purge]
Purge:表就会被彻底删除
drop table text_b purge;
3.找回删除的表
Flashback table 表名 to before drop;
flashback table text_b to before drop;
4.重命名
Rename 原来表名 to 新表名
rename stu_b to stu;
5.增加一列（修改表的结构）
Alter table 表名
Add 列名 数据类型 [约束]
alter table stu add phone varchar2(20);
6.删除一列
Alter table 表名
drop column 列名
alter table stu drop column age;
7.修改已有列的数据类型
Alter table 表名 modify 列名 修改类型
alter table stu modify phone varchar2(50);
8.列重命名
Alter table 表名
Rename column 原来列名 to 新名字
alter table stu rename column phone to phones;
9.查询select
Emp:雇员表 dept：部门表
1. 基本查询
Select * from 表名
说明：*代表所有的列，直接写列名
2. 模糊查询：like
查询条件不精确，通过关键字进行查询
%：0或n个字符
_：1个字符
select * from emp where ename like '%A%' order by empno desc;
select * from emp where ename like '_L%';
select sum(sal) from emp group by deptno;
select * from emp;
select * from dept;
3. 分组查询：group by
一般聚合函数（sum,avg,max,min,count），统计
注意：1.聚合函数 2.分组列名
Order by ：排序（升序，降序 desc）
4. 多表联合查询:两张以上表联合查询，主外键关系
注意：避免笛卡尔积，加上主外键约束关系
1. Where
select empno,ename,sal,dname,loc from emp,dept where emp.deptno=dept.deptno;
2. 内连接 inner join
select empno,ename,sal,dname,loc from emp inner join dept on emp.deptno=dept.deptno
3. 外链接 ：left join right join full join
主表，附表：主表数据全部显示。，附表匹配主表进行显示
主表有的列附表没有，附表以空格的形式填充
主表没有附表有的列，附表的列就不在显示
5. 子查询
查询语句嵌套了查询语句
注意:子查询必须加上（）
相关子查询：子查询不可以脱离父查询而单独执行
先执行父查询，子查询利用父查询的列执行查询，父查询在利用子查询返回的结果作为查询条件
非相关子查询（独立子查询）：先执行子查询（内查询），将子查询的结果父查询（外查询）的条件,子查询都可以脱离父查询而单独执行
1. 单行子查询:子查询返回的结果一个
运算符：>,<,>=,<=,=,<>
2. 多行子查询:子查询返回的结果又多个值
运算符：in, all, any，not in
/**
查询工资比Allen高的(非相关子查询)
*/
select sal from emp where ename='ALLEN';
select * from emp where sal>(select sal from emp where ename='ALLEN');
--和Allen同部门
select deptno from emp where ename='ALLEN';
select * from emp where deptno=(select deptno from emp where ename='ALLEN');
--比平均工资高的
select avg(sal) from emp;
select * from emp where sal>(select avg(sal) from emp);
--每部门工资最低的员工信息
select min(sal) from emp group by deptno
--多行子查询 in all any notin
select * from emp where sal in (select min(sal) from emp group by deptno);
--查询工资高于所有部门的平均工资
select avg(sal) from emp group by deptno
select * from emp where sal>all(select avg(sal) from emp group by deptno);
--查询工资高于任意部门的平均工资
select * from emp where sal>any(select avg(sal) from emp group by deptno);
--查询工资高于本部门平均工资的所有员工（相关子查询）
select * from emp e where sal>(select avg(sal) from emp where deptno=e.deptno);
--
select count(*),deptno from emp group by deptno
6. 分页查询 rownum(伪列)
Rownum从1开始执行
select rownum r,deptno,dname,loc from dept
select * from (select rownum r,deptno,dname,loc from dept) where r>2 and r<5;
6.序列（掌握）
在oracle中sequence就是序号，每次取的时候它会自动增加。sequence与表没有关系
Create sequence 名称(seq_表名)
注意：需要先执行一次nextval,才能执行currval
--简单创建序列
create sequence seq_stu start with 2;
select seq_stu.nextval from dual;
--借助序列实现主键自增
insert into stu values(seq_stu.nextval,'张珊珊','126743453','上海');
操作数据：
delete from stu where stuId=2;
update stu set stuName='丽水'，phones='13478564' where stuId=3;
序列字段说明
create sequence SEQ_LOG_ID
minvalue 1 --增长最小值
maxvalue 9999999999 --增长最大值,也可以设置NOMAXvalue -- 不设置最大值
start with 101 --从101开始计数
increment by 1 --自增步长为1
cache 50 --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---NOCACHE防止跳号
cycle; --循环,当达到最大值时,不是从start with设置的值开始循环。而是从1开始循环
使用
insert into 表名(id,name)values(seqtest.Nextval,'sequence 插入测试');CurrVal：返回 sequence的当前值 NextVal：增加sequence的值，然后返回 增加后sequence值
注意：第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的INCREMENT BY值，然后返回增加后的值。
CURRVAL 总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。
一次NEXTVAL会增加一次 SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。- 如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，oracle自动再取一组 到cache。 使用cache或许会跳号， 比如数据库突然不正常down掉（shutdown abort),cache中的sequence就会丢失. 所以可以在create sequence的时候用nocache防止这种情况。
7.创建视图
create view user_view
as
select stuName 员工姓名,stuAge 员工年龄 from userStu;
select * from user_view
8.函数
--字符码—返回字符对应十进制
select ASCII('我爱你') from dual;
select chr(52946) from dual;--参数为整数表示unicode码，返回对应的字符
--链接concat—
--concat链接两个字符串
select concat('0371-','4265324532') from dual;
--||连接符
select '0371-'||'3644532' from dual;
select concat('0319-','45836')||'转2465239' 电话码 from dual;
--首字母大写
--initcap返回字符串将其第一个字母大写，其余变小写
select initcap('hello') from dual;
--全大写
--upper返回字符串，并将其所有大写
select upper('hello') from dual;
--全小写
--lower返回字符串，并将其所有小写
select lower('HELLO') from dual;
--查找出现位置
--instr(备查字符串，要查找字符串，查找起始位置，第几次出现)
select instr('411421199603026845','1',1,3) from dual;
--补充
--lpad(原字符串，补充到达个数，补充的字符串)
select lpad('gao',10,'*#') from dual; --lpad在列的左边粘贴字符
select rpad('gao',7,'#@') from dual; --rpad在列的右边粘贴字符
select ltrim(' ltrim') from dual; --删除左边出现的字符 如空格
select rtrim('ltrimr','rm') from dual; --删除右边的字符串
--trim(type ‘字符1’ from ‘字符2’)从字符2两边开始删除字符1，如果前两个参数和from省略从字符2删除两边，type:删除方式（leading:从左边开始删除；trailing:从右边开始删除；both:默认两边删除）
select trim(leading '=' from '=trim=') from dual; --删除字符串leading左边的字符串默认两边
select trim(trailing '=' from '=trim=') from dual;--- trailing右边删除
--截取substr
--substr(字符串，起始位置，截取个数)
select substr('123843564',3,5) from dual;
--替换
select replace('全能就是好','全能','切糕') from dual;
--日期函数
--sysdate系统当前日期
Select sysdate,to_char(sysdate,’dd-mm-yyyy day’) from dual;
select sysdate+14 from dual;
--add_months增加或减去月份
select add_months(sysdate,+1) from dual;
-- month_between(date2,date1)给出date2和date1相差月份
select month_between(sysdate,hiredate),ename from emp;
--
select last_day(sysdate) from dual;
--下一个
--next_day(date,’day’)当前data下个星期的日期
select next_day(sysdate,'星期三') from dual;
--舍取小数
select abs(-10) from dual; --取正
select ceil(3.006) from dual; --ceil向上取值
select floor(3.9999) from dual; --floor向下取值
select round(2.65) from dual; --四舍五入
--trunc 截断
select trunc(3.34523,2) from dual; --截断结果3.34
select trunc(sysdate,'dd') from dual;--截断到dd天
--mod取余
select mod(9,4) from dual;
--求次方power
select power(2,2) from dual;
--开平方 sqrt
select sqrt(4) from dual;
--to_char类型转换
select to_char(sysdate,'yyyy/mm/dd') from dual;
--将数字转换字符串--点可以用D代替
select to_char(123.4,'999.9')+100 from dual; --转换成指定类型999.9
select to_char(1273494,'999,999,999')from dual;
---将字符串转换时间
select to_date('2019-03-20','yyyy-MM-dd') from dual;
--to_number(string,numeric)
--查询奖金不为空的员工数量 count不统计空值
select count(comm) from emp;
-- nvl,nvl2代替空值
--nvl(expr1,expr2)如果expr1为null返回expr2,不为null返回expr1, 注意expr1,expr2两者类型要一致
--nvl2(expr1,expr2,expr3) 如果expr1不为null返回expr2,为null返回expr3,如果expr2和expr3类型不同，expr3会转换为expr2类型。
----员工工资（基本+奖金(有奖金的返回奖金，没有0)）
select sal+nvl(comm,0) from emp;
select sal+nvl2(comm,comm,0) from emp;
--nullif
比较 expr1和 expr2 的值，若二者相等，则返回 NULL,否则返回 expr1的值其中 expr1不能为 NULL
select nullif(3000,2000) from dual;
--plsql代码块
--定义变量v_ename varchar2(20);
--定义常量v_pi constant number(6,2):=3.14
declare--定义部分
v_ename varchar2(20);
begin—执行
--通过编号得到用户名
select enam into v_enam from emp where empno=&eno;-- &eno输入符号加载体
exception –异常处理部分
when no_data_fount then
dbms_output.put_line(‘用户名不存在！’);
end;--结束
declare
v_ip constant number(6,2):=3.14;
v_r number(1):=2;
v_area number(6,2);--1.数据类型 2.精度
begin
v_area:=v_pi*v_r*v_r;
dbms_output.put_line(‘面积为:’||v_area);
end;
--自动匹配变量类型
--into字句:在plsql块中查询出的数据必须借助变量输出 into 给v_emp变量
declare
v_emp emp.ename%type;
begin
select ename into v_emp from emp where empno=&eno;
dbms_output.put_line(v_emp);
end;
--rowtype匹配一行
declare--定义部分--rowtype匹配一行
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=&eon;
dbms_output.put_line(v_emp.ename||v_emp.sal);
end;
----table匹配整表
declare
--定义table类型
type v_emp_type is table of emp%rowtype
index by binary_integer;--通过索引一行行存储
--定义变量
v_emp v_emp_type;
begin
select * into v_emp(0) from emp where empno=7369;
select * into v_emp(1) from emp where empno=7499;
dbms_output.put_line(v_emp(0).ename||v_emp(1).ename);
end;
----record:自定义
declare
type v_emp_recordtype is record(--定义想要的数据
ename emp.ename%type,
total_sal number(6) --不在表里类型也可以自定义
);
v_emp v_emp_recordtype;
begin
select ename,sal+nvl(comm,0) into v_emp from emp where empno=&eno;
dbms_output.put_line('实发工资:'||v_emp.total_sal);
end;
---流程控制语句 （分支)(循环）
--分支if-eals(区间) switch-case
--oracle --if then end if; case when then else end case;
--if then end if;
--plsql块
declare
---输入
v_s number;
begin
v_s:=&s;
if v_s<60 then
dbms_output.put_line('不及格');
else
dbms_output.put_line('及格');
end if;
end;
---if then elseif else end if
declare
v_s number;
begin
v_s:=&s;
if v_s<60 then
dbms_output.put_line('不及格');
elsif v_s>=60 and v_s<70 then
dbms_output.put_line('及格');
elsif v_s>=70 and v_s<90 then
dbms_output.put_line('良好');
elsif v_s>=90 and v_s<=100 then
dbms_output.put_line('优秀');
else
dbms_output.put_line('输入有误！');
end if;
end;
--------------------------------
declare
v_empno emp.empno%type;
v_sal emp.sal%type;
v_comm emp.comm%type;
begin
v_empno:=&eno;
select sal,comm into v_sal,v_comm from emp where empno=v_empno;
if v_comm is null then
update emp set comm=v_sal*0.1 where empno=v_empno;
elsif v_comm<1000 then
update emp set comm=1000 where empno=v_empno;
else
update emp set comm=v_comm+v_comm*0.1 where empno=v_empno;
end if;
end;
select * from dept;
--case when then else end case
declare
v_deptno dept.deptno%type;
begin
v_deptno:=&deo;
case v_deptno
when 10 then dbms_output.put_line('纽约');
when 20 then dbms_output.put_line('达拉斯');
when 30 then dbms_output.put_line('芝加哥');
when 40 then dbms_output.put_line('波士顿');
else dbms_output.put_line('....');
end case;
end;
-------------------case2
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=&emo;
case
when v_sal<2000 then dbms_output.put_line('A级别工资');
when v_sal>=2000 and v_sal<3000 then dbms_output.put_line('B级别工资');
else dbms_output.put_line('C级别工资');
end case;
end;
select * from dept;
-----------------
/***循环
1.loop 2.while 3.for
*/
--loop exit when end loop
--loop
循环体
exit 【when】;--退出出口
end loop;
declare
--定义table类型
type v_dept_table is table of dept%rowtype
index by binary_integer;
---定义变量
v_dept v_dept_table;
---定义循环变量
i number:=0;
begin
loop
select * into v_dept(i) from dept where deptno=(i+1)*10;
dbms_output.put_line('编号:'||v_dept(i).deptno||'名称'||v_dept(i).dname||'地址'||v_dept(i).loc);
i:=i+1;
exit when i=4;
end loop;
end;
-----while I loop end loop
--while 表达式 loop 循环体 end loop;
declare
type v_dept_table is table of dept%rowtype
index by binary_integer;
v_dept v_dept_table;
i number:=0;
begin
while i<4 loop
select * into v_dept(i) from dept where deptno=(i+1)*10;
dbms_output.put_line('编号:'||v_dept(i).deptno||'名称'||v_dept(i).dname||'地址'||v_dept(i).loc);
i:=i+1;
end loop;
end;
-------for I in 0..number loop end loop
--for 循环变量 i in 初始表达式..终止表达式 loop 循环体 end loop;
declare
type v_dept_table is table of dept%rowtype
index by binary_integer;
v_dept v_dept_table;
begin
for i in 0..3 loop
select * into v_dept(i) from dept where deptno=(i+1)*10;
dbms_output.put_line('编号:'||v_dept(i).deptno||'名称'||v_dept(i).dname||'地址'||v_dept(i).loc);
end loop;
end;
---异常处理---
begin
insert into dept values(10,'aaa','bbb');
exception
when dup_val_on_index then
dbms_output.put_line('aaaaaa');
dbms_output.put_line('其他');
end;
---自定义异常
declare
my_exception exception;
begin
delete from emp where empno=&eno;
if sql%notfound then
raise my_exception;--raise引发
end if;
exception
when my_exception then
dbms_output.put_line('编号不存在！');
end;
---事务---
create table bankjs(
jsId int primary key,
jsMonny varchar2(20) not null
);
create table bankny(
nyId int primary key,
nyMonny varchar2(20) not null
);
insert into bankjs values(1,'100');
insert into bankny values(1,'100');
select * from bankny;
select * from bankjs;
declare
i number:=1;
begin
update bankjs set jsMonny=jsMonny+100 where jsId=1;
i:=i/0;
update bankny set nyMonny=nyMonny-100 where nyId=1;
commit;--提交
exception
when zero_divide then
rollback;--回滚
end;
---存储过程-----
--1,保存数据库中，针对相同的操作，下次再次使用不用重新编译
--2，预编译：sql--先编译—在执行
/*变量声明块：紧跟着的as (is )关键字，可以理解为pl/sql的declare关键字，用于声明变量。 */
在存储过程（PROCEDURE）和函数(FUNCTION)中没有区别，在视图（VIEW）中只能用 ，在游标(CURSOR)中只能用IS不能用AS。
--不带参
--编译的过程：并没有执行修改的操作，只是对要执行的操作进行一个语法解析等操作
create or replace procedure ifrst_procedure
is---声明
begin
--要执行的参数
update emp set comm=nvl(comm,0)+300;
end;
select * from emp;
--调用执行过程：这个时候才真正的执行
begin
ifrst_procedure;
end;
--带输入参数
--默认不写是in 入参
create or replace procedure text_in(v_empno in number)
is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=v_empno;
dbms_output.put_line(v_sal);
end;
begin
text_in(7369);
end;
--带输入参数输出参数
create or replace procedure text_inout(v_empno in number,v_sal out number)
is
begin
select sal into v_sal from emp where empno=v_empno;
end;
declare
v_salout emp.sal%type;
begin
text_inout(7369,v_salout);
dbms_output.put_line(v_salout);
end;
--入参数输出参数 in out-----
--in out ：在执行时先作为输入参数使用，在作为输出参数
create or replace procedure tet_inout(v_inout in out number)
is
begin
select sal into v_inout from emp where empno=v_inout;
end;
-----
create or replace procedure test_inout(in_out in out number)
is
begin
select sal into in_out from emp where empno=in_out;
end;
----执行
declare
a number;
begin
a:=&a;
--test_inout(a);
tet_inout(a);
dbms_output.put_line(a);
end;
---存储过程添加---
create or replace procedure insert_data(v_dempno number,v_dname varchar2,v_loc varchar2)
is
begin
insert into dept values(v_dempno,v_dname,v_loc);
end;
--执行
begin
insert_data(50,'部门','地址');
end;
select * from dept;
----传参关联付=>
--传参方式：位置，名称，组合。
--形参和实参关联。传递参数可以不按顺序-名称传递
begin
insert_data(v_dname=>'部门1',v_loc=>'地址1',v_dempno => 60);
end;
--------函数---------
--函数-先编译：返回特定数据，函数肯定会给我们一个数据
--不带参function return
create or replace function my_func
return number –返回随机数
is
v_num number; --接受产生的随机数并返回
begin
v_num:=floor(dbms_random.value(1,10));--产生随机数
return v_num; --注意:至少有一条return语句
end;
--执行
declare
a number;
begin
a:=my_func;
dbms_output.put_line(a);
end;
--输入输出function in out return var
create or replace function my_funout(v_empno in number,v_dname out varchar2)
return varchar2
is
v_loc dept.loc%type;
begin
select dname,loc into v_loc,v_dname from dept,emp where dept.deptno=emp.deptno and emp.empno=v_empno;
return v_loc;
end;
declare
v_loc dept.loc%type;
v_dname dept.dname%type;
begin
v_loc:=my_funout(7369,v_dname);
dbms_output.put_line(v_loc);
dbms_output.put_line(v_dname);
end;
--包规范-- package
create or replace package my_package
is
pi constant number(10,7):=3.1415926;--定义常量 关键词constant :=赋值符
function getarea(ridus number) return number;--定义函数
procedure print_area;--定义过程
end my_package;
---包体-- package body
create or replace package body my_package
is
v_area number;
--实现函数
function getarea(ridus number)
return number
is
begin
v_area:=pi*ridus*ridus;
return v_area;
end;
--实现过程
procedure print_area
is
begin
dbms_output.put_line(v_area);
end;
end my_package;
------调用包.----
declare
area number;
begin
area:=my_package.getarea(4);
dbms_output.put_line(area);
my_package.print_area;
end;
--序列--
create sequence cc
start with 5
increment by 2;
select cc.currval from dual;
--------过程---------
create or replace procedure v_dept_output(v_empno in out number)
is
begin
select sal into v_empno from emp where empno=v_empno;
end;
declare
empno_sal number;
begin
empno_sal:=&编号;
v_dept_output(empno_sal);
dbms_output.put_line(empno_sal);
end;
----函数 return out—
/**
过程和函数相同点：1，先编译，再执行
2，编译直接保存在数据库中
3，带参数，参数类型相同
不同点：1,语法，函数:function 过程:procedure
3. 函数有返回值，return
什么时候使用：多个值或者不返回值用过程procedure
特定值使用函数function
**/
---通过函数输出两个数据：return 1个 利用out参数
--通过多表联合通过编号 部门 地址
create or replace function my_fun1(v_empno in number,v_loc out varchar2)
return varchar2
is
v_dname dept.dname%type;
begin
select dname,loc into v_dname,v_loc from emp,dept where dept.deptno=emp.deptno and empno=v_empno;
return v_dname;
end;
---
declare
v_dname dept.dname%type;
v_loc dept.loc%type;
begin
v_dname:=my_fun1(7369,v_loc);
dbms_output.put_line(v_loc);
dbms_output.put_line(v_dname);
end;
-----包规范----
----定义一些共有的组件，没有实现体
create or replace package my_packages1
is
ip constant number:=3.1415926;
function getarea(ridus in number) return number;--函数只用定义部分没有实现体
procedure print_area; --过程只有定义部分
end my_packages1;
----包体-----
--包体具体执行部分，是实现包规范
create or replace package body my_packages1
is --is里定义全局变量
v_area number;
---实现包规范
--实现函数
function getarea(ridus in number)
return number
is
begin
v_area:=ip*ridus*ridus;
return v_area;
end;
---实现过程
procedure print_area
is
begin
dbms_output.put_line(v_area);
end;
end my_packages1;
-----调用执行：包名.----
declare
var_arae number;
begin
var_arae:=my_packages1.getarea(5);
dbms_output.put_line(var_arae);
my_packages1.print_area;
end;
select * from emp;
--通过查询字典USER_SOURCE,可显示当前子程序及源码
Select text from user_source where name=’pack_util’;
--删除子程序
Drop procedure proc_name;
--创建索引—create index table on column
create index emp_index on emp(deptno);
select * from emp where emp.deptno='20'
--------------
--创建包规范—游标处理结果集相当java类接口
create or replace package testProduct
is
type cursorType is ref cursor;--定义一个游标变量oracle分配内存处理结果集
end testProduct;
--过程
create or replace procedure testProcedure(userId in number,userList out testProduct.cursorType)
is
begin
if userId=null or userId='' then
open userList for select * from userinfo;
else
open userList for select * from userinfo u where u.userid=userId;
end if;
end;
--申明包结构
create or replace package atii.mypackage as
type mycursor is ref cursor;
procedure queryCount(startDate in date,endDate in date,countList out mycursor);
end mypackage;
--创建包体
create or replace package body atii.mypackage as
procedure queryCount(startDate in date,endDate in date,countList out mycursor)
as
begin
open countList for
select t.createDate,count(t.createDate)
from
(select case when createtime>=(trunc(createtime)+18/24) then trunc(createtime)+1
else trunc(createtime) end createDate
from t_count ) t
where t.createDate>=to_date('2017-08-19','yyyy-MM-dd') and t.createDate<=to_date('2017-08-24','yyyy-MM-dd')
group by t.createDate
order by t.createDate;
end queryCount;
end mypackage;
----
create or replace procedure PRC_STAT_LOGIN
IS
v_username users.username%TYPE;
v_password USERS.PASSWORD%TYPE;
v_userlevel USERS.USER_LEVEL%TYPE;
begin
--声明游标 查询出每个每个用户的信息
declare cursor cursor_login is select * from users ;
--cursor cursor_name is select username from users;
begin
if not cursor_login%isopen then
open cursor_login;
end if;
loop
FETCH cursor_login INTO v_username,v_password,v_userlevel;
EXIT WHEN cursor_login%NOTFOUND;
--正常登录，返回”成功登陆”
DBMS_OUTPUT.PUT_LINE('Login successfully!');
IF v_username is NULL THEN--如用户名不存在，返回”用户名不存在”
DBMS_OUTPUT.put_line('The user is not existed!');
ELSE
IF v_userlevel = 'U' THEN--如用户名、密码都正确，但是级别不够，管理员是A，一般用户是U，那么返回”级别不够”
DBMS_OUTPUT.put_line('Low level!') ;
END IF;
end if;
end loop;
exception
when NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('No data found!');
when LOGIN_DENIED THEN --如用户名存在，密码错误，返回”密码错误”
DBMS_OUTPUT.PUT_LINE('PASSWORD ERROR');
close cursor_login;
end;
end PRC_STAT_LOGIN;
-------游标使用---------
游标分为两种：1.隐含游标用于处理select into 和DML语句
2.显示游标用于select语句返回多行数据
Begin
Delete from emp where empno=7369;--DML
IF sql%notfound then –被隐含创建了
dbms_output.put_line(‘sql起作用了’);
end if;
end;
使用显示游标语法：
1， 定义游标CURSOR cursor_name IS select_statement;
2， 打开游标OPEN cursor_name;
3， 提取数据 FETCH cursor_name INTO variable1,variable2;
4， 关闭游标 CLOSE cursor_name;
--游标：oracle分配的一块内存，通过指针针对每一行单独处理
--游标一行一行提取数据
declare
type v_cursor is ref cursor;--定义游标
test_cursor v_cursor;
test_table test_user_info%rowtype;
begin
--打开游标
--执行查询，将结果集放在内存中，等待提取，每次提取一行。
open test_cursor for select * from test_user_info;
loop –循环提取
fetch test_cursor into test_table;
exit when test_cursor%notfound;
dbms_output.put_line(test_table.user_id||'-'||test_table.user_name||'-'||test_table.sex);
end loop;
close test_cursor;--关闭游标，释放内存
end;
--------------table-------------一次性提取
declare
cursor emp_cursor is select * from emp;
type test_emp_table is table of emp%rowtype
index by binary_integer;
v_emp test_emp_table;
begin
open emp_cursor;
fetch emp_cursor bulk collect into v_emp; ----一次性全部提取到表结构中
close emp_cursor; --关闭游标
for i in v_emp.first..v_emp.last loop
dbms_output.put_line(v_table(i).user_id||'-'||v_table(i).user_name||'-'||v_table(i).sex);
end loop;
end;
------------------定义参数游标---------------------
declare –部门20员工
cursor emp_cursor(var_deptno number) is
select * from emp where deptno=v_deptno;
v_emp emp%rowtype;
begin
open emp_cursor(20);
loop
fetch emp_cursor into v_emp;
exit when emp_cursor%notfound;
dbms_output.put_line(‘编号’||v_emp.empno||’名称’v_emp.ename);
end loop;
close emp_cursor;
end;
-------------部门信息---------------
--部门编号对应员工信息
declare
--部门游标
cursor dept_cursor is
Select * from dept;
v_dept dept%rowtype;
--emp游标
cursor emp_cursor(v_deptno number) is
select * from emp where deptno=v_deptno;
begin
open dept_cursor;
loop
fetch dept_cursor into v_dept;
exit when dept_cursor%notfound;
dbms_output.put_line(‘部门编号’||v_dept.deptno);
open emp_cursor(v_dept.deptno)
loop
fetch emp_cursor into v_emp;
exit when emp_cursor%notfound;
dbms_output.put_line(‘ 员工编号’||v_emp.empno||’名称’v_emp.ename);
end loop;
close emp_cursor;
end loop;
close dept_cursor;
end;
----简化操作-----
--游标for循环简化游标处理，当使用for循环时，oracle会隐含的打开游标，提取数据，关闭游标
declare
cursor emp_cursor is
select * from emp;
begin
for emp_row in emp_cursor loop
dbms_output.put_line('编号'||emp_row.empno||'名字'||emp_row.ename);
end loop;
end;
---------当时使用游标for循环，可以直接使用子查询-----
begin
for emp_row in (select * from emp) loop –使用子查询当做内容
dbms_output.put_line('编号'||emp_row.empno||'名字'||emp_row.ename);
end loop;
end;
----游标变量-----
Declare
--游标类型
type cursor_type is ref cursor;
--定义变量
cursor_emp cursor_type;
v_emp emp%rowtypw;
begin
--打开游标
Open cursor_emp for select * from emp;
loop
fetch cursor_emp into v_emp;
exit when emp_cursor%notfound;
dbms_output.put_line(‘ 员工编号’||v_emp.empno||’名称’v_emp.ename);
end loop;
end;
--系统动态游标SYS_REFCURSOR的使用---
-- 创建表
create table test_user_info(
user_id integer primary key,--primary key
user_name varchar2(20),
sex varchar2(2)
);
-- 插入测试数据
insert into test_user_info(user_id,user_name,sex) values(1,'小明','M');
insert into test_user_info(user_id,user_name,sex) values(2,'小美','F');
insert into test_user_info(user_id,user_name,sex) values(3,'小美','w');
insert into test_user_info(user_id,user_name,sex) values(4,'小美','q');
commit;
-- 查数
select * from test_user_info;
---- 创建过程取数
create or replace procedure test_SysCursor(p_cursor out sys_refcursor)
is
begin
open p_cursor for select * from test_user_info;
end;
---sys_refcursor入参返结果集
create or replace procedure inout_SysCursor(tname Nvarchar2,out_resultSet out sys_refcursor)
is
begin
open out_resultSet for select * from test_user_info where user_name=tname;
end;
--测试
declare
v_cursor sys_refcursor;
type type_table is table of test_user_info%rowtype
index by binary_integer;
v_table type_table;
--su varchar2(20);
begin
inout_SysCursor('小美',v_cursor);
fetch v_cursor bulk collect into v_table;
for i in v_table.first..v_table.last loop
dbms_output.put_line(v_table(i).user_id||'-'||v_table(i).user_name||'-'||v_table(i).sex);
end loop;
end;
----测试1
declare
v_cursor sys_refcursor;
u test_user_info%rowtype;
begin
test_SysCursor(v_cursor);
--loop fetch v_cursor into u.user_id, u.user_name,u.sex;
loop
fetch v_cursor into u;
exit when v_cursor%notfound;
dbms_output.put_line(u.user_id||'-'||u.user_name||'-'||u.sex);
end loop;
end;
---测试2
declare
v_cursor sys_refcursor;
type test_table is table of test_user_info%rowtype
index by binary_integer;
v_table test_table;
begin
test_SysCursor(v_cursor);
fetch v_cursor bulk collect into v_table;--一次性全部提取
for i in v_table.first..v_table.last loop
dbms_output.put_line(v_table(i).user_id||'-'||v_table(i).user_name||'-'||v_table(i).sex);
end loop;
end;
---普通动态游标的创建
declare
type rc is ref cursor; -- 定义类型
cursor c is
select * from dual; -- 普通静态游标
r_cursor rc; -- 普通动态游标
sr_cursor sys_refcursor; -- 系统动态游标
begin
if (to_char(sysdate, 'mi') >= 40) then
-- ref cursor with dynamic sql
open r_cursor for 'select * from dim_employee';
open sr_cursor for 'select * from dim_org_dept';
elsif (to_char(sysdate, 'mi') <= 20) then
-- ref cursor with static sql
open r_cursor for select * from dim_org_dept;
open sr_cursor for select * from dim_employee;
else
-- ref cursor with static sql
open r_cursor for select * from dual;
open sr_cursor for select * from dual;
end if;
-- the "normal" static cursor
open c;
end;
----触发器---
1.DML触发器—在对数据库DML操作触发，并且可以对每一行或者语句操作上进行触发
2.替代触发器—专门为试图操作的一种触发器
3,系统触发器—对数据库系统事件进行触发，如启动关闭
--触发器组成
1.触发事件—DML或者DDL语句
2.触发时间，是在触发之前(before)还是之后（aftre）
3.触发操作—使用PL/sql
4.触发对象—表，视图，模式，数据库
5.触发频率，定义执行次数
--触发器调用过程，只能包含DML
DML触发器
----事件触发器
-----星期天时不能对emp进行修改操作（insert,delete,update）
Create or replace trigger tri_no_sun
Before insert or update or delete --在修改，添加，删除之前触发
On emp –在哪个表触发
Begin –被触发执行的操作
If to_char(sysdate,’day’) in (‘星期日’) then
--raise_application_error(-20000,‘今天不能修改emp表’);
case
when inserting then –条件谓词使用
raise_application_error(-20000,‘今天不能添加emp表’);
when updateing then
raise_application_error(-20001,‘今天不能修改emp表’);
when deleteing then
raise_application_error(-20002,‘今天不能删除emp表’);
end caes;
End if;
End;
测试
Delete from emp where empno=7369;
-----失效触发器
Alter trigger tri_on_sun disable;
--启动
Aletr tigger tri_on_sun enable;
--删除
Drop trigger tri_on_sun;
-----行级触发器-fo reach row-----
--当降低部门30工资触发
Create trigger no_sql
Before update of sal,comm or delete
On emp
for each row –行级触发器
when(old.deptno=30)
begin
caes
when updateing(‘sal’) then
if :new.sal<:old.sal then
raise_application_error(-20002,‘这个部门工资不能降低’);
end if;
when updateing(‘comm) then
if :new.comm<:old.comm then
raise_application_error(-20000,‘这个部门奖金不能降低’);
end if;
when deleteing then
raise_application_error(-20001,‘这个部门不能删除’);
end;
-----after触发器执行DML之后触发----
---级联更新after触发---
Create trigger case_update
After update of deptno –修改emp表之后触发
On dept
For each row
Begin
Update emp set deptno=:new.deptno where deptno=:lod.deptno;
End;
Update dept set deptno=50 where deptno=20;
------数据的备份-----
--删除一个表时，将被删除的数据通过过程添加到一个回收表，回收表在删除时被触发调用
--1.创建一个回收表，和被删除的表一样
Create table deldept(
Deptno number(7),
Dname varchar2(30),
Loc varchar2(40)
);
--2,创建过程
Create or replace procedore add_deldept(v_deptno number,v_dname varchar2,v_loc varchar2)
Is
Begin
Insert into deldept values(v_deptno,v_dname,v_loc);
End;
--触发器调用过程
create or replace trigger beifen_dept
after delete –在删除dept行之后触发
on dept
for each row –行级触发
begin
add_deldept(:old,deptno,:old,bname,:old.loc);--存储旧参数
endl
delete from dept where deptno=40;
替代触发器
--不能在复杂视图上执行DML操作，必须基于视图创建instead of 触发器
注意：只能适用于视图，不能指定before和after,必须指定for each row,只能视图上创建
Create or replace view emp_view
As
Selet deptno,count(*) total_employeer,sun(sql) total_sal from emp group by deptnol
--删除不成功
Delete from emp_view where deptno=20;
Create or replace trigger view_tri
Instead of delete
On emp_view
For each row
Begin
dbms_output.put_line(‘替代触发器创建成功’);
End;
浙公网安备 33010602011771号