金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 如何理解postgresql的存储过程,使用存储过程

如何理解postgresql的存储过程,使用存储过程

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-05 18:52
ALTER PROCEDURE [dbo].[POR_CURSOR_FOR_UPDATE]    --创建存储过程
AS
BEGIN
  SET nocount ON                   --忽略行数显示
  DECLARE UpdateSHEBEI CURSOR              --声明游标
  FOR SELECT X_D_TJSL,X_G_JSL FROM FACT_MX  --要查询的结果集

  OPEN UpdateSHEBEI          --打开游标 
  DECLARE @X_D_TJ VARCHAR(128),@X_G_J VARCHAR(128);  --声明变量

 FETCH NEXT FROM UpdateSHEBEI INTO @X_D_TJ,@X_G_J     
 WHILE @@FETCH_STATUS = 0       --开始循环
 BEGIN 
   SELECT @X_D_TJ=CEILING(rand()*50);
    SELECT @X_G_J=CEILING(rand()*6);    --给变量赋值
    UPDATE FACT_SHEBEI_MX SET X_D_TJSL=@X_D_TJ,X_G_JSL=@X_G_J WHERE CURRENT OF UpdateSHEBEI  --更新表数据
   FETCH NEXT FROM UpdateSHEBEI INTO @X_D_TJ,@X_G_J     --下一行数据
  END
  CLOSE UpdateSHEBEI;   --关闭游标
 DEALLOCATE UpdateSHEBEI    
  SET nocount OFF      --打开计数

END

在b/s系统的构建过程中,数据库操作几乎成为了一个必不可少的操作。调用存储过程实现数据库操作使很多程序员使用的方法,而且大多数的程序员都是能使用存储过程就使用存储过程,很少直接使用sql语句,所以存储过程是很有用而且很重要的。

背景:

学过之后却没有总结,今天好不容易有点时间来看看。

   上学时学的东西,都忘了,用到了,就翻出来学习了一下。使用存储过程编写,可直接运行该存储过程注释都写好了,变量赋值也比较清楚,需要的可以直接复制下来然后替换就好。

存储过程简介

存储过程的定义:完成一定功能的可重复调用的程序

简单的说,存储过程是由一些sql语句和控制语句组成的被封装起来的过程,它驻留在数据库中,可以被客户应用程序调用,也可以从另一个过程或触发器调用。它的参数可以被传递和返回。与应用程序中的函数过程类似,存储过程可以通过名字来调用,而且它们同样有输入参数和输出参数。

根据返回值类型的不同,我们可以将存储过程分为三类:返回记录集的存储过程,返回数值的存储过程(也可以称为标量存储过程),以及行为存储过程。顾名思义,返回记录集的存储过程的执行结果是一个记录集,典型的例子是从数据库中检索出符合某一个或几个条件的记录;返回数值的存储过程执行完以后返回一个值,例如在数据库中执行一个有返回值的函数或命令;最后,行为存储过程仅仅是用来实现数据库的某个功能,而没有返回值,例如在数据库中的更新和删除操作。

存储过程的通俗解释:你使用手机拨打A同事的手机,需要一个一个号码的输入,然后才能拨打
而如果你把这个号码设置为快速拨号,那么你只要长按1(自己设置的数字键)就可以直接拨打电话了
把这个号码设置为快速拨号的过程你就可以理解为创建存储过程。

最近公司在做食堂POS机研发,有个功能是上传离线交易。每次向服务器提交1000~2000条数据,我们现在是循环将每一条save进去,现想优化下用存储过程来实现,减少jdbc连接。

存储过程的优势

1.简化复杂的SQL语句,将多个SQL语句封装成为一个存储过程,可以在其中加上一些流程控制语句

2.存储过程封装在数据库内部,编译之后直接调用,大大提高效率

3.模块化编程,将一定功能的SQL语句封装,开发人员无需了解内部构造直接调用


使用存储过程的好处

相对于直接使用sql语句,在应用程序中直接调用存储过程有以下好处:

(1)减少网络通信量。调用一个行数不多的存储过程与直接调用sql语句的网络通信量可能不会有很大的差别,可是如果存储过程包含上百行sql语句,那么其性能绝对比一条一条的调用sql语句要高得多。

(2)执行速度更快。有两个原因:首先,在存储过程创建的时候,数据库已经对其进行了一次解析和优化。其次,存储过程一旦执行,在内存中就会保留一份这个存储过程,这样下次再执行同样的存储过程时,可以从内存中直接调用。

(3)更强的适应性:由于存储过程对数据库的访问是通过存储过程来进行的,因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动,而这些改动不会对应用程序造成影响。

(4) 布式工作:应用程序和数据库的编码工作可以分别独立进行,而不会相互压制。

由以上的分析可以看到,在应用程序中使用存储过程是很有必要的。

1.准备

创建存储过程(Stored Procedure)

delimiter //
    create proceduce sp_test(
    in id int,out num int )
    begin
    select sum(id) into num;
    end
    //
delimiter;

存储过程的定义规范

一,存储过程的格式

Create or replace function 过程名(参数名 参数类型,…..) returns 返回值类型 as
                   $body$

                            //声明变量
                            Declare
                            变量名变量类型;
                            如:
                            flag Boolean;

                            变量赋值方式(变量名类型 :=值;)
                            如:
                            str  text :=值; / str  text;  str :=值;

                            Begin
                                     函数体;

                             return 变量名; //存储过程中的返回语句

                            End;
                   $body$
         Language plpgsql;

二,变量类型
有整数类型(Smallint,Int等),浮点类型(float),时间类型(date,time,timestamp,interval),还有其他特殊类型比如inet,point等。
三,连接字符
Postgresql存储过程中的连接字符使用的是"||"
四,控制结构
使用LOOP,EXIT,CONTINUE,WHILE, 和FOR 语句,可以控制PL/pgSQL 函数重复一系列命令。需要使用的时候请查详细资料。
五,异常捕获

EXCEPTION
WHEN 错误码(如:STRING_DATA_RIGHT_TRUNCATION:字串数据右边被截断) THEN
        /**后台打印错误信息*/
        RAISE NOTICE '错吴信息';

或者错误码为UNDEFINED_TABLE时可以执行创建表的操作然后在入数据。

说明:我们数据库是:orcale 11g,java 是7

代码分析:

1.首先SQL语句的结束标志改为//,便于区分SQL语句的结束和存储过程的结束,不会出现混乱,用完之后记得还原。

2.关于参数有三种类型,in,out,inout

3.select...into... 可以将数据通过num返回

4.在内容中可以利用流程控制大大提升代码功能,之后会补充


存储过程的示例

例子1

/**
批量插入一批数据,经纬度字段值要满足中国地理位置上的经纬度范围;
注:时间不能指定为同一时间,否则会扫描全表,导致性能低下。下列脚本未考虑时间的分段,采用的一个时间点。
*/
create or replace function intobatch() returns integer as
$body$
declare
    skyid integer;
    lot float;
         lat float;
         sex varchar;
         level integer;
         ctime int :=1325404914;
         num integer :=0;
         total integer :=0;
    begin
                   lot='73.6666666';
                   lat='3.8666666';
                   FOR skyid IN 404499817 ..404953416 loop
                             if(lot > 135.0416666) then
             lot=73.6666666;
                             end if;
                        if(lat > 53.5500000) then
             lat=3.8666666;
                             end if;
                             if(skyid%2 <> 0) then
                                                        sex='1';
                                                        level=0;
                             else
                                                        sex='2';
                                                        level=1;
                             end if;

                            INSERT INTO user_last_location(user_id,app_id,lonlat,sex,accurate_level,lonlat_point,create_time)

VALUES(skyid,2934,ST_GeomFromText('POINT('||lot||' '||lat||')',4326),sex,level,POINT(lot,lat),to_timestamp(ctime));

                            lot=lot+0.1;
                            lat=lat+0.1;
                            skyid=skyid+1;

        end loop;        
                   return skyid;  
    end
$body$
languageplpgsql;

SELECT *from intobatch();

例子2:此例子是审计的生产环境的例子

-- 存储过程
CREATE OR REPLACE FUNCTION audit_event_partition_trigger()
RETURNS TRIGGER AS $$
DECLARE date_text TEXT;
DECLARE end_date_text TEXT;
DECLARE insert_statement TEXT;
BEGIN
    SELECT to_char(NEW.time_event, 'YYYY_MM_DD') INTO date_text;
    SELECT to_char(NEW.time_event + INTERVAL '1 DAY', 'YYYY_MM_DD') INTO end_date_text;
    insert_statement := 'INSERT INTO audit_event_' || date_text || ' VALUES($1.*)';
    EXECUTE insert_statement USING NEW;
    RETURN NULL;
    EXCEPTION
    WHEN UNDEFINED_TABLE
    THEN
        EXECUTE 
            'CREATE TABLE IF NOT EXISTS audit_event_'  || date_text || '(CHECK ( time_event >= ''' || date_text || ''' AND  time_event < '''|| end_date_text ||''' )) INHERITS (audit_event)';
        RAISE NOTICE 'CREATE NON-EXISTANT TABLE audit_event_%', date_text;
        EXECUTE
            'CREATE INDEX audit_event_' ||  date_text || '_event_msg_body ON audit_event_'  || date_text || ' USING gin(event_msg_body gin_trgm_ops); ' 
            || 'CREATE INDEX audit_event_' || date_text || '_gid ON audit_event_'  || date_text || ' USING btree(gid);' 
            || 'CREATE INDEX audit_event_' || date_text || '_id ON audit_event_'  || date_text || ' USING btree(id);' 
            || 'CREATE INDEX audit_event_' || date_text || '_module ON audit_event_'  || date_text || ' USING btree(module);' 
            || 'CREATE INDEX audit_event_' || date_text || '_access_time ON audit_event_'  || date_text || ' USING btree(access_time);' 
            || 'CREATE INDEX audit_event_' || date_text || '_mid ON audit_event_'  || date_text || ' USING btree(mid);' 
            || 'CREATE INDEX audit_event_'  || date_text || '_time_event ON audit_event_' || date_text || ' USING btree(time_event);';
        EXECUTE insert_statement USING NEW;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql

这个是由触发器来引发执行的存储过程:

DROP TRIGGER IF EXISTS auto_insert_into_audit_event_tbl_partiton ON audit_event;
CREATE TRIGGER  auto_insert_into_audit_event_tbl_partiton BEFORE INSERT OR UPDATE ON  audit_event  
FOR EACH ROW
EXECUTE PROCEDURE audit_event_partition_trigger()

reference
对存储过程的一些理解
简单通俗的解释一下存储过程是干什么的
分表分库设计

1.1 创建一张表

查看存储过程

show proceduce status G;

show create produce sp_testG;
详细查看,可以看到具体过程中的代码内容

create table A
(
  aa VARCHAR2(30),
  b  VARCHAR2(30),
  c  VARCHAR2(40)
)

删除存储过程

drop procedure sp_test;

1.2 java创建一个类带main方法的类

执行操作过程

call sp_test(1,@num);
将返回值存储在num变量中,在out型变量前加上@
直接可以使用select @num;来查看具体的值

---恢复内容结束---

学过之后却没有总结,今天好不容易有点时间来看看。

/** 
 *
 *  @类功能说明  TODO
 *  @类修改者  
 *  @修改日期  
 *  @修改说明  
 *  @公司名称 Dlk
 *  @作者 樊强 
 *  @创建时间 2015年10月8日 上午11:28:34 
 *  @版本 V1.0 
 *
 */
public class JDBCProTest {
   public static void main(String[] args) {


   }
}

存储过程的优势

1.简化复杂的SQL语句,将多个SQL语句封装成为一个存储过程,可以在其中加上一些流程控制语句
2.存储过程封装在数据库内部,编译之后直接调用,大大提高效率
3.模块化编程,将一定功能的SQL语句封装,提高可读性


1.3 java 基本数据

创建存储过程(Stored Procedure)

delimiter // create proceduce sp_test( in id int,out num int ) begin select sum(id) into num; end // delimiter;

  private static String X_DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static String X_USERNAME = "bpim";
    private static String X_PASSWORD = "123456";
    private static String X_URL = "jdbc:oracle:thin:@192.168.1.25:1521:orcl";

代码分析:

1.首先SQL语句的结束标志改为//,便于区分SQL语句的结束和存储过程的结束,不会出现混乱,用完之后记得还原。
2.关于参数有三种类型,in,out,inout
3.select...into... 可以将数据通过num返回
4.在内容中可以利用流程控制大大提升代码功能,之后会补充


2.直接在java程序里调用存储过程无参数无返回值。

查看存储过程

show proceduce status G;

show create produce sp_testG;
详细查看,可以看到具体过程中的代码内容

2.1 存储过程

删除存储过程

drop procedure sp_test;

CREATE OR REPLACE PROCEDURE A_PROC
IS
BEGIN
 INSERT INTO A (aa)values('1');
  COMMIT;
END A_PROC;

执行操作过程

call sp_test(1,@num);
将返回值存储在num变量中,在out型变量前加上@
直接可以使用select @num;来查看具体的值

2.2 java程序

/**
 * 
 *  @throws Exception
 *  @创建时间 2015年10月8日 上午11:35:03
 *  @作者  樊强
 *  @返回值 void
 *  @描述 测试调用存储过程:无返回值 无参数
 *
 */
    public static void testProcNoInNoOut() throws Exception {
        System.out.println("-------  start 测试调用存储过程:无返回值  无参数");
        Connection conn = null;
        CallableStatement callStmt = null;
        try {
            Class.forName(X_DRIVER);
            conn = DriverManager.getConnection(X_URL, X_USERNAME, X_PASSWORD);
            callStmt = conn.prepareCall("{call A_PROC()}");
            callStmt.execute();
            System.out.println("-------  测试调用存储过程:无返回值   无参数 End.");
        } catch (Exception e) {
            e.printStackTrace(System.out);
        } finally {
            if (null != callStmt) {
                callStmt.close();
            }
            if (null != conn) {
                conn.close();
            }
        }
    }

  public static void main(String[] args) {
        try {
          testProcNoInNoOut();
        }catch (Exception e) {
        e.printStackTrace();
    }
}

          2.3 结果

 图片 1

3.直接在java程序里调用存储过程带参数无返回值。

     3.1 存储过程

CREATE OR REPLACE PROCEDURE AA_PROC(AA IN VARCHAR2) IS
BEGIN
 INSERT INTO A (aa)values(AA);
 COMMIT;
END AA_PROC;

3.2 java程序

 /**
     * 
     *
     *  @param String a
     *  @throws Exception
     *  @创建时间 2015年10月8日 下午2:07:48
     *  @作者  樊强
     *  @返回值 void
     *  @描述 测试调用存储过程:无返回值 有参数
     *
     */
    public static void testProc(String a) throws Exception {
        System.out.println("-------  start 测试调用存储过程:无返回值 有参数");
        Connection conn = null;
        CallableStatement callStmt = null;
        try {
            Class.forName(X_DRIVER);
            conn = DriverManager.getConnection(X_URL, X_USERNAME, X_PASSWORD);
            callStmt = conn.prepareCall("{call AA_PROC(?)}");
            callStmt.setString(1, a);
            callStmt.execute();
            System.out.println("-------  测试调用存储过程:无返回值 有参数 End.");
        } catch (Exception e) {
            e.printStackTrace(System.out);
        } finally {
            if (null != callStmt) {
                callStmt.close();
            }
            if (null != conn) {
                conn.close();
            }
        }
    }

   public static void main(String[] args) {
        try {
                testProc("中文");
             } catch (Exception e) {
        e.printStackTrace();
             }
    }

3.3 结果

图片 2

4.直接测试带循环的存储过程

  4.1 存储过程

CREATE OR REPLACE PROCEDURE AAA_PROC is
begin
  for i in 1 .. 5 loop
    insert into A (AA) values (i);
    commit;
  end loop;
end AAA_PROC;

    4.2 测试存储过程

begin
  aaa_proc;
end;

  4.3 结果

图片 3

5.调用带list参数的存储过程

  带list参数的存储过程其实应该是array参数的存储过程,都是需要用list转化为array传到数据库中的。

 5.1 存储过程

create or replace type a_table is table of number;
create or replace type b_table is table of varchar2(30);
create or replace type c_table is table of varchar2(30);

在存储过程创建之前先声明这个array中的数据类型。(1 这里不是很确定)

CREATE OR REPLACE PROCEDURE AAAAA_PROC(
                       v_1 a_table,
                       v_2 b_table,
                       v_3 c_table)is
    v_count number;
begin
  v_count := v_1.count;
    for i in 1..v_count
       loop
           insert into A(AA, B, C)values(v_1(i),v_2(i), v_3(i));
           commit;
       end loop;
end AAAAA_PROC;

  5.2java程序调用

   /**
     * 
     *
     *  @param a
     *  @throws Exception
     *  @创建时间 2015年10月8日 下午2:07:48
     *  @作者  樊强
     *  @返回值 void
     *  @描述 测试调用存储过程:无返回值 有参数list 多字段
     *
     */
    public static void testProcList(List<Number> a1,List<String> a2,List<String> a3) throws Exception {
        System.out.println("-------  start 测试调用存储过程:无返回值 有参数list 多字段");
        Connection conn = null;
        CallableStatement callStmt = null;
        try {
            Class.forName(X_DRIVER);
            conn = DriverManager.getConnection(X_URL, X_USERNAME, X_PASSWORD);
            callStmt = conn.prepareCall("{call AAAAA_PROC(?,?,?)}");
            ArrayDescriptor tabDesc = ArrayDescriptor.createDescriptor("A_TABLE", conn); 
            ArrayDescriptor tabDesc1 = ArrayDescriptor.createDescriptor("B_TABLE", conn); 
            ArrayDescriptor tabDesc2 = ArrayDescriptor.createDescriptor("C_TABLE", conn); 
            ARRAY vArray1 = new ARRAY(tabDesc, conn, a1.toArray()); 
            ARRAY vArray2 = new ARRAY(tabDesc1, conn, a2.toArray()); 
            ARRAY vArray3 = new ARRAY(tabDesc2, conn, a3.toArray()); 
            callStmt.setArray(1, vArray1);
            callStmt.setArray(2, vArray2);
            callStmt.setArray(3, vArray3);
            callStmt.execute();
            System.out.println("-------  测试调用存储过程:无返回值 有参数list多字段 End.");
        } catch (Exception e) {
            e.printStackTrace(System.out);
        } finally {
            if (null != callStmt) {
                callStmt.close();
            }
            if (null != conn) {
                conn.close();
            }
        }
    }
    public static void main(String[] args) {
        try {
            long aaaa=System.currentTimeMillis();
            List<Number> a=new ArrayList<Number>();
            List<String> b=new ArrayList<String>();
            List<String> c=new ArrayList<String>();
            a.add(1);
            b.add("A"+1);
            c.add("AA"+1);
            testProcList(a,b,c);
            long bbbb=System.currentTimeMillis();
            System.out.println((bbbb-aaaa)/ 1000.00);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

5.3 结果

          0.488秒

图片 4

下面对这个调用过程做了个时间的验证

public static void main(String[] args) {
        try {
            long aaaa = System.currentTimeMillis();
            // testProcNoOut();
            // testProc("中文");
            List<Number> a = new ArrayList<Number>();
            List<String> b = new ArrayList<String>();
            List<String> c = new ArrayList<String>();
            for (int i = 0; i < 1000000; i++) {
                a.add(i);
                b.add("A" + i);
                c.add("AA" + i);
             }
            testProcList(a, b, c);
            long bbbb = System.currentTimeMillis();
            System.out.println((bbbb - aaaa) / 1000.00);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

100条       0.592秒

1000条    0.604秒

10000条     1.306秒

100000条 8.663秒

1000000条      85.3秒

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:如何理解postgresql的存储过程,使用存储过程

关键词:

上一篇:金沙棋牌app手机下载:Tookit工具包之pt

下一篇:没有了