Oracle数据库开发--oracle sql developer工具/存储过程/函数/触发器

3/8/2017来源:ASP.NET技巧人气:2427

 Oracle数据库开发--oracle sql developer工具/存储过程/函数/触发器

什么是PL/SQL程序?
(1)PL/SQL( PRocedure Language / SQL)
(2)PLSQL是Oracle对sql预言的过程化扩展
-- 指在SQL命令语言中增加了过程处理语句(如分支、循环等),使SQL 语言具有过程处理能力。

oracle sql developer图形化工具

linux的安装.sh的文件用户名、口令:scott/tiger如果以sys登录,角色要选成SYSDBA主机名是服务器的ip地址,端口是1521;SID是数据库名字,orcl//打开行号:工具-首选项-代码编辑器-行装订线-显示行数//连接MySQL:工具-首选项-第三方JDBC驱动程序-添加条目-mysql...-bin.jar

PL/SQL语法

---打开输出开关(默认关闭)
set serveroutput on
---打印Hello World
declare 
---说明部分(变量、光标和例外)
begin
--程序体
dbms_output.put_line('Hello World');//调用内部存储过程
end;
备注--查看程序包的结构,以dbms_output为例:
desc dbms_output

定义基本变量(名字再前面,类型再后面)

基本类型:char,varchar2(长度),date,number(有效位,小数位),boolean,long 举例:var1 char(15);
引用型变量:emp%type  (引用型变量:表%type 代表类型)
引用型变量
    举例:my_name emp.ename%type;
    引用emp表中ename列的类型作为变量my_name的类型,并且变量的类型始终与其保持一致
赋值方式两种: := 和into关键字
定义(引用),赋值(select S1,S2 into X1,X2 from emp where ),使用dbms_output.put_line(X1||'的薪水'||X2);
记录型变量:emp%rowtype (代表表中的一行的类型,)
记录型变量 代表表中的一行
    举例:emp_rec emp%rowtype;
  记录型变量分量的引用
    emp_rec.ename := 'ADAMS';
declare说明部分——变量定义
--使用基本变量类型
declare 
 --基本数据类型
  pnumber number(7,2);//
  --字符串变量
  pname varchar2(20);
  --日期变量
  pdate date;
begin
  pnumber :=1;
  pname:='Tom';
  pdate:=sysdate;
  DBMS_OUTPUT.PUT_LINE(pnumber);
  DBMS_OUTPUT.PUT_LINE(pname);
  DBMS_OUTPUT.PUT_LINE(pdate);
   --计算明天的日期
  DBMS_OUTPUT.PUT_LINE(pdate+1);
end;

if..then .. elsif

/*
判断用户从键盘输入的数字
1、如何使用if语句
2、接收一个键盘输入(字符串)
*/
set serveroutput on
--接收一个键盘输入
--num:地址值,含义是:在该地址上保存了输入的值
accept num prompt'请输入一个数字';

declare
  --定义变量保存用户从键盘输入的数字
  pnum number := #
begin
  --执行if语句进行条件判断
  if pnum = 0 then dbms_output.put_line("您输入的数字是0");
     elsif pnum = 1 then dbms_output.put_line("您输入的数字是1");
     elsif pnum = 2 then dbms_output.put_line("您输入的数字是2");
     else dbms_output.put_line("其他数字");
  end if;
end;

循环(while,loop,for)推荐使用loop循环,它对于操作光标有优势

oracle中没有自增++  自身=自身+1即可。
WHILE 条件 LOOP ... END LOOP;
LOOP EXIT WHEN 条件  ... END LOOP;
FOR I IN 1..5 (必须为连续区间)LOOP ... ;END LOOP;

光标--就是一个结果集(Result Set)

cursor 光标名[(参数名 数据类型[,参数名 数据类型].....)]
is select 语句:
光标的属性:
%found     %notfound     此为boolen类型
%isopen 判断光标是否打开
%rowcount 影响的行数
--查询并打印员工的姓名和薪水
--光标的属性 %found %notfound
set serveroutput on 
declare
--定义一个光标
cursor cemp is select ename,sal from emp;
--为光标定义对应的变量(引用变量)
pename emp.ename%type;
psal   emp.sal%type;
begin
--打开光标
open cemp;
loop
--抓取一条记录
fetch cemp into pename,psal;
exit when cemp%notfound;
dbms_output.putline(pename||'的薪水是'||pssal);
end loop;
--关闭光标
close cemp;
end;

-- 给对应级别的员工涨工资,key是empno
set serveroutput on
declare 
 -- 定义光标代表给哪些员工涨工资
 cursor cemp is select empno,empjob from emp;
 pempno emp.empno%type;
 pempjob emp.empjob%type;
begin
 rollback; //事务回滚
--打开光标
 open cemp;
 --loop fetch ** into ** ; exit when condition;block;end loop;
 loop
 --取出一个员工
 fetch cemp into pempno,pempjob;
 --loop 退出条件
 exit when cemp%notfound;
 -- 判断员工的职位
  if pempjob='PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
 -- if condition then block ;elsif condition then block;else block;end if;
  elsif pempjob='MANAGER' then update emp set sal=sal+800 where empno=pempno;
  else update emp set sal=sal+400 where empno=pempno;
  end if;
end loop;
-- 关闭光标
close cemp;

 -- oracle的默认事务隔离级别是read committed
 --事务的ACID 原子性、一致性、隔离性、持久性
 commit;
end ;

--使用 show parameter cursors;语句查看包含cursors的参数设置
NAME                                     TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                         string        EXACT
cursor_space_for_time              boolean     FALSE
open_cursors                           integer      300
session_cached_cursors            integer      50

--默认的一个会话最多可以打开300个光标
修改光标数的限制:
alter system set open_cursors=400 scope = both;
    其中scope的取值:both,memory,spfile
    memory:表示只更改当前实例,不更改参数文件
    spfile:表示只更改参数文件,不更改当前示例,数据库服务需要重启
    both:表示上边两个同事更改

例外

系统例外: 1.No_date_found(没有找到数据)
2.Too_many_rows (select...into语句匹配多个行)
3.Zero_Divide (被零除)
4.Value_error (算术或转换错误)  负数开平方等and abc转成数字222
5.Timeout_on_resource (等待资源时发生超时,分布式数据库)

--系统例外:no_data_found

--系统例外:no_data_found
declare
  pename emp.ename%type;
begin
  select ename into pename from emp where empno=222222;
  SYS.DBMS_OUTPUT.PUT_LINE(pename);
  exception 
    when no_data_found then sys.dbms_output.put_line('没有对应的记录');
    when others then sys.dbms_output.put_line('其它例外');
end;

瀑布模型

1.需求分析 2.设计 2.1概要设计 2.2详细设计 3.编码coding 4.测试Testing 5.上线(部署)
案例2:涨工资问题,从最低工资的员工开始涨起,每人涨10%,工资总额不能超过50000,返回涨工资的人数和涨后的工资总额
1、用到的sql语句:
      select empno,sal from emp order by sal;
      select sum(sal) into totalsal from emp;
2、需要声明的变量:
      工资总额:totalsal   涨工资人数:count
3、循环推出的条件:
      工资总额>5W       or        全部员工都涨完工资开启光标>开启循环>取值>退出条件>结束循环>关闭光标*/
set serveroutput on;
declare
  cursor cemp is select empno,sal from emp order by sal;
  pempno emp.empno%type;
  psal emp.sal%type;
  pcount number:=0;
  psum number;
begin
  select sum(sal) into psum from emp;
  open cemp;
    loop
      fetch cemp into pempno,psal;
      exit when cemp%notfound or psum>250000 or psum+psal*0.1>250000;
      update emp set sal=psal*1.1 where empno=pempno;
      psum:=psum+psal*0.1;
      pcount:=pcount+1;
    end loop;
  close cemp;
  commit;
  dbms_output.put_line('涨后的工资总额为'psum);
  dbms_output.put_line('涨工资的总人数为'pcount);
end;

存储过程和存储函数

数据库存储过程:指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数
·相同点:完成特定功能的程序
·不同点:是否用return语句返回值。存储函数可以return返回值。存储过程不可以通过return语句返回函数值。

1.创建存储过程

create or replace procedure 过程名(参数列表)
as
begin
..........PLSQL子程序体;
end;

2.执行存储过程

--1.exec 存储过程名();
--2.begin
     存储过程名();
    end;
带参数的存储过程
 举例:为指定的员工,涨100块钱的工资;并且打印涨前和涨后的薪水
 1、创建一个带参数的存储过程:
 给执行的员工涨100块钱的工资,并且打印涨前和涨后的薪水
 create or replace procedure raisesalary(eno in number)
 as
 --定义一个变量保存涨前的薪水
  psal emp.sal%type;
 begin
 --得到员工涨前的薪水
 select sal into psal from emp where empno=eno;
 --给该员工涨100
 update emp set sal=sal+100 where empno=eno;
 --需不需要commit?
 --注意:一般不在存储过程或存储函数中,commit和rollback
 --打印
 dbms_output.put_line('涨前:'||psal||'涨后:'(pasl+100))
 end;
 /
 2、如何调用:
 begin
  raisesalary(7839);
  raisesalary(7566);
 commit;
 end;

存储过程的调试

1.调试存储过程最好放到Oracle数据库所在的系统或虚拟机上,解压SQL developer ,双击运行。
2.为了确保存储过程或函数是可调试的,右键“以编译并进行调试”,点击红色按钮“调试”
3.利用已写好的调用函数进行调试。

1.函数的定义

是一个命名的存储程序,可带参数,并返回一个计算值。必须有return 子句,用于返回函数值。

2.创建存储函数语法

create or replace function 函数名(参数列表) return 函数值类型 as begin PLSQL子程序体; end;

3.表达式中某个字段为空时,表达式返回值为空。为防止含有表达式的返回值错误,在可能为空的字段上加上NVL(字段名,0)。

--查询某个员工的年收入
create or replace function queryemp_income(eno number) return number
as 
    --定义变量接收薪水和奖金
    p_sal emp.sal%type;
    p_comm emp.comm%type;
begin

  select sal,comm into p_sal,p_comm from emp where empno=eno;
  --nvl为遇空函数,如果p_comm为空则返回0
  return nvl(p_comm,0)+p_sal*12;
end;
/

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

存储函数可以有一个返回值,存储过程没有返回值

2.in out 参数

存过和函数都可以通过out 指定一个或多个输出参数。可以利用out参数,实现多个返回值。

3.使用存过和存储函数的原则

只有一个返回值的话,用存储函数;否则,用存储过程。
create or replace procedure query
(eno in numbr,
 pename out varchar2,
psal out  number,
pjob out varchar2
 )
as
begin
select ename,sal,empjob into pename,psal,pjob from emp where  mpno=eno;
end

在out参数中使用光标

·申明包结构 包头(申明) 包体(实现) ·案例:查询某个部门中所有员工的所有信息 //ref(reference引用) cursor(光标)

#包头

create or replace package mypackage as
type empcursor is ref cursor;
procedure queryEmpList(dno in number,empList out empcursor);
end mypackage;

#包体

create or replace package body mypackage as
procedure queryEmpList(dno in number,empList out empcursor) as
begin
open empList for select * from emp where deptno=dno;
end queryEmpList;
end mypackage;***********包体需要实现包头中声明的所有方法*********************

触发器

应用场景:

1.复杂的安全性的场景(涉及到权限的问题);
例子:下班时间不能插入数据库;
2.数据的确认(涉及数据是否合理问题);
例子:涨工资越涨越高,低了就不能修改;
3.数据的审计(涉及到数据的增、删、改的操作记录)---Oracle自身已经实现了审计;
例子:把操作的时间、帐户等信息记录下来;4.数据的备份和同步(备份和同步重要);
例子:不同的数据表间进行同步备份

什么是触发器:

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

触发器:

1.创建触发器的语法

  create or replace trigger 触发器名称
  before (after)
  delete (insert update) [of 列名] --of 列名表示该列发生变化时,触发该触发器
  on 表名
  [for each row[when条件]] --行级触发器的关键字
  PLSQL块

2.触发器的两种类型

  语句级触发器:不管这条语句影响多少行,只执行一次(针对表)
  行级触发器:每影响一行,都被触发一次。
行级触发器中使用:old :new伪记录变量(针对行)第一个触发器:每当成功插入新员工后,自动打印“成功插入新员工”触发器单词:trigger
create trigger saynewem //创建触发器名称
after insert //在插入操作以后
on emp //针对emp的表
declare //操作体
begin
//触发器操作的内容 
end;

触发器案例一 : 复杂的安全性检查

例如禁止在非工作时间插入数据
/**
  1.周末: to_char(sysdate,'day') in ('星期六',‘星期日’)
  2.上班前,下班后: to_number(to_char(sysdate,'hh24')) not between 9 and 18
/
create or replace trigger securityemp
before insert 
on emp 
begin 
  if to_char(sysdate,'day') in ('星期六', '星期日') or
    to_number(to_char(sysdate,'hh24')) not between 9 and 18 then

   raise_application_error(-20001,'禁止在非工作时间插入新员工');
 end if;
end;

触发器案例二: 数据的确认

涨工资不能越涨越少
:old 表示操作该行之前这一行的值
:new 表示操作该行之后这一行的值
create or replace trigger check_salary
before update 
on emp
for each row
begin 
if :new.sal<:odl.sal then
raise_application_error(-20002,'涨后薪水不能少于涨前薪水。 涨后薪水为:'||:new.sal ||'涨前的薪水:'||:old.sal);
end if;
end;

触发器案例三:基于值的审计

例子:给员工涨工资,当涨后的薪水超过6000块时候,审计该员工的信息
--创建表,用于保存审计信息
create table audit_info(
information varchar2(200)
);
create or replace trigger do_audit_emp_salary
after update 
on emp
for each row 
begin 
if :new.sal>6000 then 
insert into audit_info values(:new.empno||' '||:new.ename||' '||:new.sal);
end if;
end;

触发器应用场景四: 数据的备份和同步

例子:当给员工涨完工资后,自动备份新的工资资料到备份表中
create or replace trigger trigger_sync_salary
after update 
on emp
for each row 
begin
update emp_back set sal=:new.sal where empno=:new.empbo;
end;