oracle存储过程写法

存储过程(procedure)：是一个命名了的语句块，可以有0个或多个参数

		语法：
		
		create or replace procedure HelloWorld
		as
		begin
		dbms_output.put_line('HelloWorld');
		end;
		
		调用存储过程
		* 命令调用 exec helloworld
		* 语句块调用
			begin 
			helloworld;
			end;
			/
			
		DELETE:
		
			create or replace procedure del_emp01
			is
			begin
			delete from emp01 where empno=7369;
			end;
			
			exec del_emp01;
			
			create or replace procedure del_emp01(v_empno in emp01.empno%type)
			is
			begin	
			delete from emp01 wherer empno=v_empno;
			end;	
			
			exec del_emp01(7521);
			
		INSERT
		
			create or replace procedure ins_emp01
			(v_empno emp01.empno%type,v_ename emp01.ename%type)
			as
			begin
			insert into emp01(empno,ename) values(v_empno,v_ename);
			end;
			
			 exec ins_emp01(1000,'李四');
			
		UPDATE
		
			create or replace procedure upd_emp01
			(v_empno emp01.empno%type,v_ename emp01.ename%type)
			as
			begin
			update emp01 set ename=v_ename where empno=v_empno;
			end;
		
			exec upd_emp01(1000,'张三');
			
		SELECT		
			create or replace procedure sel_emp01
			(v_empno emp01.empno%type,v_emp01_data out emp01%rowtype)
			as
			begin
			select * into v_emp01_data from emp where empno=v_empno;
			end;
			
			带有输出参数的存储过程不能使用命令直接调用	
			
			只能由语句块或程序调用(JAVA)	****************************************************************
			declare
			v_emp_data emp01%rowtype;
			begin
			sel_emp01(7499,v_emp_data);
			dbms_output.put_line(v_emp_data.ename||' '||v_emp_data.sal);
			end;
			调用：
			declare
			v_emp_data emp01%rowtype;
			begin
			sel_emp01(7499,v_emp_data);
			dbms_output.put_line(v_emp_data.ename||' '||v_emp_data.sal);
			end;
使用scott用户登录	
		统计某个部门的员工的工资总和，员工的人数，平均工资，创建存储过程    
		    create or replace procedure deptcount
		    (v_deptno emp.deptno%type,v_sal_sum out number,v_recordes out number,v_avg_sal out number,errorMsg out varchar2)
		    is    
		    begin
		    select sum(sal) into v_sal_sum from emp group by deptno having deptno=v_deptno;
		    select count(*) into v_recordes from emp group by deptno having deptno=v_deptno;
		    select avg(sal) into v_avg_sal from emp group by deptno having deptno=v_deptno;    
		    exception
		    when no_data_found then
		    errorMsg:='没有该部门';   
		    end;    
		    调用：
		    declare
		    v_sal_sum number;
		    v_recordes number;
		    v_avg_sal number(8,2);
		    v_errormsg varchar2(20);
		    begin
		    deptcount(90,v_sal_sum,v_recordes,v_avg_sal,v_errormsg);
		    dbms_output.put_line(v_sal_sum||' '||v_recordes||' '||v_avg_sal);
		    dbms_output.put_line(v_errormsg);
		    end;
		    
		    //输出参数
		    create or replace procedure my_pro(v_num in number,v_result out number)
		    is
		    v_temp number;
		    begin
		    	v_temp:=0;
		    	for i in 1..v_num 
		    	loop
		    		v_temp:=v_temp+i;
		    	end loop;
		    	v_result:=v_temp;
		    end;    
		    declare
		    v_recieve number;
		    begin
		    	my_pro(100,v_recieve);
		    	dbms_output.put_line(v_recieve);
		    end;
		    //既是输入参数又是输出参数
		    create or replace procedure my_pro1(v_i in out number)
		    is
		    v_j number;
		    begin
		    v_j:=30;
		    v_i:=v_i*v_j;  		   
		    end;
		    
		    declare
		    v_t number;
		    begin
		    v_t:=20;
		    my_pro1(v_t);
		    dbms_output.put_line(v_t);
		    end;