4.pl/sql

什么是PL/SQL?

PL/SQL（Procedure Language/SQL）

PLSQL是Oracle对sql语言的过程化扩展，指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来，使得PLSQL面向过程但比过程语言简单、高效、灵活和实用。

范例1：为职工长工资，每人长10％的工资。
Update emp set sal=sal*1.1

范例2：例2: 按职工的职称长工资,总裁长1000元,经理长800元，其他人员长400元。
这样的需求我们就无法使用一条SQL来实现，需要借助其他程序来帮助完成，也可以使用pl/sql。

1. pl/sql程序语法

1.程序语法：

declare
      说明部分    （变量说明，游标申明，例外说明 〕 
begin
      语句序列   （DML语句〕… 
exception
      例外处理语句   
End;

2.常量和变量定义

在程序的声明阶段可以来定义常量和变量。

• 变量的基本类型就是oracle中的建表时字段的变量如char, varchar2, date, number, boolean, long)，
  定义语法：

varl  char(15); 
Psal  number(9,2);

说明变量名、数据类型和长度后用分号结束说明语句。
常量定义：married boolean:=true

• 引用变量
  Myname emp.ename%type;
  引用型变量，即my_name的类型与emp表中ename列的类型一样
  在sql中使用into来赋值

declare 
  emprec emp.ename%type;
begin
  select t.ename into emprec from emp t where t.empno = 7369;
  dbms_output.put_line(emprec);
end;

• 记录型变量
  Emprec emp%rowtype

记录变量分量的引用
emp_rec.ename:='ADAMS';

declare
  p emp%rowtype;
begin
  select * into p from emp t where t.empno = 7369;
  dbms_output.put_line(p.ename || ' ' || p.sal);
end;

3.if分支

语法1：
      IF   条件  THEN 语句1;
      语句2; 
      END IF;
语法2：
      IF  条件  THEN  语句序列1；   
      ELSE   语句序列 2；
      END   IF； 
语法3：
      IF   条件  THEN 语句;
      ELSIF  条件  THEN  语句;
      ELSE  语句;
END  IF; 

范例1：如果从控制台输入1则输出我是1

declare
  pnum number := #
begin
  if pnum = 1 then
    dbms_output.put_line('我是1');
  end if;
end;

范例2：如果从控制台输入1则输出我是1否则输出我不是1

declare
  mynum number := #
begin
  if mynum = 1 then
    dbms_output.put_line('我是1');
  else
    dbms_output.put_line('我不是1');
  end if;
end;

范例3:判断人的不同年龄段18岁以下是未成年人，18岁以上40以下是成年人，40以上是老年人

declare
  mynum number := #
begin
  if mynum < 18 then
    dbms_output.put_line('未成年人');
  elsif mynum >= 18 and mynum < 40 then
    dbms_output.put_line('中年人');
  elsif mynum >= 40 then
    dbms_output.put_line('老年人');
  end if;
end;

4.LOOP循环语句

其中语法2比较常用

语法1：
    WHILE  total  <= 25000  LOOP
    ……
    total : = total + salary;
    END  LOOP; 

语法2：
    Loop
    EXIT [when   条件];
    ……
    End loop

语法3：
    FOR   I   IN   1 …… 3    LOOP
    语句序列 ;
    END    LOOP ; 

范例:使用语法1输出1到10的数字

declare
  step number := 1;
begin
  while step <= 10 loop
    dbms_output.put_line(step);
    step := step + 1;
  end loop;
end;

范例:使用语法2输出1到10的数字

declare
  step number := 1;
begin
  loop
    exit when step > 10;
    dbms_output.put_line(step);
    step := step + 1;
  end loop;
end;

范例:使用语法3输出1到10的数字

declare
  step number := 1;
begin
  for step in 1 .. 10 loop
    dbms_output.put_line(step);
  end loop;
end;

5.游标Cursor

在写java程序中有集合的概念，那么在pl/sql中也会用到多条记录，这时候我们就要用到游标，游标可以存储查询返回的多条数据。

语法：
CURSOR 游标名 [ (参数名 数据类型,参数名 数据类型,...)] IS SELECT 语句;
例如：cursor c1 is select ename from emp;

游标的使用步骤：

• 打开游标： open c1; (打开游标执行查询)
• 取一行游标的值：fetch c1 into pjob; (取一行到变量中)
• 关闭游标： close c1;(关闭游标释放资源)
• 游标的结束方式 exit when c1%notfound
• 注意： 上面的pjob必须与emp表中的job列类型一致：
  定义：pjob emp.empjob%type;

范例1：使用游标方式输出emp表中的员工编号和姓名

declare
  cursor pc is
    select * from emp;
  pemp emp%rowtype;
begin
  open pc;
  loop
    fetch pc
      into pemp;
    exit when pc%notfound;
    dbms_output.put_line(pemp.empno || ' ' || pemp.ename);
  end loop;
  close pc;
end;

范例2：按员工的工种长工资,总裁1000元，经理长800元其，他人员长400元。

declare
  cursor pc is
    select * from myemp;
  addsal myemp.sal%type;
  pemp   myemp%rowtype;
begin
  open pc;
  loop
    fetch pc
      into pemp;
    exit when pc%notfound;
    if pemp.job = 'PRESIDENT' then
      addsal := 1000;
    elsif pemp.job = 'MANAGER' then
      addsal := 800;
    else
      addsal := 400;
    end if;
    update myemp t set t.sal = t.sal + addsal where t.empno = pemp.empno;
  end loop;
  close pc;
end;

范例3：写一段PL/SQL程序，为部门号为10的员工涨工资。

declare
  cursor pc(dno myemp.deptno%type) is
    select empno from myemp where deptno = dno;
  pno myemp.empno%type;
begin
  open pc(20);
  loop
    fetch pc
      into pno;
    exit when pc%notfound;
    update myemp t set t.sal = t.sal + 1000 where t.empno = pno;
  end loop;
  close pc;
end;

6.例外

例外是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。
系统定义例外

      no_data_found    (没有找到数据)
      too_many_rows          (select …into语句匹配多个行) 
      zero_divide   ( 被零除)
      value_error     (算术或转换错误)
      timeout_on_resource      (在等待资源时发生超时)

范例1：写出被0除的例外的plsql程序

declare
  pnum number;
begin
  pnum := 1 / 0;
exception
  when zero_divide then
    dbms_output.put_line('被0除');
  when value_error then
    dbms_output.put_line('数值转换错误');
  when others then
    dbms_output.put_line('其他错误');
end;

用户也可以自定义例外，在声明中来定义例外

DECLARE
    My_job   char(10);
    v_sal   emp.sal%type;
    No_data    exception;
cursor c1 is select distinct job from emp    order by job;

如果遇到异常我们要抛出raise no_data;

范例：查询部门编号是50的员工

declare
  no_emp_found exception;
  cursor pemp is
    select t.ename from emp t where t.deptno = 50;
  pename emp.ename%type;
begin
  open pemp;
  fetch pemp
    into pename;
  if pemp%notfound then
    raise no_emp_found;
  end if;
  close pemp;
exception
  when no_emp_found then
    dbms_output.put_line('没有找到员工');
  when others then
    dbms_output.put_line('其他错误');
end;

2.存储过程

    存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

创建存储过程语法：

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]  
AS 
begin
        PLSQL子程序体；
End;

或者

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]  
is
begin
        PLSQL子程序体；
End  过程名;

范例：创建一个输出helloword的存储过程

create or replace procedure helloworld is
begin
  dbms_output.put_line('helloworld');
end helloworld;

调用存储过程
在plsql中调用存储过程

begin
  -- Call the procedure       
  helloworld;
end;

范例2：给指定的员工涨100工资，并打印出涨前和涨后的工资
分析：我们需要使用带有参数的存储过程

create or replace procedure addSal1(eno in number) is
  pemp myemp%rowtype;
begin
  select * into pemp from myemp where empno = eno;
  update myemp set sal = sal + 100 where empno = eno;
  dbms_output.put_line('涨工资前' || pemp.sal || '涨工资后' || (pemp.sal + 100));
end addSal1;

调用

begin
  -- Call the procedure
  addsal1(eno => 7902);     
  commit;
end;

3.存储函数

create or replace function 函数名(Name in type, Name in type, ...) return 数据类型 is
  结果变量 数据类型;
begin  
  return(结果变量);
end函数名;

存储过程和存储函数的区别

• 一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。但过程都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和中实现返回多个值。

范例：使用存储函数来查询指定员工的年薪

create or replace function empincome(eno in emp.empno%type) return number is
  psal  emp.sal%type;
  pcomm emp.comm%type;
begin
  select t.sal into psal from emp t where t.empno = eno;
  return psal * 12 + nvl(pcomm, 0);
end;

使用存储过程来替换上面的例子

create or replace procedure empincomep(eno in emp.empno%type, income out number) is
  psal emp.sal%type;
  pcomm emp.comm%type;
begin
  select t.sal, t.comm into psal, pcomm from emp t where t.empno = eno;
  income := psal*12+nvl(pcomm,0);
end empincomep;

调用：

declare
  income number;
begin
  empincomep(7369, income);
  dbms_output.put_line(income);
end;

使用java代码调用存储过程和函数

             /* Class.forName("oracle.jdbc.OracleDriver");
		Connection conn = null;
		conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl",
				"scott", "tiger");
		CallableStatement call = conn.prepareCall("{call countyearsal(?,?)}");
		call.setInt(1, 7369);
		
		call.registerOutParameter(2, OracleTypes.NUMBER);
		call.execute();
		int sum = call.getInt(2);
		System.out.println(sum); */
		
		Class.forName("oracle.jdbc.OracleDriver");
		Connection conn = null;
		conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl",
				"scott", "tiger");
		CallableStatement call = conn.prepareCall("{?= call countyearsal1(?)}");
		call.registerOutParameter(1, OracleTypes.NUMBER);
		call.setInt(2, 7369);
		
		call.execute();
		int sum = call.getInt(1);
		System.out.println(sum);

4.触发器

    数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。

触发器可用于

• 数据确认
• 实施复杂的安全性检查
• 做审计，跟踪表上所做的数据操作等
• 数据的备份和同步

触发器的类型

• 语句级触发器 ：在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行 。
• 行级触发器（FOR EACH ROW） ：触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量, 识别值的状态。

语法:

CREATE  [or REPLACE] TRIGGER  触发器名
   {BEFORE | AFTER}
   {DELETE | INSERT | UPDATE [OF 列名]}
   ON  表名
   [FOR EACH ROW [WHEN(条件) ] ]
begin
   PLSQL 块 
End 触发器名

范例：插入员工后打印一句话“一个新员工插入成功”

create or replace trigger testTrigger
  after insert on person  
declare
  -- local variables here
begin
  dbms_output.put_line('一个员工被插入');
end testTrigger;

范例：不能在休息时间插入员工

create or replace trigger validInsertPerson
  before insert on person

declare
  weekend varchar2(10);
begin
  select to_char(sysdate, 'day') into weekend from dual;
  if weekend in ('星期一') then
    raise_application_error(-20001, '不能在非法时间插入员工');
  end if;
end validInsertPerson;

当执行插入时会报错

在触发器中触发语句与伪记录变量的值

触发语句	:old	:new
Insert	所有字段都是空(null)	将要插入的数据
Update	更新以前该行的值	更新后的值
delete	删除以前该行的值	所有字段都是空(null)

范例：判断员工涨工资之后的工资的值一定要大于涨工资之前的工资

create or replace trigger addsal4p
  before update of sal on myemp
  for each row
begin
  if :old.sal >= :new.sal then
    raise_application_error(-20002, '涨前的工资不能大于涨后的工资');
  end if;
end;

调用

update myemp t set t.sal = t.sal - 1;

create tablespace my0108
logging   
datafile 'D:\oracle\product\10.2.0\oradata\orcl\my0108.dbf'   
size 32m   
autoextend on   
next 32m maxsize 2048m   
extent management local;