存储过程

传智播客——oracle数据字典和数据库约束和存储过程(2010-01-02 12:16:01)

标签：oracle索引 oracle存储过程 oracle序列化 视图 索引 top—n语句 jdbc it  分类：java技术

思路：

      a.用户表和数据字典表

         用户表：被用户创建和维护,包含用户的信息

         数据字典： dictionary （包括表明和注释） dba_tab_coumns,dba_constraints,dba_tables,dba_indexes  //对象信息   dba_segments,dba_extents //空间分配信息    dba_tablespaces,dba_data_files //数据库结构

        b. 数据约束：

              1、NOT NULL  非空           //唯一一个只能在列级定义的约束!!! 可以在外部将列修改为非空 alter       table          tableName          modify       列名               not null

               2、UNIQUE Key        唯一键       //可以包含一个或多个空值  每个唯一约束对应一个唯一索引

               3、PRIMARY KEY    主键           //相当于非空且唯一约束,也对应一个唯一索引,一个表只能有一个主键.联合主键的应用:每个学生可以有多个课程成

              4、FOREIGN KEY     外键           //只能为主键值或空

              5、CHECK                  检察

 

          c.索引，视图，top-n语句，序列  //相当于存储过程

          d.存储过程的使用

          e.jdbc连接类图

 

修改表中的语句：

a.        向表中插入列   alter  table   tableName   add  列名(varhchar2) default(‘p’)  //列名和数据类型

b.        修改列         alter  table  tableName  modify  列名 varchar2(45)

c.        删除列               alter           table 表名 drop column 列名

d.        删除表               ALTER TABLE employee //ddl语句不可回滚,释放存储空间     DROP COLUMN sex; //不能回滚,所有数据和结构和索引都将被删除

e.        修改表名            rename 原表名 to  新表名

 

Oracle数据库的表:用户表和数据字典表

用户表:

被用户创建和维护,包含用户的信息

 

数据字典表   相当于mssql中的内置表,存储各种特殊信息

被Oracle数据库创建和维护的一些表

包括了数据库的信息

 

是每个Oracle数据库的核心

用于描述数据库和它的所有对象

包括了只读的表和视图

被 SYS用户拥有

被 Oracle server维护

用户可以使用 SELECT访问

 

数据约束:

Oracle使用SYS_Cn格式命名约束，也可以由用户命名

创建约束的时机

1、在建表的同时创建

2、建表后创建

 a、约束条件可定义在列级或表级，相应的语法略有区别。

  b、约束条件可以构建在单列或多列的组合上,如果约束条件构建在多列组合上，那么定义约束条件只能在表级定义

 

常用约束：

       1、NOT NULL  非空           //唯一一个只能在列级定义的约束!!! 可以在外部将列修改为非空  alter       table          tableName          modify         列名           not null

2、UNIQUE Key        唯一键       //可以包含一个或多个空值  每个唯一约束对应一个唯一索引

3、PRIMARY KEY    主键           //相当于非空且唯一约束,也对应一个唯一索引,一个表只能有一个主键.联合主键的应用:每个学生可以有多个课程成

4、FOREIGN KEY     外键           //只能为主键值或空

5、CHECK                  检察

 

        约束名：SYS_C[number]  

        约束类型：（unique,primary key,foreign key,check）

 

 

在内部表级定义的格式:

表（

constraint 约束名（自定） 约束类型（约束的字段名）

）

在外部定义的格式:

alter  table 表名 add constraint 约束名（自定）约束类型(约束字段一，约束字段二)

 

定义组合约束：

alter  table 表名 add constraint 约束名（自定）约束类型(约束字段一，约束字段二)

定义外键约束：

表（

constraint 约束名（自定） foreign      key（约束的字段名） references   表名（列名）

)

定义检查约束：

表(

         constain     约束名 check（值>600）

)

 

 

删除约束

alter            table  table_name  drop  constraint  约束名

删除主键约束及相关的外键约束

alter  table  表名  drop  primary  key  cascade;

 

 

索引 自定维护，提高查询效率

创建索引格式：

         create        index 索引名 on  表名 (列名1，列名2);

删除索引:

         drop  index 索引名

 

 

视图

创建视图：

         Create  view   视图名   as  select * from user

查询视图：

         Selectd * from 视图名

查询视图结构：

         Desc 视图名     describe  视图名

创建一个复杂的视图   多表查寻

Create        or   replace   view   视图名

As       select  ……

插入数据(一般不用)

Insert        into   视图名    values(1,2,3)

禁止对视图进行操作

Create   or  replace    view  视图名  as   select ……

with  read  only     //视图设置为只读

删除视图：

Drop  view 视图名

 

 

TOP—N分析

用Top-N分析法可以得到某列上最什么的前n个值. 例如: 薪水最高的前5个员工

1、使用了rownum这个伪列，这个伪列将会返回行号，可以作为返回记录的序列号显示。

2、在from后面使用了子查询，这是标准的行内视图的使用。

3、在子查询中使用了order by进行排序，在前面的子查询中不需要使用。

4、在主查询中通过where条件中的rownum伪列定义过滤条件，只返回最什么的前几行数据。用between and不能查询到数据，因此可以考虑将rownum变为表中的列，在进行查询

 

同义词

创建同义词

         Create synonym  b  for   词名

查询：

         Select * from  词名

删除：

         Drop   synonym   b

 

序列   //相当于自增长列

a.是一数据库对象，利用它可生成唯一的整数。b.一般使用序列自动地生成表主键值或唯一键值

c.不直接连接到数据库中的任何表

语法：

CREATE SEQUENCE [user.]sequence_name
[increment by n]
[start with n]
[maxvalue n|]
[minvalue n ];
[cache]

  [NOCYCLE|CYCLE]

     INCREMENT BY： 指定序列号之间的间隔，该值可为正的或负的整数，但不可为0。序列为升序。忽略该子句时，缺省值为1。

     START WITH：指定生成的第一个序列号。在升序时，序列可从比最小值大的值开始，缺省值为序列的最小值。对于降序，序列可由比最大值小的值开始，缺省值为序列的最大值。

     MAXVALUE：指定序列可生成的最大值。

     MINVALUE：指定序列的最小值
NOCYCLE：一直累加，不循环

     CYCLE：累加到最大值循环

--currval :返回序列的当前值

--nextval 返回序列首次引用时的起始值，以后使用nextval的引用将使 

Select  序列名.currval  from  dual

Select  序列名.nextVal  from  dual

 

创建一个序列

Create  sequence  序列名

increment  by  1     //步长

start  with  1               //开始值

maxvalue   21               //最大值

minValue   1                 //最小值

cycle(oncycle)              //循环 不循环

cache  20                       //设置缓冲值，最少为20，避免多次操作硬盘，提高速度

 

 

修改一个序列

alter sequence  emp_seq
increment by 2
minvalue 3
maxvalue 99999

 

存储过程：用于完成特定操作或者任务procedures

创建一个带返回值的存储过程

   --修改dept的表

create or replace procedure updatedept

(

    v_deptno  in  number,

    v_dname   in   varchar2,

    v_loc    in     varchar2,

    v_demo   out    varchar2

)

is

begin

  update dept set dname=v_dname,loc=v_loc where deptno=v_deptno;

  commit;

  v_demo:='success';

end;

 

 

调用存储过程

declare

  v_demo varchar2(34);

begin

  updatedept(13,'789','678',v_demo);

  dbms_output.put_line(v_out);

end

 

oracle总结:

 1 select

     * 简单的查询(distinct,is not null,where,like,in,between and)

     * order by排序 asc desc

     * oracle中的函数(数学函数,字符函数,日前函数,转换函数*,其他函数)--oracle的函数

     * oralce中的聚合函数(avg count max min sum)

     * where group by having的执行流程

    

     * 多表查询

       * oracle8i

            * 等值连接

            * 非等值连接

            * 左外连接 右外连接

            * 自连接

       * oracle9i及以后的版本

            * 自然连接

            * 内连接 左外连接 右外连接  全外连接

            * 子查询

 2 dml

   * insert update delete merge

 3 事务

     * 什么事事务

     * commit

     * rollback

 4 DDl

   * 创建（修改  删除）表 视图  同义词 序列

   * 约束(非空唯一  主键  外键  检查)

   * Top n分析方法

 5 存储过程

  

 

 

Jdbc连接

 

Jdbc  API 的接口

 

DriverManager：

Connection——》Statement——》ResultSet

Connection——》PreparedStatement——》ResultSet

Connection——》CallableStatement——》ResultSet