oracle7天学习经典笔记

--************************************************ --2010.9.26 oracle第2天2课

--建表语句

create table student( id number(4), name varchar(20), sex char(2), birthday date, sal number(7,2) );

--修改表的信息

--（1）添加字段

alter table student add (score number); --(2)修改字段长度

alter table student modify (score number(3,1)); --(3)删除一个字段

alter table student drop column score;

--查看表的结构（注意，此命令只有用在命令窗口才能够显示表的结构信息） desc student

--打开显示时间的操作开关（注意，此命令只有用在命令窗口才能够显示表的结构信息, --此命会将后面的查询操作执行时间显示出来） set timing on

--查询

select *from student

--如果查询某个字段为空的信息使用 is关键字 select *from student where birthday is null

--插入（注意：时间的插入写法）

insert into student values(1,'yoyo1','男','1-2月-1988','123.11') --转换时间插入的格式输入法，为中国人习惯型的输入格式 alter session set nls_date_format='yyyy-mm-dd' --转换时间输入格式后的插入写法

insert into student values(1,'yoyo1','男','1988-3-2','123.11')

--修改

update 表名 set 字段名1=值1，字段名2=值2... where 条件

--设置一个保存点 savepoint a --删除表信息

delete from student --回滚 rollback

--回滚后可以查询刚刚删除的信息 select *from student

--删除

--(1)删除表的内容，不会删除自动增长列已经增长到的某个值的记录 delete from student --（2）删除表结构 drop table student

--（3）删除表的内容，速度快，删除后没有日志记录,自动增长列会从1重新开始记录 truncate table student

--********************************************** --2010.9.26 oracle第2天3课

select *from emp select *from dept --查询速度的区别

--使用 * 的速度比指明 字段名 的速度 慢

--查询smith的工作，薪水和部门（注意：SMITH 内容的大小写，关键字不区分大小写） select deptno,job,sal from emp where ename='SMITH'

--查询职工一年的工资（sal*13）

--查询结果一样，问题(如果查询字段中有一个为null值，那么该字段跟其他字段合起来查询就为null值)

select (sal*13)+(comm*13) 年工资 from emp select sal*13+comm*13 年工资 from emp

--解决( nvl(comm,0)意思：如果comm字段为空，那么该字段值就为0，如果不为空那么就以该字段值为准进行下一步运算 )

select sal*13+nvl(comm,0)*13 年工资 from emp

--如何显示工资高于3000的员工

select ename,sal from emp where sal>3000

--如何显示工资在800到1600的员工(注意：使用between 800 and 1600，查询出的结果包含800，和1600的值的)

select ename,sal from emp where sal between 800 and 1600

--如何查找1982.1.1后入职的员工(注意： 时间输入 及使用 大于 符合) select ename,hiredate from emp where hiredate>'1-1月-1982' --或者

alter session set nls_date_format='yyyy-mm-dd'

select ename,hiredate from emp where hiredate>'1982-1-1'

--如何使用like操作符

-- %表示：任意0-多个字符，_表示：任意单个字符，?表示：所有 select ename,sal from emp where ename like 'S%' --查询员工名字第3个字符是O的员工

select ename,sal from emp where ename like '__O%'--此处是2条_(下划线)

--where条件中使用in

--查询员工号为：7369,7499,7521

select ename,empno from emp where empno in(7369,7499,7521) --查询没有上级的员工

select *from emp where mgr is null

--查询工资大于500或者职位为MANAGER，并且名字是以S开头的员工 select *from emp where (sal>500 or job='MANAGER') and ename like 'S%'

--order by (默认asc：低到高，高到底：desc) select *from emp sal order by sal desc

--按照部门号升序而雇员的工资降序排列 select *from emp order by empno asc , sal desc --按照部门号升序而雇员的入职降序排列

select *from emp order by empno asc , hiredate desc

--使用列的别名排序，使用年薪排名

select ename,sal*12 年薪 from emp order by 年薪 asc

select ename,sal*12+nvl(comm,0)*12 年薪 from emp order by 年薪 asc

--分页查询

--复杂查询

--如何显示所有员工的最高，最低工资 select MAX(sal) from emp select MIN(sal) from emp

--查询最高工资，最低工资员工的详细信息(注意:使用子查询--把查询出的结果看做一个结果集，来筛选)

select *from emp where sal=(select max(sal) from emp) --查询工资高于平均工资的员工信息

select *from emp where sal>(select avg(sal) from emp)

--grop by 和 having子句

--如何显示每个部门的最高工资，最低工资,并按照部门编号降序排列

select avg(sal),max(sal),deptno from emp group by deptno order by deptno desc

--显示每个部门的每种岗位的平均工资和最低工资

select avg(sal),min(sal),deptno,job from emp group by deptno,job

--显示评价工资低于2000的部门号和它的平均工资

select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000

--分组统计的规律

--分组函数只能出现在选择列表、having、order by子句中 --顺序：group by,having,order by

--笛卡尔集：多表查询的条件是，至少不能少于表的个数减1

--查询员工的姓名，工资，工资级别 select *from salgrade select *from emp select *from dept

select a.ename,a.sal,b.grade from emp a,salgrade b where a.sal between b.losal and b.hisal

--显示雇员名，工资，及所在部门名字，并按部门排序

select a.ename,a.sal,b.dname from emp a,dept b where a.deptno=b.deptno order by b.deptno

--自连接(单独一个表多次利用自己)

--如何显示某个员工的上级领导的名字(难点!!! 把一张表当2张表使用) select a.ename,b.ename from emp a,emp b where a.mgr=b.empno

--子查询又称嵌套查询（一个查询语句里面出现多个select关键字，也就是一个以查询结果为条件的语句）

--如何显示与smith同一部门的所有员工

--数据库在执行sql语句时，是从左到右扫描执行，而写sql语句又是从右往左写的

--写sql语句时，如果条件是多个，那么把第一次查询出来结果最多的那个放在左边，提高查询性能。最好从多的开始查询再到少的查询

select ename 名字,deptno 同一部门编号 from emp where deptno=(select deptno from emp where ename='SMITH')

--多行子查询

--如何查询和部门10相同的雇员的名字、岗位、工资、部门号

--返回的是多行的语句，那么条件使用in关键字，而不用 ‘ = ’ select *from emp where job in (select job from emp where deptno=10)

--使用all关键字

--如何查询工资比部门30的所有员工的工资高的员工的姓名、工资、部门号 --方法一

select ename,sal,deptno from emp where sal >all (select sal from emp where deptno=30) --方法二(查询快点:原因是在第二次筛选结果集中他的筛选的范围要小店)

select ename,sal,deptno from emp where sal >all (select max(sal) from emp where deptno=30)

--any关键字的使用

--如何显示工资比部门30号的任意一个员工工资高的员工姓名，工资，部门号 --方法一

select ename,sal,deptno from emp where sal >any (select sal from emp where deptno=30) --方法二(查询快点:原因是在第二次筛选结果集中他的筛选的范围要小店)

select ename,sal,deptno from emp where sal >any (select max(sal) from emp where deptno=30)

--多列子查询(如果根据查询的结果集里面包含2列，而不是1列) --查询与smith的部门和岗位完全相同的所有雇员 --注意：列匹配顺序要一样

select *from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH')

--如何显示高于自己部门平均工资的员工的信息(--难点 --)

--1.首先查询各个部门的平均工资和部门号（其中 a 代表平均工资的 别名） select deptno,avg(sal) a from emp group by deptno

--2.把上面的查询结果看做是一张 子表 ,并给他取个别名(其中 a1 代表上面所查询的结果的表的别名)

--给表取别名不能加as，给列取别名可以加as

select ename,a2.sal,a2.deptno,a1.a from emp a2,(select deptno,avg(sal) a from emp group by deptno) a1

where a2.deptno=a1.deptno and a2.sal>a1.a order by deptno

--分页查询（oracle分页一共有3种方式） --按雇员的id号升序查询 --方法一：rownum分页

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

--问题：如何显示第6条到10条的记录？使用分页显示 --1.rownum分页( select *from emp )

--2.显示 rownum[oracle分配的] 行号( select a1.*,rownum rn from (select *from emp) a1 ) --3.显示某一部分的信息（rownum只能用一次，不能用多次，使用多次试不会查询出数据结果的）

select a1.*,rownum rn from (select *from emp) a1 where rownum<=10

--不能写成如下这样：select a1.*,rownum rn from (select *from emp) a1 where rownum<=10 and rownum>=6

--4.完成分页效果机制 select *from

(select a1.*,rownum rn from (select *from emp) a1 where rownum<=10) where rn>=6

--5.几个查询变化

--a.如果指定查询列，只需修改最底层的查询：select *from emp select ename,sal from emp

--b.如果是排序，只需修改最底层的查询:select *from emp select ename,sal from emp order by sal

--方法二：（最快） --根据ROWID来分

select *from t_xiaoxi where rowid in (select rid from

(select rownum rn,rid from

(select rowid rid,cid from t_xiaoxi order by cid desc) where rownum<10000) where rn>0080) order by cid desc

--方法三：按分析函数来分（最慢） select *from

(select t.*,row_number() over (order by cid desc) rk from t_xiaoxi t)

where rk<10000 and rk>9980

--查询总共有多少记录：使用count()函数

--用查询结果创建表（常用技巧）

create table myemp2 (id,name,sal) as select empno,ename,sal from emp --查看表的结构：desc myemp2 select *from myemp2

--复杂查询，合并查询，使用关键字：union ,union all,intersect,minus

--(1)union 该操作符用于取得2个结果集的并集，使用时会自动去掉结果集中的重复行 --集合查询速度比and，between等快

select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job='MANAGER' --(2)union all 会出现重复，取并集

select ename,sal,job from emp where sal>2500 union all

select

ename,sal,job from emp where job='MANAGER' --(3)intersect 取交集 --(4)minus 取差集

--创建数据库有2种方法 --(1)使用工具；

--dbca【数据库创建配置助手】

--(2)。。。

*******************************************************************

--2010年10月1日 --第3章 事务与加锁

--时间转换函数to_date('1988-12-1','yyyy-mm-dd')

--设置回滚点：注意（只有在事务没有提交前管用） savepoint a1; rollback to a1;

--在java程序中如何使用事务

--加入事务处理(在java代码中写入)

--conn.setAutoCommit(false);//设置不能提交点

--只读事务：指只允许执行查询的操作，而不允许执行dml（增，删，改）操作的事务 --使用只读事务可以确保用户只能取得某时点的数据 set transaction read only

--1.sql函数的使用

lower(char):将字符串转换为小写 upper():--大写 length():

substr(char,m,n):截取字符，m 代表从第几的个字符开始截取 ，n 代表取3个

--以首字母大写方式显示所有员工的信息 --第一步：取首写字母大写

select upper(substr(ename,1,1)) from emp --第二步：取后面小写部分

select lower(substr(ename,2,length(ename)-1)) from emp --合并：使用 || 符合

select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp

--2.sql函数的使用

replace(char1,search_string,replace_string)--替换字符串 instr(char1,char2,[,n[,m]])--找函数的位置

--3.sql（数学）函数的使用:n 代表字字符 round(n,[m]):四舍五入 trunc(n,[m])：截取数字 mod(m,n)：取模

floor(n)：返回<=n的最大整数 ceil(n)：返回>=n的最大整数

--表dual是用来做测试用的表 select mod(10,3) from dual

--显示在一个月为30天的情况所有员工的日新金，忽略余数 使用floor,trunc...

--日期函数

--默认情况：dd-mm-yy(即12-10月-2010) sysdate：获取系统时间

add_months(时间或者字段,所要加的月份数) last_day():返回指定日期所在月份的最后一天 --dual表位测试表

select sysdate from dual--查询当前时间 --查询入职超过300个月的员工

select *from emp where sysdate>add_months(hiredate,300) --查询员工入职的天数

select sysdate-hiredate \入职天数\--去掉小数

select trunc(sysdate-hiredate) \入职天数\--找出各月倒数第3天受雇的所有员工 select hiredate,last_day(hiredate) from emp

select hiredate,ename from emp where last_day(hiredate)-2=hiredate

--转换函数（隐形转换）

to_char(要转换的时间字符,转换成什么格式):

select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp

--在插入时间数据时，如果插入需要精确到分钟，秒，那么显示的时候就需要用该函数转换一下，才会显示到时、分、秒 --to_char()用于人民币的格式转换

select to_char(sal,'L99,999.99') from emp select to_char(sal,'$99,999.99') from emp

--显示1980年,12月份入职的员工

select *from emp where to_char(hiredate,'yyyy')=1980 select *from emp where to_char(hiredate,'mm')=12

--to_date()函数...

--系统函数 sys_context()：

--(1)terminal:当前会话客户所对应的终端的标识符

select sys_context('USERENV','db_name') from dual--查询当前使用的数据库 --(2):language

select sys_context('USERENV','language') from dual --(3):session_user 当前用户

select sys_context('USERENV','session_user') from dual

--(4):current_schema 当前方案名（方案名与用户名一样）用户和方案的关系 --用户和方案的关系

select sys_context('USERENV','current_schema') from dual

--数据库管理员

sys(高于后者)与system用户的区别: (1)存储的数据的重要性不同 (2)权限不同

-- sys用户必需以as sysdba或者as sysoper身份登录

-- system用户可以以normal登录，但是就是一个普通用户

--注意：system可以以sysdba登录，但是如果以这样登录的话，那么就是一个sys用户了，具有他的所有权限

sysdba(高于后者)与sysoper的区别：sysoper>dba(普通用户)

前者可以打开、关闭数据库，创建和删除，后者不行，前者数据恢复可以是部分，可以是全部，而后者是全部恢复 --shutdown（关闭数据库） --startup（启动数据库）

--管理初始化的参数

--show parameter(该行没有运行出来，有点问题)

数据库表的逻辑备份与恢复 --逻辑备份和物理备份

前者：是将数据库的对象导出到一个磁盘里面存放（备份）

将磁盘的信息导入到数据库（恢复）--数据库在运行状态才能恢复

--导出：使用命令exp命令来完成

--注意：在导入导出的时候要到oracle的bin目录下EXP.exe中去操作********** 一、导出表

（1）导出表 a.导出自己的表

exp userid=scott/tiger@ORCL tables=(emp,dept) file=e:\\save1.dmp

b.导出其他方案的表(用户需要具有dba或者exp_full_database的权限) exp userid=scott/tiger@ORCL tables=(scott.emp,scott.dept) file=e:\\save2.dmp

（2）导出表的结构

exp userid=scott/tiger@ORCL tables=(emp,dept) file=e:\\save3.dmp rows=n

（3）使用直接导出方式(处理大表，速度快)

exp userid=scott/tiger@ORCL tables=(emp,dept) file=e:\\save4.dmp direct=y

二、导出方案，使用export工具导出一个方案或者多个方案中对象的表、索引、约束 (1)导出自己的方案

exp scott/tiger@ORCL owner=scott file=e:\\sava4.dmp

(2)导出其他的方案(用户需要具有dba或者exp_full_database的权限) exp system/manager@ORCL owner=(system,scott) file=e:\\sava4.dmp

三、导出数据库(利用export导出所有数据库中的对象及数据，用户需要具有dba或者exp_full_database的权限)

exp userid=system/manage@ORCL full=y inctype=complete file=e:\\save5.dmp --inctype=complete:增量备份 --full=y ：全部导出

--导入省略...第4天3讲10分钟

--数据字典（基表，存放静态的数据）和动态性能视图（动态的数据） 数据字典的所有者是sys用户 数据字典只能执行select语句

user_xxx，all_xxx,dba_xxx --当前用户所建的所有表

select table_name from user_tables --当前用户可以访问到的所有表 select table_name from all_tables

--当前系统各个用户的所有表（只有是dba角色和拥有查询所有表权限的用户才可以访问） select table_name from dba_tables

--用户名，权限，角色

查询dba_users可以显示所有数据库用户的详细信息 select username,password from dba_users

查询dba_sys_privs可以显示用户具有的系统权限

dba_tab_privs可以显示用户具有的对象权限 dba_cal_privs可以显示用户具有列的权限 dba_role_privs可以显示用户所具有的角色

select *from dba_role_privs where GRANTEE='SCOTT' --desc dba_role_prives ...

问题：

1、如何查询一个角色包含的权限 2、oracle究竟有多少种角色

-- 一般在命令窗口运行

查询oracle中所有系统权限，一般是dba

select *from system_privilege_map order by name 查询oracle中所拥有的角色，一般是dba select *from dba_roles

查询oracle中所有对象权限，一般是dba select distinct privilege from dba_tab_privs 查询数据库的表空间

select tablespace_name from dba_tablespaces

--权限

对象权限和系统权限

--动态性能视图（省略）

--管理表空间和数据文件(表空间是一个逻辑组织结构,是由数据文件构成的) 数据库的逻辑结构（逻辑结构包括：空间，段，区，块）

表空间的作用：控制数据库占用磁盘空间，dba可以将不同数据类型部署到不同的位置，提高i/o性能，同时利于备份和恢复

--建立表空间(一般由dba来建，或者拥有此权限的用户) create tablespace

--建立数据表空间(size不能超过500M)

create tablespace sp1 datafile 'e:\\tablespace1.dbf' size 10M uniform size 128k --使用数据表空间 create table mytable( id number(4),

name varchar2(20) ) tablespace sp1 --改变表空间状态 （1）使表空间脱机

alter tablespace 表空间名 offline

（2）使表空间联机

alter tablespace 表空间名 online

（3）只读表空间(不能向该表空间里添加任何数据信息)

alter tablespace 表空间名 read only--如果想修改表空间的状态：（alter tablespace 表空间名 read write）

--显示表空间包括的表

select *from all_tables where tablespace_name='表空间名' --查询表属于哪个表空间

select tablespace_name,table_name from user_tables where table_name='表名'

--删除表空间

-- including contents:表示删除表空间时，删除该空间的所有数据对象，而datafiles表示将数据库文件也删除

drop tablespace 表空间名 including contents and datafiles

--扩展表空间

增加表空间大小，有3种方法 （1）增加数据文件

alter tablespace 表空间名 add datafile （2）增加数据文件大小

alter tablespace 表空间名 'e:\\name1.dbf'

（3）设置文件自动增长(size不能超过500M)

alter tablespace 表空间名 'e:\\name1.dbf' autoextend on next 10m maxsize 500m

--移动数据文件

（1）确定数据文件所在的表空间

select tablespace_name from dab_data_files where file_name='e:\\name1.dbf' （2）使表空间脱机

alter tablespace 表空间名 offline

（3）使用命令移动数据文件到指定的目标位置 host move 原路径 新路径

（4）移动数据文件（使用alter tablespace命令对数据文件进行逻辑修改） alter tablespace 表空间名 rename datafile '旧路径' to '新路径' （5）使表空间联机

alter tablespace 表空间名 online

--显示表空间所包含的数据文件

select file_name,bytes from dba_date_files where tablespace_name='表空间名' --显示表空间信息

select tablespace_name from dba_tablespaces

*******************************************************************************

***--2010年10月5日晚上 --为字段增加约束不为空，使用modify关键字，其他的约束用add constraint ，删除使用drop constraint

alter table 表名 modify 字段名 not null

alter table 表名 add constraint cardunique unique(字段名)--cardunique：为约束加字段名的连写

--删除主键约束

alter table 表名 drop primary key cascade

--列级定义和表级定义

--索引（数据海量的时候用索引，提高查询速度，建了索引后会将该字段排序便于查询） --经常查询的字段建索引 索引使用原则：

在大表上建立索引

在where子句或是连接条件上经常引用的列上建索引 索引的层次不要超过4层 单列索引

create index 索引名 on 表名（列名）

create index NameSuoYin on customer(customerId)

复合索引（一般把一次就能筛选很多数据的字段放在前面，减少后面筛选的负担） create index emp_idx1 on emp(ename,job)--先ename，后job create index emp_idx1 on emp(job,ename)--先job，后ename

索引的缺点：系统需要用空间来存放索引而且空间为表空间的1.2倍，更新数据时，同时需要对索引进行更改

--B*树，方向索引，位图索引... 显示表的索引

select index_name,index_type from user_indexes where table_name=表名 显示索引列

select table_name,column_name from user_ind_columns where index_name=索引名

--管理权限和角色

--10月14日

grant create session (with admin option)

--sys用户创建用户monkey

create user monkey identified by m123 --scott用户给monkey授权 grant select on emp to monkey

--赋予monkey增删该查的所有权限 grant all on emp to monkey

--只授予emp表一个字段的修改权限 grant update on emp(sal) to monkey --授予index权限（索引）

grant index on soctt.emp to blake (with grant option)

--比较区别(对象权限,角色 会级联回收，系统权限不级联回收) --system用户授权

grant create session to to blake2 with admin option --连接到scott用户

grant select on emp to blake2 with grant option --连接到system用户,创建新用户 create user blake3 identified by m123 --给blake3授权

grant create session to blake3 with admin option --删除select

revoke select on emp from blake2

--预定义角色与自定义角色 connect resource dba --cba创建自定义角色，（具有crate role 的系统权限） --建立角色（不验证）

create role 角色名 not identified --显示角色信息

select *from dba_roles

--显示角色具有的系统权限

select privilege,admin_option form role_sys_privs where role='角色名' --显示角色具有的对象权限 dba_tab_privs

--显示用户具有的角色及默认角色(dba_role_privs)

select granted_role,default_role from dba_role_privs where grantee='角色名'

*********************************************************************

--10月14日下午

--创建存储过程，该过程可以向表中添加记录 --创建表

create table mytest ( name varchar2(20), pwd varchar2(20) )

--创建过程(replace表示如果有该存储过程替换)

create or replace procedure pro_test is begin --执行部分

insert into mytest values('yoyo1','pwdyoyo'); end;

--查看错误信息使用：show error命令 --如何调用存储过程

①exec 过程名(参数1，参数2...) ②call 过程名(参数1，参数2...)

create or replace procedure pro_name2 is begin --执行部分

delete from mytest where name='yoyo'; end;

--调用存储过程（就会执行上面的操作） exec pro_name2;

--简单分类： 块（编程）：过程(存储过程)，函数，触发器，包

--编写规范： ①注释 单行注释--

多行注释/* ... */

②标识符合的命令规范

定义变量，用v_作前缀：v_sal 定义常量，用c_作前缀：v_rate

定义游标，用_cursor作后缀：emp_cursor 定义例外，用e_作前缀：e_error

--介绍：块（block）

定义部分，执行部分，例外处理部分

declear :定义常量，变量，游标，例外，复杂数据类型 begin :执行的pl/sql语句和sql语句 exception :处理运行的各种错误

--块结构与java编程的比较 --java：

public static void main(String []args){ int a=1; try{

a++; }catch(Execpion e){ //捕获异常 } }

--块：

set serveroutput on --打开输出选项

set serveroutput off --执行语句之后，不会输出信息

--eg： begin

dbms_output.put_line('hello'); end;

dbms_output是Oracle的提供的一个包

--eg2 declare v_ename varchar2(5); v_sal number(7,2); begin --把查出来的信息放到变量v_ename 里面 --&表示接收从控制台输入的信息 select ename,sal into v_ename,v_sal from emp where empno=&no; dbms_output.put_line('雇员名：' || v_ename || '工资'：|| v_sal); end;

--eg3(例外处理_异常)

--如果输入的雇员编号不存在，但是我想他输出信息为：not found ,后面的语句继续运行 declare v_ename varchar2(5); v_sal number(7,2); begin --把查出来的信息放到变量v_ename 里面 --&表示接收从控制台输入的信息 select ename,sal into v_ename,v_sal from emp where empno=&no; dbms_output.put_line('雇员名：' || v_ename || '工资'：|| v_sal); --异常处理 exception

when no_data_found then

dbms_output.put_ling('not found info!') end;

--eg4

create procedure pro_name(newName varchar2,newSal number) is begin

--执行部分，根据用户名修改工资

update emp set sal=newSal where ename=newName; end;

--eg5(存储过程是给java调用的)，在java程中如何调用存储过程 java:

Class.forName(\Connection

conn=DriverManager.getConncection(\CallableSattement cs=conn.prepareCall(\cs.setString(1,\sc.setInt(2,10); cs.execute();

--函数

--10月15日下午 条件分支语句

--编写一个过程，可以输入一个雇员名,如果工资〈2000，就增加10%的工资 create or replace procedure sp_pro6(spName varchar2) is v_sal emp.sal%type; begin

select sal into v_sal from emp where ename=&spName; if v_sal〈2000 then

update emp set sal=sal*1.1 where ename=spName; end if; end;

--二重条件分支

create or replace procedure sp_pro7(spName varchar2) is v_comm emp.comm%type; begin

select comm into v_comm from emp where ename=&spName; if v_comm<>0 then

update emp set comm=comm+100 where ename=spName; else

update emp set comm=comm+200 where ename=spName; end if; end;

--多重条件分支

create or replace procedure sp_pro8(spNo number) is v_job emp.job%type;

begin

select job into v_job from emp where empno=spNo; if v_job='PRESIDENT' then

update emp set sal=sal+1000 where empno=spNo; --注意elsif写法

elsif v_job='MANAGER' then

update emp set sal=sal+500 where empno=spNo; --注意else写法 else

update emp set sal=sal+200 where empno=spNo; end if; end;

--loop循环

create or replace procedure sp_pro9(spName varchar2) is --:=表示赋值

v_num number:=1; begin loop insert into users values(v_num,spName); --判断是否退出循环 exit when v_num=10; --自加 v_num:=v_num+1; end loop; end;

--while循环

create or replace procedure sp_pro9(spName varchar2) is --:=表示赋值

v_num number:=11; begin

while v_num<-20 loop insert into users values(v_num,spName); v_num:=v_num+1; end loop; end;

--for循环 结构： begin for i in reverse 1..10 loop insert into users values(i,'yoyo'); --sql语句 end loop;

end;

--顺序控制语句goto,null ①goto语句 --不建议使用

用于多重循环语句，直接跳出循环 declare i int :=1; begin loop dbms_output.put_line('输出i='||i); if i=10 then goto end_loop; end if; i:=i+1; end loop; <> --结束语句跳出循环 dbms_output.put_line('循环结束'); end;

②null --提高pl/sql的可读性

--***********分页**************** --编写pl/sql的分页过程

--book表 书号，书名，出版社 --建表：

create table book( bookId number, bookName varchar2(50), publishHouse varchar2(50); )

--编写存储过程

①无返回值的存储过程

--in代表spBookId 是一个输入值，默认为in --out表一个输出参数

create or replace procedure sp_pro(spBookId in number,bookName in varchar2,sppublishHouse in varchar2) is begin insert into book values(spBoodId,spBoodName,sppulishHouse); end;

--java代码中调用存储过程 //调用没有犯或者的过程 --main：

Class.forName(\

Connection

ct=DriverManager.getConnection(\//创建CallableStatement--调用存储过程的链接

CallableStatement cs=ct.prepareCall(\cs.setInt(1,10);

cs.setString(2,\笑傲江湖\； cs.setString(3,\人民出版社\cs.execute();//执行

//最后要关闭所有的链接...省略

②有返回值的存储过程（输入雇员编号，返回雇员姓名）

create or replace procedure sp_pro(spNo in varchar2,spName out varchar2) is begin select ename into spName from emp where empno=spNo; end;

--java代码中调用存储过程 //如何调用有返回值的存储过程

CallableStatement cs=ct.prepareCall(\cs.setInt(1,7788);

//注意此处赋值：oracle.jdbc.OracleTypes.VARCHAR用何种类型取决于‘?’的类型 cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR); cs.execute(); //取回返回值

String name=cs.getString(2);--注意‘?’的顺序 System.out.println(\编号的名字是\

--③返回值有多个的存储过程（直接在后面加就可以）

create or replace procedure sp_pro(spNo in varchar2,spName out varchar2，spSal out number,spJob out varchar2) is begin select ename,sal,job into spName,spSal,spJob from emp where empno=spNo; end;

--java代码中调用存储过程 //如何调用有返回值的存储过程

CallableStatement cs=ct.prepareCall(\cs.setInt(1,7788);

//注意此处赋值：oracle.jdbc.OracleTypes.VARCHAR用何种类型取决于‘?’的类型 cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR); cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);

cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR); cs.execute(); //取回返回值

String name=cs.getString(2);--注意‘?’的顺序 System.out.println(\编号的名字是\

--④有返回值的存储过程（返回列表【结果集】） --建包,在该包中定义一个类型(游标):test_cursor create or replace package testpackage as type test_cursor is ref cursor; end testpackage;

--建立存储过程：p_cursor out testpackage注意他的写法（游标 out + 包名）

create or replace procedure sp_pro(spNo in number,p_cursor out testpackage.test_cursor) is begin

open p_cursor for select *from emp where deptno=spNo; end;

--如何在java中调用该存储过程 //创建CallableStatement

CallableStatement cs=ct.prepareCall(\//给?赋值 cs.setInt(1,10); //

cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR); cs.execute(); //得到结果集

ResultSet rs=(ResultSet)cs.getObject(2); while(rs.next()){ System.out.println(rs.getInt(1)+\}

--分页存储

select t1.*,rownum rn from (select * from emp) t1 where rownum<=10;

select * from

(select t1.*,rownum rn from (select * from emp) t1 where rownum<=10) where rn>=6;

--开发一个包

--建包,在该包中定义一个类型(游标):test_cursor create or replace package testpackage as type test_cursor is ref cursor;

end testpackage;

--分页存储过程

create or replace procedure fenye (tableName in varchar2, pagesize in number, pageNow in number,

myrows out number,--总记录数 myPageCount out number,--总页数

p_cursor out testpackage.test_cursor--返回的记录集 ) is

--定义部分

--定义sql语句 字符串 v_sql varchar2(1000); --开始条数

v_begin number:=(pageNow-1)*pagesize+1; --结束条数

v_end number:=pageNow*pagesize;

begin v_sql:='select * from(select t1.*,rownum rn from (select * from '|| tableName||') t1 where rownum<='||v_end||') where rn>='v_begin?; --把游标与sql语句关联起来 open p_cursor for v_sql; --计算myrows和myPafeCount,组织一个sql语句 v_sql:='select count(*) from '|| tableName; --执行sql语句，并把返回的值赋给myrows execute immddiate v_sql into myrows; --计算myPageCount,使用mod函数判断 if mod(myrows,pagesize)=0 then myPageCount:=myrows/pagesize; else myPageCount:=myrows/pagesize+1; end if; --关闭游标 close p_cursor; end;

--使用java测试分页存储过程

CallableStatement cs=ct.prepareCall(\cs.setString(1,\cs.setInt(2,5); cs.setInt(3,1);

cs.registerOurParameter(4,oracle.jdbc.OracleTypes.INTEGER);//注册总记录数 cs.registerOurParameter(4,oracle.jdbc.OracleTypes.INTEGER);//注册总页数 cs.registerOurParameter(4,oracle.jdbc.OracleTypes.CURSPR);//注册返回结果集 cs.execute();

//取出总记录数(注意getInt(4)中4是有该参数的位置决定的) int rowNum=cs.getInt(4); int pageCount=cs.getInt(5);

ResultSet rs=(ResultSet)cs.getObject(6);

//测试是否正确

System.out.println(\System.out.println(\while(rs.next){ System.out.println(\编号：\名字：\}

--新要求，要求按照薪水低——高顺序排序，然后取出6-10名的人员只需修改内层的sql语句

--例外传递 declare --定义

v_ename emp.ename%type; begin

select *from emp where empno=&gno; dbms_output.put_line('名字：'||v_ename); exception when no_data_found then dbms_output.put_line('编号没有！！'); end;

--pl/sql中的例外

--casa_not_found (如果在when子句中没有包含必须的条件分支) create or replace procedure sp_pro(spno number) is v_sal emp.sal%type; begin

slect sal into v_sal from emp where empno=spno; case

when v_sla<1000 then

update emp set sal=sal+100 where empno=spno; when v_sal<2000 then

update emp set sal=sal+200 where empno=spno; end case;

exception

when case_not_found then

dbms_output.put_line('case语句没有与'||v-sal||'相匹配的条件'); end;

--cursor_already_open(如果游标已经打开，又打开一次) --dup_val_on_index(在唯一索引插入重复列值)

--invaild_cursor(如果游标没有打开就开始操作,或者打开了之后又打开) declare

cursor emp_cursor is select ename,sal from emp; emp_record emp_cursor%rowtype; begin

--打开游标

open emp_cursor;

fetch emp_cursor into emp_record;

dbms_output.put_line(emp_record.ename); close emp_cursor; exception

when invalid_cursor then

dbms_output.put_line('请检查游标是否打开'); end;

--invalid_number(当输入数据有误) begin

update emp set sal=sal+'1oo'; exception

when invalid_number then

dbms_output.put_line('输入的数字不正确'); end;

--too_many_rows(如果返回超过一行) declare

v_ename emp.ename%type; begin

select ename into v_ename from emp; exception

when too_many_rows then

dbms_output.put_line('返回了多行'); end;

--zero_divide(当除数为0时)

--value_error(变量的长度不足容纳实际数据)

--login_denide(非法用户登录)

--not_logged_on(用户没有登录执行操作) --storage_erro(内存不足或损坏) --timeout_on_resource(等待超时)

--非预定义例外：自定义例外

--处理自定义例外

create or replace procedure ex_test(spNo number) is

myex exception; begin

--更新用户sal,只有select查询语句如果输入编号错误会抛data_not_found update emp set sal=sal+1000 where empno=spNo; --sql%notfound:表示没有更新

--raise myex:表示出发myex的例外 if sql%notfound then raise myex; end if; exception

when myex then

dbms_output.put_line('没有更新任何用户'); end;

--视图：视图时一个虚拟表，其内容由查询定义 --视图可以检查复杂的查询

--创建视图，把emp表薪水小于1000的雇员映射到该视图 create view myview as select *from emp where sal<1000; select *from myview

--显示雇员编号，姓名，部门名称 select *from dept

create view myview2 as select emp.empno,emp.ename,dept.dname from emp,dept where emp.deptno=dept.demtno select *from myview2

--创建和修改：如果后面跟有[with read only]表示视图只能读 --drop 视图名

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