数据库基础知识

一.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


未连接数据库前访问数据

  • 异常传播机制:

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

  • SQL与异常规范:

总原则:

任何异常处理必须有Others分支

1.SELECT  INTO 语句

常见no_data_foundtoo_many_rows异常,也就是说要保证查询结果有且只有一条数据才不会触发入上异常。因此不能用 SQL%Rowcount来判断行数。

若where 条件中包括物理上的唯一索引,则可以不对too_many_rows进行处理。

另:无group by 子句的,SELECT Count(*)、sum、max等聚合函数 INTO 能保证有且仅有一条数据,因此可以不对too_many_rows和 no_data_found进行处理。

2.INSERT 语句:

若插入的表存在唯一索引或主键,则需要判断 dup_val_on_index 异常;

空值异常在 Others 里处理 (ORA-01400);

能用 SQL%Rowcount 来判断行数。

3.Update语句:

若被修改的列是唯一索引所在列,则需要判断 dup_val_on_index 异常;

空值异常在 Others 里处理 (ORA-01400);

能用 SQL%Rowcount 来判断行数。

4.DELETE 语句:

处理Other异常即可,无须其它异常处理

能用 SQL%Rowcount 来判断行数。

  • 异常嵌套:

为了程序结构清晰,应避免异常处理语句中出现异常嵌套;也就是说,异常处理中不应该再编写可能再次出现异常的语句如如:select、insert、delete、update等语句。可写成子程序或跳出此语句块后再处理。

一.10 临时表

不同于Sybase,临时表必须事先创建好。

临时表独立于每个session,也就是说,A连接不可能访问到B连接临时表中的数据。临时表有两种创建形式:

commit preserve rows,随着事务提交保存。数据一直存储,直到当前session断开。因此,在此种情况下,去维护临时表的表结构是行不通的。

commit delete rows,随着事务提交时清空。若只是用于中间计算,可用此种形式。

小技巧:临时表所有字段均为空,以便适用于不同场景。

一.11 哈希表

也称关联数组、索引表。是普通集合的扩展,可以用健值获得数组中的值。

优点,键值唯一,访问速度快。

  • 普通集合

Type t_array is table of varchar2(20)

L_array t_array := {‘John’, ‘Susan’};

Dbms_Output.Put_Line(l_Array(2)); -- 下标

  • 哈希表

Type t_array_hash is table of varchar2(20) index by varchar2(4);

L_array_hash t_array_hash;

L_array_hash(‘0001’) := ‘John’;

L_array_hash(‘0002’) := ‘Susan’;

Dbms_Output.Put_Line(l_Array_Hash(‘0001‘)); -- 键值

  • 单主键表转为一维哈希表

如有下表(userid varchar2(5) is primary key, userna varchar2(20), brchno varchar2(6) )

TYPE t_User_Rec IS RECORD( -- 先对主键外字段建立“记录型”用户自定义类型

userna VARCHAR2(20),

brchno VARCHAR2(6)

);

-- 在结构上建立哈希表

TYPE t_User_tab IS TABLE OF t_User_Rec INDEX BY VARCHAR2(5);

l_User_Rec t_User_Rec; --申明记录变量

l_User_tab t_User_tab; --申明表变量

l_Userna   varchar2(20); --普通变量

-- 哈希表插入记录

l_User_Rec.userna = ‘李大照’; l_User_Rec.brchno = ‘010201’;

l_User_tab(‘用户1’) := l_User_Rec; -- 记录1

l_User_Rec.userna = ‘王虎’; l_User_Rec.brchno = ‘019801’;

l_User_tab(‘00002’) := l_User_Rec; -- 记录2

-- 访问记录

l_Userna = l_User_tab(‘00002’).Userna;

  • 双主键表转为二维哈希表

如上表需增加维度行号(bankno varchar2(12) is primary key, userid varchar2(5) is primary key,…)

-- 需要增加的工作是对一维哈希表再建立一层哈希表

TYPE t_Bank_User_tab IS TABLE OF t_User_tab INDEX BY VARCHAR2(12);

l_User_Rec     t_User_Rec; --申明记录变量

l_bank_user    t_Bank_User_tab --申明二维表变量

l_Userna       varchar2(20);   --普通变量

-- 哈希表插入记录

l_User_Rec.userna = ‘李大照’; l_User_Rec.brchno = ‘010201’;

l_User_tab(‘武汉分行’)(‘用户1’) := l_User_Rec; -- 记录1

l_User_Rec.userna = ‘王虎’; l_User_Rec.brchno = ‘019801’;

l_User_tab(‘常洲分行’)(‘00002’) := l_User_Rec; -- 记录2

-- 访问记录

l_Userna = l_User_tab(‘常洲分行’)(‘00002’).Userna;

时间: 07-13

数据库基础知识的相关文章

MySQL数据库基础知识

day02 MySQL数据库基础知识 一.基础知识概述: 基础决定你这门课程的学习成败!只有学习好这些基础知识以后,你才能真正的运用自如.才能够对数据库有更深入的了解,道路才会越走越远. 二.基础知识: 1.数据库(database):数据库就好比是一个物理的文档柜,一个容器,把我们整理好的数据表等等归纳起来. 创建数据库命令:        create database 数据库名; 2.查看数据库         show databases; 3.打开指定的数据库         use 

SQL数据库基础知识-巩固篇<一>

SQL数据库基础知识-巩固篇<一>... 首先展示两款我个人很喜欢的数据库-专用于平时个人SQL技术的练习<特点:体积小,好安装和好卸载,功能完全够用了> MySQL-57 DataBase MS-SQLServer-2000 DataBase SQL的含义:结构化查询语言(Structured Query Language)简称SQL 作用:SQL(Structured Query Language,结构化查询语言)是一种用于操作数据库的语言. 结构化查询语言包含6个部分:一:数

oracle学习入门系列之二 数据库基础知识

oracle学习入门系列之二 数据库基础知识 本篇蛤蟆要梳理下那些被淡忘的数据库基础知识,也许根本就没被人记住过.不管是哪种情况,该记住的必须记住,记不住就把他记下来吧. 首先问几个问题如下: 数据库基础知识是什么? 好吧,蛤蟆直接吐后而不亡,看目录开始吧. 本人邮箱:[email protected] 微信公众号:HopToad 欢迎各界交流 1      基本概念 概念就是概念,大伙对这些名词不要死磕,但是对定义一定要理解,理解方能领悟,领悟方能运用自如后创新. 1.1      数据 数据

SQL Server之 (一) 数据库简介 SQL Server环境配置 数据库基础知识

   前言 这个是我工作两年多后,再次从最基础的SQL入门开始,认真的学一遍SQL Server,捡漏和巩固都有;因为自己刚开始学的时候,总是心烦气躁,最近换工作,发现1到2年经验,问到基础性的东西还是很多,这个时候需要的是扎实的基础功夫,所以一系列打击+反省后,自己节假日在家从最基础重新认识一下SQL Server,继续沉淀一下.哪里有不对或需深入探讨,请直接留言或者小窗我;欢迎~ (一) 数据库简介   SQL Server环境配置   数据库基础知识 1.什么是数据库,数据库有哪些特点,为

Oracle数据库基础知识

oracle数据库plsql developer 目录(?)[-] 一     SQL基础知识 创建删除数据库 创建删除修改表 添加修改删除列 oracle cascade用法 添加删除约束主键外键 创建删除索引 创建修改删除视图 二     SQL查询 基本的SQL语句 unionminusintersect 内连接外连接 子查询关联子查询 betweeninexists 复制表insert into selectselect into from 三     SQL查询优化 尽量少用 IN 操

数据库基础知识_

01. 前言介绍 前言知识介绍说明 基础知识配置

mysql数据库基础知识总结

这里把自己学的mysql数据库的知识总结一下,当是给自己复习一遍,也是方便以后查询 安装和配置mysql就不说了,可以借鉴这位博友的安装过程   https://www.cnblogs.com/by330326/p/5608290.html 一.mysql基本命令 1.创建命令 create user '用户名' @'ip' identified by '密码';  创建用户 ip是指用户登录mysql的电脑ip,可以写%,本地写localhost grant 权限(select/insert/

数据库基础知识(六)

1.用两种方式根据部门号从高到低,工资从低到高列出每个员工的信息. employee: eid,ename,salary,deptid; select * from employee order by deptiddesc,salary 2.列出各个部门中工资高于本部门的平均工资的员工数和部门号,并按部门号排序 创建表: mysql> create table employee921(idint primary key auto_increment,name varchar(5 0),salar

数据库基础知识:数据库中的约束和三大范式

一.数据库中的范式: 范式, 英文名称是 Normal Form,它是英国人 E.F.Codd(关系数据库的老祖宗)在上个世纪70年代提出关系数据库模型后总结出来的,范式是关系数据库理论的基础,也是我们在设计数据库结构过程中所要遵循的规则和指导方法,以下就是对这三个范式的基本介绍: 第一范式(1NF): 数据表中的每一列(字段),必须是不可拆分的最小单元,也就是确保每一列的原子性. 例如: userInfo: '山东省烟台市 1318162008' 依照第一范式必须拆分成