Oracle输入输出参数的存储过程与调用方法 |
发表者:admin分类:数据库2015-08-29 10:28:01 阅读[2368] |
Oracle带输入输出参数的存储过程
需求:在emp_copy中添加一条记录,empno为已有empno的最大值+1,ename不能为空且长度必须大于0,deptno为60。
创建存储过程:
create or replace procedure insert_emp(emp_name in varchar2, dept_no in number) as
begin
declare max_empno number;
begin
if(emp_name is null or length(emp_name) = 0) then
return;
end if;
if(dept_no != 60) then
return;
end if;
select max(empno) into max_empno from emp_copy;
insert into emp_copy(empno, ename, deptno) values(max_empno + 1, emp_name, dept_no);
end;
end insert_emp;
/
Procedure created.
调用存储过程并验证:
(1)
SQL>execute insert_emp('Li Si', 60);
PL/SQL procedure successfully completed.
SQL>
col empno format 99999;
col ename format a15;
col deptno format 99999;
select empno, ename, deptno from emp_copy where deptno = 60;
EMPNO ENAME DEPTNO
------ --------------- ------
7981 Li Si 60
(2)
SQL> execute insert_emp('', 6);
PL/SQL procedure successfully completed.
SQL> select empno, ename, deptno from emp_copy where deptno = 6;
SQL>
(二)使用输出参数
需求:在上个需求的基础上,要分别统计表emp_copy插入数据前后的记录数。
创建存储过程:
create or replace procedure insert_emp(emp_name in varchar2, dept_no in number, original_count out number, current_count out number) as
begin
declare max_empno number;
begin
if(emp_name is null or length(emp_name) = 0) then
return;
end if;
if(dept_no != 60) then
return;
end if;
select count(1) into original_count from emp_copy;
select max(empno) into max_empno from emp_copy;
insert into emp_copy(empno, ename, deptno) values(max_empno + 1, emp_name, dept_no);
select count(1) into current_count from emp_copy;
end;
end insert_emp;
/
Procedure created.
调用存储过程:
declare count1 number;
count2 number;
begin
insert_emp('Wang Wu', 60, count1, count2);
dbms_output.put_line('Original count of table emp_copy is ' || count1);
dbms_output.put_line('Current count of table emp_copy is ' || count2);
end;
/
Original count of table emp_copy is 15
Current count of table emp_copy is 16
PL/SQL procedure successfully completed.
(三)使用输入输出参数
in out参数综合了上述两种参数类型,既向过程体传值,也被赋值而传到过程体外。in out参数既可以用作输入也可以用作输出。
需求:实现两数交换。
创建存储过程:
create or replace procedure swap(value1 in out number, value2 in out number) as
begin
value1 := value1 + value2;
value2 := value1 - value2;
value1 := value1 - value2;
end swap;
/
Procedure created.
调用存储过程:
declare a number := 22;
b number := 33;
begin
dbms_output.put_line('Before swap: a = ' || a || ', b = ' || b);
swap(a, b);
dbms_output.put_line('After swap: a = ' || a || ', b = ' || b);
end;
/
Before swap: a = 22, b = 33
After swap: a = 33, b = 22
PL/SQL procedure successfully completed.
输入参数 v_depid 部门编号 ,输出参数 v_avgsalary 部门的平均工资。
输入 部门编号,查出部门的平均工资是多少。
create or replace procedure depavgsalary
( v_depid in number,v_avgsalary out number)
as
begin
select avg(e.salary) into v_avgsalary from employees e where e.department_id = v_depid;
end;
或是:
create or replace procedure depavgsalary
( v_depid in number,v_avgsalary out number)
is
begin
select avg(e.salary) into v_avgsalary from employees e where e.department_id = v_depid;
end depavgsalary;
过程写完调用方法:
declare
v_avgsalary number;
begin
depavgsalary(10,v_avgsalary);
dbms_output.put_line(v_avgsalary);
end;
/
或是下面的:
declare
i number;
begin
depavgsalary(20,i);
dbms_output.put_line(i);
end;
查出部门编号为20 的部门平均工资
需求:在emp_copy中添加一条记录,empno为已有empno的最大值+1,ename不能为空且长度必须大于0,deptno为60。
创建存储过程:
create or replace procedure insert_emp(emp_name in varchar2, dept_no in number) as
begin
declare max_empno number;
begin
if(emp_name is null or length(emp_name) = 0) then
return;
end if;
if(dept_no != 60) then
return;
end if;
select max(empno) into max_empno from emp_copy;
insert into emp_copy(empno, ename, deptno) values(max_empno + 1, emp_name, dept_no);
end;
end insert_emp;
/
Procedure created.
调用存储过程并验证:
(1)
SQL>execute insert_emp('Li Si', 60);
PL/SQL procedure successfully completed.
SQL>
col empno format 99999;
col ename format a15;
col deptno format 99999;
select empno, ename, deptno from emp_copy where deptno = 60;
EMPNO ENAME DEPTNO
------ --------------- ------
7981 Li Si 60
(2)
SQL> execute insert_emp('', 6);
PL/SQL procedure successfully completed.
SQL> select empno, ename, deptno from emp_copy where deptno = 6;
SQL>
(二)使用输出参数
需求:在上个需求的基础上,要分别统计表emp_copy插入数据前后的记录数。
创建存储过程:
create or replace procedure insert_emp(emp_name in varchar2, dept_no in number, original_count out number, current_count out number) as
begin
declare max_empno number;
begin
if(emp_name is null or length(emp_name) = 0) then
return;
end if;
if(dept_no != 60) then
return;
end if;
select count(1) into original_count from emp_copy;
select max(empno) into max_empno from emp_copy;
insert into emp_copy(empno, ename, deptno) values(max_empno + 1, emp_name, dept_no);
select count(1) into current_count from emp_copy;
end;
end insert_emp;
/
Procedure created.
调用存储过程:
declare count1 number;
count2 number;
begin
insert_emp('Wang Wu', 60, count1, count2);
dbms_output.put_line('Original count of table emp_copy is ' || count1);
dbms_output.put_line('Current count of table emp_copy is ' || count2);
end;
/
Original count of table emp_copy is 15
Current count of table emp_copy is 16
PL/SQL procedure successfully completed.
(三)使用输入输出参数
in out参数综合了上述两种参数类型,既向过程体传值,也被赋值而传到过程体外。in out参数既可以用作输入也可以用作输出。
需求:实现两数交换。
创建存储过程:
create or replace procedure swap(value1 in out number, value2 in out number) as
begin
value1 := value1 + value2;
value2 := value1 - value2;
value1 := value1 - value2;
end swap;
/
Procedure created.
调用存储过程:
declare a number := 22;
b number := 33;
begin
dbms_output.put_line('Before swap: a = ' || a || ', b = ' || b);
swap(a, b);
dbms_output.put_line('After swap: a = ' || a || ', b = ' || b);
end;
/
Before swap: a = 22, b = 33
After swap: a = 33, b = 22
PL/SQL procedure successfully completed.
转载请标明出处【Oracle输入输出参数的存储过程与调用方法】。
《www.micoder.cc》
虚拟化云计算,系统运维,安全技术服务.
Tags: | [阅读全文...] |
最新评论