原文出处:http://blog.csdn.net/yueguanghaidao/article/details/7019377
select * from scott.salgrade;
/*解锁scott用户*/
alter user scott account unlock;
alter user scott account lock;
/*修改用户密码(密码必须以字母开头)*/
alter user scott identified by scott123;
alter user scott identified by tiger;
select * from salgrade;
--创建表
--CREATE TABLE <表名> (<字段名1> <字段类型1>,
-- <字段名2> <字段类型2>...)
create table student(
name varchar2(30),
age number(3)
)
--添加数据
--INSERT INTO <表名> <字段名> VALUES <输入参数>
insert into student (name,age) values ('比尔盖茨',20);
insert into student (name,age) values ('乔布斯',22);
--提交
commit;
--查询数据
--SELECT * || <字段名> FROM <表名> [WHERE <条件语句>]
select * from student;
select name from student where age <= 20;
--修改数据
--UPDATE 表名 SET <字段名>=？ [where <条件语句>]
update student set age = 22 where name = '比尔盖茨';
--删除数据
--DELETE FROM 表名 [where <条件语句>]
delete from student where name = '比尔盖茨';
--修改表结构（增加列）
--ALTER TABLE 表名 ADD (<列名> <列类型> [默认值])
alter table student add (sex varchar2(2));
alter table student add (birthda varchar2(20));
--修改表结过(修改列)
--ALTER TABLE <表名> RENAME COLUMN <旧列名> TO <新列名>
alter table student rename column birthda to birthday;
--修改表结过(修改列类型)
--ALTER TABLE <表名> modify (<列名> <列类型> [默认值])
alter table student modify (birthday number(10));
--修改表结构（删除列）
--ALTER TABLE <表名> drop COLUMN <列名>
alter table student drop column sex;
--修改表名称
--ALTER TABLE <旧表名> RENAME TO <新表名>
alter table student rename to teacher;
--删除表所有数据
--truncate table <表名>
--当需要删除表中所有数据时，请使用truncate，效率比delete
--from table 高
truncate table student;
--删除表结构
--DROP TABLE <表名>
drop table teacher;
--利用现有的表创建表
--CREATE TABLE <新表> AS SELECT <旧表列名> FROM <旧表> [where子句]
create table teacher as select name,age from student;
select * from teacher;
--char 固定长度字符 范围0~2000个字节，中文为2个字节
--varchar2 可变长度字符 范围0~2000个字节
--long 可变长度字符 范围2GB个字节,一个表中最多有1个long字段
create table test1(
name1 (5),
name2 varchar2(5),
name3 long
)
insert into test1 (name1,name2,name3) values (
'jack','jerry','longger'
);
insert into test1 (name1,name2,name3) values (
'广州1','jerry','longger'
);
select * from test1;
--增加两列（整型，浮点型）
alter table test1 add (num1 number);
alter table test1 add (num2 number(2));
alter table test1 add (num3 number(16,4));
select * from test1;
insert into test1 (num1,num2,num3) values (
132456789,12,3.141592656256
);
select * from test1;
--增加2列 DATE(精确到秒) 和 TIMESTAMP(可以包含时区),
--插入数据需要使用to_date()
alter table test1 add (d1 date default null);
alter table test1 add (d2 timestamp default null);
insert into test1 (d1,d2) values (
to_date('2012-12-23','YYYY-MM-DD'),
to_date('2011/01/01 01:50:30','YYYY/MM/DD hh:ss:mi')
);
--添加一列RAW 二进制
alter table test1 add(bany raw(1000));
select * from test1;
--添加lob（大对象列）
alter table test1 add(cl clob,bl blob,bf bfile);
select * from test1 for update;
--ROWID,数据表中一行的物理地址，可以唯一表示一行记录
--ROWNUM，查询返回结果集的行数
select t.*,t.rowid from test1 t;
select t.*,t.rowid from test1 t where rowid='AAAM1AAAEAAAAGkAAD';
select rownum,t.* from test1 t where name1 <>'jack';
select * from test1 for update;
--distinct 去除数据表中重复的行
select distinct t.name1 from test1 t;
select * from test1;
--事物控制语言
update test1 set name1 ='广州2' where num1=123;
savepoint mark1;
delete from test1 where num1 = 123;
rollback to savepoint mark1;
commit;
--数据控制语言
grant select on emp to XXX
revoke select on emp from XXX
create table test2(
num1 number,
num2 number
);
insert into test2 values (100,200);
select * from test2;
--算术运算符 + - * /
select num1,num2,(num1+num2) / (num1* num2) as total from test2;
--比较运算符 = ,!= ,< ,> ,<= ,>=, between...and, in, like, is not null
create table student1(
sno number(10),
name varchar2(30),
age number(3),
sex char(1)
);
insert into student1 values(1001,'梅西',20,'m');
insert into student1 values(1002,'贝克汉姆',30,'m');
insert into student1 values(1003,'小罗',25,'m');
insert into student1 values(1004,'C罗',23,'m');
insert into student1 values(1005,'肥罗',50,'m');
insert into student1 values(1006,'卡卡',26,'m');
insert into student1 values(1007,'鲁尼',28,'m');
insert into student1 values(1008,'某某',28,'f');
insert into student1 values(1008,'',28,'f');
select * from student1;
select * from student1 where age < 25;
select * from student1 where sex !='m';
select * from student1 where sno>=1003 and sno <=1006;
select * from student1 where sno between 1003 and 1006;
select * from student1 where age in (23,24,25,26,27,28);
--like 模糊匹配 ，两种方式 一个_表示匹配一个字符（一个字母或一个汉字都用一个_匹配），一个%表示匹配多个字符
select * from student1 where name like '%罗';
select * from student1 where name like '_克%';
select * from student1 where name = '';
--如果插入的数据为''，则在数据表中显示为null，不能用''去判断
select * from student1 where name is null;
--逻辑运算符 AND(与) OR(或) NOT(非)
select * from student1 where sno>=1003 and sno <=1006;
select * from student1 where sno=1003 or sno =1006;
select * from student1 where not sno=1004;
--连接操作符 ||
select ('学号是：'|| sno || ',姓名：'|| name ||',年龄：'|| age ||',性别：'|| sex) as info from student1;
--集合操作符
--UNION(返回两个表中字段的所有数据，相同数据只显示一次) 相当于是并集
--UNION ALL(返回两个表中字段的所有数据，不去除重复行) 相当于是叠加
--INTERSECT（返回两个表中字段的相同数据行，相容的数据只显示一次）交集
--MINUS(返回从第一个查询结果中去除第二个查询结果中出现的行) 补集
create table student2 as (select * from student1);
select * from student2;
insert into student2 values(1009,'姚明',28,'m');
insert into student2 values(1010,'林丹',28,'m');
insert into student2 values(1009,'刘翔',29,'m');
select name from student1
union
select name from student2;
select name from student1
union all
select name from student2;
select name from student1
intersect
select name from student2;
select name from student2
minus
select name from student1;
--sql字符函数
--initcap 把每个英文单词的首字母变大写，其他字母变小写；
select initcap('helLO world') from dual;
--lower 把每个英文字符都变小写
--upper 把每个英文字符都变大写
select lower('helLO world') from dual;
select upper('helLO world') from dual;
--ltrim(char,set) 从左开始把char包含的set字符窜截断，返回剩下的字符串,只能去除最左边出现的set字符串
--rtrim(char,set) 从右开始把char包含的set字符窜截断，返回剩下的字符串
select ltrim('*今夜阳光明媚，*大法好','*') from dual;
select rtrim('*今夜阳光明媚，*大法','') from dual;
--translate(char,from,to) 把char中包含from组合的字符串转换为to，from与to中的字符须一一对应
select translate('中国abcdcdef','中国','美国') from dual;
--replace(char from ,to) 把char中包含from组合的字符串转换为to
select replace('abcdcdef','d','x') from dual;
--instr(char,set) 返回char中set字符（窜）的位置，类似java中的indexof
select instr('welcome to niit','o') from dual;
--substr(char,index,length)从index处开始截取length长度的字符串，
select substr('江苏省常州市',4,3) from dual;
--concat(char1,char2)连接2个字符串
select concat('welcome',' to niit') from dual;
--length 返回字符串的长度
select name,length(name) from student1;
--sysdate()系统时间
select sysdate from dual;
--round(x,y)将日期X四舍五入到Y所指的日期单位（月或年）的第一天
select round(sysdate,'year') from dual;
select trunc(sysdate,'month') from dual;
------------------------转换函数----------------------
--to_char(date) 将日期型数值转换为字符串，默认格式DD-MON-YY
select to_char(sysdate) from dual;
--to_char(date,'format_model')
select to_char(sysdate,'fmyyyy-mm-dd hh24:mi:ss day pm') from dual;
--to_date(char) char 格式DD-MON-YY
select to_date('23-12月-12') from dual;
--to_date(char,'format_model')
--to_char(number)将数字转换成字符串
select to_char(100) from dual;
--to_char(number,'format_model')
select to_char(100,'L999') from dual;
--to_number(char)
select to_number('10000') from dual;
--to_number(cahr,'format_model')
select to_number('￥100','L999') from dual;
---------------------其他函数---------------
--nvl(exp1,exp2) 将exp1的空值null替换成制定的缺省值exp2
select nvl(name,'无名') from student1;
--nvl2(exp1,exp2,exp3) 如果exp1的值不为null，则返回exp2，否则返回exp3
select nvl2(name,name,'无名') from student1;
--nullif(exp1,exp2)如果exp1等于exp2，返回null，否则返回exp1
select nullif('小强','小强') from dual;
--case exp when <表达式1> then <结果1>
-- [when <表达式2> then <结果2>
-- when <表达式3> then <结果3>...]
-- else
--end
select * from student1 for update;
select name,age,
case
when age < 18 then '未成年'
when age < 25 then '青年'
when age < 40 then '中年'
when age < 60 then '壮年'
else 'no age'
end
from student1;
--decode
select name,age,
decode(age,20,'青年',
30,'中年',
23,'青年',
26,'青年',
28,'青年',
25,'青年',
50,'壮年',
'no age')
from student1;
select * from emp;
select * from dept;
select * from salgrade;
select avg(comm) from emp;
select min(sal) from emp;
select max(sal) from emp;
select sum(sal) from emp;
select count(empno) from emp;
--group by 分组
--having 对分组进行检索，使用having对每组进行过滤
--where 必须写在group by 前
select deptno,max(sal) from emp group by deptno;
select sal from emp where deptno=30
group by sal having (sal > 2000);
--order by 排序 asc(默认) desc(倒序)
select * from emp order by sal;
select * from emp order by sal desc,comm ;
--over()实现分组并显示每组中的具体信息
select ename,max(sal) over(partition by sal) from emp;
select ename, row_number() over(order by sal asc) from emp ;
select rownum,ename from emp;
-------------------------多表连接和子查询----------------------
select * from emp;
select * from dept;
--等值连接
select ename,job,sal,emp.deptno,dname,loc
from emp,dept
where emp.deptno = dept.deptno
and emp.deptno=10;
select ename,job,sal,e.deptno,dname,loc
from emp e,dept d
where e.deptno = d.deptno
and e.deptno=10;
--非等值连接
select * from salgrade;
select empno,ename,sal,grade,losal,hisal
from emp e, salgrade s
--where e.sal >=s.losal and e.sal <= s.hisal;
where e.sal between s.losal and s.hisal;
--外连接(+)
--左外连接 能够显示右表中没有跟左表匹配的记录
--右外连接 能够显示左表中没有跟右表匹配的记录
select distinct deptno from emp;
select ename,job,sal,e.deptno,d.deptno,dname,loc
from emp e,dept d
where e.deptno(+) = d.deptno;
--自连接（假定有一张相同的表存在,可以用自连接生成一棵具有上下级关系的树）
select e.empno,e.ename,e.mgr,m.ename
from emp e,emp m
where e.mgr = m.empno;
--交叉连接 cross join 与没有使用where条件语句产生的结果相同(笛卡尔积)
select * from emp cross join dept;
--自然连接 natural join(对所有同名字段进行等值连接，对null的行不查询)
select empno,ename,job,sal,deptno,dname,loc
from emp natural join dept;
--join using(选择某些列进行等值连接) 必须有select中的同名列
select empno,ename,sal,deptno,dname,loc
from emp join dept
using(deptno);
--join on 设置任意连接条件
select empno,ename,sal,comm,e.deptno,dname,loc
from emp e join dept d
on(e.deptno = d.deptno and e.deptno in(10,30));
--多表连接
select ename,dname,grade
from emp join dept
using(deptno)
join salgrade
on(sal between losal and hisal);
--左外连接
select ename,job,sal,deptno,deptno,dname,loc
from emp e full join dept d
using(deptno)
select * from a left join b where a.id=b.id;
select * from a,b where a.id=b.id(+);
select * from b right join a where b.id=a.id;
select * from b ,a where b.id(+)=a;
--------------------------子查询------------------
select * from emp;
select * from emp where sal >
(select sal from emp where ename = 'WARD');
--多行子查询
--in 等于子查询中的任何一个值
select * from emp where job in
(select job from emp where ename='SMITH'
or ename = 'SCOTT');
--ANY 与子查询返回的任何值进行比较(跟最小值比较)
select * from emp where sal >
any(select avg(sal) from emp group by deptno);
30 1567
20 2175
10 2917
--ALL 与子查询返回的任何值进行比较(跟最大值比较)
select * from emp where sal >
ALL(select avg(sal) from emp group by deptno);
--1.列出emp表中各部门的部门号, 部门，最高工资，最低工资
select e.deptno,d.dname,max(e.sal),min(e.sal)
from emp e,dept d
where e.deptno = d.deptno group by e.deptno,d.dname
--2.列出emp表中各部门job为'CLERK'的员工的最低工资，最高工资
select deptno,min(sal),max(sal)
from emp
where job = 'CLERK' group by deptno
--3.对于emp中最低工资小于3000的部门，
--列出job为'CLERK'的员工的部门号，最低工资，最高工资
select deptno,min(sal),max(sal)
from emp
where job = 'CLERK'
group by deptno having min(sal)<3000;
--4.根据部门号由高而低，工资由低而高
--列出每个员工的姓名，部门号，工资
select ename,deptno,sal
from emp order by deptno desc,sal
--5.列出'JAMES'所在部门中每个员工的姓名与部门号
select ename,deptno from emp
where deptno =
(select deptno from emp where ename = 'JAMES');
--6.列出每个员工的姓名，工作，部门号，部门名
select ename,job,e.deptno,dname
from emp e, dept d
where e.deptno = d.deptno;
--7.列出emp中工作为'CLERK'的员工的
--姓名，工作，部门号，部门名
select ename,job,e.deptno,dname
from emp e,dept d
where e.deptno = d.deptno and job = 'CLERK';
--8.对于emp中有管理者的员工，
--列出姓名，管理者姓名（管理者外键为mgr）
select e.ename,m.ename as mgrname
from emp e,emp m
where e.mgr = m.empno and e.mgr is not null;
--9.对于dept表中，列出所有部门名，部门号，
--同时列出各部门工作为'CLERK'的员工名与工作
select dname,d.deptno,ename,job
from emp e,dept d
where e.deptno = d.deptno
and e.job = 'CLERK';
--10.对于工资高于本部门平均水平的员工，
--列出部门号，姓名，工资，按部门号排序
select a.deptno,a.ename,a.sal from emp a,
(select c.deptno,avg(c.sal) as avgsalary from emp c group by c.deptno) b
where a.sal >b.avgsalary and a.deptno = b.deptno
order by a.deptno;
10 2917
20 2175
30 1567
--11.对于emp，列出各个部门中工资高于
--本部门平均水平的员工数和部门号，按部门号排序
select count(a.empno) as allcount,a.deptno from emp a,
(select c.deptno,avg(c.sal) as avgsalary from emp c group by c.deptno) b
where a.sal >b.avgsalary and a.deptno = b.deptno
group by a.deptno order by a.deptno;
--12.对于emp中工资高于本部门平均水平，人数多与1人的，
--列出部门号，人数，按部门号排序
select count(a.empno) as allcount,a.deptno from emp a,
(select c.deptno,avg(c.sal) as avgsalary from emp c group by c.deptno) b
where a.sal >b.avgsalary and a.deptno = b.deptno
group by a.deptno having count(a.empno)>1
order by a.deptno;
--13.对于emp中低于自己工资至少5人的员工，列出其部门号，
--姓名，工资，以及工资少于自己的人数
select rownum,ename from emp;
--top on 子查询 rownum伪列，必须要有1后才能有2
select * from emp where rownum <5;
select * from emp where rownum >5;
select * from emp where rownum >0;
--1.先找出所有rownum(集合)，
--2.对找出的rownum(集合)进行过滤
select ename,deptno,sal,(rn-5) as count from
(select rownum rn,ename,deptno,sal from
(select * from emp order by sal)
where rownum <=14)
where rn >5;
--这种方式更好了
select ename,deptno,sal,(rn-5) as count from
(select rownum rn,ename,deptno,sal from
(select * from emp order by sal)
where rownum <(select count(*) from emp))
where rn >5;
---------------------约束--------------------
--非空约束 not null （只能在列级定义），不能插入null值
create table student3(
sid number(6) not null,
name varchar2(30),
birth date
);
select * from student3;
insert into student3 values (10001,'张三丰',sysdate);
insert into student3 values (10002,'乔峰',to_date('1988-8-8','yyyy-mm-dd'));
insert into student3 values (10002,'乔峰',to_date('1988-8-8','yyyy-mm-dd'));
--unique 唯一性约束，不能插入重复值，null值可以重复插入
--列级unique只能对单列进行约束，
--表级unique可以对单列或多列组合进行约束
create table sutdent4(
sid number(6) unique,
name varchar2(30),
birth date
);
select * from sutdent4;
insert into sutdent4 values (null,'张三丰',sysdate);
insert into sutdent4 values (null,'乔峰',to_date('1988-8-8','yyyy-mm-dd'));
create table student5(
sid number(6),
name varchar2(30),
birth date,
constraint student5_sid_name_nu unique(sid,name)
);
select * from student5;
insert into student5 values (10001,'张三丰',sysdate);
insert into student5 values (10001,'乔峰',to_date('1988-8-8','yyyy-mm-dd'));
insert into student5 values (10001,'乔峰',to_date('1988-8-8','yyyy-mm-dd'));
--primary key 主键， 非空且唯一
create table student6(
sid number(6) primary key,
name varchar2(30),
birth date
);
select * from student6;
insert into student6 values (100001,'张三丰',sysdate);
insert into student6 values (100002,'乔峰',to_date('1988-8-8','yyyy-mm-dd'));
create table student7(
sid number(6),
name varchar2(30),
birth date,
constraint student7_sid_name_pk primary key(sid,name)
);
select * from student7;
insert into student7 values (10001,'张三丰',sysdate);
insert into student7 values (10001,'乔峰',to_date('1988-8-8','yyyy-mm-dd'));
insert into student7 values (10001,'乔峰',to_date('1988-8-8','yyyy-mm-dd'));
--foreign key 外键约束
--在字段末尾加 constraint <约束名> foreign key(<字段名>) references <主表名>(<主表字段名>)
--在字表中有主表相关联的记录，则主表中相关联的记录不能被删除
create table student8(
sid number(6) primary key,
name varchar2(30),
birth date
);
create table s_kc(
sid number(6),
chinese char(1),
english char(1),
japanese char(1),
math char(1),
computer char(1),
constraint s_kc_sid_fk foreign key(sid) references student8(sid)
);
select * from student8;
select * from s_kc;
insert into student8 values (10001,'乔峰',to_date('1988-8-8','yyyy-mm-dd'));
insert into s_kc values (10001,'Y','N','N','N','N');
delete from student8 where sid=10001;
delete from s_kc where sid = 10001;
--check 检查约束 只能在列级定义，不能在表级定义
create table student9(
sid number(6) primary key check(sid>=0),
name varchar2(30) check(length(name)>6 and length(name)<16),
age number(3) check(age >=0 and age<=150),
birth date
);
select * from student9;
insert into student9 values (10001,'乔峰二代真太郎',50,to_date('1988-8-8','yyyy-mm-dd'));
--查看约束
select * from user_constraints;
select * from user_cons_columns;
--添加约束
--alter table add constraint <约束名> <约束类型><字段名>
create table student10(
sid number(6),
name varchar2(30),
birth date
);
alter table student10 add constraint student10_sid_pk primary key(sid);
insert into student10 values (10001,'乔峰',to_date('1988-8-8','yyyy-mm-dd'));
--删除约束
--alter table <表名> drop constraint <约束名>
--alter table <表名> drop <约束类型>
--not null 只能修改
create table student11(
sid number(6),
name varchar2(30) not null,
birth date
);
alter table student10 drop constraint student10_sid_pk;
alter table student10 drop primary key;
alter table student11 modify (name null);
--删除级联约束 cascade
alter table student8 drop primary key cascade;
--禁用约束
alter table <表名> disable constraint <约束名>
--启用约束
alter table <表名> enable constraint <约束名>
------------------------视图--------------------
--创建视图
--create [or replace] view <视图名> <列名>
--as <查询语句>
grant dba to scott;
create or replace view v_emp(员工编号,员工姓名,工资)
as select empno,ename,sal from emp where sal >2000;
--查询视图
select * from v_emp;
select 工资 from v_emp;
--删除视图
drop view v_emp;
--强制创建视图
--create [or replace] [force] view <视图名> <列名>
--as <查询语句>
--复杂视图
--1.函数、分组的使用
create or replace view v_emp1(部门编号,部门最高工资,
部门最低工资,部门平均工资)
as select deptno,max(sal),min(sal),avg(sal)
from emp group by deptno;
select * from v_emp1;
--2.多表的使用
create or replace view v_emp2(工号,姓名,职位,
年薪,"工龄(月)",部门编号,部门名称)
as select empno,ename,job,sal*12+nvl(comm,0),
months_between(sysdate,hiredate),e.deptno,dname
from emp e,dept d
where e.deptno = d.deptno;
select * from v_emp2;
--创建只读视图
create or replace view v_emp3(工号,姓名,职位,
年薪,"工龄(月)",部门编号,部门名称)
as select empno,ename,job,sal*12+nvl(comm,0),
months_between(sysdate,hiredate),e.deptno,dname
from emp e,dept d
where e.deptno = d.deptno
with read only;
------------------------索引---------------------
--创建索引
--create index<索引名>
--on <表名>(列名)
create table student12(
sid number(6),
name varchar2(30),
birth date
);
create index i_s12
on student12(sid,name);
--drop index<索引名>
drop index i_s12;
--查看索引
select * from user_indexes;
select * from user_ind_columns;
--创建函数索引
--create index <索引名>
--on <表名>(function(列名))
create index i_s12
on student12(upper(name));
select * from student12 where upper(name)='SMITH';
--------------------序列--------------------
--创建序列
--create sequence <序列名>
--increment by n 设置序列步长
--start with n 序列的初始值
--maxvalue n\nomaxvalue 序列最大值
--minvalue n\nominvalue 序列最小值
--cycle\nocycle 是否循环
--cache\nocache 是否缓存
--order\noorder 是否排序
create sequence seq_test1
increment by 1
start with 1
maxvalue 9999999999999
minvalue 1
nocycle
cache 10;
--查看序列
select * from user_sequences;
--使用序列 currval(查看序列当前值) nextval(查看下一个序列值)
select seq_test1.nextval from dual;
select seq_test1.currval from dual;
--修改序列
--1.初始值不能被修改
--2.maxvalue不能小于当前序列值,minvalue不能大于当前值
--3.只影响未来序列值
--alter sequence <序列名>
--increment by n 设置序列步长
--maxvalue n\nomaxvalue 序列最大值
--minvalue n\nominvalue 序列最小值
--cycle\nocycle 是否循环
--cache\nocache 是否缓存
--order\noorder 是否排序
alter sequence seq_test1
increment by 2
maxvalue 999999999999999
nocycle
cache 2;
--删除序列
--drop sequence <序列名>
drop sequence seq_test1;
--创建同义词
--synonym 缩短对象的长度
create synonym s for student1;
select * from s;
--删除同义词
drop synonym s;
select s.name from student1 s;
-----------------pl/sql-------------------------
declare
id number(3);
begin
id := 999;
dbms_output.put_line(id);
dbms_output.put_line('hello night');
end;
declare
v_sal number;
v_month constant number := 12;
begin
select sal into v_sal from emp
where ename = 'SMITH';
DBMS_OUTPUT.put_line(v_sal*v_month);
end;
select * from emp;
--pl/sql 属性
--%type 跟表中字段匹配的类型
--%rowtype 表中的一行记录类型
declare
v_job emp.job%type;
v_sal emp.sal%type;
begin
select job,sal into v_job,v_sal
from emp where ename='SMITH';
DBMS_OUTPUT.put_line(v_job);
DBMS_OUTPUT.put_line(v_sal);
end;
declare
v_emp emp%rowtype;
begin
select * into v_emp
from emp where ename='SMITH';
dbms_output.put_line(v_emp.empno);
dbms_output.put_line(v_emp.ename);
dbms_output.put_line(v_emp.job);
dbms_output.put_line(v_emp.sal);
dbms_output.put_line(v_emp.comm);
dbms_output.put_line(v_emp.deptno);
end;
--记录 record(表示一行中某些字段)
--Type <记录名称> is record(
--<字段一> <类型一>,<字段二> <类型二>,...
--);
declare
type emp_record_type is record(
job emp.job%type,
sal emp.sal%type
);
emp_record emp_record_type;
begin
select job,sal into emp_record
from emp where ename='SCOTT';
dbms_output.put_line('工作是:'||emp_record.job);
dbms_output.put_line('工资为:'||emp_record.sal);
end;
select * from emp;
--if 简单的条件判断
--if then end if;
declare
v_sal number(6,2);
begin
select sal into v_sal
from emp1
where ename = 'SMITH';
if(v_sal < 1000) then
update emp1 set sal=v_sal + 500
where ename = 'SMITH';
end if;
commit;
end;
create table emp1 as select * from emp;
select * from emp1;
--if 二路分支结构
--if then else end if;
declare
v_comm number(6,2);.
begin
select nvl(comm,0) into v_comm from emp1
where lower(ename)='smith';
if v_comm != 0 then
update emp1 set comm = v_comm+100
where lower(ename) = 'smith';
else
update emp1 set comm = v_comm+200
where lower(ename) = 'smith';
end if;
commit;
end;
--if 多路分支
--if then elsif then elsif then ...else end if
declare
v_job emp1.job%type;
v_sal number(6,2);
begin
select job,sal into v_job,v_sal from emp1
where ename = 'SMITH';
if v_job = 'MANAGER' then
update emp1 set sal = v_sal + 200
where ename = 'SMITH';
elsif v_job = 'SALESMAN' then
update emp1 set sal = v_sal + 100
where ename = 'SMITH';
else
update emp1 set sal = v_sal + 50
where ename = 'SMITH';
end if;
commit;
end;
select * from emp1;
--case <表达式>
-- when <条件一> then <结果一>
-- when <条件二> then <结果二>
-- when <条件三> then <结果三>
-- when <条件N> then <结果N>
-- else <结果k>
-- end case;
declare
v_deptno emp1.deptno%type;
begin
select deptno into v_deptno from emp1
where ename = 'SMITH';
case v_deptno
when 10 then dbms_output.put_line('开发部');
when 20 then dbms_output.put_line('销售部');
when 30 then dbms_output.put_line('财务部');
else dbms_output.put_line('boss');
end case;
end;
declare
v_sal emp1.sal%type;
begin
select sal into v_sal from emp1
where ename = 'SMITH';
case
when v_sal<1000 then dbms_output.put_line('加薪10%');
when v_sal<2000 then dbms_output.put_line('加薪20%');
when v_sal<3000 then dbms_output.put_line('加薪30%');
else dbms_output.put_line('加薪100%');
end case;
end;
--loop循环
--loop
-- exit then
--end loop
declare
i int:=1;
begin
loop
dbms_output.put_line(i);
exit when i=10;
i:=i+1;
end loop;
end;
--while循环
--while <条件表达式> loop
--end loop
declare
i int:=1;
begin
while i<=10 loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
--for循环 reverse 倒序
--for <变量> in value1..valuen loop
--end loop
begin
for i in reverse 1..10 loop
dbms_output.put_line(i);
end loop;
end;
* * * * *
* * * *
* * *
* *
*
*
* *
* * *
* * * *
* * * * *
********
********
********
********
--goto 无条件的跳转到标签指定的语句
--<<标签名>>
declare
i int :=1;
begin
loop
dbms_output.put_line(i);
if i = 10 then
goto end_loop;
end if;
i:= i+1;
end loop;
<<end_loop>>
dbms_output.put_line('循环结束');
end;
--null 表示不执行任何语句
declare
v_sal emp1.sal%type;
begin
select sal into v_sal from emp1
where ename='SMITH';
if v_sal <2000 then
update emp1 set sal = v_sal*1.1
where ename = 'SMITH';
else
null;
end if;
commit;
end;
select * from emp1;
--too_many_rows异常 系统异常
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_ename,v_sal
from emp where deptno =20;
dbms_output.put_line(v_ename);
dbms_output.put_line(v_sal);
exception
when too_many_rows then
dbms_output.put_line('返回行数太多!');
end;
--no_data_found 异常
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_ename,v_sal
from emp where deptno =50;
dbms_output.put_line(v_ename);
dbms_output.put_line(v_sal);
exception
when no_data_found then
dbms_output.put_line('找不到数据!');
end;
--用户自定义异常 raise显示抛出异常,在exception中处理异常
declare
salEx exception;
v_sal emp.sal%type;
begin
select sal into v_sal from emp
where ename= 'SMITH';
if v_sal <>3000 then
raise salEx;
end if;
exception
when salEx then
dbms_output.put_line('工资算错,需要赔偿!');
end;
--如果不知道会抛出什么异常,直接使用others对异常进行处理
-------------------------游标----------------------
--隐式游标
--%found sql语句影响一行或多行时为true
--%notfound sql语句没有影响任何行时为true
--%rowcount sql语句影响的行数
--%isopen 判断游标是否打开,始终为false
declare
v_emp emp1%rowtype;
begin
select * into v_emp from emp1 where ename='SMITH';
if sql%found then
dbms_output.put_line('数据被更新');
else
dbms_output.put_line('数据无变化');
end if;
end;
begin
update emp1 set sal = 1000 where ename = 'tom';
if sql%found then
dbms_output.put_line('数据被更新');
else
dbms_output.put_line('数据无变化');
end if;
commit;
end;
begin
update emp1 set deptno = 50 where deptno=10;
if sql%found then
dbms_output.put_line(sql%rowcount);
else
dbms_output.put_line('数据无变化');
end if;
commit;
end;
select * from emp1;
--显示游标
--使用步骤
--1.定义 2.打开 3.使用 4.关闭
--打印emp1表中所有信息
declare
--声明变量,显示emp1表中所有字段
v_emp emp1%rowtype;
--定义游标
cursor c_emp1 is select * from emp1;
--声明一个计数器
x int := 0;
begin
--打开游标
open c_emp1;
--提取数据
--loop循环
/*loop
fetch c_emp1 into v_emp;
exit when c_emp1%notfound;
x := x+1;
dbms_output.put_line(x||':工号:'||v_emp.empno||',姓名:'||v_emp.ename);
end loop;*/
--while循环
fetch c_emp1 into v_emp;
while c_emp1%found loop
x:=x+1;
dbms_output.put_line(x||':工号:'||v_emp.empno||',姓名:'||v_emp.ename);
fetch c_emp1 into v_emp;
end loop;
--关闭游标
close c_emp1;
end;
--打印emp1表中所有信息
declare
--声明变量,显示emp1表中所有字段
v_emp emp1%rowtype;
--定义游标
cursor c_emp1 is select * from emp1;
--声明一个计数器
x int := 0;
begin
--for循环 会自动打开和关闭游标,无须手动控制
for v_emp in c_emp1 loop
x:= x+1;
dbms_output.put_line(x||':工号:'||v_emp.empno||',姓名:'||v_emp.ename);
end loop;
end;
--带参数的显示游标
--cursor <游标名> (参数名 <参数类型(不需要长度)>)
--is <select 语句>
declare
t_sal emp1.sal%type;
v_sal emp1.sal%type;
v_name emp1.ename%type;
cursor c_emp1(c_sal number)
is select ename,sal from emp1 where sal > c_sal;
begin
t_sal := 2000;
--打开游标
open c_emp1(t_sal);
loop
fetch c_emp1 into v_name,v_sal;
exit when c_emp1%notfound;
dbms_output.put_line('姓名:'||v_name||' ,工资:'||v_sal);
end loop;
--关闭游标
end;
--使用显示游标更新行
--cursor <游标名字> is <select 字句> fro update --声明部分
--current of <游标名> --使用部分
declare
v_sal emp1.sal%type;
cursor c_emp1 is select sal from emp1
for update;
begin
--打开游标
open c_emp1;
loop
fetch c_emp1 into v_sal;
exit when c_emp1%notfound;
update emp1 set sal = sal * 1.1
where current of c_emp1;
end loop;
commit;
--关闭游标
close c_emp1;
end;
select * from emp1;
--ref游标 主要用于处理动态sql查询
--type <游标名> is ref cursor [return <返回类型>]
declare
--声明ref游标
type c_emp1 is ref cursor
--返回数据类型
return emp1%rowtype;
--声明游标变量
cur_emp1 c_emp1;
--定义一个返回类型
t_emp1 emp1%rowtype;
begin
--打开游标 for select
open cur_emp1 for select * from emp1;
loop
--取出一行数据
fetch cur_emp1 into t_emp1;
exit when cur_emp1%notfound;
--打印数据
dbms_output.put_line(t_emp1.ename||','||t_emp1.sal);
end loop;
--关闭游标
close cur_emp1;
end;
select * from emp1;
--使用游标执行动态sql
--open <游标名> for <动态sql语句:1> using <参数列表>
declare
t_emp emp1%rowtype;
type c_emp1 is ref cursor;--定义变量
cur_emp1 c_emp1;--声明变量
min_sal number;
max_sal number;
begin
min_sal := 3000;
max_sal := 5000;
open cur_emp1 for 'select * from emp1 where sal>:1 and sal <:1 order by sal'
using min_sal,max_sal;
--自动打开游标,执行动态sql
dbms_output.put_line('工资在'||min_sal||'和'||max_sal||'之间的人有:');
loop
fetch cur_emp1 into t_emp;
exit when cur_emp1%notfound;
dbms_output.put_line('姓名:'||t_emp.ename||',工资:'||t_emp.sal);
end loop;
--自动关闭游标
end;
--1.编写一个PL/SQL程序块以显示所给出雇员编号的雇员的详细信息。
create table emp2 as select * from emp;
select * from emp2;
declare
emp_info emp2%rowtype;
begin
select * into emp_info from emp2 where empno=7369;
dbms_output.put_line('编号:'||emp_info.empno);
dbms_output.put_line('姓名:'||emp_info.ename);
dbms_output.put_line('工作:'||emp_info.job);
dbms_output.put_line('入职时间:'||emp_info.hiredate);
dbms_output.put_line('工资:'||emp_info.sal);
dbms_output.put_line('奖金:'||nvl(emp_info.comm,0));
end;
--2.编写一个PL/SQL程序块以计算某个雇员的年度薪水总额
declare
v_sal emp2.sal%type;
v_ename emp2.ename%type;
begin
select (nvl(sal,0)+nvl(comm,0))*12,ename into v_sal,v_ename
from emp2 where empno =7369;
dbms_output.put_line('姓名:'||v_ename||',年薪:'||v_sal);
end;
/*3.按下列加薪比执行：
10 5%
20 10%
30 15%
40 20%
加薪的百分比是以他们现有的薪水为根据的。写一PL/SQL以对指定雇员加薪。
*/
declare
v_deptno number :=10;
v_rate number := 1.05;
begin
loop
exit when v_deptno > 40;
update emp2 set sal=sal*v_rate where deptno = v_deptno;
v_deptno := v_deptno +10;
v_rate := v_rate + 0.05;
end loop;
end;
select * from emp2;
--4.编写一PL/SQL以向"emp"表添加10个新雇员编号。
--（提示：如果当前最大的雇员编号为7900，则新雇员编号将为7901到7910）
declare
v_count number;
begin
select max(empno) into v_count from emp2;
for i in 1..10
loop
v_count := v_count +1;
insert into emp2(empno) values (v_count);
end loop;
commit;
end;
--5.自定义异常 接受两个数相除并且显示结果。如果第二个数为0，则显示消息"DIVIDE BY ZERO"。
declare
num1 number;
num2 number;
zeroEx Exception;
begin
num1 := 3;
num2 := 0;
if num2 = 0 then
raise zeroEx;
end if;
dbms_output.put_line(num1/num2);
exception
when zeroEx then
dbms_output.put_line('DIVIDE BY ZERO');
end;
create table emp3 as select * from emp;
select * from emp3;
--1.编写一个PL/SQL程序块，对名字以"A"或"S"开始的所有雇员按他们的基本薪水的10%加薪
declare
cursor c_emp is
select * from emp3 where substr(ename,1,1)='A' or substr(ename,1,1)='S' for update;
begin
for i in c_emp
loop
update emp3 set sal = sal*1.1 where current of c_emp;
end loop;
commit;
end;
--2.编写一PL/SQL，对所有的"销售员"(SALESMAN)增加佣金500
declare
cursor c_emp is
select * from emp3 where job ='SALESMAN' for update;
begin
for i in c_emp
loop
update emp3 set sal= sal+ 500 where current of c_emp;
end loop;
commit;
end;
select * from emp3;
--3.编写一PL/SQL，以提升两个资格最老的"职员"为"高级职员"。（工作时间越长，优先级越高）
declare
cursor c_emp is
select * from emp3 order by hiredate for update;
begin
for i in c_emp
loop
exit when c_emp%rowcount>2;
update emp3 set job='high' where current of c_emp;
end loop;
commit;
end;
--4.编写一PL/SQL，对所有雇员按他们基本薪水的10%加薪，如果所增加的薪水大于5000，则取消加薪。
declare
cursor c_emp is
select * from emp3 for update;
begin
for i in c_emp
loop
if i.sal*1.1<5000 then
update emp3 set sal = sal*1.1 where current of c_emp;
dbms_output.put_line(i.ename||', '||i.sal||', '||i.sal*1.1);
end if;
end loop;
commit;
end;
--5.显示EMP中的第四条记录。
declare
cursor c_emp is select * from emp3;
begin
for i in c_emp
loop
if c_emp%rowcount=4 then
dbms_output.put_line(i.empno);
dbms_output.put_line(i.ename);
dbms_output.put_line(i.job);
dbms_output.put_line(i.mgr);
dbms_output.put_line(i.hiredate);
dbms_output.put_line(i.sal);
dbms_output.put_line(i.comm);
dbms_output.put_line(i.deptno);
exit;
end if;
end loop;
end;
--6.使用REF游标显示"EMP"表中的值
declare
Type emp_cousor is ref cursor return emp3%rowtype;
c_emp emp_cousor;
v_emp emp3%rowtype;
begin
open c_emp for select * from emp3;
loop
fetch c_emp into v_emp;
exit when c_emp%notfound;
dbms_output.put_line(v_emp.empno||','||v_emp.ename);
end loop;
close c_emp;
end;
--在emp3表中找出5位工资最低者,加薪20%
--显示雇员编号,姓名,部门编号,部门名称,
--加薪前工资等级,工资,最高工资,最低工资
--加薪后显示同样内容
select * from
(select empno,ename,e.deptno,dname,grade,sal,hisal,losal
from emp3 e,dept d,salgrade s
where e.deptno = d.deptno
and e.sal between s.losal and s.hisal
order by e.sal)
where rownum <=5;
----------------------存储过程-------------------
/*create [or replace] procedure <过程名>[<参数列表>]
is|as
--声明变量
begin
--业务代码
[exception
when ... then]
end;*/
create or replace procedure find_emp(emp_no number)
as
empname varchar2(30);
begin
select ename into empname from emp3
where empno = emp_no;
dbms_output.put_line('姓名:'||empname);
exception
when no_data_found then
dbms_output.put_line('找不到该员工');
end;
--3种参数类型
--in 输入参数 默认类型 (num in number)
--out 输出参数 (num1 out number)
--in out 输入输出共用一个参数 (num2 in out number)
create or replace procedure pro_info
(emp_no emp3.empno%type,
emp_name out emp3.ename%type,
dept_name out dept.dname%type)
is
begin
select ename,dname into emp_name,dept_name
from emp3 e,dept d
where e.deptno = d.deptno
and e.empno = emp_no;
end pro_info;
--存储过程 授权
--grant execute on <存储过程名> to <用户名>
--删除存储过程
--drop procedure <存储过程名>
--------------------函数--------------------
--定义函数
create [or replace] function <函数名> [<in 参数列表>]
return <数据类型> is|as
--变量声明
begin
--业务代码
return <数据>
[exception
when...then]
--异常处理
end;
--无参函数
create or replace function fun_hello
return varchar2
is
begin
return 'welcome to china';
end;
select fun_hello from dual;
--带输入参数函数
create or replace function get_sal(name varchar2)
return number
as
v_sal emp3.sal%type;
begin
select sal into v_sal from emp3 where ename = name;
return v_sal;
end;
--带输入,输出参数函数
create or replace function get_info(emp_no number,v_job out varchar2)
return varchar2
as
v_name emp3.ename%type;
begin
select ename,job into v_name,v_job from emp3
where empno = emp_no;
return v_name;
end;
--in out类型函数
create or replace function get_info1(emp_no in out number)
return varchar2
as
v_name emp3.ename%type;
begin
select ename,empno into v_name,emp_no from emp3
where empno = emp_no;
return v_name;
end;
--删除函数
drop function <函数名>
------------------包------------------
--包头和包体
--包头
create [or replace] package <包名> is|as
--定义公有变量,游标,存储过程,函数,异常
end <包名>
--包体
create [or replace] package body <包名> is|as
--定义私有变量
--存储过程代码
--函数代码
--游标代码
--异常代码
end <包名>;
create table dept1 as select * from dept;
--添加部门函数
create or replace function addDept(
v_deptno in dept1.deptno%type,
v_dname in dept1.dname%type,
v_loc in dept1.loc%type)
return number
is
begin
insert into dept1 values (v_deptno,v_dname,v_loc);
--使用隐式游标判断是否插入成功
if sql%found then
return 1;
else
return 0;
end if;
commit;
end;
--删除部门函数
create or replace function removedept(
v_deptno dept1.deptno%type)
return number
is
begin
delete from dept1 where deptno = v_deptno;
--使用隐式游标判断是否删除成功
if sql%found then
return 1;
else
return 0;
end if;
commit;
end;
--查询部门过程
create or replace procedure queryDept(
v_deptno in dept1.deptno%type,
v_dname out dept1.dname%type,
v_loc out dept1.loc%type)
is
begin
select dname,loc into v_dname,v_loc
from dept1 where deptno = v_deptno;
end;
--新建包头
create or replace package dept_info
is
function addDept(
v_deptno in dept1.deptno%type,
v_dname in dept1.dname%type,
v_loc in dept1.loc%type)
return number;
function removedept(
v_deptno dept1.deptno%type)
return number;
procedure queryDept(
v_deptno in dept1.deptno%type,
v_dname out dept1.dname%type,
v_loc out dept1.loc%type);
end dept_info;
--新建包体
create or replace package body dept_info
is
--添加部门函数
function addDept(
v_deptno in dept1.deptno%type,
v_dname in dept1.dname%type,
v_loc in dept1.loc%type)
return number
is
begin
insert into dept1 values (v_deptno,v_dname,v_loc);
--使用隐式游标判断是否插入成功
if sql%found then
commit;
return 1;
else
return 0;
end if;
end;
--删除部门函数
function removedept(
v_deptno dept1.deptno%type)
return number
is
begin
delete from dept1 where deptno = v_deptno;
--使用隐式游标判断是否删除成功
if sql%found then
commit;
return 1;
else
return 0;
end if;
end;
--查询部门过程
procedure queryDept(
v_deptno in dept1.deptno%type,
v_dname out dept1.dname%type,
v_loc out dept1.loc%type)
is
begin
select dname,loc into v_dname,v_loc
from dept1 where deptno = v_deptno;
end;
end dept_info;
select * from dept1;
--查看子程序
select * from user_objects;
--查看子程序源代码
select * from user_source;
--1.编写一过程以接受用户输入的三个部门编号并显示其中两个部门编号的部门名称
create or replace procedure pro_show_dname(
no1 dept.deptno%type,
no2 dept.deptno%type,
no3 dept.deptno%type,
dname1 out dept.dname%type,
dname2 out dept.dname%type)
as
v_second number;
begin
v_second:= to_number(to_char(sysdate,'SS'));
dbms_output.put_line(v_second);
if v_second <20 then
select dname into dname1 from dept where deptno = no1;
select dname into dname2 from dept where deptno = no2;
elsif v_second <40 then
select dname into dname1 from dept where deptno = no2;
select dname into dname2 from dept where deptno = no3;
else
select dname into dname1 from dept where deptno = no1;
select dname into dname2 from dept where deptno = no3;
end if;
end;
select * from dept;
--2.编写一过程以显示所指定雇员名的雇员部门名和位置。
create or replace procedure pro_show_dloc(
v_name emp.ename%type,
v_dname out dept.dname%type,
v_loc out dept.loc%type)
as
begin
select dname,loc into v_dname,v_loc
from emp e,dept d
where ename = v_name and e.deptno = d.deptno;
end;
--3.编写一个给特殊雇员加薪10%的过程，这之后，检查如果已经雇佣该雇员超过60个月，则给他额外加薪3000.
create or replace procedure show_sal(
no in number)
as
v_sal emp3.sal%type;
v_hiredate emp3.hiredate%type;
begin
select sal,hiredate into v_sal,v_hiredate
from emp3 where empno = no;
if months_between(sysdate,v_hiredate)>60 then
v_sal := v_sal * 1.1 + 3000;
else
v_sal := v_sal * 1.1;
end if;
update emp3 set sal = v_sal where empno = no;
commit;
end;
select * from emp3 order by hiredate;
/*4.编写一个函数以检查所指定雇员的薪水是否有效范围内。不同职位的薪水范围为：
Designation Raise
Clerk 1500-2500
Salesman 2501-3500
Analyst 3501-4500
Others 4501 and above.
如果薪水在此范围内，则显示消息"Salary is OK"，否则，更新薪水为该范围内的最小值。
*/
create table emp5 as select * from emp;
create or replace function fun_show_sal(
emp_no emp5.empno%type) return varchar2
as
v_job emp5.job%type;
v_sal emp5.sal%type;
v_msg varchar2(50);
begin
select job,sal into v_job,v_sal
from emp5 where empno =emp_no;
if v_job = 'CLERK' then
if v_sal >=1500 and v_sal <= 2500 then
v_msg := 'Salary is OK';
else
v_sal := 1500;
v_msg := 'Salary need update to '||v_sal;
end if;
elsif v_job = upper('Salesman') then
if v_sal >=2501 and v_sal <= 3500 then
v_msg := 'Salary is OK';
else
v_sal := 2501;
v_msg := 'Salary need update to '||v_sal;
end if;
elsif v_job = upper('Analyst') then
if v_sal >=3501 and v_sal <= 4500 then
v_msg := 'Salary is OK';
else
v_sal := 3501;
v_msg := 'Salary need update to '||v_sal;
end if;
else
if v_sal >=4501 then
v_msg := 'Salary is OK';
else
v_sal := 4501;
v_msg := 'Salary need update to '||v_sal;
end if;
end if;
--更新数据
update emp5 set sal = v_sal where empno = emp_no;
commit;
return v_msg;
end;
select * from emp5 for update;
--5.编写一个函数以显示该雇员在此组织中的工作天数。
create or replace function show_days(
emp_no emp5.empno%type) return number
as
v_hiredate emp5.hiredate%type;
v_days number;
begin
select hiredate into v_hiredate from emp5
where empno = emp_no;
v_days := ceil(sysdate - v_hiredate);
return v_days;
end;
/*6.编写一个数据包，它有两个函数和两个过程以操作"emp"表。
该数据包要执行的任务为：
插入一个新雇员；删除一个现有雇员；显示指定雇员的整体薪水（薪水+佣金）；显示指定雇员所在部门名称。
*/
--插入雇员过程
procedure pro_insertemp(
pempno emp5.empno%type,
pename emp5.ename%type,
pjob emp5.job%type,
pmgr emp5.mgr%type,
phiredate emp5.hiredate%type,
psal emp5.sal%type,
pcomm emp5.comm%type,
pdeptno emp5.deptno%type res out number);
--删除雇员过程
procedure pro_removeemp(
pempno emp5.empno%type,
res out number);
--显示薪水函数
function fun_show_sal_comm(
pempno emp5.empno%type
) return number;
--显示部门名称函数
function fun_show_dname(
pempno emp5.empno%type
) return varchar2;
--插入雇员过程
procedure pro_insertemp(
pempno emp5.empno%type,
pename emp5.ename%type,
pjob emp5.job%type,
pmgr emp5.mgr%type,
phiredate emp5.hiredate%type,
psal emp5.sal%type,
pcomm emp5.comm%type,
pdeptno emp5.deptno%type,
res out number)
is
begin
insert into emp5 values (
pempno,pename,pjob,pmgr,phiredate,psal,
pcomm,pdeptno);
if sql%found then
commit;
res := 1;
else
rollback;
res := 0;
end if;
end;
--删除雇员过程
procedure pro_removeemp(
pempno emp5.empno%type,
res out number)
is
begin
delete from emp5 where empno = pempno;
if sql%found then
commit;
res := 1;
else
rollback;
res := 0;
end if;
end;
--显示薪水函数
function fun_show_sal_comm(
pempno emp5.empno%type
) return number
is
v_totalsal number;
begin
select (sal+nvl(comm,0)) into v_totalsal
from emp5 where empno = pempno;
return v_totalsal;
end;
--显示部门名称函数
function fun_show_dname(
pempno emp5.empno%type
) return varchar2
is
v_dname dept.dname%type;
begin
select dname into v_dname
from emp5 e,dept d
where e.empno = pempno
and e.deptno = d.deptno;
return v_dname;
end;
select * from emp5 where empno = 7900;
select * from dept;
-----------------------触发器-------------------------
--创建触发器
--create [or replace] trigger <触发器名>
--{after|before} --指定触发时机
--{insert or delete or update} --指定触发器事件
--on <表名> --指定所监控的表
--{for each row | for each statement} --指定触发的次数
--begin
--业务代码
--end;
--after|before:在什么事件之前或之后执行
--查找正在修改的表用before
--after在更新数据库之后激活触发器
--before在更新数据库之前激活触发器
--insert or delete or update :什么事件
--on <表名> 触发建立在什么表上,即监控什么表
--for each row 行级触发,例:update 1000行,则执行1000次(一行一次)
--for each statement 语句级触发,例:update 1000行,执行一次(一句一次)
--:new 行变量:保存事件发生时新数据所在行,只有insert事件和update事件才有新数据
--:old 行变量:保存事件发生时旧数据所在行,只有delete事件和update事件才有旧数据
--insert into emp values (7999,'','','')
--update emp set sal = 1000 where empno =7369
--delete from emp where empno = 7369
--INSTEAD OF 控制对视图的操作
create or replace trigger tri_emp5_insert
after insert on emp5
for each row
begin
dbms_output.put_line(:new.ename);
end tri_emp5_insert;
insert into emp5 (empno,ename) values (1001,'李刚');
select * from emp5;
create or replace trigger tri_emp5_update
after update on emp5
for each row
declare
--声明变量
begin
dbms_output.put_line(:new.job||','||:old.job);
end;
update emp5 set job = '所长' where ename ='李刚';
select * from emp5;
select * from dept1;
create or replace trigger tri_dept_update
after update on dept1
for each row
begin
--当dept1表中的deptno修改后,emp5表中的deptno做对应的修改
update emp5 set deptno = :new.deptno where deptno = :old.deptno;
end;
update dept1 set deptno = 80 where deptno =90;
select * from emp5;
drop trigger tri_dept1_delete;
create or replace trigger tri_dept1_delete
before delete on dept1
for each row
begin
delete from emp5 where deptno = :old.deptno;
end;
delete from dept1 where deptno = 30;
--对数据不同操作的提示
create or replace trigger tri_emp5_operate1
after insert or update or delete on emp5
begin
--判断插入操作
if inserting then
dbms_output.put_line('向emp5表中插入数据!');
elsif updating then
dbms_output.put_line('更新emp5表数据!');
elsif deleting then
raise_application_error(-20000,'a');
--dbms_output.put_line('删除emp5表数据!');
end if;
end;
insert into emp5 (ename) values ('孙悟空');
update emp5 set ename = '猪八戒' where ename = '李刚';
delete from emp5 where ename = 'SCOTT';
--显示触发器
select * from user_triggers;
--禁用触发器
alter trigger tri_emp5_operate disable;
--启用触发器
alter trigger tri_emp5_update enable;
--删除触发器
drop trigger tri_emp5_update;
select * from emp1;
--1.编写一个数据库触发器以显示当任何时候雇员加薪时的加薪情况。
create table emp6 as select * from emp;
create or replace trigger tri_emp6_update
after update on emp6
for each row
declare
v_sal number;
begin
v_sal :=:new.sal - :old.sal;
dbms_output.put_line(v_sal);
end;
select * from emp6;
update emp6 set sal=sal+1000 where ename = 'SMITH';
--2.编写一个数据库触发器，它允许用户只在上午9.00到下午5.00之间执行DML任务。
create or replace trigger tri_emp6_operate
after insert or update or delete on emp6
declare
v_time number;
begin
v_time :=to_number(to_char(sysdate,'HH24'));
if v_time >=9 and v_time <=17 then
dbms_output.put_line('可以操作数据库');
else
raise_application_error(-20001,'在此时间内不可以操作数据库!');
end if;
end;
insert into emp6 (ename) values ('唐僧');
insert into emp6 (ename) values ('沙悟净');
create table emp7 as select * from emp;
--3.编写一个触发器以检查某个组织中不能有两个总裁。
create or replace trigger tri_emp7_operater
before insert or update on emp7
for each row
declare
v_count number;
begin
--找出emp7表中总裁的数量
select count(job) into v_count
from emp7
where job = 'PRESIDENT';
if v_count >= 1 then
raise_application_error(-20009,'总裁只能有一个!');
end if;
end;
insert into emp7 (job) values ('PRESIDENT');
select count(job)
from emp7
where job = 'PRESIDENT';
4.编写一个数据库触发器，当任何时候某个部门从"dept"中删除时，该触发器将从"emp"表中删除该部门的所有雇员。
------------------------------用户,权限,角色--------------
oracle权限设置
一.权限分类
系统权限:系统规定用户使用数据库的权限.(系统权限是对用户而言)
实体权限:某种权限用户对其他用户的表或视图的存取权限.(是针对表或视图而言)
二.系统权限分类:
DBA:拥有全部特权,是系统最高权限,只有dba才能创建数据库结构
RESOURCE:可以创建实体,不能创建数据库.
CONNECT:登陆数据库权限,不能创建实体,不能创建数据库
对于普通用户:授予connect,resource权限
对于DBA管理用户:授予connect,resource,dba权限
2.1授权命令
grant <权限> to 用户名1，用户名2...
grant connect to jerry;
grant resource to jerry;
grant dba to jerry;
--注:普通用户通过授权可以具有与system相同的权限,
--但永远不能达到与sys用户相同的权限,
--system用户的权限可以被回收
2.2查询用户权限,角色
select * from dba_role_privs where grantee ='JERRY';
select * from dba_sys_privs;
select * from role_sys_privs;
2.3删除用户
drop user <用户名> [cascade]--加上cascade则将用户连同其创建的东西全部删除
2.4系统权限传递
增加with admin option,则得到的权限可以传递
grant connect to jerry with admin option;
grant resource to jerry;
grant connect to jack;
grant resource to jack;
2.5系统权限回收
revoke dba from jerry;
revoke connect from jerry;
revoke connect from jack;
--注:A授权B,B授权C,A回收B权限,对C的权限无影响
三.实体权限的管理
3.1实体权限的分类:select,update,insert,alter,index,delete,all//all包括所有权限 execute//执行存储过程权限
grant all on test1 to jack;
select * from jerry.test1;
create table test1(
name varchar2(30)
);
3.2dba用户可以操作全体用户的任意基表(无须授权,包括删除)
3.3实体权限的数据字典
select owner,table_name from all_tables;--用户可以查询的表
select table_name from user_tables;--查找用户创建的表
select grantor,table_schema,table_name from all_tab_privs;--获权可以存取的表
3.4实体权限的传递 with grant option
grant connect to tom;
grant resource to tom;
grant select,update on test1 to jack with grant option;
select * from jerry.test1;
grant select,update on jerry.test1 to tom;
3.5实体权限回收
revoke select,update on jerry.test1 from jack;
--注:A授权B,B授权C,A回收B权限,同时回收C的权限
oracle 用户管理
一.创建用户的profile文件
create profile test1 limit --test1为资源文件
failed_login_attempts 3--指定锁定用户的登录失败次数
password_lock_time 5 --指定用户被锁天数
password_life_time 30--指定口令可用天数
二.创建用户
create user funny
identified by funny123 --如果密码是数字,请用双引号括起来
default tablespace users
temporary tablespace temp
profile test1
三.修改用户
1.修改口令
alter user funny identified by funny456;
2.修改缺省表空间
alter user funny default tablespace system;
3.修改临时表空间
alter user funny temporary tablespace temp;
4.用户加锁
alter user funny account lock;
5.用户解锁
alter user funny account unlock;
6.查看用户缺省表空间,临时表空间
select username,default_tablespace,temporary_tablespace from dba_users;
7.查看资源文件名
select * from dba_profiles;
四.删除用户
drop user funny;
drop user funny cascade --将用户及其所建的实体全部删除
oracle 角色管理
一.角色的定义
角色是一组权限的集合,将角色赋给一个用户,这个用户不就
拥有了这个角色中的所有权限.
二.系统预定义角色
在数据库安装后,系统自动创建的一些常用的角色.
1.connect,resource,dba 用户简单的数据库管理
2.delete_catalog_role,exectut_catalog_role,select_catalog_role 用于访问数据字典视图和包
3.exp_full_database,imp_full_database 用于导入导出数据
三.管理角色
1.创建角色
create role role1;
2.授权给角色
grant create any table,create procedure to role1;
3.授予角色给用户
grant role1 to tom;
4.查看角色所包含的权限
select * from role_sys_privs;
5.删除角色
drop role role1;
--注:1.无法使用with grant option 为角色授予对象权限
--2.可以使用with admin option 为角色服务系统权限,取消时不是级联
-----------------------临时表------------------------
--临时表分为session,transaction两种
--session级的临时表数据在真个session都存在,知道结束此次session
--transaction级临时表数据在comm,rollback,session结束后会消失
--创建临时表语法
create global temporary table <临时表名>
on commit preserve|delete rows(用perserve时就是
session级临时表,用delete就是transaction级临时表)
--session级临时表
1.建临时表
create global temporary table temp_tab1(
name varchar2(30)
) on commit preserve rows;
2.插入数据
insert into temp_tab1 values ('session table');
3.提交
commit;
4.查询
select * from temp_tab1;
--transaction级临时表
1.建临时表
create global temporary table temp_tab2(
name varchar2(30)
) on commit delete rows;
2.插入数据
insert into temp_tab2 values ('transaction table');
3.提交
commit;
4.查询
select * from temp_tab2;
-----------------------表空间-----------------------
--1.建立表空间
建立表空间使用create tablespace命令完成,需要注意,
一般情况下,建立表空间是特权用户或是dba来执行,如果
其他用户要创建表空间,则用户须具备create tablespace权限
--2.建立数据表空间
在建立数据库后,为便于管理表,最好建立自己的表空间
create tablespace data01 datafile
'd:\tablespace1\data01.dbf' size 20m uniform size 128k;
新建表空间名称为data01,文件为d:\tablespace1\data01.dbf
大小为20m,区间大小为128k;
create tablespace data05 datafile
'd:\tablespace1\data05.dbf' size 20m uniform size 128k;
grant connect to cat;
grant resource to cat;
create table test1(
name varchar2(30)
);
select * from test1;
--3.改变表空间的状态
3.1 知道表空间,显示该表空间包括的所有表
select * from all_tables where tablespace_name= 'DATA01';
3.2 知道表名,查看该表数据哪个表空间
select tablespace_name,table_name from all_tables where table_name = 'TEST1';
3.3 扩展表空间
1.增加数据文件
alter tablespace DATA01 add datafile 'd:\tablespace1\data03.dbf' size 20m;
2.增加数据文件大小
alter database datafile 'd:\tablespace1\data05.dbf' resize 100m;
--注:增加的数据文件不要超过500M
3.设置文件的自动增长
alter database datafile 'd:\tablespace1\data05.dbf' autoextend on next 10m maxsize 500m;
当建立表空间时,表空间处于联机(online)状态,并且该
表空间可以读写,如果要维护表空间,需要修改表空间的状态
1.使表空间脱机
alter tablespace DATA01 offline;
2.使表空间联机
alter tablespace DATA01 online;
3.只读表空间
如果不希望在该表空间上执行update,delete,insert操作,
可以将表空间修改为只读.
alter tablespace DATA01 read only;
4.删除表空间
drop tablespace DATA02 including contents and datafiles;
including contents删除表空间的所有数据库对象
datafiles 删除数据文件
移动数据文件
1.使表空间脱机
alter tablespace data05 offline;
2.使用命令移动数据文件到指定的目标位置(移动物理位置)
move D:\tablespace1\DATA05.DBF c:\DATA05.DBF
3.执行alter tablespace命令(移动逻辑位置)
alter tablespace data05 rename datafile 'd:\tablespace1\data05.dbf' to 'c:\data05.dbf'
4.使表空间联机
alter tablespace data05 online;
grant connect to LONNGER;
grant resource to LONNGER;
create table Test2(
name varchar2(30)
);
1.创建一个数据库
2.创建一个表空间
3.创建一个普通用户
4.给用户授权
5.建多张表或视图
6.再创建一个用户
7.创建一个角色,赋予查询权限
8.把角色授权给第二个用户
9.使用第二个用户测试
----------------------导入/导出-----------------------
导出
导出具体分为:导出表,导出方案,导出数据库三种
导出用exp命令完成,该命令常用选项有:
userid:用于指定执行导出操作的用户名,口令,连接字符串
tables:用于指定执行操作的表
owner:用于指定执行导出操作的方案
full=y 用于指定执行导出操作的数据库
inctype :用于指定导出操作的增量类型
rows :用于指定执行导出操作是否要导出表中的数据
file : 用于指导导出的文件名
注:在导入导出的时候,要到oracle的bin目录下找到exp.exe
导出表
1.导出自己的表
exp userid=scott/tiger@niit08 tables=(emp,dept) file = d:\exp\e1.dmp
2.导出其他方案(用户)的表
如果用户要导出其他方案的表,则需要dba权限或
exp_full_database的权限,如system就可以导出scott的表
exp userid=system/niit@niit08 tables=(scott.emp,scott.dept) file=d:\exp\e2.dmp
3.导出表结构
exp userid=scott/tiger@niit08 tables=(emp,dept) file =d:\exp\e3.dmp rows=N
4.直接导出方式
exp userid=scott/tiger@niit08 tables=(emp,dept) file =d:\exp\e4.dmp direct=y
这种方式比默认的常规方式速度要快,当数据量大时,可以考虑这种方法,
使用这种方法需要数据库的字符集与客户端的字符集完全一致,否则报错
导出方案
导出方案是指使用export工具导出一个方案或多个方案中的所有对象(表,视图,索引,约束...)和数据,并存放到文件中.
1.导出自己方案
exp scott/tiger@niit08 owner=scott file=d:\exp\scott.dmp
2.导出其他方案
如果用户要导出其他方案,则需要dba权限或是exp_full_database的权限,
如system可以导出任何方案
exp system/niit@niit08 owner=(scott) file=d:\exp\system_scott.dmp
导出数据库
导出数据库是指利用export导出所有数据库中的对象和数据,
需要dba权限或是exp_full_database的权限
exp userid=system/niit@niit08 full=y inctype=complete file=d:\exp\db.dmp
inctype=complete(完全增量导出,备份整个数据库)
inctype=incremental('增量型'增量导出,备份上次备份后改变的数据)
inctype=cumulative('累计型'增量导出,只是导出自上次'完全'导出之后数据库中变化了的信息)
drop table emp;
drop table dept;
select * from dept;
select * from emp;
导入表
1.导入自己的表
imp userid=scott/tiger@niit08 tables=(emp,dept) file=d:\exp\e1.dmp
2.导入表到其他用户,需要有dba或imp_full_database权限
imp userid=system/niit@niit08 tables=(emp,dept) file =d:\exp\e2.dmp fromuser=scott
3.导入表结构
imp userid=scott/tiger@niit08 tables=(emp,dept) file=d:\exp\e3.dmp rows=n
4.导入数据
如果对象已经存在,可以只导入数据
imp userid=scott/tiger@niit08 tables=(dept,emp) file=d:\exp\e1.dmp ignore=y
导入方案
导入方案是指使用import工具将文件中的对象和数据导入到一个或多个方案中.
如果要导入其他方案,要求用户具有dba或imp_full_database权限
1.导入自身方案
imp scott/tiger@niit08 file=d:\exp\scott.dmp fromuser=scott
2.导入其他方案,要求用户具有dba或imp_full_database权限
imp system/niit@niit08 file=d:\exp\system_scott.dmp fromuser=scott
导入数据库
imp userid=system/niit@niit08 full=y file=d:\exp\db.dmp
1.新建数据库test1
2.在数据库niit08,test1下新建用户tom,分别给他们赋予connect,resource权限
3.用tom用户在niit08下新建表,插入数据
4.用tom用户在niit08下执行导出表,方案,数据库
5.用tom用户在test1下执行导入表,方案,数据库(先删数据库再导入,或在其他机器上导入)
select * from emp1;
delete from emp1 where empno is null;
insert into emp1 (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (8000,'jack','程序员',7788,to_date(to_char(sysdate,'YYYY-MM-DD'),'YYYY-MM- DD'),3500,null,30);
select * from emp1 where job ='';
update emp1 set ename = 'jerry',job = null where ename='jack';
truncate table emp1;
truncate table emp2;
select * from emp1;
select * from emp2;
select * from dept;
select * from emp5;
----------------------------------返回列表的包-----------
create or replace package package_emp is
Type cur_type_emp is ref cursor;
procedure pro_show_emp(res out cur_type_emp);
end package_emp;
create or replace package body package_emp is
procedure pro_show_emp(res out cur_type_emp) is
cur_emp cur_type_emp;
begin
open cur_emp for select * from emp;
res := cur_emp;
end;
end package_emp;
select * from
(select rownum rn ,t.* from
(select empno,ename,e.deptno,dname,sal
from emp e,dept d
where e.sal>2000 and e.deptno = d.deptno
order by sal) t
where rownum <=5
) where rn >2
select * from emp for update;
select * from emp1;
truncate table emp1;
select * from users;
insert into users values (seq_users.nextval,'犬夜叉',28,'忍者',to_date('1990-3-3','yyyy-mm-dd'),'qyc@qq.com','qyc123','我是主角');
insert into users values (seq_users.nextval,'戈薇',28,'忍者',to_date('1990-3-3','yyyy-mm-dd'),'qyc@qq.com','gw123','我是主角');
----------------------进销存项目------------------
1.创建数据库
数据库名:pro_jxc sid:projxc
2.新建表空间
create tablespace data_jxc datafile
'd:\pro_jxc\data_jxc01.dbf' size 100m uniform size 128k;
3.创建用户
jxc_user
create user JXC_USER
identified by ""
default tablespace DATA_JXC
temporary tablespace TEMP
profile DEFAULT;
4.给用户赋权限
普通用户:connect resource
grant connect,resource to jxc_user;
5.创建数据表
1.users 用户表
2.role 角色表
3.model 模块(权限)表
4.customer 客户表
5.goods 商品表
6.providers 供应商表
7.sal 销售表
8.stock 库存表
9.price 价格表
10.import 进货表
select * from (
select e.*,rownum rn from emp e
where rownum <=5 )
where rn>=1
select * from (
select e.*,rownum rn from emp e
where rownum <=10 )
where rn>=6
select * from (
select e.*,rownum rn from emp e
where rownum <=15 )
where rn>=11
select * from (
select empno,ename,sal,rownum rn from emp
where rownum <=10) where rn >=6
select count(*) from emp;
select * from emp;
select * from guestbook order by id for update;
update guestbook set name = 'tom3' , phone='8887777'
, email='tom@qq.com' , title='datasource' ,
content='<p>hello</p>' , time='2011-03-01' where id=18
select * from guestbook order by id desc
insert into guestbook (id,name,phone,email,title,content,time) values (seq_gb.nextval, 'rrr','123456','rr@qq.com','tt','<p>yy</p>','2011-03-03')
select * from guestbook for update;
declare
v_count number;
begin
for i in 1..100000
loop
insert into guestbook values
(seq_gb.nextval,'tom','123456','tom@qq.com',
'lby','利比亚','2011-02-16');
end loop;
commit;
end;
select count(id) as id from guestbook;
select * from ( select gb.*,rownum rn from guestbook gb
where rownum <=10) where rn >=5
select * from ( select gb.*,rownum rn
from guestbook gb where rownum <=10) where rn >=10