oracle 编程基础笔记

 

游标函数过程 作者： itdreamlmc@163.com

游标的使用步骤

1)定义游标   cursor 游标名称[(参数列表)] is 子查询语句

cursor mysor is select * from emp;

2)打开游标   open 游标名称

  open mysor;

3)取数据      fetch 游标名称 into 变量名称，.....

fetch mysor into rowdatas;

4)关闭游标  close 游标名称

close mysor;

游标遍历查询实例：

declare

cursor mysor is select * from emp;

rowdatas emp%rowtype;

begin

  open mysor;

  loop

       fetch mysor into rowdatas;

            dbms_output.put_line(rowdatas.ename||'---'||rowdatas.job||'---'||rowdatas.sal);

       exit when mysor%notfound;

  end loop;

  close mysor;

end;

 

存储函数 作者： itdreamlmc@163.com

函数的创建语法：

create [or replace] function 函数名[(参数名 in|out 参数类型,......)] return 返回数据类型 is|as 

begin

     函数体   

     return 结果变量;

end;

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

--定义存储函数

create or replace function getsalbyempno(v_empno in number) return number

is

v_sum number;

begin

  select sal*12 into v_sum from emp where empno=v_empno;

  return v_sum;

end;

--调用函数

select getsalbyempno(7902) from dual;

带多个返回值的存储函数

-----------------------------------------------------------------

实例：定义一个可以查询指定员工的年薪与工作的函数

--定义存储函数

create or replace function getbyempno(v_empno in number,v_job out varchar2) return number

is

v_sum number;

begin

  select sal*12,job into v_sum,v_job from emp where empno= v_empno;

  return v_sum;

end;

--定义测试函数

 

返回游标 类型的函数：

实例：查询指定部门的所有员工工作:

--定义函数

create or replace function findAllJobbydeptno(v_deptno in number) return sys_refcursor

is

mycursor sys_refcursor;

begin

  open mycursor for select job from emp where deptno=v_deptno;

  return mycursor;

end;

--测试函数

select findAllJobbydeptno(20) from dual;

 

触发器 作者： itdreamlmc@163.com

 

触发器的作用

1. 数据确认
   • 实例：员工涨后的工资不能少于涨前的工资
2. 实施复杂的安全性检查
   • 实例：进制在非工作时间插入新想员工
3. 做审计
4. 数据的备份和同步

触发器的类型

1. 语句级触发器：
   1. 在指定的操作语句之前或者之后执行一次，不管这条语句影响了多少行。
2. 行级触发器(for each row)
   1. 触发语句作用的每一条记录都被触发。在行触发器中使用old和new伪记录变量，识别值的状态

定义触发器的语法：

create [or replace] trigger 触发器名 {before|after}

{delete|insert|update[of 列名]}

on 表名

[for each row [when(条件)]]

declare

     ........

begin

     PLSQL块

end 触发器名;

在触发器中触发语句与伪记录值：

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

--定义触发器

create or replace trigger trigger_emp after insert on emp

declare



begin

  dbms_output.put_line('一个新的员工插入成功');

end;

--测试触发器

insert into emp values(7878,'tom','CLERK',7782,sysdate,1600,null,20);

 

范例：修改员工薪资之前，判断修改之后是否增加！

 

----------------------------------触发器：员工修改员工薪资判断-------------------------------

create or replace trigger trigger_emp_sal before update on emp for each row

declare

begin

     if :old.sal>:new.sal then raise_application_error(-20002,'修改后的薪资小于修改前薪资');

     end if;

end;

--测试

select * from emp;

update emp set sal=77 where empno=7878;

 

范例：触发器实现序列自动 增加

--定义测试表

create table test1(

   id number(11) primary key,

   username varchar(30)

);

--定义序列

create sequence myseq;

--定义触发器

create or replace trigger trigger_test1_autoincrement before insert on test1 for each row

declare

begin

  select myseq.nextval into :new.id from dual;

end;

--测试触发器

insert into test1(username) values('jack');

select * from test1;

 

存储过程和存储函数的区别 作者： itdreamlmc@163.com

 

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

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

 

 

存储过程 作者： itdreamlmc@163.com

 

创建存储过程语法：

create [or replace] procedure 过程名[(参数名 in|out 数据类型,....)]

AS|IS

变量的定义声明部分....

begin

     PLSQL子程序体

end  [过程名];

实例：给指定员工涨100工资，并打印涨之前和之后的工资

 

--定义创建过程

create or replace procedure addSal(v_empno in number)

AS

v_old_sal emp.sal%type;

v_new_sal emp.sal%type;

begin

  select sal into v_old_sal from emp where empno=v_empno;

  update emp set sal=sal+100 where empno=v_empno;

  select sal into v_new_sal from emp where empno=v_empno;

  dbms_output.put_line('涨之前工资为：'||v_old_sal||' 涨之后工资：'||v_new_sal);

end;

--调用定义过程

call addSal(7369);

 

定义带返回数据的过程:

---------------------------------------------------------------

实例：查询指定员工的年薪

 

--定义过程

create or replace procedure findSalByempno(v_empno in number,v_annousal out number)

AS

begin

  select sal*12 into v_annousal from emp where empno=v_empno;

end;

--定义存储工程

declare

v_sum number;

begin

  findSalByempno(&num,v_sum);

  dbms_output.put_line('该员工年薪为：'||v_sum);

end;

 

PLSQL中的异常exception 作者： itdreamlmc@163.com

 

异常分类

1. 系统异常
2. 自定义异常

系统异常

1. no_data_found  没有查询到数据
2. too_many_rows 查询的结果数量大
3. zero_divide 被0整除
4. value_error 类型错误
5. timeout_on_resource 处理超时
6. others 其他错误

异常实例:

declare

v_ename emp.ename%type;

v_num1 number(11) :=0;

v_num2 number(11) :=12;

begin

--数据未找到 select ename into v_ename from emp where empno=73693;

--数据结果太多 select ename into v_ename from emp where deptno=20;

--0不能作为除数  v_num1:=v_num2/v_num1;

--数据类型错误 select ename into v_num1 from emp where empno=7369;



exception

  when too_many_rows then  dbms_output.put_line('数据太多');

  when no_data_found then dbms_output.put_line('没有查询到数据');

  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;

 

 

 

转载于:https://my.oschina.net/dreamVip/blog/1531347