ORACLE课堂讲义(13~18)

ORACLE课堂讲义

（课堂讲义有三部分共十八讲，点我ID看其他的，授课老师：贾峰）

第十三讲

PL/SQL编程

编程比java简单的多，要知道数据库的主要作用。PL/SQL在sqlplus不适合测试，我们为了方便用plsql developer界面工具测试。 一、PL/SQL程序块的基本结构 declare --声明部分，可以省略 begin --编写主题

exception --捕获异常，可以省略 end;

1、只包含执行体程序块实例

看一个简单的PL/SQL块，必须有begin和and。 begin

dbms_output.put_line('Hello World'); end;

此时，直接执行程序即可。执行之后发现没有任何输出。因为Oracle在系统设置中默认设置了输出不显示，如果要显示的话，输入以下sqlplus命令：

set serveroutput on

dbms_output.put_line('Hello World');这条语句的作用类似于java程序中system.out.println (“HELLO WORLD”);用于输出操作结果。 2、包含执行体、声明部分和异常部分的程序块

【例】程序中，声明部分声明一个变量v_sal；在程序执行部分，将员工SCOTT的工资赋予变量v_sal并输出结果；在异常处理部分，捕捉出现异常，如果查询到多条记录或没有查询到或其他情况，打印出相应异常结果。 declare

v_sal number := 0; begin

select sal into v_sal

from emp where ename = 'SMITH';

dbms_output.put_line('sal is '||v_sal);--||字符串连接符 exception

when too_many_rows then

dbms_output.put_line('too many rows!'); when no_data_found then

dbms_output.put_line('no data!!'); when others then

dbms_output.put_line('some error!'); end;

dbms_output.put_line('sal is '||v_sal);其中“||”字符串连接符，相当于java当中的“+”。Exception异常这里列举3种异常类型，详细看书P298表9-8.

就像我们java中try…catch…一样。 try{}

catch(NullPointerException e){ System.out.println(“null”); }catch(NumberFormatException e){

System.out.println(“number”);

}catch(Exception e){

System.out.println(“other error!!!”);

}

二、PL/SQL的变量 --PL/SQL中的简单变量定义 v_gender char(1); --没有初始值 v_sal number := 0; --初始值为0

v_date date:=sysdate+7 --初始值为当前日期七天后日期 --使用%type定义简单变量

通过%type属性来为变量声明类型，简单的说利用一个已经声明过的变量或者数据表的列来定义变量，新的变量类型与参照的类型完全相同。 例如：PL/SQL中使用%type定义简单变量 v_name emp.ename%type; v_deptno emp.deptno%type 三、在PL/SQL中使用SQL语句 1、PL/SQL程序中使用SELECT语句

注意一些语法特点： SELECT list INTO variable_name FROM table WHERE condition;

(1) 查询列表后必须使用into子句，用于将查询出字段值传递给变量； (2) 查询语句必须并且只能返回一行，多于一行或没有返回结果都会产生异常； (3) 可以使用完整的SELECT语法。

【例】在PL/SQL中使用select语句，取出某个员工的姓名和工资。其中使用替

代变量输入查询的员工的职员编码。 declare

v_name emp.ename%type; v_sal emp.sal%type; begin

select ename,sal into v_name,v_sal from emp

where empno=&no;

dbms_output.put_line('职员名字是：'||v_name); dbms_output.put_line('职员工资是：'||v_sal); end;

--注意：如果把where deptno = &no;换成这一句，那么结果就会报错。 2、PL/SQL程序中使用DML语句

select语句在语法和要求上有所改变，但是DML语句（insert，update，delete）在PL/SQL并没有任何改动。

【例】使用INSERT命令增加一个新的部门到部门表中。和SQL语句中的INSERT

语法完全相同。 declare

v_dno dept.deptno%type; v_dname dept.dname%type; begin

v_dno:=&dno; v_dname:='&dname';

insert into dept(deptno,dname) values(v_dno,v_dname);

commit; end; --测试

输入dno的值：50；输入dname的值：LEARNING 四、条件判断语句

--条件分支语句语法结构如下： IF condition THEN statements;

[ELSIF condition THEN statements;] [ELSE statements;] END IF;

在这个语法中使用IF语句可以根据设置的条件进行判断，选择条件分支中的某个分支进行操作，并且最多允许有一个ELSE子句。

【例】判断如果职位是“ANALYST”，则设置薪水增加10%，增加值为是“CLERK”，

则薪水增加20%，其余职位增加5%。 declare

v_empno emp.empno%type; v_job emp.job%type; begin

select empno,job into v_empno,v_job from emp where empno = &v_eno; if v_job = 'ANALYST' then

update emp set sal=sal*1.1 where empno=v_empno; elsif v_job='CLERK' then

update emp set sal=sal*1.2 where empno=v_empno; else

update emp set sal=sal*1.05 where empno=v_empno; end if; end;

--注意：在多分支语句中，ELSIF的写法，不要分开写成ELSE IF。 五、循环语句

除了IF语句外，PL/SQL程序中的另一种程序控制语句就是循环。循环用于

根据条件一次或者多次执行某些语句。

在PL/SQL程序中有三种循环类型：简单循环、FOR循环和WHILE循环，1、我们先介绍一下FOR循环语法结构： FOR index IN lower . .upper LOOP Statement1;… END LOOP;

【例】在循环中通过一个计数器作为循环结束的标记，当计数器大于10的时候，

结束循环。本例中将会插入到表temp中10个顺序递增的数字，假设temp表中只有一个数字类型的列。 declare

v_counter number; begin

for v_counter in 1..10 loop

insert into temp values(v_counter); end loop; end;

2、WHILE循环语法结构： WHILE condition LOOP Statement1;… END LOOP;

【例】用WHILE循环实现上面的例子，同样插入10条递增的记录。 declare

i number :=1; begin

while i<=10 loop

insert into temp values(i); i:=i+1; end loop; end;

第十四讲

PL/SQL程序单元

前面讲解PL/SQL程序的基本概念和语法，下面将开始编写PL/SQL程序。这种程序可以带参数，存储在数据库中，目的是将业务逻辑集成在程序块中，简化客户端的开发和维护，并且可以在不同应用程序中多次调用。 --PL/SQL程序单元重要包括四类： ◆存储过程 Procedure( 了解 )

过程( Procedure )是一种 PL/SQL存储程序单元，主要用于在数据库中完成特定的操作或者任务，如果在程序中经常需要执行某个操作，可以于这些操作建立一个过程，用于简化客户端的开发和维护，以及提高执行性能。 ◆函数Function( 了解 )

PL/SQL中的函数被用来执行复杂的计算，并返回计算结果。 ◆包Package( 了解 )

? 包是一种比较特殊的PL/SQL程序，它并不是一个PL/SQL存储程序块，而是用于将相关的存储过程和函数组织起来，组成 PL/SQL存储程序组。 ? 包由两个独立部分组成：包头和包体。 ◆触发器Trigger( 了解 )

? PL/SQL程序中的触发器的结构类似于函数和过程，与函数和过程不同，触发器是在事件发生时隐式地运行的。相当于Java 语言中的事件监听器。

一、函数Function

--函数：必须返回数据，在sql语句中生效，通常是用来计算。 --函数就是一个有返回值的过程。 1、 创建函数的语法结构如下：

create or replace function name return datatype is

变量、常量声明； begin

SQL和PL/SQL语句； return value; [exception] 异常语句

end;

【例】：定义一个函数，此函数用来计算个人所得税率： 0-1000 0% 1001-2000 1% 2001-3000 2% 大于3000 4%

create or replace function tax_ning(

v_sal number) return number is

v_result number; begin

if (v_sal < 1000) then

v_result := 0;

v_result := v_sal * 0.01; elsif (v_sal < 2000) then elsif (v_sal < 3000) then v_result := v_sal * 0.02; else

v_result := v_sal * 0.04; end if;

return v_result; end;

--测试函数的使用

SQL>select ename, sal, tax_ning(sal) from emp;

--在数据字典里查看函数。 SQL> col line format 99

SQL> col text format a50 --查看过程源代码前规范格式 SQL> select line,text from user_source

where name='TAX_NING'; --查看过程源代码

【例】：定义一个函数，输入参数：deptno，输出参数；部门人数。 create or replace function emp_count(

v_deptno emp.deptno%type)

return number is

v_count number; begin

select count(*) into v_count from emp where deptno = v_deptno; return v_count; end; --测试

SQL>select emp_count(30) from dual;

二、过程Procedure:

--过程:可以不返回数据，可以独立调用，通常执行某些动作。 1、 创建过程的语法结构如下：

create or replace procedure name[(parameter,…)] is

变量、常量声明； begin

SQL和PL/SQL语句； [exception] 异常语句 end;

其中过程参数prameter的语法如下:parameter_name[IN|OUT|IN OUT] datatype，IN，OUT和IN OUT三种模式分别具有不同的使用特性，IN用于从调用环境传入参数值，如果忽略参数模式，则默认为是IN；OUT用于将值通过参数传出到调用环境；IN OUT即可以传入值，也可以传出值。 【例】：定义一个过程，输入参数：deptno，输出参数：部门人数。 create or replace procedure myproc( v_deptno emp.deptno%type) is

v_count number; begin

select count(*) into v_count from emp where deptno = v_deptno;

dbms_output.put_line (v_count); end;

--测试过程的方式，在sqlplus中测试： SQL>exec myproc(20)

--在数据字典里查看过程。 SQL> col line format 99

SQL> col text format a50 --查看过程源代码前规范格式 SQL> select line,text from user_source

where name='MYPROC'; --查看过程源代码

--有输出参数的过程

【例】：创建一个过程，定义了一个输入参数以及两个输出参数。 create or replace procedure calcu_emp( v_deptno in number, v_sum_sal out number, v_avg_sal out emp.sal%type) is begin

select sum(sal), avg(sal) into v_sum_sal, v_avg_sal from emp

where deptno = v_deptno;

end;

--测试有输出参数的过程： declare

v_sum number; v_avg number;

calcu_emp(10, v_sum, v_avg); dbms_output.put_line(v_sum); dbms_output.put_line(v_avg);

begin

end;

--功能：修改员工薪水。

--输入参数：员工编码，新的薪水值。

【例】：如果员工的职位不是MANAGER或者PRESIDENT，且薪水高于15000，则报错“too many sal”。否则，修改指定员工的薪水为指定值，显示“update success!”。

create or replace procedure changesal(

v_empno emp.empno%type, v_sal emp.sal%type) v_job emp.job%type;

is begin

select job into v_job

from emp where empno = v_empno;

if(v_job not in ('MANAGER','PRESIDENT') and v_sal > 15000) then dbms_output.put_line('too many sal'); else

update emp set sal = v_sal where empno = v_empno;

dbms_output.put_line('update success!'); end if; exception

when others then

--测试方式

SQL>exec changesal(7788, 20000);--满足条件报错，too many sal SQL>exec changesal(7698, 20000);--不满足条件，修改

是经理，工资高于15000。

dbms_output.put_line('some error!'); end;

第十五讲

Oracle 11g图形界面创建数据库实例

Oracle体系结构是指Oracle的组成、工作过程和基本原理，以及数据在数据库中的管理和组织机制。作为数据库使用者，软件开发人员通常比较关注SQL和PL/SQL，在软件开发过程中，因为有DBA的协助，对于数据库管理和运行及可用性无需太多关注。但是对于Oracle体系结构了解，有助于帮助软件开发人员编写高效的SQL及过程、函数等程序块，有助于整个项目在数据库环节上性能的提升。

Oracle数据库的结构属于典型的C/S模式，一个运行着的Oracle数据库就可以看成是一个Oracle Server，由实例（Instance）和数据库（Database）组成，一般情况下，一个Oracle Server包含一个实例和一个与之对应的数据库。

实例（Instance）是指内存结构，当数据库启动时，Oralce占用一定的内存空间，实例就是指此时的内存结构和后台进程。当数据库服务器关闭，实例也不存在了。

数据库（Database）是由一些物理文件和与之对应的逻辑结构（表空间、段等）组成的。这些文件包括数据文件、控制文件、重做日志文件、参数文件以及归档日志文件等。

数据库服务器（Database Server）是指实例、数据库以及各软件组件如SQL*Plus等组成。也就是安装在服务器上数据库软件和启动后实例组成。

Step1创建数据库实例:

进入开始->所有程序->Oracle-OraDB11g_home1->配置和移植工具->Database Configuration Assistant

进入后如图所示：默认选项创建数据库，点击下一步：

默认一般事务和用途，点击下一步：

输入数据库名和sid，以student为例，（注：如果提示没有创建监听器，请

在开始->所有程序->Oracle-OraDB11g_home1->配置和移植工具->

Net Configuration Assistant进行配置，这里就不一一熬述了）点击下一步：

打开”步骤4(共14步):管理选项”对话框，配置管理选项，点击下一步：

如果出现监听警告提示，需要开启OracleOraDb11g_home1TNSListener服务:

为不同账户设置不同的口令，我们选择所有账户使用统一管理口令：123456，（实际上DBA应该为不同的账户使用不同的口令，可以选中“使用不同管理口

令”选项，为不同账户输入不同的口令）。点击下一步:

配置存储选项，选择“文件系统”选项，创建的Oracle11g数据库将使用文件系统进行数据库的存储。点击下一步：

数据库文件所在的位置，这里我们 指定要创建数据库文件的位置为“使用模板中的数据库文件位置”点击下一步：

恢复配置对话框，采用默认选项“指定快速恢复区”，点击下一步：

数据库内容对话框，配置数据库内容，点击下一步：

内容配置后，进入初始化参数对话框，选择“字符集”选项卡，设置数据库字符集为GBk还是UTF-8，点击下一步：

安全设置对话框，配置数据库安全选项，这里保持默认设置，点击下一步：

配置自动维护任务对话框，同样保持默认设置，点击下一步：

数据库存储对话框，（DBA对控制文件、数据文件、重做日志文件进行修改），

点击下一步：

选中“创建数据库”选项，当DBA需要将创建数据库以脚本的形式保存下来时，勾选“生成数据库创建脚本”，并指定脚本存放 的目标目录。点击完成：

单击“确定”按钮，完成创建Oracle 11g数据库所有配置工作，正式进入创建过程，将依次经过复制数据文件、创建oralce实例、数据库创建和运行定制脚本4个步骤。

记录下URL: https://lenovo-THINK:5500/em，因为我们等下要用它登陆控制台创建表空间；点击退出完成数据库实例创建。

Step2 Oralce数据库实例的启动和关闭

在IE中打开https://lenovo-THINK:5500/em（如果显示无法找到网站，请在控制面板—管理工具—服务中启动OracleDBConsolet 服务）Oracle实例、Oracle数据库和Oracle服务器。

一般的，数据库分为两个部分：实例（Instance）和数据库（Database）。实例是一个非固定的、基于内存的基本进程与内存结构，当服务器关闭后，实例也就不存在了。

而数据库是固定的、基于磁盘的数据文件、控制文件、日志文件、参数文件和归档日志文件等。

在一般情况下，一个Oracle数据库包含一个实例。默认的，当一个新的数据库创建完成之后，与该数据库实例相关的服务处于自动启动的状态。

Oracle服务的启动和关闭是在后台服务进程方式进行的，如OracleDBConsolestudent、OracleJobSchedulerSTUDENT和OracleServiceSTUDENT等，即可启动关闭数据库。

使用OEM也可以关闭数据库实例，点击“关闭”：

此时要求对用户进行身份验证，分别输入正确的操作主机身份证明（用户名/口令）和数据库身份证明，“输入主机身份证明”指定操作系统的用户名和口令, 指的是你系统的用户名和密码，如果系统没有设置密码请设置一个。设置好单击”确定”按钮。

当关闭操作完成后，即可单击“刷新”按钮返回Database Control页面。

CREATE SESSION --建立会话 CREATE SYNONYM --建立同义词 CREATE VIEW --建立视图

RESOURCE角色： --是授予开发人员的

CREATE CLUSTER --建立聚簇 CREATE PROCEDURE --建立过程 CREATE SEQUENCE --建立序列 CREATE TABLE --建表

CREATE TRIGGER --建立触发器 CREATE TYPE --建立类型

DBA角色：

拥有系统所有系统级权限

IMP_FULL_DATABASE角色、EXP_FULL_DATABASE角色：

BACKUP ANY TABLE --备份任何表

EXECUTE ANY PROCEDURE --执行任何操作 SELECT ANY TABLE --查询任何表

DELETE_CATALOG_ROLE角色：

这个角色是Oracle8新增加的，如果授予用户这个角色，用户就可以从表sys.aud$中删除记录，

sys.aud$表中记录着审计后的记录，使用这个角色可以简化审计踪迹管理。

SELECT_CATALOG_ROLE角色、EXECUTE_CATALOG_ROLE角色：

SELECT_CATALOG_ROLE角色具有从数据字典查询的权利，

EXECUTE_CATALOG_ROLE角色具有从数据字典中执行部分过程和函数的权利。 fromoradb

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库ORACLE课堂讲义(13~18)在线全文阅读。