- 博客(27)
- 资源 (1)
- 收藏
- 关注
RSS订阅原创 Oracle raw类型数据转成varchar2
将raw类型数据转成varchar2的函数create or replace function func_conversion(src_val in varchar2) return VARCHAR2 IS rtn_utf_val VARCHAR2(2000 CHAR); rtn_big5_val VARCHAR2(2000 CHAR); err_num NUMBER; err_msg VARCHAR2(1000);BEGIN BEGIN SEL
2021-07-16 11:18:31
718
原创 Oracle自定义聚合函数 拼接超长字符串
Oracle拼接字符串是由长度限制的，可以自定义聚合函数来解决这个问题create or replace TYPE T_LINK AS OBJECT ( STR VARCHAR2(32767), STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARC
2021-07-16 11:12:11
1113
原创 Oracle查找乱码的数据
SELECT COL_NAMEFROM table_name WHERE ASCIISTR(COL_NAME) LIKE '%\FFFD%' OR COL_NAME IS NOT NULL AND ASCIISTR(COL_NAME) IS NULLOR ASCII(SUBSTR(ASCIISTR(COL_NAME),1,1)) BETWEEN 2 AND 31OR LENGTHB(COL_NAME)>LENGTHB(ASCIISTR(COL_NAME));
2021-07-15 11:15:42
2339
2
原创 Oracle生成日期列表
SELECT TO_CHAR(TO_DATE('20190401', 'YYYYMMDD') + ROWNUM - 1,'YYYYMMDD') AS DATE_LIST FROM DUALCONNECT BY ROWNUM <= TRUNC(TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD') - TO_DATE('20190401', 'YYYYMMDD')) + 1;
2021-07-15 10:57:22
1290
原创 Oracle生成data patching脚本
生成data patching脚本DECLARE V_SQL CLOB; V_SUB_STR VARCHAR2(32767); V_OWNER VARCHAR2(30) := 'schema'; --' V_TABLE_NAME VARCHAR2(30) := 'table_name'; --' V_COUNT INTEGER := 0; V_TEMP_CNT INTEGER; V_PK_NUM
2021-07-15 10:57:06
173
原创 Oracle查看表中是否有中文字符
--查看表中是否有中文字符DECLARE V_SQL CLOB; TYPE T_TABLE_ARRAY IS VARRAY(50) OF VARCHAR(50); TABLES T_TABLE_ARRAY := T_TABLE_ARRAY('table_name'); V_OWNER VARCHAR2(30) := 'schema'; -- V_RESULT INTEGER; BEGIN FOR I IN 1 .. TABLES.COUNT LOOP
2021-07-15 10:56:46
481
原创 Oracle aes128和aes256加解密
–aes128加密DECLARE l_src_data VARCHAR2(20) := 'Source Data'; l_type pls_integer := dbms_crypto.encrypt_aes128 + dbms_crypto.pad_pkcs5 + dbms_crypto.chain_cbc; l_key VARCHAR2(16) := '0123456789123456'; l_encval raw(2000);BEGIN l_encval :
2021-07-15 10:56:15
1313
原创 Oracle查看正在执行的sql及锁住的表
–查看正在执行的sqlSELECT b.sid oracleID, b.username 登录Oracle用户名, b.serial#, spid 操作系统ID, paddr, sql_text 正在执行的SQL, b.machine 计算机名FROM Gv$process a, Gv$session b, Gv$sqlarea cWHERE a.addr = b.paddr --and b.username='
2021-07-15 10:55:58
550
原创 Oracle批量生成拉链表trigger
DECLARE V_SQL CLOB; V_SUB_STR VARCHAR2(32767); TYPE T_TABLE_ARRAY IS VARRAY(50) OF VARCHAR(50); TABLES T_TABLE_ARRAY := T_TABLE_ARRAY('table_name'); V_OWNER VARCHAR2(30) := ''; --' V_TAB_SPACE VARCHAR2(30) := ''; -- V_ROLE
2021-07-15 10:55:43
299
原创 SQL Server data patching模板
DECLARE @SVOC_HIS_FULL_TBL_NM NVARCHAR(128);DECLARE @SVOC_TBL_OWNER NVARCHAR(10);DECLARE @SVOC_CUR_FULL_TBL_NM NVARCHAR(128);DECLARE @RERUN_DT NVARCHAR(10);DECLARE @NEW_EFF_DT NVARCHAR(100);DECLARE @JOIN_STMT
2021-07-15 10:54:08
116
原创 Oracle查看SYS_LOB*文件属于哪个表
查看SYS_LOB*文件属于哪个表SELECT B.TABLE_NAME, B.COLUMN_NAME, A.SEGMENT_NAME, a.SEGMENT_TYPE, ROUND(SUM(A.BYTES / 1024 / 1024 / 1024), 2) G FROM DBA_SEGMENTS A LEFT JOIN DBA_LOBS B ON A.OWNER = B.OWNER AND A.SEGMENT_NAME = B.
2021-07-14 16:26:51
2300
原创 Oracle表空间管理
查看表空间剩余空间SELECT SUM(bytes) / (1024 * 1024 * 1024) AS free_space, tablespace_nameFROM dba_free_spacewhere TABLESPACE_NAME = 'tablespace name' GROUP BY tablespace_name;SELECT a.tablespace_name as tablespace_name,to_char(b.total/1024/1024/1024,999999.99
2021-07-14 16:26:23
113
原创 Oracle存储过程模板
create or replace procedure 存储过程名（param1 in type，param2 out type） as 变量1 类型（值范围）; 变量2 类型（值范围）; Begin Select count(*) into 变量1 from 表A where列名=param1； If (判断条件) then Select 列名 into 变量2 from 表A where列名=param1； Dbms_output。Put_li
2021-07-14 16:02:58
261
原创 Oracle查询某些字段的重复记录及删除
查询某些字段的重复记录select a.col1,a.col2 from tablename a, (select col1,col2 from tablename group by col1,col2 having count(*)>1) b where a.col1=b.col1 and a.col2=b.col2–保留最新的一条DELETE FROM TABLE_NAME WHERE ROWID NOT IN(SELECT MAX(ROWID) FROM TABLE_NAME D
2021-07-14 16:01:10
181
原创 Sybase生成oracle建表语句
单个表--单个表declare @pre varchar(10),@schema varchar(20), @tablename varchar(30), @tablespace varchar(30), @role1 varchar(100), @role2 varchar(100)set @pre ='' set @schema ='' --set @tablename ='tblMDRT
2021-07-14 15:59:10
183
原创 Sybase查询某些字段的重复记录
查询某些字段的重复记录select a.col1,a.col2 from tablename a, (select col1,col2 from tablename group by col1,col2 having count(*)>1) b where a.col1=b.col1 and a.col2=b.col2
2021-07-14 15:56:36
330
原创 SQL Server 生成oracle建表语句
declare @TABLESPACE nvarchar(100)= 'oracle tablespace'declare @SCHEMA nvarchar(100)= 'oracle schema'declare @PRE nvarchar(100)= 'table prefix'declare @role nvarchar(100)= 'grant role name'declare @TABLENAME nvarchar(100)= 'sql server table_name'SE
2021-07-14 11:57:49
372
2
原创 SQL Server 现有表建立分区表
查看表分区SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows] FROM sys.partitions p INNER JOIN sys.objects o ON o.object_id=p.object_id INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id WHERE o.nam
2021-07-14 11:55:43
428
原创 SQL Server 表重命名
–表重命名sp_rename 'schema_name.old_table_name', 'new_table_name';
2021-07-14 11:34:20
970
原创 SQL Server 查看表结构
SQL Server 查看表结构SELECT distinct TABLE_NAME=d.name, COLUMN_NAME=A.name, DATA_TYPE = B.name, CHAR_LENGTH=A.length, DATA_PRECISION=A.xprec, DATA_SCALE=A.xscale, NULLABLE = Case When A.isnullable=1 Then 'NULL' Else 'NOTNULL' End, PRIMARY_
2021-07-14 11:32:43
825
原创 Python迭代设计
from xml.etree.ElementTree import Elementimport os, abc, shutilclass BDMMetaObject(metaclass=abc.ABCMeta): @abc.abstractmethod def to_xml(self): pass @classmethod @abc.abstractmethod def xml_tag_name(cls): passcl
2021-07-12 18:26:49
51
原创 Python生成exe可执行文件
window 方式@echo offset PROJECT_ROOT=%~dp0\..\set PYTHONPATH=%PROJECT_ROOT%\pyinstaller -F --distpath "%PROJECT_ROOT%\bin" --workpath "%tmp%" --clean "%PROJECT_ROOT%\bin\py_gen_parm.spec"spec配置文件 py_gen_parm.spec# -*- mode: python ; coding: utf-8 -*-
2021-07-12 18:23:25
127
原创 Shell 脚本模板常用
#!/bin/bash#----------------------------------------------------------------------------# Purpose :# Author :# Date :# Usage :# Sample :#---------------------------------------------------------------------------BASE_DIR=$(cd "`dirname
2021-07-12 17:19:48
100
原创 Shell 获取文件时间，修改文件时间
##获取文件时间ctime,atime,mtime %x Time of last access, human-readable %X Time of last access, seconds since Epoch %y Time of last modification, human-readable %Y Time of last modification, seconds since Epoch %z Time of last change, human-re
2021-07-12 17:10:30
3958
原创 shell mutt发邮件
1 定义邮件函数FunEmail(){ echo -e $* | mutt -e "my_hdr From:test@qq.com" -e 'set content_type="text/html"' -s "[Error]" ${mail_list}}email_txt=' Hi All,<br><br><font color=blue>这里可以使用html的元素</font>'#调用函数FunEmail $email_txt2 直接调用
2021-07-12 17:00:48
199
原创 Shell getopt 解析命令行参数
在执行脚本时，通过传入参数的方法，赋予变量值。run_type=$(echo $* | grep "-" | wc -l)if [ $run_type -ge 1 ]; then ARGS=`getopt -o L:B:T:F:S: --long LAYER:,BAT_TS:,TABLE:,FILE_LIST:,SOURCE: -- "$@"` if [ $? -ne 0 ] ; then echo "$USAGE_STR" exit 1 ; fi eval set -- "$AR
2021-07-12 16:49:09
248
原创 Shell 多线程并发运行脚本
Shell 多线程并发运行脚本初始化线程池## @TODO: Initial thread pool## @IN: null## @OUT: nullfunction THREAD_INT { FIFO_FILE=${BASE_DIR}/Initial_pool.fifo [[ -e "$FIFO_FILE" ]] && rm -f "$FIFO_FILE" mkfifo "$FIFO_FILE" exec 6<> "$FIFO_FILE
2021-07-12 16:33:57
505
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人