![](https://img-blog.csdnimg.cn/20201014180756916.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
SQL
hutudanvip
这个作者很懒,什么都没留下…
展开
-
触发器
--短信源表创建源表触发器create or replace trigger "TRG_UMP_SOURCE_VIP" after insert on T_UMP_SOURCE_VIP for each rowdeclarebegin insert into t_ump_source_temp (ID, CONTENT,原创 2015-01-25 23:11:19 · 242 阅读 · 0 评论 -
数据泵
select count(*) from t_rpt_innerprofitlisttruncate table t_rpt_innerprofitlist1、建立目录create or replace directory dir_dp1 as '/dump1';2、查看创建目录表select * from dba_dir原创 2015-01-25 23:12:50 · 317 阅读 · 0 评论 -
游标的使用demo
create or replace package body barcode isprocedure syncCommData is--变量定义--Sql语句v_insert_sql varchar2(2000);v_merge_sql varchar2(2000);v_delete_sql varchar2(2000);--是否有足够长的变更记录hasLo原创 2015-01-25 23:23:50 · 312 阅读 · 0 评论 -
start with
--根据部门编码查询其对应的大区或大区的平级 select * from t_org_department d where d.deptlevel = 4 start with d.finasyscode = 'DP02035' connect by prior d.parentid = d.id原创 2015-01-25 23:11:59 · 321 阅读 · 0 评论 -
游标的使用
--创建包,并且定义类型test_cursor为游标create or replace package testpackage as type test_cursor is ref cursor;end testpackage;--创建过程,获取数据create or replace procedure afei_pro_emp( spNo原创 2015-01-25 23:24:34 · 293 阅读 · 0 评论 -
oracle导入导出DMP文件
导出exp dp009/dperp32011@DEP_192.168.17.253 FILE=E:\AA.DMP TABLES=t_bd_accountbanks导入imp dp009/dperp32011@DEP_192.168.17.201 FILE=E:\AA.DMP tables=T_TMP_BUSIDEPTCOMPANYimp finmanager/fin原创 2015-01-25 23:17:10 · 514 阅读 · 0 评论 -
oracle start with connect by
oracle中 connect by prior 递归算法 Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的,其基本语法是: select ... from tablename start with 条件1 connect by 条件2 where 条件3; 例: select * from t转载 2015-01-25 23:28:27 · 399 阅读 · 0 评论 -
Oracle中被锁的表以及解锁
select * from t_gl_voucher;update t_dar_deparmonrptstart rpt set rpt.ffivouchered = 0;---查看被锁的表SELECTa.sid, b.owner, object_name, object_typeFROM v$lock a, all_objects bWHERE TYPE =原创 2015-01-26 22:42:03 · 346 阅读 · 0 评论 -
oracle数组的使用
declare type type_array is varray(192) of varchar2(80); number_array type_array := type_array('20120201_W04061302' ); id_array type_array := type_ar原创 2015-01-26 22:42:28 · 537 阅读 · 0 评论 -
创建同义词
/*赋表dep009.t_bd_bankdep009.t_cus_openbankserdep009.t_cus_cityserdep009.t_cus_provinceser权限给用户finwbsuser*/grant insert,delete,select,update on dep009.t_bd_bank to finwbsuser;grant i原创 2015-01-26 22:37:00 · 694 阅读 · 0 评论 -
物化视图的使用
手工刷新物化视图exec dbms_mview.refresh('T_DAR_ARSTATE');DROP MATERIALIZED VIEW t_dept_view;DROP MATERIALIZED VIEW t_dept_city_view;/*城市客户*/CREATE MATERIALIZED VIEW t_dept_city_view --创建原创 2015-01-26 22:42:33 · 378 阅读 · 0 评论 -
删除重复数据
--消耗很少delete from t_dfn_monthdel where (fshippernumberid,fmonth) in (select fshippernumberid,fmonth from t_dfn_monthdel group by fshippernumberid,fmonth原创 2015-01-26 22:42:53 · 240 阅读 · 0 评论 -
PLSQL添加数据库连接
修改D:\oracle\product\10.2.0\client_1\tnsnames.ora文件fin_67.25 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.67.25)(PORT = 1521)) ) (CONNECT_D原创 2015-01-26 22:42:15 · 686 阅读 · 0 评论 -
视图的使用
1、/**创建报表视图*/create or replace view vm_claim_countcashrptdata asselect --事业部财务部 (select bus.finasyscode from t_org_department bus where bus.status = 1原创 2015-01-25 23:27:46 · 317 阅读 · 0 评论 -
创建DBLINK
以下是引用片段: create public database link fwq12 connect to fzept identified by neu using 'fjept' CREATE DATABASE LINK数据库链接名CONNECT TO 用户名 IDENTIFIED BY 密码 USING ‘本地配置的数据的实例名’;--创建DBLI原创 2015-01-25 23:21:39 · 254 阅读 · 0 评论 -
oracle基本语法
1、建表语句create table T_FINS_OAALERT( FID VARCHAR2(44) primary key not null, FBUSICODE VARCHAR2(20), FBILLNUM VARCHAR2(20), FBILLTYE VARCHAR2(20), FBUSITYPE VARCHAR2(20), FCREATETIM原创 2015-01-25 23:14:26 · 345 阅读 · 0 评论