分类： Oracle
2009-07-21 09:26:21
CREATE [OR REPLACE] PROCEDURE Procedure_name
[ (argment [ { IN | OUT | IN OUT } ] Type,
argment [ { IN | OUT | IN OUT } ] Type ]
{ IS | AS }
<类型.变量的说明>
BEGIN
<执行部分>
EXCEPTION
<可选的异常错误处理程序>
END; 例子：create or replace procedure delEmp(
v_empno emp.empno%TYPE
)
is
no_result exception;
begin
delete from emp where empno = v_empno;
if sql%notfound then
raise no_result;
end if;
dbms_output.put_line('empno ' || v_empno || 'has been deleted!');
exception
when no_result then
dbms_output.put_line('empno ' || v_empno || ' not found!');
when others then
dbms_output.put_line(sqlcode || sqlerrm);
end; 调用存储过程：begin delemp(7369); end;方法二：在Toad Sql Editor中执行如下代码：
方法三：在Sql Plus中调用过程：Java代码
- exec delEmp(1234);
exec delEmp(1234);
set serveroutput on;然后调用存储过程：
exec delEmp(1234);可以使用DROP语句删除过程：DROP PROCEDURE proceduer_name;
create or replace procedure getEmp(
v_empno emp.empno%TYPE,
v_ename out emp.ename%TYPE,
v_sal out emp.sal%TYPE,
v_job out emp.job%TYPE,
v_deptno out emp.deptno%TYPE
)
is
begin
select ename, sal, job, deptno into v_ename, v_sal, v_job, v_deptno from emp where empno = v_empno;
exception
when no_data_found then
dbms_output.put_line('你需要的数据不存在！');
when others then
dbms_output.put_line(sqlcode || sqlerrm);
end; 调用存储过程：declare
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
v_job emp.job%TYPE;
v_deptno emp.deptno%TYPE;
begin
getEmp(7499,v_ename,v_sal,v_job,v_deptno);
dbms_output.put_line('人员姓名：'||v_ename||'，工资：'|| v_sal || '，职位：'|| v_job || '，部门编号：'||v_deptno);
end;