ORACLE数据的基本使用

ORACLE总体来说是一款非常优秀的数据库. MYSQL数据库出现后, 由于其免费的特性, 加之其基本能够满足中小公司的业务需求, ORACLE数据库的市场占有率略有下滑, 但是其支持强大的并发访问量等优秀特性, 即使面对高昂的付费价格, 很多公司在经济条件允许的情况下, 任然会选择ORACLE, 因此掌握基本的ORACLE操作语法, 成为IT开发人员的基本技能之一.

ORALCE数据库的基本操作技能

1. 数据库的基本操作
2. 视图
3. 索引
4. PL/SQL基本语法

一、ORACLE数据库的基本操作

ORACLE数据库的逻辑结构如图所示：

ORACLE数据库可以建立多个实例，在每个实例下可以创建多个表空间和多个用户，同时一个表空间又可以被多个用户使用。

1.有关用户的操作

创建表空间

create tablespace hzbank  #hzbank为表空间名称
datafile \'c:\\hzbank.dbf\'  #datafile指定表空间对应的数据文件
size 100m  #表空间的初始大小
autoextend on  #表空间开启自动增长
next 10m  #表空间不够时一次自动增长大小

创建用户

create user hzbankuser  #创建用户名
identified by hzbankuser  #设定用户密码
default tablespace hzbank  #为用户指定默认的表空间

给用户赋予操作权限

此操作需进入system用户下进行，然后执行以下语句 grant dba to hzbank，为用户赋予最大权限。

2.有关表的操作

创建表

create table user(
    id number (10),
    name varchar2(10),
    gender number(1) default 1,
);

删除表

drop table user;

表的修改

表的添加：alter table user add (列表1  数据类型  [default  默认值] ，...);
表的修改：alter table user modify(列名1  数据类型  [default 默认值]，...);
修改列名：alter table user rename 列名1  to  列名2；

向表中添加数据

insert into user(列名1，列名2，...) value (值1，值2，...);

修改表中数据

全局修改：update user set 列名1 = 值1，列名2 = 值2，...;
条件修改：update user set 列名1 = 值1，列名2 = 值2，... where 修改条件；

删除表中的数据

delete from user where 删除条件；

3.序列

ORACLE数据库不能像MYSQL数据拥有主键自增的功能，因此要想实现主键自增功能可通过序列实现

第一步创建序列：create sequence sequenceid;
第二步使用序列：sequenceid.nextval 取得序列的下一个内容，sequenceid.currval取得序列的当前内容；

4.函数

单行函数

upper(\'aaa\') 把小写的字符转换为大写的字符；
lower(\'AAA\') 把大写的字符转换为小写的字符；

数值函数

round(12.56,2)四舍五入函数，2为保留的小数点位数；

日期函数

sysdate-日期：为系统当前日期与指定日期之间的天数
months_between(sysdate,日期)：为系统当前日期与执行日期之间的月数

转换函数

to_char(日期，‘yyyy’)：将当前日期转换为年
to_char(日期，‘mm’)：将当前日期转化为月
to_char(日期，‘dd’)：将当前日期转换为日
to_char(日期，‘yyyy-mm-dd’)：将当前日期按照指定的格式转换为年月日
to_char(日期，‘fmyyyy-mm-dd’)：将当前日期按照执行的格式转换为年月日，fm作用为去掉前导零
to_date(\'1985-04-22\',\'yyyy-mm-dd\')：将当前字符串按照指定的格式解析成日期

通用函数

nvl(列名1，默认值)：如果查询时，列名1对应的字段值为null,则使用默认值
decode(列名/表达式，值1，‘我是1’，...)：根据表达式的值来展示对应的结果
case 
  when ‘值1 ’= ‘值1’ then
    \'我是值1\'
  when ‘值2’=‘值2’ then 
    ‘我是值2’
  else
    \'其他值\'
作用类似于decode

多行函数

count(name)：统计数量
min(id)：获取最小值
max(id)：获取最大值
avg(id)：获取平均值
sum(id)：获取工资总和

5.多表查询

内连接：select * from tab1, tab2 where tab1.id = tab2.id
左链接：select * from tab1, tab2 where tab1.id(+) = tab2.id
右连接：select * from tab1, tab2 where tab1=tab2.id(+)

6.分页查询

select * from (select rownum r, tab1.* from tab1)  where t.r >0 and t.r < 10

二、视图

针对一些特定的表，如果只允许特定的用户进行查询，而不允许对其进行修改，ORACLE提供了视图来实现

第一步创建只读视图：create or replace view view1 as select * from tab1 with read only
第二部将视图给特定的用户进行赋权：请自己查询参考资料

三、索引

为了提供ORACLE的搜索效率，ORACLE同时引入了索引的概念

单列索引：create index 索引名 on 表名（列名）
复合索引：create index 索引名 on 表名 （列名1，列名2）
针对什么情况下创建索引，以及索引在那些情况下会失效，请查看详细参考资料

四、PL/SQL基本语法

PL/SQL语法的基本格式 

declare 
    说明部分 
begin 
    语句序列 
exception 
    例外处理语句
end;

在程序的声明阶段可以对变量进行定义，变量的类型主要分为基本类型，引用变量和记录型变量
基本类型变量主要有char, varchar2, date, number, boolean,类型，语法如下：

var varchar2(15);
num number(9,2);

引用变量类型语法如下：

name user.name%type; //user为表名，name为该表的字段名

记录型变量为：

document user%rowtype;  //user为表名

1.IF语法

该语法的基本格式为：

if 条件 then 
      语句序列1；
    else if 
      语句序列2；
    end if；
例如
declare 
    mynum number := #  //从键盘中输入一个数
begin
    if mynum < 18 then 
      dbms_output.put_line(\'未成年人\');
    else if mynum < 40
      dbms_output.put_line(\'中年人\');
    else if mynum < 1000
      dbms_output.put_line(\'老年人\');
    else
      dbms_output.put_line(\'大哥你是神\');
    end if
end;

2.LOOP循环语句

语法1：
while step < num loop
  dbms_output.put_line(step);
  step := step + 1;
end loop;
示例：
declare 
  step number := 1;
begin
  while step <= 10 loop
    dbms_output.put_line(step);
    step := step + 1;
  end loop;
end;

语法2：
loop
  exit when step > num;
  dbms_output.put_line(step);
  step := step + 1;
end loop;
示例：
declare 
  step number := 1;
begin
  loop
    exit while step > 10 
    dbms_output.put_line(step);
    step := step + 1;
  end loop;
end;

语法3：
for i in 1..num loop
  语句序列；
end loop；
示例：
decare 
  step num := 1;
begin
  for step in 1 .. 10 loop;
    dbms_output.put_line(step);
  end loop;
end;

3.游标

ORACLE中提供了游标存储查询的多条记录，通过游标可以对存储结果进行遍历，使用游标的方式如下

游标的使用步骤：
第一步打开游标：open cursor1
第二步取一行游标值赋值到一个变量中：fetch cursor1 into row
第三步关闭游标：close cursor1
游标使用示例：
declare
  cursor pc is 
    select * from emp;
  pemp emp%rowtype;
begin
  open pc;  //打开游标
  loop
    fetch pc
      into pemp; //取出游标值赋值到变量中
    exit when pc%notfound;  //当无游标可取时，退出游标循环
    dbms_output.put_line(pemp.empno);
  end loop; //退出循环
  close pc; //关闭游标
end；

4.存储过程和存储函数

存储过程和存储函数是对需要完成特定功能的SQL语句的一种封装，经过编译后在数据库中存储，用户只需要通过存储过程或存储函数的名字完成调用即可，用户与数据库之间只需要传递参数，避免传递臃余SQL语句的信息量以及进行SQL语句预编译的过程，提高了程序的运行效率，许多大型数据库都采用了这种方式完成数据库的存储。

存储函数语法格式：
create or replace function empincome（eno in emp.empno%type） return number is
  psal emp.sal%type;
  pcomn emp.comm%type;
begin
  select t.sal, t.comn into psal, pcomn from emp t where t.empno = eno;
  return psal * 12 + nvl(pcomn,0);
end;
调用存储过程：
declare
  income number;
begin
  income = empincome(7369);
  dbms_output.put_line(income);
end;

存储过程语法格式：
create or reolace procedure empincomep(eno in emp.empno%type,income out number) is
    psal emp.sal%type;
    pcomm emp.comn%type;
begin
    select t.sal, t.comn into psal, pcomm from emp t where t.empno = eno;
    income := psal * 12 + nvl(pcomm,0);
end empincomep;
存储过程调用过程：
declare 
  income number;
begin
  empincomep(7369,income);
  dbms_output.put_line(income);
end;

其实存储过程完全可以通过in或者out的形式传入参数和获取参数返回值，因此存储函数的功能完全可有由存储函数来替代。

5.触发器

数据库触发器是一个与表相关联的、存储PL/SQL的程序，每当一个特定的数据库操作语句（insert,update,delete）在指定的表上发生时，ORALCE会自动的执行触发器中定义的语句序列。

定义语句级触发器：
create or replace trigger trigger1
  after insert on person
declare
  -- 声明变量
begin
  dbms_output.put_line(\'一个员工被插入了\')；
end trigger1;
该触发器在insert语句执行完成后执行调用

定义行级触发器：
create or replace trigger addsal4p
  before update of sal on myemp
  for each row
begin
  if :old.sal > :new.sal then
    raise_application_error(-20002,\'涨前的工资不能大于涨后的工资\')；
  end if
end;
该触发器在表myemp字段sql对应的值改变时，每条语句都会触发。

触发器的功能还是很强大的，可以使用触发器来实现各种各样的功能，例如负责安全性检查，做审计和跟踪数据库表的操作，数据的备份和同步；

在此只是对ORACLE数据库的基本操作进行了说明，如想针对某一部分进行更深入的了解，还请各位IT人士查询详细的参考资料，后续也会更新相关内容。