利用临时表、REF游标、动态SQL实现分页查询

最新推荐文章于 2021-01-19 14:40:11 发布

原创最新推荐文章于 2021-01-19 14:40:11 发布 · 1.4k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #null #manager #table #insert #delete

Develop 专栏收录该内容

29 篇文章

订阅专栏

本文介绍了一个Oracle数据库中使用SQL创建存储过程的简单示例，包括源表和临时表的结构定义、REF游标类型及存储过程实现，展示了如何通过动态SQL进行数据插入和查询。

1、 用到的源表表结构

-- Create table create table CUSTOMER ( CUST_ID VARCHAR2(32) not null, CREATE_TIME TIMESTAMP(6) not null, CUST_NAME VARCHAR2(512), POINT_CMIS_ID VARCHAR2(20), CUST_ORG_CODE VARCHAR2(20), REG_ADDRESS VARCHAR2(256), BELONG_INDUSTRY VARCHAR2(64), INDUSTRY_CODE VARCHAR2(8), IS_LOW_RISK VARCHAR2(2), IS_SUPPLY_CHAIN VARCHAR2(2), IS_PROJECT_FINANCING VARCHAR2(2), ENP_PROP VARCHAR2(2), IS_TRADE_FINANCING VARCHAR2(2), IS_PURE_ASSURER VARCHAR2(2), EMP_NUM NUMBER, SALES_INCOMING NUMBER(16,2), ASSET_AMOUNT NUMBER(16,2), CUST_STATUS VARCHAR2(2) not null, CUST_TYPE NUMBER, ZJ_TYPE NUMBER, ZJ_NO VARCHAR2(128), JBJG_NO VARCHAR2(128), MAIN_CUST_MANAGER VARCHAR2(32), AREA VARCHAR2(32), PROP_ATTRIBUTE VARCHAR2(2), IS_GOV_FINANCING_PLATFORM VARCHAR2(2) ) ;

2、 临时表表结构

-- Create table create global temporary table T_CUSTOMER ( CUST_ID VARCHAR2(32) not null, CREATE_TIME TIMESTAMP(6) not null, CUST_NAME VARCHAR2(512), POINT_CMIS_ID VARCHAR2(20), CUST_ORG_CODE VARCHAR2(20), REG_ADDRESS VARCHAR2(256), BELONG_INDUSTRY VARCHAR2(64), INDUSTRY_CODE VARCHAR2(8), IS_LOW_RISK VARCHAR2(2), IS_SUPPLY_CHAIN VARCHAR2(2), IS_PROJECT_FINANCING VARCHAR2(2), ENP_PROP VARCHAR2(2), IS_TRADE_FINANCING VARCHAR2(2), IS_PURE_ASSURER VARCHAR2(2), EMP_NUM NUMBER, SALES_INCOMING NUMBER(16,2), ASSET_AMOUNT NUMBER(16,2), CUST_STATUS VARCHAR2(2) not null, CUST_TYPE NUMBER, ZJ_TYPE NUMBER, ZJ_NO VARCHAR2(128), JBJG_NO VARCHAR2(128), MAIN_CUST_MANAGER VARCHAR2(32), AREA VARCHAR2(32), PROP_ATTRIBUTE VARCHAR2(2), IS_GOV_FINANCING_PLATFORM VARCHAR2(2) ) on commit delete rows;

3、 REF游标

CREATE OR REPLACE PACKAGE IRS_REF IS TYPE t_cursor IS REF CURSOR; end IRS_REF; /

4、 存储过程示例

CREATE OR REPLACE PROCEDURE SP_PAGE ( P_CUST_ID VARCHAR2, P_CUR OUT IRS_REF.T_CURSOR ) AS V_CUST_ID VARCHAR2(100) := NVL(P_CUST_ID, '!'); V_SQLTEXT VARCHAR2(4000); BEGIN V_SQLTEXT := 'INSERT INTO T_CUSTOMER SELECT * FROM CUSTOMER '; IF V_CUST_ID = '!' THEN V_SQLTEXT := V_SQLTEXT; ELSE V_SQLTEXT := V_SQLTEXT || 'WHERE CUST_ID=''' || V_CUST_ID || ''''; END IF; EXECUTE IMMEDIATE V_SQLTEXT; OPEN P_CUR FOR SELECT * FROM T_CUSTOMER; END; /

5、 总结：

1. 变量如果未输入，则将其初始值设为：字符型设为’!’，数字型设为0，便于下面拼动态SQL；

2. 本例只是一个最简单的示例，根据需要，以此为例扩展；

3. 对以前的东西多看看，多想想，会有很多收获，对目前的工作也很有帮助。