首页 > 数据库 >oracle packages

oracle packages

时间：2024-12-25 11:30:44浏览次数：5  
标签：SXFX DJID BW oracle Query packages WHERE SELECT

oracle packages| Id | Title | DateAdded | SourceUrl | PostType | Body | BlogId | Description | DateUpdated | IsMarkdown | EntryName | CreatedTime | IsActive | AutoDesc | AccessPermission |

| -------------| -------------| -------------| -------------| -------------| -------------| -------------| -------------| -------------| -------------| -------------| -------------| -------------| -------------| -------------|
| 15789076| oracle packages| 2022-01-11T16:57:00| | BlogPost|

1、Packages


create or replace package SXFX is /*【运营模块】【时效报表】申请共享系统时效分析报表 */
type T_CURSOR is ref cursor;            

procedure proc_SCAN(

DJBH IN VARCHAR2,

JYZTMC IN VARCHAR2,

Re_CURSOR out T_CURSOR

);

end SXFX;

2、Package bodies

create or replace package body SXFX is

/扫描分析/

procedure proc_SCAN(

DJBH IN VARCHAR2,

JYZTMC IN VARCHAR2,

Re_CURSOR out T_CURSOR

)  AS

SQLSTR VARCHAR2(5000);

SQLWHERE VARCHAR2(200);

BEGIN


      <span style="color: #008080;">--</span><span style="color: #008080;">单据编号         </span>
      <span style="color: #0000ff;">IF</span> trim(DJBH) <span style="color: #0000ff;">IS</span> <span style="color: #808080;">NOT</span> <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">THEN</span><span style="color: #000000;">
          SQLWHERE :</span><span style="color: #808080;">=</span> <span style="color: #ff0000;">'</span><span style="color: #ff0000;"> AND DJBH = </span><span style="color: #ff0000;">'''</span>  <span style="color: #808080;">||</span> trim(DJBH) <span style="color: #808080;">||</span> <span style="color: #ff0000;">'''</span> <span style="color: #ff0000;">'</span><span style="color: #000000;">;
      </span><span style="color: #0000ff;">END</span> <span style="color: #0000ff;">IF</span><span style="color: #000000;">;

       </span><span style="color: #008080;">--</span><span style="color: #008080;">单位         </span>
      <span style="color: #0000ff;">IF</span> trim(JYZTMC) <span style="color: #0000ff;">IS</span> <span style="color: #808080;">NOT</span> <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">THEN</span><span style="color: #000000;">
          SQLWHERE :</span><span style="color: #808080;">=</span> <span style="color: #ff0000;">'</span><span style="color: #ff0000;"> AND JYZTMC = </span><span style="color: #ff0000;">'''</span>  <span style="color: #808080;">||</span> trim(JYZTMC) <span style="color: #808080;">||</span> <span style="color: #ff0000;">'''</span> <span style="color: #ff0000;">'</span><span style="color: #000000;">;
      </span><span style="color: #0000ff;">END</span> <span style="color: #0000ff;">IF</span><span style="color: #000000;">;



--BW_Query_SXFX


--alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';


DELETE FROM BW_Query_SXFX WHERE 1 = 1;


INSERT INTO BW_Query_SXFX(DJID, DJTYPE, COrder)

SELECT DISTINCT LSRWZTLOG_DJID, LSRWZTLOG_FLBH, 1

FROM LSRWZTLOG

WHERE 1 = 1

AND LSRWZTLOG_FLBH IN ('WEBROBX', 'APFKD', 'APYFD', 'WEBROJK')

AND LSRWZTLOG_TIME > to_date('2021-7-1', 'yyyy-mm-dd hh:mi:ss')

AND LSRWZTLOG_TIME < to_date('2021-10-1', 'yyyy-mm-dd hh:mi:ss')

--AND  LSRWZTLOG_FLBH = 'WEBROBX' ;

;


UPDATE BW_Query_SXFX SET DJLXID = (SELECT APFKD_DJLXID FROM APFKD B WHERE B.ID = BW_Query_SXFX.DJID),

DJBH = (SELECT APFKD_DJBH FROM APFKD B WHERE B.ID = BW_Query_SXFX.DJID),

Creator = (SELECT APFKD_ZDRMC FROM APFKD B WHERE B.ID = BW_Query_SXFX.DJID),

ZDQJY = (SELECT APFKD_KJND FROM APFKD B WHERE B.ID = BW_Query_SXFX.DJID),

ZDQJM = (SELECT APFKD_KJQJ FROM APFKD B WHERE B.ID = BW_Query_SXFX.DJID),

BZAmt = (SELECT APFKD_JE FROM APFKD B WHERE B.ID = BW_Query_SXFX.DJID)

WHERE EXISTS(SELECT 1 FROM APFKD C WHERE C.ID = BW_Query_SXFX.DJID)

AND DJTYPE = 'APFKD'

;


UPDATE BW_Query_SXFX SET DJLXID = (SELECT APYFD_DJLXID FROM APYFD B WHERE B.ID = BW_Query_SXFX.DJID),

DJBH = (SELECT APYFD_DJBH FROM APYFD B WHERE B.ID = BW_Query_SXFX.DJID),

Creator = (SELECT APYFD_ZDRMC FROM APYFD B WHERE B.ID = BW_Query_SXFX.DJID),

ZDQJY = (SELECT APYFD_KJND FROM APYFD B WHERE B.ID = BW_Query_SXFX.DJID),

ZDQJM = (SELECT APYFD_KJQJ FROM APYFD B WHERE B.ID = BW_Query_SXFX.DJID),

BZAmt = (SELECT APYFD_JE FROM APYFD B WHERE B.ID = BW_Query_SXFX.DJID)

--JYZTMC = (SELECT ROBXDJ_NM8 FROM APYFD B WHERE B.ID = BW_Query_SXFX.DJID),

--BMMC = (SELECT ROBXDJ_GXM2 FROM APYFD B WHERE B.ID = BW_Query_SXFX.DJID)

WHERE EXISTS(SELECT 1 FROM APYFD C WHERE C.ID = BW_Query_SXFX.DJID)

AND DJTYPE = 'APYFD'

;


UPDATE BW_Query_SXFX SET DJLXID = (SELECT ROBXDJ_BXLX FROM ROBXDJ B WHERE B.ROBXDJ_NM = BW_Query_SXFX.DJID),

DJBH = (SELECT ROBXDJ_BH FROM ROBXDJ B WHERE B.ROBXDJ_NM = BW_Query_SXFX.DJID),

Creator = (SELECT ROBXDJ_USER FROM ROBXDJ B WHERE B.ROBXDJ_NM = BW_Query_SXFX.DJID),

JYZTMC = (SELECT ROBXDJ_NM8 FROM ROBXDJ B WHERE B.ROBXDJ_NM = BW_Query_SXFX.DJID),

BMMC = (SELECT ROBXDJ_GXM2 FROM ROBXDJ B WHERE B.ROBXDJ_NM = BW_Query_SXFX.DJID),

ZDQJY = (SELECT ROBXDJ_KJND FROM ROBXDJ B WHERE B.ROBXDJ_NM = BW_Query_SXFX.DJID),

ZDQJM = (SELECT ROBXDJ_KJQJ FROM ROBXDJ B WHERE B.ROBXDJ_NM = BW_Query_SXFX.DJID),

BZAmt = (SELECT ROBXDJ_BXJE FROM ROBXDJ B WHERE B.ROBXDJ_NM = BW_Query_SXFX.DJID)

WHERE EXISTS(SELECT 1 FROM ROBXDJ C WHERE C.ROBXDJ_NM = BW_Query_SXFX.DJID)

AND DJTYPE = 'WEBROBX'

;


UPDATE BW_Query_SXFX SET DJLXID = (SELECT ROYWSQ_SQLX FROM ROYWSQ B WHERE B.ROYWSQ_NM = BW_Query_SXFX.DJID),

DJBH = (SELECT ROYWSQ_BH FROM ROYWSQ B WHERE B.ROYWSQ_NM = BW_Query_SXFX.DJID),

Creator = (SELECT ROYWSQ_USER FROM ROYWSQ B WHERE B.ROYWSQ_NM = BW_Query_SXFX.DJID),

JYZTMC = (SELECT ROYWSQ_GXM4 FROM ROYWSQ B WHERE B.ROYWSQ_NM = BW_Query_SXFX.DJID),

BMMC = (SELECT ROYWSQ_GXM3 FROM ROYWSQ B WHERE B.ROYWSQ_NM = BW_Query_SXFX.DJID),

ZDQJY = (SELECT ROYWSQ_KJND FROM ROYWSQ B WHERE B.ROYWSQ_NM = BW_Query_SXFX.DJID),

ZDQJM = (SELECT ROYWSQ_KJQJ FROM ROYWSQ B WHERE B.ROYWSQ_NM = BW_Query_SXFX.DJID),

BZAmt = (SELECT ROYWSQ_JKJE FROM ROYWSQ B WHERE B.ROYWSQ_NM = BW_Query_SXFX.DJID)

WHERE EXISTS(SELECT 1 FROM ROYWSQ C WHERE C.ROYWSQ_NM = BW_Query_SXFX.DJID)

AND DJTYPE = 'WEBROJK'

;


UPDATE BW_Query_SXFX SET DJLXMC = (SELECT APFKDJLX_DJMC FROM APFKDJLX B WHERE B.ID = BW_Query_SXFX.DJLXID)

WHERE EXISTS(SELECT 1 FROM APFKDJLX C WHERE C.ID = BW_Query_SXFX.DJLXID)

AND DJTYPE = 'APFKD'

;


UPDATE BW_Query_SXFX SET DJLXMC = (SELECT APYFDJLX_DJMC FROM APYFDJLX B WHERE B.ID = BW_Query_SXFX.DJLXID)

WHERE EXISTS(SELECT 1 FROM APYFDJLX C WHERE C.ID = BW_Query_SXFX.DJLXID)

AND DJTYPE = 'APYFD'

;


UPDATE BW_Query_SXFX SET DJLXMC = (SELECT ROBXLX_MC FROM ROBXLX B WHERE B.ROBXLX_NM = BW_Query_SXFX.DJLXID)

WHERE EXISTS(SELECT 1 FROM ROBXLX C WHERE C.ROBXLX_NM = BW_Query_SXFX.DJLXID)

AND DJTYPE = 'WEBROBX';


UPDATE BW_Query_SXFX SET DJLXMC = (SELECT ROSQLX_MC FROM ROSQLX B WHERE B.ROSQLX_NM = BW_Query_SXFX.DJLXID)

WHERE EXISTS(SELECT 1 FROM ROSQLX C WHERE C.ROSQLX_NM = BW_Query_SXFX.DJLXID)

AND DJTYPE = 'WEBROJK'

;


DELETE FROM BW_Query_SXFX WHERE DJLXMC IS NULL;


DELETE FROM BW_Query_SXFX WHERE DJLXMC IN ('发票核减表单', '发票红冲作废申请单','集采额度',

'甲供材付款申请单', '甲供材付款申请调整单', '甲供材结算单批', '甲供材结算单批调整单',

'开票申请单', '经营单位额度', '进度款申请单', '进项红字信息申请单', '票据冲销单',

'收款认领单', '收款认领单（冲借款/预付款）', '项目产值进度单(非上线项目)',

'项目产值进度单(上线)'

);


UPDATE BW_Query_SXFX SET DJZT = (SELECT FSYXDJ_DJZT FROM FSYXDJ B WHERE B.FSYXDJ_DJNM = BW_Query_SXFX.DJID)

WHERE 1 = 1;


UPDATE BW_Query_SXFX SET DJZTMC = (SELECT FSSCYWZT_ZTSM FROM fsscywzt B WHERE B.FSSCYWZT_ZT = BW_Query_SXFX.DJZT AND B.FSSCYWZT_YWLX = 'YW')

WHERE 1 = 1;


UPDATE BW_Query_SXFX SET TJRQ = (SELECT MIN(LSRWZTLOG_TIME) FROM LSRWZTLOG B WHERE B.LSRWZTLOG_DJID = BW_Query_SXFX.DJID AND B.LSRWZTLOG_GNID = 'ZD' AND LSRWZTLOG_CZ = '10')

WHERE 1 = 1;


/*

DELETE FROM BW_Query_SXFX1 WHERE 1 = 1;


INSERT INTO BW_Query_SXFX1(DJID, GNID, CZID, CNum)

SELECT LSRWZTLOG_DJID, LSRWZTLOG_GNID, '999', COUNT(0) FROM LSRWZTLOG

WHERE LSRWZTLOG_GNID = 'FSJS'

AND LSRWZTLOG_CZ IN ('7', '9')

AND LSRWZTLOG_DJID IN (SELECT DJID FROM BW_Query_SXFX)

GROUP BY LSRWZTLOG_DJID, LSRWZTLOG_GNID--, LSRWZTLOG_CZ

ORDER BY COUNT(0) DESC;

*/


--BW


--BW



SQLSTR := 'SELECT

DJLXMC AS "单据类型"

, DJBH AS "单据编号"

, DJZTMC AS "单据状态"

, Creator AS "制单人"

, JYZTMC AS "所属中心经营主体"

, BMMC AS "所属部门"

, TJRQ AS "第一次制单提交审批时间"

, ZDQJY AS "制单期间年"

, ZDQJM AS "制单期间月"

, BZAMT AS " 报账金额"

, TIME2 AS "扫描时间"

FROM BW_Query_SXFX  where  1=1 ';

--IF trim(SQLWHERE) IS NOT NULL THEN

SQLSTR := SQLSTR || SQLWHERE;

-- END IF;


--|| sqlwhere || ' ORDER BY DJBH, COrder ';


--EXECUTE IMMEDIATE SQLSTR;

OPEN Re_CURSOR FOR SQLSTR;

end proc_SCAN;


end SXFX;

 

| 648658| | 2022-01-11T16:57:00| false| | 2022-01-11T16:57:24.183| true| 1、Packages create or replace package SXFX is /*【运营模块】【时效报表】申请共享系统时效分析报表 */ type T_CURSOR is ref cursor; procedure proc_SCAN( DJBH IN VARCHAR2, JYZTMC | Anonymous|

标签：SXFX,DJID,BW,oracle,Query,packages,WHERE,SELECT
From： https://www.cnblogs.com/ralphlauren/p/18621278

相关文章

  • oracle exp 无法导出空表
    oracleexp无法导出空表|Id|Title|DateAdded|SourceUrl|PostType|Body|BlogId|Description|DateUpdated|IsMarkdown|EntryName|CreatedTime|IsActive|AutoDesc|AccessPermission||-------------|-------------|-------------|-------------|......
  • Oracle-DG最大可用模式下,dg备库可能对主库有什么影响？
    --查询数据库的保护模式:>selectname,database_role,protection_modefromv$database;NAMEDATABASE_ROLEPROTECTION_MODE---------------------------------------------DINGDINGPHYSICALSTANDBYMAXIMUMAVAILABILITY--验证最高可用性日志传输模式：插入数据：切换......
  • python query oracle database
    oraclesqlscript:droptableIFEXISTSGEOVINDU.School;createtableGEOVINDU.School--創建表(SchoolIdchar(5)NOTNULL,--SchoolNamevarchar(500)NOTNULL,SchoolTelNovarchar(8)NULL,PRIMARYKEY(SchoolId)--#主鍵);......
  • ORACLE多表查询优化
    一、FROM子句后面的表顺序有讲究，ORACLE在解析sql语句的时候对FROM子句后面的表名是从右往左解析的，是先扫描最右边的表，然后在扫描左边的表，然后用左边的表匹配数据，匹配成功后就合并。所以，在对多表查询中，一定要把小表写在最右边。例如下面的两个语句：--tableA：100w条记录tableB：1w......
  • ORACLE sql查询用户/表/数据/外键/主键/索引
    查询用户数量--查询用户数量SELECTCOUNT(*)ASuser_countFROMDBA_USERS;查询表数量--查询表数量SELECTOWNER,COUNT(*)AStable_countFROMDBA_TABLESWHEREOWNERIN('RBCC_ICO_ADMIN','DPDB_CONFIDENTIAL','DPDB_INTERNAL')GROUPBYOWNER;......
  • oracle: create new database
    用databaseconfigurationAssistant引导创建数据库。 创建操作用户：--别加双引号，否则，无法用createusergeovinduidentifiedby888888;createusergeovinidentifiedby888888;grantconnect,resource,dbatogeovindu;grantconnect,resource,dbatogeovin;......
  • Oracle-RAC集群心跳
    转自：https://www.cnblogs.com/binliubiao/p/13823463.html心跳线物理布线注意事项不建议直连网线，建议采用交换机连接方式心跳线与业务网络分开单独接线若公用一个交换机时，使用VLAN隔离Oracle不支持使用交叉线(cross-cabling)作为Clusterware的内部连接导致集群不稳定......
  • 【YashanDB知识库】Oracle pipelined函数在YashanDB中的改写
    本文内容来自YashanDB官网，原文内容请见https://www.yashandb.com/newsinfo/7802940.html?templateId=1718516【问题分类】功能使用【关键字】pipelined【问题描述】OraclePL/SQL中包含pipelined函数的对象迁移到YashanDB会出现不兼容现象。【问题原因分析】Pipelined函数......
  • oracle添加ACL（访问控制表）的步骤
    一、新增步骤1:创建ACL文件并授予权限首先，我们创建一个新的ACL文件，并允许INFODBA用户访问外部HTTP服务。BEGIN--创建一个新的ACL，允许bakmes用户进行网络访问DBMS_NETWORK_ACL_ADMIN.create_acl(acl=>'http_acl.xml',--ACL文件名descr......
  • CSharp: Connecting to Oracle 11g Database in C#
     usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Data;usingSystem.Drawing;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;usingSystem.Windows.Forms;//usingOracle.DataAccess.Client;usin......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99