1.创建过程
create or replace procedure 过程名 as
声明语句段;
begin
执行语句段;
exception
异常处理语句段;
end;
2. 带参数的过程
参数类型3种
in参数:读入参数,主程序向过程传递参数值
out参数:输出参数,过程向主程序传递参数值
in out参数:双向参数
定义带参数的过程示例:
Set serveroutput on
create or replace procedure scott.tempprocedure(
tempdeptno in scott.dept.deptno%type, --输入参数
tempdname out scott.dept.dname%type, --输出参数
temploc in out scott.dept.loc%type) as -- 双向参数
loc1 scott.dept.loc%type;
dname1 scott.dept.dname%type;
begin
select loc into loc1
from scott.dept
where deptno=tempdeptno;
select dname into dname1
from scott.dept
where deptno=tempdeptno;
temploc:='地址:'||loc1;
tempdname:='姓名'||dname1;
end;
使用带参数的过程示例:
set serveroutput on
declare
myno scott.dept.deptno%type;
mydname scott.dept.dname%type;
myloc scott.dept.loc%type;
begin
myno:=10;
mydname:='';
myloc:='';
scott.tempprocedure(myno,mydname,myloc);
dbms_output.put_line(myno);
dbms_output.put_line(mydname);
dbms_output.put_line(myloc);
end;