数据库的存储过程、函数、触发器

SQL语句在执行的时候是先编译再执行，在大型数据库中为了提高效率，将为了完成特定功能的SQL语句集进行编译优化后，

存储在数据库服务器中，用户通过指定存储过程的名字来调用执行。

编译：将某一种程序设计语言写的程序翻译成等价的另一种语言的程序的程序, 称之为编译程序(compiler) .

 

存储过程是干什么的？？？ 有什么优势 oracle怎么写存储过程 oracle的存储过程是不是能写在数据库里啊，还是写在文件中读取啊

 

数据过程就是预编译的SQL与逻辑控制总和，能加快数据访问速度，一次执行，多次调用，性能上优于每次编译SQL语句，尤其处理复杂查询业务有较强性能的提升，oracle写存储过程如：create or replace procedure (参数） as/is 参数 begin null; end;oracle的存储过程是写在数据库中，在程序代码中进行调用,java中用连接prepareCall(存储过程名)进行调用

 

一、存储过程与函数的区别：

　　1.一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。

　　2.对于存储过程来说可以返回参数(output)，而函数只能返回值或者表对象。

　　3.存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，

        由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。

      4.函数可以嵌入在sql中使用，可以在select中调用，存储过程则不行。

      5. 执行速度更快比函数更快 – 在数据库中保存的存储过程语句都是编译过的

二、存储过程的优点：

　　1.执行速度更快 – 在数据库中保存的存储过程语句都是编译过的

　　2.允许模块化程序设计 – 类似方法的复用

　　3.提高系统安全性 – 防止SQL注入

　　4.减少网络流通量 – 只要传输存储过程的名称

 

 

 

三、定义存储过程语法，"["   里面的内容表示可选项

　　create proc 存储过程名

　　@参数1        数据类型 [=默认值] [output],

　　@参数2        数据类型 [=默认值] [output],

　　...

　　as

　　SQL语句

 

四、简单的一个例子

　　定义存储过程：

　　create proc usp_StudentByGenderAge

　　@gender nvarchar(10) [='男'],

　　@age int [=30]

　　as

　　select * from MyStudent where FGender=@gender and FAge=@age

 

　　执行存储过程：

　　Situation One(调用默认的参数):

　　exec usp_StudentByGenderAge

　　Situation Two(调用自己指定的参数):

　　exec usp_StudentByGenderAge '女',50

　　或者指定变量名        exec usp_StudentByGenderAge @age=50,@gender='女'

 

　

存储过程示例：为指定的职工在原工资的基础上长10%的工资

 

CREATE OR REPLACE PROCEDURE cuiyaonan2000(存储过程名)  

(  

    参数1　 IN　 NUMBER,  

    参数2 　IN 　NUMBER,  

    参数3  OUT  Number  //如上是in表示你传递给存储过程的参数,out表示存储过程返回给你的参数.可以有多个

/*

为指定的职工在原工资的基础上长10%的工资，并打印工资前和工资后的工资

*/

SQL> create or replace procedure raiseSalary(empid in number)                 //给存储过程传入参数

    as                                                                       //这里用IS 还是用AS 随个人习惯,没有什么区别 

    pSal emp.sal%type;--保存员工当前 工资

    begin

--查询该员工的工资

    select sal into pSal from emp where empno=empid;

--给该员工涨工资

    update emp set sal = sal*1.1 where empno=empid;

--打印涨工资前后的工资

    dbms_output.put_line('员工号:' || empid || '涨工资前

   ' || psal || '涨工资后' || psal*1.1);

    end;

 

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

Java代码  

create or replace procedure cuiyaonan2000(a in number,b out number)IS  

    first1 char;  

    second2 number(1);  

    third3 number := 123;  

    begin  

        NULL;   

    EXCEPTION  

        WHEN NO_DATA_FOUND THEN  

            dbms_output.put_line('数据未找到错误');  

        when others then   

            dbms_output.put_line('未知错误');  

    END;  

end cuiyaonan2000;  

//如上 := 是给变量赋值的 传统意义上的 等号   

//如上exception when 就是 拦截异常 跟Try catch 一样  

//No_data_found 是异常名 others指所有异常  

// dbms_output.put_line 是数据库方法 如同system.out.println  

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

二．    流程控制语句  

     IF流程语句:

 

Java代码  

   create or replace procedure cuiyaonan2000(a in number,b out number)IS  

    first1 char;  

    second2 number(1);  

    third3 number := 123;  

    begin  

        if a > 0 then  

                begin  

                    dbms_output.put_line(a);  

                b:= 123;  

                    dbms_output.put_line(a);  

            end;  

            end if;  

            if third3 > 0 then  

                begin  

                    dbms_output.put_line(third3);  

                    third3 := third3 +1;  

                    dbms_output.put_line(third3);  

                end;  

            end if;  

Exception  

                WHEN NO_DATA_FOUND THEN  

                dbms_output.put_line('数据未找到错误');  

when others then   

                dbms_output.put_line('未知错误');  

    end cuiyaonan2000;  

    //如上的传入参数a 不能再赋值,否则报错  

    //b 用来接收 cuiyaonan2000存储过程返回的结果.少参数调用报错  

 

         FOR流程语句

Java代码  

create or replace procedure cuiyaonan2000(a in number,b out number)IS  

            Cursor cursor is select name from test_table_user;  

        begin  

        FOR temp IN cursor LOOP  

                dbms_output.put_line(temp.name);  

        END LOOP;  

    exception  

            WHEN NO_DATA_FOUND THEN  

                    dbms_output.put_line('数据未找到错误');  

            when others then   

                    dbms_output.put_line('未知错误');  

        end cuiyaonan2000;  

    //如上 cursor 是游标 跟jdbc的游标差不多 指向一条记录.然后用循环来遍历所有查到的记录.

    这里for temp in 后必须是记录集合   

 

   WHILE流程语句

Java代码  

create or replace procedure cuiyaonan2000(a in number,b out number)IS  

                c number :=1;  

            begin       

                while c < 10 LOOP   

                    begin      

                            c:= c + 1;  

                            dbms_output.put_line(c);  

  

                    end;   

                end LOOP;  

            exception  

                WHEN NO_DATA_FOUND THEN  

                        dbms_output.put_line('数据未找到错误');  

                    WHEN others then   

                        dbms_output.put_line('未知错误');  

        end cuiyaonan2000; 

 

--存储过程调用

--方法一

SQL> set serveroutput on

SQL> exec raisesalary(7369);

 

员工号:7369涨工资前

800涨工资后880

 

方法二

    set serveroutput on

begin

 raisesalary(7369);

end;

/

 

 

 

       存储函数

      函数（Function）为一命名的存储程序，可带参数，并返回一计算值。函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。

 

     建立存储函数的语法：

 

CREATE [OR REPLACE] FUNCTION函数名(参数列表)

 RETURN 函数值类型

AS

PLSQL子程序体；

 

 

      示例：查询某职工的年收入。

    /*

    查询某职工的总收入

    */

    create or replace function queryEmpSalary(empid in number)

    return number

   as

    pSal number; --定义变量保存员工的工资

    pComm number; --定义变量保存员工的奖金

   begin

   select sal,comm into psal,pcomm from emp where empno = empid;

   return psal*12+nvl(pcomm,0);

   end;

 

       函数的调用

 declare

    v_sal number;

    begin

    v_sal:=queryEmpSalary(7934);

    dbms_output.put_line('salary is:'|| v_sal);

    end;

 

salary is:15600

 

 

 

SQL> begin

    dbms_output.put_line('salary is:'|| queryEmpSalary(7934));

    end;

    /

 

salary is:15600

 

 

 

       触发器

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

 

       触发器的类型

         语句级触发器

        在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。

 

         行级触发器（FOR EACH ROW）

        触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量,识别值的状态。

 

      创建触发器

CREATE  [or REPLACE] TRIGGER 触发器名

   {BEFORE | AFTER}

   {DELETE | INSERT | UPDATE [OF列名]}

   ON  表名

   [FOR EACH ROW [WHEN(条件) ] ]

   PLSQL 块

 

       示例1：限制非工作时间向数据库插入数据

SQL> create or replace

    trigger securityEmp

    before insert on emp

    declare

    begin

    if to_char(sysdate,'day')in('星期四','星期六','星期日')

    or to_number(to_char(sysdate,'hh24'))not between 8 and 18 then

    raise_application_error(-20001,'不能在非工作时间插入数据。');

    end if;

   end;

 

 

      触发语句与伪记录变量的值

触发语句	:old	:new
Insert	所有字段都是空(null)	将要插入的数据
Update	更新以前该行的值	更新后的值
delete	删除以前该行的值	所有字段都是空(null)

 

      示例2：确认数据（检查emp表中sal的修改值不低于原值）

SQL> create or replace trigger checkSal

    before update of sal on emp

    for each row

    declare

    begin

    if :new.sal<:old.sal then

    raise_application_error(-20001,'更新后的薪水比更新前小');

    end if;

    end;

   /

 

Trigger created

运行后结果：

SQL> update emp set sal=260 where empno=7499;

 

update emp set sal=260 where empno=7499

 

ORA-20001: 更新后的薪水比更新前小

ORA-06512: 在 "SCOTT.CHECKSAL", line 4

ORA-04088: 触发器 'SCOTT.CHECKSAL'执行过程中出错

 

       触发器总结

      触发器可用于

•         数据确认 

•         实施复杂的安全性检查

•         做审计，跟踪表上所做的数据操作等