数据库相关知识

一.1 数据库

 

与Sybase不同，一个用户就对应于一个数据库。

 

create user CBMAIN

 

identified by "sunline"   

 

default tablespace CBMAIN_DATA 　-- 表空间

 

temporary tablespace CBMAIN_TEMP; -- 临时表空间

 

一.2 表空间

表空间由一个或多个物理文件组成，一个表空间只能用于一个数据库。

 

create temporary tablespace CBMAIN_TEMP datafile

 

'D:\Oradata\CBMAIN_TEMP01.dbf' size 200M,

 

'D:\Oradata\CBMAIN_TEMP02.dbf' size 200M

 

一.3 登录身份

与Sybase不同，用户登录时需指定身份登录。 分为Normal、sysdba、sysoper

 

三种不同身份。各自对数据库拥有的权限不同，sysdba最高，normal 最低。如：

 

c:\> sqlpuls /nolog

 

sql>connect sys as sysdba

 

一.4 普通数据类型

Varchar2\Varchar：在建表语句中系统保留使用varchar，但建立的表结构实际为varchar2。PL\SQL中只允许使用varchar2。

 

Char：在建表语句中，字段内容长度固定可使用char,如状态字段。其它情况下不建议使用Char来建表。

 

Boolean：不能用于建表，只用于PL\SQL；

 

Date\Timestamp：日期时间型，可用于建表，也可用于PL\SQL语句编写。但在Ltts5.0表结构设计中不对其做广泛应用，日期字段用char(8)来存储。

 

Sys_refcursor：游标数据类型，采用此种类型定义的游标，在定义时无须编写SELECT子句，打开时采用Open Cur_var For Select c1,c2 from tab1的方式。

 

一.5 结构

类似于C中的结构体，常在包、过程、函数中定义。如：

 

TYPE t_Result IS RECORD(

 

      Instam NUMBER(18, 2) default 0.00,

 

      Intxst NUMBER(18, 2) default 0.00,

 

      Intxam NUMBER(18, 2) default 0.00,

 

      Sqltxt VARCHAR2(2000),

 

      Instrt NUMBER(11, 7)

 

      );

 

行类型（rowtype）是个特殊的结构体，select * into xxxx时即可into到行类型变量，也可into到要素雷同的结构体。

 

常用于返回结果（Oracle out 型的参数必须传入，便于扩展）。

 

一.6 包

包具有部分OO特征：封装性、多态性。

 

简单的理解为一个容器，可以将若干个过程或者函数组合成一个更大的单位。

 

包体编写过程体或函数体，包头申明过程说明或函数说明。只有在包头申明过的过程或函数才能被外部程序调用。

 

除过程和函数外，还可以申明包变量、常量、结构；其中包头定义可以被外部直接访问或修改（public）。包体部分定义的只能被包自身访问（priavte）。

 

包的生命周期：起始于此包的第一次调用，结束于当前session的断开。根据此特性，可用包头或包体的变量、常量、结构存储运算结果，甚至是系统级别的参数缓存。

 

不建议在包头申明变量；通常方法是在包体申明变量同时配以一组方法访问它。

 

通常在包头定义某个结构，用来在过程传递或返回一组信息。

 

常用系统包

UTL_FILE ----------处理外部文件

 

DBMS_OUTPUT ----调试PL/SQL，DEBUG

 

DBMS_SQL ----------执行动态SQL PL\SQL

 

dbms_job ------管理JOB

 

dbms_system -------跟踪用户，SESSION

 

dbms_transaction -------- 管理SQL事务

 

 

 

一.7 过程与函数

原则上，过程和包不允许独立编写，必须封装在包中。

 

与sybase不同, out 型的参数必须传入，不能缺省。

 

函数虽可定义out型变量，但不建议如此应用。当且仅当所实现只返回一个（含预估未来）基础数据类型（字符、数字、布尔、日期）的时候才将此功能申明为函数。其它情况下都申明为方法。

 

在包头有申明的过程在定义out型变量时，建议用一个结构体。目的是在未来扩展输出参数时，无须修改已修改的代码。

 

过程或函数定义基础类型参数时，varchar2, number 等即可。没必要使用用户自定义类型如：sys_type.u_acctno%type 或者kna_accs.acctno%type;

 

对于out 或in out 参数定义时 nocopy 关键字的意义：

 

不加nocopy传递的是复制（by value），否则传递的是地址（by reference）。

 

传递地址对大型结构或大数据时可以提高效率。

 

在出现异常时，不加nocopy的情况下，外层程序读到的仍然是原值。加nocopy的情况下，若有修改，外层程序读到的是修改之后的值。因此使用nocopy 时，需要确保在被调用过程中处理所有的异常。

 

一.8 事务

Sybase需要用Begin tran来控制是否需要手动commit或rollback; Oralce无此语句，但有一开关Set autocommit OFF/ON；将commit或rollback的权力交给用户。默认关闭，即由用户来控制事务的提交或回滚；也就没有事务积数器这一全局变量（@@transcount）。

 

DDL语句与事务

Alter … create …等都属于DDL语句。

 

由于Oracle执行DDL语句时会自动调用Commit,因此禁止在业务程序里使用DDL语句。

 

独立事务

PRAGMA AUTONOMOUS_TRANSACTION;

 

可在某个子过程或函数中单独申明一段事务，有关其涉及到的业务逻辑处理，由自身commit或rollback，不会影响到主程序已处理的事务。

 

注意：函数中若有事务语句，必须申明独立事务。即：若无特殊情况，涉及事务处理都不写成函数，都应写成过程。

 

相关应用：生成流水号、记录密码错误等。

 

一.9 异常

概念要点

Sybase由系统控制异常，Oracle则可主动捕捉异常。每当PL/SQL违背了ORACLE原则或超越了系统依赖的原则就会隐式的产生内部异常。

 

异常分为两类：分为系统异常和用户自定义异常。用户定义异常则需要显现的抛出。

 

抛出方式

 

异常类型

 

通过PL/SQL运行引擎

 

系统内部异常

 

使用RAISE语句

 

用户定义异常

 

调用RAISE_APPLICATION_ERROR存储过程

 

用户定义异常

 

异常处理是用来处理正常执行过程中未预料的事件。如果PL/SQL程序块一旦产生异常而又没有指出如何处理时，程序会自动终止。

 

系统内部异常分为：预定义的内部异常和未定义内部异常。未定义内部异常只能在Other部分捕捉。

 

常见预定义异常

 

sqlerrm

 

sqlcode

 

描述

 

no_data_found

 

ora-01403

 

+100

 

Select into无符合条件的记录

 

too_many_rows

 

ora-01422

 

-1422

 

Select into符合条件的记录有多条

 

dup_val_on_index

 

ora-00001

 

-1

 

违反唯一约束

 

value_error

 

ora-06502

 

-6502

 

发生算数、转换、截断或大小约束错误

 

storage_error

 

ora-06500

 

-6500

 

内存溢出

 

zero_divide

 

ora-01476

 

-1476

 

除数为零

 

case_not_found

 

ora-06592

 

-6530

 

无匹配when子句也无默认else子句

 

cursor_already_open

 

ora-06511

 

-6511

 

试图打开已经打开的游标

 

timeout_on_resource

 

ora-00051

 

-51

 

等待某一资源，超时

 

access_into_null

 

ora-06530

 

-6530

 

试图给未初始化对象的属性赋值

 

invalid_cursor

 

ora-01001

 

-1001

 

游标操作错误，如：关闭未打开

 

login_denied

 

ora-01017

 

-1017

 

登录时用户名或密码非法

 

program_error

 

ora-06501

 

-6501

 

PL/SQL内部错误

 

rowtype_mismatch

 

ora-06504

 

-6504

 

赋值变量与游标返回变量不兼容

 

self_is_null

 

ora-30625

 

-30625

 

 

 

sys_invalid_rowid

 

ora-01410

 

-1410

 

字符串转化成rowid失败

 

subscript_beyond_count

 

ora-06533

 

-6533

 

 

 

subscript_outside_limit

 

ora-06532

 

-6532

 

 

 

collection_is_null

 

ora-06531

 

-6531

 

 

 

invalid_number

 

ora-01722

 

-1722

 

字符串转化成数字失败

 

not_logged_on

 

ora-01012

 

-1012

 

未连接数据库前访问数据

 

 

 

异常传播机制：

当异常发生时，若本语句块未做异常处理，控制将转到或传播到外层语句块的异常处理部分。直到外层捕捉到异常，则运行捕捉异常后语句块；若外层块没有该异常的处理程序则传播到调用环境，并立即终止。