ORACLE 学习总结(未修订)

本文深入解析了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值?函数 nvlrow1,row2,如果row1null 则就用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)

 

数据分组总结:

分组函数只能出现在选择列表,havingorder by子句中;

如果select子句中同时出现 order byhavinggroup 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

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

Ø  进行分页,如显示从510行的数据:

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

显示510行一下的数据->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函数的使用(数学函数)

数学函数输入的参数和返回值的数据类型都是数字类型,数学函数包括coscoshexplnlogsinsinhsqrttantanhacosasinatanround,我们讲常用的:

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

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

Ø  mod(n,m) 该函数取nm ,类似于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) 返回en次幂;

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

Ø  power(n,m) 返回mn次幂;

 

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

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

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

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

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

db_name:当前数据库名称

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

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

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

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

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

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

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

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

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

 

 

 

 

 

 

24. oracle 编程技术基础简介

1.如何调用存储过程

exec 过程名

call 过程名

2. sqlplusoracle公司提供的一个工具;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);


--输入工号 显示员工的姓名,工资,个人所得税(个人所得税率为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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值