这几天 预习了一遍 Oracle , 此处把 Oralce 数据库 中 视图 的笔记 拿出来 分享 一下， 该文中 有的地方 或许存在 点错误， 希望 看到的朋友 帮我指正出来，谢谢。
--=========================================================
函数 与 存储过程
Oracle 数据库中不仅可以使用 单条语句对数据库进行增、删、改、 查 操作，而且可以多条语句组成一个i额语句块， 并一起执行。
这些语句块可以进行显示命名， 并被其他应用调用。 这些命名的语句块 被称为 函数 与 存储过程。
Oracle 中的 自定义函数；
Oracle 中的 存储过程；
包装函数 与 存储过程—程序包。
-- 函数
-- 函数是 Oracle 中 的常用对象之一， 与 其他编程 语言 函数 一样， Oracle 中的函数也必须返回 一个值。 这也是函数 区别 于 存储过程的重要特征。
-- 函数简介：
1. 函数 与 功能的划分
2. 函数 的 参数
3. 函数 的 返回值
创建 函数
--1. 创建函数
--create or replace function get_hello_msg return varchar2 as
--begin
-- return 'hello world';
--end get_hello_msg;
--/
SQL> create or replace function get_hello_msg
return varchar2 as
begin
return 'hello world';
end get_hello_msg;
/
函数已创建。
--2. 在数据字典 中 查看函数的信息 select object_name, object_type, status from user_objects where lower(object_name)='get_hello_msg';
SQL> set linesize 180;
SQL> select object_name, object_type, status from user_objects where lower(object_name)='get_hello_msg';
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------------------------------------------------------------------------------------------------------------ --------------
GET_HELLO_MSG FUNCTION VALID
--3. 查看函数的返回值
--set serverout on;
--declare msg varchar2(20);
--begin
-- msg := get_hello_msg;
-- dbms_output_line(msg);
--end;
--/
SQL> set serverout on;
SQL> declare msg varchar2(20);
begin msg := get_hello_msg; dbms_output.put_line(msg); end;
/
hello world
PL/SQL 过程已成功完成。
--============================
函数中的 括号
其他标准语言中， 函数的括号都是必须的， 但是 函数 get-hello_msg 并没有 使用小括号。
当函数需要传入参数时， 参数列表必须使用小括号括起来， 但是当 函数没有参数时， 小括号可以省略。
当函数没有小括号时， 在形式上和变量相同， 那么有可能 会产生变量冲突。
--==========================
函数的参数
--在上一个 例子中 创建的 函数 get_hello_msg 是个 无参函数， 下面 演示一下 带参数的 创建和使用。
--create or replace
-- function get_tax(p_salary number)
-- return number as
-- begin
-- declare tax_salary number;
-- begin
-- tax_salary := p_salary - 2000;
-- if tax_salary<=0 then
-- return 0;
-- end if;
--
-- return tax_salary*5/100;
-- end;
-- end get_tax;
-- /
SQL> create or replace
function get_tax(p_salary number)
return number as
begin
declare tax_salary number;
begin
tax_salary := p_salary - 2000;
if tax_salary <=0 then return 0; end if;
return tax_salary * 5 / 100;
end;
end get_tax;
/
函数已创建。
--===============================
函数的 确定性：每次 调用 函数 ， Oracle总是 根据传入的参数 ， 执行相同 的步骤， 并返回 最终值。
--函数的确定性 是指： 传入的参数 一定， 无论函数被调用多少次， 都会返回相同的值。 例如， 对于 get_tax 函数， 每次输入相同的工资额， 那么返回值不会改变。
SQL> create or replace
function get_tax(p_salary number)
return number
deterministic as
begin
declare tax_salary number;
end get_tax;
/
警告: 创建的函数带有编译错误。
--
-- 尼玛 ， 这是什么情况， 擦的， 莫名其妙啊。 为什么 “警告: 创建的函数带有编译错误。”
--
对于具有 确定性 的函数， 在定义时， 可以使用 deterministic 选项， 已告知 Oracle 创建 确定性函数。
--=========================
典型函数举例
转换列 问题 是一个常见的问题， 即将 多行数据转换为 一列。
--例如： 在学生表中， 存储了很多学生资料， 现在欲获得 所有 学生的名称列表 ， 常见做法 是将 学生姓名 串联 起来 ， 即多行转 一行。
-- select get_student_string() from dual;
--=========================
存储过程
--存储过程（Stor Procedure） 对应 与 其他编程 语言中的过程。 存储过程 不必返回 值， 但是可以有 参数。
存储过程简介
1. 提高 数据库执行效率
2. 提高 安全性
3. 可复用
--==========
创建 存储过程
--1. 创建存储过程
create or replace procedure update_students
as
begin
update students set STUDENT_AGE = 10;
commit;
end update_students;
/
--2. 查看 存储过程在数据字典中的信息。
--select object_name, object_type, status from user_objects where lower(object_name) = 'update_students';
SQL> select object_name, object_type, status from user_objects where lower(object_name)='update_students';
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------------------------------------------------------------------------------------------------------------- --------------
UPDATE_STUDENTS PROCEDURE VALID
--
-- select * from user_source where lower(name) = 'update_students';
SQL> set linesize 300;
SQL> select * from user_source where lower(name)='update_students';
NAME TYPE LINE TEXT
------------------------------ ------------ ---------- ----------------------------------------------------------------------------------------------------------------------------------
UPDATE_STUDENTS PROCEDURE 4 update students set STUDENT_AGE = 10;
UPDATE_STUDENTS PROCEDURE 5 commit;
UPDATE_STUDENTS PROCEDURE 6 end update_students;
已选择3行。
--3. 执行存储过程
--execute update_students;
SQL> execute update_students;
PL/SQL 过程已成功完成。
--==========
存储过程的参数 IN 参数
IN 参数 ， 是指传入的参数， 即 只进不出的参数。 它由 调用者 传递给 存储过程之后， 存储过程在执行过程中 ， 无论怎样 使用 该参数， 都无法改变该参数的值。 该参数对于 存储过程来说， 是只读的。
--例如， 在更新学生信息的存储过程 update_students 中 ， 可以传入 一个年龄参数， 可以标识需要将 学生年龄修改为 多少岁。
--更新修改存储过程
--create or replace
--procedure update_students(in_age in number) as
-- begin
-- update sutdents set sutdent_age = in_age;
-- --- in_age := in_age + 10;
-- commit;
-- end update_students;
SQL> create or replace
procedure update_students(in_age in number) as
begin
update students set student_age = in_age;
--- in_age := in_age+10;
commit;
end update_students;
/
过程已创建。
--调用存储过程
--execute update_students(12);
SQL> execute update_students(15);
PL/SQL 过程已成功完成。
-- 查看执行后的 效果。
SQL> select * from students;
STUDENT_ID STUDENT_NAME STUDENT_AGE
---------- -------------------- -----------
1 xiaoMing 15
2 zhangSan 15
3 liSi 15
4 wangWu 15
5 erGouZi 15
6 wangErMaZi 15
7 testJ 15
已选择7行。
SQL>
--==========================
存储过程 OUT 参数
-- 函数 可与有 返回值， 存储过程 并没有 显示的返回值。 但是 可以通过 OUT 参数获得存储过程的处理结果。
在上面的例子汇总 ， 我们更新了 表 students 中 学生年龄， 可以通过 OUT 参数 返回 更新后的值， 以便 验证更新是否成功。
--create or replace
--procedure update_students(in_age in number, out_age out number) as
-- begin
-- update students set student_age = in_age;
-- select student_age into out_age from students where student_id = 1;
-- commit;
-- end update_students;
SQL> create or replace
2 procedure update_students(in_age in number, out_age out number) as
3 begin
4 update students set student_age = in_age;
5 select student_age into out_age from students where student_id = 1;
6 commit;
7 end update_students;
8 /
过程已创建。
--declare update_age number;
--begin
-- update_students(20,update_age);
-- dbms_output.put_line(update_age);
--end;
SQL> declare update_age number;
begin
update_students(20,update_age);
dbms_output.put_line(update_age);
end;
/
20
PL/SQL 过程已成功完成。
SQL> select * from students;
STUDENT_ID STUDENT_NAME STUDENT_AGE
---------- -------------------- -----------
1 xiaoMing 20
2 zhangSan 20
3 liSi 20
4 wangWu 20
5 erGouZi 20
6 wangErMaZi 20
7 testJ 20
已选择7行。
--==========================
存储过程的参数 IN OUT 参数
IN OUT 参数 既可以作为 输入参数 ， 也可以座位 输出 参数。 因此， IN OUT 参数 一般用于 对 参数的值的处理， 并处理结果输出。 一个典型实例就是 交换两个变量的值。
--create or replace procedure swap(in_out_param1 in out number, in_out_param2 in out number) as
--begin
-- declare param number;
-- begin
-- param := in_out_param1;
-- in_out_param1 := in_out_param2;
-- in_out_param2 := param;
-- end;
--end;
SQL> create or replace procedure swap(in_out_param1 in out number , in_out_param2 in out number) as
2 begin
3 declare param number;
4 begin
5 param := in_out_param1;
6 in_out_param1 := in_out_param2;
7 in_out_param2 := param;
8 end;
9 end;
10 /
过程已创建。
--declare
-- param1 number := 25;
-- param2 number := 35;
--begin
-- swap(param1,param2);
-- dbms_output.put_line('param1 = '|| param1);
-- dbms_output.put_line('param2 = '|| param2);
--end;
SQL> declare
2 param1 number :=25;
3 param2 number :=35;
4 begin
5 swap(param1,param2);
6 dbms_output.put_line('param1 = '|| param1);
7 dbms_output.put_line('param2 = '|| param2);
8 end;
9 /
param1 = 35
param2 = 25
PL/SQL 过程已成功完成。
--=====================
存储过程的参数 参数顺序
像其他编程语言一样， 存储过程的采纳数顺序 同样重要。
--在以上的例子中 ， 所有参数在调用时的值 都是按照顺序分配给存储过程。
--那么顺序就显得 格外重要， 如果顺序可以颠倒， 不仅得不到正确的结果， 而且有可能返回 不可预知的错误。
create or replace procedure update_students(in_age number, in_name in varchar2) as
begin
update students set sutdent_age = in_age where student_name = in_name;
commit;
end update_students;
名称表示法：
begin
update_students(in_name =>'张山', in_age =>20);
end;
--===================================
存储过程的参数， 参数的默认值 。
有时， 存储过程的参数 有很多个。 对于用户来说， 部分 参数并非 必须， 那么， 在定义存储过程时 应该为 可选参数 设定默认值， 以允许用户 不为 该参数传值。
--需要注意的是， 默认值是 进队 IN 参数 而言， OUT 和 IN OUT 参数 没有默认值。
--create or replace procedure insert_student(in_student_id in number, in_student_name in varchar2, in_student_age in number default 20) as
--begin
-- insert into students values(in_student_id, in_student_name, in_student_age);
-- commit;
--end insert_student;
--begin
-- insert_student(11,'军军');
--end;
SQL> create or replace procedure insert_student(in_student_id in number, in_student_name in varchar2, in_student_age in number default 20) as
2 begin
3 insert into students values(in_student_id, in_student_name, in_student_age);
4 commit;
5 end insert_student;
6 /
过程已创建。
SQL> begin
2 insert_student(11,'军军');
3 end;
4 /
PL/SQL 过程已成功完成。
--查看 学生表数据
SQL> select * from students;
STUDENT_ID STUDENT_NAME STUDENT_AGE
---------- -------------------- -----------
11 军军 20
1 xiaoMing 20
2 zhangSan 20
3 liSi 20
4 wangWu 20
5 erGouZi 20
6 wangErMaZi 20
7 testJ 20
已选择8行。
--=================================
存储过程 的参数 ： 参数顺序总结。
参数的顺序总结 如下：
-- 具有默认值 的参数 硬挨置于 参数列表的 末尾， 因为有时 用户需要 省略 该参数；
-- 没有默认值 的参数 可以遵循“IN 参数” ---> “OUT 参数” ----> “IN OUT 参数”
--==========================================
程序包
--程序包：
程序包 可以将 若干个 函数 或者 存储过程 组织起来， 作为 一个i额 对象进行 存储。
程序包 通常由 两个部分 构成： 规范（specification） 和 主体（body）。
程序包 也可以包含常量 和变量， 包中的所有 函数 和存储过程都能够使用这些变量 和常量。
规范：
--1. 创建程序包规范
-- create or replace package pkg_students as
-- studentString varchar2(500);
-- studentAge number := 18;
-- function get_student_string return varchar2;
-- procedure update_student(in_student_id in number);
-- procedure insert_student(in_student_id in number, in_student_name in varchar2, in_student_age in varchar2);
-- procedure delete_student(in_student_id in number);
-- end pkg_students;
SQL> create or replace package pkg_students as
2 studentString varchar2(500);
3 studentAge number := 18;
4 function get_student_string return varchar2;
5 procedure update_student(in_student_id in number);
6 procedure insert_student(in_student_id in number, in_student_name in varchar2, in_student_age in varchar2);
7 procedure delete_student(in_student_id in number);
8 end pkg_students;
9 /
程序包已创建。
--2. 在 数据字典中查看 程序包规范的信息
select object_name, object_type, status from user_objects where lower(OBJECT_NAME) = 'pkg_students';
SQL> select object_name, object_type, status from user_objects where lower(OBJECT_NAME) = 'pkg_students';
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------------------------------------------------------------------------------------- ------------------- -------
PKG_STUDENTS PACKAGE VALID
SQL>
--==================================
主体
--1. 创建程序包 主体
create or replace package body pkg_students as
end pkg_students;
范例：
--create or replace package body pkg_students as
--function get_student_string
-- return varchar2 is
-- begin
-- return 'students';
-- end get_student_string;
--end pkg_students;
SQL> create or replace package body pkg_students as
2 function get_student_string return varchar2 is
3 begin return 'students'; end get_student_string;
4 end pkg_students;
5 /
警告: 创建的包体带有编译错误。
--2. 在数据字典中查看改程序包主体的信息
--select object_name, object_type, status from user_objects where lower(OBJECT_NAME) = 'pkg_students';
SQL> select object_name, object_type, status from user_object where lower(OBJECT_NAME) = 'pkg_students';
select object_name, object_type, status from user_object where lower(OBJECT_NAME) = 'pkg_students'
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
--============================== 创建 程序包 ===========================================
SQL> conn scott/tiger
已连接。
SQL> create package emp_pkg is
2 procedure emp_update_ename(v_empno varchar2,v_ename varchar2);
3 function emp_get_sal(v_empno varchar2) return number;
4 end;
5 /
程序包已创建。
--======================== 创建程序 包 体 ===========================================
SQL> create or replace package body emp_pkg
2 is
3 procedure emp_update_ename
4 (
5 v_empno varchar2,
6 v_ename varchar2
7 )
8 is
9 vename varchar2(32);
10 begin
11 update emp set ename=v_ename where empno=v_empno;
12 commit;
13 select ename into vename from emp where empno=v_empno;
14
15 dbms_output.put_line('雇员名称：'||vename);
16
17 end;
18
19 function emp_get_sal
20 (
21 v_empno varchar2
22 )
23 return number is
24 vsal number(7,2);
25 begin
26 select sal into vsal from emp where empno=v_empno;
27 return vsal;
28 end;
29 end;
30
31 /
程序包体已创建。
SQL>
--============================== 在数据字典中查看该程序包主体的信息 =====================================================
SQL> select object_name, object_type, status from user_objects where lower(OBJECT_NAME) = 'emp_pkg';
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------------------------------------------------------------------------------------- -------------------
EMP_PKG PACKAGE VALID
EMP_PKG PACKAGE BODY VALID
--======================================================================================================================
--查询 Scott 用户下的 emp 表。
--select * from scott.emp;
--创建 程序包
/*
create package emp_pkg is
procedure emp_update_ename(v_empno varchar2,v_ename varchar2);
function emp_get_sal(v_empno varchar2) return number;
end;
程序包已创建。
*/
/* 创键程序包主体
create or replace package body emp_pkg
is
procedure emp_update_ename
(
v_empno varchar2,
v_ename varchar2
)
is
vename varchar2(32);
begin
update emp set ename=v_ename where empno=v_empno;
commit;
select ename into vename from emp where empno=v_empno;
dbms_output.put_line('雇员名称：'||vename);
end;
function emp_get_sal
(
v_empno varchar2
)
return number is
vsal number(7,2);
begin
select sal into vsal from emp where empno=v_empno;
return vsal;
end;
end;--程序包体 以创建
*/
--===============================调用 程序包中的 函数 / 存储过程 =========================================
调用 程序包中的 函数 / 存储过程
对于 程序包中的 函数 ， 可以 直接在 select 语句进行调用。 调用 格式为 package_name.function_name() 。
-- 以调用程序包 emp_pkg 中的函数 emp_pkg.emp_get_sal(empno varchar2)为例 ， 相应的代码如下 所示。
--select emp_pkg.emp_get_sal(7369) from dual;
SQL> select emp_pkg.emp_get_sal(7369) from dual;
EMP_PKG.EMP_GET_SAL(7369)
-------------------------
800
--===============================调用 程序包中的 的变量 =========================================
程序包中的 变量 一般 声明在 规范中， 而且 可以被 主体 中的 所有 函数 / 存储过程 共享。
存储过程的应用非常 广泛， 可以用来处理非常复杂的问题。 其中 比较常用的一种应用 为循环处理。
有时， 将一条语句可以处理的问题， 使用存储过程来解决， 反而是一种更好的策略。
-- 小结：
-- 以上 主要叙述了 数据库中的 两个重要的对象， 函数 与 存储过程。 二者 是 Oracle 与 其他编程语言 非常相近 的地方。
-- 函数 和存储过程 本质 是 复杂 的 SQL 语句 的组合， 在Oracle 编程 中 起了 至关重要的作用。 用户编写的代码可以存储并重用，
-- 这使得 Oralce 编程 具有编程语言 的基本特点。
-- 另外， Oracle 提供了许多 安全机制 便于控制函数 和存储过程的访问权限， 也使得 函数 和 存储过程 具有了 更加灵活的应用。
-- 程序包 和 主体 的概念， 则 体现了 Oracle 编程 照样 可以具有面向 接口编程的特点， 使得 Oralce 编程 更加 规范、可靠。
OVER!!!
630
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
