您的位置: 首页  >  IT文章  >  Oracle数据库开发--oracle sql developer工具/存储过程/函数/触发器

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

分类: IT文章 • 2022-04-20 18:55:38

 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;

相关推荐