存储过程、函数、触发器、优化方案

/*显示游标：操作查询结果集(操作select语句返回的查询结果)
使用：声明---打开游标---读取游标---有数据就继续读取，没有数据就关闭。
*/


declare 
 /* 声明一个带参数的查询游标*/
 cursor cur_emp(var_job in varchar2)
 is select empno,ename,sal 
 from emp
 where job=var_job;
 /*定义一个record记录表*/
 type record_emp is record
 (
      var_empNo emp.empno%type,
      var_ename emp.ename%type,
      var_sal emp.sal%type
 );
 /*声明定义的record变量，在下面使用*/
 emp_row record_emp;
 begin
   /*打开游标，并且传入查询参数*/
   open cur_emp('MANAGER');
   /*抓取游标数据，并且把数据放入record类型变量*/
   fetch cur_emp into emp_row;
  /* 判断抓取的结果集中是否有记录。*/
   while cur_emp%found loop 
     dbms_output.put_line(emp_row.var_sal);
     /*抓取结果集让while判断是否有记录*/
     fetch cur_emp into emp_row;
     end loop;
     close cur_emp;
   end;

 /*存储过程*/


临时的pl、sql块都是保存在临时缓存中，当退出时pl、sql块就消失了。
命名的sql块可以保存到数据库中以供重复使用：存储过程、函数、触发器、程序包
/*1.创建存储过程
命名的pl、sql块，可以没有参数，可有多个输入，多个输出参数。但是通常没有返回值。执行效率高。
create [ or replace ] procedure pro_name [(para1[,para2]...)] is|as 
begin 
       plsql_sentences;
exception
  dowith sentences;
end
2.存储过程的参数
in模式参数
输入类型参数，参数值由调用方传入，并且只能被存储过程读取。
(1)指定名称传递：参数名=>参数值
(2)按位置传递：按参数位置直接传值
(3)混合方式传递：采用以上两种方式的混合
out模式参数
输出类型的参数，这个参数已经在存储过程中被赋值，并且这个参数可以传递到当前存储过程以外的环境中。


3.in参数的默认值*/
/*
声明in参数的同时给出具体默认值，这样存储过程调用时，如果in参数没有参数传入，则存储过程可以使用默认值进行操作。
*/


--1最简单的存储过程
create procedure pro_insertDept is
begin
  insert into dept values(77,'市场拓展部','JILIN'); --插入数据记录
  commit; --提交数据
  dbms_output.put_line('插入新记录成功！'); --提示插入记录成功
end pro_insertDept;
--2如果有则替换新的
create or replace procedure pro_insertDept is
begin
  insert into dept values(99,'市场拓展部','BEIJING'); --插入数据记录
  commit; --提交数据
  dbms_output.put_line('插入新记录成功！'); --提示插入记录成功
end pro_insertDept;
--3带in参数的存储过程
create or replace procedure insert_dept(
  num_deptno in number, --定义in模式的变量，它存储部门编号
  var_ename in varchar2, --定义in模式的变量，它存储部门名称
  var_loc in varchar2) is
begin
  insert into dept
  values(num_deptno,var_ename,var_loc); --向dept表中插入记录
  commit; --提交数据库
end insert_dept;
--4带out参数的存储过程
create or replace procedure select_dept(
  num_deptno in number,--定义in模式变量，要求输入部门编号
  var_dname out dept.dname%type,--定义out模式变量，可以存储部门名称并输出
  var_loc out dept.loc%type) is
begin
  select dname,loc
  into var_dname,var_loc
  from dept
  where deptno = num_deptno;--检索某个部门编号的部门信息
exception
  when no_data_found then --若select语句无返回记录
    dbms_output.put_line('该部门编号的不存在');--输出信息
end select_dept;
/
/*4.2执行带输出参数的存储过程*/
set serverout on
declare
  var_dname dept.dname%type;
  var_loc dept.loc%type;
begin
  select_dept(99,var_dname,var_loc);
  dbms_output.put_line(var_dname||'位于：'||var_loc);
end;
/
--5 in默认值
create or replace procedure insert_dept(
  num_deptno in number,--定义存储部门编号的IN参数
  var_dname in varchar2 default '综合部',--定义存储部门名称的IN参数，并初始默认值
  var_loc in varchar2 default '北京') is
begin
  insert into dept values(num_deptno,var_dname,var_loc);--插入一条记录
end;
/

/*触发器：可以看做一个特殊的存储过程。定义了一些相关数据库相关事件(insert、update、create等事件)发生时执行相应的“功能代码”。用于管理负责的完整约束，或
监控对表的修改，或者通知奇特程序，甚至可以实现对数据库的审计功能。*/
/*触发器概念：触发事件。*/
/*语句触发器：针对一条dml语句而引起的触发器执行，触发器只会执行一次*/
--1。创建一张日志表
create table dept_log
(
  operate_tag varchar2(10), --定义字段，存储操作种类信息
  operate_time date --定义字段，存储操作日期
);
--2为dept表创建触发器做日志记录。
create or replace trigger tri_dept
  before insert or update or delete
  on dept --创建触发器，当dept表发生插入，修改，删除操作时引起该触发器执行
declare
  var_tag varchar2(10);--声明一个变量，存储对dept表执行的操作类型
begin
  if inserting then --当触发事件是INSERT时
    var_tag := '插入';--标识插入操作
  elsif updating then --当触发事件是UPDATE时
    var_tag := '修改';--标识修改操作
  elsif deleting then--当触发事件是DELETE时
    var_tag := '删除';--标识删除操作
  end if;
  insert into dept_log
  values(var_tag,sysdate);--向日志表中插入对dept表的操作信息
end tri_dept;
/
/*行级触发器：行级触发器回针对dml操作语句所影响的每一行数据都执行一次触发器。创建该触发器，必须在语法中使用for each row这个选项*/
 create table goods
(
  id int primary key,
  good_name varchar2(50)
);
create sequence seq_id;


create or replace trigger tri_insert_good
  before insert
  on goods --关于goods数据表的id，在插入id列之前，引起该触发器的运行
  for each row --创建行级触发器
begin
  select seq_id.nextval
  into :new.id
  from dual;--从序列中生成一个新的数值，赋值给当前插入行的id列
end;
/
/*替换触发器：*/
/*用户事件触发器*/

/*函数*/
/*函数定义：用于计算，返回一个值
*/
create or replace function get_avg_pay(num_deptno number) return number is--创建一个函数，该函数实现计算某个部门的平均工资，传入部门编号参数
  num_avg_pay number;--定义临时变量，保存某个部门的平均工资
begin
  select avg(sal) into num_avg_pay from emp where deptno=num_deptno;--获取某个部门的平均工资
  return(round(num_avg_pay,2));--返回平均工资
exception
  when no_data_found then --若此部门编号不存在
    dbms_output.put_line('该部门编号不存在');
    return(0); --返回平均工资为0
end;
/

普通sql语句优化
1.建议不用“*”来代替所有列
2.用truncate代替delete
3.在确保完整性的情况下多用commit语句。
4.尽量减少表的查询次数
5.用exists 代替 in
表连接优化
1.驱动表的选择
2.where子句的连接顺序：在连接之前 通过where条件过滤掉没必要的数据越多越好 。普通sql语句优化
1.建议不用“*”来代替所有列
2.用truncate代替delete
3.在确保完整性的情况下多用commit语句。
4.尽量减少表的查询次数
5.用exists 代替 in
表连接优化
1.驱动表的选择
2.where子句的连接顺序：在连接之前 通过where条件过滤掉没必要的数据越多越好 。