子程序
子程序分类
过程:又叫存储过程,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;