第十章：plsql编程基础(函数&包)

最新推荐文章于 2021-04-10 15:36:23 发布

残剑飞雪125

最新推荐文章于 2021-04-10 15:36:23 发布

阅读量427

点赞数

分类专栏： SQL&PLSQL入门文章标签： plsql 函数存储过程包

本文链接：https://blog.csdn.net/u012150392/article/details/46118415

版权

SQL&PLSQL入门专栏收录该内容

10 篇文章 0 订阅

订阅专栏

- -函数的创建和使用
- -函数像过程一样，也存储在数据库中。
- -两者之间的重要区别是，函数是能够返回单个值
- -的PL / SQL语句块。

- -创建函数的语法如下所示:
- - CREATE [ OR REPLACE] FUNCTION  function_name( parameter list)
   RETURN datatype
  { IS | AS }
     [ declare_section ]
   BEGIN
      <body >
      RETURN (return_value);
   END;

- -第一个函数
create or replace function show_description(i_course_no course.course_no% type)
return varchar2
is
  v_description varchar2( 30);
begin
   - -找到指定课程的名称，存到变量中
   select description
     into v_description
     from course
     where course_no =i_course_no;

   return v_description; - -返回课程名称
end show_description;

- -函数调用：
- - 1）在plsql块或者存储过程中调用
- -PLS - 00221: 'SHOW_DESCRIPTION' is not a procedure or is undefined
begin
  show_description( 430);
end;

- -在plsql中调用函数，要求一定要对函数的返回值做处理。
begin
  dbms_output.put_line(show_description( 430));
end;

- -或者：
declare
  v_description varchar2( 30);
begin
  v_description : = show_description( 430);

  dbms_output.put_line(v_description);
end;

- - 2)使用 select语句调用。有很多限制
select show_description( 430) from dual;

- -例 2：检查给定的学生编号是否合法
create or replace function id_is_good(i_student_id number)
return boolean
is
  v_id_cnt number( 1);
begin
   select count( *)
     into v_id_cnt
     from student
     where student_id =i_student_id;

   return ( 1 =v_id_cnt);
exception
   when others then
     return false;
end id_is_good;

- -使用 select语句调用以上函数
- -ORA - 06553: PLS - 382: expression is of wrong type
select id_is_good( 100) from dual;

- -因为函数的返回类型是 boolean型， sql不支持

begin
  if id_is_good( 100) then
    dbms_output.put_line( 'good');
   else
    dbms_output.put_line( 'bad');
   end if;
end;

- -创建和使用包。非常重要。
- -可以把包看做一个容器，里面存放过程、函数、变量、类型
- -等东西。使用多个包，就可以将过程和函数进行分类存放。

- -创建一个包分两个部分：
- - 1）包规范（包接口）：其中包含过程和函数的头部声明，
- -但是不包含他们的具体实现。包规范中的所有对象都叫做
- -公共对象，意味着他们可以被包之外的代码访问。
- - 2）包体：其中包含过程和函数的头部声明和具体实现。
- -也可以包含私有对象（过程、函数、变量等）的声明。

- -创建学生管理包
create or replace package manage_students is

   - - Author  : ADMINISTRATOR
   - - Created : 2014 - 06 - 30 9: 57: 43
   - - Purpose : 学生管理包

   procedure find_sname(i_student_id in number,
                       o_first_name out varchar2,
                       o_last_name   out varchar2);

   function id_is_good(i_student_id number) return boolean;
end manage_students;

- -当包规范创建并编译完毕后，客户端已经可以访问了。
declare
  o_first_name varchar2( 20);
  o_last_name varchar2( 20);
begin
  if manage_students.id_is_good( 102) then
    manage_students.find_sname( 102,o_first_name,o_last_name );
   end if;
end;

- -执行以上代码，会抛出异常：
ORA - 04067: not executed, package body "STUDENT.MANAGE_STUDENTS" does not exist
ORA - 06508: PL / SQL: could not find program unit being called: "STUDENT.MANAGE_STUDENTS"

- -这是正常的，因为过程和函数还没有具体实现，当然
- -不能执行。

- -实现包体
create or replace package body manage_students is
   - -过程实现
   procedure find_sname(i_student_id in number,
                       o_first_name out varchar2,
                       o_last_name   out varchar2) is
   begin
     select first_name, last_name
       into o_first_name, o_last_name
       from student
      where student_id = i_student_id;
   exception
     when no_data_found then
      DBMS_OUTPUT.PUT_LINE( 'Error in finding student_id: ' || i_student_id);
   end find_sname;

   - -函数实现
   function id_is_good(i_student_id number) return boolean is
    v_id_cnt number( 1);
   begin
     select count( *)
       into v_id_cnt
       from student
      where student_id = i_student_id;

     return( 1 = v_id_cnt);
   exception
     when others then
       return false;
   end id_is_good;
end manage_students;

- -再执行以下代码，成功
declare
  o_first_name varchar2( 20);
  o_last_name varchar2( 20);
begin
  if manage_students.id_is_good( 102) then
    manage_students.find_sname( 102,o_first_name,o_last_name );
   end if;
end;

- -创建私有对象
- -如果只在包体中定义某对象，则它是私有的。
- -该包外部的任何程序不能直接访问私有元素

- -在manage_students包规范定义中添加一个新的过程的声明（如下），并重新编译包规范
      PROCEDURE display_student_count;
END manage_students;

- -
create or replace package manage_students is

   - - Author  : ADMINISTRATOR
   - - Created : 2014 - 06 - 30 9: 57: 43
   - - Purpose : 学生管理包

   procedure find_sname(i_student_id in number,
                       o_first_name out varchar2,
                       o_last_name   out varchar2);

   function id_is_good(i_student_id number) return boolean;

   procedure display_student_count;

end manage_students;

- -包体：
create or replace package body manage_students is
   - -过程实现
   procedure find_sname(i_student_id in number,
                       o_first_name out varchar2,
                       o_last_name   out varchar2) is
   begin
     select first_name, last_name
       into o_first_name, o_last_name
       from student
      where student_id = i_student_id;
   exception
     when no_data_found then
      DBMS_OUTPUT.PUT_LINE( 'Error in finding student_id: ' || i_student_id);
   end find_sname;

   - -函数实现
   function id_is_good(i_student_id number) return boolean is
    v_id_cnt number( 1);
   begin
     select count( *)
       into v_id_cnt
       from student
      where student_id = i_student_id;

     return( 1 = v_id_cnt);
   exception
     when others then
       return false;
   end id_is_good;

   - -定义私有函数。该函数只能被本包中的
   - -其它过程或函数调用
   function student_count_priv
     return number
   is
    v_count number;
   begin
     select count( *)
       into v_count
       from student;

     return v_count;
   end student_count_priv;

   procedure display_student_count
    is
    begin
      - -调用私有函数
     dbms_output.put_line(student_count_priv);
    end display_student_count;

end manage_students;

- -测试
begin
  manage_students.display_student_count;
end;

- -PLS - 00302: component 'STUDENT_COUNT_PRIV' must be declared
begin
  dbms_output.put_line(manage_students.student_count_priv);
end;

- -使用包变量
- -将变量声明放在包规范中，那么该变量就是一个全局变量。
- -可以被任何代码来访问。可以实现
- -同一个会话的多个过程之间交换（共享）数据。

- -包的初始化部分
- -在包体的末尾。当一个用户会话第 1次调用一个包时，
- -该包的初始化部分的代码就会执行。只执行这一次

- -给manage_students包添加包变量
create or replace package manage_students is

   - - Author  : ADMINISTRATOR
   - - Created : 2014 - 06 - 30 9: 57: 43
   - - Purpose : 学生管理包

   - -包变量
  v_current_date date;

   procedure find_sname(i_student_id in number,
                       o_first_name out varchar2,
                       o_last_name   out varchar2);

   function id_is_good(i_student_id number) return boolean;

   procedure display_student_count;

end manage_students;

- -在包体的初始化部分给包变量赋值
create or replace package body manage_students is
   - -过程实现
   procedure find_sname(i_student_id in number,
                       o_first_name out varchar2,
                       o_last_name   out varchar2) is
   begin
     select first_name, last_name
       into o_first_name, o_last_name
       from student
      where student_id = i_student_id;
   exception
     when no_data_found then
      DBMS_OUTPUT.PUT_LINE( 'Error in finding student_id: ' || i_student_id);
   end find_sname;

   - -函数实现
   function id_is_good(i_student_id number) return boolean is
    v_id_cnt number( 1);
   begin
     select count( *)
       into v_id_cnt
       from student
      where student_id = i_student_id;

     return( 1 = v_id_cnt);
   exception
     when others then
       return false;
   end id_is_good;

   - -定义私有函数。该函数只能被本包中的
   - -其它过程或函数调用
   function student_count_priv
     return number
   is
    v_count number;
   begin
     select count( *)
       into v_count
       from student;

     return v_count;
   end student_count_priv;

   procedure display_student_count
    is
    begin
     dbms_output.put_line(student_count_priv);
    end display_student_count;

begin - -这是包的初始化部分，对每个会话只执行一次
   select sysdate
     into v_current_date
     from dual;
  dbms_output.put_line( '包初始化完毕');
end manage_students;

- -测试：新开会话窗口，分别执行以下代码 2次。观察输出。
begin
  dbms_output.put_line(manage_students.v_current_date);
end;

- -在包中使用游标变量。很重要
- -游标变量就是一个 ref cursor类型的变量。

- -定义 ref cursor类型的语法：
- - TYPE  ref_type_name   IS   REF   CURSOR
   [ RETURN 记录类型];

- -如果定义 ref cursor类型时带了 return子句，那么
- -该类型的游标变量就叫做强类型的游标变量。如果
- -没有带 return子句，那么该类型的游标变量就叫做
- -弱类型的游标变量

- -弱类型的游标变量可以指向任何一个 select查询的
- -结果集。而强类型的游标变量可以指向的查询，要求
- -其 select列表必须满足记录类型的内部结构。

- -游标变量在包中主要用作过程的输出参数，用来从
- -服务器向客户端返回一个结果集。

CREATE   OR  REPLACE  PACKAGE  course_pkg   AS
      TYPE  course_rec_typ   IS RECORD
         (first_name  student.first_name% TYPE,
           last_name  student.last_name% TYPE,
          course_no  course.course_no% TYPE,
          description  course.description% TYPE,
          section_no   section.section_no% TYPE
          );

       TYPE  course_cur   IS REF CURSOR   RETURN  course_rec_typ;

       PROCEDURE   get_course_list
           (p_student_id   NUMBER ,
            p_instructor_id   NUMBER ,
            course_list_cv   IN OUT  course_cur);   - -声明游标变量
END  course_pkg;

CREATE OR REPLACE PACKAGE BODY  course_pkg   AS
     PROCEDURE get_course_list
         (p_student_id   NUMBER ,
          p_instructor_id   NUMBER ,
          course_list_cv   IN OUT course_cur)
     IS
     BEGIN
         IF  p_student_id   IS NULL   AND p_instructor_id   IS NULL   THEN
               OPEN  course_list_cv   FOR    - -打开游标变量
                     SELECT 'Please choose a student-'  First_name,
                                   'instructor combination' Last_name,
                           NULL  course_no,
                           NULL  description,
                           NULL  section_no
                         FROM  dual;
         ELSIF  p_student_id    IS NULL   THEN
OPEN  course_list_cv   FOR    - -打开游标变量
             SELECT  s.first_name  first_name,
                           s.last_name  last_name,
                            c.course_no  course_no,
                            c.description  description,
                           se.section_no  section_no
               FROM  instructor i, student s,
                            section se, course c, enrollment e
             WHERE  i.instructor_id = p_instructor_id
                  AND  i.instructor_id = se.instructor_id
                  AND  se.course_no = c.course_no
                  AND  e.student_id = s.student_id
                  AND  e.section_id = se.section_id
        ORDER BY   c.course_no, se.section_no;
     ELSIF  p_instructor_id   IS NULL   THEN

OPEN  course_list_cv   FOR    - -打开游标变量
                 SELECT  i.first_name  first_name,
                               i.last_name  last_name,
                                c.course_no  course_no,
                                c.description  description,
                               se.section_no  section_no
                    FROM  instructor i, student s,
                                 section se, course c, enrollment e
                  WHERE  s.student_id = p_student_id
                       AND  i.instructor_id = se.instructor_id
                       AND  se.course_no = c.course_no
                       AND  e.student_id = s.student_id
                       AND  e.section_id = se.section_id
             ORDER BY   c.course_no, se.section_no;
        END IF;
    END  get_course_list;
END  course_pkg;

- -在sqlplus中使用游标变量调用过程
- -sqlplus中的 variable命令专门用来定义绑定变量，
- -其类型可以是游标变量。

variable course_cv refcursor;

- -注意，以上语句命令窗口不支持

- -调用过程
execute course_pkg.get_course_list( 102, null,:course_cv);

- -打印绑定变量的值。可以看到结果集输出
print :course_cv;

- -注意，print命令会自动关闭游标变量。再次执行，出错：

exec  course_pkg.get_course_list( NULL, 102, :course_cv);

print :course_cv;

- -如果过程的参数有一个弱类型的游标变量，那么
- -在包中可以不写 type ... is ref cursor语句，而是
- -直接使用系统预先定义的一个 ref cursor类型，其
- -名字是sys_refcursor.

残剑飞雪125

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
第十章：plsql编程基础(函数&包)

--函数的创建和使用--函数像过程一样，也存储在数据库中。--两者之间的重要区别是，函数是能够返回单个值--的PL/SQL语句块。--创建函数的语法如下所示: --CREATE [OR REPLACE]FUNCTION function_name(parameter list) RETURN datatype { IS | AS }
复制链接

扫一扫