Oracle数据库_03数据库基本编程操作

1、l 课程名称： l 教 材：l 主 讲：l 单 位：l 时 间：Oracle数据库系统数据库基本编程操作多表查询与子查询多表查询与子查询PL/SQL语句语句存储过程存储过程1234数据查询数据查询触发器触发器5SQL游标游标647事务与并发控制事务与并发控制数据查询实验目的实验目的了解查询的目的以及基本查询。了解查询的目的以及基本查询。熟练掌握基本查询的方法等操作。熟练掌握基本查询的方法等操作。熟悉基本查询中筛选数据的方法。熟悉基本查询中筛选数据的方法。数据查询实验内容实验内容所有的查询都必须通过select语句来实现。本实验讲述最基本的查询。由上节实验中使用的用户登录数据库，各实验组应该选取

2、不同的用户名。查询所有列查询所有列 ： 创建一张数据库中不存在的表 ： 向其中插入数据，并进行查询 ：查询特定列查询特定列 ：数据查询 通过desc命令查看想要查询对象的结构，然后再查询所有列中存在的某些特定列 数据查询排除重复行排除重复行 ： 从上述可以看出，表中有两行内容完全相同，通过采用distinct后可以去除重复行。字符串连接操作字符串连接操作 ：注意：连接符号注意：连接符号“|”用于连接两个字符串。这条语句比较复杂，注意把握。语句中用于连接两个字符串。这条语句比较复杂，注意把握。语句中的四个连续单引号是为了可以输出一个单引号。的四个连续单引号是为了可以输出一个单引号。数据查询在查询

3、语句中，如果某列是表达式或者比较复杂时，为了使查询结果表示更方便，通常为这列设置列别名 数据查询使用别名使用别名 ：上述将id*5列命名为m_id，在查询结果中，该列名称以列别名出现多表查询与子查询实验目的实验目的了解多表查询及子查询。了解多表查询及子查询。掌握多表查询及子查询方法。掌握多表查询及子查询方法。了解多表查询的不同连接方式。了解多表查询的不同连接方式。相等连接查询：相等连接查询： 多表查询与子查询实验内容实验内容鉴于在上述实验中已经创建表table1，因此本实验首先要创建表table2，然后使其中一列的意义与table1中某列的意义相同，这样就可以实现相等连接查询通过上述操作，使表

4、table2中列id与table1中列id具有相同的意义，均代表某人的编号。向其中插入一些数据后，就可以实现多表查询 ：多表查询与子查询这两条语句可以显示出两张表中的所有数据，由此可以推断，如果通过id号相等连接两张表时，其中id号为3的记录肯定不会出现在连接查询结果中。 多表查询与子查询这条语句实现了两张表的相等连接查询，两张表中id相同的数据被显示在结果中。不等连接查询不等连接查询 ： 多表查询与子查询向表table1中再插入一行，使其id号与table2中的任何一个均不相同。根据条件查询 通过以上两个列子，可以使读者理解多表查询的基本方法。多表查询与子查询当第一个查询结果被当成第二个查询

5、的一部分出现在第二个查询的条件中时，第一个查询就是子查询 多表查询与子查询从以上可以看出，子查询的结果为id = 3，因此满足id 3的表table1中的内容只有id = 4这行记录。PL/SQL语句 实验目的实验目的了解了解PL/SQLPL/SQL块的基本结构和分类。块的基本结构和分类。掌握在掌握在PL/SQLPL/SQL块中定义和使用变量。块中定义和使用变量。掌握在掌握在PL/SQLPL/SQL块中编写可执行语句。块中编写可执行语句。PL/SQL语句实验内容实验内容PL/SQL中的基本程序单元就是块，块组成PL/SQL程序。块可以嵌套，用来完成复杂的应用功能。本实验从块讲解PL/SQL语句

6、以及由PL/SQL语句所组成的PL/SQL程序 包含执行部分的包含执行部分的PL/SQL块块 ：一般PL/SQL块包含三个部分，分别是定义部分、执行部分和异常处理部分。上述用beginend包含了执行部分输出hello。可以看到该块输出了hello，并提示PL/SQL过程已成功完成。 注意：在输出注意：在输出hello的的PL/SQL块前，需要将块前，需要将serveroutput 设置为设置为on状态，否则只是提示状态，否则只是提示PL/SQL过程成功完成，并没有输出过程成功完成，并没有输出hello。编写大段的PL/SQL程序块时，容易出错，可以在SQL提示符号下输入“edit”来调用文本

7、编辑器，编辑完毕后保存退出。在SQL提示符号下输入“/”，即可运行PL/SQL程序块。注意：上述不仅包含执行部分注意：上述不仅包含执行部分（beginend之间的部分），而且之间的部分），而且还包含还包含declare声明的部分。将声明的部分。将table1中的中的name读出并写入到读出并写入到v_name中，最后输入中，最后输入v_name。首先查询首先查询table1，目的是为了保证输，目的是为了保证输入的入的id是是table1中存在的，否则会出中存在的，否则会出现错误并提示现错误并提示“未找到数据！未找到数据！”。 PL/SQL语句包含定义部分和执行部分的包含定义部分和执行部分的PL

8、/SQL块块 ：三部分都包含的三部分都包含的PL/SQL块块 ：PL/SQL语句在此示例过程中，加入了异常处理部分，当输入的id为3，且在table1中不存在时，就不会出错了。 继续运行，id为1在table1中对应2行，输入1时也不会报错 PL/SQL语句PL/SQL过程过程 ：PL/SQL块一般被划分为匿名块、命名块、子程序和触发器四种类型。子程序中的过程和函数非常重要，因此下面首先讲述PL/SQL过程。PL/SQL语句以上过程中，update_table1将王五的id编号改为3。PL/SQL语句PL/SQL函数函数 ：以上创建了函数modify_id，且其返回值为int型。注意：在创建函

9、数时，必须要有返回值。注意：在创建函数时，必须要有返回值。 存储过程实验目的实验目的了解了解PL/SQL存储过程的作用。存储过程的作用。掌握编写掌握编写PL/SQL存储过程，并掌握使用过程参数。存储过程，并掌握使用过程参数。了解存储过程的维护过程。了解存储过程的维护过程。实验内容实验内容过程是用于执行特定操作的PL/SQL块。如果经常需要执行某一特定操作，可以为这些操作建立过程。通过使用过程，不仅可以简化应用的开发和维护，而且可以提高应用程序的运行性能。 存储过程创建无参数过程创建无参数过程 ： 调用此过程后，发现系统时间按照过程中设定的格式显示出来 。 存储过程上述操作创建过程output_

10、time，该过程按照设定的格式输出系统时间。注意：在调用该过程前，需要将注意：在调用该过程前，需要将serveroutput设置为设置为on，否则，否则时间不会被显示出来。时间不会被显示出来。 创建带输入参数的过程创建带输入参数的过程 ： 该过程为table1添加一行记录，其中包含两个参数。参数id的类型与table1中id列的属性完全相同，参数name与table1中name列的属性完全相同。存储过程注意：在创建过程时，过程参数注意：在创建过程时，过程参数id 也可以定义为也可以定义为int，同时，同时name也可以定义为也可以定义为varchar2（30）。但是为了与）。但是为了与tabl

11、e1中的列完全相同，因此采用中的列完全相同，因此采用type来实现。来实现。存储过程调用该过程，在调用时必须为参数赋值。通过查询表table1可以看到该过程确实完成了向表中插入一行数据的功能。创建带有输入输出参数的过程创建带有输入输出参数的过程 ： 上述过程divide实现了两数相除及取余的操作。可以看到num1、num2既可以输入，也可以输出。存储过程调用该过程，实现了相除及取余操作，并且将商和余数显示出来。存储过程存储过程维护过程维护过程 ： 删除、显示编译错误、编译过程、查看过程代码等都是维护过程，以下通过这几个方面详细介绍过程维护。为了避免过程的运行错误，可以在调用前对过程重新编译，如

12、上所示。通过查询数据字典user_resource，可以看到子程序名称及源代码 。存储过程通过show errors可以查看创建过程中出现错误的过程 。存储过程上述过程error_proc在创建过程中出现警告，通过show errors查看该错误原因以及错误所在的行 。触 发 器实验目的实验目的了解各种不同的触发器。了解各种不同的触发器。掌握创建语句以及行掌握创建语句以及行DML触发器。触发器。了解如何维护触发器。了解如何维护触发器。实验内容实验内容触发器是被隐含执行的存储过程。当发生特定事件时，Oracle会自动执行触发器的相应代码，这些特定事件一般是插入、删除和修改表。触发器比数据库本身标

13、准的功能有更精细和更复杂的数据控制能力 。触 发 器创建创建before语句触发器语句触发器 ：在为某对象创建触发器时，首先要保证对象不属于sys用户。因此首先创建用户test，使其具有连接、创建表、插入和创建触发器等权限，然后通过test用户来实践创建触发器 。注意：注意：不同的组选应该用不同的用户名，可以用不同的组选应该用不同的用户名，可以用test1、test88等任意符合等任意符合Oracle命命名规范的用户名。名规范的用户名。触 发 器创建创建before语句触发器语句触发器 ：在test用户下创建表empinf并为其插入一条语句，目的是为了方便实践触发器。 触 发 器为表为表emp

14、inf创建了触发器创建了触发器tr_fmodify_empinf，使用户在星期二、星期六不可以更新表，使用户在星期二、星期六不可以更新表中的数据中的数据 ：要判断该触发器是否起作用，只要执行 :触 发 器注意：在实践此示例时，时注意：在实践此示例时，时间为星期二。用户在实践时，间为星期二。用户在实践时，可以将时间改为自己实践的可以将时间改为自己实践的具体日期，否则更新数据不具体日期，否则更新数据不会报错，这样就不能判断触会报错，这样就不能判断触发器是否起作用了。发器是否起作用了。 After触发器是在执行了DML语句后被触发的触发器 ,上面程序实现在对表插入一行数据后，触发器被触发，并且按照预

15、设输出提示 。触 发 器创建创建after语句触发器语句触发器 ：触 发 器创建行触发器创建行触发器 ：从上述可以看出，当从上述可以看出，当id1的行中对应的的行中对应的name，sal列的值显列的值显示出来。可以看到，采用记录接收游标数据比标示出来。可以看到，采用记录接收游标数据比标量接收游标数据方便多了量接收游标数据方便多了 。SQL游标 更新游标行更新游标行 ：SQL游标 批量提取批量提取 ：Oracle 9i后可以使用fetchbulk collect提取所有数据，具体如下所示 。SQL游标 从上述可以看出，当输入clerk时并没有按照预定输出，检查后发现多输入了一个分号，正确的输入如

16、下所示。可以看到，当输入clerk时，将其所对应的记录显示出来，实现了批量提取所有数据的目的。 注意：当输入注意：当输入job值时，需要特别注意不能输入分号，否则值时，需要特别注意不能输入分号，否则Oracle会认为会认为job为为“clerk;”，而不是，而不是“clerk”，从而导致没有输出结果。，从而导致没有输出结果。事务与并发控制 实验目的实验目的了解事务及其控制的基本语句。了解事务及其控制的基本语句。掌握事务控制的语句以及用法。掌握事务控制的语句以及用法。了解事务的属性。了解事务的属性。实验内容实验内容事务是数据操作的逻辑单位，是用户定义的一组操作序列，由一条或多条相关SQL语句组成

17、，是数据库应用程序的基本逻辑单位。事务可以有效、安全地修改数据，使数据库中的数据始终处于一个数据一致性的状态，本实验通过以下示例详细讲述事务。事务就是一组不可分隔的SQL语句，在执行时要么全部成功，要么全部撤销，Oracle就是通过事务来确保数据库中数据的一致性的。Oracle提供的事务控制语句包括提交事务、回退事务、设置保存点、回退到保存点、设置事务的属性、设置可延迟约束的检验时机。 事务与并发控制 事务与并发控制 提交事务提交事务 ：用在前面实验中自己的用户名登录，将表table1的所有操作权限赋予test用户，然后通过以下示例实现事务的提交。注意：语句是以注意：语句是以sys用户为例的，

18、具用户为例的，具体实验中要替换成自己的用户名。体实验中要替换成自己的用户名。 事务与并发控制 sys用户向表table1中插入一行值，其中id为6，未提交。然后打开另一个窗口，通过具有table1操作权限的test用户连接数据库，查询表table1中的数据。可以看到，sys插入的新值并没有在结果中显示出来 。事务与并发控制 在sys用户连接窗口中输入commit，具体如下所示。当sys提交完成后，test用户查看table1中的id为6的行，可以查到sys向表新插入的值，具体如下所示。事务与并发控制 回退全部事务回退全部事务 ：事务与并发控制 上述操作首先向表table1中插入一条记录，然后删除id为6的记录，最后回退事务。通过查询可以看到，这两条操作都被回退了。回退部分操作回退部分操作 ：当一个事务包含多条语句时，可以只回退几条语句，具体如下所示。事务与并发控制 通过设置保存点，可以选择回退到具体