一.创建程序包规范实例:
create or replace package pack_op isprocedurepro_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) isidentity 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;