Oracle培训笔记

Oracle
----------
scott
 
connect conn
用法：

disconnect disc //断开当前链接
exit //退出数据库

交互式命令
& 可以代替变量，而且该变量在执行的时候，需要用户输入
edit 可以编辑指定的sql脚本
@ start 运行sql脚本
spool 可以将sql*plus屏幕上的内容输入的指定的文件中
      用法：spool f:\test1.sql
            ..........(输入的脚本)
            spool off
【spool f:\test1.sql .....  spool off】
显示和设置环境变量
1、linesize：设置显示行的宽度，默认是80个字符
2、pagesize:设置每页显示行的数据
用法：set linesize/pagesize 大小（800）
【set linesize 500 / set pagesize 600】

/  重复执行上一条语句的操作

查询表结构
desc tablename
select {distinct} *|具体的列名 {as}别名 from 表名
//distinct 删除重复的行

限制查询
select {distinct} *|具体的列名 {as}别名 from 表名 where

||  字符串的连接

四则运算（+-*/）
比较运算（= > < >= <= != <>） //!= <> 不等与

空值：
is null / is not null

逻辑运算符
and or not // 优先级别 not and or

在sql中有个指定范围的语句： between...and(包括最小值和最大值)

指定范围查询：in
语法：字段名in（值1，值2，值3）

模糊查询：使用like 语句
两种通配符
% //可以匹配任意长度的内容
_ //可以匹配一个长度的内容
语法： 字段名 like（not like） + 字符串

排序
select {distinct} *|具体的列名 {as}别名 
from 表名 
where 条件
order by 排序字段 asc|desc,排序字段 asc|desc //asc-升序

单行函数：
1、字符函数
大小写函数
upper(‘’) //把小写换成大写
lower(‘’) //把大写变小写
initcap(‘’) //把首字母大写
concat(‘’，‘’) //字符串进行连接
substr(字符串，开始位置，截取的长度) //截取字符串
length() //求字符串的长度
replace('',要换的字符，替换成的字符) //替代
trim() //去除字符串头部和尾部的空格trim（‘’ from ‘’） 默认去除空格
	select trim('c' from 'abc' ) from dual; //c右边截取掉
	select trim('a' from 'abc' ) from dual; //a左边截取掉
ltrim()
rtrim() select rtrim('abc','c') from dual;
lpad() //以有对齐方式补充字符型的数据，左边补充特定的字符
rpad() select rpad('abc',15,'**') from dual;
instr(字符串，字符) //显示字符所在的字符串的位置
2、数值函数
round()// 四舍五入 select round(78.125,-1) from dual; //第二参数是从小数点开始看
trunc()// 直接截取
mod()// select mod(10,3)from dual;
3、日期函数
sysdate // 显示当前系统时间 dd-mon-year

日期-数字=日期
日期+数字=日期
日期-日期=数字（天数）

months_between()//求出给定日期的月数
	select months_between(sysdate,'29-4月 -13') from dual;
add_months() //得到月份
	select add_months(sysdate,1) from dual;
next_day() //下一个今天是哪天 select next_day('日期','星期二') from dual;
	select next_day(sysdate,'星期二') from dual; //当前日期的下个星期二是那个日期
last_day()//这个月的最后一天
	select last_day(sysdate) from dual; //得到是个日期
round() select round(sysdate,'month') from dual;
trunc() select trunc(sysdate,'month') from dual;
4、转换函数
to_char() //转换成字符串
1、对日期转换 格式：to_char(date,'formate model')
   to_char(sysdate,'yyyy-mm-dd hh24:mi:ss AM')
年：yyyy 月：mm 日：dd   星期 dy  时：hh 分：mi 秒：ss
英文显示
年：year 月：month（mon） 日：day（dy） 小时：hour 分钟：minute 秒：second

2、对数字进行转换
格式：to_char(number,'format')
9:数字
0：0
￥
$:美元
L:本地的货币符号
.:小数点
，：千位符
fm：去除多余的空格或者前导0

to_number():把字符串转换成数字
to_date():转换为日期 to_date('2013-5-3','yyyy/mm/dd')

5、通用函数
nvl(expr1,expr2):将null的值转换成一个实际的值 //nvl(comm,0)
nvl2(expr1,expr2,expr3):如果expr1不是null，则返回expr2，是null返回expr3
decode(col/expr,search1,result1,search2,result2,.......)： 
	col/expr:列或者是表达式
	search1....：用于比较的条件
	result:返回的值
//最后一个可以不写search

case表达式
	case expr/col when expr1 then return1
		  when expr2 then return2
		  .................
		  else ethne
	end	  	

nullif(expr1,expr2):expr1和expr2相同返回空值 不等 返回第一个表达式

coalesce(expr1,expr3....exprn):返回第一个非空表达式

多表查询
select {distinct}*|列名 from 表名1，表名2。。。{where 条件}。。。。。

等值连接
自连接

左右连接

左练级：（+） 在右边
右连接：（+） 在左边

SQL：1999语法
格式：select table.column,table2.column
from table[cross join table2] 交叉连接
[natural join table2] 自然连接 自动进行字段的关联
[join table2 using(col)] 直接关联列
[join table2 on(table.col=table2.col)] 自己写连接的条件
左连接（左外连接）和右连接（右外连接）
left outer join         right join
select e.deptno,e.ename,d.dname from emp e left outer join dept d on(e.deptno=d.deptno); //标准语法
select e.deptno,e.ename,d.dname from emp e right outer join dept d on(e.deptno=d.deptno); //标准语法

组函数以及分组统计
组函数：
count():求出全部的记录数
max():求出一组中最大的值
min();求出一组中最小的值
avg();求出平均值
sum();求总和

分组统计：
group by  //select后的字段只能是group by后面的的字段名
          //where 后不能出现分组函数

having 分组条件 //having 不能使用组函数的别名

分组的简单原则
注意：分组函数可以使用嵌套，但是在组函数嵌套使用的时候，不能出现分组条件的查询语句

子查询：
select * from 表，（select 语句） 别名
select * from 表 where 条件(select 语句)

子查询在操作中分为三类
单列子查询：返回的结果是一列的一个内容
单行子查询：返回多个列，也有可能是一条完整的记录
多行子查询：返回多条记录

注意点:所有的子查询必须在()中编写

子查询中，存在三种查询的操作符
in:指定一个查询的范围 

all： 
>all：比里面最大的要大
<all：比里面最小的要小

any：
=any(与in的操作符功能完全相同) 
>any:比里面最小的值要大 
<any:比里面最大的值要小


5.6
-----------
DDL：数据库模式定义语言，关键字：create
DML：数据操纵语言，关键字：Insert、delete、update
DCL：数据库控制语言 ，关键字：grant、remove
DQL：数据库查询语言，关键字：select

常见的oracle数据类型：
1、字符型：
varchar2(n):可变长度的字符串类型，实际字符串的长符不足的时候，不会使用空格进行补充。n<=4000
char(n)：固定长度的字符串

2、数值行:
number(p,s)
p：有效数
s：s>0:精确到小数点右边s位，并四舍五入
s=0：number(p),此时表示的是整数
s<0:精确到小数点左边s位，并且四舍五入 

3、日期时间型
date:
century year month day hour minutes second

4、lob类型（大对象类型）
主要是存储大对象，最大存储容量4GB
clob:用于存储大型的文本数据（备注。。。）
blob：用于存储二进制的数据，如图片文件
bfile：作为单独的文件存在的二进制

DML：1、select 2、insert update delete 

create table xwq_tab as select * from emp;

insert:
语法：insert into 表名(字段名称） values(字段名称1的值，。。。)

update：
修改全部：update 表名 set 要修改的值=新值，要修改的值=新值
修改局部：update 表名 set 要修改的值=新值，要修改的值=新值 where 修改的条件

delete：
delete from 表名 where 条件

oracle 中的事务:保证事务的完整性
回滚：rollback;
提交：commit;

设置回滚点：
savepoint 回滚点；
rollback to 回滚点；

创建和管理表
---------------
表的创建：
语法：create table 表名（字段，字段类型[default 默认值]，。。。。。）
create table 表名 as select（子查询语句）---复制表（表结构+表数据）
子查询
create table emp_xwq as select * from emp where 1=2;(复制表结构)

删除表：
drop table 表名

修改表：
alert table 表名 add(字段名 字段类型 【default】,......)

修改类型和默认值
alter table 表名 modify(字段的名称 列的类型【default,......)

为表重命名
rename 旧表名 to 新表名

重命名字段名（列）
alter table 表名 rename column 旧的字段名 to 新的字段名

截断表
truncate table 表名；【注意:彻底删除 操作后不能rollback】

约束：（重点）
-----------
约束的分类：
主键约束：唯一+非空 primary key
唯一约束：unique
非空约束：not null  只能在字段后面加
检查约束：check(age between 0 and 100) cheack(sex in('男','女'))
外键约束：foreign key | 
constraint 约束名 foreign key(外键字段名) references 父表名（主键字段名） on delete cascade;

//外键约束的级联删除，级联置空
on delete cascade
on delete set null

on delete cascade ---强制删除数据不考虑子表的引用


外键约束主要是定义在父表和子表之间的关系，外键要定义在子表上，父表则必须具有主键约束

约束名： 表名_字段名_约束类型

约束使用方法：
1、字段名类型后+约束  
2、最后写 constraint person_pid_key primary key(pid)  （正规）

增加约束：
alter table 表名 add constraint 字段名 约束
修改约束：
alter table 表名 modify (字段名 约束名称)
删除约束：
alter table 表名 drop constraint 约束名称；

//禁用约束(disable|enable)
alter table student disable constraint 约束名字
alter table student disable constraint uk_phone;
alter table student enable constraint uk_phone;

查看约束的信息：
user_constraints
select * from user_constraints;

显示约束的列
user_cons_columns

rowid---表示一个文件的物理地址
rownum---伪列

//设置列不可用，但磁盘中还存在
alter table tablename set unused column (col,col1....)
alter table tablename drop unused column;

集合操作
-------

//合并表
merge into xwq1 x1 using xwq2 x2 on(x1.id = x2.id)
when matched then update  set x1.name = x2.name
when not matched then insert  values (x2.id,x2.name);


//并 union 将多个查询的结果组合到一个查询结果中，没有重复的内容
select * from emp intersect select * from emp11;
select * from emp union select * from emp11;
//并union all 将多个查询的结果组合到一个查询结果中，包含重复的内容
select * from emp union all select * from emp11;

//交 intersect 返回多个查询结果中相同的部分
select * from emp intersect select * from emp11;

//差  minus 返回多个查询结果中不同的部分
select * from emp intersect select * from emp11;  //多的在前面


用户管理：
-------
sys：必须以as sysdba身份登录
system  sys > system
普通用户

sys
conn 用户名/密码@sid as sysdba/sysoper
sysdba:可以创建数据库
sysoper：不可以创建数据库

sys > sysdba >普通用户

创建用户：
create user 用户名 identified by 密码

删除用户：
drop user 用户名
drop user 用户名 cascade //在有表等情况下 强制删除

修改密码：
//给自己修改密码
password 直接回车 
//修改其它用户密码 必须具有dba的权限
password 用户名
alter user 用户名 identified by 新密码  

权限：privilege
授权：
------
grant 权限 to 用户名 [with admin option/with grant option]

with admin option:系统权限，具有传递的作用  //可以授权给别人
with grant option:对象权限，具有传递的作用  //可以授权给别人

系统权限：用户对数据库的相关的权限
create session:
create table
create view
create procedure
......很多
【select * from system_privilege_map order by name;】 //查看系统权限

对象权限：用户对其它用户的数据对象操作的权限
常用
select inset update delete ..........
grant 对象权限 on 表名 to 用户名
grant all on 表名 to 用户名  //把所有的对象权限 授权
【select distinct privilege from dba_tab_privs;】 //查看对象权限  需要一定的权限

角色：一组权限的集合，目的：就是简化权限的管理
预订义角色：
1、connect 角色
2、resource 角色
3、dba 角色
dba > resource > connect
自定义角色：
语法：
create role 角色名 not identified;
create role 角色名 identified by 密码;

grant 权限 to 角色；
grant 角色 to 用户；

收回权限：
-------
revoke 权限/角色 from 用户名
revoke select on emp from test；

oracle 的权限回收不是级联机制 //上级权限回收，不会影响到自己的权限

删除角色
drop role 角色；

数据字典：记录数据库系统信息 基表和视图集合而成的
基表：存储数据库的基本信息，普通用户是不能访问基表
视图：
user_xxx:当前用户所拥有的信息
all_xxx:当前用户可以访问的信息
dba_xxx:所有用户可以拥有的数据库信息

动态性能视图：记录当前数据库实例的活动信息
v_$开始  v$同义词
v_$datafile 对应于 v$datafile

数据库的备份和恢复
exp  导出
imp  导入

cmd环境下  操作 只能在服务器

数据泵 可以在客户端
expdp
impdp

序列(sequence):一组连续而且是不重复的值
-------------------
语法：
create sequence 序列名
[increment by n]    	//递增的值
[start with n]		//初始的值
[maxvalue n]		//最大的值
[minvalue n]		//最小的值
[cycle n]			//循环 当序列达到最大值或者最小值的限制 重新开始
[cache n]			//缓存，定义存放在内存快的大写，默认是20

如何引用序列：
currval: 用于生成当前的值
nextval: 用于生成序列下一个值
序列名.currval
序列名.nextval

注意：第一次调用 用nextval

create sequence xwq increment by 2 start with 3 maxvalue 10;

create sequence xwq_seq start with 2 increment by 2 maxvalue 20 cycle nocache;
//循环以后从1开始

同义词：synonym
-------------
语法：
create [public] synonym 名称 for 用户名.表名

视图：view 就是封装类一条复杂的sql语句  一张虚表
--------------------
create view 视图名 as 子查询 with read only/with check option
with read only:只读视图
with check option：创建视图的条件是不可以修改的

create view emp11 as selelc * form emp;
select * from emp11;
drop view emp;
delete view emp;

视图和表的区别：
1、表需要占用磁盘空间，视图不需要
2、视图可以简化我们的复杂的查询
3、视图有利于提高数据的安全性
4、视图是不能添加索引，查询速度变慢

索引：加速数据的存取，合理的使用索引可以大大的降低I/O的次数，提高数据访问的性能
------------------
创建：create index 索引名 on 表名（列名1，列名2，.....）
单列索引
复合索引

原则：
1、在大表上建立索引才有意义
2、在where字句或者连接条件经常引用上创建索引

索引缺点：
1、建立索引，系统会占用额外的磁盘空间和内存空间来保存索引
2、更新数据的时候，系统必须要额外的时间对索引进行更新，来保证更新数据和索引的一致

pl/sql编程:oracle 在标准的sql语言上的扩展
(procedual language)

优点：
缺点：移植性不好

简单的分类：
块（存储过程，函数，触发器，包）
编码规范：
1、注释 
单行注释 --
注释多行 /* */ 

2、命名规则
a、当定义常量，建议使用c_作为前缀
b、当定义变量，建议使用v_作为前缀
c、当定义游标，建议使用_cursor作为后缀
d、当定义例外，建议使用e_作为前缀

pl/sql 块
组成
declare  --定义部分
begin  --执行部分 可以执行pl/sql 和 sql语句
exception  --例外处理部分 ， 处理运行的各种错误
end;  --结束
/


//declare 和 exception 可以不写

执行部分
begin
null;
end;

包括定义部分和执行部分
declare
 v_aa varchar2(20):='&d';
begin
 dbms_output.put_line(v_aa);
end;

set serveroutput on //设置开关

包括定义部分和执行部分以及例外处理部分

declare
 v_name varchar2(20);
begin
 select ename into v_ename from emp wherer empno=&s;
 dbms_output.put_line(v_name);
 exception
  when no_data_found then
  dbms_output.put_line('没有找到数据！');
end;

在pl/sql sql语句已经赋值必须into

例子：根据员工编号，查询楚姓名，工资，工作并把查询的信息输出
declare
 v_name varchar2(20);
 v_sal number;
 v_job varchar2(20);
begin
 select ename,sal,job into v_name,v_sal,v_job from emp where empno=&v; 
 dbms_output.put_line(v_name);
 dbms_output.put_line(v_sal);
 dbms_output.put_line(v_job);
 dbsm_output.put_line(v_name||v_sal||v_job);
end;

插入一条记录并显示
declare
 v_info varchar2(20);
begin
 insert into dept_xwq values(50,'财务室','南京') returning deptno||dname||loc into v_info;
 dbms_output.put_line(v_info);
end;

returning:用于语句中所影响到的数据行
into:把表中的数据赋予一个变量


复合类型
记录类型
--------
语法：
 type 记录类型的名称 is record(
 v1 datatype,
 ........
);

declare
 type test_rec is record(
 ename varchar2(20),
 sal number(7,2),
 hiredate date);
 v_arec test_rec;
begin
 select ename,sal,hiredate into v_arec from emp where empno=&empno;
 dbms_output.put_line(v_arec.ename||v_arec.sal||v_arec.hiredate);
end;

数组类型
定义：
 type 数组的名称 is varray(size) of element_type;
 size: 可以容纳的成员的最大数量
 element_type:每个成员的数据类型
属性：
 first:可以取数组下表的最小值
 last:可以取数组下表的最大值

定义一个最多保存5个varchar2(20)数组的数据类型
 type a_varray is varray(5) of varchar2(20);--定义数组类型
 v_varray a_varray; --声明一个类型为数组类型
 v_varray.first -- 获得最小值下标值
 v_varray.last --

declare
 type a_varray is varray(5) of varchar2(20);--定义数组类型
 v_varray a_varray; --声明一个类型为数组类型
begin
 v_varray:=a_varray('浦口','江北','鼓楼','江宁','秦淮')
 dbms_output.put_line(v_varray(1)||v_varray(5));
 dbms_output.put_line('数组中的最小值'||v_varray.first);
 dbms_output.put_line(v_varray.last);
end;

%type;类型
------------
用法：表名.字段名%type
v_ename emp.ename%type; --为了让 v_ename 的类型更加灵活,我们使用%type,这就是自适应

%rowtype类型 -- 返回一个记录类型，其数据类型和数据库表的数据结构一致
用法：表%rowtype

declare
 v_info dept%rowtype
begin
 select * into v_info from dept where deptno=&deptno;
 dbms_output.put_line(v_info.deptno);
end;

pl/sql流程控制语句
-------------------
控制语句：
if语句
语法：
if 条件 then
  pl/sql和sql语句
elsif 条件 then
..........
elsif 条件 then
..........
else
........
end if;

代码案例：
declare
 v_sal emp.sal%type;
begin
 select sal into v_sal from emp where empno=&empno;
 --dbms_output.put_line('v_sal='||v_sal);
 if v_sal < 1500 then
 dbms_output.put_line('工资太少了，加点吧！');
 elsif v_sal < 5500 then
 dbms_output.put_line('工资太多了，降点吧！');
 else
 dbms_output.put_line('工资正常！');
 end if;
end;

case 表达式
格式一：
case 条件表达式
 when  条件表达式的结果1 then
  语句段； 
 when  条件表达式的结果1 then
 。。。。。
 [else 条件表达式的结果]
 end case;

格式二：
 case 
  when 条件表达式1 then
   语句段
   ..... 
  when 条件表达式1 then
   语句段
   ..... 
  [else 条件表达式的结果]
 end case;

代码案例：
declare
 v_char varchar2(1);
 v_comment varchar2(20);
begin
 v_char:='&v';
 case 
   when v_char='A' then v_comment:='优秀';
   when v_char='B' then v_comment:='良好';
   when v_char='C' then v_comment:='中等';
   else v_comment:='没有成绩';
 end case;
  dbms_output.put_line(v_comment);
end;

循环语句
loop
 语法：
   loop
     要执行语句
     exit when 条件语句 -- 满足条件，退出循环
   end loop;

代码案例：
declare
 v_value number:=0;
begin
 loop
  dbms_output.put_line(v_value);
  v_value:=v_value+1;
  exit when v_value>10;
 end loop;
end;
-------------
while 循环
 语法：
  while 条件 loop
    要执行的语句；
  end loop;

declare
 v_value number:=0;
begin
 loop
  dbms_output.put_line(v_value);
  v_value:=v_value+1;
  exit when v_value>10;
 end loop;
end;
-----
for 循环
----
语法：
 for 变量名 in[reverse]下限.. 上限 loop
  要执行的语句
 end loop;

 变量名:单存的一个名称
 in 每次循环一次自动加1
 in reverse:每次循环自动减1

//10 9 8 7 6 5 4 3 2 1 0

begin
 for i in reverse 0.. 10 loop
  dbms_output.put_line(i);
 end loop;
end;

goto
---------
无条件的跳转指定的标号中去
语法：
goto lable;
.......
<<lable>>

declare 
 v_counter number:=1;
begin
 loop
  dbms_output.put_line(v_counter);
  v_counter:=v_counter+1;
  if v_counter>10 then 
   goto offloop;
  end if;
 end loop;
 <<offloop>>
 dbms_output.put_line(v_counter);
end;
-----------
存储过程(procedure)
语法：
create [or replace] procedure 存储过程的名字(arg1[in/out/inout] type1,....)
is / as
<类型或者变量的定义>
begin 
 执行部分
end 存储过程的名字；

模式：
in：将实参的值传递给行参，只能读不能写  in可以省略
out：在过程中可以读可以写，返回时，行参的值可以给是实参
inout：in的特性和out特性

create procedure sp1(spno in number)
is
spname emp.ename%type;
begin
 select ename into spname from emp where empno=spno;
 dbms_output.put_line(spname);
end sp1;

如何调用：
call：
exec：

块中进行调用
begin
 sp1();
end;

存储过程中有in和out模式
create procedure p2(spno number,spname out varchar2(20))
is
begin
 select ename into spname from emp where empno=spno;
end p2;

调用打印出来
declare
 v1 varchar2(20);
begin
 p2(7369,v1) 
 dbms_output.put_line(v1);
end;

函数
------
语法：
create[or replace] function 函数名 (arg1[in/out/inout] type1,....) return 返回值类型
is / as
<类型或者变量的定义>
begin 
 执行部分
 return expression;
 exception
end 函数过程的名字；

create or replace function f1(fename varchar2) return number
is
 v_yearsal number;
begin
 select (sal+nvl2(comm,comm,0))*12 into v_yearsal from emp where ename=fename;
 return v_yearsal;
end;

调用方法：
select f1('SMITH') from dual;
------
declare
 v_yearsal number;
begin
 v_yearsal:=f2('SMITH',v_yearsal);
 dbms_output.put_line(v_yearsal);
end;
-----
var v1 number
call f1('SMITH') into:v1;

-----------
游标：cursor 就是一个指针，指向的是一个内存区域或者是缓冲区域
分类：
显式游标：
处理显式游标的四个步骤
1、声明一个游标，以及对应的select语句
格式：cursor 游标的名称 is select语句
2、打开游标
格式：open 游标的名称
注意点：游标不能重复打开
3、提取游标的数据
格式：fetch 游标的名字 into{variable list/record}
执行fetch语句时，每次返回的是一个数据行，然后自动将游标移动指向的下一个数据行
4、关闭游标
close 游标名称


游标的属性：
notfound
found
isopen
rowcount --行数，可以了解访问类多少行数据
用法：游标的名字%游标的属性


declare
 cursor c1_cursor is select * from emp;
 v_row emp%rowtype;
begin
 open c1_cursor;
 loop
  fetch c1_cursor into v_row;
 exit when c1_cursor%notfound;
 dbms_output.put_line(v_row.empno||' '||v_row.job);
 end loop;
 close c1_cursor;
end;

游标的for循环
格式：
 for variable in 游标的名字
 loop
  .....
 end loop;

注意点：
1、隐藏了打开游标
2、隐藏了fetch操作
3、隐藏了检查循环停止的操作
4、隐藏了关闭游标        

declare
 cursor c1_cursor is select * from emp;
begin
 for v_row in c1_cursor loop
  dbms_output.put_line(v_row.empno||' '||v_row.ename);
 end loop;
end;

隐式游标
对insert,update,delete操作

用户只能通过隐式游标的属性来完成相应的操作
属性：found notfounf isopen rowcount
用法：sql%属性
sql --隐式游标的名字，它是由oracle定义的

begin
 delete from dept;
 dbms_output.put_line(sql%rowcount);
end;

游标变量 类型
和游标一样，也是一个指针，指向多行数据
与游标不同的是，游标变量是动态的，游标是静态
语法：
 type 游标变量的类型的名字 is ref cursor;
游标变量2步
1、定义游标变量
2、引用或者声明一个数据类型为游标变量类型

弱类型定义：
 type 名字 is ref cursor;
打开游标变量: open 游标变量的名称 for 'select语句' 

declare
 type cur_type is ref cursor;
 cur1_cursor cur_type;
 v_emp emp%rowtype;
begin
 open cur1_cursor for 'select * from emp';
 loop
  fetch cur1_cursor into v_emp;
  exit when cur1_cursor%notfound;
  dbms_output.put_line(v_emp.empno);
 end loop;
 close cur1_cursor;
end;
 
强类型定义：
语法：
 type 游标变量的类型的名字 is ref cursor return 类型;
游标变量2步
1、定义游标变量
2、引用或者声明一个数据类型为游标变量类型

打开游标变量: open 游标变量的名称 for select语句 ;  //没有 ''

declare
 type cur_type is ref cursor return emp%rowtype;
 cur1 cur_type;
 v_emp emp%rowtype;
begin
 open cur1 for select * from emp;
 loop
  fetch cur1 into v_emp;
 exit when cur1%notfound;
 dbms_output.put_line(v_emp.ename);
 end loop;
 close cur1;
end;

-----

declare
 type cur_type is ref cursor;
 cur1_cursor cur_type;
 v_dept dept%rowtype;
 v_emp emp%rowtype;
begin
 open cur1_cursor for 'select * from dept';
 loop
  fetch cur1_cursor into v_dept;
  exit when cur1_cursor%notfound;
  dbms_output.put_line(v_dept.dname);
 end loop;
 close cur1_cursor;
 
 open cur1_cursor for 'select * from emp';
 loop
  fetch cur1_cursor into v_emp;
  exit when cur1_cursor%notfound;
  dbms_output.put_line(v_emp.ename);
 end loop;
 close cur1_cursor;
end;

包：
可以将相关的对象存储在一起的pl/sql结构。
包含了2个部分：包说明，包主体

每个部分 都被存储在数据字典中。
包说明：一个操作接口，可见的
包主体：是一个黑盒，对应用者来说隐藏了实现的细节

包的组成：程序单元（过程，函数，变量，类型，游标，常量，异常）

定义包说明：
语法：create [or replace] package 包名字 is/as
公共的变量的定义
公共的类型的定义
公共的错误处理的定义
公共的游标的定义
函数说明
过程说明
end；

create package sal_package is
v_raise_sal emp.sal%type;
v_reduce_sal emp.sal%type;
procedure raise_sal(v_empno emp.empno%type,v_sal emp.sal%type);
end;

创建包主体的语法：
create[or replace] package body 包名 is/as
私有变量的定义
私有类型
私有出错
函数体
过程体
end;

create package body sal_package is
 procedure raise_sal(v_empno emp.empno%type,v_sal emp.sal%type)
 is
 begin
  update emp set sal=sal+v_sal where empno=v_empno;
  v_raise_sal:=v_raise_sal+v_sal_raise;
 end;
end;

包的调用
begin
 sal_package.raise_sal(7369,2000);
end;

系统包
dbms_output
dbms_lock
dbms_pipe 管道通信
dbms_mail

查看过程，函数和包的有关信息
select * from user_procedures where object_name='大写';

查看过程，函数的代码
user_source
select text from user_source where name='SP1'; 

查看包的代码
select text from user_source where name='包名' and type='PACKAGE';

异常(例外)：
ora-xxxx

有三种类型的异常错误
1、预订义错误
no_data_found
to_many_rows


语法：
exception 
 when first_exception then
 ....
 when second_exception then
 .....
 ......
 when others then
 ...

2、非预订义错误   
 是oracle的标准错误，这种情况的处理，需要在程序中定义，然后由oracle自动引发
步骤：
a、在定义部分定义异常
 异常名称 exception;
b、把定义好的异常和为标准的oracle错误联系起来
exception_init()
 pragma exception_init(异常名称，错误代码)；
c、在exception中读异做出处理

declare
 has_foreignkey exception;
 pragma exception_init(has_foreignkey,-2292);
begin 
 delete from dept where deptno=&deptno;
 exception
 when has_foreignkey then
  dbms_output.put_line('还有外键在指向，不能删除');
end;


3、自定义错误
步骤：
a、定义异常
 异常名字 exception;
b、raise 异常名字
c、exception中对你抛出的异常进行处理

raise_application_error(异常的代码【-20000到-20999】，异常信息);
 
declare
 e_noempno exception;
 v_sal emp.sal%type;
 v_empno emp.empno%type:=&empno;
 cursor cur_cursor is select sal from emp where empno=v_empno;
begin
 open cur_cursor;
 fetch cur_cursor into v_sal;
 if cur_cursor%found then
  update emp set sal=sal+100 where empno=v_empno;
 else
  raise e_noempno;
 end if;
 exception
  when e_noempno then
   dbms_output.put_line('没有此员工'||v_empno||'编号');
  close cur_cursor;
end;

触发器：
--------
是有一个事件来启动运行，当莫个事件发生的时候自动隐式的执行，而且触发器是不能接受参数的
语法：
create [or replace] trigger 触发器的名称
{bofore/after}
{insert/delete/update[of column[,column]]} on 表名
[for each row]
[when 条件]
begin
 触发体；
end;

before:前触发，在执行触发器之前触发当前所创建的触发器
after:后触发，在执行触发器之后触发当前所创建的触发器
insert/delete/update：触发的事件
for each row:说明触发器为行级触发
when:说明触发的条件

分类：
1、dml触发器：oracle在dml语句进行的触发
DML触发器：
基本要点：
触发的时间
触发的事件：insert update delete
条件谓词：当触发器中包含多个触发事件时，为了分别对不同的事件进行不同的处理，
需要用到条件谓词 inserting updating deleting
触发的对象是：表

dml触发器中的语句级触发
create trigger tr1
after
insert on dept
begin
 dbms_output.put_line('对dept进行增加数据');
end;

行级触发  //有一行触发一次
create or replace trigger tr1
 after 
 delete on dept
 for each row
 begin
  dbms_output.put_line('');
 end;
 
条件谓词
create or replace trigger tr1
 after insert or delete or update on dept
 for each row
 begin
  if inserting then
   dbms_output.put_line('insert');
  elsif updating then
   dbms_output.put_line('update');
  elsif deleting then
   dbms_output.put_line('delete');
  end if;
 end;

属性 ：old和：new
：old --访问操作完成前的值
：new --访问操作完成后的值

     insert update  delete
:old  无      有      有 
:new  有      有      无

编写一个触发器，修改emp表雇员的薪水，显示雇员修改前的工资和修改后的工资，
确保修改后的工资不能低于修改前的工资

create or replace trigger tr1
 after update on emp
 for each row
 when :new > :old
 begin
  dbms_output.put_line('操作成功！');
 end;


2、替代触发器(instead of)：对视图进行的操作
默认：就是一个行级触发器
语法：
 create or replace trigger 触发器的名字
 instead of insert/update/delete on 视图名
 declare
 begin
  .....
 end;
 
create trigger view1
instead of delete on view1
begin
 dbms_output.put_line('');
end;


3、系统触发器：oracle系统的事件触发的。比如：oracle的开启，关闭，用户的登录，退出.....
语法：
create or replace trigger 触发器的名字
before/after 系统事件 on database
begin
 .....
end;

时间戳：timestamp

系统事件：logon（登录） logoff（退出） startup/startdown(数据库的开启和关闭)
 
create table log_event(
user_name varchar2(20),
address varchar2(20),
logon_date timestamp,
logoff_date timestamp);

--创建一个登录的触发器
create trigger tr_logon
after logon on database
begin
 insert into log_event(user_name,address,logon_date) 
 values(ora_login,ora_client_ip_address,systimestamp);
end;

--创建一个退出的触发器
create trigger tr_logoff
before logoff on database
begin
 insert into log_event(user_name,address,logoff_date)
 values(ora_login,ora_client_ip_address,systimestamp);
end;
=================================补充========================================

profile 管理用户口令

需求:只允许某个用户,最多尝试登陆三次,如果三次没有成功,则锁定两天,两天后才可以重新登录;

create profile myprofile1 limit failed_login_attempts 3 password_lock_time 2;
alter user stu profile myprofile1;

用户的解锁/锁定
alter user stu account unlock/lock

功能:一个账号的密码只能用 10 天,宽限期为二天
create profile myprofile2 limit password_life_time 10 password_grace_time 2;
alter user stu profile myprofile2;

删除 profile 文件
conn sys/m123@lhforcl as sysdba

drop profile myprofile1;




--创建表空间
create tablespace xwq datafile 'd:\13031\xwq.dbf' size 10m;
--创建用户
create user xwq identified by xwq default tablespace xwq;
--为用户授权
grant dba to xwq;
--修改用户密码
alter user xwq identified by xwq1;
password 用户名
//多种权限形成类角色
权限：
create session 
create table
create index
create view 等等
角色：有 dba connect resource