存储过程(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;
调用存储过程
最新推荐文章于 2024-09-10 10:21:38 发布