子程序和程序包(what why how)

what:

•命名的 PL/SQL 块，编译并存储在数据库中。

•子程序的各个部分：

–声明部分

–可执行部分

–异常处理部分(可选)

•子程序的分类：

–过程 － 执行某些操作

–函数 － 执行操作并返回值

why

子程序的优点：

•模块化

–将程序分解为逻辑模块

•可重用性

–可以被任意数目的程序调用

•可维护性

–简化维护操作

•安全性

–通过设置权限，使数据更安全

how

创建过程

--创建存储过程，根据员工编号输出员工信息 create or replace procedure find_emp_no(emp_no in employees.employee_id%type) is emp_row employees%rowtype; begin select e.* into emp_row from employees e where e.employee_id=emp_no; dbms_output.put_line('编号：'||emp_row.employee_id||'，薪资：'||emp_row.salary); end;

调用过程的方式

--调用存储过程 --方式一:使用pl/sql程序调用 declare

begin --过程名称和参数调用 find_emp_no(&员工编号); end; --方式二：在命令行窗口execute find_emp_no(100)，如果控制台不输出结果，执行set serveroutput on

课堂练习

练习1

--创建存储过程，根据员工编号获取薪资,参数模式in默认的用来传入，out用来传出去给调用过程者使用 create or replace procedure get_salary_empno(empno in employees.employee_id%type,salary out employees.salary%type) is begin select e.salary into salary from employees e where e.employee_id = empno; end;

--调用 返回多个值就用存储过程 declare salary employees.salary%type; begin dbms_output.put_line('工资'||salary); --过程名称和参数调用 get_salary_empno(&员工编号,salary); dbms_output.put_line('工资'||salary); end;

练习2

--创建一个过程，，实现两个数交换功能

create or replace procedure swap(a in out number , b in out number) is temp number; begin temp := a; a := b; b :=temp; end;

过程的调用:

declare a number :=&a; b number :=&b; begin dbms_output.put_line('a='||a||'b='||b); swap(a,b); dbms_output.put_line(',a='||a||',b='||b);

end;

创建函数

--创建函数，根据员工编号返回工资 create or replace function get_salary_by_emp_no(empno employees.employee_id%type)return employees.salary%type

is salary employees.salary%type; begin select e.salary into salary from employees e where e.employee_id=empno; return salary;--return返回一个值，类型必须声明是一致

end;

函数的调用:

--函数调用 --方式一:ql/sql调用 declare salary employees.salary%type; begin salary := get_salary_by_emp_no(&员工编号); dbms_output.put_line('工资'||salary); end;

--方式二：使用dual伪表输出 select get_salary_by_emp_no(101) from dual;

课堂练习

课堂练习1

--创建函数，实现根据部门编号返回总人数get_total_by_dept_no create or replace function get_total_by_dept_no(emp_dmp employees.department_id%type)return number is num_total number; begin select count(*) into num_total from employees e where e.department_id = emp_dmp; return num_total; end;

--函数调用

select get_total_by_dept_no(90) from dual; select * from employees e where e.department_id=100;

课堂练习2

我写的

--从一个数到另一个的之和 --创建函数，计算a数到整数b之间的累计和 create or replace function sum_num(a in number,b in number )return number is temp number; begin if (a-b)/2=0 then temp := (a+b)* (b-a)/2; else temp := (a+b)*(b-a)/2+(a+b)/2; end if; return temp; end;

--函数的调用

select sum_num(2,2) from dual;

declare num_1 number; num_2 number; num_3 number; begin salary := sum_num(&员工编号); dbms_output.put_line('工资'||salary); end;

老师写的:

--创建函数，计算a数到整数b之间的累计和 create or replace function total_sum(a number ,b number) return number is total number :=0;

begin for i in a..b loop total :=total + i; end loop; return total; end;

--函数的调用:

select total_sum(2,2) from dual;

--老师改进后：

--创建函数，计算a数到整数b之间的累计和

create or replace function total_sum(a number ,b number) return number is total number :=0;

begin if a<=b then for i in a..b loop total :=total +i; end loop; else for i in b..a loop total :=total+i; end loop; end if; return total; end;

总结：

函数跟过程联系和区别 相同点：都是子程序，封装pl\sql语句块，可以接收参数，拥有封装，模块化，可复用性，安全性作用 不同点：函数有返回值，过程参数有三种模式，过程本身不返回值，但是可以通过参数out模式回写多个值，函数使用 select 函数名 from dual 调用，过程在命令窗口使用execute过程名调用

程序包（what，why，how）：

what：

•程序包是对相关过程、函数、变量、游标和异常等对象的封装

•程序包由规范和主体两部分组成

why：

程序包的优点：

•模块化

•更轻松的应用程序设计

•信息隐藏

•新增功能

•性能更佳

how：

--创建程序包 create or replace package my_pack as procedure find_emp_no(emp_no in employees.employee_id%type); procedure get_salary_empno(empno in employees.employee_id%type,salary out employees.salary%type); procedure swap(a in out number , b in out number); function get_salary_by_emp_no(empno employees.employee_id%type)return employees.salary%type; function get_total_by_dept_no(emp_dmp employees.department_id%type)return number; function total_sum(a number ,b number) return number; end my_pack;

--创建程序包主体 create or replace package body my_pack as ----------------- find_emp_no开始----------------------------------- procedure find_emp_no(emp_no employees.employee_id%type) is emp_row employees%rowtype; begin select e.* into emp_row from employees e where e.employee_id = emp_no; dbms_output.put_line('编号：' || emp_row.employee_id || '，薪资：' || emp_row.salary); end find_emp_no; ----------------------get_salary_empno开始--------------------------------------- procedure get_salary_empno(empno in employees.employee_id%type, salary out employees.salary%type) is begin select e.salary into salary from employees e where e.employee_id = empno; end get_salary_empno; ----------------------swap开始--------------------------------------- procedure swap(a in out number, b in out number) is temp number; begin temp := a; a := b; b := temp; end swap; ----------------------get_salary_by_emp_no开始--------------------------------------- function get_salary_by_emp_no(empno employees.employee_id%type)return employees.salary%type

is salary employees.salary%type; begin select e.salary into salary from employees e where e.employee_id=empno; return salary;--return返回一个值，类型必须声明是一致

end get_salary_by_emp_no; ----------------------get_total_by_dept_no开始--------------------------------------- function get_total_by_dept_no(emp_dmp employees.department_id%type)return number is num_total number; begin select count(*) into num_total from employees e where e.department_id = emp_dmp; return num_total; end get_total_by_dept_no; ----------------------get_total_by_dept_no开始--------------------------------------- function total_sum(a number ,b number) return number is total number :=0;

begin if a<=b then for i in a..b loop total :=total +i; end loop; else for i in b..a loop total :=total+i; end loop; end if; return total; end total_sum; end my_pack;

--程序包调用 declare salary employees.salary%type; num1 number :=100; num2 number :=200; total number;

begin my_pack.find_emp_no(100); my_pack.get_salary_empno(100,salary); dbms.output.put_line('工资'||salary); dbms.output.put_line('num1='||num1||'num2='||num2); my_pack.swap(num1,num2); dbms.output.put_line('num1'||num1||'num2'||num2);

end;

-------------------创建程序包，输入部门编号，输出所有员工信息---------- create or replace package emp_pack as --声明一个游标，指定返回值类型，即游标中存放的数据类型 cursor emp_cursor(deptno employees.department_id%type) return employees%rowtype; --根据员工部门编号，输出员工信息 procedure get_emps_by_deptno(deptno employees.department_id%type); --根据部门编号获取员工信息 function total(deptno employees.department_id%type) return number;

end emp_pack;

---创建程序包主体 create or replace package body emp_pack as cursor emp_cursor(deptno employees.department_id%type) return employees%rowtype is select e.* from employees e where e.department_id=deptno; -------------------------get_emps_by_deptno--------------------- procedure get_emps_by_deptno(deptno employees.department_id%type) is emp_row employees%rowtype; begin --打开游标 open emp_cursor(deptno); loop --逐行读取数据存入变量 fetch emp_cursor into emp_row; --游标没有数据退出循环 exit when emp_cursor%notfound;

dbms_output.put_line('编号'||emp_row.employee_id||'工资'||emp_row.salary); end loop; --关闭游标 close emp_cursor; end get_emps_by_deptno; -------------------------tortal--------------------- function total(deptno employees.department_id%type) return number is total_num number; begin select count(*) into total_num from employees e where e.department_id=deptno; return total_num; end total;

end emp_pack;

--测试 declare deptno employees.department_id%type:=&部门编号; begin emp_pack.get_emps_by_deptno(90); dbms_output.put_line(deptno||'..'||emp_pack.total(deptno)||',详细如下：'); emp_pack.get_emps_by_deptno(deptno); end;

程序包中的游标：

•游标的定义分为游标规范和游标主体两部分

•在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型

•RETURN子句指定的数据类型可以是：

–用 %ROWTYPE 属性引用表定义的记录类型

–程序员定义的记录类型

总结

•子程序是命名的 PL/SQL 块，可带参数并可在需要时随时调用

•有两种类型的PL/SQL子程序，即过程和函数

•过程用户执行特定的任务，函数用于执行任务并返回值

•程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装

•程序包由两部分组成，即包规范和包主体

•使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳