ORACLE 学习总结（未修订）

最新推荐文章于 2025-01-18 00:37:32 发布

devid

最新推荐文章于 2025-01-18 00:37:32 发布

阅读量849

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/devidh/article/details/9115949

本文深入解析了Oracle数据库的基本操作，包括启动服务、管理用户权限、角色与对象权限传递等核心概念。同时，展示了如何在Java程序中调用Oracle数据库函数，涉及加载驱动、获取连接、准备调用语句及执行调用流程，适用于数据库与Java开发人员。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

ORACLE 学习总结（未修订）

1 启动oracle服务（使用oracle必须启动的两个服务）

监听和实例（lsnrctl意识是listener-control）

启动监听服务 ->lsnrctl start （+监听的名字，不写为默认名字）

停止监听服务 ->lsnrctl stop （+监听的名字，不写为默认名字）

启动数据库实例 -> oradim -starup -sid orcl(实例名)

（或者 -> net start OracleServiceORCL）

说明：启动数据库实例就是启动一个数据库；

也可以同时启动多个实例；

2 oracle系统默认的三个用户

SYS用户：超级管理员，权限最高，它的角色是DBA。默认密码是change_on_install。具有创建数据库的权限

SYSTEM用户：系统管理员，权限很高，它的角色是DBA operator，默认密码manager。不具有创建数据库的权限！

普通用户(normal)，如系统安装时的scott用户，默认密码是tiger。普通用户的权限是SYS用户或SYSTEM用户给的，如果没有给，那普通用户连很基本的访问权限，连接权限也没有。

3 创建用户

用户登录 –> conn user/password@ip(网络服务器名)[若是特权用户必须带上 as sysdba ]

用超级用户登录 ->sqlplus / as sysdba;

显示用户 ->show user;

创建用户 ->create uesr username(用户名) identified by password(密码);

修改当前用户的密码 –> passw；（说明：如果要修改其他用户的密码，需要sys/system登录）；

删除当前用户 –> drop user username cascade;（如果删除的用户已经建立了表，则必须带cascade关键字）；

断开与当前数据库的链接 -> disconn;

断开与数据库的链接，并退出 –> exit;

4 系统权限

创建会话权限（用户授权） ->grant create session to username;

撤销创建会话权限 -> revoke create session fromuserename;

创建表权限 ->grant create table to username;

撤销权限创建表权限 ->revoke create table from username;

创建表空间权限 ->grant unlimited tablespace to name;

撤销创建表空间权限 ->revoke unlimited tablespace fromusername;

创建表 ->create table tablename(id int);

获取系统信息 ->select * from user_sys_privs;

所有用户授权（public）-> grant create session to public;

5 对象权限(对象向对象授权，谁拥有权限谁授权)

对象授权 -> grant select on table1 to username2;

全部授权 -> grant all on table1 to username2;

所有用户授权 -> grant all on tabe1 to public;

撤销授权 -> revoke all on table1 from username2;

对象所有表的权限 -> select * from user_tab_privs;

指定用户对表的某一列进行操作 ->grant update|insert(某一列) on mytable to username1;(查询，删除不能控制到列)

查询对列的操作权限 -> select * from user_col_privs;

6 权限传递

系统权限实现权限传递 -> grant create table|alter any table|(权限) to username with admin option;

对象权限实现权限传递 -> grant select on mytable to username1with grant option;

7 角色（权限的集合）

创建角色 -> create role myrole;

赋予角色权限 -> grant create table,create session tomyrloe;

角色赋予用户 -> grant myrole to username;

删除角色 ->drop role myrole;

注意：

有些系统权限无法赋予角色

如：unlimited tablespace,alert table,drop table

表示属于某个用户的，但是角色是不属于某个用户的。

8 修改用户密码

修改普通户密码 -> alert user username identified bynewpassword;

9 补充命令

获取一个数据库中有多少张表 -> select * from tab;

获取表的完整结构 -> desc tablename;

10 文件操作命令

运行sql脚本–> start d:/aa.sql;(->@ d:/a.sql;)

编辑sql脚本–> edit d:/aa,sql;

将sql*plus屏幕上的内容输入到指定文件 –> spool d:/bb.sql; (创建bb.sql文件)

Spooloff;

10 显示和设置环境变量

展示行的宽度 -> show linesize

设置行的宽度 –> set linesize 100;

展示每页显示的行数 –> show pagesize

设置每页显示的行数 -> set pagesize 10

11 oracle 基本查询

查看表的结构 –> desc tablename;

查看取消重复行 –> select distinct 【列名】from tablename;

如何处理null值？函数 nvl（row1,row2）,如果row1为null 则就用row2所替代，如果row1不为空就取row2的值；

Oracle查询日期格式为“day/month月/year”或者“day-month月-year”；

如何使用like？ %表示0到多个任意多的字符， _ 表示单个的任意字符；

排序查询（order by）-> select * from tablename order by row (asc/desc);(说明：默认排序是升序排列，asc是升序排列，desc是降序排列)；

多个字段排序 -> select * from emp order by row1(asc),row desc;

使用别名排序 –> select row1 as name,row2 fromtablename order by name;

12 oracle 复杂查询（数据分组）

分组函数：

查询最大值 –> select max(row) from tablename

查询最小值 –> select min(row) fromtablebname;

查询某一列值的总和 -> select sum(row) from tablename;

查询某一列值的平均值 –> select avg(row) fromtablename;

查询表的总行数或者某一列非空值的总行数 –> select count(*/row) fromtablename;

group by 和 having 子句：

group by 用于对查询结果进行分组统计 –> select 表达式1,表达式2,…,row1,row2,… from tablename group by row1,row2,..;

Having 用于限制分组显示结果 –> select 表达式1,表达式2,…,row1,row2,… from tablename group by row1,row2,.. having(条件); (说明：条件中所用的参数必须是select后面的表达式或者row)；

数据分组总结：

分组函数只能出现在选择列表，having，order by子句中；

如果select子句中同时出现 order by，having，group by中那么他的顺序是group by….having….order by ;

在选这列中如果有列，表达式，和分组函数，那么这些列和表达式必须有一个出现在group by子句中，否则就会出错；

13 oracle 复杂查询（子查询）

不同表之间进行查询 –> select a1.row1,a1.row2,…a2.row1,a2.row2,…fromtable1 a1,table2 a2 where a1.row1=a2.row1(条件);

内连接查询（一个表链接自身）-> select a1.row1,….,a2.row,…. Fromtable a1,table a2 where a1.row1=a2.row2(条件);

子查询？子查询是指嵌入在其他sql语句中select语句也叫嵌套语句；

单行子查询？是指只返回一行数据的子查询 -> select * from table where row =(select row from table/table1 where [条件])；

多行子查询？是指返回多行语句的子查询 -> select * from table where row in(select row from table/table1 where [条件])；

多行子查询中使用all -> select * from table where row> all(select row from table/table1 where [条件])；

多行子查询中使用any -> select * from table where row> any(select row from table/table1 where [条件])；

多行子查询（特例）-> select row1,row2,…from table where(row1,row2,….)=(select row1,row2,…from table where(条件));

把一个子查询看做一个子表->selecta1.row1,a1.row2,a2.row1,a2.row2,… from table1 a1 ,(selectrow1,avg(row2),max(row3),….from table2 group by row1) a2 where a1.row=a2.row(条件)；

14 oracle 复杂查询（分页查询）

Oracle分页查询（三种，介绍其中一种 rownum分页）

Rownum分页

Ø 建立视图 -> select * from table

Ø 显示rownum（oracle分配行id号）->select a1.* ,rownumrn from (select * from table) a1;

Ø 进行分页，如显示从5—10行的数据:

显示小于等于10行一下的数据-> select a1.* ,rownum rnfrom (select * from table) where rownum <= 10;

显示5—10行一下的数据->select * from (select a1.* ,rownum rn from (select * from table) a1 where rownum <= 10) wherern>=5;

Ø 对查询进行变化，如指定查询列，对查询进行排序只需修改最里层的查询；

指定查询列 ->select * from (select a1.*,rownum rnfrom (select row1,row2,row3,… from table) a1 where rownum <= 10) rn >=5;

对查询进行排序 –> select * from (selecta1.*,rownum rn from (select row1,row2,row3,… from table order by row1) a1 whererownum <= 10) rn >= 5;

Rowed 分页（不做介绍）；

分析函数分页(不做介绍)；

15 oracle 复杂查询（合并查询）

再实际应用中，为了合并多个select查询结果，可以使用集合操作符号 union,union all,intersect,minus;

Ø Union 该操作符用于取的两个结果的并集->select * from tablename1 where (条件) union select * from tablename2 where (条件);

Ø Union all 该操作符赋予union 相似，但是不会消除重复行，不会进行排序；

Ø Intersect 该操作符用于取得两个结果的交集；

Ø Minus 该操作符用于取得两个结果的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据；

补充：

设置显示执行SQL语句所消耗的时间 -> set timing on;

疯狂复制法 -> insert into tablename(row1,row2,row3,……)select * from tablename;

用查询结果创建表

Ø (这种命令是一种快速的创建表方式) –> create tabletablename2(row1,row2,row3) as select row1,row2,row3 from tablename1;(说明在创建表的同事也将查询结果的数据导入新创建的表中)；

衡量一个程序员的标准

Ø 对 SQL 语句的使用；

Ø 对网络的了解；

Ø 对程序的优化；

16 JAVA操作ORACLE

一、 Java如何连接oracle

举例说明：写一个showEmp.java 分页显示emp表中的数据：

Emp表中的数据如下：

Ø Java 操作 oracle有两种操作方式

1. Jdbc-odbc桥连接

a) 通过ODBC数据源配置

b) //1.加载jdbc-odbc数据库驱动

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

//2.获取链接

Connection conn = DriverManage.getConnection(“jdbc:odbc:testOra”,”userName”,”password”); (说明：testOra 是配置数据源的实例名)；

//Statement 是 Java 执行数据库操作的一个重要方法，用于在已经建立数据库连接的基础上，向数据库发送要执行的SQL语句

Statement sm= conn.createStatement();

//获取执行SQL语句

ResultSet rs=sm.executeQuery("select * from emp");

2. Jdbc连接方式

//加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//获取链接

Connectionconn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.10.15:1521:TEST","testUser","testUser");

//Statement 是 Java 执行数据库操作的一个重要方法，用于在已经建立数据库连接的基础上，向数据库发送要执行的SQL语句

Statementsm=conn.createStatement();

//获取执行SQL语句

ResultSet rs=sm.executeQuery("select * from emp");

Ø 进行分页

<%@ page language="java" import="java.util.*,java.sql.*"pageEncoding="utf-8"%>

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

<!DOCTYPE HTML PUBLIC "-//W3C//DTDHTML 4.01 Transitional//EN">

<html>

<head>

<title>My JSP 'MyTestOracl.jsp' startingpage</title>

<!--

-->

</head>

<body>

<table>

//加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//获取链接

Connectionconn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.10.15:1521:TEST","testUser","testUser");

//Statement 是 Java 执行数据库操作的一个重要方法，用于在已经建立数据库连接的基础上，向数据库发送要执行的SQL语句

Statementsm=conn.createStatement();

//接受pageNow

int pageNow = 1;

String s_pageNow =(String)request.getParameter("pageNow");

if(s_pageNow != null)

{

pageNow =Integer.parseInt(s_pageNow);

}

//总页数

int pageCount = 0;

//每页显示的行数

int pageSize = 3;

//总行数

int rowSize = 0;

//获取执行SQL语句

ResultSet rs =sm.executeQuery("select count(*) from emp");

if(rs.next())

{

//获取总行数

rowSize =rs.getInt(1);

//计算总页数

/* if(rowSize%pageSize == 0)

{

pageCount =rowSize/pageSize;

}

else

{

pageCount =rowSize/pageSize+1;

}*/

pageCount = (rowSize%pageSize==0) ?rowSize/pageSize : rowSize/pageSize+1;

}

ResultSet rs1=sm.executeQuery("select * from (select a1.*,rownum rn from(select * from emp) a1where rownum<="+pageNow*pageSize+") where rn>="+((pageNow-1)*pageSize+1)+" ");

while(rs1.next())

{

out.println("<tr>");

out.println("<td>"+rs1.getString(2)+"</td>");

out.println("<td>"+rs1.getString(6)+"</td>");

out.println("</tr>");

}

for(int i=1; i<=pageCount; i++)

{

out.print("<ahref=MyTestOracl.jsp?pageNow="+i+">["+i+"]</a>");

}

</table>

</body>

</html>

17.子查询修改数据

Ø To_date 函数

使用to_date函数-> update tablename set rowdate = to_date('03-26-2013','mm-dd-yyyy')where (条件); (注，实际年月日要与年月日样式保持一致，如（’2013-03-23’,’yyyy-mm-dd’）)

Ø 使用子查询插入数据时，一条insert语句可以大量数据；

è insert into tablename1(row1,row2) select row1,row2from tablename2 where (条件);

Ø 使用子查询修改数据时，一条update语句可同时修多条语句

è Update tablename set (row1,row2,…)=(selectrow1,row2,…from [条件]) where [条件]

18.oracle中事务处理

Ø 什么是事务？

事务就是用于保证数据的一致性，它由一组相关的dml语句组成，改组的dml语句要么全部成功，要么全部失败;(dml语句就是数据操作语言，如查询，删除等等)；

Ø 事务和锁

当执行事务操作时（dml语句），oracle会在被操作的表上加锁，防止其他用户改变表的结构；

Ø 提交事务

当执行使用commit语句会提交事务，当执行了commit事务，会确认事务的变化，结束事务，删除保存点，释放锁。当使用commit提交事务后，其他会话可以看到事务确认变化的新数据；

Ø 保存点

保存点是事务中的一个点，用于取消部分事务，当事务结束时，会自动删除该事务所定义的所有保存点。

Ø 回滚事务

当执行rollback时，通过制定保存点可以回退到制定的点；（注：当事务执行commit提交了以后，不能再回滚了）。

Ø 事务的几个重要操作

1) 设置保存点

Savepoint a1；

2) 取消部分事务

Rollback to a1；

3) 取消全部事务

Rollback；

4) 提交事务

Commit；

Ø Java中如何使用事务

在java中操作数据时，为了保证数据库的一致性，比如转账操作；

import java.sql.*;

publicclass testOra2 {

//演示jdbc链接操作oracle

publicstaticvoid main(String[] args) {

Connection conn=null;

try

{

//1.加载jdbc驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//2. 获取链接

conn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.10.15:1521:TEST","testUser","testUser");

//下面操作和sql server一样

Statementsm=conn.createStatement();

//加载事务处理

conn.setAutoCommit(false);

sm.executeUpdate("update emp set sal=sal-100where ename='KING'");

//设置异常

int i=7/0;

sm.executeUpdate("update emp set sal=sal+100where ename='SCOTT'");

//提交事务处理

conn.commit();

//关闭资源

conn.close();

sm.close();

}

catch(Exception ex)

{

try

{

//事务回滚

conn.rollback();

}

catch (Exception e)

{

e.printStackTrace();

}

ex.printStackTrace();

}

Ø 只读事务

只读事务就是只允许执行查询操作，不允许执行其他任何dml操作的事务，使用只读事务可以确保用户只能取得某时间点的数据。

设置只读事务

Set transaction read only;

19 oracle -sql函数的使用（字符函数）

Lower(char)将字符串转化为小写格式；

Upper(char)将字符串转化为大写格式；

Length(char)返回字符串的长度；

Substr(char,m,n)（注：m表示截取字符串的起始位置，n表示截取的长度）去字符串的子串；

Ø 典型案例：

以首字母大写其余字母小写显示某一列 -> selectupper(substr(row,1,1))||lower(substr(row,2,length(row)-1)) from tablename;

Replace(char1,search_string,replace_string)替换，search_string表示需要被替换的字符串，replace_string表示替换的字符串；

Instr(char1,char2,[,n[,m]])取字串在字符串中的位置；

20． oracle -sql函数的使用（数学函数）

数学函数输入的参数和返回值的数据类型都是数字类型，数学函数包括cos，cosh，exp，ln，log，sin，sinh，sqrt，tan，tanh，acos，asin，atan，round，我们讲常用的：

Ø round(n,[m]) 该函数用于执行四舍五入，如果省略m则四舍五入到整数位，如果m为整数，则截取到小数点m位后，如果m为负数，则截取到小数点的前m位；

Ø trunc(n,[m]) 该函数用于截取数字，如果省略掉m，就截去小数点部分，如果m是正数就截取小数点m位后，如果m为负数就截取小数点的前m位；

Ø mod(n,m) 该函数取n模m ，类似于n除以m的余数，例：select mod(13,2) from dual;(注：dual是一个虚拟表)

Ø floor(n) 返回小于或等于n的最大整数；

Ø ceil(n) 返回大于或等于n的最大整数；

其他的数学函数：

Ø abs(n) 返回数值n的绝对值；

Ø acos(n) 返回n的反余弦值；

Ø asins(n) 返回n的反正弦值；

Ø atan(n) 返回 n的方正切值；

Ø exp(n) 返回e的n次幂；

Ø log(n,m) 返回对数值；

Ø power(n,m) 返回m的n次幂；

21． oracle -sql函数的使用（日期函数）

日期函数用于处理date类型的数据：

Ø sysdate该函数返回系统时间

è select sysdate from dual;

Ø add_months(row,months) 增加约数

è select add_months(row,10) from tablename;

Ø last_day(rowdate) 指定日期所在月份的最后一天：

è select last_day(rowdate) from tablename;

Ø To_date 函数

22． oracle -sql函数的使用（转换函数）

è select to_char(rowdate,’$99,999.99’) fromtablename;

è select to_char(sal,’yyyy-mm-dd hh24:mi:ss’)from tablename;

è select to_char(sal,’yyyy’) from tablename;

23． oracle -sql函数的使用（系统函数）

Ø sys_context

l terminal:当前会话客户所对应的终端标示符；

select sys_context(‘userenv’,’terminal’)from dual;

l language:返回当前操作的语言：

select sys_context(‘userenv’,’language’)from dual;

l db_name:当前数据库名称

select sys_context(‘userenv’,’db_name’)from dual;

l nls_date_format:当前会话客户所对应的日期格式：

select sys_context(‘userenv’,’nls_date_format’)from dual;

l session_user:当前会话客户所对应的数据库用户名：

select sys_context(‘userenv’,’session_user’)from dual;

l current_schema:当前会话客户所对应的默认方案名：

select sys_context(‘userenv’,’current_schema’) from dual;

l host:返回数据库所在的主机名：

select sys_context(‘userenv’,’host’) from dual;

24. oracle 编程技术基础简介

1．如何调用存储过程

① exec 过程名

② call 过程名

2. sqlplus是oracle公司提供的一个工具；pl/sql developer开发公司，是一个独立的产品，是用于pl/sql块的集成开发环境：

3. 简单分类：

块（编程）

函数

触发器

包

过程（存储过程）

4.编程规范

5.块结构示意图

块有三部分组成：定义部分，执行部分，列外处理部分；

如下所示：

declear

/* 定义部分——定义变量，常量，游标，例外，复杂数据类型*/

begin

/* 执行部分——执行pl/sql语句和sql语句*/

exception

/*例外处理部分——处理运行的各种错误*/

end；

Ø set serveroutput on --打开输出选项

Ø set serveroutput off –关闭输出选项

注：declear 定义部分可选，begin执行部分必选，exception例外处理部分可选

实例-1 只包含执行部分pl/sql

begin

dbms_output.put_line('HelloWord!');

end;

说明：dbms_ouput是oracle提供的包（类似java提供的开发包），该报包含一些过程；put_line就是dbms_ouput包中一个过程（这些过程类似java开发包中提供的一些函数）；

实例-2 包含定义部分和执行部分pl/sql块

实例表：

ame Type Nullable Default Comments

-------------------- -------- ------- --------

EMPNO NUMBER

ENAME VARCHAR2(10) Y

JOB VARCHAR2(9) Y

MGR VARCHAR2(9) Y

HIREDATEDATE Y

SAL NUMBER Y

COMM NUMBER Y

DEPTNO NUMBER Y

Declare --定义部分

v_namevarchar2(20);

v_salnumber(7,2);

begin --执行部分

selectename,sal into v_name,v_sal from emp where empno = &no;

dbms_output.put_line('姓名：'|| v_name||' 工资：'||v_sal);

end;

说明：&表示要接收从从控制台输入的变量；

实例-3 包含定义部分，执行部分和例外处理部分

为了避免pl/sql程序块出错，提高pl/sql的健壮性，应该对可能的出错进行处理，这个很有必要：

比如：实例-2中如果查找的员工不存在，应当做例外处理。有时出现异常，希望用另外的逻辑处理；

Declare --定义部分
v_name varchar2(20);
v_sal number(7,2);
begin --执行部分
select ename,sal intov_name,v_sal from emp where empno = &no;
dbms_output.put_line('姓名：'|| v_name||' 工资：'||v_sal);
exception --异常处理部分
when no_data_found then
dbms_output.put_line('对不起，输入条件不存在，请重新输入...!');
end;

25. oracle 编程技术—过程

n 过程

过程用于执行待定参数，当建立过程时，即可以指定输入（in）参数，也可以指定输出（out）参数；通过在过程中使用输入参数，可以将输入参数传递到执行部分;通过使用输出参数，可以将执行部分的数据传递到应用环境；

在sqlplus中可以使用create procedure命令来建立过程；

示例如下：

实例-4 可以考虑编写一个存储过程，输入员工名，新工资可以修改员工的工资：

create procedureemp_pro(sName varchar2,newSal number) is
begin
--执行部分，根据员工姓名，修改员工的工资
update emp set sal =newSal where ename = sName;
end;

实例-5 如何调用执行过程

① call emp_pro(‘KING’,10000); ② exec emp_pro(‘SCOTT’,12000);

Ø 如何在java中调用一个存储过程：

import java.sql.*;

publicclass TestProcedure {

/**

* @param args

publicstaticvoid main(String[] args)

{

try

{

//1.加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//2. 获取链接

Connectioncnn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.10.15:1521:TEST","testUser","testUser");

//3. 创建callablestatement

CallableStatementcs = cnn.prepareCall("{call emp_pro(?,?)}");

//4.给？赋值

cs.setString(1,"KING");

cs.setInt(2,4444444);

cs.execute();

//关闭

cs.close();

cnn.close();

}

catch(Exception ex)

{

ex.printStackTrace();

}

25. oracle 编程技术—函数

n 函数

用于返回特定数据，当建立函数时，在函数头部必须包含return子句，而在函数体内必须包含return语句返回的数据，我们可以通过create function 建立函数，实际案例：

查询员工的年薪：

SQL> createfunction sp_fun(spName varchar2) return

2 number is yearSal number(7,2);

3 begin

4 --执行部分

5 select sal*12+nvl(comm,0)*12 into yearSal from emp where ename = spName;

6 return yearSal;

7 end;

8 /

在sqlplus中调用函数

SQL>var vSal number;

SQL>call sp_fun('KING') into:vSal;

SQL>print vSal;

在java程序中调用函数：

package testfunction;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Types;

publicclass TestFunction

{

/**

* @param args

publicstaticvoid main(String[] args)

{

try

{

//1.加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//2. 获取链接

Connectioncnn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.10.15:1521:TEST","testUser","testUser");

//3. 创建callablestatement

CallableStatementcs = cnn.prepareCall("{?=call sp_fun(?) }");

cs.registerOutParameter(1, Types.INTEGER);//第一个占位为输出，类型是INTEGER

cs.setString(2, "SCOTT");

cs.execute(); //不能忘记execute()

System.out.println(cs.getInt(1));//打印输出结果，对应registerOutParameter

//关闭

cs.close();

cnn.close();

}

catch(Exception ex)

{

ex.printStackTrace();

}

25. oracle 编程技术—包

n 包

包在逻辑上组合过程和函数，他是由包规范和包体两部分组成；

① 我们可以用 create package 命令来创建包；

例如：

--创建包 sp_package
--声明一个过程 update_sal
--声明一个函数 annual_income
create or replace package sp_package is
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) return number;
end;

包规范只包含了过程和函数的说明，没有包含过程和函数的实现代码，包体用实现了包规范中的过程和函数

② 我们可以通过create package body 创建包体：

--创建包体
create or replace package body sp_package is
procedure update_sal(name varchar2,newsal number)
is
begin
update emp set sal = newsal where ename = name;
end;
function annual_income(name varchar2) return numberis
annual_salary number;
begin
select sal*12+nvl(comm,0) intoannual_salary from emp where ename = name;
return annual_salary;
end;
end;

③ 如何调用包的过程和函数名

当掉拥包的过程和函数时，在过程和函数名前加包名，若要访问其他的包，还要在包前加方案名；

如：call sp_package.update_sal('AA',1111111);

25. oracle 编程技术—定义并使用变量

n 标量—常用类型

标量定义的案例

① 定义一个变长字符串

V_name vachar2(10);

②定义一个小数

V_sal number(7,2);

③ 定义一个小数并给一个初始值5.4 “：=”是pl/sql的赋值符号；

C_sal number(7.2) := 5.4;

④ 定义一个日期类型的数据

V_hirdate date;

⑤ 定义一个布尔变量，不能为空，初始值为false；

V_valid Boolean not null defaultfalse;

⑥ 标量使用%type类型来定义变量，这样他会按照数据库的列来确定你定义的变量长度和宽度：

表示符表名.列名%type；

实例-1：输入工号显示员工的姓名，工资，个人所得税（个人所得税率为0.03）；

n
--输入工号显示员工的姓名，工资，个人所得税（个人所得税率为0.03）；
declare
c_tex_rate number(5,2) := 0.03;
v_name emp.ename%type;
v_sal emp.sal%type;
v_tex_sal number(12,2);
begin
select ename,sal into v_name,v_sal from emp whereempno = &no;
v_tex_sal := v_sal*0.03;
dbms_output.put_line('姓名:'||v_name);
dbms_output.put_line('工资:'||v_sal);
dbms_output.put_line('个税:'||v_tex_sal);
end;

n 复合变量

用于存放多个值得变量。主要包括这几种：

l Pl/sql记录

l Pl/sql表

l 嵌套表（略）

l Varray（略）

① 复合变量—Pl/sql记录

类似于高级语言的结构体，需要注意的是在引用pl/sql成员时，必须要加记录变量作为前缀（记录变量.记录前缀）；

实例—2

--输入工号显示员工的姓名，工作，工资；
declare
--定义一个pl/sql记录类型，类型包括五个数据变量v_name，v_job，v_sal
type emp_record_type is record(v_name emp.ename%type,v_jobemp.job%type,v_sal emp.sal%type);
--定义一个sp_record变量，变量类型为emp_record_type
sp_record emp_record_type;
begin
select ename,job,sal into sp_record from emp whereempno = &no;
dbms_output.put_line('姓名:'||sp_record.v_name);
dbms_output.put_line('工作:'||sp_record.v_job);
dbms_output.put_line('工资:'||sp_record.v_sal);
end;

② 复合变量—Pl/sql表

相当于高级数据语言的数组，但是需要注意的在高级数组中的下标不可以为负，但是在Pl/sql表中的下标可以为负，而且不受限制；

实例—2

--输入工号显示员工的姓名，工资，个人所得税（个人所得税率为0.03）；
declare
--定义一个pl/sql表类型emp_table_type，该类型用于存放emp.ename%type
--indexby binary_integer表示下标是整数
type emp_table_type is table of emp.ename%typeindex by binary_integer;
--定义一个sp_table变量，变量类型为emp_table_type
sp_table emp_table_type;
begin
select ename,job into sp_table(0),sp_table(1) from emp whereempno = 7788;
dbms_output.put_line('姓名:'||sp_table(0));
dbms_output.put_line('工作:'||sp_table(1));
end;

n 参照变量

参照变量是用于存储数值的指针变量，使用参照变量可以使应用程序共享相同的对象，而降低应用空间。

l 参照变量—ref coursor 游标变量

使用游标时，当定义游标时不需要指定相应select语句，但是当使用游标时需要指定select语句，这样游标就与一个select语句结合；

实例-3

请使用pl/sql编写一个块，可以输入部门号并显示该部门的所有员工姓名和他的工资；

declare
--定义游标类型sp_emp_cursor
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
--定义变量
v_name emp.ename%type;
v_sal emp.sal%type;
begin
--循环取出
open test_cursor for select ename,sal from emp wheredeptno = &deptno;
loop
fetch test_cursor into v_name,v_sal;
--判断test_cursor是不是指空
exit when test_cursor%notfound;
dbms_output.put_line('姓名'||v_name||' 工资:'||v_sal);
end loop;
end;

26. oracle 编程技术— pl/sql进阶控制结构

n 条件分支语句

Pl/sql提供了三个条件分支语句：

① 一重条件 If …then …….

② 二重条件分支If …then …….else

③ 多重条件分支If …then …….elsif…then……else…..

这里我们可以和java语句做一个比较

实例-1

可以输入一个员工的编号，如果该员工职位是PRESIDENT，他的工资就加1000，如果该员工的职位是MANGER，他的工资就加500，如果是其他职位员工，他的工资就加200.

--可以输入一个员工的编号，如果该员工职位是PRESIDENT，他的工资就加1000，
--如果该员工的职位是MANGER，他的工资就加500，如果是其他职位员工，他的工资就加200.
--声明一个存储过程
create or replace procedure sp_test_pro(epNo number)is
--声明定义
v_job emp.job%type;
--执行部分
begin
select job into v_job from emp where EMPNO = epNo;
--判断
if v_job = 'PRESIDENT' then
update emp t1 set t1.sal = t1.sal+1000 wheret1.EMPNO = epNo;
elsif v_job = 'MANGER' then
update emp t2 set t2.sal = t2.sal+500 wheret2.EMPNO = epNo;
else
update emp t3 set t3.sal = t3.sal+200 wheret3.EMPNO = epNo;
end if;
end;

n 循环语句

① 循环语句 –loop

这种循环是以loop开头，以end loop结尾，这种循环至少被循环一次；

实例-2

编写一个过程，可以输入用户名，并循环添加10个用户到users表中，用户编号从1开始增加；

--编写一个过程，可以输入用户名，并循环添加10个用户到users表中，用户编号从1开始增加
create or replace procedure sp_test_pro(v_name varchar2)is
v_id user1.id%type := 1;
begin
loop
insert into user1 values(v_id,v_name);
exit when v_id = 10;
v_id := v_id+1;
end loop;
end;

② 循环语句 –while

While循环体至少要循环一次，是以while…loop 开始，以endwhile结束

实例-3

编写一个过程，可以输入用户名，并循环添加10个用户到users表中，用户编号从11开始增加；

--编写一个过程，可以输入用户名，并循环添加10个用户到users表中，用户编号从11开始增加；
create or replace procedure sp_test_pro(v_name varchar2)is
v_id user1.id%type := 11;
begin
while v_id<=20
loop
insert into user1 values(v_id,v_name);
v_id := v_id+1;
end loop;
end;

③ 循环语句 –for （不常用【略】）

n 顺序控制语句

① 顺序控制语句—goto（可读性差，不建议使用【略】）

② 顺序控制语句—null

null语句不执行任何操作，并且直接将控制传递给下一个控制语句，使用null好处就是提高pl/sql的可读性；

实例-1

可以输入一个员工的编号，如果该员工职位是PRESIDENT，他的工资就加1000，如果该员工的职位是MANGER，他的工资就加500，如果是其他职位员工，他的工资不变；

--可以输入一个员工的编号，如果该员工职位是PRESIDENT，他的工资就加1000，
--如果该员工的职位是MANGER，他的工资就加500，如果是其他职位员工，他的工资不变.
--声明一个存储过程
create or replace procedure sp_test_pro(epNo number)is
--声明定义
v_job emp.job%type;
--执行部分
begin
select job into v_job from emp where EMPNO = epNo;
--判断
if v_job = 'PRESIDENT' then
update emp t1 set t1.sal = t1.sal+1000 wheret1.EMPNO = epNo;
elsif v_job = 'MANGER' then
update emp t2 set t2.sal = t2.sal+500 wheret2.EMPNO = epNo;
else
null;
end if;
end;

27. oracle 编程技术— pl/sql进阶编写分页过程

n 无返回值的存储过程

实例—1

现有一张表，表结构如下：bookId, bookName,bookPublish;请编写一个过程，想表中添加书的信息，并通过java添加；

--创建book表
create table book(bookId number,bookName varchar2(50),bookPubish varchar2(50));

--创建存储过程，存储book信息
--in代表输入参数
--out代表输出参数
create or replace procedure sp_book_pro(sp_bookId innumber,sp_bookName in varchar2,sp_bookPublish in varchar2)is
begin
insert into book values(sp_bookId,sp_bookName,sp_bookPublish);
end;

package com.hudewei.oracle.pro;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

publicclass TestNoReturn {

/**

* @param args

publicstaticvoid main(String[] args)

{

try

{

//加载数据库驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//获取数据库连接

Connection con =DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:Test","testUser","testUser");

//创建callableStatement

CallableStatementcs = con.prepareCall("{call sp_book_pro(?,?,?)}");

//给？赋值

cs.setInt(1,10);

cs.setString(2, "平凡人生");

cs.setString(3, "人民出版社");

//执行

cs.execute();

//关闭资源

cs.close();

con.close();

}

catch (Exception e)

{

// TODO Auto-generated catch block

e.printStackTrace();

}

n 有返回值的存储过程（非列表）

实例-2

编写一个存储过程，可以输入员工的编号，返回该员工的姓名，工作，工资；

--可以输入员工的编号，返回该员工的姓名，工作，工资；
create or replace procedure sp_return_pro(spNo innumber,spName out varchar2,spJob out varchar2,spSalout number) is
--执行
begin
select t.ename,t.job,t.sal into spName,spJob,spSal fromemp t where t.empno = spNo;
end;

package com.hudewei.oracle.pro;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

publicclass TestReturn {

publicstaticvoid main(String[] args)

{

try

{

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con =DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:Test","TestUser","TestUser");

CallableStatementcs = con.prepareCall("{call sp_return_pro(?,?,?,?)}");

//给第一个?赋值

cs.setInt(1,7839);

//給第二个？赋值

cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);

cs.registerOutParameter(3,oracle.jdbc.OracleTypes.VARCHAR);

cs.registerOutParameter(4,oracle.jdbc.OracleTypes.DOUBLE);

cs.execute();

//去除返回值，注意？的顺序

String name =cs.getString(2);

String job =cs.getString(3);

Double sal = cs.getDouble(4);

System.out.println("姓名："+name+" 工作："+job+" 工资："+sal);

//关闭资源

cs.close();

con.close();

}

catch (Exception e)

{

e.printStackTrace();

}

n 有返回值的存储过程（列表【结果集】）

实例—3

编写一个存储过称，出入部门编号，返回该部门的所有员工信息；

--编写一个存储过称，出入部门编号，返回该部门的所有员工信息；
--1.创建包,在该包中定义类型sp_test_cursor，是一个游标类型
create or replace package sp_package is
type sp_test_cursor is ref cursor;
end;

--2.创建存储过程
create or replace procedure sp_cursor_pro(spDeptNo innumber,sp_cursor out sp_package.sp_test_cursor) is
begin
open sp_cursor for select * from emp wheredeptNo = spDeptNo;
end;

package com.hudewei.oracle.pro;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

publicclass TestCursor

{

/**

* @param args

publicstaticvoid main(String[] args)

{

try

{

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con =DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:Test","TestUser","TestUser");

CallableStatementcs = con.prepareCall("{call sp_cursor_pro(?,?)}");

cs.setInt(1,20);

cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);

cs.execute();

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

while(rs.next())

{

System.out.println(" "+rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(6));

}

cs.close();

con.close();

}

catch (Exception e)

{

// TODO Auto-generated catch block

e.printStackTrace();

}

n 编写分页过程：

编写一个存储过程，要求可以输入当前表名，每页显示数，当前页数，返回总记录数，总页数和按照工资从高到低返回结果集；

--1.创建包,在该包中定义类型sp_test_cursor，是一个游标类型
create or replace package fenye_package is
type fenye_cursor is ref cursor;
end;

--2.编写一个存储过程，要求可以输入当前表名，每页显示数，当前页数，返回总记录数，总页数和按照工资从高到低返回结果集；
create or replace procedure fenye_pro(
sp_tableName in varchar2, --输入表名
sp_pageSize in number,--每页显示的记录数
sp_nowPge in varchar2,--当前页数
sp_countSize out number,--总记录数
sp_countPage out number,--总页数
sp_cursor out fenye_package.fenye_cursor) is
--定义部分
--定义sql语句字符串
v_sql varchar2(1000);
--定义两个变量
v_startSize number:=(sp_nowPge-1)*sp_pageSize+1;
v_endSize number:=sp_nowPge*sp_pageSize;
--执行部分
begin
v_sql:='select * from (select t1.* ,rownum rn from (select *from '||sp_tableName||' order by saldesc) t1 where rownum<='||v_endSize||') where rn>='||v_startSize;
--把游标和sql关联
open sp_cursor for v_sql;
--计算sp_countSize和sp_countPage
v_sql:='select count(*) from '||sp_tableName;
execute immediate v_sql into sp_countSize;
if mod(sp_countSize,sp_pageSize)=0 then
sp_countPage := sp_countSize/sp_pageSize;
else
sp_countPage := sp_countSize/sp_pageSize+1;
end if;
end;

package com.hudewei.oracle.pro;

import java.sql.*;

publicclass TestFenye

{

/**

* @param args

publicstaticvoid main(String[] args)

{

try

{

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con =DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:Test","testUser","testUser");

CallableStatementcs = con.prepareCall("{call fenye_pro(?,?,?,?,?,?)}");

cs.setString(1, "emp");

cs.setInt(2, 5);

cs.setInt(3, 3);

cs.registerOutParameter(4,oracle.jdbc.OracleTypes.NUMBER);

cs.registerOutParameter(5,oracle.jdbc.OracleTypes.NUMBER);

cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);

cs.execute();

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

System.out.println("总记录数："+cs.getInt(4));

System.out.println("总页数："+cs.getInt(5));

while(rs.next())

{

System.out.println(" "+rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(6));

}

} catch (Exception e)

{

// TODO Auto-generated catch block

e.printStackTrace();

}

27. oracle 编程技术— pl/sql进阶例外处理

n 例外传递

编写一个过程可以输入员工的编号，并显示员工的姓名，如果员工的编号不存在，怎样去处理？

declare
v_name emp.ename%type;
begin
--循环取出
select ename into v_name from emp where empno =&deptno;
dbms_output.put_line(v_name);
exception
when no_data_found then
dbms_output.put_line('雇员编号不存在！');
end;

n 处理预定义例外

预定义例外是由pl/sql所提供的系统例外，当pl/sql违反了oracle规定的例外时，则会隐含的触发一个内部例外；

介绍常见的预定义例外：

① case_not_found

在开发pl/sql块中的编写case语句时，如果when子句中没有包含必要的条件分支，就会触发case_not_found 例外；

create or replaceprocedure sp_test_pro(epNo number) is
--声明定义
v_job emp.job%type;
--执行部分
begin
select job into v_job from emp where EMPNO = epNo;
--判断
case
when v_job = 'PRESIDENT' then
update emp t1 set t1.sal = t1.sal+1000 wheret1.EMPNO = epNo;
when v_job = 'MANGER' then
update emp t2 set t2.sal = t2.sal+500 wheret2.EMPNO = epNo;
end case;
exception
when case_not_found then
dbms_output.put_line('case语句没有与相匹配');
end;
②预定义例外 cursor_already_open

当重新打开已经打开的游标，会隐含的触发例外cursor_already_open

③ 预定义类外 dup_val_on_index

在唯一索引对应的列上插入重复的数据时，会隐含的触发例外dup_val_on_index例外；

④

n 处理自定义的例外

--创建一个试图，显示员工的编号，姓名，和部门名
create or replace view myview as selectempno,ename,dname from emp t1,dept t2 where t1.deptno =t2.deptno;

--自定义举例，更新用户的工资，如果该用户编号不存在
create or replace procedure exce_pro(spNo number)is
--定义一个列外
myex exception;
begin
update emp set sal = sal+100 where empno =spNo;
--sql%notfound表示没有update
--raisemyex触发myex
if sql%notfound then
raise myex;
end if;
exception
when myex then
dbms_output.put_line('该用户编号不存在！');
end;