PL/SQL之存储过程和函数
1、创建存储过程
1.1语法: CREATE[OR REPLACE] PROCEDURE [schema.] procedure_name[(argument[{IN|OUT|IN OUT}] datatype[,...])] {IS|AS} pl/sql_body; procedure_name为存储过程的名称, argument是参数名, datatype是对应参数的数据类型, pl/sql_body是该存储过程真正进行的处理操作的PL/SQL块, OR REPLACE是可选项,如果存在一个同名的存储过程,则先删除后创建, 关键字IS|AS是等价的,用来引出过程体。 1)存储过程的参数 创建存储过程 CREATE OR REPLACE PROCEDURE UpdateAuths( p_AuthsCode auths.author_code%TYPE, p_AuthsSalary auths.salary%TYPE) AS BEGIN UPDATE auths SET salary=p_AuthsSalary WHERE author_code=p_AuthsCode; COMMIT; END UpdateAuths; 调用存储过程 DECLARE v_authorcode auths.author_code%TYPE:='A0001'; v_salary auths.salary%TYPE:=350; BEGIN UpdateAuths(v_authorcode,v_salary); END; 形参和实参 p_AuthsSalary,p_AuthsCode为形式参数 v_authorcode,v_salary为实际参数 关键字代表参数的三种不同模式 IN:当调用存储过程的时候,该模式的形参接收对应实参的值,并且该形参是只读的,即不能被修改。如果在创建存储过程时没有指定参数的模式,则默认为IN。 OUT:在存储过程中,该形参被认为是只能写,即只能为其赋值。在存储过程中不能读它的值。返回时,将该形参值传给相应的实参。 IN OUT:该模式是前两种模式的合并。 2)存储过程的处理部分 一个PL/SQL块,该块包含定义部分、可执行部分以及异常处理部分,其中可执行部分是必须有的。 CREATE OR REPLACE 过程名 ( --参数定义部分 ) IS --局部变量定义部分 BEGIN --可执行部分 EXCEPTION --异常处理部分 END 过程名; 在END后加上存储过程名是可选的,只是为了增加程序的可读性。如果加上,则必须要和前面的存储过程名相同。 1.2存储过程的参数 1)参数的数据类型 在定义一个存储过程参数时,不能指定CHAR类型和VARCHAR2类型形参的长度,也不能指定NUMBER形参的精度和标度。这些约束有实参来传递。 CREATE OR REPLACE PROCEDURE proc_auths( --参数定义了类型长度,这将产生编译错误。 p_code IN OUT VARCHAR2(6), P_salary OUT NUMBER(8,2)) AS BEGIN ... END proc_auths; 应修改为: CREATE OR REPLACE PROCEDURE proc_auths( p_code IN OUT VARCHAR2, P_salary OUT NUMBER) AS BEGIN SELECT salary INTO p_salary FROM auths WHERE author_code=p_code; END proc_auths; 调用存储过程: DECLARE v_code VARCHAR2(6); v_salary NUMBER(8,2); BEGIN v_code:='A0001'; proc_auths(v_code,v_salary); END; 如果使用%TYPE为参数定义类型,那么该参数将具有定义在形参上而不是通过实参传递的数据长度。 CREATE OR REPLACE PROCEDURE query_salary( p_code IN OUT auths.author_code%TYPE, P_salary OUT auths.salary%TYPE) AS BEGIN ... END query_salary; 如上面的存储过程,由于auths表中的author_code字段长度为6,因此p_code的长度也为6。 2)参数的传值方式 位置表示法 实参通过位置与形参进行联系。 名称表示法 实参是与形参的名称进行联系。 定义一个存储过程 CREATE OR REPLACE PROCEDURE insert_auths( p_code auths.author_code%TYPE, p_name auths.name%TYPE) AS BEGIN INSERT INTO auths(author_code,name) VALUES(p_code,p_name); END insert_auths; DECLARE v_code VARCHAR2(6); v_name VARCHAR2(12); BEGIN v_code:='A0001'; v_name:='张三'; --使用位置表示法调用 insert_auths(v_code,v_name); --使用命名表示法调用 insert_auths(p_name-->v_name,p_code-->v_code); END; 两种表示法可以混合使用。但是,当调用存储过程中出现了第一个命名表示法的参数时,后面的参数也必须使用命名表示法传值。 3)参数的缺省值 类似于变量的声明,一个过程或函数的形参可以有缺省值。如果参数有缺省值,那么在调用时就可以不用给它传值,只使用缺省值。如果给它传值,则实参的值代替缺省的值。 参数缺省值声明如下: parameter [mode] datatype {:=|DEFAULT} initial_value parameter是形参名称 mode是参数模式 datatype是参数的类型 initial_value用来为形参指定缺省值 使用关键字DEFAULT或":="来指定一个缺省值。 CREATE OR REPLACE PROCEDURE insert_auths( p_code auths.author_code%TYPE :='A0001', p_name auths.name%TYPE DEFAULT '张三' ) AS BEGIN ... END insert_auths;
2、创建函数
函数和存储过程非常类型,都有三种模式的参数。他们都可以被存储在数据库中,并且在快中调用。 存储过程只能作为一个PL/SQL语句调用,而函数作为表达式的一部分调用。并且它们的定义部分、可执行部分和异常处理部分都是不同的。 2.1 创建函数的语法 CREATE [OR REPLACE] FUNCTION schema.function [(argument[{IN|OUT|IN OUT}]datatype[,...])] RETURN return_datatype {IS|AS} PL/SQL_body; function是函数名 argument是参数名 datatype是参数的类型 return_datatype是函数返回值的类型 PL/SQL_body是函数的处理部分 参数列表时可选的。在没有参数的情况下,函数的定义与调用都没有圆括号。但返回值类型是必需的,因为函数是作为表达式的一部分调用,必须返回一个值。 CREATE OR REPLACE FUNCTION SalaryStat( p_Sex auths.sex%TYPE) RETURN BOOLEAN IS v_MaxAuthors NUMBER; v_ReturnValue BOOLEAN; BEGIN SELECT COUNT(author_code) INTO v_MaxAuthors FROM auths; IF v_MaxAuthors> 10 THEN v_ReturnValue:=TRUE; ELSE v_ReturnValue:=FALSE; END IF; RETURN v_ReturnValue; END SalaryStat; DECLARE CURSOR c_Auths IS SELECT distinet sex FROM auths; BEGIN FOR v_AuthsRecord IN c_Auths LOOP IF SalaryStat(v_AuthsRecord.sex) THEN UPDATE auths SET salary = salary-50 WHERE sex=v_AuthsRecord.sex; END IF; END LOOP; END; 2.2函数的返回值 在函数内,是通过RETURN语句来返回值的。 RETURN expression; expression是准备返回的值。如果expression的类型和函数头中指定的类型不符,expression类型会自动转换。RETURN语句被执行后,控制权立刻返回给调用环境。 在函数体重可以有多条RETURN语句,但还只能有一条被执行。在函数结束的时候,如果没有执行RETURN语句会产生错误。 CREATE OR REPLACE FUNCTION SalaryStat( p_Sex auths.sex%TYPE) RETURN VARCHAR2 IS v_MaxAuthors NUMBER; BEGIN SELECT COUNT(author_code) INTO v_MaxAuthors FROM auths; IF v_MaxAuthors> 10 THEN RETURN v_ReturnValue; ELSE RETURN v_ReturnValue; END IF; END SalaryStat; RETURN也可以用在存储过程中。在这种情况下,它没有参数。当执行了不带参数的RETURN语句后,立刻将控制权返回到调用环境,并将OUT和IN OUT模式的形参的当前值传给实参,然后继续执行调用存储过程后的语句。 函数和存储过程的相同点: 都可以通过OUT模式的参数返回一个活多个值。 代码都有定义部分、可执行部分和异常部分。 都可以使用缺省值。 都可以用位置表示法和命名表示法调用。 一般情况下,如果只有一个返回值,使用函数;如果有多个返回值则使用存储过程。
3、删除过程与函数
删除过程 DROP PROCEDURE procedure_name; 删除函数 DROP FUNCTION function_name;
4、库存子程序和局部子程序
前面所讲的子程序都是存储在数据库中的子程序,即库存子程序。这些子程序都是有CREATE命令创建的,并可在其它的PL/SQL快中调用。他们在创建时要要进行编译,并将编译后的代码存储在数据库中。当子程序被调用时,编译后的代码从数据库中读出并执行。 一个子程序也可以在块的定义部分创建,这样的子程序被叫作局部子程序。 DECLARE CURSOR c_AllAuthors IS SELECT name,sex FROM auths; v_FormattedName VARCHAR2(60); /*函数将返回带性别的作家名,性别用括号括起放在名字的后面*/ FUNCTION FormatName(p_Name IN VARCHAR2,p_Sex IN NUMBER) RETURN VARCHAR2 IS v_Sex VARCHAR2(16); BEGIN IF p_Sex=1 THEN v_Sex:='男'; ELSE v_Sex:='女'; END IF; RETURN p_Name ||'('||v_Sex||')'; END FormatName; --块的执行部分开始 BEGIN FOR v_AuthsRecord IN c_AllAuthors LOOP v_FormattedName:=FormatName(v_AuthsRecord.name,v_AuthsRecord.sex); DBMS_OUTPUT.PUT_LINE(v_FormattedName);--显示结果 END LOOP; END; 上面的示例,在无名块的定义部分穿件了FormatName函数。这个函数只在创建它的块中可用,它的作用域从创建它开始到块结束,其他的块不能调用它。 局部子程序只能在定义部分的最后被创建,如将FormatName函数移到c_AllAuthors的前面,会出现编译错误。
作者:何海洋
本博客内容主要以学习、研究和分享为主,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文链接,否则保留追究法律责任的权利。