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

时间：2022-11-08 05:07:34

 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;


--for 循环遍历,最简单,用的最多,不需要声明 v_student、 打开关闭游标、 fetch。declarecursor c_student(v_id binary_integer) isselect * from student where id>v_id;beginfor v_student in c_student(10) loopdbms_output.put_line('name: '||v_student.name);end loop;end;



-- 给对应级别的员工涨工资，key是empno
set serveroutput ondeclare  -- 定义光标代表给哪些员工涨工资 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        EXACTcursor_space_for_time              boolean     FALSEopen_cursors                           integer      300session_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转成数字2225.Timeout_on_resource (等待资源时发生超时，分布式数据库)
--系统例外：no_data_found
--系统例外：no_data_founddeclare  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   涨工资人数：count3、循环推出的条件：      工资总额>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 过程名(参数列表)asbegin..........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 numberas     --定义变量接收薪水和奖金    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 )asbeginselect 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 asprocedure queryEmpList(dno in number,empList out empcursor) asbeginopen 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 securityempbefore 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_salarybefore update on empfor each rowbegin if :new.sal<:odl.sal thenraise_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_salaryafter update on empfor 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;
触发器自增主键： 使用序列和触发
### 建表 ###CREATE TABLE "SPORTS"."LINEUP"("ID" NUMBER NOT NULL, "TYPE" NUMBER(3) NOT NULL, "BODY" VARCHAR2(100) NOT NULL, "HITS" NUMBER(10) DEFAULT 0 NOT NULL, PRIMARY KEY("ID"))TABLESPACE "TS_SPORTS"### 建序列 ###CREATE SEQUENCE "SPORTS"."SPORTS_LINEUP_ID_SEQ" INCREMENT BY 1START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLECACHE 50 NOORDER### 建自动更新的触发器 ###CREATE OR REPLACE TRIGGER "SPORTS"."SPORTS_LINEUP_ID_TRIGGER"BEFORE INSERT ON "SPORTS"."LINEUP"FOR EACH ROWDECLAREnext_id NUMBER;BEGIN--Get the next id number from the sequenceSELECT sports_lineup_id_seq.NEXTVALINTO next_idFROM dual;--Use the sequence number as the primary key--for the record being inserted.:new.id := next_id;END;### 建保护 PRIMARY KEY 的触发器 ###CREATE OR REPLACE TRIGGER "SPORTS"."LINEUP_ID_UPDATE_TRIGGER"BEFORE UPDATE OF "ID" ON "SPORTS"."LINEUP"FOR EACH ROWBEGINRAISE_APPLICATION_ERROR (-20000,'sports_lineup_id_update_trigger : Updates of the ID field'｜｜ 'are not allowed. ');END

 

标签：
相关文章