05 PLSQL程序设计 - 2-过程函数

必须说明
必须说明
调用过程时，实际参数取值 过程结束时，形参的内容 被传递给过程。过程结束时， 将赋给实参。把值返回给 形参的内容将赋给实参。把 调用环境。 值返回给调用环境。 形式参数不能被初始化， 只能被赋值。当过程调用 实际参数变量必须初始化。 时，实参中具有的任何值 将被忽略。 实际参数必需是变量 实际参数必需是变量。
成都信息工程大学 软件工程学院
过程函数
–
实际参数(Actual parameters):在调用过程中作为参数传 递给过程的变量或表达式就是实际参数。
DECLARE
v_no emp.empno%TYPE:=10000, v_name emp.ename%TYPE :=‘Jones’, v_job emp.job%TYPE := 'SALESMAN',
• 执行
DECLARE v_empno emp.empno%TYPE:=&no; BEGIN raise_salary(v_empno); END;
成都信息工程大学 软件工程学院
过程函数
• 例子
CREATE OR REPLACE PROCEDURE query_emp (p_id IN emp.empno%TYPE, p_name OUT emp.ename%TYPE, p_salary OUT emp.sal%TYPE,p_comm OUT m%TYPE) IS BEGIN SELECT ename, sal, comm INTO p_name, p_salary, p_comm FROM emp WHERE empno = p_id;
• 执行：
过程函数
• 例子
CREATE OR REPLACE PROCEDURE add_dept (p_name IN dept.dname%TYPE DEFAULT 'unknown', p_loc IN dept.loc%TYPE DEFAULT 'NEW YORK') IS BEGIN INSERT INTO dept (deptno,dname, loc) VALUES (dept_seq.NEXTVAL, p_name, p_loc); END add_dept;
过程函数
• 例子
CREATE OR REPLACE PROCEDURE raise_salary (p_id IN emp.empno%TYPE) BEGIN UPDATE emp END raise_salary; SET sal = sal * 1.10 WHERE empno = p_id; IS
成都信息工程大学 软件工程学院
过程函数
• 子程序是带名的PL/SQL块，能够接受参数和被环境调用。在PL/SQL
中两类子程序：过程和函数
<header>
IS | AS
声明部分 BEGIN 执行部分 EXCEPTION (可选) 异常处理部分 END;
子程序说明
子程序体
成都信息工程大学 软件工程学院
CREATE OR REPLACE PROCEDURE ParameterLength ( p_Parameter1 IN OUT VARCHAR2(10), p_Parameter2 IN OUT NUMBER(3,2)) AS BEGIN p_Parameter1 := 'abcdefghijklm'; p_Parameter2 := 12.3; END ParameterLength; / 过程声明非法
过程函数
–
形式参数(Formal parameters):在过程中声明的参数就是形式 参数。
CREATE OR REPLACE PROCEDURE insert_emp (no emp.empno%TYPE, Job emp.job%TYPE, name emp.ename%TYPE, mgr emp.mgr%TYPE,
v_mgr emp.mgr%TYPE :=7369,
v_hiredate emp.hiredate%TYPE :=SYSDATE, v_salary emp.sal%TYPE := 800, v_comm m%TYPE :=NULL,
v_deptno emp.deptno%TYPE :=10
exception_handlers;] END [procedure_name];
3.存储过程定义完成后需要调用 才能执行过程内部的代码。
成都信息工程大学 软件工程学院
过程函数
•
IS
Example 创建存储过程，输出系统的日期和时间
CREATE OR REPLACE PROCEDURE display_time BEGIN dbms_output.put_line(systimestamp); END display_time;
END query_emp;
成都信息工程大学 软件工程学院
过程函数
• 执行
DECLARE v_empno emp.empno%TYPE:=&no; v_name emp.ename%TYPE; v_salary emp.sal%TYPE; v_comm m%TYPE;
BEGIN
query_emp(v_empno,v_name,v_salary,v_comm); dbms_output.put_line(v_empno||' '||v_name||' '||v_salary||' '||v_comm); END;
不能分配缺省值 不能分配缺省值 成都信息工程大学 软件工程学院
过程函数

PL/SQL子程序程参数的三种模式及特点:
◦ IN用于向程序传递数据，参数在程序内部不能赋值
◦ OUT用于从程序内获取数据，参数在程序内部赋值 前是null ◦ IN OUT 用于向程序传递数据和从程序内获取数据
成都信息工程大学 软件工程学院
• 执行：SQL>BEGIN
add_dept; add_dept ('TRAINING'); add_dept ( p_loc =>'BOSTON ', p_name => 'EDUCATION'); add_dept ( p_loc => 'CHICAGO') ; END; / SQL>SELECT * FROM dept; 成都信息工程大学
• 使用三种方式调用上面创建的存储过程
• • •
方式一：使用sqlplus命令EXECUTE(简写EXEC) 调用 EXECUTE display_time; 方式二：使用sql命令CALL调用 CALL display_time( ); 方式三：在PL/SQL块中调用 BEGIN display_time; END; 成都信息工程大学 软件工程学院
hiredate emp.hiredate%TYPE , salary emp.sal%TYPE ,
comm m%TYPE , ) IS BEGIN INSERT INTO emp VALUES(no,name,job,mgr,hiredate,salary,comm,deptno); END; deptno emp.deptno%TYPE
过程函数
• 子程序说明（header）决定 : – PL/SQL子程序的类型是过程还是函数。 – 子程序的名称 – 参数列表 – 当子程序是函数时必须要有返回值(使用RETURN
子句)
–
关键字IS或AS是必须的。
成都信息工程大学 软件工程学院
过程函数
•
子程序体:是一个拥有声明、执行和异常处理部分的 PL/SQL块。
– 声明部分介于IS|AS和BEGIN之间。在匿名块中必 须使用DECLARE关键字显示的指出声明部分，而在子 程序中没有DECLARE关键字。
– 执行部分介于BEGIN和END关键字之间，这部分
必EPTION和 END之间是可选 的部分
成都信息工程大学 软件工程学院
过程函数
• 语法
CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [IN | OUT | IN OUT] data_type , parameter2 [IN | OUT | IN OUT] data-_type,…)] IS |AS 注意与匿名块有三点区别： [declaration_section;] 1.无DECLARE关键字 BEGIN executable_section; 2.在END后面可以加过程名 作为 定义结束的标志 [EXCEPTION
BEGIN insert_emp(v_no, v_name, v_job, v_mgr, v_hiredate, v_salary, v_comm, v_deptno ); END;
成都信息工程大学 软件工程学院
过程函数
–
对形式参数的约束：在过程声明中，限制CHAR和 VARCHAR2参数的长度以及限制NUMBER参数的精度和/ 或刻度范围都是非法的。
软件工程学院
过程函数 例8.4 为scott.emp表创建一个能完成插入功能的存储过程insert_emp。 CREATE OR REPLACE PROCEDURE insert_emp 注意：在参数的定义 (no IN scott.emp.empno%TYPE, 上，除了向主键字段 name IN scott.emp.ename%TYPE DEFAULT NULL, empno插入值的变量 job IN scott.emp.job%TYPE DEFAULT 'SALESMAN', no没有设置默认值外 mgr IN scott.emp.mgr%TYPE DEFAULT 7369, ，其他所有的变量都 给出了默认值，这样 hiredate scott.emp.hiredate%TYPE DEFAULT SYSDATE, 当用户调用该存储过 salary scott.emp.sal%TYPE DEFAULT 800, 程时，可以指定任意 comm m%TYPE DEFAULT NULL, deptno scott.emp.deptno%TYPE DEFAULT 10) IS 个数的实参。 e_integrity EXCEPTION; PRAGMA EXCEPTION_INIT (e_integrity,-2291); BEGIN INSERT INTO scott.emp VALUES(no,name,job,mgr,hiredate,salary,comm,deptno); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN dbms_output.put_line('该员工已经存在！'); WHEN e_integrity THEN dbms_output.put_line('部门编号填写错误！'); 成都信息工程大学 软件工程学院 END;