oracle的存储过程在语法上和sql server的有很大不同,下面是一个针对oracle的存储过程实例,该存储过程向数据库表插入大量数据,包括用户角色随机数生成。
数据库表(userInfo)DDL描述如下:
CREATE
TABLE
USERINFO
(
USERCODE VARCHAR2 ( 50 BYTE) NOT NULL ,
USERNAME VARCHAR2 ( 50 BYTE),
ROLETYPE VARCHAR2 ( 50 BYTE)
)
(
USERCODE VARCHAR2 ( 50 BYTE) NOT NULL ,
USERNAME VARCHAR2 ( 50 BYTE),
ROLETYPE VARCHAR2 ( 50 BYTE)
)
存储过程SQL语句如下:
CREATE
OR
REPLACE
PROCEDURE
add_UserInfo
as
V_userCode VARCHAR2 ( 30 ): = '' ;
V_userName VARCHAR2 ( 30 ): = ' qs name ' ;
V_userCode_base VARCHAR2 ( 30 ): = ' qs_ ' ;
V_count INTEGER : = 100000 ;
V_index INTEGER : = 10000 ;
V_rand INTEGER : = 0 ;
BEGIN
WHILE V_index <= V_count LOOP
V_userCode: = CONCAT(V_userCode_base,V_index);
select round (dbms_random.value( 0 , 5 ), 0 ) into V_rand from dual;
insert into userInfo (userCode,roleType,userName)
Values (V_userCode,V_rand,V_userName);
V_index: = V_index + 1 ;
END LOOP;
END add_UserInfo;
/
V_userCode VARCHAR2 ( 30 ): = '' ;
V_userName VARCHAR2 ( 30 ): = ' qs name ' ;
V_userCode_base VARCHAR2 ( 30 ): = ' qs_ ' ;
V_count INTEGER : = 100000 ;
V_index INTEGER : = 10000 ;
V_rand INTEGER : = 0 ;
BEGIN
WHILE V_index <= V_count LOOP
V_userCode: = CONCAT(V_userCode_base,V_index);
select round (dbms_random.value( 0 , 5 ), 0 ) into V_rand from dual;
insert into userInfo (userCode,roleType,userName)
Values (V_userCode,V_rand,V_userName);
V_index: = V_index + 1 ;
END LOOP;
END add_UserInfo;
/