“koi0985”通过精心收集，向本站投稿了7篇求记录中的数据的方法!数据库教程，下面是小编为大家整理后的求记录中的数据的方法!数据库教程，供大家参考借鉴，希望可以帮助您。
篇1：求记录中的数据的方法!数据库教程
数据
已知数据:
Column1 Column2 Column3 Column4A 10 am -01-01 00:00:00.000A 11 am 1999-01-02 00:00:00.000B 12 bm 1999-01-03 00:00:00.000B 13 bm 1999-01-04 00:00:00.000C 14 cm 1999-01-05 00:00:00.000C 15 cm 1999-01-06 00:00:00.000
要求得到数据:
Column1 Column2 Column3 Column4A 11 am 1999-01-02 00:00:00.000B 13 bm 1999-01-04 00:00:00.000C 15 cm 1999-01-06 00:00:00.000
--数据装载
Create Table #T(Column1 varchar(10),Column2 int,Column3 varchar(10),Column4 datetime)insert #T select 'A',10,'am','1999-1-1'union all select 'A',11,'am','1999-1-2'union all select 'B',12,'bm','1999-1-3'union all select 'B',13,'bm','1999-1-4'union all select 'C',14,'cm','1999-1-5'union all select 'C',15,'cm','1999-1-6'
--测试语句 方法1：
select a.* from #T a where (a.Column4) = (select top 1 (Column4) from #T where Column1 = a.Column1 order by Column4 desc)
--测试结果:
Column1 Column2 Column3 Column4 ---------- ----------- ---------- ------------------------------------------------------ A 11 am 1999-01-02 00:00:00.000B 13 bm 1999-01-04 00:00:00.000C 15 cm 1999-01-06 00:00:00.000
--测试语句 方法2:(最佳效率)
select a.* from #T a join( select Column1,Column4=max(Column4) from #T group by Column1 )b on a.Column1=b.Column1 and a.Column4=b.Column4order by a.Column1
--测试结果 方法2:
Column1 Column2 Column3 Column4 ---------- ----------- ---------- ------------------------------------------------------ A 11 am 1999-01-02 00:00:00.000B 13 bm 1999-01-04 00:00:00.000C 15 cm 1999-01-06 00:00:00.000
篇2：四种数据库，取随机记录的方法数据库教程
数据|数据库|随机
mysql：select * from tablename order by rand limit 10sqlserver：select top 10 * from tablename order by NEWID()
ORACLE：使用 dbms random value的一种方法
SQL> select *from (select *from t order by dbms_random.value) where rownum<10;
A ---------- 39 101 134 5 83 97 96 140 81
已选择9行，
SQL> select *from (select *from t order by dbms_random.value) where rownum<10;
A ---------- 27 118 141 103 128 10 142 68 74 有人说这是一种效率最差的方法,恳请告知比这更好的解决方案!
ACCESS（一）：
yourstr=“*1*3*4*6*12*....”sql=“select top 10 * form. yourdb where instr('*'&id&'*','”&yourstr&“')0
ACCESS（二）：
<% n=10 ''取任意10条记录 set rs = server.CreateObject (”adodb.recordset“) sql = ”select * from table“ rs.open sql,conn,1,1 count=rs.recordcount ''记录总数 IF Countempty Then Randomize for i = 1 to n ''循环n次 num=Fix(Rnd*count) ''num便是随机产生的记录行数，用Fix()，使其不会大于count值。 rs.move num ''移到改随机行 Response.write rs(0) ''出该条记录 rs.movefirst ''别忘了再把指针移到第一条 next End IF rs.close set rs = nothing%>
SQL> select *from (select *from t order by dbms_random.value) where rownum<10;
A ---------- 39 101 134 5 83 97 96 140 81
已选择9行，
SQL> select *from (select *from t order by dbms_random.value) where rownum<10;
A ---------- 27 118 141 103 128 10 142 68 74 有人说这是一种效率最差的方法,恳请告知比这更好的解决方案!
ACCESS（一）：
yourstr=”*1*3*4*6*12*....“sql=”select top 10 * form. yourdb where instr('*'&id&'*','“&yourstr&”')0
ACCESS（二）：
<% n=10 ''取任意10条记录 set rs = server.CreateObject (“adodb.recordset”) sql = “select * from table” rs.open sql,conn,1,1 count=rs.recordcount ''记录总数 IF Countempty Then Randomize for i = 1 to n ''循环n次 num=Fix(Rnd*count) ''num便是随机产生的记录行数，用Fix()，使其不会大于count值。 rs.move num ''移到改随机行 Response.write rs(0) ''出该条记录 rs.movefirst ''别忘了再把指针移到第一条 next End IF rs.close set rs = nothing%>
篇3：随机取出数据库中的若干条记录的方法
SELECT TOP n * FROM table_name ORDER BY NEWID()
top n，n就是要取出的记录数
篇4：ERP系统中与BOM有关的常用方法数据库教程
erp
一: BOM展开(按任一父结点展开到最底层)
以下写一个简单的，视具体要求稍做修改即可，
create table 表(levelid int,levelname char(2),parent int)
insert 表 select 1, 'AA' , 0
union all select 2 , 'BB' , 1
union all select 3 , 'CC' , 1
union all select 4 , 'DD' , 2
union all select 5 , 'EE' , 3
union all select 6 , 'FF', 5
create function bom (@name char(2))
returns @tb table (levelid int,levelname char(2),parent int)
as
begin
insert @tb select levelID,LevelName,parent from 表 where Levelname = @name
while @@rowcount >0
insert @tb select levelID,LevelName,parent from 表
where parent in (select levelID from @tb)
and levelID not in (select levelID from @tb)
return
end
select * from dbo.bom('bb')
levelid levelname parent
----------- --------- -----------
2 BB 1
4 DD 2
（所影响的行数为 2 行）
二: LLC(最低层码)
1：物料主文件中至少有这两个字段
itemNo,llc
2：BOM中至少有这两个字段 (树状)
parentItem,itemNo
3:
Create Procedure LLC
As
Update 物料主文件 set llc = 0 --先将LLC全部清为0
Declare @i tinyint
Set @i = 0
While @i <= 10 -- 假设BOM最多只有10层
Begin
Update a Set a.llc = @i + 1 --子结点的LLC加1
From 物料主文件 a
Join bom b on a.itemNo = b.itemNo
Join 物料主文件 c on c.itemNo = b.parentItem
where c.llc = @i
Set @i = @i + 1
End
/*********** Usage: Exec LLC *******/
篇5：中英文字符混合处理方法数据库教程
1,TXT文件导入SQL时
303410001401??????? 600 LANG 4-T粤IG? 0220??? 011840628900000?? BPO
303410001501??????? 600 LANDAU中ER? 0220??? 011840628900000?? BPO
303410001601??????? 600 LANG 6-T汉IG? 0220??? 011840628900000?? BPO
有此一TXT文件要导入SQL，有固定数据格式，因无明显界定符，将其导成一列，再在SQL中截取分离，存在以下问题：
数据结构给定的长度是单字节长度，但在SQL中使用substring一个汉字只算一位，在些要求中因汉字是无固定位置，如只算一位将影响后面数据正确性
解决方法：cast(substring(cast(col001 as varbinary(1000)),39,4) as char(18)) as time_id
就是先将字段转成varbinary类型，这样汉字也算2位，截取就满足了固定格式要求了，然后再转回字符型，
中英文字符混合处理方法数据库教程
，
2,SQL表导出至TXT中
使用cast(????? as char(20))限定
3,取长度
len()汉字只算1位
使用datalength()汉字算2位
篇6：PL/SQL中的几种异常处理方法数据库教程
异常处理
这是Pona的文章，我斗胆将其贴上来，Pona不要介意哦！^_^
PL/SQL里，有三种方法可以在处理大批量数据时不会因为一条或几条数据错误而导致异常中止程序，
1、用Fetch into a cursor%TYPE把要处理的数据放到记录集里。当一条数据不符条件时，用标签<
-------------------------------------------------------------------------------
-- Function Name : CalculateImportCharge
-- Function Desc : Calculate Import Charge
-- Created by : Author
-- Created Date : -05-16
-------------------------------------------------------------------------------
FUNCTION CalculateImportCharge (
p_i_job_id IN VARCHAR2,
p_i_as_of_date_id IN VARCHAR2) RETURN NUMBER
AS
CURSOR cur_ShipBlHeader IS
SELECT import_folder_no
FROM GMY_SHIP_BL_HEADER
WHERE CANCEL_FLG = GMY_GA000_PKG.BL_CANCEL_FLG_OFF;
rec_ShipBlHeader cur_ShipBlHeader%ROWTYPE;
BEGIN
OPEN cur_ShipBlHeader;
FETCH cur_ShipBlHeader INTO rec_ShipBlHeader;
WHILE cur_ShipBlHeader%FOUND LOOP
x_num_error_code := GMY_GA000_PKG.CheckValidMasterBlNo (
p_i_job_id,
p_i_as_of_date_id,
rec_ShipBlHeader.import_folder_no,
x_vch_message);
IF x_num_error_code
IN (GMY_GA000_PKG.gn#NG, GMY_GA000_PKG.INVALID_BL_NO) THEN
x_vch_message :=
p_i_job_id
|| ' WARNING: Function CheckValidMasterBlNo @'
|| ' Import folder '
|| rec_ShipBlHeader.import_folder_no
|| ' - Invalid BL No.';
COM_LOG.PUTLINE (p_i_job_id, x_vch_message);
GOTO NEXT_RECORD;
END IF;
x_num_error_code := CheckExistsOfAccDate (
p_i_job_id,
p_i_as_of_date_id,
rec_ShipBlHeader.import_folder_no);
IF x_num_error_code = GMY_GA000_PKG.gn#NG THEN
GOTO NEXT_RECORD;
END IF;
COMMIT;
<
FETCH cur_ShipBlHeader INTO rec_ShipBlHeader;
END LOOP;
CLOSE cur_ShipBlHeader;
RETURN GMY_GA000_PKG.gn#OK;
EXCEPTION
WHEN OTHERS THEN
x_vch_message :=
p_i_job_id
|| ' ERROR: Function CalculateImportCharge @ '
|| SUBSTR (SQLERRM (SQLCODE), 1, 100);
COM_LOG.PUTLINE (p_i_job_id, x_vch_message);
RETURN GMY_GA000_PKG.gn#NG;
END CalculateImportCharge;
2、当使用the Cursor FOR Loop循环时，在Loop循环里，把会出问题的情况写进一个独立的block块中，这个块包括完整的begin、end部分及exception异常处理部分。这样即使一条数据出现异常，也会继续执行下一条。
-------------------------------------------------------------------------------
-- Function Name : GenerateInsCostInfRec
-- Function Desc : Generate records to transmit in INF table
-- Created by : SISS(AP)
-- Created Date : 2003-03-26
-- ----------------------------------------------------------------------------
FUNCTION GenerateInsCostInfRec (
p_i_job_id IN VARCHAR2,
p_i_as_of_date_id IN VARCHAR2) RETURN NUMBER
AS
CURSOR cur_cost IS
SELECT cost.ROWID costRowId,
cost.import_folder_no,,
cost.insur_trans_id
FROM GMY_COST_BL cost,
GMY_COMMON_MST mst
WHERE cost.import_folder_no=invheader.import_folder_no
AND cost.billing_amt_num IS NOT NULL
AND cost.billing_amt_num!=0
AND cost.insur_db_cr!=0;
BEGIN
FOR rec_cost IN cur_cost LOOP
BEGIN
x_num_ret_value := GMY_GA000_PKG.CheckValidMasterBlNo(
p_i_job_id,
p_i_as_of_date_id,
rec_cost.import_folder_no,
x_vch_error_msg);
IF x_num_ret_value = GMY_GA000_PKG.VALID_BL_NO THEN
INSERT INTO GMY_COST_INS_INF(
cost_trx_id,,
created_by,
program_name)
VALUES(
GMY_COST_INS_INF_S.NEXTVAL,
PRG_NAME,
PRG_NAME);
ELSIF x_num_ret_value = GMY_GA000_PKG.INVALID_BL_NO THEN
x_vch_error_msg := p_i_job_id
|| ' Import folder '
|| rec_cost.import_folder_no
|| ' has repeated BL No. with other import folder.'
|| ' Failed in insurance cost transmission.';
COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg);
END IF;
EXCEPTION
WHEN OTHERS THEN
IF SQL%ROWCOUNT >0 THEN -- check for 'too many rows'
x_vch_error_msg := p_i_job_id||' '||
SUBSTR(SQLERRM(SQLCODE),1,100);
COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg);
ELSE
x_vch_error_msg := p_i_job_id||' '||
SUBSTR(SQLERRM(SQLCODE),1,100);
COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg);
END IF;
END;
END LOOP;
COMMIT;
RETURN GMY_GA000_PKG.gn#OK;
EXCEPTION
WHEN OTHERS THEN
x_vch_error_msg := p_i_job_id||' '||SUBSTR(SQLERRM(SQLCODE),1,100);
COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg);
ROLLBACK;
RETURN GMY_GA000_PKG.gn#NG;
END GenerateInsCostInfRec;
3、当使用the Cursor FOR Loop循环时，在Loop循环里，把会出问题的情况拆分成子函数，分别处理，
----------------------------------------------------------------------------
-- Function Name : CopyDsToActualDs
-- Function Desc : Copy the records from DS DB to Actual DS DB.
-- Created by : Author
-- Created Date : 2003-02-20
----------------------------------------------------------------------------
FUNCTION CopyDsToActualDs (
p_i_job_id IN VARCHAR2,
p_i_as_of_date_id IN VARCHAR2) RETURN NUMBER
IS
CURSOR cur_DsScc IS
SELECT *
FROM GMY_DS_SCC;
BEGIN
FOR rec_DsHead IN cur_DsScc LOOP
x_num_error_code := InsToActualScc(
p_i_job_id,
p_i_as_of_date_id,
rec_DsHead.order_by_code,
rec_DsHead.po_code,
rec_DsHead.wh);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
x_vch_error_msg := p_i_job_id
||' Function Name: CopyDsToActualDs';
COM_LOG.PUTLINE(p_i_job_id,x_vch_error_msg);
x_vch_error_msg:=p_i_job_id||' '||SUBSTR(SQLERRM(SQLCODE),1,100);
COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg);
ROLLBACK;
RETURN GMY_GA000_PKG.gn#NG;
END CopyDsToActualDs;
----------------------------------------------------------------------------
-- Function Name : InsToActualScc
-- Function Desc : Deal with insert section.
-- Created by : Author
-- Created Date : 2003-03-13
----------------------------------------------------------------------------
FUNCTION InsToActualScc(
p_i_job_id IN VARCHAR2,
p_i_as_of_date_id IN VARCHAR2,
p_i_order_by_code IN VARCHAR2,
p_i_po_code IN VARCHAR2,
p_i_wh IN VARCHAR2
) RETURN NUMBER
IS
x_vch_error_msg VARCHAR2(255);
BEGIN
INSERT INTO GMY_ACTUAL_DS_SCC(
order_by_code,
po_code,
wh )
VALUES( p_i_order_by_code,
p_i_po_code,
p_i_wh);
COMMIT;
RETURN GMY_GA000_PKG.gn#OK;
EXCEPTION
WHEN OTHERS THEN
x_vch_error_msg := p_i_job_id||' Function Name: InsToActualScc';
COM_LOG.PUTLINE(p_i_job_id,x_vch_error_msg);
x_vch_error_msg := p_i_job_id
||' The key of the record that failed to insert is: ';
COM_LOG.PUTLINE(p_i_job_id,x_vch_error_msg);
ROLLBACK;
RETURN GMY_GA000_PKG.gn#NG;
END InsToActualScc;
篇7：9I中如何修改Ddid和Dbname方法测试小结数据库教程
测试环境：Windows2000 + Oracle 9.2.0.1
具体操作如下：
一、NID工具使用：（oracle工具，数据库安装完成后，就自动安装了），具体用法如看;
C:\>nid
DBNEWID: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
Keyword Description (Default)
----------------------------------------------------
TARGET Username/Password (NONE)
DBNAME New database name (NONE)
LOGFILE Output Log (NONE)
REVERT Revert failed change NO
SETNAME Set a new database name only NO
APPEND Append to output log NO
HELP Displays these messages NO
用法举例：
Changing Only the DBID
The following example connects with operating system authentication and changes only the DBID:
% nid TARGET=/
Changing the DBID and Database Name
The following example connects as user SYS and changes the DBID and also changes the database name to test2:
% nid TARGET=SYS/oracle@test1 DBNAME=test2
Changing Only the Database Name
The following example connects as user SYSTEM and changes only the database name, and also specifies a log file for the output:
% nid TARGET=SYSTEM/manager@test2 DBNAME=test3 SETNAME=YES LOGFILE=dbid.out
二、修改步骤如下:
1)、SQL>conn sys/admin@test as sysdba
Connected.
SQL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>startup mount
ORACLE instance started.
Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL>host nid target=sys/admin@test dbname=testdb
DBNEWID: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
Connected to database TEST (DBID=1835076349)
Control Files in database:
C:\ORACLE\ORADATA\TEST\CONTROL01.CTL
C:\ORACLE\ORADATA\TEST\CONTROL02.CTL
C:\ORACLE\ORADATA\TEST\CONTROL03.CTL
Change database ID and database name TEST to TESTDB? (Y/[N]) =>y
Proceeding with operation
Changing database ID from 1835076349 to 2321050327
Changing database name from TEST to TESTDB
Control File C:\ORACLE\ORADATA\TEST\CONTROL01.CTL - modified
Control File C:\ORACLE\ORADATA\TEST\CONTROL02.CTL - modified
Control File C:\ORACLE\ORADATA\TEST\CONTROL03.CTL - modified
Datafile C:\ORACLE\ORADATA\TEST YSTEM01.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF - dbid changed, wrote new nam
Datafile C:\ORACLE\ORADATA\TEST\INDX01.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\TEST\TOOLS01.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\TEST\USERS01.DBF - dbid changed, wrote new name
Datafile D:\DATAFILE\PORMALS_SPA.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\TEST\OEM_REPOSITORY.DBF - dbid changed, wrote ne
name
Datafile D:\DATAFILE\PORMALS_SPA_01.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\TEST\TEMP01.DBF - dbid changed, wrote new name
Control File C:\ORACLE\ORADATA\TEST\CONTROL01.CTL - dbid changed, wrote new
name
Control File C:\ORACLE\ORADATA\TEST\CONTROL02.CTL - dbid changed, wrote new
name
Control File C:\ORACLE\ORADATA\TEST\CONTROL03.CTL - dbid changed, wrote new
name
Database name changed to TESTDB.
Modify parameter file and generate a new password file before restarting.
Database ID for database TESTDB changed to 2321050327.
All previous backups and archived redo logs for this database are unusable.
Shut down database and open with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
2）立即关闭数据库，修改db_name参数
SQL>shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
在spfile文件里作相应修改，数据库db_name
SQL>startup mount;
SQL>alter system set db_name=testdb scope=spfile;
System altered.
注：如果是pfile文件，需手工修改db_name参数值
3）重新创建密码文件
SQL>host orapwd file=c:\oracle\ora92\database\pwwdtestdb.ora password=admin entries=8
SQL>conn sys/admin@test as sysdba
Connected to an idle instance.
4）、以Resetlogs选项打开数据库
SQL>startup mount
ORACLE 例程已经启动，
9I中如何修改Ddid和Dbname方法测试小结数据库教程
，
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
SQL>alter database open resetlogs;
数据库已更改。
5）、查看修改是构成功
SQL>select dbid,name from v$database;
DBID NAME
---------- ---------
2321050327 TESTDB
SQL>
注意：2、3不能颠倒，否则，重建口令文件是不可用的
参考文档：download-west.oracle.com/ ... 52/ch14.htm#1004918
