PLSQL基础一

PL/SQL编程基础 1 / 19 PL/SQL块语法格式: DECLARE 定义以DECLARE开始(可选) /* * 定义部分-->定义常量.变量.复杂数据类型.游标.例解 */ BEGIN 执行部分以BEGIN开始(必选) /* * 执行部分-->PL/SQL语句和SQL语句 */ EXCEPTION 例外处理以EXCEPTION开始(可选) /* * 例外处理部分-->处理运行错误 */ END; /*块结束标记*/

注意:DECLARE,BEGIN,EXCEPTION后面是没有分号的(;)而END后面必需要带有分号(;)/*…*/(多行注释符号) 例: SQL> set serveroutput on <==执行过程的时候，显式输出结果 on功能打开/off功能关闭 SQL> begin 2 dbms_output.put_line('Hello,everyone!'); 3 end; 4 / Hello,everyone!

dbms_output是oracle所提供的系统包,put_line是该包所包含的过程,用于输出字符串信息.当使用dbms_output包输出数据或消息时,必需要将SQL*Plus的环境变量serveroutput 设置on

例2:带定义部分 SQL> declare 2 v_ename varchar2(5); 3 begin 4 select ename into v_ename from emp 5 where empno=&no; 6 dbms_output.put_line('雇员名:'||v_ename); 7 end; 8 / 输入 no 的值: 7369 原值 5: where empno=&no; 新值 5: where empno=7369; 雇员名:mfs

例3:带例外部分 SQL> declare 2 v_ename varchar2(5); 3 begin 4 select ename into v_ename from emp 5 where empno=&no; 6 dbms_output.put_line('雇员名:'||v_ename); 7 exception PL/SQL编程基础 2 / 19

8 when no_data_found then <== NO_DATA_FOUND是PL/SQL的预定义例外. 9 dbms_output.put_line('请输入正确的雇员号'); 10 end; 11 / 输入 no 的值: 999 原值 5: where empno=&no; 新值 5: where empno=999; 请输入正确的雇员号

根据实现的应用模块功能,分为,匿名块,命名块,子程序和触发器四种 1匿名块 没有名称的PL/SQL块 例: SQL> declare 2 v_avgsal number(6,2); 3 begin 4 select avg(sal) into v_avgsal from emp 5 where deptno=&no; 6 dbms_output.put_line ('平均工资;'||v_avgsal); 7 end; 8 / 输入 no 的值: 20 原值 5: where deptno=&no; 新值 5: where deptno=20; 平均工资;2300.78 PL/SQL 过程已成功完成。

执行以上PL/SQL块,会根据输入的部门号显示部门平均工资,但因为该PL/SQL块直接以DECLARE开始,没有给出任何名称,所以该PL/SQL块属于匿名块

2命名块 指具有特定名称标识的PL/SQL块,命名块和匿名块非常类似,只不过在PL/SQL块前使用<< >> 加以标记.当使用嵌套块时,为了区分多级嵌套层次关系,可以使用命名块加以区分 例: SQL> <> 2 declare 3 v_deptno number(2); 4 v_dname varchar2(10); 5 begin 6 <> 7 begin 8 select deptno into v_deptno from emp 9 where lower(ename)=lower('&name'); 10 end;--<> 11 select dname into v_dname from dept 12 where deptno=v_deptno; 13 dbms_output.put_line('部门名:'||v_dname); PL/SQL编程基础 3 / 19

14 end;--<> 15 / 输入 name 的值: scott 原值 9: where lower(ename)=lower('&name'); 新值 9: where lower(ename)=lower('scott'); 部门名:RESEARCH PL/SQL 过程已成功完成。 注解:<>主块(外层块)<>子块(内层块)的标记

3子程序 子程序包括过程,函数和包 3.1过程 --过程用于执行特定操作,当建立过程时,即可以指定输入参数(IN),也可以指定输出参数(OUT).通过在过程中使用输入参数,可以讲应用环境的数据传递到执行部分;通过使用输出参数,可以将执行部分的数据传递到应用环境. 在SQL*Plus中可以使用CREATEPROCEDURE命令建立过程 例: SQL> create procedure update_sal( 2 name varchar2,newsal number) 3 is 4 begin 5 update emp set sal=newsal 6 where lower(ename)=lower(name); 7 end; SQL> / 过程已创建。

调用过程 SQL> exec update_sal('scott',2000) PL/SQL 过程已成功完成。 或 SQL> call update_sal('scott',3000); 调用完成。

3.2函数 --用于返回特定数据.当建立函数时,在函数头部必须包含RETURN子句,而在函数体内必须要包含RETURN语句返回数据. 在SQL*Plus中可以使用CREATE FUNCTION命令简历函数 SQL> create function annual_income(name varchar2) 2 return number is 3 annual_salary number(7,2); 4 begin 5 select sal*12+nvl(comm,0) into annual_salary 6 from emp where lower(ename)=lower(name); 7 return annual_salary; 8 end; 9 / 函数已创建。 PL/SQL编程基础 4 / 19

调用函数 --调用函数时,可以使用多种方法.这里使用SQL*Plus绑定变量存放输出结果 SQL> var income number SQL> call annual_income('scott') into :income; 调用完成。

SQL> print income INCOME ---------- 36000

3.3包 --用于逻辑组合相关的过程和函数,它由包规范和包体两部分组成.包规范用于定义公用的常量,变量,过程和函数. 在SQL*Plus中建立包规范可以使用CREATE PACKAGE命令 例: SQL> create package emp_pkg is 2 procedure update_sal( 3 name varchar2,newsal number); 4 function annual_income(name varchar2) 5 return number; 6 end; 7 / 程序包已创建。

包规范只包含了过程和函数的说明,而没有过程和函数的实现代码. 包体用于实现包规范中的过程和函数,在SQL*Plus中使用CREATE PACKAGE BODY命令 例: SQL> create package body emp_pkg is 2 procedure update_sal(name varchar2,newsal number) 3 is 4 begin 5 update emp set sal=newsal 6 where lower(ename)=lower(name); 7 end; 8 function annual_income(name varchar2) return number 9 is 10 annual_salary number(7,2); 11 begin 12 select sal*2+nvl(comm,0) into annual_salary 13 from emp where lower(ename)=lower(name); 14 return annual_salary; 15 end; 16 end; 17 / 程序包体已创建。

调用包 --当调用包的过程和函数时,在过程和函数名之前必需要带有包名作为前缀(包名.子程序名),而如果要