oracle编程

PL/SQL 编程

---

1. 格式
   
declare
代码块
begin
代码块
end
exception
begin end块是必须有的

2. 声明变量的格式
   
变量名 变量类型 值
a number(5,2) :=2;


3. PL/SQL数据类型

   1. 标量类型
      
      • number
      • character
      • boolean
      • date/time
   2. %type 和 %rowtype
      

      % type 用表的列数据类型定义数据类型
      
      % rowtyoe
      用表的所有数据类型定义类型
      
      salary employees.salary%type;
      emp employees%rowtype;

4. 条件结构
   
   1. if结构
      
if ... then ... end if


if ... then ... else ... end if


if ... then ...
elsif ... then ...
else ...
end if

      条件语句允许嵌套
   2. case
      
case 变量
when ... then ...
when ... then ...
esle
end case;


5. 循环结构

   1. 用exit退出

      lOOP
      ...
      exit ...
      end loop;

   2. while
      ...
      loop
      ...
      end loop;

   3. for counter in [reverse]
      start_range ... end_range
      ...
      end loop;
      
      reverse表示降序

   一个例子

   counter_i:=0;
   loop 
   counter_i:=counter_i+1;
    if  counter_i>20 then  exit  ; end if;
    -- exit when counter_i>20
    end loop;

   6 . 常见异常

EXCEPTION 
    ...
    WHEN PROGRAM_ERROR THEN -- ORA-06501 SQLCODE = -6501 程序错误
      ...
    WHEN TOO_MANY_ROWS THEN -- ORA-01422 SQLCODE = -1422 返回多行
      ...
    WHEN VALUE_ERROR THEN -- ORA-06502 SQLCODE = -6502 数值转换错误
      ...
    WHEN ZERO_DIVIDE THEN -- ORA-01476 SQLCODE = -1476 被零除
      ...
    WHEN OTHERS THEN -- 其它任何错误的处理
      ...
  END;

1. 游标
   
   1. 隐式游标
      
declare
q_salary employees.salary%type
begin
select salary into q_salary from employees where employee_id='10'
dbms_output.putline(...)
end;


declare
q_emp employees%rowtype
begin
select * into q_emp from employees where ....
dbms_output.putline(....)
end;

   2. 显式游标
      
declare
q_salary employees.salary%type;
q_department_id employees.department_id%type;
--声明游标--
cursor q_cur is select salary,department_id from employees where employee_id='200';
begin
--打开游标--
open q_cur
fetch q_cur into q_salary ,q_department_id;
dbms_output.putline(q_salary||q_department_id);
close q_cur;
end;

   3. 游标的遍历
      
      1. 
         
declare
cursor emp_cursor is select empno,ename from emp;
begin
for emp_record in emp_cursor loop
dbms_output.putline(ename||','||empno);
end loop;
end;

      2. 
         
begin
for re in (select ename from emp) loop
dbms_output.putline(ename);
end loop;
end;
该种形式更为简单，省略了游标的定义

      3. loop when循环
         
declare
r_emp emp%rowtype;
cursor c_emp is select * from emp;
begin
open c_emp;
loop
fetch c_emp into r_emp;
exit when c_emp%NOTFOUND;
dbms_output.putline(r_emp.ename||','||r_emp.empno);
end loop;
close c_emp;
end;


2. 存储过程

   1. 格式

      create or replace procedure 过程名
      (参数1 方式1 数据类型，参数2 方式2 数据类型，....） is/as
      begin
      过程体
      end 过程名;

   2. 过程参数类型

      1. in:输入给过程的参数
      2. out：参数在过程中可被赋值，可送给过程外部
      3. in out：in和out的结合
   3. 调用例子
      

      create or replace add_demo(i in int,j in int,k out int) as 
      begin
      k:=i+j;
      end add_demo;
      
      declare
      i int :=1;
      j int :=1;
      k int ;
      begin 
      add_demo(i,j,k);
      dbms_output.putline(k);
      end;

   在过程中声明变量不用declare。
   存储过程可以直接用excute调用。

   1. 函数
      

      create or replace function 方法名
      (参数1 方式1 数据类型，参数2 方式2 数据类型，....） 
      return 返回类型
      is/as
      begin
      函数体
      return ..;
      end 过程名;

   函数的调用和过程一样，删除用drop

   1. 触发器

      1. 格式

         create or replace tigger 名称 (before/after)
         触发事件 on 表名
         [REFERENCING OLD AS OLD NEW AS NEW]
         [for each row [触发条件]]
         begin
         触发器主体
         end  触发器名称;

      2. 实例,编写一个数据库触发器，当任何时候某个国家从nationality表中删除时，该触发器将从authors表中删除该国家的所有作者。

      create or replace  trigger del_au_nat_id
      before   delete 
      on  nationality   
      referencing old as old new as new
      for  each  row  when (old. nationality_id!=0)
      begin
      delete from   authors 
      Where  nationality_id=:old.nationality_id;
      end del_au_nat_id ;