Oracle学习复习笔记

Oracle

-- 解锁用户：

alteruser 用户名 account unlock;

-- 锁定用户：

alteruser 用户名 account lock;

-- 建立用户：create user 用户名identified by 密码;

createuser test identified by test;

-- 修改密码:alter user 用户名identified by 新密码

alteruser test identified by t123;

-- 给用户分配权限

grantconnect,resource to test;

-- 建立表：

createtable 表名称(

列1类型(精度),

列2类型(精度),

...）

Oracle 的启动：

--三个步骤(状态)：

-- 1- nomount 状态：仅仅加载Oracle运行的基本表，没有加载用户需要运行的数据表。

-- 用来进行数据库程序的最底层维护

-- 2-mount 状态：在 no mount状态上多添加了一些用户使用的数据表，但是不能让普通用户登录以及使用数据。

-- 用来进行用户的数据库调优和数据维护，但是不提供数据服务。

-- 3-open 状态：可以使用普通用户登录来对外提供服务。

-- 用来提供数据服务，但是不能做用户底层的数据调优。

-- 注意：不能越级修改数据库状态：

-- 不能再nomount状态下 直接修改为 open状态，必须先修改为mount.

-- 记住： select open_mode,log_mode from v$database;

-- 关闭方式：------

-- 1-shutdown [normal]; 等待已连接用户退出才能关闭

-- 2-SHUTDOWN TRANSACTIONAL; 不等待已连接用户退出，但是会等待已发出的事务。

-- 3-shutdown immediate ; 不等待用户退出，不等待事务，所有以发生的事务全部回滚。会写检查点。

-- 4-shutdown abort: 强制关闭，不等待用户退出，不等待事务，所有以发生的事务全部回滚。不会写检查点。

-- 给列起别名：

selectempno 员工编号,ename emp_name,deptno dno fromemp;

selectempno as "员工编号",ename as"emp_name",deptno as "dno" from emp;

selectsysdate,1+2 from dual;

select *from dual;

-- 字符函数

selectascii('a'),chr(97) from dual;

-- 注意：函数仅仅会使结果集合，的结果变化，不会影响物理数据。如果想更新物理数据，必须使用update

selectinitcap(ename) from emp;

selectinstr('abcdedfh','ce') from dual;

selectlength(ename),ename from emp;

--RPAD() LPAD

selectlpad(ename,6,' ') from emp;

--trim() rtrim() ltrim()

select' abc',

length(rtrim(' abc')),

length(ltrim(' abc')),

length(trim(' abc ')) from dual;

select'ok' from dual where 'abc'=trim('abc ');

select *from emp;

selectempno,ename,sal,nvl(comm,0),sal+nvl(comm,0) sumsal from emp;

selectempno,ename,sal,nvl2(comm,'not null',0),sal+nvl(comm,0) sumsal from emp;

selectreplace(ename,'A','a') from emp;

updateemp set ename=upper(ename);

selectsubstr('abcedf',3,2) from dual;

select substr('abcedf',-4,2)from dual;

-- 数值函数

selectceil(3.00001),floor(3.000001) from dual;

selectround(3.1456,2) from dual;

-- 转换函数

--decode(); 多条件分支

selectdecode(sal,800,'八百',1200,'一千贰',5000,'最高工资',1600,10,'其他工资'),sal from emp;

selectto_char(sysdate+10,'yyyy-mm-dd day') from dual;

-- 日期函数

--MONTHS_BETWEEN函数

selectmonths_between(to_date('2013-7-20','yyyy-mm-dd'),sysdate)from dual;

--ADD_MONTHS函数

select add_months(sysdate,12)from dual;

-- #NEXT_DAY函数

selectnext_day(sysdate,7) from dual;

-- 查询 ---

updateemp set ename=upper(ename);

select *from emp;

createtable t(tid number(10));

droptable t;

createtable t as select empno e_o,ename e_name,deptno d_no from emp;

createtable t(t_no,t_name,t_dno) as select empno,ename,deptno from emp;

--

alteruser test quota unlimited on users;

--1、获取每个职员详细信息：编号，姓名，相应部门的部门编号和部门所在地

selectemp_no,emp_fname,e.dept_no,location

fromemployee e,department d

wheree.dept_no=d.dept_no;

-- 笛卡尔积：

selecte.*,d.*

from empe,dept d

wheree.deptno=d.deptno;

--2、获取为项目Gemini工作的所有职员的全部信息

-- 如果有n张表，就需要n-1个连接条件

selecte.*,d.*,p.project_no,p.project_name,p.budget,w.job,w.enter_date

fromemployee e,department d,project p,works_on w

wheree.emp_no=w.emp_no and w.project_no=p.project_no and e.dept_no=d.dept_no

andp.project_name='Gemini';

--3、获取在98年10月15日加入项目的所有职员的部门编号

selecte.*

fromemployee e,works_on w

wheree.emp_no=w.emp_no and w.enter_date=to_date('1998-10-15','yyyy-mm-dd');

-- 自连接

--5、获取与至少一个其他部门拥有相同所在地的所有部门的全部细节信息（自连接）

selectdistinct d1.*

fromdepartment d1,department d2

whered1.dept_no<>d2.dept_no and d1.location=d2.location

createtable employee_enh

as select* from employee;

altertable employee_enh

addemp_address varchar(30) null;

updateemployee_enh set emp_address='San Antonio' whereemp_no='25348';

updateemployee_enh set emp_address='Houston' where emp_no='10102';

updateemployee_enh set emp_address='San Antonio' whereemp_no='18316';

updateemployee_enh set emp_address='Seattle' where emp_no='29346';

updateemployee_enh set emp_address='Portland' where emp_no='9031';

updateemployee_enh set emp_address='Tacoma' where emp_no='2581';

updateemployee_enh set emp_address='Houston' where emp_no='28559';

commit;

-- 多表连接 ---

-- 1- 等值连接

-- 显示所有员工信息和其部门名称

selecte.*,d.dname

from empe,dept d

wheree.deptno=d.deptno;

-- 2- 不等值连接

-- 显示所有员工信息以及其工资等级

selecte.*,s.*

from empe,salgrade s

wheree.sal >=s.losal and e.sal<=s.hisal;

selecte.*,s.*

from empe,salgrade s

wheree.sal between s.losal and s.hisal;

-- 注意：连接查询，必须要指定同名列的出处（数据来源）。

selectempno,ename,e.deptno,dname

from empe,dept d

wheree.deptno=d.deptno;

-- 注意：一旦定义了表的别名，则不能再使用表的原名。

selectempno,ename,e.deptno,dname

from empe,dept d

wheree.deptno=d.deptno;

-- 外连接：除了显示符合连接条件的记录外，还显示不符合连接条件的记录。

-- 显示所有员工信息，以及其部门信息，包括所有部门。

selecte.*,d.*

from empe,dept d

wheree.deptno(+)=d.deptno;

-- 注意：

-- 外链接时，如果使用外链接符号，那张表使用了外连接符号，则另外一边的表会出现不符合连接条件的记录。

-- 如果有多个条件时，则那张表使用了外链接符号，则每个条件都必须有这张表参与，并且必须带外链接符号，否则外链接失效

-- 一个连接条件只能有一个外链接符号，否则编译错误。

-- 显示10部门的员工，以及所有部门信息

selecte.*,d.*

from empe,dept d

wheree.deptno(+)=d.deptno and e.deptno(+)=10;

-- 显示10部门的信息，以及所有员工信息

selecte.*,d.*

from empe,dept d

wheree.deptno=d.deptno(+) and d.dname(+)='ACCOUNTING';

-- 显示10部门员工及其部门信息，并显示其他员工和其他部门的信息。（全外链接）

-- 注意：外链接符号不支持全外链接。

selecte.*,d.*

from empe,dept d

wheree.deptno(+)=d.deptno(+) and d.dname(+)='ACCOUNTING';

-- 自连接： 使一张表，自己和自己连接，从不同行取值，放置到同一行的逻辑结果，称为自连接。

-- 自连接的目的(意义)：把不同行数据放置到同一行显示(跨行访问数据)。

selecte1.*,e2.ename mgrname

from empe1,emp e2

wheree1.mgr=e2.empno

-- SQL/92标准的链接

/*

SELECT table1.column, table2.column

FROM table1

[CROSSJOIN table2] |

[NATURALJOIN table2] |

[JOINtable2 USING (column_name)] |

[JOINtable2

ON(table1.column_name = table2.column_name)]|

[LEFT|RIGHT|FULLOUTER JOIN table2

ON (table1.column_name =table2.column_name)];

*/

-- CROSSJOIN ： 笛卡尔积连接

selecte.*,d.*

from empe cross join dept d

--NATURAL JOIN: 自然连接（自动匹配同名列的等值连接）

-- 注意：natural join 连接不能给同名列加限定。

select e.empno,ename,deptno,dname

from empe natural join dept d

-- JOIN... USING ...: 指定同名列的链接

-- 注意：JOIN USING 连接不能给using指定的同名列加限定。

selecte.empno,ename,ddeptno,dname

from empe join dept d using(deptno);

-- JOIN... ON ... :自己指定连接条件的链接。

selecte.*,s.grade

from empe join salgrade s on( e.sal between s.losal and s.hisal);

-- 注意：如果有多于两张以上的表：

-- 显示所有员工信息，部门名称以及工资等级

selectempno,ename,deptno,s.grade

from empe natural join dept d join salgrade s on(e.sal between s.losal and s.hisal)

-- 注意：where条件是在表连接后附加的行筛选条件，可以和 SQL/92标准混用

selectempno,ename,deptno,s.grade

from empe natural join dept d join salgrade s on(e.sal between s.losal and s.hisal)

wheredeptno=10;

selectempno,ename,e.deptno,s.grade

from empe join dept d on(e.deptno=d.deptno and e.deptno=10)

join salgrade s on(e.sal between s.losal ands.hisal)

--LEFT|RIGHT|FULL OUTER JOIN ... ON (外链接条件)

-- LEFTOUTER ：左外链接

-- 显示所有员工信息，和10部门信息

selecte.*,d.*

from empe left outer join dept d on(e.deptno=d.deptno and d.deptno=10);

-- RIGHTOUTER ：右外链接

select e.*,d.*

from empe right outer join dept d on(e.deptno=d.deptno and e.deptno=10);

-- FULLOUTER : 全外链接

selecte.*,d.*

from empe full outer join dept d on(e.deptno=d.deptno and e.deptno=10);

selecte1.*,e2.ename mgrname

from empe1 join emp e2 on(e1.mgr=e2.empno);

-- 分组查询

-- 求出每个部门的工资和

selectsum(sal),deptno

from emp

group bydeptno;

-- 求出每个部门的工资平均值

selectsum(sal),count(*),round(avg(sal),2),deptno

from emp

group bydeptno;

-- 单组分组函数：

/*

sum()

avg()

count()

max()

min()

注意：null值，不在计算范围，需要 nvl()nvl2() 转换。

*/

--count()

-- 统计emp表中的工种

updateemp set job=upper(job);

selectcount(distinct job) jobnum

from empe

-- 统计每个工种的人数

selectcount(job),job jobnum

from empe

group byjob;

-- MAX()

-- 得到每个部门工资最高的数值

selectmax(sal),deptno

from empe

group bydeptno;

-- 注意：分组函数的错误用法：

-- 在分组查询中，所有列，必须参加gourp by 分组或者参加分组函数，否则错误。

-- 得到每个部门工资最高的人员信息？ （必须使用子查询或者自定义函数）？？？？？？？？？？？？？

selectmax(sal),deptno,ename,empno

from empe

group bydeptno;

/*

查询原理：

1- 所有查询结果集合均为矩阵，否则错误

2- SQL语句的顺序：

select ...

from ...

[where ...]

[group by ...]

[having ...]

[order by ... asc|desc]

*/

-- 得到comm的平均值

selectround(avg(nvl(comm,0)),2) avgcomm,deptno from emp group by deptno;

-- 注意: 如果不指定分组，则所有数据为一组。

selectmin(sal),deptno from emp;

-- 多列分组

-- 对同一个部门并且同一种工作的员工，求出工资和

selectsum(sal),deptno,job,count(*)

from empe

group bydeptno,job;

-- 注意：where只能筛选行条件，不能筛选分组条件。 having可以筛选分组条件，但是不能筛选行条件

-- 的到部门工资和高于9000 的部门编号

selectsum(sal),deptno

from emp

---wheresum(sal)>9000

group by deptno

havingsum(sal)>9000;

selectsum(sal),deptno

from emp

wheresal>900

group by deptno

havingsum(sal)>9000;

-- ？？有没有条件即可以存在于where 后也可以存在 having 后

-- 分组列作为条件时，可以应用在分组条件或者行条件

selectsum(sal),deptno

from emp

--wheredeptno>10

group by deptno

havingdeptno>10;

-- 子查询：嵌套查询

-- 得到scott同一个部门的其他员工

selectdeptno from emp where ename='SCOTT';

select *from emp where deptno=(select deptno from emp where ename='SCOTT') andename<>'SCOTT';

-- 根据自查寻的结果分类：

-- 1-单行单列子查询（一个值）

-- 2-单行多列子查询

-- 得到和scott同一个部门，并且同一个工作的人员信息

select *from emp where (deptno,job)=(select deptno,job from emp where ename='SCOTT')and ename<>'SCOTT';

-- 3- 多行单列

-- 得到其他部门的工作和10部门中所有工作一样的员工信息。

select *

from emp

where jobin(

selectdistinct job from emp where deptno=10)

anddeptno<>10;

/*

注意：多行子查询关键字

IN匹配多个结果中的一个结果即可 --- 多行的等值查询

ANY符合多个结果中的其中一个结果即可 ----多行的不等值查询

ALL符合多个结果中的任意一个结果。--|

*/

-- 得到工资高于20部门其中一个人员的其他部门人员信息。(高于20工资部门最低的人即可)

select *from emp

where sal> any (

selectsal from emp where deptno=20)

anddeptno!=20;

-- 得到工资高于30部门任意一个人员的其他部门人员信息。(高于20工资部门最高的人)

select *from emp

where sal> all (

selectsal from emp where deptno=30)

anddeptno!=30;

-- 4- 多行多列

-- 得到每个部门工资最高的人员信息？

selectmax(sal),deptno from emp group by deptno;

select *from emp

where(sal,deptno) in (select max(sal),deptno from emp group by deptno);

-- 子查询 ---------

-- 可以使用子查询的地方：

-- 1-select 子句后

-- 得到工资为所有员工工资的百分

selectempno,ename,deptno,sal,round(sal/(select sum(sal) from emp )*100,2)||'%' persalfrom emp;

-- 在查询中使用子查询

-- 2-from子句后：相当于临时表

-- 3-where字句后：使用子查询的结果作为行条件表达式

-- 4-group by ： 不行

-- 5-having：作为分组条件表达式。

-- 6-order by： 不行

select *from

(select *from emp order by sal)

-- 相关子查询

select *from emp e1

whereexists (select * from emp e2 where e2.mgr=e1.empno);

select *from emp where 1=0;

select *from emp where 1=1;

/*

查询原理：

3-列的别名什么时候有效

结果集合产生后有效

4-表的别名什么时候有效

本条SQL语句有效(注意：一旦定义了表的别名，就不能再使用表的原名)

5- order by 排序列asc|desc;

排序的特殊位置：结果集合已经产生，并且还在本条SQL语句内。所以在order by 子句中既可以使用列的别名，也可以使用表的别名。

但是，不能使用： 表的别名.列别名。

*/

select *from(

selectempno eno,deptno dno,ename en

from empe)

wheree.eno=7788;

-- 得到某种工作，只有一个员工做,的员工信息

select *from emp e

where notexists (select * from emp e2 where e2.job=e.job and e2.empno<>e.empno)

-- DML语句中使用子查询

-- 删除scott所在部门的其他员工

deleteemp where deptno=(select deptno from emp where ename='SCOTT') andename!='SCOTT';

select *from emp;

insertinto tab select * from emp where deptno=10;

-- DDL语句中使用子查询

createtable tab as select * from emp where 1=0;

createview v_tab as select * from tab;

-- orderby 的特殊应用：

selectempno eno,ename en,deptno dno, sal from emp e

order bye.eno;

-- 主排序： deptno次排序：empno

select *from emp

order bydeptno asc,empno desc;

--- 高级查询 ----------------------

-- 集合操作符 --- 作用： 把两个结构一样的查询结果，合并

-- 结构一样：1- 列个数相同 2- 列的类型相同

select *from emp where sal>2000;

select *from emp where job='MANAGER';

-- UNION:过滤重复行数据，并且按照对第一列升序排序

select *from emp where sal>2000

UNION

select *from emp where job='MANAGER';

-- UNIONALL: 不过滤重复行数据，不排序

select *from emp where sal>2000

UNION ALL

select *from emp where job='MANAGER';

--INTERSECT : 取交集

select *from emp where sal>2000

INTERSECT

select *from emp where job='MANAGER';

-- MINUS: 使用第一个结果集减去第二个结果集合

select *from emp where sal>2000

MINUS

select *from emp where job='MANAGER';

select *from emp where job='MANAGER'

MINUS

select *from emp where sal>2000;

-----------

selectempno,deptno,ename,sal from emp

union

selectdeptno,empno,job,sal from emp;

-- case 表达式： 给值起别名（decode也可以做到给值起别名）

selects.losal,s.hisal,

case s.grade

when 1 then '试用期工资'

when 2 then '工资一级'

when 3 then '工资二级'

when 4 then '工资三级'

else '工资高级'

endgrade

fromsalgrade s

-- 层次化查询

selecte.*,level

from empe

startwith empno=7839 connect by prior empno=mgr;

-- startwith empno=7839 用来定义树顶（树根）

--connect by prior empno=mgr; 用来定义层次划分关系的

-- 使用当前行的 mgr 判断和 已有分层的empno相同，则level是已有行的level+1

-- 输出层次

selectlpad(' ',level*2)||ename from emp startwith empno=7839 connect by prior empno=mgr;

-- 层次化查询的意义：

-- 1- 取子树：

-- 获取 jons 领导的团队

selectlpad(' ',level*2)||ename from emp startwith empno=7566 connect by prior empno=mgr;

-- 2- 过滤子树：

-- 获取所有员工，但是过滤掉 jons 领导的团队

selectlpad(' ',level*2)||ename from emp

startwith empno=7839

connectby prior empno=mgr and empno!=7566 ;

-- 3- 反向遍历

-- 获取 scott 以及其所有领导

selectlpad(' ',level*2) ||ename,level from empstart with empno=7788 connect by prior mgr=empno;

-- 管理常用对象 -----

-- 注意：一张表最多可以有1000列

-- 1- 表

CREATETABLE [schema.]tablename (

col1 type1(length) default .. ,

col2 type2(length) ...

);

createtable scott.tab(

tabid number,

tabname varchar2(10) default 'aa'

);

insertinto tab values(1,null);

insertinto tab(tabid) values(2);

-- 使用子查询生成表

CREATETABLE table_name [(column,column…]

ASsubquery;

-- 使用子查询建立表，并且自定义列名

createtable tab(eno,ename,ejob,emgr,ehiredate,esal,ecomm,edeptno) as select * fromemp;

-- 使用子查询建立表，并且自定义列名

createtable tab as select empno e_no,ename e_name,sal e_sal from emp;

-- 注意：使用子查询建立表时，不能指定列的类型和精度。

-- 维护表

/*

修改表：

1、增加列：

ALTER TABLE table_name ADD(

column datetype [DEFAULT expr]);*/

alter table tab add address varchar2(20)DEFAULT 'xx';

alter table tab add test1 number;

/*

2、修改定义列：

ALTER TABLE table_name MODIFY(

column datetype [DEFAULT expr]);

注意：能修改什么？

如果一个列没有值，

1- 类型

2- 默认值

3- 非空属性（如果列中已经有非空值不能修改非空属性）

如果一个列中有值，

1- 默认值

2- 非空属性（如果列中已经有非空值不能修改非空属性）

注意：如果有值， char 和 varchar2 可以互相修改，但要注意不要出现精度损失。

*/

alter table tab modify test1 char(20);

alter table tab modify address char(10);

alter table tab modify address varchar2(9);

/*

3、删除列：

ALTER TABLE table_name DROP COLUMNcoloum;

*/

alter table tab drop column address;

/*

4、修改列名：

ALTER TABLE table_name RENAME COLUMN

coloum_name TO new_name;

*/

alter table tab rename column test1 to test2;

/*

5、修改表名：

RENAME table_name TO new_name;

*/

rename tab to tab2;

/*

截断和删除表：

1、当表中的数据不再需要，但表结构需要保留时需要截断表：

TRUNCATE TABLE table_name;

2、当表不再需要时，删除：

DROP TABLE table_name [CASCADECONSTRAINTS][PURGE];

PURGE 用于指定彻底删除表。这是oracle10g以后的新特性。

*/

drop table tab2;

/*

3、恢复删除（闪回）：

FLASHBACK TABLE table_name TO BEFORE DROP;

*/

flashback table tab2 to before drop;

createtable tab2(

tid number

);

-- 绕过回收站的删除

drop table tab2 PURGE;

-- 截断表

createtable tab as select * from emp;

truncatetable tab;

deletetab;

-- 查看当前用户表、对象

selecttable_name from user_tables;

selectobject_name from user_objects;

-- 约束 ————————————————————

-- 约束的意义：为了实现对表中的数据进行逻辑维护，

-- 1- NOTNULL 非空约束

-- 注意：非空约束是列的属性；其他约束为对象

createtable tab(

tid number,

tname varchar2(20) not null

);

insertinto tab(tid,tname) values(1,'aa');

-- 2-PRIMARY KEY 主键约束

-- 注意：主键约束，不允许为null 不允许重复。

-- 主键约束的意义：从逻辑上区分每行数据，别的列也从逻辑上依赖主键（别的列跟主键有唯一对应的关系）；

-- 定义主键：

-- 2.1 定义列的时候定义主键：

createtable tab(

tid number constraint pk_tid primarykey,

tname varchar2(10)

);

insertinto tab values(1,'aa');

-- 2.2 定义表的时候定义主键：

createtable tab(

tid number ,

tname varchar2(10),

constraint pk_tid primary key(tid)

);

-- 2.3 定义表以后定义

createtable tab(

tid number ,

tname varchar2(10)

);

altertable tab add constraint pk_tid primary key(tid);

-- 注意：一个表只能有一个主键

-- 3- 外键约束：

-- 主表：约束表

-- 子表（从表）：被约束表

createtable dept2 as select * from dept;

createtable emp2 as select * from emp;

altertable emp2 add constraint pk_emp2 primary key(empno);

altertable dept2 add constraint pk_dept2 primary key(deptno);

-- 3.1- 建立列时建立

createtable tab(

tabid number,

dno number constraint fk_tab_deptreferences dept(deptno)

);

insertinto tab values(3,10);

-- 3.2- 建立表的时候建立

createtable tab(

tabid number,

dno number,

constraint fk_tab_dept foreign key(dno)references dept(deptno)

);

-- 3.3- 建立表以后建立

altertable emp2 add constraint fk_emp2_dept2 foreign key(deptno) referencesdept2(deptno);

-- 外键约束需要注意问题：

-- 主表对子表的约束：

-- 子表对主表的约束：

deletedept2 where deptno=10;

-- 解除所有子表的外键约束后删除主表。

droptable dept2 cascade constraint;

flashbacktable dept2 to before drop;

alter tableemp2 drop constraint fk_emp2_dept2;

-- 设定级联删除外键

altertable emp2 add constraint fk_emp2_dept2 foreign key(deptno) referencesdept2(deptno) on delete cascade;

deletedept2 where deptno=20;

select *from emp2;

-- 设定级联置空外键

altertable emp2 add constraint fk_emp2_dept2 foreign key(deptno) referencesdept2(deptno) on delete set null;

-- 注意：主表中什么样的列可以作为子表的约束列： 主键列和唯一性约束列

-- 4-UNIQUE 唯一性约束

-- 意义：不允许添加重复值。但是可以为null

-- 4.1- 定义列时定义

createtable tab(

tid number,

tname varchar2(20) constraint uq_tnameunique

);

insertinto tab values(3,'2');

-- 4.2- 定义表的时候定义

createtable tab(

tid number,

tname varchar2(20),

constraint uq_tname unique(tname)

);

-- 4.3- 定义表后定义

createtable tab(

tid number,

tname varchar2(20)

);

altertable tab add

constraint uq_tname unique(tname);

-- 5- 检查约束 ：

-- 意义：自定义简单检查条件的列

-- 5.1- 定义列时定义

createtable tab(

tid number,

tage number constraint ck_agecheck(tage>=18)

);

insertinto tab values(2,19);

-- 5.2- 定义表时定义

createtable tab(

tid number,

tsex number(1),

constraint ck_sex check(tsex in(1,0))

);

insertinto tab values(2,0);

-- 5.3- 定义表以后定义

createtable tab(

tid number,

tsex number(1)

);

altertable tab add

constraint ck_sex check(tsex in(1,0));

/*

维护约束：

1、增加约束：

如果增加notnull约束使用：

ALTER TABLE tab MODIFY col NOT NULL;

如果是增加UNIQUE、PRIMARY KEY、FOREIGNKEY、CHECK

约束：

ALTER TABLE tab ADD [CONSTRAINT c_name]

CON_TYPE (column);

a、ALTERTABLE temp MODIFY name not null;

b、ALTERTABLE ADD

CONSTRAINT t_Ck UNIQUE(name);

c、ALTERTABLE empcon

ADDPRIMARY KEY(name);

d、ALTERTABLE empcon add name varchar2 REFERENCES

dept(dname);

e、ALTERTABLE empcon ADD CHECK(sal between 800 to

5000);

2、修改约束名：

约束是ORACLE中的对象，在同一个用户中是不允许相同的。

修改：ALTERTABLE tab RENAME CONSTRAINT old_cK TO

new_ck;

3、删除约束：

ALTER TABLE tab DROP

CONSTRAINT c_name|PRIMARY KEY[CASCADE]

4、禁止约束

ALTER TABLE tab DISABLE CONSTRAINT c_name[CASCADE]

5、激活约束

ALTER TABLE tab ENABLE CONSTRAINTc_name;

*/

-- 管理对象 -----------

-- 1-视图： 表的逻辑表示，其捆绑一个查询。没有物理数据

-- 建立试图： CREATE [OR REPLACE] VIEW view_name ASsub_query

create orreplace view v_emp as select * from emp;

grant dbato scott;

updateemp set ename=lower(ename) where deptno=30;

select *from v_emp;

-- 给视图的列起别名

create orreplace view v_emp(eno,dno,ejob,ename) as select empno,deptno,job,ename fromemp;

-- 视图分类：

-- 1- 简单视图：视图的数据源来自一张表原始数据

-- 通过简单视图，可以直接对简单视图做DML操作而影响表数据（物理数据）。

updatev_emp set ename=upper(ename) where dno=30;

-- 2- 复杂视图：视图的数据源来自多张表；或者对一张表的数据做过加工(分组，DISTINCT，使用过函数..)

-- 2.1- 如果复杂视图由两张没有主外键关系，但有逻辑关系的表组成，则不能修改任何物理数据

create orreplace view v_e2_d2 as

select e.*,d.dname,d.loc from emp2e,dept2 d where e.deptno=d.deptno;

updatev_e2_d2 set ename=lower(ename) where deptno=10;

updatev_e2_d2 set dname=lower(dname) where deptno=20;

-- 2.2- 如果复杂视图由两张有主外键关系的表组成，则只能修改子表数据。不能修改主表

updatev_e2_d2 set ename=upper(ename) where deptno=10;

updatev_e2_d2 set dname=lower(dname) where deptno=20;

-- 视图的意义：用来显示物理数据，其本身没有物理数据。

-- 试图使用规范：一般只用来显示数据，不用来修改数据。如果需要借助视图做DML操作，

-- 则需要使用“视图触发器”

-- 2- 索引 ： 索引是为了提高检索数据的效率而对一张物理表产生的一个子表。

-- 索引的自动建立：当某个列设定为主键或者唯一性约束后，自动会对这列添加索引

-- 索引的使用：当用户做查询时，由数据库本身决定是否使用索引。

-- 3- 序列： 序列号生成器

createsequence SQ_E

minvalue1

maxvalue999999999999999999999999999

startwith 10

incrementby 1

cache 20;

-- 序列的使用

insertinto emp (empno,deptno) values(sq_e.nextval,20);

insertinto emp2 (empno,deptno) values(sq_e.nextval,20);

-- 使用下个序列号，并跳号

序列.nextval

-- 查看当前序列号，不跳号

序列.currval

selectsq_e.nextval from dual;

selectsq_e.currval from dual;

-- 4 同义词：可以跨用户，像使用自己的数据源表一样使用别的用户数据。

-- 4.1 公有同义词

CREATEPUBLIC SYNONYM sy_emp FOR emp;

-- 4.2 私有同义词

CREATESYNONYM sy_dept FOR dept;

grantselect on emp to test;-- test用户也需要访问scott.emp 的权限，才能访问对应的同义词。

select *from sy_emp;

select *from scott.sy_dept;

-- 分析函数

-- 计算正在运行的行

selectempno,ename,deptno,count(*) over(order by empno) from emp;

-- 逐行的显示一个部门的累计工资。每行包括前面各行工资的总和。

selectempno,ename,deptno,sal,sum(sal) over(partition by deptno order by empno) fromemp;

--显示在某些部门中付给个人的总工资的百分数。将他们的工资与该部门的工资总和相除。

selectempno,ename,deptno,sal,round(sal/sum(sal) over(partition by deptno)*100,2)||'%'persum,

sum(sal) over(partition by deptno) deptsalfrom emp;

--pageRownum:r page: n start row:(n-1)*r+1 end row: n*r

selectt2.* from(

selectt.*,rownum rn from(

selecte.* from emp e order by sal

) t) t2where rn>=6 and rn<=10;

select *from(

selecte.*,row_number() over(order by sal) rn

from empe) where rn between 11 and 15;

-- 计算正在流动平均值

selectempno,ename,sal,avg(sal) over(order by empno) from emp;

-- 得到部门工资最高的前三个人的信息

select *from (

selectempno,ename,sal,deptno,rank() over(partition by deptno order by sal desc) rkfrom emp)

where rkbetween 1 and 3 ;

-- 分析函数语法格式： 功能函数(..) over()

-- over()的功效： 复制功能函数的结果，匹配相应的行。

-- 得到每个员工的信息和所有人的总工资信息

selecte.*,sum(sal) over() from emp e;

-- 错误：select sum(sal) over(),e.* from emp e groupby deptno;

-- over 中的： partition by 分区子句:

--partition by 子句会在指定的分区求功能函数的结果并在指定的分区复制这个结果。

-- 注意：如果不指定partition by 子句，则所有行在一个分区。

-- 得到工资高于部门平均工资的人员信息

selecte.*,avg(sal) over(partition by deptno) from emp e;

-- over 中的： order by 排序子句：

-- orderby 排序子句：在指定分区内排序结果，并影响最终结果的顺序。

-- 注意：同时会对结果进行数值偏移的开窗方式，来影响功能函数的结果。

selecte.*,sum(sal) over(order by sal) from emp e;

selecte.*,sum(sal) over(partition by deptno order by sal) from emp e;

-- over 中的：windowing 开窗子句：

-- 注意：如果使用 windowing 开窗子句，必须排序（必须要先有 order by 子句）

-- 开窗方式：

-- 1- 行偏移开窗: rows n preceding

selecte.*,sum(sal) over(order by sal desc rows 2 preceding) from emp e;

-- 2- 数值偏移开窗: range n preceding

-- 统计从当前记录开始，比当前记录早入职100天以内的员工人数

selectempno,ename,hiredate-100,hiredate,count(*) over(order by hiredate range 100preceding) from emp;

-- 求出比我当前工资高于500以内的人数

selectempno,ename,sal,sal+500 ,count(*) over(order by sal desc range 500 preceding)from emp;

-- 注意：开窗子句必须要先有 order by 排序子句。

-- 注意：只写 order by 子句，不写windowing 子句，则默认使用数值偏移

-- over(order by sal) 数值往无穷小偏移

-- over(order by sal desc) 数值往无穷大便宜。

selectempno,ename,job,sal,sum(sal) over(order by job) from emp;

-- 分析函数中的功能函数：

--DENSE_RANK（） 不会跳号的等级函数

selectempno,ename,sal,dense_rank() over(order by sal desc) from emp

-- RANK()会跳号的等级函数

selectempno,ename,sal,rank() over(order by sal desc) from emp

--PERCENT_RANK

selectempno,ename,sal,PERCENT_RANK() over(order by sal desc) from emp

-- 注意： 等级函数需要order by子句，否则编译错误。

--FIRST_VALUE ：取分区内的第一个值

--LAST_VALUE ：取分区内的最后一个值

selectempno,ename,sal,job,deptno,

sum(sal) over(partition by job )

,first_value(sal) over(partition bydeptno ) from emp;

selectempno,ename,sal,job,deptno

,sum(sal) over(partition by deptno orderby empno),

count(sal) over(partition by job orderby empno)

from emp;

-- laglead ：可以不用自连接，就可以跨行取数据。

-- 注意：此函数不能没有 order by子句。

selectempno,ename,sal,deptno,lag(sal,3) over(order by sal),

lead(sal,2) over(order by sal) from emp;

-- MAX（expression）

-- MIN（expression）

selectempno,ename,sal,deptno,max(sal) over(partition by deptno order by sal),

min(sal) over (partition by deptno order by sal) from emp;

selectempno,ename,sal,deptno,max(sal) over(partition by deptno order by sal desc),

min(sal) over (partition by deptno orderby sal desc) from emp;

--NTILE（expression）

-- 需要 order by子句

selectempno,ename,sal,deptno,ntile(4) over(order by sal) from emp;

--row_number(): 运行统计函数

-- 需要 order by子句

selectempno,ename,sal,job,deptno,

row_number() over(order by sal) from emp;

-- 查询以 A开头的员工名字的员工信息

select *from emp where ename like 'A%';

-- 查询以 L为第二个字符的后面跟任意字符的名字

select *from emp where ename like '_L%';

select *from emp where ename like '_%K%';

insertinto emp(empno,job) values(1000,'asdf%asdf');

-- 找一个工作中含有%号的人员信息

select *from emp

where joblike '%|%%' escape '|';

-- PL/SQL匿名块

-- 1-定义部分

declare

v_var number;

v_var2 varchar2(20) :='abc';

begin

-- 2-执行部分

v_var := 10/0;-- 注意： 赋值号是:=

dbms_output.put_line(v_var||' '||v_var2);

-- 3-异常处理部分

-- 注意：异常处理部分语句是语句块最后的语句，其后不允许放置正常执行语句。

exception when others then -- others 相当于异常父类，可以使用它捕获所有异常

dbms_output.put_line('有异常');

end;

--Command(SQLPLUS)打开输出标记命令： set serveroutput on;

-- 使用嵌套方式可以实现异常后继续执行的语法格式

declare

begin

begin

exception when others then

...

end;

...

end;

-- 变量的生命周期：其decalre后紧跟的begin end：之间的范围。

declare

v_var number:=10;

begin

declare

v_var2 number:=v_var;

begin

dbms_output.put_line(v_var);

end;

dbms_output.put_line(v_var2);

end;

-- 使用PL/SQL查询表中数据

-- 使用 %Type%Rowtype

declare

v_ename emp.ename%type;

begin

-- select 查询语句不能直接应用在PL/SQL中，

select ename into v_ename from emp whereempno=&员工编号;

dbms_output.put_line(v_ename);

exception when others then

dbms_output.put_line('没有查到数据');

end;

--

declare

v_empno emp.empno%type;

v_ename emp.ename%type;

v_job emp.job%type;

v_mgr emp.mgr%type;

v_hiredate emp.hiredate%type;

v_sal emp.sal%type;

v_comm emp.comm%type;

v_deptno emp.deptno%type;

begin

select * intov_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_deptno

from emp where ename='&员工姓名';

dbms_output.put_line(v_empno||' '||v_deptno||' '||v_ename||''||v_sal);

end;

--

declare

v_row emp%rowtype;

begin

select * into v_row

from emp where ename='&员工姓名';

dbms_output.put_line(v_row.empno||' '||v_row.deptno||' '||v_row.ename||' '||v_row.sal);

end;

-- select... into ... 只能访问一行数据，如果想访问多行数据，必须使用游标

declare

v_row emp%rowtype;

begin

select * into v_row

from emp ;

dbms_output.put_line(v_row.empno||' '||v_row.deptno||' '||v_row.ename||' '||v_row.sal);

end;

-- 程序控制语法格式：

-- IF 条件(boolean 表达式)then 执行过程 end if;

-- 注意 elsif 是正确的写法

declare

v_num1 number:=&num1;

v_num2 number:=&num2;

begin

if v_num1>v_num2 then

dbms_output.put_line('num1 > num2');

elsif v_num1=v_num2 then

dbms_output.put_line('num1 = num2');

else

dbms_output.put_line('num1 <= num2');

end if;

end;

-- 多条件分支语法结构：

declare

v_info varchar2(20);

v_num number;

begin

v_num:=&num;

case v_num

when 0 then

v_info:='零';

when 1 then

v_info:='壹';

when 2 then

v_info:='贰';

else

v_info:='其他';

end case;

dbms_output.put_line(v_info);

end;

-- 搜索式CASE条件

declare

v_info varchar2(20);

v_num number;

begin

v_num:=&num;

case

when v_num=0 then

v_info:='零';

when v_num=1 then

v_info:='壹';

when v_num=2 then

v_info:='贰';

when 1=1 then

v_info:='1=1';

else

v_info:='其他';

end case;

dbms_output.put_line(v_info);

end;

-- CASE表达式

declare

v_info varchar2(20);

v_num number;

begin

v_num:=&num;

v_info := case v_num

when 0 then '零'

when 1 then '壹'

when 2 then '贰'

else '其他'

end;

dbms_output.put_line(v_info);

end;

-- 搜索式的case表达式

declare

v_info varchar2(20);

v_num number;

begin

v_num:=&num;

v_info := case

when v_num=0 then '零'

when v_num=1 then '壹'

when v_num=2 then '贰'

else '其他'

end;

dbms_output.put_line(v_info);

end;

-- 循环：

-- 简单 LOOP：

declare

v_sum number:=0;

v_i number:=1;

begin

loop

v_sum:=v_sum+v_i; -- 注意： PL/SQL中没有 运算符号： ++ --+= ...

v_i:=v_i+1;

-- if v_i>100 then

-- exit; -- 注意：退出循环的命令： exit

-- end if;

-- 简化退出循环的写法

exit when v_i>100;

end loop;

dbms_output.put_line(v_sum);

end;

-- WHILE 循环

declare

v_sum number:=0;

v_i number:=1;

begin

while v_i<=100 loop

v_sum:=v_sum+v_i;

v_i:=v_i+1;

end loop;

dbms_output.put_line(v_sum);

end;

-- FOR 循环

declare

v_sum number:=0;

v_i number:=1;

begin

for v_i in 1..100 loop

v_sum:=v_sum+v_i;

end loop;

dbms_output.put_line(v_sum||' '||v_i);

end;

begin

for v_i in REVERSE 1..10 loop

dbms_output.put_line(v_i);

end loop;

end;

-- 异常： 是语句块中最后的执行语句，其后会跟异常的处理语句，不允许正常语句出现。

declare

v_i number;

begin

v_i:=10/1;

dbms_output.put_line(v_i);

exception when others then

dbms_output.put_line('异常');

dbms_output.put_line('程序结束');

end;

-- 错误类型(异常分类)：

-- PLS 编译错误

-- ORA 运行错误

-- 错误代号：一串表示错误代号的数字（在Oracle中记录错误的编号）； 特点：一串负值

-- 错误文本：错误消息，包括错误代号； 引用错误文本的关键字 sqlerrm

declare

v_i number;

begin

v_i:=10/0;

dbms_output.put_line(v_i);

exception when others then

dbms_output.put_line(sqlerrm);

end;

declare

v_ename emp.ename%type;

begin

select ename into v_ename from emp wherejob='&job';

dbms_output.put_line(v_ename);

-- 多个异常处理语句：

-- others 是相当于异常父类，所以，when others then 语句必须放到别的异常处理语句之后，否则导致其后的异常处理语句无效。

exception

when no_data_found then

dbms_output.put_line('没找到查询数据：'||sqlerrm);

when too_many_rows then

dbms_output.put_line('查到多个结果： '||sqlerrm );

when others then

dbms_output.put_line('其他异常： '||sqlerrm);

end;

-- 自定义异常（给异常编号实名）

declare

my_exception exception;

PRAGMA EXCEPTION_INIT(my_exception,-2291);

begin

insert into emp(empno,deptno)values(7000,60);

exception when my_exception then

dbms_output.put_line('自定义异常：'||sqlerrm);

end;

-- 自定义异常（ raise_application_error(...)）： 相当于java中的 throw

declare

v_sex varchar(20);

begin

v_sex := '&性别';

if(v_sex = '男'or v_sex='女') then

dbms_output.put_line(v_sex);

else

raise_application_error(-20000,'性别只能是男或者女');-- throw

end if;

exception when others then

dbms_output.put_line(sqlerrm);

end;

-- 游标：

-- 定义游标格式： CURSOR 游标名称 IS 查询结果集合

-- 对游标的操作：OPEN-->FETCH-->CLOSE;

-- 使用loop循环操作游标

declare

cursor cur_emp is select * from emp;

v_row emp%rowtype;

begin

-- 1- 打开游标

open cur_emp;

-- 2- 提取游标数据

loop

fetch cur_emp into v_row;

exit when cur_emp%notfound;-- 判断游标到结果集合尾，不再能提取到数据

dbms_output.put_line(v_row.empno||''||v_row.ename||' '||v_row.deptno||' '||v_row.sal);

end loop;

-- 3- 关闭游标

close cur_emp;

end;

-- 使用while循环操作游标：

declare

cursor cur_e_d is selecte.empno,e.ename,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

v_empno emp.empno%type;

v_ename emp.ename%type;

v_dname dept.dname%type;

v_loc dept.loc%type;

begin

open cur_e_d;

fetch cur_e_d intov_empno,v_ename,v_dname,v_loc;

while cur_e_d%found loop

dbms_output.put_line(v_empno||''||v_ename||' '||v_dname||' '||v_loc);

fetch cur_e_d intov_empno,v_ename,v_dname,v_loc;-- 注意：需要再次fetch

end loop;

close cur_e_d;

end;

-- 游标属性：

/*

%FOUND 指明是否取到了指定的记录行

%ISOPEN 指明游标是打开的还是关闭的

%NOTFOUND 指示FETCH是否失败或是否还有可取的记录行

%ROWCOUNT 指明总共取到了多少行数据

注意：不能重复打开，不能重复关闭，不能关闭一个没有打开的游标。 否则报ORA错误

*/

declare

cursor cur_emp is select * from emp;

v_row emp%rowtype;

begin

-- 检测游标是否打开

if not cur_emp%isopen then

open cur_emp;

end if;

loop

fetch cur_emp into v_row;

exit when cur_emp%notfound;-- 判断游标到结果集合尾，不再能提取到数据

dbms_output.put_line(cur_emp%rowcount||''||v_row.empno||' '||v_row.ename||' '||v_row.deptno||' '||v_row.sal);

end loop;

-- 检测游标是否打开

if cur_emp%isopen then

close cur_emp;

end if;

end;

-- 参数化游标：带有参数的游标

-- 参数化游标的意义：影响游标的结果集合。

/*

public static void method(int x){

System.out.println("test");

}

*/

-- 注意：形参的定义，不能带精度。

declare

cursor cur_e_d(dno number,mon number) isselect empno,ename,dname,loc,sal*mon saldno from emp,dept whereemp.deptno=dept.deptno and dept.deptno=dno;

v_row cur_e_d%rowtype;-- 使用游标来定义 引用行类型。

begin

open cur_e_d(20,12);-- 参数化游标，需要在打开游标时传递实参。

loop

fetch cur_e_d into v_row;

exit when cur_e_d%notfound;

dbms_output.put_line(v_row.empno||' '||v_row.ename||' '||v_row.dname||' '||v_row.loc||' '||v_row.saldno);

end loop;

close cur_e_d;

end;

-- 使用更新游标

-- 更新游标的意义：逐行判断复杂的逻辑条件来更新游标所指的当前行数据。

-- 更新游标的定义： CURSOR 游标名称IS 结果集合 FOR UPDATE;

-- FOR UPDATE 的作用：锁定生成游标的结果集合，结果集合范围是多少，则锁定多少。

--current of 游标，是根据伪列： rowid 来取得物理数据。

-- 修改10 部门人员工资，让每个人工资+100

update emp set sal=sal+100 where deptno=10;

-- 修改10 部门人员工资，让每个人工资+100,加完后如果超过原工资的5%，则保持原工资不变。

declare

cursor cur_emp is select * from emp for update;

v_row emp%rowtype;

begin

open cur_emp;

loop

fetch cur_emp into v_row;

exit when cur_emp%notfound;

if v_row.deptno=10 and100/v_row.sal<0.05 then

update emp set sal=sal+100 wherecurrent of cur_emp; -- 获取当前行： current of 游标

end if;

end loop;

close cur_emp;

end;

-- 多表的更新游标

-- 需要指定游标的rowid的锁定

declare

cursor cur_e_d is selectempno,ename,dname,loc from emp,dept where emp.deptno=dept.deptno for update;

v_row cur_e_d%rowtype;

begin

open cur_e_d;

fetch cur_e_d into v_row;

while cur_e_d%found loop

update emp set ename=lower(ename) wherecurrent of cur_e_d;

fetch cur_e_d into v_row;

end loop;

close cur_e_d;

end;

-- 使用 emp join dept

declare

cursor cur_e_d is selectempno,ename,dname,loc from emp join dept using(deptno) for update;

v_row cur_e_d%rowtype;

begin

open cur_e_d;

fetch cur_e_d into v_row;

while cur_e_d%found loop

update emp set ename=lower(ename) wherecurrent of cur_e_d;

fetch cur_e_d into v_row;

end loop;

close cur_e_d;

end;

-- deptjoin emp

declare

cursor cur_e_d is selectempno,ename,dname,loc from dept join emp using(deptno) for update;

v_row cur_e_d%rowtype;

begin

open cur_e_d;

fetch cur_e_d into v_row;

while cur_e_d%found loop

update emp set ename=lower(ename) wherecurrent of cur_e_d;

fetch cur_e_d into v_row;

end loop;

close cur_e_d;

end;

-- 制定多表的rowid为锁定表

declare

cursor cur_e_d is selectempno,ename,dname,loc from emp,dept where emp.deptno=dept.deptno for update ofemp.deptno;

v_row cur_e_d%rowtype;

begin

open cur_e_d;

fetch cur_e_d into v_row;

while cur_e_d%found loop

update emp set ename=lower(ename) wherecurrent of cur_e_d;

fetch cur_e_d into v_row;

end loop;

close cur_e_d;

end;

declare

cursor cur_e_d is selectempno,ename,dname,loc from emp,dept where emp.deptno=dept.deptno for update ofdept.deptno;

v_row cur_e_d%rowtype;

begin

open cur_e_d;

fetch cur_e_d into v_row;

while cur_e_d%found loop

update dept set dname=lower(dname)where current of cur_e_d;

fetch cur_e_d into v_row;

end loop;

close cur_e_d;

end;

-- 游标中的NOWAIT子句：指定游标不等待事务

declare

cursor cur_emp is select * from emp wheredeptno=10 for update nowait;

begin

-- 游标FOR循环： 优点-- 可以隐含执行 open fetch close

-- 缺点--不能用在参数化游标上，以及游标变量上。

for v_row in cur_emp loop

update emp set ename=lower(ename) wherecurrent of cur_emp;

end loop;

end;

-- 游标FOR循环的应用：

declare

cursor cur_emp is select * from emp;

begin

for v_row in cur_emp loop

dbms_output.put_line(v_row.empno||' '||v_row.ename||' '||v_row.job||' '||v_row.sal||' '||v_row.deptno);

end loop;

end;

-- 使用游标FOR循环甚至可以不用定义游标

begin

for v_row in (select * from emp) loop

dbms_output.put_line(v_row.empno||' '||v_row.ename||' '||v_row.job||' '||v_row.sal||' '||v_row.deptno);

end loop;

end;

-- 游标变量：由游标类型定义出来的一种对象，需要在open时 指定结果集合。

-- 游标类型： 定义游标变量的一种自定义类型。

-- 格式： TYPE 游标类型名称 IS REF CURSOR;

declare

TYPE my_cur IS REF CURSOR;

cur_mymy_cur;

v_row emp%rowtype;

begin

-- 需要在open时指定游标的结果集合

open cur_my for select * from emp;

loop

fetch cur_my into v_row;

exit when cur_my%notfound;

dbms_output.put_line(v_row.empno||''||v_row.ename||' '||v_row.deptno||' '||v_row.sal);

end loop;

close cur_my;

end;

-- 注意：普通不能使用游标变量来指定游标行引用类型。

declare

TYPE my_cur IS REF CURSOR;

cur_mymy_cur;

v_row cur_my%rowtype;-- 错误

begin

-- 需要在open时指定游标的结果集合

open cur_my for selecte.empno,e.ename,e.deptno,e.sal,d.dname,d.loc from emp e,dept d wheree.deptno=d.deptno;

loop

fetch cur_my into v_row;

exit when cur_my%notfound;

dbms_output.put_line(v_row.empno||''||v_row.ename||' '||v_row.deptno||' '||v_row.sal);

end loop;

close cur_my;

end;

-- 定义带返回类型的游标变量类型

-- 游标的返回类型是规定，游标的打开时指定的结果集合的列，如果不按照返回类型指定打开列，则错误。

declare

type my_cur is ref cursor returnemp%rowtype;

cur_my my_cur;

v_row cur_my%rowtype;

begin

open cur_my for select e.empno,e.deptno,e.salfrom emp e;

loop

fetch cur_my into v_row;

exit when cur_my%notfound;

dbms_output.put_line(v_row.empno||''||v_row.ename||' '||v_row.deptno||' '||v_row.sal);

end loop;

close cur_my;

end;

-- 多表的返回类型指定：

declare

-- 自定义记录（行）类型：

TYPE my_record IS RECORD(

v_empno emp.empno%type,

v_ename emp.ename%type,

v_deptno emp.deptno%type,

v_sal emp.sal%type,

v_dname dept.dname%type,

v_loc dept.loc%type

);

-- 使用自定义的记录类型(行类型)来限定游标类型定义。

TYPE my_cur IS REF CURSOR return my_record;

cur_mymy_cur;

v_row cur_my%rowtype;-- 错误

begin

-- 需要在open时指定游标的结果集合

open cur_my for selecte.empno,e.ename,e.deptno,e.sal,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

loop

fetch cur_my into v_row;

exit when cur_my%notfound;

dbms_output.put_line(v_row.v_empno||''||v_row.v_ename||' '||v_row.v_deptno||' '||v_row.v_sal);

end loop;

close cur_my;

end;

--过程

create orreplace procedure pro_printeEmpInfo is

-- 声明部分

cursor cur_emp is select * from emp;

begin

--执行部分

for v_row in cur_emp loop

dbms_output.put_line(v_row.empno||' '||v_row.ename||' '||v_row.deptno||' '||v_row.sal);

end loop;

exception

when others then

dbms_output.put_line(sqlerrm);

endpro_printeEmpInfo;

-- 执行过程

--command / SQLPLUS：

SQL>execute pro_printeempinfo;

-- 第三方程序调用 ： JDBC

SQL>call pro_printeempinfo();

-- 参数类型：

-- IN 类型

create orreplace procedure pro_printEmpByEmpno(eno number) is

v_row emp%rowtype;

begin

select * into v_row from emp where empno=eno;

dbms_output.put_line(v_row.empno||' '||v_row.ename||' '||v_row.deptno||' '||v_row.sal);

exception

when NO_DATA_FOUND then

dbms_output.put_line(sqlerrm);

when others then

dbms_output.put_line(sqlerrm);

endpro_printEmpByEmpno;

-- 执行：

SQL>exec pro_printempbyempno(7839);

-- 输出类型参数： OUT

create orreplace procedure pro_getEnameByEmpno(eno number,emp_name out varchar2 ) is

v_row emp%rowtype;

begin

select ename into emp_name from emp whereempno=eno;

exception

when NO_DATA_FOUND then

dbms_output.put_line(sqlerrm);

when others then

dbms_output.put_line(sqlerrm);

endpro_getEnameByEmpno;

-- 执行

SQL>variable v_ename varchar2(20);

SQL>exec pro_getenamebyempno(7788,:v_ename);

-- 输入输出类型： IN OUT

create orreplace procedure pro_getSalByEmpno(eno in out number) is

v_row emp%rowtype;

begin

select sal into eno from emp where empno=eno;

exception

when NO_DATA_FOUND then

dbms_output.put_line(sqlerrm);

when others then

dbms_output.put_line(sqlerrm);

endpro_getSalByEmpno;

-- 执行：

SQL>variable eno number;

SQL>exec :eno := 7788;

SQL>exec pro_getsalbyempno(:eno);

-- 规范： 一般只使用输入类型参数。

-- 传递参数的方式：

create orreplace procedure pro_printEname(eno number,v_sal number,dno number) is

v_row emp%rowtype;

v_ename varchar2(20);

begin

select ename into v_ename from emp whereempno=eno and sal=v_sal and deptno=dno;

dbms_output.put_line(v_ename);

exception

when NO_DATA_FOUND then

dbms_output.put_line(sqlerrm);

when others then

dbms_output.put_line(sqlerrm);

endpro_printEname;

-- 1- 按位置传递

SQL>exec pro_printename(7788,3000,20);

-- 2- 按名称传递

SQL>exec pro_printename(v_sal => 3000,eno=>7788,dno => 20);

-- 3- 混合传递

SQL>exec pro_printename(7788,dno => 20,v_sal => 3000);

-- 注意：混合传递时，一旦按名称传递开始，则其后不能再按位置

SQL>exec pro_printename(7788,v_sal => 3000,20);

-- 规范：一般只按位置传递。

-- 查看过程：

SQL>select text from user_source where name='PRO_PRINTENAME';

-- 删除过程

dropprocedure pro_printempbyempno;

-- 函数：

create orreplace function fun_getSalById(eno number) return number is

v_sal number;

begin

select sal into v_sal from emp whereempno=eno;

return v_sal;

exception

when others then

dbms_output.put_line(sqlerrm);

endfun_getSalById;

-- 执行函数：

-- 1-

SQL> varv_sal number;

SQL>execute :v_sal := fun_getsalbyid(7788);

-- 2-

SQL>exec dbms_output.put_line(fun_getsalbyid(7839));

-- 3- 在select 语句中调用

-- 输出员工信息和其工资占部门总共资百分比

create orreplace function fun_getDeptSal(dno number) return number is

v_sal number;

begin

select sum(sal) into v_sal from emp wheredeptno=dno;

return v_sal;

exception

when others then

dbms_output.put_line(sqlerrm);

endfun_getDeptSal;

selecte.*,round(e.sal/fun_getDeptSal(e.deptno)*100,2)||'%' perdeptsal

from empe order by deptno

-- 函数和过程的区别：

--1- 定义时：

-- 1.1-- 过程的关键字 procedure函数：function

-- 1.2-- 过程没有返回类型定义；函数必须有返回类型的定义

-- 1.3-- 过程的执行体没有返回命令； 函数必须有返回命令。

--2- 执行时：

-- 过程直接使用： exec 过程名(...)

-- 函数需要先定义变量或者使用输出语句来调用。

-- 还可以像使用系统定义的函数那样在普通查询中调用自定义函数

/*

SQL> var v_sal number;

SQL> execute :v_sal :=fun_getsalbyid(7788);

SQL> execdbms_output.put_line(fun_getsalbyid(7839));

*/

-- 规范： 如果只是执行某些复杂的动作，或者功能，不需要要返回值，则定义成过程。

-- 如果需要返回值，则定义成函数。

-- 删除函数

dropfunction fun_getsalbyid;

-- 包：

-- 包的意义：管理处理一类问题的函数和过程以及变量。

-- 包的组成：

-- 1- 包的规范部分（声明部分）；

create orreplace package pak_scott is

-- Author: ywb

-- Created : 2012-8-1 10:43:52

-- Purpose : scott operation

-- Public variable declarations

v_sal number;

-- Public function and procedure declarations

function get_salbyempno(eno number) returnnumber;

procedure print_salbyempno(eno number);

--overload procedure

procedure print_ename(dno number);

procedure print_ename(v_job varchar2);

--

function get_sum(num number) return number;

endpak_scott;

-- 2- 包体（定义部分）；

create orreplace package body pak_scott is

-- Private variable declarations

v_deptno number;

-- public Function and procedureimplementations

procedure print_salbyempno(eno number) is

begin

dbms_output.put_line(get_salbyempno(eno));

end;

function get_salbyempno(eno number) returnnumber is

begin

select sal into v_sal from emp whereempno=eno;

return v_sal;

end;

-- private procedure or function

function get_enamebyempno(eno number) returnvarchar2 is

v_ename varchar2(20);

begin

select ename into v_ename from emp whereempno=eno;

return v_ename;

end;

procedure print_enamebydeptno(dno number) is

cursor cur_emp is select empno from empwhere deptno=dno;

begin

for v_row in cur_emp loop

dbms_output.put_line(get_enamebyempno(v_row.empno));

end loop;

end;

-- 实现重载

procedure print_ename(dno number) is

begin

print_enamebydeptno(dno);

end;

procedureprint_ename(v_job varchar2) is

cursor cur_emp is select ename from emp wherejob=v_job;

begin

for v_row in cur_emp loop

dbms_output.put_line(v_row.ename);

end loop;

end;

-- 实现递归

function get_sum(num number) return number is

begin

if num>1 then

return num+get_sum(num-1);

else

return 1;

end if;

end;

endpak_scott;

-- 调用包：

execpak_scott.print_ename(20);

-- 注意：在包的过程中调用时，如果是调用私有的过程或者函数，则必须先定义。

n包中实现重载

-- 触发器：

-- 办公用品出入库管理

-- 入库表触发器：

create orreplace trigger tri_indopt

before insert on indopt

for each row

declare

-- local variables here

v_count number;

begin

-- :new 关键字，指代当前要插入的新记录行

select count(*) into v_count from dopt whereproid=:new.proid;

if v_count>0 then

update dopt set doptnum=doptnum+:new.innumwhere proid=:new.proid;

else

insert into doptvalues(:new.proid,:new.innum);

end if;

endtri_indopt;

-- 添加入库

insertinto indopt values(sq_in.nextval,10,20,sysdate);

insertinto indopt values(sq_in.nextval,10,30,sysdate);

select *from indopt;

select *from dopt;

rollback;

-- 添加出库触发器

create orreplace trigger tri_outdopt

before insert on outdopt

for each row

declare

v_count number;

v_num number;

begin

select count(*) into v_count from dopt whereproid=:new.proid;

if v_count>0 then

select doptnum into v_num from dopt whereproid=:new.proid;

if v_num<:new.outnum then

raise_application_error(-20001,'没有足够货物');

else

update dopt setdoptnum=doptnum-:new.outnum where proid=:new.proid;

end if;

else

-- 没有出库的物品

raise_application_error(-20000,'没有出库货物');

end if;

endtri_outdopt;

-- 添加出库

insertinto outdopt values(sq_out.nextval,20,20,sysdate);

ORA-20000:没有出库货物

ORA-06512:在 "DOPT.TRI_OUTDOPT",line 16

ORA-04088:触发器 'DOPT.TRI_OUTDOPT' 执行过程中出错

SQL>insert into outdopt values(sq_out.nextval,10,60,sysdate);

ORA-20001:没有足够货物

ORA-06512:在 "DOPT.TRI_OUTDOPT",line 10

ORA-04088:触发器 'DOPT.TRI_OUTDOPT' 执行过程中出错

--- 触发器的构成

-- 1- 触发事件：引起触发器工作的事件

-- 1.1 DML触发器(insert delete update) 开发人员需要研究的问题。

-- 1.2 DDL触发器 例： ddl on scott （对scott做DDl操作时出发）

-- 1.3 系统触发器 例： startup database

-- oracle8i 以前只有DML触发器。

-- 2- 触发时机： beforeafter 触发器的工作时机是在DML语句之前或者之后。

-- 3- 触发表： 触发器为之工作的表

-- 4- 触发类型：（以触发频度分类）

-- 4.1- 行级触发器：

--每修改或者删除、添加一条记录都会触发一次。

-- 4.2- 表级触发器(语句级触发器 statement trigger)：

--无论修改或者删除、添加多少条记录，触发器只触发一次。

-- 对emp做触发器

SQL>update emp set ename=lower(ename) where deptno=10;

语句级前触发器===========

行级前触发器--------

行级后触发器--------

行级前触发器--------

行级后触发器--------

行级前触发器--------

行级后触发器--------

语句级后触发器===========

-- 5- 触发器的执行过程

-- 6- 触发条件：限制触发器执行的条件：when 条件

-- 触发器的注意问题：

-- 1- 触发器代码不能超过32K

-- 2- 触发器代码中不能包含DDL语句和事物控制语句

-- 触发器的工作流程：

-- DML语句开始------> 触发器开始--------->触发器结束-------->DML语句结束

-- |

-- error

-- |------------->结束操作（自动回滚当前的DML语句）

-- 表级触发器。

-- 多个触发器捆绑到一个触发器：

create orreplace trigger tri_emp2

before insert or update or delete onemp2

declare

v_day varchar2(20);

begin

select to_char(sysdate,'day') into v_day fromdual;

if v_day='星期六'or v_day='星期日' then

raise_application_error(-20000,'休息日不能修改员工信息');

end if;

endtri_emp2;

insertinto emp2(empno) values(7000);

-- 使用条件谓词

--INSERTING DELETING UPDATING

create orreplace trigger tri_emp2

before insert or update or delete on emp2

declare

v_day varchar2(20);

begin

select to_char(sysdate,'day') into v_day fromdual;

if v_day='星期六'or v_day='星期日' then

if INSERTING then

raise_application_error(-20000,'休息日不能添加员工信息');

elsif UPDATING then

raise_application_error(-20000,'休息日不能修改员工信息');

elsif DELETING then

raise_application_error(-20000,'休息日不能删除员工信息');

end if;

end if;

endtri_emp2;

insertinto emp2(empno) values(7000);

-- :new 当前操作的新记录 :old当前操作的旧记录

-- 做 insert只有 :new 可用 ; 如果使用 :old 会没有任何效果

-- 做 delete只有 :old 可用 ; 如果使用:new 会没有任何效果

-- 做 update都有

-- 注意：

-- 1-:new :old 不能在表级触发器中使用。 否则会报编译错误。

-- 2-:new :old 并不只是你DML语句中修改的列，它们都可以引用全列。

-- 3- 在触发条件中使用当前新记录或者旧记录时，格式new、old 不能使用 :new、:old

updateemp set ename=lower(ename) where deptno=10;

:new.ename:new.empno=:old.empno

create orreplace trigger tri_emp2

before insert or update or delete onemp2

for each row

declare

begin

dbms_output.put_line(:new.empno||' '||:new.ename||' '||:new.deptno||' '||:new.sal);

dbms_output.put_line(:old.empno||' '||:old.ename||' '||:old.deptno||' '||:old.sal);

endtri_emp2;

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

SQL>update emp2 set ename=lower(ename) where deptno=10;

7782 clark 102450

7782 CLARK 102450

7839 king10 5000

7839 KING10 5000

7934 miller10 1300

7934 MILLER10 1300

3 rowsupdated

-- before 和 after 触发器的区别

-- 1- 执行时机的区别

-- 2- 行级前触发器，可以修改:new的值，而行级后触发器不行。

-- 所有员工必须按照指定的员工编号来添加。不能随意指定员工编号

insertinto emp2 (deptno) values(20);

select *from emp2;

-- 触发条件

create orreplace trigger tri_emp2_sq

before insert on emp2

for each row

when (new.deptno=20)

declare

v_sq number;

begin

select sq_emp.nextval into v_sq from dual;

:new.empno := v_sq;

endtri_emp2_sq;

insertinto emp2(deptno) values(40);

select *from emp2;

-- 视图触发器：

createview v_emp_dept as select e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc from empe,dept d where e.deptno=d.deptno;

updatev_emp_dept set ename=lower(ename),dname=lower(dname) where empno=7788;

updateemp set ename=lower(ename) where empno=7788;

updatedept set dname=lower(dname) where deptno=(select deptno from emp whereempno=7788);

-- 建立视图触发器

create orreplace trigger tri_v_emp_dept

instead of update on v_emp_dept

for each row

declare

-- local variables here

begin

update emp set ename=lower(ename) whereempno=:new.empno;

update dept set dname=lower(dname) wheredeptno=(select deptno from emp where empno=:new.empno);

endtri_v_emp_dept;

select *from emp;

select *from dept;

-- 视图触发器和普通触发器的区别：

-- 1- 普通触发器的执行不会终止DML操作，而试图触发器，会终止DML操作。所有操作由视图触发器在其执行过程中完成。

-- 2- 没有触发时机的选择。

-- 3- 视图触发器都是行级触发器。

-- 视图触发器的执行流程

---DML操作开始----->视图触发器开始----->终止原有的DML操作并执行视图触发器过程-------> 试图触发器结束

--|

--error

--|------> 试图触发器结束

updatev_emp_dept set ename=lower(ename) where deptno=20;

-- 变异表的讨论：

-- 变异表：正在执行DML语句进行数据变异的表。

-- 每个部门最多只能有6个人。

-- 解决方案：

-- 1- 定义一个包，其中定义一个保存部门编号的公共变量

create orreplace package pak_deptno is

v_deptno number;

endpak_deptno;

-- 2- 定义一个行级前/后触发器，来获取当前行的 :new.deptno的值，并放置到包的公共变量中

create orreplace trigger tri_emp_b

after insert or update on emp

for each row

declare

begin

pak_deptno.v_deptno:=:new.deptno;

endtri_emp_b;

-- 3- 定义一个表级后触发器来从包中获取部门编号来维护每个部门6个人的逻辑。

create orreplace trigger tri_emp_st_a

after insert or update on emp

declare

v_count number;

begin

select count(*) into v_count from emp wheredeptno=pak_deptno.v_deptno;

if v_count>6 then

raise_application_error(-20000,'每个部门最多6个人');

end if;

endtri_emp_st_a;

--

-- insert一条记录时，读取变异表不会报错，原因是不会读取到脏数据

updateemp set deptno=10 where empno=7788;

insertinto emp(empno,deptno) values(1000,10);

-- 如果使用子查询 insert 时，即使也是只插入一条数据，也会包读取变异表的错误

createtable emp_temp as select * from emp where empno=7788;

deleteemp where empno=7788;

select *from emp;

-- 特权设置----------------------------

-- 特权分类：

-- 1- 系统特权

drop usertest cascade;

createuser test identified by test;

grantcreate session to test;

grantcreate table,create view to test;

-- 分配users表空间使用配额

alteruser test quota unlimited on users;

--给用户授予系统特权并允许向下传递授予：

-- GRANT 权限1,权限2… TO 用户名WITH ADMIN OPTION
grant create session,create table,create view to test with admin option;

-- 将权限授予所用用户：

-- GRANT 权限 TOPUBLIC

grantcreate session to public;

createuser test3 identified by test3;

--撤销用户被授予系统特权：revoke特权 from 用户名

-- 注意：系统特权不存在级联回收。

-- 2- 对象特权

-- 在scott中

grantselect,insert,update(sal,ename),delete on emp to test with grant option;

-- 在test中执行：

select *from scott.emp;

insertinto scott.emp(empno,deptno) values(200,20);

deletescott.emp where empno<=1000;

updatescott.emp set sal=sal+100,ename=upper(ename) where empno=7788;

-- 给用户授予对象特权并允许向下传递授予：

-- GRANT 权限1,权限2… ON 对象TO 用户名 WITH GRANT OPTION

grantselect on scott.emp to test2 ;

-- 注意：对象特权存在级联回收

revokeselect on emp from test;

-- 角色：一组打包的特权-------------------

createrole my_role;

grantcreate session,create table,create view to my_role;

createuser test4 identified by test4;

grantmy_role to test4;

revokecreate session from my_role;