create or replace procedure 过程名称
          (argument1 [mode1] datatype1,argument2 [mode2] datatype2,...)
is [as]
PL/SQL Block;

1、建立无参数过程
create or replace procedure out_time
is
begin
     dbms_output.put_line(sysdate);
end;
/
execute out_time;

2、建立带有输入参数的过程
create or replace procedure add_emp(
        empno scott.emp.empno%type,
        ename scott.emp.ename%type,
        job scott.emp.job%type,
        mgr scott.emp.mgr%type,
        hiredate scott.emp.hiredate%type,
        sal scott.emp.sal%type,
        comm scott.emp.comm%type,
        deptno scott.emp.deptno%type)
is
begin
     insert into scott.emp
            values(empno,ename,job,mgr,hiredate,sal,comm,deptno);
end;
/
execute add_emp(8000,'hjp','manager',8000,'01-10月-08',5000,1000,20);

3、建立带有输出参数的过程
create or replace procedure update_sal
        (eno number,salary number,name out varchar2)
is
begin
     update scott.emp set sal=salary where empno=eno returning ename into name;
end;
/

declare
       v_name scott.emp.ename%type;
begin
     update_sal(8000,4000,v_name);
     dbms_output.put_line(v_name);
end;

4、建立带有输入输出参数的过程
create or replace procedure divide
        (num1 in out number,num2 in out number)
is
  v1 number;
  v2 number;
begin
     v1:=trunc(num1/num2);
     v2:=mod(num1,num2);
     num1:=v1;
     num2:=v2;
end;
/
declare
       n1 number:=&n1;
       n2 number:=&n2;
begin
     divide(n1,n2);
     dbms_output.put_line('商:'||n1||',余数:'||n2);
end;

5、使用记录变量作为输入参数
create or replace procedure add_dept(dept_record scott.dept%rowtype)
is
begin
     insert into scott.dept values dept_record;
end;
/
declare
  dept_record scott.dept%rowtype;
begin
     dept_record.deptno:=&dno;
     dept_record.dname:='&name';
     dept_record.loc:='&loc';

     add_dept(dept_record);
end;

6、使用记录变量作为输出参数
create or replace procedure get_employee
        (eno scott.emp.empno%type,emp_record out scott.emp%rowtype)
is
begin
     select * into emp_record from scott.emp where empno=eno;
exception
         when no_data_found then
              raise_application_error(-2000,'雇员不存在');
end;
/
declare
  emp_record scott.emp%rowtype;
begin
     get_employee(&eno,emp_record);
     dbms_output.put_line(emp_record.ename||','||emp_record.sal);
end;

7、使用集合变量作为输入参数
create type deptno_table_type is table of number(2);
/
create type dname_table_type is table of varchar2(10);
/
create type loc_table_type is table of varchar2(20);
/
create or replace procedure add_department
      (deptno_table deptno_table_type,
       dname_table dname_table_type,
       loc_table loc_table_type)
is
begin
     forall i in 1..deptno_table.count
        insert into scott.temp_dept values (deptno_table(i),dname_table(i),loc_table(i));
end;
/
declare
  deptno_table deptno_table_type:=deptno_table_type(60,70,80);
  dname_table dname_table_type:=dname_table_type('ALC','PERS','PCS');
  loc_table loc_table_type:=loc_table_type('SZ','WH','GD');
begin
     add_department(deptno_table,dname_table,loc_table);
end;

8、使用集合变量作为输出参数
create type ename_table_type is table of varchar2(10);
/
create type job_table_type is table of varchar2(10);
/
create or replace procedure get_emp
  (dno number,ename_table out ename_table_type,job_table out job_table_type)
is
begin
     select ename,job bulk collect into ename_table,job_table
            from scott.emp where deptno=dno;
end;
/
declare
  ename_table ename_table_type;
  job_table job_table_type;
begin
     get_emp(&dno,ename_table,job_table);
     for i in 1..ename_table.count loop
         dbms_output.put_line('姓名:'||ename_table(i)||',岗位:'||job_table(i));
     end loop;
end;

9、维护过程
授权system能运行em_ins
grant execute on em_ins to system;

删除过程
drop procedure 过程名；

显示编译错误
show errors;

确定无效过程
select object_name from user_objects where object_type='PROCEDURE' and status='INVALID';

编译过程
alter procedure get_emp COMPILE;

查看过程代码
select text from user_source where name='GET_EMP';

例
create or replace procedure em_ins(v_name varchar2) as
  v_empno number(4);
begin
  select max(empno)+1 into v_empno from scott.emp;
  insert into scott.emp(empno, ename,job,mgr,hiredate,sal,comm,deptno)
  values(v_empno,v_name,null,null,sysdate,null,null,20);
  commit;
end em_ins;