Oracle复习笔记
/*----------------------------------------常用命令(sqlplus环境)----------------------------------------*/
conn scott/tiger@orcl [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)唯一索引，Oracle自动为主键、唯一键创建唯一索引
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('hello@sina.com',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;
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。