Oracle复习笔记

页面导航:首页 > 数据库 > oracle > Oracle复习笔记

Oracle复习笔记

来源: 作者: 时间:2016-01-29 09:07 【

*----------------------------------------常用命令(sqlplus环境)----------------------------------------* conn scott [email protected] [as sysdba] || [as sysoper] --切换用户show user --当前
/*----------------------------------------常用命令(sqlplus环境)----------------------------------------*/

conn [email protected] [as sysdba] || [as sysoper] --切换用户
show user  					 --当前用户
passw[ord] 					 --修改密码
disc       					 --断开连接
clear scr  					 --清屏
start || @  				 --运行sql脚本
edit       					 --编辑指定sql脚本
spool      					 --sqlplus截屏(sql>spool d:\spool.sql; sql>spool off;)
exit || quit   			 --退出(执行后会commit)
&  			  				   --动态输入值
set linesize    		 --设置显示行宽度(默认80字节)
set pagesize    		 --设置每页显示的行数目(默认14行)
set colsep |         --设置列与列之间的分割符号
set echo on					 --设置运行命令是是否显示语句
set feedback on			 --设置显示“已选择XX行”
set serveroutput on  --启用输出(/表示结束PL-SQL块)
set heading on       --设置显示列名
set timing on        --显示执行速度
set time on          --显示当前时间
set autotrace on     --设置允许对执行的sql进行分析
call pro||fun;       --调用存储过程或函数
--给一个事物命名
set transaction name tname 
--指定一个事物使用回滚段
set transaction use rollback segment 回滚段名
--如果在设置隔离级前有更新表t1没有提交,在设置隔离级后,更新表会等待,先前的提交后,隔离级里的会报错
set transaction  isolation level serializable
--默认情况的设置,如果在设置隔离级前有更新表t1没有提交,在设置隔离级后,更新表会等待,先前的提交后,隔离级里的会执行
set transaction level read commited
--只能读,不能进行dml操作
set transaction read only
--可以进行dml操作默认(默认)
set transaction  read write

/*----------------------------------------导入(imp)、导出(exp)----------------------------------------*/

exp
将数据库内的各对象以二进制方式成damp文件,方便数据迁移。
buffer:下载数据缓冲区,以字节为单位,缺省依赖操作系统
consistent:下载期间所涉及的数据保持read only,缺省为n
direct:使用直通方式        ,缺省为n
feedback:显示处理记录条数,缺省为0,即不显示
file:输出文件,缺省为expdat.dmp
filesize:输出文件大小,缺省为操作系统最大值
indexes:是否下载索引,缺省为n,这是指索引的定义而非数据,exp不下载索引数据
log:log文件,缺省为无,在标准输出显示
owner:指明下载的用户名
query:选择记录的一个子集
rows:是否下载表记录
tables:输出的表名列表

导出整个实例
exp system/admin file=oradb.dmp log=oradb.log full=y consistent=y direct=y;
导出指定用户所有对象
exp dbuser/oracle file=dbuser.dmp log=dbuser.log owner=dbuser buffer=4096000 feedback=10000
导出表
exp dbuser/oracle file=dbuser.dmp log=dbuser.log tables=table1,table2 buffer=4096000 feedback=10000

imp
将exp下载的dmp文件上载到内。
buffer:上载数据缓冲区,以字节为单位,缺省依赖操作系统
commit:上载数据缓冲区中的记录上载后是否执行提交
feeback:显示处理记录条数,缺省为0,即不显示
file:输入文件,缺省为expdat.dmp
filesize:输入文件大小,缺省为操作系统最大值
fromuser:指明来源用户方
ignore:是否忽略对象创建错误,缺省为n,在上载前对象已被建立往往是一个正常现象,所以此选项建议设为y
indexes:是否上载索引,缺省为n,这是指索引的定义而非数据,如果上载时索引已建立,此选项即使为n也无效,imp自动更新索引数据
log:log文件,缺省为无,在标准输出显示
rows:是否上载表记录
tables:输入的表名列表
touser:指明目的用户方

导入整个实例
imp system/admin file=oradb.dmp log=oradb.log full=y buffer=4096000 commit=y ignore=y feedback=10000
导入指定用户所有对象
imp dbuser/oracle file=dbuser.dmp log=dbuser.log fromuser=dbuser touser=dbuser2 buffer=2048000 commit=y ignore=y feedback=10000
导入表
imp dbuser2/oracle file=user.dmp log=user.log tables=table1,table2 fromuser=dbuser touser=dbuser2 buffer=2048000 commit=y ignore=y feedback=10000

/*----------------------------------------用户操作----------------------------------------*/

1)创建用户并指定表空间
create user lee identified by 123 [default tablespace users];

2)修改用户(密码或指定表空间)
alter user lee [identified by newpwd] || [default tablespace users];

3)锁定用户
alter user lee account lock;

4)解锁用户
alter user lee account unlock;

5)删除用户[含所有对象]
drop lee [cascade];

/*----------------------------------------权限操作----------------------------------------*/

授权时加with admin option可以使权限传递,当最上级回收权限时,系统权限不会被级联回收,对象权限会被级联回收.

1)授予系统权限
grant connect,resource to lee [with admin option];

2)授对象权限
grant all on scott.emp to lee [with admin option];

3)撤销权限
revoke resource from lee;
revoke update,delete on scott.emp from lee;

/*----------------------------------------角色操作----------------------------------------*/

connect角色具有一般应用开发人员需要的大部分权限,当建立了一个用户后,
多数情况下,只要给用户授予connect和resource角色就够了。

connect角色具有以下系统权限:

alter session    create cluster    create database link
create session   create view       create sequence

resource角色具有应用开发人员所需要的其他权限,比如建立存储过程、触发器等。
这里需要注意的是resource角色隐含了unlimited tablespace系统权限。

resource角色包含以下系统权限:

create cluster    create indextype    create table
create sequence   create type         create procedure
create trigger

1)创建角色
create role lee not identified;
create role lee identified by tiger;
 
2)删除角色
drop role 角色名;

/*----------------------------------------序列操作----------------------------------------*/

--创建序列
create sequence orcl_seq
increment by 1 --每次增长幅度[默认:1]
start with 1   --开始时的序列号[默认:1]
maxvalue 999   --限制生成的最大值
minvalue 1     --限制生成的最小值
nocycle        --达到最大值后,重新生成序列,[默认:nocycle]
cache 20;      --预先分配20个空间以便更快生成序列

--删除序列
drop sequence orcl_seq;

--使用序列
select orcl_seq.nextval from dual;

--查看序列的当前值(至少使用1次后才可查询)
select orcl_seq.currval from dual;

/*----------------------------------------表操作----------------------------------------*/

--建表
create table tb(
	id number(10) primary key,
	name varchar2(20),
  age number(3) default 0 not null
  --constraint CK_name check(name not in ('@','#','$'))
) tablespace system;

--插入数据
insert into tb values(orcl_seq.nextval,'lee',26);

--修改列
alter table tb modify age default 1
							 modify name not null;
--添加约束
alter table tb add constraint CK_name check(name not in ('@','#','$'))
							 add constraint UQ_name unique(name);
--alter table tb add constraint FK_? foreign key(?) references tb2(?);

--删除约束
alter table tb drop constraint UQ_name;

--修改列名
alter table tb rename column name to tname;

--修改表名(sqlplus)
rename tb to tab;

--删除表
drop table tab;

--复制表(不会复制约束)
create table copy_tb as select ename,sal from scott.emp [where 1=2];

--多行插入(将查询结果集直接插入现有表中)
insert into copy_tb select ename,sal from emp where ename = 'KING';

/*----------------------------------------视图操作----------------------------------------*/

视图是一张虚拟表,是对基表数据的引用,
如果基表数据发生改变,视图中的数据也随之改变,
对视图成功的增删改操作,也将影响基表的数据.
实际开发中,一般只对视图作查询.

--创建视图
create or replace view v_emp
as
select * from emp
with read only;

--删除视图(注:对视图的DML操作会影响原表的数据)
drop view v_emp

/*----------------------------------------索引操作---------------------------------------*/

索引基于表的列创建,给经常用于where条件、分组、排序的列添加索引

1)标准索引
create index 索引名 on 表名(列名);

2)唯一索引,自动为主键、唯一键创建唯一索引
create unique index 索引名 on 表名(列名);

3)组合索引,一般用于多个条件的组合查询
create index 索引名 on 表名(列名1,列名2...);

4)反向键索引,按字节形式将数据反转,用于经常从后匹配的列
create index 索引名 on 表名(列名) reverse;

5)基于函数的索引,在应用函数的列上创建索引
--eg 经常按入职月份查询员工信息,请创建索引
create index ix_hiredate1 on emp(to_char(hiredate,'fmmm'));
--注意:Oracle自动根据查询语句应用索引进行优化
select * from emp where to_char(hiredate,'fmmm')='2';

6)删除索引
drop index 索引名;

/*----------------------------------------事务提交----------------------------------------*/

1、概念
1)事务是1个最小的执行单元(逻辑单元)
2)该单元包含1个或1组数据库操作(增删改)
3)该组操作要么同时成功,要么同时失败

2、特点(ACID)
A- 原子性,事务是不允许再分的单元
C- 一致性,事务前后的数据应该保持一致
I- 隔离性,事务之间是相互独立的
D- 永久性,事务提交后,对数据库的影响是永久的,无法回滚

Oracle中的事务
1)1次连接=1次会话session=1个事务
2)一旦遇到commit或rollback,则提交或回滚该会话中所有未提交的操作
3)对于DDL语句(create/alter/drop),执行成功后相当于执行了commit

--事务提交
commit;

--设置事务回滚点
savepoint p1;

--事务回滚
rollback;
rollback to p1;

--删除表所有数据(不允许回滚)
truncate table tb;

/*----------------------------------------行级锁----------------------------------------*/

1)Oracle自动为 insert/delete/update/select...for update 操作应用行级锁
	 一旦数据被锁住,就不允许其他会话进行操作,直到commit或rollback.

2)用于锁定查询结果集 select * from emp for update;

3)用于检测数据是否被锁定,以及限制用户等待的时间.
   select * from emp for update nowait;  --不等待
   select * from emp for update wait 10; --等待10S

/*----------------------------------------表空间操作----------------------------------------*/

--授权创建表空间系权限
grant create tablespace to test2;

--创建表空间
create tablespace myspace
datafile 'd:\mydata.dbf'
size 20m
autoextend on
next 3m
maxsize 100m;

--扩展表空间
alter tablespace myspace add datafile 'd:\mydata2.dbf' size 30m;
alter database datafile 'd:\mydata.dbf' resize 35m;
alter database datafile 'd:\mydata2.dbf' autoextend on next 3m maxsize 100m;

--删除表空间(including contents and datafiles物理文件会一起删除)
drop tablespace myspace including contents and datafiles;

/*----------------------------------------并集、交集、减集----------------------------------------*/

/*union并集(或的关系,不包含重复记录),union all包含重复记录 */
select * from emp where sal > 2500 union select * from emp where job = 'MANAGER';

/*intersect交集(与的关系)*/
select * from emp where sal > 2500 intersect select * from emp where job = 'MANAGER';

/*munus减集 */
select * from emp where sal > 2500 minus select * from emp where ename = 'MANAGER';

/*----------------------------------------常用函数----------------------------------------*/

--分组函数:针对每组数据返回1个结果
select count(*) 总人数,max(sal) 最高工资,min(sal) 最低工资,sum(sal) 工资总和,avg(sal) 平均工资 from emp;
select count(*) 公司总人数,count(mgr) 有上级的人数 from emp;
select deptno,avg(sal) ageSal from emp group by deptno having avg(sal)>=2000 order by avg(sal) desc;

--日期函数:针对每行数据返回1个结果
select to_date('1-5月-13') - sysdate 天 from dual;
select ename,months_between(sysdate,hiredate)/12 "工龄(年)" from emp;
select ename,hiredate 入职时间,add_months(hiredate,3) 转正日期 from emp;
select * from emp where hiredate = last_day(hiredate);
select * from emp where extract(month from hiredate) = 5;

--字符函数
select length('oracle') from dual;
select lengthb('你好') from dual;
select substr('',7,4) from dual;
select instr('oracle','a') from dual;
select substr('&email',1,instr('&email','@')-1) 用户名 from dual;
select replace('ddl','dl','ml') from dual;
select length('abcabca') - length(replace('abcabca','a')) from dual;
select chr(65) from dual;
select ascii('A') from dual;
select lpad('A',5,0),rpad('A',5,0) from dual;

--数学函数
select ceil(9.1) from dual;
select floor(9.1) from dual;
select round(1.5),round(1.55,1),round(155,-1) from dual;
select trunc(155.55) from dual;

--分析函数(用于数据的排名统计)
select ename,sal,
row_number() over(order by sal desc) row_number,
rank() over(order by sal desc) rank,
dense_rank() over(order by sal desc) dense_rank 
from emp;

--转换函数
select to_char(sysdate,'yyyy-MM-dd hh24:mm:ss day') from dual;
select to_char(sysdate,'yyyy"年"fmmm"月"dd"日"') from dual;
select to_date('2013-5-1 15:33:40','yyyy-mm-dd hh24:mi:ss') from dual;
select to_number('000123') from dual;
select nvl(null,'is null'),nvl('lee','is null') from dual;
select nvl2(null,'is null','not is null') from dual;
select '星期' || decode(7,1,'一',2,'二',3,'三',4,'四',5,'五',6,'六',7,'日') from dual;

--case when的使用
select ename,sal,
case
  when sal = (select sal from emp where ename = 'KING') then 'BOSS'
  when sal between 3000 and 4999 then '太牛了'
  else '太少了'
end 工资水准
from emp;

select ename,sal,
case ename
  when 'KING' then 'BOSS'
  else '员工'
	end 工资水准
from emp;

/*----------------------------------------数据库字典----------------------------------------*/

--查询当前数据库
select * from global_name;
--查询当前用户可以访问的数据库字典
select * from dict where comments like '%grant%';
--查询用户
select * from user_users;
select * from dba_users;
--查看序列
select sequence_name,sequence_owner from user_sequences;
select sequence_name,sequence_owner from all_sequences 
[where sequence_owner = 'SCOTT'];
--查询表(all_tables当前用户表以及可操作表)
select table_name,tablespace_name from user_tables;
select table_name,tablespace_name from dba_tables;
select table_name,tablespace_name from all_tables;
--查看视图
select * from user_views;
select * from dba_views;
--查看索引
select * from user_indexes;
select * from dba_indexes;
--查询表空间
select tablespace_name from dba_tablespaces;
select tablespace_name from user_tablespaces;
--查询当前用户包含的角色
select * from user_role_privs;
--查询所有系统权限
select * from system_privilege_map;
--查询对象权限
select distinct privilege from user_tab_privs;
select distinct privilege from dba_tab_privs [where grantee = 'CONNECT'];
--查询所有角色(dba)
select * from dba_roles;
--查询指定用户包含的角色(dba)
select * from dba_role_privs where grantee = 'SCOTT';
--查询指定角色包含的系统权限(dba)
select * from dba_sys_privs where grantee = 'CONNECT';
--查询指定角色包含的对象权限(dba)
select * from dba_tab_privs where grantee = 'CONNECT';

/*----------------------------------------PL-SQL----------------------------------------*/

SQL:结构化查询语言(数据库行业的标准)
T-SQL:事务的结构化查询语言,是SQLServer对SQL的扩展
PL-SQL:过程语言和结构化查询语言,是Oracle对SQL的扩展:
1、使用变量,使用流程控制结构
2、使用过程、函数封装复杂的业务逻辑(变量、流程控制、SQL操作)
3、对Oracle基本的数据类型进行了扩展(属性类型)

数据类型:
e_name emp.ename%type; --列类型(引用表中指定列的类型)
e_emp_row emp%rowtype; --行类型(引用表中行的类型)
v_bool boolean;        --布尔类型 boolean(取值true,false,null,仅用于逻辑判断,不能直接输出)

异常处理:exception
1、others 能处理所有异常
2、select..into.. 如果未找到数据,则引发no_data_found异常
3、select..into.. 如果返回值过多,则引发too_many_rows异常
4、使用raise_application_error引发应用程序异常
1)raise_application_error(错误号,错误信息)
2)错误号在[-20999,-20000]之间
3)错误信息必须小于2048字节

语法:
[declare
  --任何类型的变量在未赋值之前,默认为null
  声明变量、类型、游标...;]
begin
  实现功能的代码...;
  --others可以捕获所有异常
  [exception when 异常类型 
  then 异常处理;]
end;

--查询员工KING的编号和薪水(匿名块)
declare
  v_empno number;
  v_sal number(10,2);
begin
  select empno,sal into v_empno,v_sal from emp where ename = 'KING';
  dbms_output.put_line('EMPNO:' || v_empno || ' SAL:' || v_sal);
end;

--loop、while、for循环
declare
  v_num number(2) := 1;
begin
  loop
    dbms_output.put_line(v_num);
	  v_num := v_num + 1;
    exit when v_num > 10;
	  --if v_num > 10 then exit; end if;
  end loop;
end;

declare
  v_num number(10) := 1;
begin
  while v_num 1=1 loop
    dbms_output.put_line(v_num);
    v_num := v_num + 1;
  end loop;
end;

declare
begin
  --in后加reverse倒序,i值不可更改
  for i in 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;

/*----------------------------------------游标使用----------------------------------------*/

游标:指向查询结果集的指针(对查询结果集的引用)
一、隐式游标(例:SQL%rowcount)
1、Oracle自动为增删改操作创建隐式游标,名称统一为SQL
2、用于获取最近的增删改操作对数据库的影响(在提交或回滚之前)
3、游标属性
1)%rowcount 返回受影响的行数
2)%found 如果影响了数据,则返回true否则返回false
3)%notfound 如果没影响数据,则返回true否则返回false
4)%isopen 如果游标打开,则返回true否则返回false(隐式游标始终返回false)

二、显式游标(例:cursor my_cursor is select * from emp)
1、显示游标必须在声明部分显式声明 
2、用于处理返回多行的查询结果集,便于用户逐行处理数据
3、游标属性
1)%rowcount 返回游标所在的行号
2)%found 如果找到了数据(fetch成功),则返回true否则返回false
3)%notfound 如果没找到数据(fetch失败),则返回true否则返回false
4)%isopen 如果游标打开,则返回true否则返回false

三、for循环游标:简化游标的操作(自动打开、提取数据、关闭)
declare
  cursor mycursor
  is
  select * from emp;
begin
	--'r'表示游标中结果集对应的行类型变量
  for r in mycursor
  loop
    if(r.sal>=3000) then
      dbms_output.put_line(mycursor%rowcount||'-'||r.ename||'-'||r.sal);
    end if;
  end loop;
end;

四、带参数的游标:提高游标的灵活性
--eg 根据部门编号查询员工信息
declare
  --声明参数,类型不能指定长度
  cursor mycursor(d_no number) 
  is
  select * from emp where deptno=d_no;
begin
  for r in mycursor('&d_no') --传参
  loop
    dbms_output.put_line(r.ename);
  end loop;
end;

五、使用游标更新数据
1)使用select..for update nowait;给游标加锁
2)使用where current of 游标名;限定更新游标所在行
--eg 给员工加薪(10+100,20+200,30+300)
declare 
  money number;
  cursor mycursor
  is
  select * from emp for update nowait;--给游标加锁
begin
  for r in mycursor
  loop
    if(r.deptno=10) then
      money:=100;
    elsif(r.deptno=20) then
      money:=200;
    elsif(r.deptno=30) then
      money:=300;
    end if;
    --where current of 游标名; 限定更新游标所在行
    update emp set sal=sal+money where current of mycursor;
  end loop;
end;

六、REF游标(动态游标)
1、用于处理运行时才能确定的查询结果集
2、REF游标必须在声明部分:
1)声明REF游标类型,type 类型名 is ref cursor;
2)声明REF游标变量,变量名 类型名;
3) 动态游标不能带参数?
--查询每个员工的姓名和薪水
declare
  type my_refcursor_type is ref cursor;
  my_refcursor my_refcursor_type;
  emp_row emp%rowtype;
begin
  open my_refcursor for select * from emp;
  loop
    fetch my_refcursor into emp_row;
    exit when my_refcursor%notfound;
    dbms_output.put_line(emp_row.ename || ':' ||emp_row.sal);
  end loop;
  close my_refcursor;
end;

/*----------------------------------------存储过程、函数、复合类型(record,table)、包----------------------------------------*/

一、分类
1)过程 procedure,完成特定功能
2)函数 function,完成特定功能并返回1个结果

2、优点
1)模块化,按业务功能进行封装
2)重用性好,易于维护
3)执行效率高,减少网络流量的占用
4)安全性高(涉及权限管理)

二、组成
1)声明部分(必须的)        create [or replace]...
2)可执行部分(必须的)      begin...end;
3)异常处理部分(可选的)    exception...

三、参数模式
1)输入参数 in
   接收用户输入,不允许在过程中修改,如果未指定参数模式默认为输入参数
2)输出参数 out
   向用户返回结果,必须声明变量传参
3)输入输出参数 in out
   既接收用户输入,又向用户返回结果

四、创建语法
过程:
create [or replace]
procedure 过程名[(参数列表)]
as | is
  [变量列表;]
begin
  可执行代码;
  [exception
   when others then null;]
end;
函数:
create [or replace]
function 函数名[(参数列表)]
return 返回类型
as | is
  [变量列表;]
begin
  可执行代码;
  return 返回值;
  [exception
   when others then return null;]
end;

五、删除子程序
drop procedure 过程名;
drop function 函数名;

--根据员工姓名查询工资(过程)
create or replace procedure myproc1(e_ename in varchar2,e_sal out number)
is
  v_sal emp.sal%type;
begin
  select sal into e_sal from emp where ename = e_ename;
end;

declare
  sal number(20);
begin
  myproc1('KING',sal);
  dbms_output.put_line('SAL:' || sal);
end;

--根据员工姓名查询编号(函数)
create or replace function myfun1(e_ename varchar2) return number
is
  v_empno emp.empno%type;
begin
  select empno into v_empno from emp where ename = e_ename;
  return v_empno;
end;

declare
  empno emp.empno%type;
begin
  empno := myfun1('KING');
  dbms_output.put_line('EMPNO:' || empno);
end;

六、复合类型
记录(record):
--根据编号查询员工姓名和薪水(复合类型——记录)
create or replace procedure myproc2(e_empno number)
is
  type record_type is record(v_ename emp.ename%type,v_sal emp.sal%type);
  ename_sal_recode record_type;
begin
  select ename,sal into ename_sal_recode from emp where empno = e_empno;
  dbms_output.put_line('ENAME,SAL:' || ename_sal_recode.v_ename || ' ' || ename_sal_recode.v_sal);
end;
表(table):
--根据员工姓名查询该员工上级(复合类型——表)
create or replace procedure myproc3(e_ename varchar2)
is
  type emp_table_type is table of emp.ename%type index by binary_integer;
  emp_table emp_table_type;
begin
  select e2.ename into emp_table(0) from emp e1,emp e2 where e2.empno = e1.mgr and e1.ename = upper(e_ename);
  dbms_output.put_line('MGR:' || emp_table(0));
  exception  when no_data_found then 
    dbms_output.put_line('该员工不存在或没有上级!');
end;

--取出emp表所有员工姓名(复合类型——表)
create or replace procedure myproc4
as
  type emp_table_type is table of emp.ename%type index by binary_integer;
  emp_table emp_table_type;
  num number;
begin
  select count(*) into num from emp;
  dbms_output.put_line('EMP TABLE ALL ENAME:');
  for i in 1..num loop
     select e2.ename into emp_table(i) from (select rownum rn,e1.* from (select * from emp order by empno) e1) e2 where e2.rn = i;
     dbms_output.put_line(emp_table(i));
  end loop;
end;

七、程序包:用于封装子程序、游标、变量等对象
1、组成
1)包规范 package
   用于声明公共成员和子程序规范(定义接口)
2)包主体 package body
   用于声明私有成员和实现子程序(定义类实现接口)

--包规范
create or replace package mypack1
is
  procedure test1;
  procedure test2;
end;

--包主体
create or replace package body mypack1 
is
  --test1
  procedure test1
  is
  begin
    dbms_output.put_line('my is test1');
  end;
  --test2
  procedure test2
  is
  begin
    test1();
    dbms_output.put_line('my is test2');
  end;
end;

begin
  mypack1.test2();
end;

/*----------------------------------------动态sql----------------------------------------*/

注:execute immediate sql执行结果只能取出单行单列或多列,如多行必须使用游标

--根据员工姓名查询薪水
declare
  type my_record_type is record(e varchar2(20),s number);
  strSql varchar2(100);
  my_record my_record_type;
begin
  strSql := 'select ename,sal from emp where ename = ''KING''';
  execute immediate strSql into my_record;
  dbms_output.put_line(my_record.e || ' ' || my_record.s);
end;

/*----------------------------------------分页存储过程----------------------------------------*/

--定义包
create or replace package pagePack as
  type pageCursorType is ref cursor; --游标类型
  procedure pageProc(
	    tableName varchar2,				--表名
			showField varchar2, 			--查询字段
			whereText varchar2, 			--查询条件
			orderText varchar2, 			--排序字段[asc] || [desc]
			pageIndex number,   			--当前页码
			pageSize number,    			--每页显示几条
			counter out number,     			--总记录数
			pageCount out number,   			--总页数
			pageCursor out pageCursorType --结果集
  );
end pagePack;

--实现包体
create or replace package body pagePack as
  procedure pageProc(
	    tableName varchar2,				--表名
			showField varchar2, 			--查询字段
			whereText varchar2, 			--查询条件
			orderText varchar2, 			--排序字段[asc] || [desc]
			pageIndex number,   			--当前页码
			pageSize number,    			--每页显示几条
			counter out number,     			--总记录数
			pageCount out number,   			--总页数
			pageCursor out pageCursorType --结果集
  )
  as
    strSql varchar2(500);
		wText varchar2(100);
		oText varchar2(100);
  begin
		--判断是否有where条件
		if whereText is not null then
			wText := ' where ' || whereText;
		end if;
		--判断是否有order by排序
		if orderText is not null then
			oText := ' order by ' || orderText;
		end if;
		--拼接分页sql语句
		strSql := 'select * from (select rownum rn,' || showField || ' from (select * from ' || tableName || wText || oText ||' ) t where rownum <= :1) where rn > :2';
		dbms_output.put_line(strSql);
		--打开游标并取值(不用关闭)
		open pageCursor for strSql using pageIndex * pageSize,(pageIndex - 1)* pageSize;
		--拼接记录数sql语句
		strSql := 'select count(1) from ' || tableName || wText || oText;
		dbms_output.put_line(strSql);
		--执行动态sql获取记录数
		execute immediate strSql into counter;
    --计算总页数
		pageCount := floor((counter + pageSize - 1) / pageSize);
  end;
end pagePack;

--创建视图(rowtype需要rownun列,因此需要创建视图,视图中其他列需要制定别名否则报错)
create or replace view viewEmp
as
select rownum rn,emp.* from emp;

--调用分页存储过程
declare
	counter number;
  pageCount number;
	pageCursor pagePack.pageCursorType;
  emp viewEmp%rowtype;
begin
  pagePack.pageProc('emp','t.*','','',1,5,counter,pageCount,pageCursor);
	dbms_output.put('COUNT:' || counter || ' PAGE_COUNT:' || pageCount);
	dbms_output.new_line();
  --这里使用for循环遍历游标会报异常,因为调用过程返回的游标已是打开的
  loop
    fetch pageCursor into emp; --类似java中ResultSet中rs.next();
    exit when pageCursor%notfound;
    dbms_output.put_line(emp.ename);
  end loop;
  close pageCursor;
end;

/*----------------------------------------触发器----------------------------------------*/

语法:
create [or replace] trigger 触发器名称
after | before | instead of 
[insert] [[or] update [of 字段列表]] 
[[or] delete]    
on 表名 | 视图名 --触发器语句(事件)
[referencing {OLD [as] old / NEW [as] new}]  --指定old表和new表的别名
[for each row]   --行级触发器,每行都触发 
[when (条件)]    --触发器条件
begin
    触发器操作;
end;

--限制只有scott才能对emp进行DML操作
create or replace trigger trig1
before
insert or update or delete
on emp
begin
  if user <> 'SCOTT' then
    raise_application_error(-20000,'只有SCOTT才能对此表进行操作!');
  end if;
end;

--记录对scott用户下emp表操作(用户、操作时间、操作类型)
--扩展:如果是update操作记录该用户操作了哪些列?
create or replace trigger trig2
after
insert or update or delete
on emp
declare
  action varchar2(10);
begin
  if inserting then
    action := 'insert';
  elsif deleting then
    action := 'update';
  elsif updating then
    action := 'update';
  end if;
  --system用户有一张scott_emp_log表并赋予public
  insert into system.scott_emp_log values(user,sysdate,action,orcl_seq.nextval);
end;

--使用触发器对scott_emp_log表自动生成序号(插入数据id列写0或不指定id列)
create or replace trigger trig3
before insert
on system.scott_emp_log
for each row
begin
  select orcl_seq.nextval into :new.id from dual;
end;

--创建触发器限制不允许修改员工奖金
create or replace trigger trig4
before update of comm
on emp
for each row
begin
  if :new.comm <> :old.comm then
    raise_application_error(-20000,'不允许修改comm字段!');
  end if;
end;

示例6:使用触发器更新视图
--创建视图
create or replace view view_test
as
select empno,empno||ename newname,sal from emp;

--创建触发器
create or replace
trigger eg_trigger6
instead of update on view_test
begin
  --将修改视图的操作替换成修改基表的操作
  update emp set ename=substr(:new.newname,5) where empno=:new.empno;
end;

update view_test set newname='7369LISHU' where empno=7369;

示例7:记录删除的对象
--创建日志表
create table droped_objects
(
  object_name varchar2(30),
  object_type varchar2(30),
  drop_date date
);

--创建触发器
create or replace
trigger eg_trigger7
after drop 
on scott.schema --谁的操作
begin
  insert into droped_objects values
  (ora_dict_obj_name,ora_dict_obj_type,sysdate);
end;

create table T(tid number);
create sequence sq;
drop table T;
drop sequence sq;

--禁用触发器
alter trigger 触发器名称 disable;

--启用触发器
alter trigger 触发器名称 enable;

--查看触发器的内容
select line,text from user_source where name=upper('触发器名称');

/*----------------------------------------Oracle SQL:经典查询练手第一篇----------------------------------------*/
--1.	列出至少有一个员工的所有部门。
select dname from dept where deptno in(select distinct deptno from emp);
select distinct d.dname from dept d,emp e where d.deptno = e.deptno;
select distinct d.dname from dept d inner join emp e on d.deptno = e.deptno;
--2.	列出薪金比“SMITH”多的所有员工。
select * from emp where sal > (select sal from emp where ename = 'SMITH');
--3.	列出所有员工的姓名及其直接上级的姓名。
select ename,(select ename from emp b where b.empno = a.mgr) from emp a;
select e1.ename,e2.ename from emp e1,emp e2 where e2.empno = e1.mgr
--4.	列出受雇日期早于其直接上级的所有员工。
select * from emp a where a.hiredate < (select hiredate from emp b where b.empno = a.mgr);
--5.	列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
select d.dname,e.* from  dept d left join emp e on d.deptno = e.deptno;
--6.	列出所有“CLERK”(办事员)的姓名及其部门名称。
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno and e.job = 'CLERK';
--7.	列出最低薪金大于1500的各种工作。
select job,min(sal) from emp group by job having min(sal) > 1500;
--8.	列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。
select ename from emp where deptno = (select deptno from dept where dname = 'SALES');
select e.ename from emp e,dept d where e.deptno = d.deptno and d.dname = 'SALES';
--9.	列出薪金高于公司平均薪金的所有员工。
select ename from emp where sal > (select avg(sal) from emp);
--10.	列出与“SCOTT”从事相同工作的所有员工。
select ename from emp where job = (select job from emp where ename = 'SMITH');
--11.	列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select ename,sal,deptno from emp where sal in(select sal from emp where deptno = 30) and deptno <> 30;
--12.	列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select ename,sal from emp where sal > (select sum(sal) from emp where deptno = 30);
--13.	列出在每个部门工作的员工数量、平均工资和平均服务期限。
select (select dname from dept where deptno = emp.deptno) dname,count(*),round(avg(sal),2),round(avg(sysdate-hiredate)) from emp group by deptno;
--14.	列出所有员工的姓名、部门名称和工资。
select ename,(select dname from dept where deptno = emp.deptno) dname,sal from emp;
--15.	列出所有部门的详细信息和部门人数。
select deptno,dname,(select count(*) from emp where deptno = dept.deptno group by deptno) dnum from dept;
--16.	列出各种工作的最低工资。
select job,min(sal) from emp group by job;
--17.	列出各个部门的MANAGER(经理)的最低薪金。
select (select dname from dept where deptno = emp.deptno) dname,min(sal) from emp where job = 'MANAGER' group by deptno;
--18.	列出所有员工的年工资,按年薪从低到高排序。
select ename,(sal*12) yearsal from emp order by sal asc;

/*----------------------------------------Oracle SQL:经典查询练手第二篇----------------------------------------*/

--1.	找出EMP表中的姓名(ENAME)第三个字母是A 的员工姓名。
select ename from emp where ename like '__A%';
--2.	找出EMP表员工名字中含有A 和N的员工姓名。
select ename from emp where ename like('%A%') and ename like('%N%');
--3.	找出所有有佣金的员工,列出姓名、工资、佣金,显示结果按工资从小到大,佣金从大到小。
select ename,sal,comm from emp where comm is not null order by sal,comm desc;
4.	列出部门编号为20的所有职位。
select job from emp where deptno = 20;
--5.	列出不属于SALES 的员工。
select ename from emp where deptno in(select deptno from dept where dname <> 'SALES');
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno and d.dname <> 'SALES';
6.	显示工资不在1000 到1500 之间的员工信息:名字、工资,按工资从大到小排序。
select ename,sal from emp where sal not between 1000 and 1500 order by sal desc;
select ename,sal from emp where sal < 1000 or sal > 1500 order by sal desc;
7.	显示职位为MANAGER 和SALESMAN,年薪在15000 和20000 之间的员工的信息:名字、职位、年薪。
select ename,job,(sal*12) yearsal from emp where job in('MANAGER','SALESMAN') and (sal*12) between 15000 and 20000;
8.	说明以下两条SQL语句的输出结果:
    SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL; --有结果
    SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;  --无结果
9.	让SELECT 语句的输出结果为
1.	SELECT * FROM SALGRADE;  
2.	SELECT * FROM BONUS;  
3.	SELECT * FROM EMP;  
4.	SELECT * FROM DEPT;  
5.	……
列出当前用户有多少张数据表,结果集中存在多少条记录。 
select 'SELECT * FROM ' || tab.tname || ';' from tab;  
select * from tab;
10.	判断SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否报错,为什么?
--不会报错,Oracle会检测是否可转为数字格式,如果可以将隐士转换,不可则报错。

/*----------------------------------------Oracle SQL:经典查询练手第三篇----------------------------------------*/

/*Oracle分页查询*/
--三层嵌套分页
select e2.* from (select e1.*,rownum rn from (select * from emp order by empno) e1 where rownum <= 5) e2 where e2.rn >0;

--减集分页(不能排序?)
select * from emp where rownum<=5 minus select * from emp where rownum<=0;

--多表连接分页查询(第一层需要指定显示列,列名不能用重复)
select b.* from 
  (select rownum rn,a.* from (select e.*,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno order by e.empno) a 
  where rownum <=5) b where b.rn >=1;

/*查询高于自己部门平均工资的员工信息*/
select e2.empno,e2.ename,e2.sal,e2.deptno,e1.deptavgsal 
from emp e2,(select deptno,avg(sal) deptavgsal from emp group by deptno) e1 
where e2.deptno = e1.deptno and e2.sal > e1.deptavgsal;

Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<