王绍桦

CREATE OR REPLACE TRIGGER TGR_NAME AFTER INSERT OR UPDATE ON TABLE1_NAME FOR EACH ROW BEGIN IF inserting then INSERT INTO TABLE2_NAME (zj,mc) VALUES(:new.zj,:new.mc); ELSIF updating then UPDATE

1.创建计划任务需要执行的存储过程CREATE OR REPLACE PROCEDURE "PRO_INTERVAL_DELETE_LOG" ASBEGIN DELETE FROM T_SYS_LOG WHERE CZSJ < SYSDATE-30;END;  备注：SQL为删除30天之前数据，可根据自身业务编写。2.创建计划任务DECLARE DELET...

1.生成执行计划explain plan forselect * from t_call_records where t_bjhm='123456'  备注：explain plan for后面为要生成执行计划的查询语句2.查看执行计划结果select * from table(dbms_xplan.display)    如上图所示，TABLE ACCESS FUL...

查询今天数据：SELECT COUNT(1) FROM T_CALL_RECORDS WHERE TO_CHAR(T_RKSJ,'YYYY-MM-DD')=TO_CHAR(SYSDATE,'YYYY-MM-DD')查询昨天数据：SELECT COUNT(1) FROM T_CALL_RECORDS WHERE TO_CHAR(T_RKSJ,'YYYY-MM-DD')=TO_CHAR...

  查看用户默认表空间SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='DB_USER_NAME';  查看表空间文件路径、大小、已使用、使用率SELECT B.FILE_NAME 物理文件名, B.TABLESPACE_NAME 表空间名称, B.BYTES/1024/1024 大小M,...

-- 调用：SELECT FUN_GET_TABLE_ID('TEST','ID','A',23) FROM DUALCREATE OR REPLACE FUNCTION FUN_GET_TABLE_ID(IN_TABLE_NAME VARCHAR2, IN_COLUMN_NAME VARCHAR2, ...

1.在本地数据库创建远程数据库的dbLinkCREATE PUBLIC DATABASE LINK LINK_NAMECONNECT TO USER IDENTIFIED BY PASSWORDUSING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.1.183)(PORT = 1521))...

数据导出：1.完全导出exp weixin/weixin@localhost:1521/orcl buffer=64000 file=F:/weixin.dmp full='y'2.按用户导出exp weixin/weixin@localhost:1521/orcl buffer=64000 file=F:/weixin.dmp owner='weixin'3.按表导出...

1.查询引起了锁表的原因select l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#, l.os_user_name, s.machine, s.terminal, a.sql_text,...

  创建存储过程：CREATE OR REPLACE PROCEDURE "TEST_PROC"(NAME IN VARCHAR2,DM IN VARCHAR2) IS ZJ VARCHAR2(40); JH VARCHAR2(10); VERRINFO VARCHAR2(200); USER_COUNT NUMBER; B...

update tablename set columnname = replace(columnname,substr(columnname,7,8),'11111111'); 

1.delete清空表操作： flashback table tableName to timestamp to_timestamp(‘2017-12-28 16:00:00’,’yyyy-mm-dd hh24:mi:ss’); 备注：上面时间为数据所要恢复的时间点，若提示 cannot flashback the table because row movement is not enable

select regexp_substr(b.columnName, ‘[^/]+’, 1, c.lv) from (select regexp_count(a.columnName, ‘[^/]+’) as cnt,a.columnName from tableName a ) b join (select level lv from columnName connect b

CREATE OR REPLACE TRIGGER TGR_NAME AFTER UPDATE ON TABLE1_NAME FOR EACH ROW DECLARE V_COUNT NUMBER(10); BEGIN SELECT COUNT(*) INTO V_COUNT FROM TABLE2_NAME WHERE ZJ=:NEW.ZJ; IF V_COUNT = 0 THEN

包含空字段的数据去重：DELETE FROM TABLE_NAME WHERE ROWID NOT IN(SELECT MIN(ROWID) FROM TABLE_NAME GROUP BY COLUMN1,COLUMN2)不包含空字段的数据去重：DELETE FROM TABLE_NAME TWHERE (T.COLUMN1,T.COLUMN2) IN ( SELECT ...