Oracle学习之存储过程(转)

rfyfxfplwg

　　子程序
子程序分类
过程：又叫存储过程，Stored Procedure，简称：sp。
函数：function
触发器:trigger
存储过程：strore procedure,所以用 sp_
函数:function,所以用 fun_
触发器:trigger,所以用 tr_
包：package，所以用pkg
参数：parameter,所以用 p_
变量：value,所以用 v_
说明：
每个公司都有一些自定义开发规范。
子程序优点
模块化：一个过程完成一个模块
可重用性：一次编写，反复调用
易维护：谁出错，修改谁
易调试：可以单步调试，而匿名块不能(实用功能)
存储过程
与块的关系
存储过程头定义+块 = 存储过程
调用存储过程
call 存储过程(变量):JDBC中调用sp标准方式
exec 存储过程(变量)：PL/SQL中调用，支持控制台输出         
 
案例一：删除指定名称表格
特点：无参数存储过程
CREATE OR REPLACE PROCEDURE sp_del_table
AS 
str_sql VARCHAR2(1000);
str_table_name VARCHAR2(100);
BEGIN
  str_table_name := 't1';
  str_sql := 'drop table '||str_table_name; 
  EXECUTE IMMEDIATE str_sql;
END;
案例二：添加员工和部门(给出部门名，工号，姓名，工作日期)
特点：输入参数
CREATE OR REPLACE PROCEDURE sp_add_info
(
p_dept_name IN dept.dname%TYPE,
p_empno emp.empno%TYPE,
p_ename emp.ename%TYPE,
p_hiredate emp.hiredate%TYPE
)
AS
v_max_deptno dept.deptno%TYPE;
BEGIN
SELECT nvl(MAX(dept.deptno),0) INTO v_max_deptno FROM dept;
INSERT INTO dept(deptno,dname) VALUES(v_max_deptno+1,p_dept_name);
INSERT INTO emp(empno,ename,hiredate,deptno) VALUES(p_empno,p_ename,p_hiredate,v_max_deptno+1);
COMMIT;
END;
SQL> exec sp_add_info('new1',2000,'javaboy',to_date('2008-4-5','yyyy.mm.dd'));
案例三：添加指定员工，并返回员工总数
特点：输入参数，输出参数
CREATE OR REPLACE PROCEDURE sp_add_emp
(
p_emp_no emp.empno%TYPE,
p_emp_name emp.ename%TYPE,
p_emp_count OUT NUMBER
)
AS
BEGIN
INSERT INTO emp(empno,ename) VALUES(p_emp_no,p_emp_name);
COMMIT;
SELECT COUNT(*) INTO p_emp_count FROM emp;
END;
调用命令：
SQL> set serveroutput on
SQL> var emp_count number;
SQL> exec sp_add_emp('kk',2002,:emp_count);
SQL> begin
  2   dbms_output.put_line(:emp_count);
  3  end;
  4  /
案例四：根据员工号更新员工姓名(最大10位)，并返回新姓名
特点：输入输出参数
CREATE OR REPLACE PROCEDURE update_emp_info
(
  p_emp_no emp.empno%TYPE,
  p_emp_name IN OUT emp.ename%TYPE
)
AS
  len_name NUMBER(2);
BEGIN
  len_name := length(p_emp_name);
  IF(len_name>10)THEN
     p_emp_name := substr(p_emp_name,1,10);
  END IF;
  INSERT INTO emp(empno,ename) VALUES(p_emp_no,p_emp_name);
  COMMIT;
END;
调用：
SQL> var emp_name varchar2(20);
SQL> exec :emp_name :='01234567890123456789';
 
PL/SQL procedure successfully completed
emp_name
---------
01234567890123456789
 
SQL> exec update_emp_info(3001,:emp_name);
 
PL/SQL procedure successfully completed
emp_name
---------
0123456789
 
SQL> begin
  2    dbms_output.put_line(:emp_name);
  3  end;
  4  /
传递参数
根据位置传递（根据参数定义的顺序调用）
根据名称传递(根据参数的名称调用，顺序可与定义时不同)
混合传递(同时使用位置和名称传递)。
案例：添加部门信息，部门名称重复时要给出提示，然后通过三种方式调用。
CREATE OR REPLACE PROCEDURE add_dept
( p_dept_no dept.deptno%TYPE,
  p_dept_name dept.dname%TYPE
)
AS
  dept_count NUMBER(1);
BEGIN
  SELECT COUNT(*) INTO dept_count FROM dept
  WHERE dept.dname=p_dept_name;
  IF(dept_count>=2)THEN
     raise_application_error(-20005,'部门名称不能重复！');
  END IF;
 
  INSERT INTO dept(deptno,dname)
  VALUES(p_dept_no,p_dept_name);
  COMMIT;
END;