oracle用append添加存储过程,oracle存储过程写法(一)

Create or Replace Procedure Proc_AppendRecords

(

nAppendCount int -- 要添加的记录条数

)

as

begin

declare

nstart int;

nend int;

begin

select NVL(max(UserID),0) into nstart from T_Test_Checkbill;

nend := nstart + nAppendCount;

while nstart < nend loop

nstart := nstart + 1;

insert into T_Test_Checkbill values(Seq_testCheckbill.nextval, 'User'||nstart, 'UPwd'||nstart,

mod(nstart*73, 100), sysdate, 'Note'||nstart);

end loop;

end;

end;

其中NVL说明如下：

Syntax

Purpose

NVL lets you replace null (returned as a blank) with a string in the results of a query. Ifexpr1 is null, thenNVL returnsexpr2. Ifexpr1 is not null, thenNVL returnsexpr1.

The arguments expr1 and expr2 can have any datatype. If their datatypes are different, then:

If expr1 is character data, then Oracle Database convertsexpr2 to the datatype ofexpr1 before comparing them and returnsVARCHAR2 in the character set ofexpr1.

If expr1 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

See Also: Table 2-11, "Implicit Type Conversion Matrix" for more information on implicit conversion and"Numeric Precedence " for information on numeric precedence

Examples

The following example returns a list of employee names and commissions, substituting "Not Applicable" if the employee receives no commission:

SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable')

"COMMISSION" FROM employees

WHERE last_name LIKE 'B%'

ORDER BY last_name;