`
张洪财
  • 浏览: 275173 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

SQL精华收集 job

阅读更多
SQL精华收集-2
2008年12月16日 星期二 13:34
每一种物品有很多价格,每一种物品选择排在前三的纪录

1。job的使用:
DBMS_JOB.SUBMIT(:jobno,//job号
'your_procedure;',//要执行的过程
trunc(sysdate)+1/24,//下次执行时间
'trunc(sysdate)+1/24+1'//每次间隔时间
);
删除job:dbms_job.remove(jobno);
修改要执行的操作:job:dbms_job.what(jobno,what);
修改下次执行时间:dbms_job.next_date(job,next_date);
修改间隔时间:dbms_job.interval(job,interval);
停止job:dbms.broken(job,broken,nextdate);
启动job:dbms_job.run(jobno);
例子:
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
'Procdemo;',//Procdemo为过程名称
SYSDATE, 'SYSDATE + 1/720');
commit;
end;
/
2。把一个表放在内存里
alter table tablename cache.
3。创建临时表
CREATE GLOBAL TEMPORARY TABLE TABLENAME (
COL1 VARCHAR2(10),
COL2 NUMBER
) ON COMMIT PRESERVE(DELETE) ROWS ;
这种临时表不占用表空间,而且不同的SESSION之间互相看不到对方的数据
在会话结束后表中的数据自动清空,如果选了DELETE ROWS,则在提交的时候即清

空数据,PRESERVE则一直到会话结束
4。加一个自动增加的id号
第一种方法:
第一步:创建SEQUENCE
create sequence s_country_id increment by 1 start with 1 maxvalue

999999999;
第二步:创建一个基于该表的before insert 触发器,在触发器中使用该

SEQUENCE
create or replace trigger bef_ins_t_country_define
before insert on t_country_define
referencing old as old new as new for each row
begin
select s_country_id.nextval into :new.country_id from dual;
end;
/
第二种方法:
CREATE OR REPLACE TRIGGER TR1
BEFORE INSERT ON temp_table
FOR EACH ROW
declare
com_num NUMBER;
BEGIN
SELECT MAX(ID) INTO COM_NUM FROM TEMP_TABLE;
:NEW.ID:=COM_NUM+1;
END TR1;
/
5。限制用户登录:创建一个概要文件
create profile CLERK_PROFILE limit
session_per_user 1 #用户可拥有的会话次数
idle_time 10 #进程处于空闲状态的时间(10分钟)
然后就可以将该概要文件授予一个用户
alter user A profile CLERK_PROFILE;
6。使触发器为无效alter trigger yourtriggername disable

如果是对于某一个表的所有的触发器:
alter table yourtablename disable all triggers


更改数据库时间显示格式:
SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
会话已更改。

1. 选取 TOP N 行记录
A. SELECT * FROM CAT WHERE ROWNUM<=N
B. SELECT * FROM
( SELECT * FROM CAT ORDER BY TABLE_TYPE )
WHERE ROWNUM<=N

2. 选取N1-N2行记录
A. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT )
WHERE ROWSEQ BETWEEN N1+1 AND N2;
或:
SELECT * FROM CAT WHERE ROWNUM<=N2
MINUS
SELECT * FROM CAT WHERE ROWNUM
B. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY TABLE_TYPE)
WHERE ROWSEQ BETWEEN N1+1 AND N2;

查主键名称:
select * from user_constraints
where table_name = 'ART'
and constraint_type ='P';

保存过程内容到文件
先修改init.ora
例如:
utl_file_dir=/usr //路径为 oracle所在的盘:/usr
此过程将用户TEMP的P1过程的代码保存到ORACLE安装盘下/USR/TEXT.TXT中
create or replace procedure TEST
is
file_handle utl_file.file_type;
STOR_TEXT VARCHAR2(4000);
N NUMBER;
I NUMBER;
begin
I:=1;
SELECT MAX(LINE) INTO N FROM ALL_SOURCE WHERE OWNER='TEMP' AND NAME='P1';
file_handle:=utl_file.fopen('/usr','test.txt','a');
WHILE I<=N LOOP
SELECT TEXT INTO STOR_TEXT FROM ALL_SOURCE WHERE OWNER='TEMP' AND NAME='P1' AND LINE= I;
I:=I+1;
utl_file.put_line(file_handle,stor_text);
END LOOP;
utl_file.fclose(file_handle);
commit;
end TEST;
/

0、建立分区表
create table partition_test
(
id number(9),
tmpStr varchar2(10)
)
partition by range(id)
(
partition id01 values less than (3000000) tablespace test_tabspc1,
partition id02 values less than (6000000) tablespace test_tabspc2,
partition id03 values less than (9000000) tablespace test_tabspc3,
partition id04 values less than (12000000) tablespace test_tabspc4,
partition id05 values less than (MAXVALUE) tablespace test_tabspc5
)
/

1、建立局部分区索引
Create index your_index on caishui.partition_test(id)
local
(
partition id01 tablespace test_tabspc1,
partition id02 tablespace test_tabspc2,
partition id03 tablespace test_tabspc3,
partition id04 tablespace test_tabspc4,
partition id05 tablespace test_tabspc5
)
/

2、重建某一个分区的索引
alter index your_index rebuild partition id01 tablespace test_tabspc1
/

3、增加分区
alter table caishui.partition_test
add partition id06 values less than (15000000) tablespace test_tabspc6
/

4、有影响

5、可以
ALTER TABLE PARTITION_TEST
MERGE PARTITIONS
id01, id02
INTO PARTITION 新分区名
/

6、外部数据文件 d:\test.txt
1|猪八戒
2|孙悟空
3|唐僧

建一个控制文件 d:\test.ctl
load data
infile 'd:\test.txt'
append
into table partition_test
FIELDS TERMINATED BY "|"
(id,tmpStr)

将数据文件的数据导入数据库
sqlldr userid=caishui/password control=d:\test.ctl

如何正确利用Rownum来限制查询所返回的行数?

软件环境:
1、Windows NT4.0+ORACLE 8.0.4
2、ORACLE安装路径为:C:\ORANT

含义解释:
1、rownum是oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,
依此类推,这个伪字段可以用于限制查询返回的总行数。
2、rownum不能以任何基表的名称作为前缀。
使用方法:
现有一个商品销售表sale,表结构为:
month    char(6)      --月份
sell    number(10,2)   --月销售金额

create table sale (month char(6),sell number);
insert into sale values('200001',1000);
insert into sale values('200002',1100);
insert into sale values('200003',1200);
insert into sale values('200004',1300);
insert into sale values('200005',1400);
insert into sale values('200006',1500);
insert into sale values('200007',1600);
insert into sale values('200101',1100);
insert into sale values('200202',1200);
insert into sale values('200301',1300);
insert into sale values('200008',1000);
commit;

SQL> select rownum,month,sell from sale where rownum=1;(可以用在限制返回记录条数的地方,保证不出错,如:隐式游标)

ROWNUM MONTH SELL
--------- ------ ---------
1 200001 1000

SQL> select rownum,month,sell from sale where rownum=2;(1以上都查不到记录)

没有查到记录

SQL> select rownum,month,sell from sale where rownum>5;
(由于rownum是一个总是从1开始的伪列,Oracle 认为这种条件不成立,查不到记录)


没有查到记录

只返回前3条纪录
SQL> select rownum,month,sell from sale where rownum<4;

ROWNUM MONTH SELL
--------- ------ ---------
1 200001 1000
2 200002 1100
3 200003 1200


如何用rownum实现大于、小于逻辑?(返回rownum在4—10之间的数据)(minus操作,速度会受影响)
SQL> select rownum,month,sell from sale where rownum<10
2 minus
3 select rownum,month,sell from sale where rownum<5;

ROWNUM MONTH SELL
--------- ------ ---------
5 200005 1400
6 200006 1500
7 200007 1600
8 200101 1100
9 200202 1200

想按日期排序,并且用rownum标出正确序号(有小到大)
SQL> select rownum,month,sell from sale order by month;

ROWNUM MONTH SELL
--------- ------ ---------
1 200001 1000
2 200002 1100
3 200003 1200
4 200004 1300
5 200005 1400
6 200006 1500
7 200007 1600
11 200008 1000
8 200101 1100
9 200202 1200
10 200301 1300

查询到11记录.

可以发现,rownum并没有实现我们的意图,系统是按照记录入库时的顺序给记录排的号,rowid也是顺序分配的

SQL> select rowid,rownum,month,sell from sale order by rowid;

ROWID ROWNUM MONTH SELL
------------------ --------- ------ ---------
000000E4.0000.0002 1 200001 1000
000000E4.0001.0002 2 200002 1100
000000E4.0002.0002 3 200003 1200
000000E4.0003.0002 4 200004 1300
000000E4.0004.0002 5 200005 1400
000000E4.0005.0002 6 200006 1500
000000E4.0006.0002 7 200007 1600
000000E4.0007.0002 8 200101 1100
000000E4.0008.0002 9 200202 1200
000000E4.0009.0002 10 200301 1300
000000E4.000A.0002 11 200008 1000

查询到11记录.

正确用法,使用子查询
SQL> select rownum,month,sell from (select month,sell from sale group by month,sell) where rownum<13;

ROWNUM MONTH SELL
--------- ------ ---------
1 200001 1000
2 200002 1100
3 200003 1200
4 200004 1300
5 200005 1400
6 200006 1500
7 200007 1600
8 200008 1000
9 200101 1100
10 200202 1200
11 200301 1300

按销售金额排序,并且用rownum标出正确序号(有小到大)
SQL> select rownum,month,sell from (select sell,month from sale group by sell,month) where rownum<13;

ROWNUM MONTH SELL
--------- ------ ---------
1 200001 1000
2 200008 1000
3 200002 1100
4 200101 1100
5 200003 1200
6 200202 1200
7 200004 1300
8 200301 1300
9 200005 1400
10 200006 1500
11 200007 1600

查询到11记录.

利用以上方法,如在打印报表时,想在查出的数据中自动加上行号,就可以利用rownum。

返回第5—9条纪录,按月份排序
SQL> select * from (select rownum row_id ,month,sell
2 from (select month,sell from sale group by month,sell))
3 where row_id between 5 and 9;

ROW_ID MONTH SELL
---------- ------ ----------
5 200005 1400
6 200006 1500
7 200007 1600
8 200008 1000
9 200101 1100

(1)

查所及杀锁
select l.session_id sid,
l.locked_mode lockmode,
l.oracle_username db_user,
l.os_user_name os_user,
s.machine,
s.schemaname,
o.object_name tablename,
q.sql_text
from v$locked_object l, v$session s, v$sql q, all_objects o
where l.session_id=s.sid and
s.type='USER' and
s.sql_address=q.address and
l.object_id=o.object_id

alter system kill session 'sid,SERIAL#'


1.having 子句的用法

having 子句对 group by 子句所确定的行组进行控制,having 子句条件中只允许涉及常量,聚组函数或group by 子句中的列.

2.外部联接"+"的用法

外部联接"+"按其在"="的左边或右边分左联接和右联接.若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,则前者的行与 后者中的一个空行相匹配并被返回.若二者均不带’+’,则二者中无法匹配的均被返回.利用外部联接"+",可以替代效率十分低下的 not in 运算,大大提高运行速度.例如,下面这条命令执行起来很慢

select a.empno from emp a where a.empno not in

(select empno from emp1 where job=’SALE’);

倘若利用外部联接,改写命令如下:

select a.empno from emp a ,emp1 b

where a.empno=b.empno(+)

and b.empno is null

and b.job=’SALE’;

可以发现,运行速度明显提高.

3.删除表内重复记录的方法

可以利用这样的命令来删除表内重复记录:

delete from table_name a

where rowid< (select max(rowid) from table_name

where column1=a.column1 and column2=a.column2

and colum3=a.colum3 and ...);

问:用ORACLE的like(匹配操作命令)操作时,要查的条件含有特殊符号(_或%),该怎样写?
如我要找出以tt_开头的表,若安以下写法只能取出以tt开头的表,
因为_在like中用意是任意单一字符。
SELECT Tname FROM tab
WHERE Tname like 'tt_%'


答:SELECT * FROM tab
WHERE tname LIKE 'TT/_%' ESCAPE '/'

自增字段:
ORACLE一般的做法是同时使用序列和触发器来生成一个自增字段.
CREATE SEQUENCE SEQname
INCREMENT BY 1
START WITH 1
MAXVALUE 99999999
/
CREATE TRIGGER TRGname
BEFORE INSERT ON table_name
REFERENCING
NEW AS :NEW
FOR EACH ROW
Begin
SELECT SEQname.NEXTVAL
INTO :NEW.FIELDname
FROM DUAL;
End;
/

动态sql:
在oracle8.1.5中:
用execute immediate来实现
declare
tsql varchar2(200);
begin
tsql:='insert into '||tname||'values ('aaa','bbb')';
execute immediate tsql;
end;
/



说明:复制表(只复制结构,源表名:a 新表名:b)
SQL: select * into b from a where 1<>1
说明:拷贝表(拷贝数据,源表名:a 目标表名:b)
SQL: insert into b(a, b, c) select d,e,f from b;
说明:显示文章、提交人和最后回复时间
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
说明:外连接查询(表名1:a 表名2:b)
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
说明:日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
说明:两张关联表,删除主表中已经在副表中没有的信息
SQL:
delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
说明:--
SQL:
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1,
(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,
(SELECT NUM, UPD_DATE, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') || '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y,
WHERE X.NUM = Y.NUM (+)
AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B
WHERE A.NUM = B.NUM
说明:--
SQL:
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩
说明:
从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源)
SQL:
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
FROM TELFEESTAND a, TELFEE b
WHERE a.tel = b.telfax) a
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')
说明:四表联查问题:
SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

说明:得到表中最小的未使用的ID号
SQL:
SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID
FROM Handle
WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)
分享到:
评论

相关推荐

    SQL 精华 整合版

    这个“SQL精华整合版”包含了多个关于SQL学习的资源,旨在帮助用户从基础到高级全面掌握SQL语言。让我们逐一探讨这些资源中可能涵盖的关键知识点。 1. **SQL21自学通.rar**:这可能是一个系统的SQL教程,涵盖了SQL...

    xxl-job-admin-sqlserver.zip

    XXL-JOB是一个轻量级分布式任务调度平台,其核心设计目标是开发迅速、学习简单、轻量级、易扩展。现已开放源代码并接入多家公司线上产品线,开箱即用. ...xxl-job-admin 版本为2.0.2 SqlServer版本为2012

    sql server job定时任务

    sql server job 定时任务 纯碎数据库客户端定时任务

    sql脚本创建job任务调度

    ### SQL脚本创建Job任务调度知识点详解 #### 一、任务调度概述 在数据库管理领域,**任务调度**是一项非常重要的功能,它允许用户设定特定的任务(如备份、清理、统计等)按照预设的时间自动执行。这不仅提高了工作...

    自动备份sqlserver JOB 用脚本

    在SQL Server管理中,"JOB"是一个非常重要的概念,它是一种计划的任务,可以在特定时间或按一定频率自动执行。在本场景中,标题提到的"自动备份sqlserver JOB 用脚本"指的是利用SQL Server Agent服务创建一个JOB,该...

    SQLServer定时作业job的设置方法.doc

    SQL Server 定时作业 Job 的设置方法 本文档主要介绍了在 SQL Server 中设置定时作业 Job 的方法,包括创建作业、设置步骤、调度和通知等步骤的详细配置过程。 创建作业 在 SQL Server 中,要创建一个新的作业,...

    SQL Server精华技巧集

    SQL Server精华技巧集,自已收集.

    sqlserver新建job时报c001f011错的解决

    ### SQL Server 新建 Job 时报 C001F011 错误的解决方法 在 SQL Server 的日常管理和维护过程中,我们经常会遇到各种各样的问题。其中,“新建 Job 时出现 C001F011 错误”就是一个比较常见的问题。本文将详细介绍...

    SQL Server 2000定时计划job

    在SQL Server 2000中,定时计划job是一项重要的管理功能,允许用户安排自动执行的任务,例如数据清理。在上述场景中,我们利用这个特性来实现对离港信息表dyndepflt的定期维护,确保数据库保持高效运行并节省存储...

    xxl_job.sql

    xxl_job.sql

    从SQLServer收集事件并将其保存到Elasticsearch以便进行进一步分析

    本教程将探讨如何从SQL Server收集事件,并将这些事件数据传输到Elasticsearch,以便进行更深入的分析和洞察。 首先,我们需要了解SQL Server中的事件收集。SQL Server提供了SQL Server Profiler和Extended Events...

    sql精华电子书版 chm格式

    sql server 2000 电子书,sql server chm格式的电子书。sql精华电子书版 sql精华电子书版

    sql脚本创建job任务调度.doc

    SQL脚本创建Job任务调度 SQL脚本创建Job任务调度是SQL Server中的一种任务调度机制,该机制允许用户创建、管理和执行各种类型的Job任务,例如数据备份、统计报表生成、数据传输等。下面是该机制的详细知识点: 1. ...

    SQL Server 2008的性能数据收集器

    ### SQL Server 2008性能数据收集器详解 #### 引言 SQL Server 2008引入了一项革新性的功能——性能数据收集器,旨在简化数据库性能监控与分析流程,为数据库管理员(DBA)提供了一种更加高效、集中化的方法来处理...

    oracle pl/sql 精华

    oracle pl/sql 精华 oracle pl/sql 精华

    xxl-job的pgsql初始化sql

    在使用XXL-JOB之前,你需要将这些SQL脚本在你的PostgreSQL数据库中执行,以便于XXL-JOB能正常地存储和管理任务调度的相关信息。 首先,XXL-JOB的数据库模型主要包括以下几个核心表: 1. `xxl_job_info`:存储任务...

    xxl-job2.2.0使用oracle数据库,执行此sql,来创建数据表

    xxl-job2.2.0使用oracle数据库,执行此sql,来创建数据表

    监控MS SQL 的Job运行状况的存储过程,很强悍

    2. **job_monitor.sql** - 这个文件包含了主存储过程的源代码,它负责收集关于Jobs的信息,如Job的状态、最近的执行时间、持续时间、是否成功等。这个存储过程可能通过查询`sysjobs`、`sysjobhistory`等系统视图来...

    SQLSERVER语句收集

    SQLSERVER语句收集SQLSERVER语句收集SQLSERVER语句收集SQLSERVER语句收集SQLSERVER语句收集SQLSERVER语句收集SQLSERVER语句收集SQLSERVER语句收集SQLSERVER语句收集SQLSERVER语句收集

    flink_sql_JOB.sql

    flink_sql_JOB.sql

Global site tag (gtag.js) - Google Analytics