Oracle数据库系列(六)、Oracle数据库对象
Oracle数据库系列(六)、Oracle数据库对象
第六章、Oracle数据库对象
学习目标
使用函数实现数据处理
使用过程实现数据处理
使用包实现程序代码的组织
学会在Oracle中使用过程
学会在Oracle中使用函数
学会在Oracle中使用视图
学会在Oracle中使用序列
6.1、过程
创建存储过程练习1
-------------创建存储过程（根据性别查询员工信息）
create or replace procedure proc_emp_search_sex
(
---此处参数的数据类型要和查询的表中字段类型保持一致（切记不可带指定长度）
v_sex char
)
as
----声明部分
Cursor cur_emp is select * from emp where sex = v_sex;
begin
---for-loop-end loop
for c_emp in cur_emp loop
dbms_output.put_line(c_emp.ename||'---'||c_emp.sal||'---'||c_emp.sex);
end loop;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
调用存储过程1（也可以直接右键测试，建议自行调用，更清楚逻辑）
---------调用存储过程proc_emp_search_sex
declare
v_sex char(3);
begin
v_sex := '男';
proc_emp_search_sex(v_sex);
end;
1
2
3
4
5
6
7
创建存储过程练习2
------------创建存储过程(根据部门编号获取部门员工人数)
create or replace procedure proc_getEmpCount_byDeptno
(
v_deptno in number ,
--返回值接收参数(可以在声明处，也可以在定义处定义返回参数)
v_count out number
)
as
begin
select count(empno) into v_count from emp where deptno = v_deptno;
end;
------------调用存储过程proc_getEmpCount_byDeptno
declare
v_deptno number := 20;
v_count number;
begin
proc_getEmpCount_byDeptno(v_deptno,v_count);
dbms_output.put_line(v_count);
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
创建存储过程练习3
------------创建存储过程(根据员工编号，获取员工工资)
create or replace procedure proc_getEmpSal_byEmpno
(
v_empno in number,
v_sal out number
)
as
begin
select sal into v_sal from emp where empno = v_empno;
end;
-----------调用存储过程proc_getEmpSal_byEmpno
declare
v_empno number := 7369;
v_sal number;
begin
proc_getEmpSal_byEmpno(v_empno,v_sal);
dbms_output.put_line('员工编号为'||v_empno||'的工资是'||v_sal);
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
创建存储过程练习4
-----------创建存储过程proc_dept_insert
create or replace procedure proc_dept_insert
(
v_deptno in number, --输入参数
v_dname in varchar2, --输入参数
v_loc in varchar2, --输入参数
v_result out number, --输出参数(返回值)
v_msg out varchar2 --输出参数(返回信息)
)
is
begin
insert into dept values(v_deptno,v_dname,v_loc);
commit;--提交事务
v_result := 1;
v_msg := 'success';
--如果插入信息失败
Exception
when others then
v_result := 0;
v_msg := 'error';
end;
----------调用存储过程proc_dept_insert
declare
v_deptno number(2) := 88;
v_dname varchar2(20) := '测试5部';
v_loc varchar2(20) := '洛阳';
v_result number(1);
v_msg varchar2(20);
begin
proc_dept_insert(v_deptno,v_dname,v_loc,v_result,v_msg);
dbms_output.put_line(v_result||'---'||v_msg);
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
6.2、函数
创建自定义函数（通过部门号获取该部门员工人数）
create or replace function func_getEmpCount_ByDeptno
(
v_deptno in number --输入参数
)
return number --函数返回值类型
as
--声明参数
func_count number; --记录部门人数
begin
select count(empno) into func_count from emp where deptno = v_deptno;
return func_count;
end;
----------调用函数func_getEmpCount_ByDeptno
declare
f_count number;
v_deptno number := 20;
begin
f_count := func_getEmpCount_ByDeptno(v_deptno);
dbms_output.put_line('部门'||v_deptno||'有'||f_count||'人');
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
6.3、过程与函数的比较
6.4、Oracle中的包
6.4.1、创建包头（关键字package）
create or replace package pack_emp
as
--根据部门编号插叙部门人数
procedure proc_getdeptcount_bydeptno(v_deptno in number,v_count out number);
--根据员工编号查询员工工资
procedure proc_getempsal_byempno(v_empno in number,v_sal out number);
--自定义函数，根据部门编号插叙部门人数
function func_getempcount_bydeptno(v_deptno in number) return number;
end pack_emp;
1
2
3
4
5
6
7
8
9
10
11
12
13
6.4.2、创建包体（关键字package body）
create or replace package body pack_emp
as
--根据部门编号插叙部门人数
procedure proc_getdeptcount_bydeptno(v_deptno in number,v_count out number)
as
begin
select count(empno) into v_count from emp where deptno = v_deptno;
end;
--根据员工编号查询员工工资
procedure proc_getempsal_byempno(v_empno in number,v_sal out number)
as
begin
select sal into v_sal from emp where empno = v_empno;
end;
--自定义函数，根据部门编号插叙部门人数
function func_getempcount_bydeptno(v_deptno in number) return number
as
--声明
func_count number;
begin
select count(empno) into func_count from emp where deptno = v_deptno;
return func_count;
end;
end pack_emp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
6.2.3、调用包下的对象
declare
v_count1 number;
v_sal number;
v_count2 number;
begin
---存储过程proc_getdeptcount_bydeptno
pack_emp.proc_getdeptcount_bydeptno(20,v_count1);
dbms_output.put_line('存储过程查询---部门号为20的员工人数是'||v_count1);
--存储过程pack_emp.proc_getempsal_byempno
pack_emp.proc_getempsal_byempno(7369,v_sal);
dbms_output.put_line('存储过程查询---员工编号为7369的员工工资是'||v_sal);
--自定义函数pack_emp.func_getempcount_bydeptno
v_count2 := pack_emp.func_getempcount_bydeptno(20);
dbms_output.put_line('自定义函数查询---部门号为20的员工人数是'||v_count2);
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
6.5、视图
以scott用户为例，要想创建视图，必须要有dba权限。首先就是要system管理员为scott用户授予dba权限，然后才可以创建视图。
--授予scott用户dba权限（授予后仍然要在角色权限勾选可授予）
grant dba to scott;
1
2
也可以直接在角色权限中添加dba权限，并勾选可授予（推荐）
创建视图view_emp
--根据部门编号，查询员工信息、部门名称和地址，并以员工编号升序排序
create or replace view view_emp
as
select e.*,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno
order by empno
1
2
3
4
5
通过视图查询数据
--通过视图查询数据
select * from view_emp where dname = 'ACCOUNTING';
1
2
6.6、序列
创建序列
--创建序列
create sequence seq_empid
start with 1 --初始值
increment by 1 --增长间隔值
minvalue 1 --最小值
maxvalue 9999 --最大值
cache 20 --缓冲池（提高效率）
1
2
3
4
5
6
7
查询和修改序列
--查询下一个序列值
select seq_empid.nextval from dual;
--查询当前序列值
select seq_empid.currval from dual;
--修改增长间隔值
alter sequence seq_empid
increment by 2;
--再次查询下一个序列值（增长值为2）
select seq_empid.nextval from dual;
1
2
3
4
5
6
7
8
9
10
11
12
6.7、数据库链
数据库链的创建
create database link link_qq
connect to qq identified by "123456"
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME = ORCL))
)';
1
2
3
4
5
6
7
小贴士：
跨用户查询，此处远程用户的密码最好使用双引号引起来，因为11g数据库会默认把密码中的字符转化为大写，双引号的作用就是指定字符的大小写。
使用远程数据库完整路径创建数据库链
小贴士：
服务器路径可以是远程的，也可以是本地的。
调用数据库链
select * from userinfo@link_qq;
1
小贴士：
如果查询的远程用户的表中含有LOB字段，是不可以查询的，可以指定LOB外的字段查询，也可以创建临时表查询（自行了解）。
指定字段远程查询
select userid,username,userpwd,money from userinfo@link_qq;
————————————————
版权声明：本文为CSDN博主「一宿君」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_52596258/article/details/116024002