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;