第5章_存储子程序

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,1,第,5,章 存储子程序,2,知识要点,存储过程,存储函数,存储过程与存储函数的区别,局部子程序,存储子程序定义,是以独立对象的形式存储在数据库服务器中的，主要包括,存储过程,和,存储函数,两种。,存储过程的目的是执行某些操作，不需要返回值；,存储函数的目的是执行某些操作并返回一个具体值。,5.1,存储过程,存储过程的创建,CREATE OR REPLACE PROCEDURE,过程名,(,参数名,IN|OUT|IN OUT,数据类型,),IS|AS,说明部分,BEGIN,语句序列,EXCEPTION,出错处理,END,过程名,;,5.1,存储过程,形式参数的三种类型,模式,描述,IN,参数,（默认模式）,输入参数，用来从调用环境中向存储过程传递值，在过程体内不能给,IN,参数赋值,OUT,参数,输出参数，用来从存储过程中返回值给调用者，在过程体内必须给,OUT,参数赋值,IN OUT,参数,输入输出参数，既可以从调用者向存储过程中传递值，也可以从过程中返回可能改变的值给调用者,5.1,存储过程,【,例,5-1,】,定义一个过程，此过程说明三种模式参数的区别。,CREATE OR REPLACE PROCEDURE modetest,(p_inparameter IN number,p_outparameter OUT number,p_inoutparameter IN OUT number),IS,v_localvariable number;,BEGIN,v_localvariable:=p_inparameter;/*,正确*,/,(p_inparameter:=7;-,错误,),v_outparameter:=7;/*,正确*,/,(v_localvariable:=p_outparameter;-,错误,),v_localvariable:=p_inoutparameter;/*,正确*,/,p_inoutparameter:=7;/*,正确*,/,END modetest;,5.1,存储过程,【,例,5-2,】,创建一个无参数的存储过程，输出当前系统的日期。,CREATE OR REPLACE PROCEDURE out_date,IS,BEGIN,DBMS_OUTPUT.PUT_LINE(,当前系统日期为,:|SYSDATE);,END out_date;,5.1,存储过程,【,例,5-3,】,创建一个带输入参数的存储过程，将指定雇员号的员工工资提高,100,。,CREATE OR REPLACE PROCEDURE update_employees,(v_employee_id IN employees.employee_id%TYPE),IS,BEGIN,UPDATE employees,SET salary=salary+100,WHERE employee_id=v_employee_id;,END update_employees;,5.1,存储过程,【,例,5-4,】,创建一个带输入和输出参数的存储过程，根据给定的雇员号返回员工的姓名和工资。,CREATE OR REPLACE PROCEDURE query_employees,(v_employee_id IN employees.employee_id%TYPE,v_last_name OUT employees.last_name%TYPE,v_first_name OUT employees.first_name%TYPE,v_salary OUT employees.salary%TYPE),IS,BEGIN,SELECT last_name,first_name,salary,INTO v_last_name,v_first_name,v_salary,FROM employees,WHERE employee_id=v_employee_id;,END query_employees;,5.1,存储过程,【,例,5-5】,创建一个带输入输出参数的存储过程，对输入的工资增加,10%,，并返回。,CREATE OR REPLACE PROCEDURE raise_salary,(v_salary IN OUT number),IS,BEGIN,v_salary:=v_salary+v_salary*0.1;,END raise_salary;,5.1,存储过程,存储过程的调用,存储过程创建后，以编译的形式存储于数据库的,数据字典,中。可以在,SQL*Plus,、,Oracle,开发工具或第三方开发工具中调用并运行存储过程。如果不被调用，存储过程是不会执行的。,5.1,存储过程,参数传值,通过存储过程的名称调用存储过程时，,实参,的数量、顺序、类型要与,形参,的数量、顺序、类型,相匹配,。,形式参数是,IN,模式的参数，实际参数可以是具体的值，或是一个已经赋值的变量。,形式参数是,OUT,模式的参数，实际参数,必须,是变量，而,不能,是常量。当调用存储过程后，此变量就,被,赋值了。,形式参数是,IN OUT,模式的参数，则实际参数,必须,是已经赋值的变量。当存储过程完成后，该变量将被,重新,赋值。,5.1,存储过程,调用方法,（,1,）在,SQL*Plus,中调用存储过程,SQL*Plus,中使用,EXECUTE,命令调用存储过程。,（,2,）在,PL/SQL,程序中调用存储过程,在,PL/SQL,程序中，存储过程可以作为一个独立的,表达式,被调用。,5.1,存储过程,【,例,5-6】,使用,EXECUTE,命令调用存储过程,out_date,。,【,例,5-7】,使用,EXECUTE,命令调用存储过程,update_emp,。,EXECUTE out_date;,EXECUTE update_employees(102);,5.1,存储过程,【,例,5-8】,从,PL/SQL,程序中调用存储过程,out_date,，查询当前系统日期。,BEGIN,out_date;,END;,5.1,存储过程,【,例,5-9】,从,PL/SQL,程序中调用存储过程,update_emp,，从键盘输入雇员号，并对该雇员的工资进行更新。,DECLARE,s_employee_id employees.employee_id%TYPE:=,BEGIN,update_employees(s_employee_id);,END;,5.1,存储过程,【,例,5-10】,从,PL/SQL,中调用存储过程,query_employees,，查询雇员号为,101,员工的姓、名和工资。,DECLARE,s_last_name employees.last_name%TYPE;,s_first_name employees.last_name%TYPE;,s_salary employees.salary%TYPE;,BEGIN,query_employees(101,s_last_name,s_first_name,s_salary);,DBMS_OUTPUT.PUT_LINE(,雇员姓名为,:|s_last_name|,|s_first_name);,DBMS_OUTPUT.PUT_LINE(,雇员工资为,:|s_salary);,END;,5.1,存储过程,【,例,5-11】,从,PL/SQL,程序中调用存储过程,raise_sal,，将原有的低保工资提升,10%,。,DECLARE,s_salary number:=,BEGIN,DBMS_OUTPUT.PUT_LINE(,原来的低保工资为,:|s_salary);,raise_salary(s_salary);,DBMS_OUTPUT.PUT_LINE(,提升后的低保工资为,:|s_salary);,END;,5.1,存储过程,存储过程的管理,1,修改存储过程,为了修改存储过程，可以先删除该存储过程，然后重新创建。,或,CREATE OR REPLACE PROCEDURE,语句重新创建并覆盖原有的存储过程。,2,删除存储过程,删除存储过程使用,DROP PROCEDURE,语句,5.1,存储过程,存储过程的管理,3,查看存储过程语法错误,存储过程在编译时可能出现一些语法错误，但只是以警告的方式提示“创建的过程带有编译错误”，用户如果想查看错误的详细信息，可以使用,SHOW ERRORS,命令显示刚编译的存储过程的出错信息。,4,查看存储过程结构,查看存储过程的基本结构，包括存储过程的形式参数名称、形式参数的模式以及形式参数的数据类型可以通过执行,DESC,命令获得。,5.1,存储过程,存储过程的管理,5.,查看存储过程源代码,存储过程的源代码通过查询数据字典,USER_SOURCE,中的,TEXT,即可获得。,SELECT TEXT,FROM USER_SOURCE,WHERE NAME=QUERY_EMPLOYEES;,5.2,存储函数,存储函数的创建,存储函数的创建与存储过程的创建基本相似，不同的地方是存储函数必须有返回值。,CREATE OR REPLACE FUNCTION,函数名,(,参数名,IN,数据类型,),RETURN,数据类型,IS|AS,说明部分,BEGIN,语句序列,RETURN(,表达式,),EXCEPTION,例外处理程序,END,函数名,;,5.2,存储函数,形式参数与返回值,创建存储函数时，可以定义,零个或多个形式参数,，并且都为,IN,模式，,IN,可以省略不写。存储函数是靠,RETURN,语句返回结果，并且,只能返回一个结果,。在函数定义的头部，参数列表之后，必须包含一个,RETURN,语句来指明函数返回值的类型，但不能约束返回值的长度、精度等。,在函数体的定义中，必须,至少,包含一个,RETURN,语句，来指明函数的返回值。也可以有,多个,RETURN,语句，但最终,只有一个,RETURN,语句被执行。,函数体中,RETURN,后面的表达式是要返回的数据，数据的类型与函数定义时,RETURN,语句指定的,类型相同,。,5.2,存储函数,【,例,5-12,】,创建一个无参数的存储函数，返回雇员的最高工资。,CREATE OR REPLACE FUNCTION max_salary,RETURN employees.salary%TYPE,IS,m_salary employees.salary%TYPE;,BEGIN,SELECT MAX(salary)INTO m_salary,FROM employees;,RETURN m_salary;,END max_salary;,5.2,存储函数,【,例,5-13,】,创建一个有参数的存储函数，根据雇员号返回员工所在的部门号。,CREATE OR REPLACE FUNCTION get_department,(v_employee_id employees.employee_id%TYPE),RETURN employees.department_id%TYPE,IS,v_department_id employees.department_id%TYPE;,BEGIN,SELECT department_id INTO v_department_id,FROM employees,WHERE employee_id=v_employee_id;,RETURN v_department_id;,END get_department;,5.2,存储函数,存储函数的调用,（,1,）在,SQL,语句中调用存储函数,（,2,）在,PL/SQL,程序中调用存储函数,5.2,存储函数,【,例,5-14】,在,SQL,语句中调用存储函数,max_salary,。,SELECT employee_id,first_name,last_name