/*----------------------------------------常用命令(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;