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>
<base href="<%=basePath%>">
<title>My JSP 'MyTestOracl.jsp' startingpage</title>
<meta http-equiv="pragma"content="no-cache">
<meta http-equiv="cache-control"content="no-cache">
<meta http-equiv="expires"content="0">
<meta http-equiv="keywords"content="keyword1,keyword2,keyword3">
<meta http-equiv="description"content="This is my page">
<!--
<link rel="stylesheet"type="text/css" href="styles.css">
-->
</head>
<body>
<table>
<tr><td>姓名</td><td>工资</td></tr>
<%
//加载驱动
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 函数
使用to_date函数-> update tablename set rowdate = to_date('03-26-2013','mm-dd-yyyy')where (条件); (注,实际年月日要与年月日样式保持一致,如(’2013-03-23’,’yyyy-mm-dd’))
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;
28. oracle 编程技术—视图
视图是一个虚拟表,其内容由查询定义,通真实表一样,试图包含一系列的列和行数据,视图并不在数据库中,以存储数据值的形式存在。
n 创建视图:
create view 视图名 as select 语句 [with read only]
实例-1
--创建一个试图,显示员工的编号,姓名,和部门名
create or replace view myview as selectempno,ename,dname
from emp t1,dept t2 where t1.deptno =t2.deptno;
--java调用视图
import java.sql.*;
publicclass testOracl {
//演示jdbc-odbc桥连接操作oracle
publicstaticvoid main(String[] args) {
try
{
//1.加载jdbc-odbc数据库驱动
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//2.获取链接
Connectionconn=DriverManager.getConnection("jdbc:odbc:testOra","testUser","testUser");
//下面的操作和sql server 操作完全一样
Statementsm=conn.createStatement();
ResultSetrs=sm.executeQuery("select * from myview");
while(rs.next())
{
System.out.println(" "+rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
}
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
n 创建或修改视图
create or replace view 视图名 as select 语句 [with read only]
n 删除视图
drop view 视图名
附录:
1 . emp表
2.dept表