下载文档原格式
Oracle数据库实验-PLSQL游标、过程、函数、包的使用Oracle数据库基础实验5 PL/SQL游标、过程、函数、包的使用【实验学时】2学时【实验目的】1.了解PL/SQL语言中显式游标和隐式游标的概念和属性。
2.了解显式游标和隐式游标的差异。
3.掌握PL/SQL中cursor for循环的使用方法。
4.学会在PL/SQL程序中使用游标来解决实际问题。
5.学会在PL/SQL程序中进行异常处理的方法。
6.练习在PL/SQL中自定义异常。
7.理解过程和函数的作用。
8.学会编写过程、函数、包，并加以调用。
【实验内容】1.用显式游标编写程序，程序的功能是：计算每一个部门的平均工资。
注意异常的处理（例如，分母为0的异常）。
已写入文件afiedt.buf1 declare2 cursor c_dept is select * from dept order by deptno;3 cursor c_emp(p_dept emp.deptno%type)is selectename,sal from emp where deptno=p_dept4 order by ename;5 r_dept dept%rowtype;6 v_ename emp.ename%type;7 v_salary emp.sal%type;8 v_tot_salary emp.sal%type;9 begin10 open c_dept;11 loop12 fetch c_dept into r_dept;13 exit when c_dept%notfound;14dbms_output.put_line('department:'||r_dept.deptno||'-'||r_de pt.dname);15 v_tot_salary:=0;16 open c_emp(r_dept.deptno);17 loop18 fetch c_emp into v_ename,v_salary;19 exit when c_emp%notfound;20dbms_output.put_line('name:'||v_ename||'salary:'||v_salary);21 v_tot_salary:=v_tot_salary+v_salary;22 end loop;23 close c_emp;24 dbms_output.put_line('total salary fordept:'||v_tot_salary);25 end loop;26 close c_dept;27* end;SQL> /PL/SQL 过程已成功完成。
O racle教程2009.5L ou i s目录引言 (3)第一章数据库基础 (6)第二章Oracle 入门 (6)第三章查询基础 (11)第四章单行函数 (14)第五章分组函数 (19)第六章多表查询 (21)第七章子查询 (24)第八章高级查询 (40)第九章数据字典 (43)第十章Oracle 数据类型 (44)第十一章Oracle 体系结构(DBA) (45)第十二章DDL(改变表结构) (46)第十三章DML(改变数据结构） (48)第十四章约束 (49)第十五章视图 (55)第十六章索引 (56)第十七章序列、同义词 (65)第十八章PL SQL (67)第十九章游标、函数 (79)第二十章存储过程 (86)第二十一章触发器 (90)第二十二章事务（数据库系统概论） (99)第二十三章用户管理 (100)第二十四章备份恢复SQLLoader (104)第二十五章数据库设计范式 (106)第二十六章数据库设计工具 (107)第二十七章对象关系数据库系统 (112)第二十八章其他数据库 (113)-2-引言SUN 2008 初10 亿美元收购MySQLOracle 2009 年4 月74 亿美元收购SUNSun 与Oracle 合并的未来1，如果云计算对企业来说变得越来越重要，那么数据将是云计算的核心。
而讲到数据，也就意味着数据库。
就如塔克商学院数字策略中心主任M. Eric Johnson 所说的那样，Sun 已经清楚展现了一个真实的云计算环境。
就那些将云计算停留在理论阶段的对手来说，收购Sun 对Oracle 来说将获得竞争优势。
2，Johnson 主任还指出，Sun 在很多需求旺盛的方面“保持领先地位”。
Sun 的技术可以帮助数据库提高性能，尤其是对大型数据库。
Oracle 和Sun 的结合之后，对任何一个信息官来说都具有吸引力。
3，Oracle 软件和Java 的紧密结合，使得Oracle 的软件能跨平台使用。
oracle function的编写和应用在Oracle数据库中，可以使用PL/SQL语言编写函数。
函数是一段可重用的代码块，它可以接受输入参数，并返回一个值。
函数可以应用于各种场景，例如计算、数据转换和复杂查询等。
以下是编写和应用Oracle函数的一般步骤：1. 创建函数：使用CREATE FUNCTION语句创建函数。
指定函数的名称、参数和返回类型。
函数体由BEGIN和END关键字包围。
例如，下面是一个简单的函数，用于计算两个数值的和：```sqlCREATE FUNCTION sum_numbers(a NUMBER, b NUMBER) RETURN NUMBERISresult NUMBER;BEGINresult := a + b;RETURN result;END;```2. 存储函数：使用该CREATE FUNCTION语句创建函数时，它将被存储在数据库中以供后续使用。
可以通过使用替代CREATE OR REPLACE FUNCTION语句来修改已存在的函数。
例如，可以使用以下语句修改上面的函数：```sqlCREATE OR REPLACE FUNCTION sum_numbers(a NUMBER, b NUMBER)RETURN NUMBERISresult NUMBER;BEGINresult := a + b;RETURN result;END;```3. 调用函数：使用SELECT语句调用函数。
将函数名和参数传递给SELECT语句，可以将函数的返回值嵌入到查询结果中。
例如，可以使用以下SELECT语句调用上述函数并查看结果：```sqlSELECT sum_numbers(10, 5) FROM dual;```在调用函数时，也可以将其作为表达式的一部分使用，例如：```sqlSELECT sum_numbers(10, 5) * 2 FROM dual;```以上是基本的Oracle函数编写和应用的步骤。
oracle 19c 创建函数摘要：1.Oracle 19c 简介2.创建函数的步骤3.创建函数的示例4.函数的调用与测试正文：【1.Oracle 19c 简介】Oracle 19c 是Oracle 公司的一款关系型数据库管理系统，该版本在性能、安全和易用性等方面都有显著的提升。
Oracle 19c 提供了丰富的功能，如数据库自动化管理、机器学习、大数据处理等，为企业级应用提供了强大的支持。
【2.创建函数的步骤】在Oracle 19c 中创建函数，需要遵循以下步骤：1) 登录到Oracle 数据库，使用SQL*Plus 或者其他SQL 客户端工具。
2) 创建一个新的表空间，用于存储函数的元数据。
例如：```sqlCREATE TABLESPACE function_spaceDATAFILE "function_space.dbf" SIZE 10MAUTOEXTEND ON NEXT 10M;```3) 创建一个新的函数，可以使用CREATE FUNCTION 语句。
例如，创建一个名为`GET_USER_INFO`的函数，用于查询用户信息：```sqlCREATE FUNCTION GET_USER_INFO (p_user_id IN er_id%TYPE)RETURNS er_info%ROWTYPEASBEGINSELECT * FROM users WHERE user_id = p_user_id;EXCEPTIONWHEN NO_DATA_FOUND THENRAISE NO_DATA_FOUND;END;/```4) 编译函数，使用ALTER FUNCTION 语句。
例如：```sqlALTER FUNCTION GET_USER_INFOCOMPLETE;```5) 测试函数，使用SELECT 语句或者在应用程序中调用。
例如，查询用户ID 为1 的用户信息：```sqlSELECT * FROM GET_USER_INFO(1);```【3.创建函数的示例】除了上述的`GET_USER_INFO`函数示例，还可以创建其他类型的函数，如存储过程、触发器等。
在Oracle Database 19c中，您可以按照以下步骤创建一个函数：1. 首先，登录到Oracle数据库。
您可以使用SQL*Plus或SQL开发工具等客户端工具连接到数据库。
2. 打开SQL开发工具（如SQL*Plus）并连接到您的数据库。
3. 在SQL提示符下，输入以下语法来创建一个函数：```sqlCREATE OR REPLACE FUNCTION function_name (parameter1 datatype, parameter2 datatype, ...) RETURN return_datatype IS-- 声明变量和类型BEGIN-- 函数逻辑代码-- 可以包含条件语句、循环、计算等-- 在这里执行您的逻辑并返回结果RETURN result; -- 返回结果EXCEPTION-- 处理异常的代码块WHEN exception_name THEN-- 处理异常的逻辑代码RETURN result; -- 返回结果END;/```在上面的代码中，您需要替换以下部分：* `function_name`：您希望为函数指定的名称。
* `parameter1, parameter2, ...`：函数的参数列表，每个参数包括参数名称和数据类型。
您可以根据需要指定任意数量的参数。
* `return_datatype`：函数返回的数据类型。
这可以是任何有效的Oracle数据类型，例如NUMBER、VARCHAR2、DATE等。
* `exception_name`：您希望处理的异常名称。
如果函数中可能发生多个异常，您可以在`EXCEPTION`子句中列出多个`WHEN`块来处理每个异常。
4. 在代码块内部，您可以编写实现函数逻辑的PL/SQL代码。
根据您的需求，可以包含条件语句、循环、计算等。
在函数逻辑的末尾，使用`RETURN`语句返回结果。
5. 如果函数逻辑中发生了异常，您可以编写异常处理代码块来处理异常情况。
oracle 函数的用法Oracle 是一个非常强大的数据库管理系统，它提供了丰富的函数库，可以让我们在SQL 语句中使用各种函数来实现数据处理、计算和查询。
本文将给大家介绍一些常用的Oracle 函数及其用法。
一、字符串函数1. UPPER 函数：将字符串转换为大写字母。
SELECT UPPER('hello world!') FROM DUAL;结果为：HELLO WORLD!结果为：hello world!3. INSTR 函数：查找子字符串在字符串中第一次出现的位置。
结果为：54. SUBSTR 函数：截取字符串的一部分。
5. REPLACE 函数：将字符串中的某个子串替换为另一个子串。
二、数值函数1. ROUND 函数：将指定的数字四舍五入到指定的小数位数。
3. ABS 函数：计算数字的绝对值。
4. SIGN 函数：返回数字的符号，如果为正数返回 1，为负数返回 -1，为零返回 0。
SELECT SIGN(-10), SIGN(10), SIGN(0) FROM DUAL;结果为：-1 1 05. POWER 函数：计算一个数的指定次幂。
三、日期函数1. SYSDATE 函数：返回当前系统日期和时间。
SELECT SYSDATE FROM DUAL;2. MONTHS_BETWEEN 函数：计算两个日期之间相差的月数。
SELECT MONTHS_BETWEEN('2022-01-01', '2021-01-01') FROM DUAL;3. ADD_MONTHS 函数：对指定日期增加指定的月数。
4. TRUNC 函数：截取日期到指定的精度，例如截取到月份。
5. TO_CHAR 函数：将日期类型转换为字符串类型。
SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;结果为：2021-08-01 14:00:00四、聚合函数1. COUNT 函数：计算查询结果集中的行数。
oracle function的编写和应用编写一个Oracle函数的步骤如下：1. 创建函数：使用CREATE FUNCTION语句创建函数，指定函数的名称、参数和返回值类型。
2. 编写函数体：使用BEGIN和END关键字包围函数体，编写具体的函数逻辑。
3. 定义参数：在函数体中定义参数，并使用它们进行计算或返回。
4. 返回结果：使用RETURN语句返回函数的结果。
5. 完善函数：根据需求，可以添加异常处理、条件语句、循环等逻辑。
以下是一个示例，说明了一个计算员工薪水增加额度的函数：```sqlCREATE OR REPLACE FUNCTION calculate_salary_increase( emp_id IN employees.employee_id%TYPE,increase IN NUMBER)RETURN NUMBERIScurrent_salary NUMBER;BEGIN-- 获取员工当前的薪水SELECT salary INTO current_salaryFROM employeesWHERE employee_id = emp_id;-- 计算薪水增加后的结果current_salary := current_salary + increase;-- 返回计算结果RETURN current_salary;END;/```使用Oracle函数的步骤如下：1. 调用函数：使用SELECT语句调用函数，传入所需的参数。
2. 获取返回值：通过SELECT语句或将函数作为子查询的一部分，获取函数返回的结果。
以下是一个使用上述函数计算员工薪水增加额度的示例：```sql-- 调用函数，传入参数SELECT calculate_salary_increase(101, 500) AS new_salary FROM dual;```该示例中，函数calculate_salary_increase被调用，并传入员工编号101和薪水增加额度500。
oracle 语句块函数摘要：1.简介2.Oracle语句块的概述3.Oracle函数的使用4.总结正文：1.简介在Oracle数据库中，语句块和函数是两种重要的编程结构，可以帮助我们实现复杂的业务逻辑。
本文将详细介绍Oracle语句块和函数的使用方法。
2.Oracle语句块的概述Oracle语句块是Oracle中一种可执行的代码块，它允许我们在一个SQL 语句中执行多个SQL命令。
语句块有三种类型：PL/SQL块、匿名PL/SQL块和扩展SQL块。
其中，PL/SQL块是最常用的语句块类型，它支持变量、循环、条件语句等高级编程功能。
使用PL/SQL块时，我们需要使用BEGIN和END关键字来标识代码块的开始和结束。
例如：```BEGIN-- 这里是PL/SQL块的内容END;```3.Oracle函数的使用在Oracle中，函数是一种将输入参数转换为特定输出的PL/SQL代码块。
函数可以提高代码的重用性，减少冗余代码。
Oracle提供了许多内置函数，如字符串处理函数、数学函数等。
我们还可以自定义函数，以满足特定的业务需求。
定义函数时，我们需要使用CREATE FUNCTION关键字，并指定函数名、参数类型和返回类型。
例如：```CREATE FUNCTION add_numbers (p1 NUMBER, p2 NUMBER)RETURN NUMBER ISresult NUMBER;BEGINresult := p1 + p2;RETURN result;END;/```4.总结本文介绍了Oracle语句块和函数的使用方法。
语句块是一种可执行的代码块，可以帮助我们实现复杂的业务逻辑；函数是将输入参数转换为特定输出的PL/SQL代码块，可以提高代码的重用性。
子程序和程序包目标：能够创建子程序能够创建程序包第一部分指导本阶段的重点是学习创建过程、函数和程序包，并在程序包中使用过程、函数和游标的方法。
阶段一：使用过程需求说明：公司的销售经理需查看月底的订单情况。
如果指定订单号的状态为“c”，则所执行的过程应显示消息“订单已确认”。
如果指定订单号的状态为“p”，则所执行的过程应显示消息“订单待处理”，否则显示“无可用状态”。
创建一过程以接受销售经理输入的特定订单号，并显示订单状态。
实现思路：创建一个过程，可以接受ORDER-NO参数。
用户可使用SELECT语句从ORDER-MASTER表中检索订单状态，并将检索到的值存储在STATUS中。
如果订单状态为已确认，则向用户显示消息“订单已确认”。
如果订单状态为待处理，则显示消息“订单待处理”。
如果未找到任何数据，则显示说明此情况的消息。
参考步骤：（1）使用用户名和口令调用SQL提示符。
（2）输入下列代码。
此过程接受销售经理输入的ORDER-MASTER表中的特定订单的ORDER_NO，并根据特定订单的ORDER_NO向销售经理显示订单的状态。
要对“o001”号订单执行此过程，请输入以下命令。
如果订单状态为已确认，则此过程的输出结果为“订单已确认”。
如果订单状态为待处理，则此过程的输出结果为““订单待处理””。
否则输出结果为“无可用状态”。
阶段二：使用函数需求说明：公司人事部门工作人员经常需要根据职员的编号驱动器所在部门的名称，但部门名称在dept表中，而工作人员需要的其他所有信息都在emp表中。
为了避免使用表连接，请创建函数在接受职员编号后返回职员所在部门名称。
实现思路：创建一个名为dept-name 的函数，该函数接受一个数字类型的参数，代表职员的编号。
根据职员编号从emp表中得到部门编号，再根据部门编号从dept表中检索部门名称。
参考步骤：（1）使用用户名和口令调用SQL提示符。
（2）输入以下语句创建函数。
