Oracle学习笔记2-pl/sql

最近在学习pl/sql
特此记录。
pl/sql 是oracle扩展的一门过程性语言。在sql的基础上增加过程处理语句（分支，循环）。
举例为证：
eg：给员工涨10%的工资
update emp set sal=sal*1.1
这时候我们一条语句就能搞定
但是，董事长涨1000，经理800，其他人500呢
这个怎么办？ pl/sql
ok
…
我们先来看看PL/SQL的语法

declare
   声明部分；
 begin
    语句序列
  exception
   异常处理
 end;

常量和变量的定义

declare
  pname varchar2(20);
  married  boolean :=true;
  my_name emp.ename%type;
  psal number(7,2);
  emp_src  emp%rowtype；

注：
pname 说明变量
my_name 引用型变量，其类型与emp表中ename的类型一样
emp_src 记录性变量
sql中 := 代表赋值

在sql语句中使用关键字into 来进行赋值。
引用变量：

declare
 my_name emp.ename%type;
begin
select t.ename into my_name from emp t where t.empno=1234
end;

记录型变量：

 emp_src  emp%rowtype；

记录型变量的引用：

emp_src.ename := ‘ADMIN’

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

if语句

语法：

1  IF  条件  THEN
    语句1；
    END IF;

2  IF 条件  THEN 语句序列1；
   ELSE  语句序列
   END IF;

3  IF 条件  THEN  语句
    ELSIF  语句  THEN  语句
    ELSE  语句；
    END IF;

示例：
从控制台输入1，则输出‘你输入了1’
其他的输出，‘你输出的不是1’

declare
  pnum number :=&pum;
begin 
  if pnum=1 then 
    dbms_output.put_line('你输出的是1');
    else
      dbms_output.put_line('你输出的不是1');
  end if;
end;

利用elsif来判断
18岁以下未成年
18岁到40成年
40岁以上老年

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

循环Loop
语法

1. while  total<500
      loop 
      ......
      total :=total +1;
      end loop;

2  loop 
    exit when total >500
    total :=total+1;
    end loop;

3  for  i  in 1...500
    loop
    total :=total+1;
    end loop;

输出1到10的数字
示例一：

  declare
      step number :=0;
      begin
      while step<10
        loop
          step :=step+1;
          dbms_output.put_line('输出'||step);
        end loop;
      end;

示例二

 declare
    step number :=0;
    begin
      loop
        exit when step>9;
          step:=step+1;
          dbms_output.put_line('输出'||step);
      end loop;
end;

示例三：

begin
 for i in 1..10 
   loop
    dbms_output.put_line('输出'||i);
    end loop;
end;

游标：Cursor
我们知道Java有集合的概念，可以存储多条数据，同样在数据库，我们也可能返回多条数据，这时候我们就要用到游标了，游标可以存储返回的多条数据
语法：
CURSOR 游标名 [（参数名 数据类型，参数名 数据类型）] is select …

示例：cursor c1 is select ename from emp
游标的使用步骤
1 打开游标 open cursor
2 取一行游标的值 fetch c1 into pjob;
3 关闭游标
游标的结束方式：exit when c1%notfound
注意：pjob的类型必须与emp表的类型一致

pjob emp.ename%type;
或
pjob emp%rowtype;

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

带参数的游标示例：

declare
cursor pc(cname emp.ename%type) is select eid from emp t where t.ename=cname;
myid emp.eid%type;
begin
  open pc('mrxu||i');
    loop
       fetch  pc into myid;
       exit when pc%notfound;
       dbms_output.put_line(myid);
    end loop;
end;

例外（异常处理）
异常是程序设计语言提供的一种功能，用来增强程序的容错。
系统定义异常

1. no_data_found (找不到数据)
2. too_many_rows(select into 匹配多个行)
3. zero_divide (零除异常)
4. value_error (算术或转换错误)
5. timeout_on_resource(在等待资源时发生超时)

示例：
0除异常

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;

用户也可以自定义异常
声明一下就ok了。

declare
   no_data  exception;

抛出异常

  raise   no_data;

二，存储过程
存储过程在大型数据库经常被用到，是一组为了完成特定功能的sql语句集，经编译后存储在数据库，用户通过指定名字并传参来调用它。

存储过程创建的语法：

create [or replace] procedure 过程名[(参数名  in/out  参数类型)]
AS
begin
   pl/sql子程序体；
end;

– 或者

 create [or replace] procedure 过程名[(参数名  in/out  参数类型)]
 is
 begin
     pl/sql子程序体；
 end 过程名;

存储过程示例：
给指定eno的员工涨工资 默认是1

create or replace procedure updsal(eno in number default 1) is  
pemp emp%rowtype;
asal emp.psal%type;
begin
  select * into pemp from emp where eid=eno;
  update emp t set psal=psal+10 where t.eid=eno;
  select  psal into asal from emp t where t.eid=eno; 
  dbms_output.put_line('涨薪前'||pemp.psal);
  dbms_output.put_line('涨薪后'||asal);
end updsal;

存储过程调用：

begin
  updsal(eno =>2)
end;

存储函数：
语法：

create or replace function 函数名 (参数名  参数类型，...) 
return 数据类型
is
结果变量  结果类型
begin
   return （结果变量）；
end;

一般来说，存储过程和存储函数的区别在于存储函数可以有一个 return 返回值
而存储过程没有。
但是二者都可通过制定out 参数来实现返回。
示例：

create or replace function empsal(eno in emp.eid%type default 1) 
return number is
  sal emp.psal%type;
  begin 
    select psal into sal from emp e where e.eid=eno; 
    return sal;
    end; 

存储函数的调用

 declare 
    sal emp.psal%type;
    begin
      sal:= empsal();
      dbms_output.put_line(sal);
      end;

将以上存储函数转换为存储过程

create or replace procedure pempsal(eno in emp.eid%type default 1,sal out emp.psal%type)
 is
 begin 
   select psal into sal from emp e where e.eid=eno;
 end;

存储过程的调用

 declare
  sal emp.psal%type;
 begin 
   pempsal(1,sal);
   dbms_output.put_line(sal);
   end;

java调用存储过程
数据库连接：

String driver ="oracle.jdbc.OracleDriver";
String url="jdbc:oracle:thin:@192.168.179.136:1521:orcl";
String username="...";
String pwd="...";

…略

触发器：
触发器的的作用

• 数据确认
  示例： 员工涨薪后工资不能少于涨薪前
• 实施复杂的安全性检测
  示例：禁止在工作时间插入新员工
• 做审计，跟踪表上的数据操作
• 数据的备份与同步

触发器的类型：
1.语句级触发器
在指定的操作语句操作之前或之后执行一次，不管这条瑜伽班影响了多少行
2.行级触发器
触发语句的每一条记录都会被触发，在行触发器使用old和new为记录变量，识别值的状态。

触发器定义语法：

CREATE OR REPLACE TRIGGER
{BEFORE | AFTER }
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
[FOR EACH ROW [WHERE(条件)]]
declare
    ........
 begin
    plsql块
  end;
end 触发器名；

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

create or replace trigger testTrigger
after insert on emp
declare
  begin
    dbms_output.put_line('成功插入一个员工信息');
  end;

插入测试–>控制台输出信息。

示例二：
不能在规定时间执行插入信息:

create or replace trigger testTrigger1
before insert on emp
declare
weekend varchar2(10);
begin
  select to_char(sysdate,'day') into weekend from dual;
  if weekend='thursday'
    then
      raise_application_error(-20001,'不能在规定时间插入员工信息');
      end if;
  end ValidInserttestTrigger1;

到目前为止，对于PL/Sql 还停留在简单的理解与使用下。