Oracle笔记整理

0.oracle卸载

windows系统下彻底清除oracle

1.删除oracle注册表信息.运行regedit,删除注册表项 HKEY_LOCAL_MACHINE\\SOFTWARE\\Oracle 2.删除oracle服务.oracle服务在注册表中的位置是: HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Service 删除所有以oracle字符打头的服务.

3.删除事件日志.注册表中的位置是

HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentContrilSet\\Services\\Eventlog\\Application 删除所有以oracle字符打头的键.

4.删除ORACLE环境变量,如\变量和Path变量中的oracle路径 5.删除oracle菜单.

6.删除\目录 7.重启WINDOWS. 8.删除oracle主目录.

1常见命令

Oracle的内置用户

超级用户:sys-->on_change_install,系统管理员sysdba,系统操作员sysoper 系统管理员:system-->manager,系统管理员sysdba,系统操作员sysoper sys和system的区别：能否创建数据库 conn system/manager as sysdba 示例用户:scott-->tigger

注意:scott安装完成后可能处于冻结锁定状态 为账号解锁(必须拥有解冻的权限) alter user 用户名 account unlock; 为账号更改密码

alter user 用户名 identified by 密码;

1.打开sqlplus

开始-->运行-->cmd -->sqlplus 用户名/密码 [as 身份] 身份：sysdba,sysoper,normal

eg: sqlplus system/xasxt as sysdba

2.conn[ect] 用户名/密码 [as sysdba]：连接数据库 3.disconn[ect] :断开连接 4.clear scr[een] 清屏

5.show user 显示当前登录用户

6. select * from tab;查看当前用户的表

7.desc 表名; 查看表结构

8.创建账号: create user 用户名 identified by 密码 9.为账号分配权限获取角色

系统权限允许用户执行某些数据库操作，如创建表就是一个系统权限 grant 权限/角色 to 用户名

grant create session,create table,create seqence to xiaoming;--为用户分配权限 grant connect to xiaoming;--为用户分配角色 grant resource to xiaoming;

10.移除权限

revoke create session, create table from xiaoming;

对象权限允许用户对数据库对象（如表、视图、序列等）执行特定操作 grant select,update,insert,delete on scott.emp to xiaoming; revoke select on scott.emp from xiaoming; 11. drop user 用户名 [cascade] 12.ed:编辑 13.spool d:/xxx.txt xxxxx

spool off

14.执行文件的中sql语句 \文件路径\eg: @d:1.txt

isqlplus:http://localhost:5500/em

2表管理基本的SQL语句

1.创建Person2表,并将person表中的数据插入到Person2中: --create table person2 as select * from person; 在已存在的Person2表中复制Person表中的数据: --insert into person2 select * from person;

新增:

insert into person(pid,pname,page,birthday) values(1,'aa',23,'1-1月-1988') 修改

update person set pname='bb' where pid=1; 删除:

delete from person where pid=1

2.创建表

create table person (

pid number primary key, pname varchar2(20) not null, page number(3), birthday date )

/

3.添加约束

主键约束:primary key

唯一约束:unique 非空约束:not null, Check约束:check 外键约束:foreign key 默认值:default create table person3 (

pid number primary key, pname varchar2(20) unique,

page number(3) check(page between 1 and 150),//page number(3) check(page>=1 and page<=150),

birthday date not null )

create table person5 (

pid number ,

pname varchar2(20) , page number(3),

birthday date,

address varchar2(20) default '不详'

)

alter table person4 add constraint pk_pid primary key (pid);

alter table person4 add constraint ck_page check (pid between 1 and 150);

create table classinfo(

classId number(4) primary key, className varchar2(10) not null )

create table stuinfo(

stuId number(4) primary key, stuName varchar2(10) not null,

classId number(4) --references classinfo(classId) )

alter table stuinfo add constraint fk_xx foreign key (classId) references classinfo(classId);

4.为表添加列

alter table person4 add address varchar2(50); 5.更改列的大小

alter table person4 modify pname varchar2(30); 6.查看约束

select constraint_name,table_name from user_constraints;

7.删除约束

alter table person4 drop constaint ck_pid;

8.表空间 作用:

1.可以使用表空间限制数据库文件的大小

2.利用表空间将数据文件存放到不同的磁盘上,提高IO性能，利于数据的备份和恢复

-表空间 --段(Segment) ---区(Extent) ----块(Block)

创建表空间:

CREATE TABLESPACE tablespacename DATAFILE ‘filename’ [SIZE integer [K|M]] [AUTOEXTEND [OFF|ON]];

create tablespace myspace datafile

'e:/my01.dbf' size 5 M, 'd:/my02.dbf' size 10 M

更改表空间

1.为表空间添加数据文件

alter tablespace myspace add datafile 'e:/cc.dbf' size 5m; 2.更改数据文件的大小

alter database datafile 'e:/bb.dbf' resize 5m;

3.设置数据文件自动增长(每次自动增长2M,最大为10M)

alter database datafile 'e:/cc.dbf' autoextend on next 2M maxsize 10M;

9.创建表时,为表指定表空间 create table person4 (

pid number ,

pname varchar2(20) , page number(3),

birthday date,

address varchar2(20) default '不详' )tablespace myspace;

grant create tablespace,alter tablespace to xiaoming;

10.查询表空间

select tablespace_name from user_tablespaces;

11.查询sp01表空间下的表

select table_name from all_tables where tablespace_name='sp01';

3课堂练习，基本查询

scott/tigger

--1 查询当前用户下的所有表 select * from tab;

--2 查询雇员表中所有信息 select * from emp;

--3 查询雇员编号,姓名,工作,工资 select empno,ename,job,sal from emp

--4 查询雇员编号,姓名,工作,工资,并显示中文(为列起别名)

select empno as 编号,ename as 姓名,job as 工作,sal as 工资 from emp --5 消除重复列,查询雇员工作种类 select distinct job from emp

--6 字符串连接操作(||)

--查询雇员编号,姓名,工作.按以下格工显示:编号:7369,姓名:Smith,工作:Clerk select '编号:'||empno,'姓名:'||ename,'工作:'||job from emp --7 查询列支持四则运算(年薪=(工资+奖金)*12) --查询雇员编号,姓名,工作,年薪

select empno,ename,job,(sal+nvl(comm,0))*12 from emp nvl(comm,0)==>如果comm值为空，取值0

--8 Where条件查询

-- 查询工资大于1500的所有雇员 select * from emp where sal>1500 --查询可以得到奖金的所有雇员

select * from emp where comm is not null

--查询工资大于1500或可以得到奖金的雇员 select * from emp where sal>1500 or comm is not null --查询工资大于1500并且可以领取奖金的雇员

select * from emp where sal>1500 and comm is not null --查询工资不大于1500或者不可以领取奖金的雇员 select * from emp where sal<=1500 or comm is null --查询工资在1500到3000的所有雇员信息 select * from emp where sal>=1500 and sal<=3000 select * from emp where sal between 1500 and 3000 --查询在1981年雇用的员工信息

select * from emp where hiredate like '?%' --查询雇员姓名中第二个字母为\的雇员 select * from emp where ename like '_M%'

--查询雇员工资中带8这个数字的

select * from emp where sal like '%8%'

--查询编号是7369,7499,7521,7799的雇员信息

select * from emp where empno=7369 or empno=7499 or empno=7521 or empno=7799 select * from emp where empno in(7369,7499,7521,7799) --查询雇员编号不是7369,7499,7521,7799的所有雇员信息 select * from emp where empno not in(7369,7499,7521,7799) --查询雇员编号为7369的雇员信息 select * from emp where empno =7369 --查询雇员编号不为7369的雇员信息 select * from emp where empno !=7369 select * from emp where empno <>7369

--查询雇员信息,按工资由低到高排序 select * from emp order by sal asc

--查询雇员信息,按工资由高到低排序 select * from emp order by sal desc

操作集合:

union:将两个记录合并,去掉重复项

select distinct deptno from emp union select deptno from dept; union:将两个记录合并,不去掉重复项

select distinct deptno from emp union all select deptno from dept; intersect:取两个集合的交集

select distinct deptno from emp intersect select deptno from dept; minus:去掉交集(集合A-(集合A和集合B的交集))

select deptno from dept minus select distinct deptno from emp ;

4高级查询

转换函数: to_char

select pname,to_char(birthday,'yyyy-mm-dd') from person; select to_char(sal,'L99,999') from emp;-->L本地伙伴符号 select to_char(sal,'$99,999') from emp;-->$:美元 select to_char(sal,'$000,000') from emp;

to_date

insert into person(pid,pname,page,birthday)values(3,'cc',21,to_date('1991-2-2','yyyy-mm-dd'));

连接查询

内连接:

select e.empno,e.ename,e.sal,d.dname from emp e, dept d where e.deptno=d.deptno;

select e.empno,e.ename,e.sal,d.dname from emp e join dept d on(e.deptno=d.deptno) 左连接

select e.empno,e.ename,e.sal,d.dname from emp e left join dept d on(e.deptno=d.deptno)

select e.empno,e.ename,e.sal,d.dname from emp e ,dept d where e.deptno=d.deptno(+);

右连接:

select e.empno,e.ename,e.sal,d.dname from emp e right join dept d on(e.deptno=d.deptno)

select e.empno,e.ename,e.sal,d.dname from emp e ,dept d where e.deptno(+)=d.deptno; 交叉连接:笛卡尔乘积 select * from emp,dept

select e.empno,e.ename,e.sal,d.dname from emp e cross join dept d on(e.deptno=d.deptno)

全连接:

select e.empno,e.ename,e.sal,d.dname from emp e full join dept d on(e.deptno=d.deptno)

子查询:

--查询部门编号为10的员工中工资最高的员工的编号,姓名,工资

select empno,ename,sal from emp where deptno=10 and sal=(select max(sal) from emp where deptno=10) 聚合函数:

count,sum,avg,max,min 分组:

group by

--查询平均工资>2000,的部门编号,部门名称，平均工资 --平均工资>2000的部门编号

select deptno from emp group by deptno having avg(sal)>2000 --查询平均工资>2000,的部门编号,部门名称

select deptno,dname from dept where deptno in(select deptno from emp group by deptno having avg(sal)>2000)

--查询平均工资>2000,的部门编号,部门名称，平均工资

select d.deptno,d.dname,avg(e.sal) from emp e,dept d where e.deptno=d.deptno group by d.deptno,d.dname having avg(e.sal)>2000

Oracle分页:

mysql: select * from emp limit 0,3

sqlserver: select top 5* from emp where empno not in(select top 0 empno from emp); 总记录数:14 分页单位:5

总页数:总记录数%分页单位==0?总记录数/分页单位:总记录数/分页单位+1

第一页: 前5条,使用rownum(行号)<6

select A.*,rownum from(select * from emp) A where rownum <6;

select * from (select A.*,rownum rn from(select * from emp) A where rownum <6) where rn>0;

第二页:rownum>5,rownum<11

select A.*,rownum from(select * from emp) A where rownum >5 and rownum<11; X

select * from (select A.*,rownum rn from(select * from emp) A where rownum <11) where rn>5; 第三页:

select * from (select A.*,rownum rn from(select * from emp) A where rownum <16) where rn>10;

开始位置=(当前页-1)*分页单位 结束位置=当前页*分页单位+1

select * from (select A.*,rownum rn from(select * from emp) A where rownum <结束位置) where rn>开始位置;

注意:Oracle中为列起别名可以加as,但为表起别名不能加as

/******************使用jdbc连接oracle********************* driverclass:oracle.jdbc.driver.OracleDriver url:jdbc:oracle:thin:@localhost:1521:orcl

//1.加载驱动

Class.forName(\

//2.获取连接 url=jdbc:oracle:thin:@IP地址:端口:数据库名称

Connection conn = DriverManager.getConnection(\\

//3.创建Statement或者PrepareStatement Statement stmt = conn.createStatement();

//4.执行增,删,改,(executeUpdate),查(executeQuery) ResultSet rs = stmt.executeQuery(\//5.遍历结果集 while(rs.next()){

int empno= rs.getInt(\

String ename= rs.getString(\System.out.println(empno+\

}

//6.释放资源 rs.close(); stmt.close(); conn.close();

5锁和表分区

Oracle中锁的优点:解决并发访问的问题 1.一致性 2.完整性

分类:行级锁和表级锁

行级锁是一种排他锁，防止其他事务修改此行 在使用以下语句时，Oracle会自动应用行级锁： INSERT UPDATE DELETE

SELECT … FOR UPDATE[wait second][no wait]

SELECT ? FOR UPDATE语句允许用户一次锁定多条记录进行更新 使用COMMIT或ROLLBACK语句释放锁 eg:

用户A:

select * from emp where empno=7934 for update[wait 5][nowait] 用户B:

update scott.emp set sal=sal+10 where empno=7934 不能更改 update scott.emp set sal=sal+10 where empno=7902 可以更改

表级锁:锁定整个表，限制其他用户对表的访问。 LOCK TABLE table_name IN mode MODE eg:

用户A:lock table emp in share mode; 用户B:

select * from scott.emp 可以

update scott.emp set sal=sal+10 where empno=7902 不可以

死锁:当两个事务相互等待对方释放资源时，就会形成死锁

Oracle会自动检测死锁，并通过结束其中的一个事务来解决死锁 eg:

用户A:lock table emp in share mode;

update emp set sal=sal+10 where empno=7902 用户B:lock table emp in share mode;

update scott.emp set sal=sal+10 where empno=7902

表分区的作用：

1.允许用户将一个表分成多个分区

2.用户可以执行查询，只访问表中的特定分区

3.将不同的分区存储在不同的磁盘，提高访问性能和安全性 4.可以独立地备份和恢复每个分区 表分区的类型 1.范围分区

以表中的一个列或一组列的值的范围分区

create table sale( sid number(4), sdate date,

scost number(10,2) )

partition by range(scost) (

partition p1 values less than (1000), partition p2 values less than (2000), partition p3 values less than (3000), partition p4 values less than (maxvalue) )

2.散列分区

允许用户对不具有逻辑范围的数据进行分区 通过在分区键上执行HASH函数决定存储的分区 将数据平均地分布到不同的分区

按empname的hash码划分3个分区:d1,d2,d3 create table employee( empId number, empname varchar(20), deptname varchar(20) )

partition by hash(empname) (

partition d1, partition d2, partition d3

)

按empname的hash码划分4个分区 create table employee2( empId number,

empname varchar(20), deptname varchar(20) )

partition by hash(empname) partitions 4 3.列表分区

允许用户将不相关的数据组织在一起 create table employee3( empId number,

empname varchar(20), empaddress varchar(200) )

partition by list(empaddress)

(

partition E values('山东'),

partition W values('新疆','西藏') )

4.复合分区

范围分区与散列分区或列表分区的组合

PARTITION BY RANGE (column_name1) --范围分区 SUBPARTITION BY HASH (column_name2)--散列分区

SUBPARTITIONS number_of_partitions --散列分区的数量 (

PARTITION part1 VALUES LESS THAN(range1), PARTITION part2 VALUES LESS THAN(range2), ...

PARTITION partN VALUES LESS THAN(MAXVALUE) ); eg:

先按照薪资进行范围分区(3个分区)

再按照编号进行散列分区(创建4个分区)

create table employee4( empId number,

empname varchar(20), empaddress varchar(200), empsal number(7) )

partition by range(empsal) subpartition by hash(empId) subpartitions 4 (

partition s1 values less than (2000), partition s2 values less than (4000), partition s3 values less than (maxvalue) ) /

操纵已分区的表

在已分区的表中插入数据与操作普通表完全相同，Oracle会自动将数据保存到对应的分区 查询、修改和删除分区表时可以显式指定要操作的分区

insert into sale values(1,to_date('2010-9-1','yyyy-mm-dd'),1500);--将数据存放到sale表中p2分区上

--查询指定分区中的数据

select * from sale partition(p2)--p2为分区的名称 --删除指定分区的数据

delete from sale partition(p2)

6.序列和同义词

1.同义词是现有对象的一个别名。 同义词优点: a.简化SQL语句

b.隐藏对象的名称和所有者 c.提供对对象的公共访问

同义词的分类：私有同义词和公有同义词 创建私有同义词

create synonym 同义词名称 for 模式.表名 create synonym emp for scott.emp;

select * from emp;-->select * from scott.emp;

创建公有同义词:(需要为其授权:create public synonym) grant create public synonym to xiaoming;

create public synonym 同义词名称 for 模式.表名 create public synonym pub_emp for scott.emp; eg:

SQL> conn system/xasxt

Connected.

SQL> grant select on pub_emp to xiaohua; Grant succeeded.

SQL> conn xiaohua/xiaohua; Connected.

SQL> select * from pub_emp; 删除同义词:

drop synonym emp;

drop public synonym pub_emp;

2.序列:实现类似于sqlserver中的主键自增的功能 序列是用于生成唯一、连续序号的对象 序列可以是升序的，也可以是降序的 使用CREATE SEQUENCE语句创建序列 create sequence my_seq start with 1 increment by 1 maxvalue 100000 minvalue 1 nocycle cache 10 /

通过nextval获取下一个序列的值:select my_seq.nextval from dual 通过currval获取当前序列的值:select my_seq.currval from dual; 更改和删除序列:

更改: ALTER SEQUENCE toys_seq MAXVALUE 5000 CYCLE; 删除: DROP SEQUENCE toys_seq;

7.视图和索引

1.视图:

视图以经过定制的方式显示来自一个或多个表的数据 视图可以视为“虚拟表”或“存储的查询” 创建视图所依据的表称为“基表” 视图的优点有：

提供了另外一种级别的表安全性 隐藏的数据的复杂性

简化的用户的SQL命令 隔离基表结构的改变

通过重命名列，从另一个角度提供数据 eg:

create or replace view V$_myview--建议视图名称以V$开头 as

select e.empno,e.ename,d.dname from emp e join dept d on(e.deptno=d.deptno) /

select * from V$_myview--查询视图(将视图看成一张表) 注意:视图是可以更新的,但是需要满足一下条件 a.只能修改一个底层的基表

b.如果修改违反了基表的约束条件，则无法更新视图

c.如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或 GROUP BY 子句，则将无法更新视图

d.如果视图包含伪列或表达式，则将无法更新视图 删除视图: drop view V$_myview;

2.索引(字典中的索引目录) 优点:

1.用以提高 SQL 语句执行的性能 2.减少磁盘I/O访问次数 原则:

1.只有在大表上创建索引才有意义 2.在where子句中的条件(列)经常建立索引 3.建立索引会带来一些额外的开销:

a.写的速度会变慢:需要额外维护索引信息 b.加大磁盘和内存空间的占用量(1.2倍)

创建标准索引:

CREATE INDEX 索引名称 ON 表名 (列名) TABLESPACE 表空间

eg:

create index idx_myIndex on emp(ename);

create index idx_myIndex --组合索引

on emp(ename,sal);

select * from emp where ename='zhangsan' and sal=1000;

重建索引:

ALTER INDEX idx_myIndex REBUILD; 删除索引:

drop index idx_myIndex;

唯一索引:

Oracle自动在表的主键列上创建唯一索引 CREATE UNIQUE INDEX 索引名 ON 表名 (列名);

8.PL-SQL基础

--声明变量(建议变量名以v_开头) --变量名类型:=值; --赋值变量名:=值; declare

v_hello varchar2(20):='hello'; v_world varchar2(20);

v_cons constant number(4):=123;--声明常量:不能重新赋值 begin

v_world:=',xasxt!';

--v_cons:=345;

dbms_output.put_line(v_hello||v_world);--类似于System.out.println(); dbms_output.put_line('v_cons='||v_cons); end;

--使用select ..into语句为变量赋值 declare

v_name varchar2(20); v_sal number(10); v_empno number(10); begin

v_empno:='&empno';--获取控制台输入的编号:&(交互式命令) select ename,sal into v_name,v_sal from emp where empno=v_empno; dbms_output.put_line('姓名:'||v_name); dbms_output.put_line('薪水:'||v_sal);

end;

--%type:引用变量和数据库列的数据类型 --%rowtype:提供表示表中一行的记录类型 declare

v_name emp.ename%type;-- 与emp表中ename列的类型一致 v_sal emp.sal%type;

v_empno emp.empno%type;

v_record emp%rowtype;--保持一条记录 begin

v_empno:='&empno';--获取控制台输入的编号:&(交互式命令) select ename,sal into v_name,v_sal from emp where empno=v_empno; dbms_output.put_line('姓名:'||v_name);

dbms_output.put_line('薪水:'||v_sal);

dbms_output.put_line('**************************');

select * into v_record from emp where empno=v_empno;--将查询的记录赋值给v_record dbms_output.put_line('工作:'||v_record.job); end;

--条件结构 --if.... end if; declare

v_age number(3); begin

v_age:='&age'; if v_age >= 18 then

dbms_output.put_line('成年!'); end if; end;

--if....else....end if; declare

v_age number(3); begin

v_age:='&age'; if v_age >= 18 then

dbms_output.put_line('成年!'); else

dbms_output.put_line('未成年!'); end if; end;

---if...elsif...else....end if declare

v_age number(3); begin

v_age:='&age';

if v_age >= 50 then

dbms_output.put_line('老年!'); elsif v_age>=30 then

dbms_output.put_line('状年!'); elsif v_age>=18 then

dbms_output.put_line('青年!'); else

dbms_output.put_line('未成年!'); end if; end;

--case子句:类似于switch DECLARE

grade varchar2(2); BEGIN

grade:='&grade'; CASE grade

WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('优异'); WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE ('优秀'); WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE ('良好'); WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE ('一般'); WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE ('较差'); ELSE DBMS_OUTPUT.PUT_LINE ('没有此成绩'); END CASE; END;

--loop循环结构: do...while循环 --loop --循环体; -- exit when 条件 --end loop; declare

i binary_integer:=1; begin

loop

dbms_output.put_line('i='||i); i:=i+1;

exit when i>10; --满足条件推出循环 end loop; end;

--while循环结构:类似于while循环 --while 条件 --loop --循环体 --end loop;

declare

i binary_integer:=1; begin

while(i<=10) loop

dbms_output.put_line('i='||i); i:=i+1; end loop; end;

9.PL-SQL基础2

--动态SQL /*

动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句

编译程序对动态 SQL 不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行

DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过动态 SQL 来执行 执行动态 SQL 的语法：

EXECUTE IMMEDIATE dynamic_sql_string [INTO define_variable_list] [USING bind_argument_list]; */

--根据用户输入的员工编号,动态将该员工的编号和薪水插入到bound2表(动态创建)中 declare

v_emp_id number; v_sql varchar2(200); v_emp_rec emp%rowtype; begin

v_emp_id:='&empno';

--查询指定编号的员工信息

v_sql:='select * from emp where empno=:id';

execute immediate v_sql into v_emp_rec using v_emp_id;--将查询出的记录存放到v_emp_rec变量中

--动态创建bound2表

v_sql:='create table bound3(id number,sal number)'; execute immediate v_sql;--创建表

v_sql:='insert into bound3 values(:1,:2)';

execute immediate v_sql using v_emp_rec.empno,v_emp_rec.sal; end;

select * from bound3; --异常处理 declare

v_num1 number:=2;

v_num2 number:=0; v_result number; begin

v_result:= v_num1/v_num2; dbms_output.put_line(v_result);

exception when others then--Exception dbms_output.put_line('出错了.....'); end; declare

v_emp_id number; v_sal number; begin

select empno,sal into v_emp_id,v_sal from emp; dbms_output.put_line('v_emp_id='||v_emp_id); dbms_output.put_line('v_sal='||v_sal); exception when too_many_rows then dbms_output.put_line('error...'); end;

--自定义异常

declare

tel number(8);

invildNumber exception; begin

tel:='&tel';

if tel not in(12345678,87654321) then raise invildNumber; else

dbms_output.put_line(tel); end if;

exception when invildNumber then

--dbms_output.put_line('error...');

raise_application_error(-20001,'error....'); end;

10.游标

/*

游标:类似于jdbc中的ResultSet

逐行处理查询结果，以编程的方式访问数据 游标的类型:隐式游标,显示游标,Ref游标 隐式游标:

在PL/SQL中使用DML语句时自动创建隐式游标

隐式游标自动声明、打开和关闭，其名为 SQL

通过检查隐式游标的属性可以获得最近执行的DML 语句的信息 隐式游标的属性有：

%FOUND – SQL 语句影响了一行或多行时为 TRUE %NOTFOUND – SQL 语句没有影响任何行时为TRUE %ROWCOUNT – SQL 语句影响的行数 %ISOPEN - 游标是否打开，始终为FALSE */

declare

v_emp_id number; begin

v_emp_id:='&请输入编号';

update emp set sal=sal+10 where empno=v_emp_id; if SQL%FOUND then

dbms_output.put_line('受影响的行数'||SQL%ROWCOUNT); end if; end;

select * from emp; /*

显示游标

显式游标在 PL/SQL 块的声明部分定义查询，该查询可以返回多行 */

--使用loop循环遍历游标 declare

v_emp_rec emp%ROWTYPE;--用于保存一行数据 cursor my_cursor is select * from emp;--声明游标 begin

open my_cursor;--打开游标 loop

fetch my_cursor into v_emp_rec;--提取行 exit when my_cursor%NOTFOUND ;

dbms_output.put_line('姓名:'||v_emp_rec.ename); end loop;

close my_cursor;-- 关闭游标 end;

--使用while循环遍历游标

declare

v_emp_rec emp%ROWTYPE;--用于保存一行数据 cursor my_cursor is select * from emp;--声明游标 begin

open my_cursor;--打开游标 fetch my_cursor into v_emp_rec; while (my_cursor%FOUND)

loop

dbms_output.put_line('姓名:'||v_emp_rec.ename); fetch my_cursor into v_emp_rec;--提取行 --exit when my_cursor%NOTFOUND ; end loop;

close my_cursor;-- 关闭游标 end;

--使用for循环,不需要打开和关闭游标 declare

v_emp_rec emp%ROWTYPE;--用于保存一行数据 cursor my_cursor is select * from emp;--声明游标 begin

for v_emp_rec in my_cursor loop

dbms_output.put_line('姓名:'||v_emp_rec.ename); end loop; end;

11.游标2

--带参数的游标 declare

v_sal emp.sal%type;

v_emp_rec emp%rowtype;

cursor cur(salpara number) is select * from emp where sal < salpara; --声明带参数的游标 begin

v_sal:='&sal';

for v_emp_rec in cur(v_sal)

loop

dbms_output.put_line(v_emp_rec.ename||'--'||v_emp_rec.sal); end loop; end;

select * from emp where sal<1500

--使用游标更新(薪水<1500员工，加薪100) declare

v_sal number;

cursor cur is select sal from emp where sal < 1500 for update; begin

open cur; loop

fetch cur into v_sal;

exit when cur%NOTFOUND ;

dbms_output.put_line('sal:'||v_sal);

update emp set sal=v_sal+100 where current of cur;--使用当前的游标作为条件

end loop;

commit;--提交事务 end;

select * from emp where sal<1500 /* ???? declare

cursor cur is select sal from emp where sal < 1500 for update; begin

for v_sal in cur loop

dbms_output.put_line('sal:'||v_sal);

update emp set sal=v_sal+100 where current of cur;--使用当前的游标作为条件 end loop; commit;--提交事务 end;

*/ /*

ref游标:可执行动态SQL 1.声明游标类型 2.声明游标

3.操作游标 */

declare

type my_cur_type is ref cursor;--声明ref游标类型 my_cur my_cur_type;--声明游标(声明变量) v_sql varchar2(200); v_emp_rec emp%rowtype; begin

v_sql:='select * from emp'; open my_cur for v_sql;--打开游标 loop

fetch my_cur into v_emp_rec; exit when my_cur%NOTFOUND ;

dbms_output.put_line('ename:'||v_emp_rec.ename); end loop; close my_cur; end;

12.存储过程

/*

存储过程的优点:

模块化:将程序分解为逻辑模块

可重用性:可以被任意数目的程序调用 可维护性:简化维护操作

安全性:通过设置权限，使数据更安全 执行效率较高 语法:

CREATE [OR REPLACE] PROCEDURE [()] IS|AS --变量的声明部分 BEGIN

--业务 [EXCEPTION --异常处理 ] END; */

create or replace procedure pro1 is

v_hello varchar2(20):='hello,xasxt!'; begin

dbms_output.put_line(v_hello); end; /*

调用存储过程的方式:

1.execute 存储过程名称(sqlplus中) eg: execute pro1();

2.call 存储过程名称(sqlplus中) eg: call pro1();

3.使用程序块调用 begin

存储过程名称 end; */ begin pro1(); end;

--根据部门编号,为员工加薪(部门编号=10,加薪100,部门编号=20,加薪200,部门编号=30,加薪300)

create or replace procedure pro2 is

cursor c is select * from emp for update; begin

for v_emp_rec in c

loop

if(v_emp_rec.deptno=10) then

update emp set sal=sal+100 where current of c; elsif v_emp_rec.deptno=20 then

update emp set sal=sal+200 where current of c; elsif v_emp_rec.deptno=30 then

update emp set sal=sal+300 where current of c; end if; end loop; commit; end;

select * from emp begin pro2();

end;

--带参数的存储过程 /*

IN:用于接受调用程序的值 OUT:用于向调用程序返回值

IN OUT：用于接受调用程序的值，并向调用程序返回更新的值 */

create or replace procedure pro3(emp_no number) is

v_name varchar2(20); begin

select ename into v_name from emp where empno=emp_no; dbms_output.put_line('名称:'||v_name); exception when NO_DATA_FOUND then

dbms_output.put_line('没有找到雇员信息..'); end;

--调用带参的存储过程 begin

pro3('793422'); end;

--既有输入参数又有输出参数(接受返回值)

create or replace procedure pro4(emp_no in number,emp_sal out number) is

v_name varchar2(20); begin

select ename,sal into v_name,emp_sal from emp where empno=emp_no; dbms_output.put_line('名称:'||v_name); exception when NO_DATA_FOUND then

dbms_output.put_line('没有找到雇员信息..'); end;

-- 调用 declare

emp_no number:=7934;

emp_sal number;--接收返回值 begin

pro4(emp_no,emp_sal);

dbms_output.put_line('out:....'||emp_sal); end;

--根据分页单位计算出指定表的总页数

create or replace procedure pro5(tableName in varchar2,pageSize in number,totalPages out number)

is

v_totalRows number;--总记录数 v_sql varchar2(200); begin

--查询指定表的总记录数

v_sql:='select count(*) from '||tableName; --执行动态SQL

execute immediate v_sql into v_totalRows;

--总记录数%分页单位==0?总记录数/分页单位:总记录数/分页单位+1 if mod(v_totalRows,pageSize)=0 then totalPages:=v_totalRows/pageSize; else

totalPages:=floor(v_totalRows/pageSize)+1; end if; end;

--调用 declare

tableName varchar2(20):='&tableName'; pageSize number :='&pageSize'; totalPages number;

begin

pro5(tableName,pageSize,totalPages);

dbms_output.put_line('totalPages--->'||totalPages); end;

12.函数

/*

函数:类似于存储过程,都是oracle的子程序,函数有返回值,而存储过程没有返回值 函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数 CREATE [OR REPLACE] FUNCTION [(param1,param2)]

RETURN IS|AS [local declarations] BEGIN

Executable Statements; RETURN result; EXCEPTION

Exception handlers; END; */

create or replace function fun_hello return varchar2 is

begin

return 'hello,xasxt!'; end;

--调用函数

select fun_hello from dual;

create or replace function fun_sal(sal_val number) return varchar2 is

v_max number; v_min number;

v_return varchar2(50); begin

select max(sal),min(sal) into v_max,v_min from emp; if sal_val>=v_min and sal_val<=v_max then

v_return:='你的输入的工资在最高工资和最低工资之间!'; else

v_return:='你的工资不在最高工资和最低工资范围之间'; end if ;

return v_return;

end;

--调用方式1:使用SQL调用 select fun_sal(20000) from dual; --调用方式2：使用程序块调用 declare

v_str varchar2(50); v_sal number; begin

v_sal:='&sal';

v_str:= fun_sal(v_sal);

dbms_output.put_line(v_str); end;

13.调用存储过程

/**

* 调用存储过程 * 1.创建连接

* 2.创建CallableStatement对象:执行存储过程的对象 :conn.prepareCall(\存储过程名称(?,?,?)}\

* 3.为输入参数绑定值,为输出参数注册类型

* 4.执行存储过程 ,调用 CallableStatement对象中的execute方法 * 5.获取输出的值：CallableStatement对象中的getXxx(输出参数下标); * 6.释放资源 * @param args

*/

public static void main(String[] args) {

try { Class.forName(\

Connection

conn

=

DriverManager.getConnection(\

// System.out.println(conn);

//{call 存储过程名称(?,?,?)}

CallableStatement csmt = conn.prepareCall(\

//为占位符绑定值:如果是输入参数(in):setXxx()方法绑定输入参数,如果是输出参数(out):使用registerOutParameter()方法注册输出参数 csmt.setString(1, \表名 csmt.setInt(2, 4);//绑定第二个占位符的值(分页单位)

csmt.registerOutParameter(3, oracle.jdbc.OracleTypes.INTEGER); //注册一个输出

类型的参数 //调用 execute方法执行存储过程 boolean isCorrect = csmt.execute();//如果第一个结果是 ResultSet 对象，则返回 true；如果第一个结果是更新计数或者没有结果，则返回 false

}

int totalPages = csmt.getInt(3);//获取输出参数的值 System.out.println(\ System.out.println(\csmt.close(); conn.close();

} catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }

14.程序包

/*

包:程序包是对相关过程、函数、变量、游标和异常等对象的封装,程序包由规范和主体两部分组成

--声明程序包

CREATE [OR REPLACE] PACKAGE

package_name IS|AS [Public item declarations] [Subprogram specification] END [package_name]; --声明程序包的主题

CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS [Private item declarations] [Subprogram bodies] [BEGIN

Initialization] END [package_name]; */

create or replace package my_pack is

procedure pro6(hello varchar2); end;

create or replace package body my_pack is

procedure pro6(hello varchar2) is

begin

dbms_output.put_line(hello); end;

end;

--调用程序包中对象

begin

my_pack.pro6('hello world'); end;

15触发器

/*

触发器:触发器是当特定事件出现时自动执行的存储过程 语法:

CREATE [OR REPLACE] TRIGGER trigger_name AFTER | BEFORE | INSTEAD OF

[INSERT] [[OR] UPDATE [OF column_list]]

[[OR] DELETE] ON table_name [FOR EACH ROW] begin

pl/sql_block; end; */

--利用触发器,维护部门人数

--（如果新添加员工,让部门人数+1; --如果删除员工,让部门人数-1;

--如果为员工更改部门:原有部门人数-1，现有部门人数+1）

create table tbl_dept (

deptId number(4) primary key, deptName varchar2(20), deptNum number(4) )

create table tbl_emp

(

empId number(4) primary key,

empName varchar2(20),

deptId number(4) references tbl_dept(deptId) )

create or replace trigger tri_dept after insert or update or delete on tbl_emp for each row begin

if inserting then

update tbl_dept set deptNum = deptNum+1 where deptId=:new.deptId;--:new新增的记录 elsif deleting then

update tbl_dept set deptNum = deptNum-1 where deptId=:old.deptId; --:old旧数据 elsif updating then --:new 新纪录; :old 旧记录

update tbl_dept set deptNum = deptNum-1 where deptId=:old.deptId; update tbl_dept set deptNum = deptNum+1 where deptId=:new.deptId; end if; end;

insert into tbl_emp values(4,'zhaoliu',1) delete from tbl_emp where empId=4

update tbl_emp set deptId = 2 where empId=2

16.存储过程-分页

--使用存储过程进行分页 --分页,rownum select * from emp

select rownum rn,A.* from (select * from emp) A

select rownum rn,A.* from (select * from emp) A where rownum<4 --第一页

select rownum rn,A.* from (select * from emp) A where rownum>3 and rownum<7 --错误 select rownum rn,A.* from (select * from emp) A where rownum<7

--select * from (select rownum rn,A.* from (select * from 表名) A where rownum<结束位置) where rn>开始位置

--结束位置=当前页*分页单位+1

--开始位置=(当前页-1)*分页单位

select * from (select rownum rn,A.* from (select * from emp) A where rownum<4) where rn>0 --第一页

select * from (select rownum rn,A.* from (select * from emp) A where rownum<7) where rn>3 --第二页

select * from (select rownum rn,A.* from (select * from emp) A where rownum<10) where rn>6 --第三页

--创建分页的存储过程(1.获取当前页的数据,2.总页数,3.总记录数)

--存储过程的参数(1.表名(in),2.当前页(in),3.分页单位(in),4.游标(out),5.总记录数(out),6.总页数(out))

--1.声明Ref游标类型

create or replace package xasxt as type my_cur_type is ref cursor; end;

--2.创建存储过程

create or replace procedure fenye(tableName in varchar2,curPage in number,pageSize in number, my_cur out xasxt.my_cur_type,totalRows out number ,totalPages out number) is

v_sql varchar2(200);-- 拼SQL语句 v_begin number;--开始位置 v_end number;--结束位置 begin

v_begin:=(curPage-1)*pageSize;--开始位置=(当前页-1)*分页单位

v_end:=curPage*pageSize+1;--结束位置=当前页*分页单位+1

v_sql:='select * from (select rownum rn,A.* from (select * from '||tableName||') A where rownum<'||v_end||') where rn>'||v_begin; open my_cur for v_sql;--打开游标

v_sql:='select count(*) from '||tableName;

execute immediate v_sql into totalRows ;--动态执行SQL if mod(totalRows,pageSize)=0 then --计算总页数 totalPages:=totalRows/pageSize; else

totalPages:=floor(totalRows/pageSize)+1; end if; end;

/*

jdbc调用分页存储过程

public static void main(String[] args) { try { Class.forName(\ Connection

conn

DriverManager.getConnection(\

CallableStatement csmt = conn.prepareCall(\ csmt.setString(1, \表名 csmt.setInt(2, 1);//当前页

csmt.setInt(3, 5);//分页单位

csmt.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR); csmt.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER); csmt.registerOutParameter(6, oracle.jdbc.OracleTypes.INTEGER); csmt.execute();

//获取输出参数

ResultSet rs = (ResultSet)csmt.getObject(4);//获取游标(查询出的数据) int totalRows = csmt.getInt(5);//总记录数 int totalPages = csmt.getInt(6);//总页数

System.out.println(\总记录数:\总页数:\ while(rs.next()){ int empno = rs.getInt(\

String ename = rs.getString(\

System.out.println(empno+\ }

rs.close(); csmt.close();

conn.close();

} catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace();

}

}

*/

select * from emp

=

本文来源：https://www.bwwdw.com/article/1086.html