Oracle学习之存储过程(转)

子程序

子程序分类

过程：又叫存储过程，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;