存储过程procedure+触发器triggger+游标cursor

来源：互联网发布：vb简单小游戏代码编辑：程序博客网时间：2024/06/07 02:43

一，创建 t_procedure 表存储过程要用到的表

create table t_procedure(

tid number not null ,

tname varchar2(10) ,

constraint yy primary key(tid)

);

二，创建存储过程

create or replace procedure save_pro
(
userid t_procedure.tid%type,
username t_procedure.tname%type
) //定义了存储函数的变量，里面有两个变量 userid ,username ,也可以写死类型像 userid in number
as
begin // 存储的逻辑体，从begin 开始
insert into t_procedure(tid,tname) values (userid,username); //把定义的变量插入表中
end save_pro;

三，在pl/sql 中执行procedure save_pro

call save_pro(10,'nb');，也可以用execute和exec。

四，在java 中运行

1，利用存储过程往Oracle里插入数值

第一步在java project 里导入 Oracle 驱动（文章后面有oracle5.jar 下载） build path -> add external

第二步创建class ，在Oracle 里调用出save_pro，然后往存储函数里插入值

import java.sql.*;
import java.sql.ResultSet;

public class oracle_procedure {
public oracle_procedure() {
}
public static void main(String[] args ){
String driver = "oracle.jdbc.driver.OracleDriver"; //创建驱动
String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; //Oracle路径
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
CallableStatement cstmt = null;

try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, " odi_manager_test ", " odi_manager_test "); //路径，用户，密码
CallableStatement proc = null;
proc = conn.prepareCall("{ call savepro(?,?) }"); // 调用savepro 设定传2个参数
proc.setString(2, "马文涛"); //2是tname 字符串类型varchar
proc.setInt(1,10); //1是tid number

proc.execute(); //执行
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
catch (Exception ex2) {
ex2.printStackTrace();
}
finally{
try {
if(rs != null){
rs.close();
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}
}
catch (SQLException ex1) {
}
}
}
}

2，利用存储过程，在java里显示出Oracle数据

   (1)1，建一个程序包。如下： //游标，函数必须封装在包里
            create or replace package userpackage as
            type usercursor is ref cursor; //usercursor 是游标类型
            end userpackage;

        (2)建立存储过程，存储过程为：
            create or replace procedure pro_getalluser
            (
                pro_cursor out userpackage.usercursor //out 类型是Oracle往java输出，in类型是java往Oracle输入
            )is //pro_cursor 定义为同包里的游标的类型一样
            begin
                open pro_cursor for select * from t_procedure; //打开游标，执行select
            end pro_getalluser;
            可以看到，它是把游标（可以理解为一个指针），作为一个out 参数来返回值的。

(3)java程序：

   import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import oracle.jdbc.OracleTypes;

public class TestProcedure {
public static void main(String[]args){
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";   //根据实际情况
String user = "odi_manager_test"; //根据实际情况
String password = "odi_manager_test"; //根据实际情况
Connection con = DriverManager.getConnection(url, user, password);
String sql = "{call pro_getalluser(?)}";
CallableStatement cs = con.prepareCall(sql);
cs.registerOutParameter(1, OracleTypes.CURSOR); //注册out，（1位置，传对象类型）
cs.execute(); //执行

ResultSet rs = (ResultSet)cs.getObject(1); //返回结果集
while(rs.next()){
System.out.println(rs.getInt(1)+"----"+rs.getString(2)+"----");
}
rs.close();
cs.close();
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
}

触发器

一，触发器的语法如下

CREATE OR REPLACE TRIGGER trigger_name

<before | after | instead of> <insert | update | delete> ON table_name

//before 是在操作表之前触发，after 是之后触发，instead of 是视图的操作

[FOR EACH ROW] //有这个话的意思是Oracle里的每一条数据都触发

WHEN (condition) // 执行触发器的条件

DECLARE

BEGIN

//逻辑体

END;

例子：

create or replace trigger t_table1
after update on table_2 for each row //在update table_2 后触发

when ( NEW.departid>1) //判断条件
DECLARE //声明变量
v NUMBER;
begin update table_1 u set u.departid=:new.departid //别忘了分号
where u.departid=:old.departid;
dbms_output.put_line('x'); //oracle 输出
end t_table1;

CREATE OR REPLACE TRIGGER salary_raiu
AFTER INSERT OR UPDATE OF amount ON salary //插入或更新salary里的amount才会触发
FOR EACH ROW
when ( NEW.amount >= 1000 AND (old.amount IS NULL OR OLD.amount <= 500))
DECLARE
v_maxsalary NUMBER;
BEGIN
SELECT maxsalary
INTO v_maxsalary //变量赋值
FROM employment
WHERE employee_id = :NEW.employee_id;
IF :NEW.amount > v_maxsalary THEN //使用new 一定要在前面加【：】
dbms_output.put_line('超工资');
END IF;
END;

二，instead of触发器，该触发器主要使用在对视图的更新上，

以下是instead of触发器的语法：

CREATE OR REPLACE TRIGGER trigger_name
INSTEAD OF <insert | update | delete> ON view_name
[FOR EACH ROW]
WHEN (condition)
DECLARE
BEGIN
--触发器代码
END;

直接往视图里是插入不了数据的，要用触发器，往视图里插数据，然后再调用存储过程插入数据

代码如下：

新建员工表employment
CREATE TABLE EMPLOYMENT
(
EMPLOYEE_ID NUMBER, --员工ID
MAXSALARY NUMBER --工资上限
)

CREATE TABLE SALARY
(
EMPLOYEE_ID NUMBER, --员工ID
MONTH VARCHAR2(20), --名字

AMOUNT NUMBER --量
)

创建视图：

CREATE OR REPLACE VIEW EMPLOYEE_SALARY AS
SELECT a.employee_id, a.maxsalary, b.MONTH, b.amount
FROM employment a, salary b
WHERE a.employee_id = b.employee_id;

创建触发器

create or replace trigger employee_salary_rii
   instead of insert on employee_salary //要用instead of 并发
   for each ROW
DECLARE //定义变量
v_cnt NUMBER;
BEGIN
   --检查是否存在该员工信息
SELECT COUNT(*)
     INTO v_cnt //赋值
     FROM employment
   WHERE employee_id = :NEW.employee_id;
IF v_cnt = 0 THEN
     INSERT INTO employment
    (employee_id, maxsalary)
  VALUES
    (:NEW.employee_id, :NEW.maxsalary);
END IF;
   --检查是否存在该员工的工资信息
  SELECT COUNT(*)
   INTO v_cnt
   FROM salary
   WHERE employee_id = :NEW.employee_id
      AND MONTH = :NEW.MONTH;
  IF v_cnt = 0 THEN
   INSERT INTO salary
      (employee_id, MONTH, amount)
   VALUES
      (:NEW.employee_id, :NEW.MONTH, :NEW.amount);
  END IF;
END employee_salary_rii;

语句级触发器的语法：
CREATE OR REPLACE TRIGGER trigger_name
<before | after | instead of ><insert | update | delete > ON table_name
DECLARE
BEGIN
--触发器主体
END;

创建触发器

CREATE OR REPLACE TRIGGER salary_saiu
AFTER INSERT OR UPDATE OF amount ON salary
DECLARE
v_sumsalary NUMBER;
BEGIN
SELECT SUM(amount) INTO v_sumsalary FROM salary;
IF v_sumsalary > 500000 THEN
raise_application_error(-20001, '总工资超过500000');
END IF;
END;

创建临时表

create global temporary table SALARY_TMP
(
   EMPLOYEE_ID NUMBER,
   MONTH       VARCHAR2(6),
   AMOUNT      NUMBER
)

为了把操作记录插入到临时表中，创建行级触发器：

CREATE OR REPLACE TRIGGER salary_raiu
AFTER INSERT OR UPDATE OF amount ON salary
FOR EACH ROW
BEGIN
INSERT INTO salary_tmp(employee_id, month, amount)
VALUES(:NEW.employee_id, :NEW.MONTH, :NEW.amount);
END;

该触发器的作用是把更新后的记录信息插入到临时表中，如果更新了多条记录，则每条记录都会保存在临时表中。

创建语句级触发器：

CREATE OR REPLACE TRIGGER salary_sai
AFTER INSERT OR UPDATE OF amount ON salary
DECLARE
  v_sumsalary NUMBER;
BEGIN
  FOR cur IN (SELECT * FROM salary_tmp) LOOP //创建游标cur
   SELECT SUM(amount) //游标遍历cur.employee_id 然后把salary 里同个employee_id

   INTO v_sumsalary //里的amount加一起来
      FROM salary
   WHERE employee_id = cur.employee_id;
   IF v_sumsalary > 50000 THEN
    raise_application_error(-20002, '员工累计工资超过50000');
   END IF;
      DELETE FROM salary_tmp;
  END LOOP;
END;

该触发器首先用游标从salary_tmp临时表中逐条读取更新或插入的记录，取employee_id，在关联表salary中查找所有相同员工的工资记录，并求和。若某员工工资总和超过50000，则抛出异常。如果检查通过，则清空临时表，避免下次检查相同的记录。

游标例子

declare
               cursor cursor_user
                 is
                 select username,age
                   from t_user;
               a t_user.username%type; //定义变量
               b t_user.age%type; //定义变量
            begin
            open cursor_user;
            loop
                 fetch cursor_user into a,b;
                   if a='宝宝'and b=43 then
                    dbms_output.put_line(a || '   ' || b);
                    end if;
                 exit when cursor_user%notfound;
            end loop;
            close cursor_user;
            end;

用包封装触发器代码
目的：改写例五，封装触发器主体代码
创建代码包：
CREATE OR REPLACE PACKAGE BODY salary_trigger_pck IS

PROCEDURE load_salary_tmp( //定义存储

i_employee_id IN NUMBER,

       i_month     IN VARCHAR2,
       i_amount IN NUMBER) is
BEGIN
     INSERT INTO salary_tmp VALUES (i_employee_id, i_month, i_amount);
END load_salary_tmp;

PROCEDURE check_salary is //定义存储
   v_sumsalary NUMBER;
BEGIN
     FOR cur IN (SELECT * FROM salary_tmp) LOOP
    SELECT SUM(amount)
       INTO v_sumsalary
       FROM salary
     WHERE employee_id = cur.employee_id;
    IF v_sumsalary > 50000 THEN
       raise_application_error(-20002, '员工累计工资超过50000');
    END IF;
    DELETE FROM salary_tmp;
   END LOOP;
END check_salary;
END salary_trigger_pck;

包salary_trigger_pck中有两个存储过程，load_salary_tmp用于在行级触发器中调用，往 salary_tmp临时表中装载更新或插入记录。而check_salary用于在语句级触发器中检查员工累计工资是否超限。

修改行级触发器和语句级触发器：
CREATE OR REPLACE TRIGGER salary_raiu
AFTER INSERT OR UPDATE OF amount ON salary
FOR EACH ROW
BEGIN
salary_trigger_pck.load_salary_tmp(:NEW.employee_id,:NEW.MONTH,:NEW.amount);
END;

CREATE OR REPLACE TRIGGER salary_sai
AFTER INSERT OR UPDATE OF amount ON salary
BEGIN
salary_trigger_pck.check_salary;
END;

这样主要代码就集中到了salary_trigger_pck中，触发器主体中只实现了一个调用功能。

10，触发器命名规范
为了方便对触发器命名和根据触发器名称了解触发器含义，需要定义触发器的命名规范：
Trigger_name = table_name_trg_<R|S><A|B|I><I|U|D>

例如：
Salary_rai salary表的行级after触发器，触发事件是insert
Employee_sbiud employee表的语句级before触发器，触发事件是insert,update和delete

0 0