Oracle 创建Package Procedure Function

一.创建程序包规范实例：

create or replace package pack_op is
         procedurepro_print_ename(id number,value2 out number);
         procedurepro_print_sal(id number);
         functionfun_re_date(id number) return date;
end pack_op;
创建程序包主体：
create or replace package body pack_op is
         procedure pro_print_ename(id number,value2 out number) is/as
         name emp.ename%type;
         begin
             select ename into name from emp whereempno=id;
                   dbms_output.put_line('职员姓名：'||name);
                   value2:= 500;
         Exception
                   Whenothers then
                      Rollback;
         end pro_print_ename;
        
         procedure pro_print_sal(id number) is/as
         salaryemp.sal%type;
         begin
                   selectsal into salary from emp where empno=id;
                   dbms_output.put_line('职员工资：'||salary);
         Exception
                   When others then
                     Rollback;
         end pro_print_sal;
        
         function fun_re_date(id number) return date is/as
         bedateemp.hiredate%type;
         begin
                   select hiredate into bedate from emp where empno=id;
         returnbedate;
         end fun_re_date;
end pack_op;
调用程序包中创建的过程和函数
declare value2 number;( 如果赋值value2 number :=100; value2就是in out类型)
         begin
         pack_op.pro_print_ename(7900,value2);
         dbms_output.put_line(value2);
end;
exec pack_op.pro_print_sal(7900);
select pack_op.fun_re_date(7900) from dual;

查询有关过程、函数和程序包的信息： USER_OBJECTS数据字典视图
column object_name format a18
select object_name,object_type fromuser_objects
      where object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');

二.创建带OUT参数的过程(默认是 in,输入输出in out)

create or replace procedure test(value1varchar2,value2 out number) is
identity number;
begin
         select sal into identity from emp where empno=value1;
         if identity<2000 then value2:=1000;
         else value2:=500;
         end if;
end test;
调用带OUT参数的过程：(value2 number := 100--相当于in out)
declare value2 number;
         begin
         test(7900,value2);
         dbms_output.put_line(value2);
end;
将过程的执行权限授予其他用户
GRANT EXECUTE ON find_emp TO scott;  GRANT EXECUTE ON swap TO PUBLIC;
将find_emp过程的执行权限授予给用户scott，将执行swap过程的权限授予所有数据库用户。
删除过程语法：DROP PROCEDURE procudure_name;

注意事项：
1. 存储过程参数不带取值范围,in表示传入,out表示输出,in out两者都(传入最大值4000)
2.变量带取值范围，后面接分号--定义变量最大值32767
3.在判断语句前最好先用count(*)函数判断是否存在该条操作记录
4.用select 。。。into。。。给变量赋值
5.在代码中抛异常用 raise+异常名

三.创建函数

1)有返回值无参数
create or replace function fun_hello
return varchar2
is/as
begin
         return'朋友，您好';
end fun_hello;
调用函数：select fun_hellofrom dual;

2)默认 in 型参数
create or replace function get_sal(dept_idnumber)
return number//返回number类型
as
v_sumsal number(10) := 0;
cursor salary_cursor is select salary fromemployees where department_id = dept_id;
begin
for c in salary_cursor loop
v_sumsal := v_sumsal +c.salary;
end loop;
return v_sumsal;
end;
调用方式一
begin
dbms_output.put_line(get_sal(80));
end;
调用方式二
select get_sal(80) from dual;

3)带有 out 型参数
create or replace function get_salsum(dept_id number,total_count out number)
//total_count为 out 型参数
return number
as
v_sumsal number(10) := 0;
cursor salary_cursor is select salary fromemployees where department_id = dept_id;
begin
// 为total_count赋初值 0;
total_count := 0;
for c in salary_cursor loop
v_sumsal := v_sumsal +c.salary;
total_count := total_count + 1;
end loop;
return v_sumsal;
end;

调用方式
declare
v_num number(10);( v_num number := 100--相当于in out)
begin
dbms_output.put_line(get_salsum(80,v_num));
dbms_output.put_line(v_num);
end;
形式参数必须只使用数据库类型，不得使用PL/SQL类型。函数的返回类型也必须是数据库类型

删除函数：DROP FUNCTIONfunction_name;

会遇到的小问题：select into问题
SELECT INTO STATEMENT将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，
必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)

解决办法：
BEGIN
SELECT name
  into v_name
  FROM t_student
 WHERE id = '101'
EXCEPTION
WHEN NO_DATA_FOUND THEN
  v_name := '';
  WHEN OTHERS THEN
  v_name := '多于一条啦';
END;