第10章 存储过程、函数和包

第10章存储过程、函数和包

存储过程(PROCEDURE)、函数(FUNCTION)和包(PAKAGE)是以编译的形式存储在数据库中的数据库的对象，并成为数据库的一部分，可作为数据库的对象通过名字被调用和访问。

存储过程通常是实现一定功能的模块；函数通常用于计算，并返回计算结果；包分为包头和包体；用于捆绑存放相关的存储过程和函数，起到对模块归类打包的作用。

存储过程、函数和包是数据库应用程序开发的重要方法，三者既有区别，也有联系。

?存储过程和存储函数。

?过程的参数和调用。

?包和包的应用。

10.1 存储过程和函数

存储过程和函数也是一种PL/SQL块，是存入数据库的PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程序，我们通常把PL/SQL程序称为无名块，而存储过程和函数是以命名的方式存储于数据库中的。

10.1.1 认识存储过程和函数

和PL/SQL程序相比，存储过程有很多优点，具体归纳如下：

·存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的，因为代码不保存在本地，用户可以在任何客户机上登录到数据库，并调用或修改代码。

·存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要有存储过程和函数的所有者的授权，只有被授权的用户或创建者本身才能执行存储过程或调用函数。

·存储过程和函数的信息是写入数据字典的，所以存储过程可以看作是一个公用模块，用户编写的PL/SQL程序或其他存储过程都可以调用它(但存储过程和函数不能调用PL/SQL 程序)。一个重复使用的功能，可以设计成为存储过程，比如：显示一张工资统计表，可以设计成为存储过程；一个经常调用的计算，可以设计成为存储函数；根据雇员编号返回雇员的姓名，可以设计成存储函数。

·像其他高级语言的过程和函数一样，可以传递参数给存储过程或函数，参数的传递也有多种方式。存储过程可以有返回值，也可以没有返回值，存储过程的返回值必须通过参数带回；函数有一定的数据类型，像其他的标准函数一样，我们可以通过对函数名的调用返回函数值。

存储过程和函数需要进行编译，以排除语法错误，只有编译通过才能调用。

10.1.2 创建和删除存储过程

创建存储过程，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限。该权限可由系统管理员授予。创建一个存储过程的基本语句如下：

CREATE [OR REPLACE] PROCEDURE 存储过程名

[(参数[IN|OUT|IN OUT]数据类型…)]

{AS|IS}

[说明部分]

BEGIN

可执行部分

[EXCEPTION

错误处理部分]

END [过程名]；

其中：

可选关键字OR REPLACE表示如果存储过程已经存在，则用新的存储过程覆盖，通常用于存储过程的重建。

参数部分用于定义多个参数(如果没有参数，就可以省略)。参数有三种形式：IN、OUT 和IN OUT。如果没有指明参数的形式，则默认为IN。

关键字AS也可以写成IS，后跟过程的说明部分，可以在此定义过程的局部变量。

编写存储过程可以使用任何文本编辑器或直接在SQL *Plus环境下进行，编写好的存储过程必须要在SQL *Plus环境下进行编译，生成编译代码，原代码和编译代码在编译过程中都会被存入数据库。编译成功的存储过程就可以在Oracle环境下进行调用了。

一个存储过程在不需要时可以删除。删除存储过程的人是过程的创建者或者拥有DROP ANY PROCEDURE系统权限的人。删除存储过程的语法如下：

DROP PROCEDURE 存储过程名；

如果要重新编译一个存储过程，则只能是过程的创建者或者拥有ALTER ANY PROCEDURE 系统权限的人。语法如下：

ALTER PROCEDURE 存储过程名 COMPILE；

执行(或调用)存储过程的人是过程的创建者或是拥有EXECUTE ANY PROCEDURE系统权限的人或是被拥有者授予EXECUTE权限的人。执行的方法如下：

方法1：

EXECUTE 模式名.存储过程名[(参数…)];

方法2：

BEGIN

模式名.存储过程名 [(参数…)]；

END；

传递的参数必须与定义的参数类型、个数和顺序一致(如果参数定义了默认值，则调用时可以省略参数)。参数可以是变量、常量或表达式，用法参见下一节。

如果是调用本账户下的存储过程，则模式名可以省略。要调用其他账户编写的存储过程，则模式名必须要添加。

以下是一个生成和调用简单存储过程的训练。注意要事先授予创建存储过程的权限。

【训练1】创建一个显示雇员总人数的存储过程。

步骤1：登录SCOTT账户(或学生个人账户)。

步骤2：在SQL *Plus输入区中，输入以下存储过程：

CREATE OR REPLACE PROCEDURE EMP_COUNT

AS

V_TOTAL NUMBER;

BEGIN

SELECT COUNT(*) INTO V_TOTAL FROM EMP;

DBMS_OUTPUT.PUT_LINE('雇员总人数为：'||V_TOTAL);

END;

步骤3：按“执行”按钮进行编译。

如果存在错误，对脚本进行修改，直到没有错误产生。

如果编译结果正确，将显示：

过程已创建。

步骤4：调用存储过程，在输入区中输入以下语句并执行：

EXECUTE MEP_COUNT

[说明] 在该训练中，V_TOTAL变量是存储过程定义的局部变量，用于接收查询到的雇员总人数。

[注意] 在SQL *Plus中输入存储过程，按“执行”按钮是进行编译，不是执行存储过程。

如果在存储过程中引用了其他用户的对象，比如表，则必须有其他用户授予的对象访问权限。一个存储过程一旦编译成功，就可以由其他用户或程序来引用。但存储过程或函数的所有者必须授予其他用户执行该过程的权限。

存储过程没有参数，在调用时，直接写过程名即可。

【训练2】在PL/SQL程序中调用存储过程。

步骤1：登录SCOTT账户。

步骤2：授权STUDENT账户使用该存储过程，即在SQL *Plus输入区中，输入以下的命令：

GRANT EXECUTE EMP_COUNT TO STUDENT

步骤3：登录STUDENT账户，在SQL *Plus输入区中输入以下程序：

SET SERVEROUTPUT ON

BEGIN

SCOTT.EMP_COUNT;

END;

步骤4：执行以上程序，结果为：

[说明] 在本例中，存储过程是由SCOTT账户创建的，STUDEN账户获得SCOTT账户

的授权后，才能调用该存储过程。

[注意] 在程序中调用存储过程，使用了第二种语法。

【训练3】编写显示雇员信息的存储过程EMP_LIST，并引用EMP_COUNT存储过程。

步骤1：在SQL *Plus输入区中输入并编译以下存储过程：

CREATE OR REPLACE PROCEDURE EMP_LIST

AS

CURSOR EMP_CURSOR IS

SELECT EMPNO,ENAME FROM EMP;

BEGIN

FOR EMP_RECORD IN EMP_CURSOR LOOP

DBMS_OUTPUT.PUT_LINE(EMP_RECORD.EMPNO||EMP_RECORD.ENAME);

END LOOP;

EMP_COUNT;

END;

步骤2：调用存储过程，在输入区中输入以下语句并执行：

EXECUTE EMP_LIST

[说明] 以上的EMP_LIST存储过程中定义并使用了游标，用来循环显示所有雇员的信息。然后调用已经成功编译的存储过程EMP_COUNT，用来附加显示雇员总人数。通过EXECUTE 命令来执行EMP_LIST存储过程。

[练习1] 编写显示部门信息的存储过程DEPT_LIST，要求统计出部门个数。

10.1.3 参数传递

参数的作用是向存储过程传递数据，或从存储过程获得返回结果。正确的使用参数可以大大增加存储过程的灵活性和通用性。

参数的类型有三种，如表10-1所示。

表10-1 参数的类型

参数类型说明

IN 定义一个输入参数变量，用于传递参数给存储过程

OUT 定义一个输出参数变量，用于从存储过程获取数据

IN OUT 定义一个输入、输出参数变量，兼有以上两者的功能

参数名 IN 数据类型 DEFAULT 值;

定义一个输入参数变量，用于传递参数给存储过程。在调用存储过程时，主程序的实际参数可以是常量、有值变量或表达式等。DEFAULT关键字为可选项，用来设定参数的默认值。如果在调用存储过程时不指明参数，则参数变量取默认值。在存储过程中，输入变量接收主程序传递的值，但不能对其进行赋值。

参数名 OUT 数据类型;

定义一个输出参数变量，用于从存储过程获取数据，即变量从存储过程中返回值给主程序。在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。在存储过程中，参数变量只能被赋值而不能将其用于赋值，在存储过程中必须给输出变量至少赋值一次。

参数名 IN OUT 数据类型 DEFAULT 值;

定义一个输入、输出参数变量，兼有以上两者的功能。在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。DEFAULT关键字为可选项，用来设定参数的默认值。在存储过程中，变量接收主程序传递的值，同时可以参加赋值运算，也可以对其进行赋值。在存储过程中必须给变量至少赋值一次。

如果省略IN、OUT或IN OUT，则默认模式是IN。

以下是几个训练实例，以帮助读者学习和了解存储过程和函数的参数的使用和值的传递过程。

【训练4】编写给雇员增加工资的存储过程CHANGE_SALARY，通过IN类型的参数传递要增加工资的雇员编号和增加的工资额。

步骤1：登录SCOTT账户。

步骤2：在SQL *Plus输入区中输入以下存储过程并执行：

CREATE OR REPLACE PROCEDURE CHANGE_SALARY(P_EMPNO IN NUMBER DEFAULT 7788,

P_RAISE NUMBER DEFAULT 10)

AS

V_ENAME VARCHAR2(10);

V_SAL NUMBER(5);

BEGIN

SELECT ENAME,SAL INTO V_ENAME,V_SAL

FROM EMP

WHERE EMPNO=P_EMPNO;

UPDATE EMP

SET SAL=SAL+P_RAISE

WHERE EMPNO=P_EMPNO;

DBMS_OUTPUT.PUT_LINE('雇员'||V_ENAME

||'的工资被改为'||TO_CHAR(V_SAL+P_RAISE));

COMMIT;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('发生错误,修改失败!');

ROLLBACK;

END;

步骤3：调用存储过程，在输入区中输入以下语句并执行：

EXECUTE CHANGE_SALARY(7788,80)

[说明] 从执行结果可以看到，雇员SCOTT的工资已由原来的3000改为3080。

参数的值由调用者传递，传递的参数的个数、类型和顺序应该和定义的一致。如果顺序不一致，可以采用以下调用方法。如上例，执行语句可以改为：

EXECUTE CHANGE_SALARY(P_RAISE=>80,P_EMPNO=>7788);

可以看出传递参数的顺序发生了变化，并且明确指出了参数名和要传递的值, =>运算符左侧是参数名，右侧是参数表达式，这种赋值方法的意义较清楚。

[练习1] 创建插入雇员的存储过程INSERT_EMP，并将雇员编号等作为参数。

在设计存储过程的时候，也可以为参数设定默认值，这样调用者就可以不传递或少传递参数了。

【训练5】调用存储过程CHANGE_SALARY，不传递参数，使用默认参数值。

在SQL *Plus输入区中输入以下命令并执行：

EXECUTE CHANGE_SALARY

[说明] 在存储过程的调用中没有传递参数，而是采用了默认值7788和10，即默认雇员号为7788，增加的工资为10。

【训练6】使用OUT类型的参数返回存储过程的结果。

步骤1：登录SCOTT账户。

步骤2：在SQL *Plus输入区中输入并编译以下存储过程：

CREATE OR REPLACE PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)

AS

BEGIN

SELECT COUNT(*) INTO P_TOTAL FROM EMP;

END;

步骤3：输入以下程序并执行：

DECLARE

V_EMPCOUNT NUMBER;

BEGIN

EMP_COUNT(V_EMPCOUNT);

DBMS_OUTPUT.PUT_LINE('雇员总人数为:'||V_EMPCOUNT);

END;

[说明] 在存储过程中定义了OUT类型的参数P_TOTAL，在主程序调用该存储过程时，传递了参数V_EMPCOUNT。在存储过程中的SELECT…INTO…语句中对P_TOTAL进行赋值，赋值结果由V_EMPCOUNT变量带回给主程序并显示。

以上程序要覆盖同名的EMP_COUNT存储过程，如果不使用OR REPLACE选项，就会出现以下错误：

ERROR 位于第1行：

ORA-00955: 名称已由现有对象调用。

[练习2] 创建存储过程，使用OUT类型参数获得雇员经理名。

【训练7】使用IN OUT类型的参数，给电话号码增加区码。

步骤1：登录SCOTT账户。

步骤2：在SQL *Plus输入区中输入并编译以下存储过程：

CREATE OR REPLACE PROCEDURE ADD_REGION(P_HPONE_NUM IN OUT VARCHAR2) AS

BEGIN

P_HPONE_NUM:='0755-'||P_HPONE_NUM;

END;

步骤3：输入以下程序并执行：

SET SERVEROUTPUT ON

DECLARE

V_PHONE_NUM VARCHAR2(15);

BEGIN

V_PHONE_NUM:='26731092';

ADD_REGION(V_PHONE_NUM);

DBMS_OUTPUT.PUT_LINE('新的电话号码: '||V_PHONE_NUM);

END;

[说明] 变量V_HPONE_NUM既用来向存储过程传递旧电话号码，也用来向主程序返回新号码。新的号码在原来基础上增加了区号0755和-。

10.1.4 创建和删除存储函数

创建函数，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限。该权限可由系统管理员授予。创建存储函数的语法和创建存储过程的类似，即

CREATE [OR REPLACE] FUNCTION 函数名[(参数[IN] 数据类型…)]

RETURN 数据类型

{AS|IS}

[说明部分]

BEGIN

可执行部分

RETURN (表达式)

[EXCEPTION

错误处理部分]

END [函数名];

其中，参数是可选的，但只能是IN类型(m关键字可以省略)。

在定义部分的RETURN数据类型，用来表示函数的数据类型，也就是返回值的类型，此部分不可省略。

在可执行部分的RETURN(表达式)，用来生成函数的返回值，其表达式的类型应该和定义部分说明的函数返回值的数据类型一致。在函数的执行部分可以有多个RETURN语句，但只有一个RETURN语句会被执行，一旦执行了RETURN语句，则函数结束并返回调用环境。

一个存储函数在不需要时可以删除，但删除的人应是函数的创建者或者是拥有DROP ANY PROCEDURE系统权限的人。其语法如下：

DROP FUNCTION 函数名;

重新编译一个存储函数时，编译的人应是函数的创建者或者拥有ALTER ANY PROCEDURE 系统权限的人。重新编译一个存储函数的语法如下：

ALTER PROCEDURE 函数名 COMPILE;

函数的调用者应是函数的创建者或拥有EXECUTE ANY PROCEDURE系统权限的人，或是被函数的拥有者授予了函数执行权限的账户。函数的引用和存储过程不同，函数要出现在程序体中，可以参加表达式的运算或单独出现在表达式中，其形式如下：

变量名:=函数名(…)

【训练8】创建一个通过雇员编号返回雇员名称的函数GET_EMP_NAME。

--步骤1；登录SCOTT账户。

--步骤2：在SQL *Plus输入区中输入以下存储函数并编译：

CREATE OR REPLACE FUNCTION GET_EMP_NAME(P_EMPNO NUMBER DEFAULT 7788)

RETURN VARCHAR2

AS

V_ENAME VARCHAR2(10);

BEGIN

SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=P_EMPNO;

RETURN(V_ENAME);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('没有该编号雇员!');

RETURN(NULL);

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE('有重复雇员编号!');

RETURN(NULL);

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('发生其他错误!');

RETURN(NULL);

END;

--步骤3：调用该存储函数，输入并执行以下程序：

BEGIN

DBMS_OUTPUT.PUT_LINE('雇员7369的名称是: '||GET_EMP_NAME(7369));

DBMS_OUTPUT.PUT_LINE('雇员7839的名称是: '||GET_EMP_NAME(7839));

END;

[说明] 函数的调用直接出现在程序的DBMS_OUTPUT.PUT_LINE语句中,作为字符串表达式的一部分。如果输入了错误的雇员编号，就会在函数的错误处理部分输出错误信息。试修改雇员编号，重新运行调用部分。

[练习1] 创建一个通过部门编号返回部门名称的存储函数GET_DEPT_NAME。

[练习2] 将函数的执行权限授予STUDENT账户，然后登录STUDENT账户调用。

10.1.5 存储过程和函数的查看

可以通过对数据字典的访问来查询存储过程或函数的有关信息，如果要查询当前用户的存储过程或函数的源代码，可以通过对USER_SOURCE数据字典视图的查询得到。USER_SOURCE的结构如下：

DESCRIBE USER_SOURCE

[说明] 里面按行存放着过程或函数的脚本，NAME是过程或函数名，TYPE代表类型(PROCEDURE或FUNCTION)，LINE是行号，TEXT为脚本。

【训练9】查询过程EMP_COUNT的脚本。在SQL *PLUS中输入并执行如下查询: SELECT TEXT FROM USER_SOURCE WHERE NAME='EMP_COUNT';

如果要查询过程或函数的名字和参数，可以用如下方法。

【训练10】查询过程GET_EMP_NAME的参数。

DESCRIBE GET_EMP_NAME

在存储过程或函数的编译过程中，如果发生错误，可以使用SHOW ERRORS命令显示出错的细节。

【训练11】在发生编译错误时，显示错误。

SHOW ERROR

[说明] 查询一个存储过程或函数是否是有效状态(即编译成功)，可以使用数据字典USER_OBJECTS的STATUS列。

【训练12】查询EMP_LIST存储过程是否可用：

SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME='EMP_LIST';

[说明] VALID表示该存储过程有效(即通过编译)，INVALID表示存储过程无效或需要重新编译。当Oracle调用一个无效的存储过程或函数时，首先试图对其进行编译，如果编译成功则将状态置成VALID并执行，否则给出错误信息。

当一个存储过程编译成功，状态变为VALID，会不会在某些情况下变成INVALD。结论是完全可能的。比如一个存储过程中包含对表的查询，如果表被修改或删除，存储过程就会变成无效INVALID。所以要注意存储过程和函数对其他对象的依赖关系。

如果要检查存储过程或函数的依赖性，可以通过查询数据字典USER_DENPENDENCIES来确定，该表结构如下：

DESCRIBE USER_DEPENDENCIES;

[说明] NAME为实体名，TYPE为实体类型，REFERENCED_OWNER为涉及到的实体拥有者账户，REFERENCED_NAME为涉及到的实体名，REFERENCED_TYPE为涉及到的实体类型。

【训练13】查询EMP_LIST存储过程的依赖性。

SELECT REFERENCED_NAME,REFERENCED_TYPE

FROM USER_DEPENDENCIES

WHERE NAME='EMP_LIST';

[说明] 可以看出存储过程EMP_LIST依赖一些系统包、EMP表和EMP_COUNT存储过程。如果删除了EMP表或EMP_COUNT存储过程，EMP_LIST将变成无效。

还有一种情况需要我们注意：如果一个用户A被授予执行属于用户B的一个存储过程的权限，在用户B的存储过程中，访问到用户C的表，用户B被授予访问用户C的表的权限，但用户A没有被授予访问用户C表的权限，那么用户A调用用户B的存储过程是失败的还是成功的呢?答案是成功的。如果读者有兴趣，不妨进行一下实际测试。

10.2 包

包(PACKAGE)是一种规范的程序设计方法，是将相关的程序对象存储在一起的PL/SQL 结构。通过将相关对象组织在一起，程序就会有清晰的结构。包的方法减少了依赖性的限制，具有许多性能上的优点。

10.2.1 包的概念和组成

包是用来存储相关程序结构的对象，它存储于数据字典中。包由两个分离的部分组成：包头(PACKAGE)和包体(PACKAGE BODY)。包头是包的说明部分，是对外的操作接口，对应用是可见的；包体是包的代码和实现部分，对应用来说是不可见的黑盒。

包中可以包含的程序结构如表10-2所示。

表10-2 包中包含的程序结构

程序结构说明

过程(PROCUDURE) 带参数的命名的程序模块

函数(FUNCTION) 带参数、具有返回值的命名的程序模块

变量(VARIABLE) 存储变化的量的存储单元

常量(CONSTANT) 存储不变的量的存储单元

游标(CURSOR) 用户定义的数据操作缓存区，在可执行部分使用

类型(TYPE) 用户定义的新的结构类型

异常(EXCEPTION) 在标准包中定义或由用户自定义，用于处理程序错误说明部分可以出现在包的三个不同的部分：出现在包头中的称为公有元素，出现在包体中的称为私有元素，出现在包体的过程(或函数)中的称为局部变量。它们的性质有所不同，如表10-3所示。

在包体中出现的过程或函数，如果需要对外公用，就必须在包头中说明，包头中的说明应该和包体中的说明一致。

包有以下优点：

·包可以方便地将存储过程和函数组织到一起，每个包又是相互独立的。在不同的包中，过程、函数都可以重名，这解决了在同一个用户环境中命名的冲突问题。

·包增强了对存储过程和函数的安全管理，对整个包的访问权只需一次授予。

·在同一个会话中，公用变量的值将被保留，直到会话结束。

·区分了公有过程和私有过程，包体的私有过程增加了过程和函数的保密性。

·包在被首次调用时，就作为一个整体被全部调入内存，减少了多次访问过程或函数的I/O次数。

10.2.2 创建包和包体

包由包头和包体两部分组成，包的创建应该先创建包头部分，然后创建包体部分。创建、删除和编译包的权限同创建、删除和编译存储过程的权限相同。

创建包头的简要语句如下：

CREATE [OR REPLACE] PACKAGE 包名

{IS|AS}

公有变量定义

公有类型定义

公有游标定义

公有异常定义

函数说明

过程说明

END；

创建包体的简要语法如下：

CREATE [OR REPLACE] PACKAGE BODY 包名

{IS|AS}

私有变量定义

私有类型定义

私有游标定义

私有异常定义

函数定义

过程定义

END；

包的其他操作命令包括：

删除包头：

DROP PACKAGE 包头名

删除包体：

DROP PACKAGE BODY 包体名

重新编译包头：

ALTE RPACKAGE 包名 COMPILE PACKAGE

重新编译包体：

ALTE RPACKAGE 包名 COMPILE PACKAGE BODY

在包头中说明的对象可以在包外调用，调用的方法和调用单独的过程或函数的方法基本相同，惟一的区别就是要在调用的过程或函数名前加上包的名字(中间用“.”分隔)。但

要注意，不同的会话将单独对包的公用变量进行初始化，所以不同的会话对包的调用属于不同的应用。

10.2.3 系统包

Oracle预定义了很多标准的系统包，这些包可以在应用中直接使用，比如在训练中我们使用的DBMS_OUTPUT包，就是系统包。PUT_LINE是该包的一个函数。常用系统包如表10-4所示。

表10-4 常用系统包

10.2.4 包的应用

在SQL *Plus环境下，包和包体可以分别编译，也可以一起编译。如果分别编译，则要先编译包头，后编译包体。如果在一起编译，则包头写在前，包体在后，中间用“/”分隔。

可以将已经存在的存储过程或函数添加到包中，方法是去掉过程或函数创建语句的CREATE OR REPLACE部分，将存储过程或函数复制到包体中，然后重新编译即可。

如果需要将私有过程或函数变成共有过程或函数的话，将过程或函数说明部分复制到包头说明部分，然后重新编译就可以了。

【训练1】创建管理雇员信息的包EMPLOYE，它具有从EMP表获得雇员信息，修改雇员名称，修改雇员工资和写回EMP表的功能。

--步骤1：登录SCOTT账户，输入以下代码并编译：

CREATE OR REPLACE PACKAGE EMPLOYE --包头部分

IS

PROCEDURE SHOW_DETAIL;

PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER);

PROCEDURE SAVE_EMPLOYE;

PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2);

PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER);

END EMPLOYE;

/

CREATE OR REPLACE PACKAGE BODY EMPLOYE --包体部分

IS

EMPLOYE EMP%ROWTYPE;

----显示雇员信息----

PROCEDURE SHOW_DETAIL

AS

BEGIN

DBMS_OUTPUT.PUT_LINE('---雇员信息---');

DBMS_OUTPUT.PUT_LINE('雇员的编号:'||EMPLOYE.EMPNO);

DBMS_OUTPUT.PUT_LINE('雇员的姓名:'||EMPLOYE.ENAME);

DBMS_OUTPUT.PUT_LINE('雇员的职务:'||EMPLOYE.JOB);

DBMS_OUTPUT.PUT_LINE('雇员的工资:'||EMPLOYE.SAL);

DBMS_OUTPUT.PUT_LINE('部门的编号:'||EMPLOYE.DEPTNO);

END SHOW_DETAIL;

----从EMP表取得一个雇员----

PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER)

AS

BEGIN

SELECT * INTO EMPLOYE FROM EMP WHERE EMPNO=P_EMPNO;

DBMS_OUTPUT.PUT_LINE('获取雇员'||EMPLOYE.ENAME||'信息成功'); EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('获取雇员信息发生错误!');

END GET_EMPLOYE;

----保存雇员到EMP表----

PROCEDURE SAVE_EMPLOYE

AS

BEGIN

UPDATE EMP

SET ENAME=EMPLOYE.ENAME,SAL=EMPLOYE.SAL

WHERE EMPNO=EMPLOYE.EMPNO;

DBMS_OUTPUT.PUT_LINE('雇员信息保存完成!');

END SAVE_EMPLOYE;

----修改雇员名称----

PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2)

AS

BEGIN

EMPLOYE.ENAME:=P_NEWNAME;

DBMS_OUTPUT.PUT_LINE('修改名称完成!');

END CHANGE_NAME;

----修改雇员工资----

PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER)

AS

BEGIN

EMPLOYE.SAL:=P_NEWSAL;

DBMS_OUTPUT.PUT_LINE('修改工资完成!');

END CHANGE_SAL;

END EMPLOYE;

--步骤2: 获取雇员7788的信息:

SET SERVEROUTPUT ON

EXECUTE EMPLOYE.GET_EMPLOYE(7788);

--步骤3: 显示雇员信息:

EXECUTE EMPLOYE.SHOW_DETAIL;

--步骤4: 修改雇员工资:

EXECUTE EMPLOYE.CHANGE_SAL(3800);

--步骤5: 将修改的雇员信息存入EMP表:

EXECUTE EMPLOYE.SAVE_EMPLOYE;

[说明] 该包完成将EMP表中的某个雇员的信息取入内存记录变量，在记录变量中进行修改编辑，在确认显示信息正确后写回EMP表的功能。记录变量EMPLOYE用来存储取得的雇员信息，定义为私有变量，只能被包的内部模块访问。

[练习1] 为包增加修改雇员职务和部门编号的功能。

第9章 存储过程与存储函数

第9章存储过程与存储函数 一、选择题 1．MySQL中存储过程的建立以关键字（）开始，后面仅跟存储过程的名称和参数。A．CREATE FUNCTION B．CREATE TRIGGER C．CREATE PROCEDURE D．CREATE VIEW 2．下列关于存储过程名描述错误的是（）。 A．MySQL的存储过程名称不区分大小写。 B．MySQL的存储过程名称区分大小写。 C．存储过程名不能与MySQL数据库中的内置函数重名。 D．存储过程的参数名不要跟字段名一样。 3．下面声明变量正确的是（）。 A．DECLARE x char(10) DEFAULT 'outer ' B．DECLARE x char DEFAULT 'outer ' C．DECLARE x char(10) DEFAULT outer D．DECLARE x DEFAULT 'outer ' 4．从tb_sutdent表中将名称为mrsoft的用户赋值给host，以下SQL语句正确的是（）。A．SELECT host INTO name FROM tb_sutdent WHERE name ='mrsoft'; B．SELECT name INTO host FROM tb_sutdent WHERE name= 'LeonSK '; C．SELECT name INTO host FROM tb_sutdent WHERE name='mrsoft'; D．SELECT name INTO host FROM tb_sutdent WHERE name=‘mrsoft’; 5．光标的一般使用步骤，以下正确的是（）。 A．声明光标使用光标打开光标关闭光标 B．打开光标声明光标使用光标关闭光标 C．声明光标打开光标选择光标关闭光标 D．声明光标打开光标使用光标关闭光标 6．下列控制流程语句中，MySQL存储过程不支持（）。 A．WHILE B．FOR C．LOOP D．REPEAT 25

oracle自定义函数和存储过程

oracle自定义函数和存储过程 oracle自定义函数和存储过程（转）2008-07-23 10:43--过程(PROCEDURE)--------------------------------------------------// --创建表 CREATE TABLE user_info ( id VARCHAR2(4), name VARCHAR2(15), pwd VARCHAR2(15), address VARCHAR2(30) ); --插入数据 INSERT INTO user_info VALUES('u001','zhao','zhao','shanghai'); --如要经常执行插入，Oracle每次都要进行编译，并判断语法正确性，因此执行速度可想而知， --所以我们要创建一个过程来实现 CREATE OR REPLACE PROCEDURE AddNewUser ( n_id user_info.id%TYPE, n_name user_https://www.wendangku.net/doc/0518427604.html,%TYPE, n_pwd user_info.pwd%TYPE, n_address user_info.address%TYPE ) AS BEGIN --向表中插入数据 INSERT INTO user_info(id,name,pwd,address) VALUES(n_id,n_name,n_pwd,n_address); END AddNewUser; / --下面我们利用PL/SQL匿名块调用该过程 DECLARE --描述新用户的变量 v_id user_info.id%TYPE := 'u002'; v_name user_https://www.wendangku.net/doc/0518427604.html,%TYPE := 'wish'; v_pwd user_info.pwd%TYPE := 'history'; v_add user_info.address%TYPE := 'shanghai'; BEGIN --调用过程，添加wish用户到数据库

第10章 存储过程、函数和包

第10章存储过程、函数和包 存储过程(PROCEDURE)、函数(FUNCTION)和包(PAKAGE)是以编译的形式存储在数据库中的数据库的对象，并成为数据库的一部分，可作为数据库的对象通过名字被调用和访问。 存储过程通常是实现一定功能的模块；函数通常用于计算，并返回计算结果；包分为包头和包体；用于捆绑存放相关的存储过程和函数，起到对模块归类打包的作用。 存储过程、函数和包是数据库应用程序开发的重要方法，三者既有区别，也有联系。 ?存储过程和存储函数。 ?过程的参数和调用。 ?包和包的应用。 10.1 存储过程和函数 存储过程和函数也是一种PL/SQL块，是存入数据库的PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程序，我们通常把PL/SQL程序称为无名块，而存储过程和函数是以命名的方式存储于数据库中的。 10.1.1 认识存储过程和函数 和PL/SQL程序相比，存储过程有很多优点，具体归纳如下： ·存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的，因为代码不保存在本地，用户可以在任何客户机上登录到数据库，并调用或修改代码。 ·存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要有存储过程和函数的所有者的授权，只有被授权的用户或创建者本身才能执行存储过程或调用函数。 ·存储过程和函数的信息是写入数据字典的，所以存储过程可以看作是一个公用模块，用户编写的PL/SQL程序或其他存储过程都可以调用它(但存储过程和函数不能调用PL/SQL 程序)。一个重复使用的功能，可以设计成为存储过程，比如：显示一张工资统计表，可以设计成为存储过程；一个经常调用的计算，可以设计成为存储函数；根据雇员编号返回雇员的姓名，可以设计成存储函数。 ·像其他高级语言的过程和函数一样，可以传递参数给存储过程或函数，参数的传递也有多种方式。存储过程可以有返回值，也可以没有返回值，存储过程的返回值必须通过参数带回；函数有一定的数据类型，像其他的标准函数一样，我们可以通过对函数名的调用返回函数值。 存储过程和函数需要进行编译，以排除语法错误，只有编译通过才能调用。

存储过程和函数的区别

存储过程与函数的区别 存储过程: 存储过程可以使得对数据库的管理、以及显示关于数据库及其用户信息的工作容易得多。存储过程是SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。 存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。 可以出于任何使用SQL 语句的目的来使用存储过程，它具有以下优点： 1、可以在单个存储过程中执行一系列SQL 语句。 2、可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。 3、存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL 语句快。 用户定义函数： Microsoft SQL Server 2000 允许创建用户定义函数。与任何函数一样，用户定义函数是可返回值的例程。根据所返回值的类型，每个用户定义函数可分成以下三个类别： 1、返回可更新数据表的函数 如果用户定义函数包含单个Select 语句且该语句可更新，则该函数返回的表格格式结果也可以更新。 2、返回不可更新数据表的函数 如果用户定义函数包含不止一个Select 语句，或包含一个不可更新的Select 语句，则该函数返回的表格格式结果也不可更新。 3、返回标量值的函数 用户定义函数可以返回标量值。 存储过程 功能强大，限制少 不能直接引用返回值 用select语句返回记录集 自定义函数 诸多限制，有许多语句不能使用，许多功能不能实现 可以直接引用返回值 用表变量返回记录集

触发器、存储过程和函数三者有何区别 四

触发器、存储过程和函数三者有何区别四 什么时候用存储过程？存储过程就是程序，它是经过语法检查和编译的SQL语句，所以运行特别快。 触发器是特殊的存储过程，存储过程需要程序调用，而触发器会自动执行；你所说的函数是自定义函数吧，函数是根据输入产生输出，自定义只不过输入输出的关系由用户来定义。在什么时候用触发器？要求系统根据某些操作自动完成相关任务，比如，根据买掉的产品的输入数量自动扣除该产品的库存量。什么时候用存储过程？存储过程就是程序，它是经过语法检查和编译的SQL语句，所以运行特别快。 存储过程和用户自定义函数具体的区别 先看定义： 存储过程 存储过程可以使得对数据库的管理、以及显示关于数据库及其用户信息的工作容易得多。存储过程是SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。 存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。 可以出于任何使用SQL 语句的目的来使用存储过程，它具有以下优点： ·可以在单个存储过程中执行一系列SQL 语句。 ·可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。·存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL 语句快。用户定义函数 函数是由一个或多个Transact-SQL 语句组成的子程序，可用于封装代码以便重新使用。Microsoft? SQL Server? 2000 并不将用户限制在定义为Transact-SQL 语言一部分的内置函数上，而是允许用户创建自己的用户定义函数。 可使用CREATE FUNCTION 语句创建、使用ALTER FUNCTION 语句修改、以及使用DROP FUNCTION 语句除去用户定义函数。每个完全合法的用户定义函数名(database_name.owner_name.function_name) 必须唯一。 必须被授予CREATE FUNCTION 权限才能创建、修改或除去用户定义函数。不是所有者的用户在Transact-SQL 语句中使用某个函数之前，必须先给此用户授予该函数的适当权限。若要创建或更改在CHECK 约束、DEFAULT 子句或计算列定义中引用用户定义函数的表，还必须具有函数的REFERENCES 权限。 在函数中，区别处理导致删除语句并且继续在诸如触发器或存储过程等模式中的下一语句的Transact-SQL 错误。在函数中，上述错误会导致停止执行函数。接下来该操作导致停止唤醒调用该函数的语句。 用户定义函数的类型

实验14 存储过程与函数

实验十四存储过程与函数 【实验目的与要求】 1．熟练掌握存储过程的编写。 2．熟练掌握函数的编写与使用。 【实验内容与步骤】 14.1.基础知识 存储过程（Stored Procedure）和函数是一组编译好存储在服务器上的完成特定功能T-SQL代码，是某数据库的对象。客户端应用程序可以通过指定存储过程或函数的名字并给出参数（如果该存储过程带有参数）来执行存储过程。 14.2.创建用户存储过程 1. 使用存储过程模板创建存储过程 在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开选择“可编程性”节点，右击“存储过程”，选择“新建存储过程”命令，如图所示： 在右侧查询编辑器中出现存储过程的模板，可以在此基础上编辑存储过程，单击“执行”按钮，即可创建该存储过程。

实验14-1：创建一个简单的存储过程，实现从CP表中读取Mp3产品相关信息USE CPXS GO CREATE PROCEDURE get_mp3 --此为无参存储过程 AS SELECT产品编号,产品名称,价格,库存量 FROM CP WHERE产品名称='mp3' 实验14-2：执行存储过程 执行存储过程可用下列方法之一： （1）使用存储过程名字如：get_mp3 （2) 使用Exec命令：如：EXEC get_mp3 执行上面创建的存储过程，并给出执行结果：

2. 使用T-SQL语句创建存储过程 在查询分析器里使用T-SQL可直接创建存储过程格式： CREATE PROC 过程名 @形参名类型 @变参名类型OUTPUT AS SQL语句 实验14-3：创建一个多表查询的存储过程。 问题：查询在2009年9月18日有销售的产品名称（1）请给出相应的代码 create proc get_cp as select产品名称 from cp,cpxsb where销售日期='2009-9-18' （2）执行存储过程,并给出执行结果：

存储过程和函数的区别

存储过程和函数的区别 存储过程是用户定义的一系列sql语句的集合，涉及特定表或其它对象的任务，用户可以调用存储过程，而函数通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。 . l 视图的优点？建立视图的基本语法结构？ 视图的优点： 1. 视图对于数据库的重构造提供了一定程度的逻辑独立性。数据的逻辑独立性是指数据库重构造时，如数据库扩大(增加了新字段，新关系等)，用户和用户程序不会受影响。 2. 简化了用户观点。视图的机制使用户把注意力集中在他所关心的数据上。若这些数据不是直接来自基本表，则可以定义视图，从而使用户眼中的数据结构简单而直接了当，并可大大简化用户的数据查询操作，特别是把若干表连接在一起的视图，把从表到表所需要的连接操作向用户隐蔽了起来。 3. 视图机制使不同的用户能以不同的方式看待同一数据。 4. 视图机制对机密数据提供了自动的安全保护功能。可以把机密数据从公共的数据视图(基本表)中分离出去，即针对不同用户定义不同的视图，在用户视图中不包括机密数据的字段。这样，这类数据便不能经由视图被用户存取，从而自动地提供了对机密数据的保护。 视图的基本语法结构： CREATE VIEW view_name [(column ][,...n])] AS select_statement 其中view_name为要建立的视图的名称，而AS子句后面的就是建立视图的查询语句。而此语句有以下限制：不能包含ORDER BY、COMPUTE和COMPUTE BY等子句；不能包含INTO 关键字；不能涉及临时表。 . l 事务是什么？ 事务是作为一个逻辑单元执行的一系列操作，一个逻辑工作单元必须有四个属性，称为ACID （原子性、一致性、隔离性和持久性）属性，只有这样才能成为一个事务： 1、原子性：事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。 2、一致性：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规

数据库函数、存储过程实验报告

南京信息工程大学数据库系统实验（实习）报告实验（实习）名称数据库系统实验4 实验（实习）日期2016-6-7 得分指导教师顾韵华 系计软院专业计科年级2014级班次计科 3 班姓名仇彤学号20141308071 一、实验目 1、掌握T-SQL函数及其调用方法。 2、掌握存储过程的定义及执行方法。 3、掌握有参存储过程的定义及执行方法。 4、掌握C#访问数据库的方法。 二、实验内容 1、使用系统函数(DA TEDIFF(d,date_expr1,date_expr2))，计算今天距离“2020-1-1”还剩多少天。（P299"思考与练习"） 2、编写T-SQL程序，利用系统转换函数，检索总订购商品数在10～19的客户姓名。 3、定义函数RectArea，计算一个长方形的面积（长、宽作为函数的参数）。 4、在SPDG数据库中定义函数，根据商品编号，查询该商品的名称；（函数名为QryGoods）。 5、在SPDG数据库中定义存储过程GetSPBH，返回所有商品编号，并使用EXEC语句执行存储过程。 6、在SPDG数据库中定义存储过程KH_NJ_Qry，返回江苏南京的客户编号、姓名、及其订购商品的编号、商品名称和数量，并使用EXEC语句执行存储过程。 7、在SPDG数据库中定义存储过程SP_FOOD_Qry，返回食品类商品编号、商品名称及其订购客户编号、姓名、订购数量，并使用EXEC语句执行存储过程。 8、定义存储过程SP_Total，查询指定商品编号的总订购数。并执行该存储过程。 9、定义存储过程SP_TotalCost，查询指定商品编号的总订购金额。并执行该存储过程。 10、定义存储过程SP_Name_Qry，查询指定商品名称的商品信息。并执行该存储过程。 11、定义存储过程SP_Name_Qry1，查询指定商品名称的商品信息；若存在，输出1；否则，输出0。并执行该存储过程。 12、定义存储过程SP_Name_Qry2，查询指定商品名称的商品信息；若存在，用输出参数传出1；否则传出0。 三、实验过程与结果 1、使用系统函数(DATEDIFF(d,date_expr1,date_expr2))，计算今天距离“2020-1-1”还剩多少天。（P299"思考与练习"） 设计的SQL语句如下： print datediff(d,getdate(),'2020-1-1') 执行结果：

Removed_数据库-存储过程触发器和函数实验报告

存储过程、触发器和用户自定义函数实验 兰州大学数据库实验报告实验内容一 练习教材中存储过程、触发器和用户自定义函数的例子。教材中的BookSales数据库，在群共享中，文件名为BookSales.bak。 实验内容二 针对附件1中的教学活动数据库，完成下面的实验内容。 1、存储过程 （1）创建一个存储过程，该存储过程统计“高等数学”的成绩分布情况，即按照各分数段统计人数。 CREATE Proc MATH_NUM@MATH CHAR(20)='高等数学' AS SELECT@MATH as canme,count(case when score>=90 then 1 end)as[90以上], count(case when score>=80 and score<90 then 1 end)as[80-90], count(case when score>=70 and score<80 then 1 end)as[70-80], count(case when score>=60 and score<70 then 1 end)as[60-70], count(case when score<60 then 1 end)as[60以下] FROM study,course WHERE https://www.wendangku.net/doc/0518427604.html,o=https://www.wendangku.net/doc/0518427604.html,o and https://www.wendangku.net/doc/0518427604.html,ame=@MATH GROUP BY https://www.wendangku.net/doc/0518427604.html,ame

运行结果： （2）创建一个存储过程，该存储过程有一个参数用来接收课程号，该存储过程统计给定课程的平均成绩。 CREATE Proc AVG_SCORE@cno CHAR(5) AS SELECT@cno as课程号,https://www.wendangku.net/doc/0518427604.html,ame as课程名,STR(AVG(score),5,2)as平均成绩 FROM study,course WHERE https://www.wendangku.net/doc/0518427604.html,o=https://www.wendangku.net/doc/0518427604.html,o and https://www.wendangku.net/doc/0518427604.html,o=@cno GROUP BY https://www.wendangku.net/doc/0518427604.html,ame 运行结果：

实验11 存储过程和用户自定义函数_图

实验11 存储过程和用户自定义函数 实验目的 1.掌握通过企业管理器创建、修改、删除存储过程和用户自定义函数的方法 2.学会编写存储过程和用户自定义函数 3.掌握存储过程的执行方法 4.学会编写、调用三类用户自定义函数 实验准备 1.学习存储过程和用户自定义函数相关知识。 2.已掌握常程序控制流语句。 3.熟练使用T-SQL完成数据查询和程序设计。 4.还原studentdb数据库 实验内容和步骤 1.打开企业管理器，展开studentdb子目录，选中“存储过程”，单击鼠标右键，弹出 快捷菜单，选择【新建存储过程（S）…】，打开新建存储过程窗口，如图11- 1。 图11- 1 新建存储过程

2.新建并执行存储过程“字母打印”。 (1)在新建存储过程窗口输入以下代码。 CREATE PROCEDURE 字母打印AS 注解：该存储过程是将26个小写英文字母按a~z的顺序输出，其中ascii()函 数——返回字符对应ASCII码，char()函数——把ASCII码转换成对应字符。 (2)输入完成后，单击【检查语法】按钮，确认输入内容正确后，单击【确认】按 钮完成存储过程的创建。 (3)打开查询分析器，输入： exec 字母打印 (4)执行，查看运行结果。 3.修改存储过程“字母打印”并执行。 (1)在企业管理器存储过程列表窗格中，选中存储过程“字母打印”，弹出快捷菜 单，选择【属性（R）】，或直接双击该存储过程，打开属性窗口，如图11- 2。

图11- 2 存储过程“字母打印”属性窗口 (2)修改代码内容，将“print char(ascii('a')+@count)”改为“print char(ascii('A')+@count)”。 (3)单击【确认】按钮，完成存储过程的修改。 (4)重新在查询分析器执行该存储过程，查看运行结果。 4.新建并执行带输入参数的存储过程。 (1)在企业管理器中新建存储过程“成绩查询”，代码如下： (2)在查询分析器窗口中，选择studentdb数据库。要求：通过存储过程“成绩查 询”查看学号为“2007224117”的成绩。 ●方法一：输入exec 成绩查询‘2007224117’，并执行。 ●方法二：输入 执行，查看该同学的成绩。 注：以上是执行含输入参数存储过程的常用方法，参数可以直接通过值传递， 也可以通过变量传递。 5.练习：请新建存储过程“学生信息”，输入参数仍为学号，返回学号对应的“学生” 表信息，并通过该存储过程查看学号为“2007224117”的个人信息。 6.新建带返回参数的存储过程并执行。 (1)在企业管理器中新建存储过程“学生平均成绩”，代码如下：

数据库函数、存储过程实验报告

南京信息工程大学数据库系统实验(实习)报告实验（实习)名称数据库系统实验４实验(实习）日期2016-６－7 得分指导教师顾韵华 系计软院专业计科年级20１4级班次计科3班姓名仇彤学号20141３０８071 一、实验目 1、掌握Ｔ-SQL函数及其调用方法。 2、掌握存储过程得定义及执行方法。 3、掌握有参存储过程得定义及执行方法。 4、掌握C#访问数据库得方法。 二、实验内容 1、使用系统函数(DATEDIＦF(d,date_exｐｒ1,ｄate＿expｒ２)),计算今天距离“2020-１－1”还剩多少天。(Ｐ２99"思考与练习") 2、编写T－ＳQL程序,利用系统转换函数,检索总订购商品数在10~19得客户姓名。 ３、定义函数RectArea,计算一个长方形得面积（长、宽作为函数得参数)。 4、在SＰDG数据库中定义函数,根据商品编号，查询该商品得名称;(函数名为QryGoods）。５、在SＰDG数据库中定义存储过程GetＳPBH，返回所有商品编号,并使用EXＥC语句执行存储过程。 ６、在ＳPＤG数据库中定义存储过程KH_NJ_Ｑrｙ，返回江苏南京得客户编号、姓名、及其订购商品得编号、商品名称与数量,并使用EXEＣ语句执行存储过程。 7、在SPDG数据库中定义存储过程SP_FOOD_Qry，返回食品类商品编号、商品名称及其订购客户编号、姓名、订购数量，并使用EXEC语句执行存储过程。 ８、定义存储过程ＳP_Totaｌ,查询指定商品编号得总订购数。并执行该存储过程。 9、定义存储过程SP＿ＴotaｌCost,查询指定商品编号得总订购金额。并执行该存储过程。 10、定义存储过程SP＿Namｅ_Ｑry,查询指定商品名称得商品信息。并执行该存储过程。 11、定义存储过程ＳP_Namｅ_Qry１，查询指定商品名称得商品信息;若存在,输出1；否则,输出0。并执行该存储过程。 1２、定义存储过程SP_Nａｍｅ_Qｒy2,查询指定商品名称得商品信息;若存在,用输出参数传出1;否则传出０。 三、实验过程与结果 1、使用系统函数(DA TEDＩFF（d，datｅ＿ｅxpr1,daｔe_expr2)）,计算今天距离“２020-1-1”还剩多少天。（P2９9"思考与练习"） 设计得SQL语句如下: prｉnｔdatediｆf(ｄ,geｔdate()，'２0２0-1-１') 执行结果: 2、编写T-SＱL程序,利用系统转换函数,检索总订购商品数在10~19得客户姓名。 设计得SQL语句如下: usｅSPDG select客户姓名 from KＨＢx， (sｅlｅct b、客户编号,SＵM(数量) aｓ总数量 ｆroｍKＨB a,SPDGB b whｅre ａ、客户编号=b、客户编号

Java中调用存储过程或存储函数的方法

1.调用存储过程： CallableStatement clstmt=null; try{ clstmt=conn.prepareCall("{call package_name.procedure_name(?,?,?,?,?)}"); clstmt.setString(1,bill.getBillType()); clstmt.setString(2,bill.getId()); clstmt.setInt(3,bill.getNum()); clstmt.registerOutParameter(4,Types.CHAR); clstmt.registerOutParameter(5,Types.VARCHAR); clstmt.execute(); out_opresult=clstmt.getString(4).trim(); out_operror=clstmt.getString(5).trim(); }catch(SQLException e){ System.out.println(e.getMessage()); } 2.调用存储函数： CallableStatement clstmt=null; try{ clstmt=conn.prepareCall("{?=call package_name.function_name(?,?,?,?)}"); clstmt.registerOutParameter(1,Types.VARCHAR); clstmt.setString(2,bill.getBillType()); clstmt.setString(3,bill.getId()); clstmt.registerOutParameter(4,Types.CHAR); clstmt.registerOutParameter(5,Types.VARCHAR); clstmt.execute(); out_opresult=clstmt.getString(4).trim(); out_operror=clstmt.getString(5).trim(); }catch(SQLException e){ System.out.println(e.getMessage()); } *存储过程编写时应注意： 1.输出参数不能作为查询条件； eg：假设，out_id已赋值， SELECT name INTO out_name FROM tb_test WHERE id=out_id; 这样写PL/SQL中会正常通过，而在Java程序调用该存储过程时会报错，需要改成：

存储过程和函数

存储过程和函数 1：创建一个可以向dept表中插入一行的过程 CREATE OR REPLACE PROCEDURE pro_c AS BEGIN INSERT INTO emp V ALUES(8902,'FORD','ANAL YST',7566,to_date('1981-12-3','yyyy-MM-dd'),3000.00,null,20); END; 2:创建一个过程，传入雇员名，返回该雇员的薪水（薪金+佣金） CREATE OR REPLACE PROCEDURE pro_2(names V ARCHAR2,sals OUT NUMBER ) AS BEGIN SELECT sal+nvl(comm,0) INTO sals FROM emp WHERE ename=names; END; --调用存储过程 DECLARE sa NUMBER; BEGIN pro_2('SCOTT',sa); dbms_output.put_line('薪水为：'||pro_2('SCOTT',sa)); END; 3：创建一个过程，传入雇员号，和薪水及增长比例（10%=0.1）。其中薪水为in out参数！更新薪水为新薪水用一个PL/SQL程序块来调用此过程，其传入到过程中的参数都是用户输入得到的 CREATE OR REPLACE PROCEDURE pro_3(num NUMBER,sal IN OUT NUMBER,increase NUMBER) AS BEGIN UPDATE emp SET sal=sal*(1+increase) WHERE empno=num; COMMIT; SELECT sal INTO sal FROM emp WHERE empno=num; END; --调用存储过程 DECLARE num NUMBER; incre NUMBER; sal NUMBER; BEGIN

Oracle 存储过程procedure,函数function用法,以及package用法

Oracle 存储过程procedure，函数function用法，以及package用法使用scott登录normal，简单的操作emp表实现存储过程，函数，包 定义存储过程： create or replace procedure get_user_name(userid in emp.empno%type) is xx emp.ename%type; begin select ename into xx from emp where empno=userid; dbms_output.put_line(xx); end; 定义函数： create or replace function get_user_sal(userid in emp.empno%type) return number is salary emp.sal%type; begin select sal into salary from emp where empno=userid; return salary; exception when NO_data_founD then dbms_output.put_line('NOT FOUND!'); end get_user_sal; 定义包头： create or replace package ex is procedure get_user_name(userid in emp.empno%type) ; function get_user_sal(userid in emp.empno%type) return number; end ex; 定义包体： create or replace package body ex is procedure get_user_name(userid in emp.empno%type) is xx emp.ename%type; begin select ename into xx from emp where empno=userid; dbms_output.put_line(xx); end; function get_user_sal(userid in emp.empno%type) return number

第11章MySQL存储过程与函数

数据库系统原理及MySQL应用教程(第2版) 课后习题参考答案 第11章MySQL存储过程与函数 1、存储过程和存储函数是在数据库中定义一些被用户定义的SQL语句集合。 二者存在以下几个区别： 1）存储过程实现的功能要复杂一点，而函数实现的功能针对性比较强。 2）存储过程可以返回参数，而函数只能返回值或者表对象。 3）存储过程可以使用非确定函数，不允许在用户定义函数主体中内置非确定函数。 4）存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用。 2、创建一个存储过程，根据学生的学号查询学生的姓名 create PROCEDURE getnamebysno(in xh char(10),out name char(20)) BEGIN select sname into name from student where sno=xh; 调用该存储过程：call getnamebysno(‘1101’); 创建一个名为numstu的存储函数,查询学生的人数 Create function numstu() Returns integer Begin Return(select count(*) from student); 调用：select numstu() 3、存储过程的优点： ◆存储过程增强了SQL语言的功能和灵活性 ◆存储过程能实现较快的执行速度。 ◆存储过程允许标准组件是编程。

◆存储过程能过减少网络流量。 ◆存储过程可被作为一种安全机制来充分利用。 4、查看存储函数状态方法： ●通过show status语句 ●如果要查看存储过程或函数的详细信息，要使用show create语句 5、游标是一个被SELECT语句检索出来的结果集。在存储了游标后，应用程序或用户就可以根据需要滚动或浏览其中的数据。 6、游标(cursor)具有以下特性： ●只读的，不能更新的 ●不滚动的 ●不敏感的，不敏感意为服务器可以活不可以复制它的结果表 （1）游标的声明：语法格式：declare cursorname cursor for select _ statement （2）打开游标：Open cursor_ name （3）读取游标：fetch cursor_name into var_ name [，var_name] …(4) 关闭游标：close cursorname 7、CREATE PROCEDURE sp_update email(IN USer_name VARCHAR(50)，IN e_mail VARCHAR(50)) UPDATE content SET email=e_mail WHERE username=user_name； 8、A 9 D 10 D 11 B 12 (1) create proceduce p1() select dno,count(*) from emp group by dno; (2) create proceduce p2(in gy char(10)) select * from emp where dno=gy; (3) create proceduce p3() select count(*) as 人数from emp where sex=’女’； (4) create proceduce p4(in gy char(10)) select avg(empage) from emp where dno=gy; call p4(‘11’);

MySQL实验报告5存储过程与函数11

四、实验内容及程序代码 1、sch数据表的表结构如表7、1所示,sch表的数据如表7、2。 表7、1 sch 表结构 字段名数据类型主键外键非空唯一自增 id INT(10) 就是否就是就是否 name V ARCHAR(50) 否否就是否否 class V ARCHAR(50) 否否就是否否 表7、2 sch 表的内容 id name class 1 李明C1 2 小梅C2 1)建表sch并插入数据。 2)创建一个存储函数,用来统计表sch中的记录数。 delimiter $$ create PROCEDURE count_sch5(out size int) BEGIN select count(*) into size from sch; END $$ delimiter; 3)创建一个存储过程,通过调用存储函数的方法来获取表sch中的记录数与sch表中id的与。delimiter $$

create PROCEDURE count_sch2(out s_a int,out s_id int) BEGIN select count(*) into s_a from sch; select sum(id) as s_id from sch; END $$ delimiter; 2、创建一存储过程insert_student_condition_user,利用自定义错误触发条件定义,当插入学生的性别不就是“男”或“女”时结束存储过程,并提示“学生性别不正确”。 表7、3 student表结构 字段名数据类型主键外键非空唯一自增 sno Char(10) 就是否就是就是否 sname VARCHAR(20) 否否就是否否 ssex CHAR(2) 否否就是否否 Sage smallint 否否就是否否 Sdept VARCHAR(30) 否否就是否否 enterdate datetime 否否就是否否 delimiter $$ create PROCEDURE insert_student_condition_user(in sno int,in sname VARCHAR(20), in ssex varchar(2),in sage int,in sdept varchar(30)) BEGIN DECLARE continue handler for sqlstate '42S02' select '学生性别不正确'; if ssex='男' or ssex='女' THEN insert into student values(sno,sname,ssex,sage,sdept); end if;

MySQL实验报告5(存储过程与函数)(1)(1)

四、实验内容及程序代码 1.sch数据表的表结构如表7.1所示，sch表的数据如表7.2。 表7.1 sch 表结构 字段名数据类型主键外键非空唯一自增 id INT(10) 是否是是否 name V ARCHAR(50) 否否是否否 class V ARCHAR(50) 否否是否否 表7.2 sch 表的内容 id name class 1 李明C1 2 小梅C2 1）建表sch并插入数据。 2）创建一个存储函数，用来统计表sch中的记录数。 delimiter $$ create PROCEDURE count_sch5(out size int) BEGIN select count(*) into size from sch； END $$ delimiter; 3）创建一个存储过程，通过调用存储函数的方法来获取表sch中的记录数和sch表中id 的和。 delimiter $$

create PROCEDURE count_sch2(out s_a int,out s_id int) BEGIN select count(*) into s_a from sch; select sum(id) as s_id from sch; END $$ delimiter; 2.创建一存储过程insert_student_condition_user，利用自定义错误触发条件定义，当插入学生的性别不是“男”或“女”时结束存储过程，并提示“学生性别不正确”。 表7.3 student表结构 字段名数据类型主键外键非空唯一自增 sno Char(10) 是否是是否 sname VARCHAR(20) 否否是否否 ssex CHAR(2) 否否是否否 Sage smallint 否否是否否 Sdept VARCHAR(30) 否否是否否 enterdate datetime 否否是否否 delimiter $$ create PROCEDURE insert_student_condition_user(in sno int,in sname VARCHAR(20), in ssex varchar(2),in sage int,in sdept varchar(30)) BEGIN DECLARE continue handler for sqlstate '42S02' select '学生性别不正确'; if ssex='男' or ssex='女' THEN insert into student values(sno,sname,ssex,sage,sdept);

Oracle11g第09讲存储过程及自定义函数(异常)讲述

--存储过程的定义默认in Create or Replace Procedure 过程名(变量名[in,out,inout] 数据类型) [is,as] --自定义变量 Begin End [过程名]; --自定义函数的定义默认in Create or Replace Function 函数名(变量名[in,out,inout] 数据类型) Return 数据类型 [is,as] 自定义变量 Begin Return 值; End [函数名]; Create function XXX(saljia number) return number As Begin …………………Sal+20 end select XXX(sal) from emp;

函数调用限制 1、SQL语句中只能调用函数 2、SQL只能调用带有输入参数,不能带有输出,输入输出函数 3、SQL不能使用PL/SQL的特有数据类型(boolean,table,record等) 4、SQL语句中调用的函数不能包含INSERT,UPDATE和DELETE语句 异常错误处理 一个优秀的程序都应该能够正确处理各种出错情况，并尽可能从错误中恢复。ORACLE 提供异常情况(EXCEPTION)和异常处理(EXCEPTION HANDLER)来实现错误处理。 1.1 异常处理概念 异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的事件,程序块的异常处理预定义的错误和自定义错误,由于PL/SQL程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行. 有三种类型的异常错误： 1．预定义( Predefined )错误 ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。 2．非预定义( Predefined )错误 即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。 3．用户定义(User_define) 错误 程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。 异常处理部分一般放在PL/SQL 程序体的后半部,结构为: EXCEPTION WHEN first_exception THEN