oracle创建数据库和使用,oracle数据库对象的创建和使用

数据库中有一worker表，以下对象的创建均是基于此表，内容如下：

SQL> select * from worker;

职工号码    姓名     性别     出生日期   党员否     参加工作        部门号

---------- -------- ---- ----------- ------ ----------- ----------

1              孙华     男     1952/3/1      否          1970/10/10         1

2              陈明     男     2045/8/1      否          1965/1/1             2

3              程西     女    1980/4/6       否           2002/10/7          3

一、游标

应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效处理。这些应用程序需要一种机制，以便每次处理一行或一部分行。游标就是用来提供这种机制的结果集扩展。

使用游标将worker表姓名列加上'_t'：

--声明游标

declare

cursor mycur is

select 姓名 from worker for update;

--声明一个v_text变量，用来存放游标结果集v_text worker.姓名%type;

begin

--打开游标open mycur;

--提取第一行数据保存在v_text

fetch mycur into v_text;

while mycur%found

loop

--在姓名后面加上'_t'

update worker set 姓名=姓名||'_t' where current of mycur;

fetch mycur into v_text;

end loop;

关闭游标close mycur;

end;

二、触发器

触发器是一种特殊的存储过程，它在指定的表中的数据发生变化时自动生效，即在INSERT、UPDATE、DELETE语句对表或视图进行修改时会被自动执行。

当对worker表中进行增删改时，将用户、日期和操作类型记录在一个tr_log表中：

CREATE OR REPLACE TRIGGER tr_1

AFTER INSERT OR DELETE OR UPDATE ON worker

begin

IF INSERTING THEN

INSERT INTO mylog VALUES(user,sysdate,’I’);

ELSIF DELETING THEN

INSERT INTO mylog VALUES(user,sysdate,’D’);

ELSE

INSERT INTO mylog VALUES(user,sysdate,’U’);

END IF;

END;

三、视图

视图是从一个或者多个表中使用select语句导出的，在数据库中，存储的是视图的定义，而不是视图查询的数据。通过这个定义，对视图的查询最终转换为对基表的查询。

使用视图查询部门1的员工信息：

create or replace view myview

as

select * from worker where 部门号=1;

四、存储过程

存储过程是sql语句和可选控制语句的预编译集合，它以一个名称存储并作为一个单元来处理。

输入职工号以查询其个人信息：

--in为输入类型，out为输出类型，%rowtype为万能行类型，不可指定参数大小

create or replace procedure pr_1(v_id in number,v_info1 out worker%rowtype)

is

begin

select * into v_info1 from worker where 职工号=v_id;

end;

--过程调用declare

v_info2 worker%rowtype;

begin

--执行，v_info2接收输出结果

pr_1(1,v_info2);

dbms_output.put_line(v_info2.职工号||' '||v_info2.姓名||' '||v_info2.性别||' '||v_info2.出生日期||' '||v_info2.党员否||' '||v_info2.参加工作||' '||v_info2.部门号);

end;

五、自定义函数

函数用于返回特定数据。执行时得找一个变量接收函数的返回值。

输入职工号以查询其姓名：

--声明类型时不用指定大小

create or replace function fun_1(v_name1 number)

--指定返回类型return varchar2

is

declare v_name2 varchar2(8);

begin

select 姓名 into v_name2 from worker where 职工号=v_name1;

return v_name2;

end;

--函数调用

--声明接收变量var v_name varchar2(8)

--执行exec :v_name:=fun_1(1)

六、包

包用于组合逻辑相关的过程和函数，它由包规范和包体两个部分组成。包规范用于定义公用的常量、变量、过程和函数，创建包规范可以使用CREATE PACKAGE命令，创建包体可以使用CREATE PACKAGE BODY

--创建包

create or replace package sp_package

is

begin

--函数声明部份

function fun_1(v_name1 number) return varchar2;--存储过程声明部份

procedure pr_1(v_id in number,v_info1 out worker%rowtype);

end;

--创建包体

create package body sp_package is

begin

--函数执行部份

function fun_1(v_name1 number)

return varchar2

is

v_name2 varchar2(8);

begin

select 姓名 into v_name2 from worker where 职工号=v_name1;

return v_name2;

end;

--存储过程执行部份

procedure pr_1(v_id in number,v_info1 out worker%rowtype)

is

begin

select * into v_info1 from worker where 职工号=v_id;

end;

end;

--调用包中函数

var v_name varchar2(8)

exec :v_name:=sp_package.fun_1(1)

--调用包中存储过程

declare

v_info2 worker%rowtype;

begin

sp_package.pr_1(1,v_info2);

dbms_output.put_line(v_info2.职工号||' '||v_info2.姓名||' '||v_info2.性别||' '||v_info2.出生日期||' '||v_info2.党员否||' '||v_info2.参加工作||' '||v_info2.部门号);

end;

七、序列

在oracle中sequence就是所谓的序列号，每次取的时候它会自动增加，一般用在需要按序列号排序的地方。

在work表中职工号字段编排规则从1开始，递增量为1，代码如下：

create sequence work_sequence

--递增量为1increment by 1

--从1开始start with 1

--不设最大值nomaxvalue

--不循环nocycle

--利用序列进行学号的自动插入

insert into work values(work_sequence.nextval,'李静','女');

八、同义词

Oracle的同义词(synonyms)从字面上理解就是别名的意思，和视图的功能类似，就是一种映射关系。

例如为worker表创建同义词：

create public synonym sy_worker for worker;

可以通过select * from sy_worker 语句进行查询。

public意为公共的，此类同义词所有用户均可访问，不加public则意为私有的，表对象所有者可以直接访问，但其它用户访问时须在表前加上所有者，无论是否加上public，非表对象所有者访问此表，前提是须有select此表的权限。