Oracle 命令操作之 存储过程

SQL> conn scott/tiger
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as scott

SQL> create or replace procedure p1
  2  	is
  3  	begin
  4  	update scott.emp set ename ='candy' where empno=7876;
  5  	end p1;
  6  /

Procedure created

SQL> set serveroutput on;
SQL> declare
  2  begin
  3  p1;
  4  end;
  5  /

PL/SQL procedure successfully completed

SQL> conn system/manager
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as system

SQL> create or replace procedure p2
  2  	(v_zym in xs.zym%type,person_num out number)
  3  	is
  4  	begin
  5  		select count(zxf) into person_num from xs where zym=v_zym and zxf>40;
  6  end p2;
  7  /

Procedure created

SQL> set serveroutput on;
SQL> declare
  2  person_n number(3);
  3  begin
  4  p2('计算机',person_n);
  5  dbms_output.put_line(person_n);
  6  end;
  7  /
2

PL/SQL procedure successfully completed

SQL> create or replace procedure p3
  2  	is
  3  	v_deptno scott.emp.deptno%type;
  4  	v_pn number;
  5  	v_avg scott.emp.sal%type;
  6  begin
  7  select deptno,person_num,avgsal into v_deptno,v_pn,v_avg
  8  	from(select deptno,count(*) person_num,avg(sal) avgsal from scott.emp
  9  		group by deptno order by person_num desc) where rownum<=1;
 10  dbms_output.put_line(v_deptno||' '||v_pn||' '||v_avg);
 11  end p3;
 12  /

Procedure created

SQL> set serveroutput on;
SQL> declare
  2  begin
  3  p3;
  4  end;
  5  /
30 6 1566.67

PL/SQL procedure successfully completed