PL/SQL笔记一

DECLARE  定义以DECLARE开始(可选)
/*
*  定义部分--&gt定义常量.变量.复杂数据类型.游标.例解
*/
BEGIN  执行部分以BEGIN开始(必选)
/*
*  执行部分--&gtPL/SQL语句和SQL语句
*/
EXCEPTION 例外处理以EXCEPTION开始(可选)
/*
*  例外处理部分--&gt处理运行错误
*/
END;  /*块结束标记*/

注意:DECLARE,BEGIN,EXCEPTION后面是没有分号的(;)而END后面必需要带有分号(;)

例:
SQL> set serveroutput on
SQL> begin
  2  dbms_output.put_line('Hello,everyone!');
  3  end;
  4  /
Hello,everyone!

PL/SQL 过程已成功完成。
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
  8  when no_data_found then
  9  dbms_output.put_line('请输入正确的雇员号');
 10  end;
 11  /
输入 no 的值:  999
原值    5: where empno=&no;
新值    5: where empno=999;
请输入正确的雇员号

PL/SQL 过程已成功完成。

NO_DATA_FOUND是PL/SQL的预定义例外.

 

PL/SQL分类
根据实现的应用模块功能,分为,匿名块,命名块,子程序和触发器四种

 

匿名块
 没有名称的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 过程已成功完成。

SQL>

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

命名块
指具有特定名称标识的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);
 14  end;--<>
 15  /
输入 name 的值:  scott
原值    9: where lower(ename)=lower('&name');
新值    9: where lower(ename)=lower('scott');
部门名:RESEARCH

PL/SQL 过程已成功完成。

SQL>
<>主块(外层块)<>子块(内层块)的标记

子程序
子程序包括过程,函数和包

过程
 --过程用于执行特定操作,当建立过程时,即可以指定输入参数(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);

调用完成。

函数
 --用于返回特定数据.当建立函数时,在函数头部必须包含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  /

函数已创建。

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

调用完成。

SQL> print income

    INCOME
----------
     36000

SQL>

 

包
 --用于逻辑组合相关的过程和函数,它由包规范和包体两部分组成.包规范用于定义公用的常量,变量,过程和函数.
在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>

包规范只包含了过程和函数的说明,而没有过程和函数的实现代码.

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

程序包体已创建。

 

调用包
 --当调用包的过程和函数时,在过程和函数名之前必需要带有包名作为前缀(包名.子程序名),而如果要访问其他方案的包,还必需要加上方案名称作为前缀(方案名,包名.子程序名)
SQL> call emp_pkg.update_sal('mfs',1919);

调用完成。

SQL> call emp_pkg.annual_income('scott') into :income;

调用完成。

SQL> print income

    INCOME
----------
      6000

 

 

触发器
 --是指隐含执行的存储过程.当定义触发器时,必需要指定触发时间以及触发操作,常用的触发器时间包括INSERT,UPDATE和DELETE语句,而触发操作实际是一个PL/SQL块
在SQL*Plus中使用 CREATE TRIGGER命令来完成的
例:
SQL> create trigger update_cascade
  2  after update of deptno on dept
  3  for each row
  4  begin
  5  update emp set deptno=:new.deptno
  6  where deptno=:old.deptno;
  7  end;
  8  /

触发器已创建

SQL>

触发器update_cascade用于实现级联更新,如果不建立触发器,那么当更新dept表的deptno列数据时就会显示错误"ORA-02292:违反完整约束条件(SCOTLFK_DEPTNO)-已找到子记录日志";而在建立了触发器之后,当更新deptno列时,就会级联更新emp表的deptno列的相关数据.

验证:
select * from dept;
update dept set deptno=60 where lower(dname)='research'
select * from emp;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22279684/viewspace-682425/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22279684/viewspace-682425/