%ROWTYPE Attribute The %ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. Fields in a record and corresponding columns in a row have the same names and datatypes.You can use the %ROWTYPE attribute in variable declarations as a datatype specifier. Variables declared using %ROWTYPE are treated like those declared using a datatype name. For more information, see "Using %ROWTYPE".%TYPE Attribute The %TYPE attribute provides the datatype of a field, record, nested table, database column, or variable. You can use the %TYPE attribute as a datatype specifier when declaring constants, variables, fields, and parameters. For more information, see "Using %TYPE".
1,参数%rowtype指记录 %type是指单个变量类型。 2,参数%notfound指游标没有找到数据和%found是指游标找到数据了! 我贴一段代码给你看:unction FUN_CKDReceive (sYMD IN VARCHAR2, ------------6位的“对象年月” , 格式:"YYYYMM" ,例如 "200404" sUser IN VARCHAR2, ------------任意位数的“用户名” sComp IN VARCHAR2, ------------任意位数的“计算机名” sMsg OUT VARCHAR2) ------------任意位数的“返回消息代码” RETURN CHAR ------------1位的字符返回值
IS ------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------Function 变量定义和cursor定义------------------------------------------- ------------------------------------------------------------------------------------------------------------------------- exchgRate 人民币汇率.汇率%TYPE; r_CKDTR CKD配船情报TR表%ROWTYPE;
TYPE rec_CKDBoxInfo IS RECORD ( boatID CKD铁箱情报.配船号%TYPE, invoiceID CKD铁箱情报.发票号%TYPE, boxID CKD铁箱情报.铁箱号%TYPE, containerID CKD铁箱情报.集装箱号%TYPE ); temp_recBoxInfo rec_CKDBoxInfo; r_BoatDetail CKD铁箱别部品明细%ROWTYPE; TYPE rec_CKDinInfo IS RECORD ( boatID CKD入库情报.配船号%TYPE, invoiceID CKD入库情报.发票号%TYPE ); temp_CKDinInfo rec_CKDinInfo;
-------------------------------------------------【检索1】cursor定义---------------------------------------------------- cursor c_exchgRate is select 汇率 from 人民币汇率 where 年度=SUBSTR(sYMD,1,4) and 月度=DECODE(SUBSTR(sYMD,5,1),0,SUBSTR(sYMD,6,1),1,SUBSTR(sYMD,5,2))and 汇率区分 ='1' ;。 。 。
open c_exchgRate; if c_exchgRate%notfound then sMsg:='IC01'; RETURN '1'; END IF; -------------------------------------------清空财务系统"CKD配船情报TR表"------------------------------------------------- delete from CKD配船情报TR表; -------------------------------------------------【检索2】实现----------------------------------------------------------- open c_CKDTR(sYMD); if c_CKDTR%notfound then sMsg:='IC02'; RETURN '1'; end if; fetch c_exchgRate into exchgRate; ---------------------------------按【检索2】得到中记录为单位,进行以下处理并提交事务------------------------------------- fetch c_CKDTR into r_CKDTR; while c_CKDTR%found loop ----------------------------------每单位记录处理开始,只提交或回滚与本条记录相关的事务----------------------------------- ----------------------------------作成财务系统的“CKD配船情报TR表”数据。 【插入1】-------------------------------------- insert into CKD配船情报TR表 ( 配船号, 。 。 。
---参数%rowtype 为表的一行,类型与表一致。
---%type为一个字段,所定义的类型与表中的字段类型一致2,参数%notfound和%found是指什么?有什么区别?
---参数%notfound为update,select等时有没找到3,sqlerrm()是一个什么函数?
---出错时oracle会自动产生出错信息,可以根据这个来判断错在那里??
%type为一个自适应的变量类型,这个我懂了
但是%rowtype为一个表的一行???类型和表一致?
数据库表的一行?但是表的行是各种不同类型的数据的集合啊
是不是它就像一个数组一样,用来存放数据结构和某一个表某一行结构
一样的数据?
还有一个问题,我说来惭愧,我来csdn很久了,但是一直没有在意如何
给你们加分,我找了很多次都没有找到。。
好歹咱也是写jsp和servlet出身的,对web敏感,但是
我真的没有找到在哪里给你们加分告诉我吧
sqlerrm()是一个什么函数?
---出错时oracle会自动产生出错信息,可以根据这个来判断错在那里;他的参数是发生错误的错误号,如果省略,则即当前产生的错误号的值;
3.sqlerrm()是一个什么函数?
---sqlerrm不是函数, 是Pro*C编程中用于SQL错误诊断的SQLCA结构中的成员,又包含sqlerrml(错误消息长度)和sqlerrmc(错误消息)
The %ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. Fields in a record and corresponding columns in a row have the same names and datatypes.You can use the %ROWTYPE attribute in variable declarations as a datatype specifier. Variables declared using %ROWTYPE are treated like those declared using a datatype name. For more information, see "Using %ROWTYPE".%TYPE Attribute
The %TYPE attribute provides the datatype of a field, record, nested table, database column, or variable. You can use the %TYPE attribute as a datatype specifier when declaring constants, variables, fields, and parameters. For more information, see "Using %TYPE".
2,参数%notfound指游标没有找到数据和%found是指游标找到数据了!
我贴一段代码给你看:unction FUN_CKDReceive
(sYMD IN VARCHAR2, ------------6位的“对象年月” , 格式:"YYYYMM" ,例如 "200404"
sUser IN VARCHAR2, ------------任意位数的“用户名”
sComp IN VARCHAR2, ------------任意位数的“计算机名”
sMsg OUT VARCHAR2) ------------任意位数的“返回消息代码”
RETURN CHAR ------------1位的字符返回值
IS
-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------Function 变量定义和cursor定义-------------------------------------------
------------------------------------------------------------------------------------------------------------------------- exchgRate 人民币汇率.汇率%TYPE;
r_CKDTR CKD配船情报TR表%ROWTYPE;
boatID CKD配船情报TR表.配船号%TYPE;
invoiceID CKD配船情报TR表.发票号%TYPE;
r_CKDinDetail CKD入库部品明细%ROWTYPE;
TYPE rec_CKDBoxInfo IS RECORD
( boatID CKD铁箱情报.配船号%TYPE,
invoiceID CKD铁箱情报.发票号%TYPE,
boxID CKD铁箱情报.铁箱号%TYPE,
containerID CKD铁箱情报.集装箱号%TYPE );
temp_recBoxInfo rec_CKDBoxInfo;
r_BoatDetail CKD铁箱别部品明细%ROWTYPE; TYPE rec_CKDinInfo IS RECORD
( boatID CKD入库情报.配船号%TYPE,
invoiceID CKD入库情报.发票号%TYPE );
temp_CKDinInfo rec_CKDinInfo;
-------------------------------------------------【检索1】cursor定义----------------------------------------------------
cursor c_exchgRate is
select 汇率
from 人民币汇率
where 年度=SUBSTR(sYMD,1,4) and
月度=DECODE(SUBSTR(sYMD,5,1),0,SUBSTR(sYMD,6,1),1,SUBSTR(sYMD,5,2))and
汇率区分 ='1' ;。
。
。
if c_exchgRate%notfound then
sMsg:='IC01';
RETURN '1';
END IF;
-------------------------------------------清空财务系统"CKD配船情报TR表"-------------------------------------------------
delete from CKD配船情报TR表;
-------------------------------------------------【检索2】实现-----------------------------------------------------------
open c_CKDTR(sYMD);
if c_CKDTR%notfound then
sMsg:='IC02';
RETURN '1';
end if;
fetch c_exchgRate into exchgRate;
---------------------------------按【检索2】得到中记录为单位,进行以下处理并提交事务-------------------------------------
fetch c_CKDTR into r_CKDTR;
while c_CKDTR%found loop
----------------------------------每单位记录处理开始,只提交或回滚与本条记录相关的事务-----------------------------------
----------------------------------作成财务系统的“CKD配船情报TR表”数据。 【插入1】--------------------------------------
insert into CKD配船情报TR表
( 配船号,
。
。
。