带参数的存储过程

生成随机数的一个存储过程  下面直接调用这'ccic_handleid.Nextval'随机数
create sequence ccic_handleid
minvalue 1
maxvalue 999999999999999999999999999
start with 1064
increment by 1
cache 20;

 

添加座席存储过程
create or replace procedure testb(p_id varchar2,p_name varchar2,p_pos varchar2,p_comcode varchar2,p_comcodeid varchar2,p_province varchar2) as
v_comcode varchar2(40);
v_MAKECOM varchar2(40);
begin

EXECUTE IMMEDIATE 'select comcode from cc_Company where comcname like ''%'||p_pos||'%''' into v_comcode;

//将查出来的comcode  赋给v_comcode
EXECUTE IMMEDIATE 'select provincecode from crm_policyarea  where provincename like ''%'||p_province||'%''' into v_MAKECOM;
INSERT INTO Cc_User (UserCode,UserName,UserEName,Password,
PasswordSetDate,PasswordExpireDate,ComCode,makecom,accountcode,Phone,Mobile,Address,
PostCode,Email,duty,LoginSystem,Newusercode,ValidStatus,Articlecode,Sex,Birthday,Marry,Nation,
EnterTime,Idcard,SchoolLevel,Healthiness,Reserve1,Reserve2,Reserve3,Reserve4,
AgentId,Nowaddress,Flag,userFlag,Userloginname,Extension,Login_Id,User_Ip)
VALUES(p_id,p_name,'','4A7D1ED414474E4033AC29CCB8653D9B',to_date('2008-01-01 00:00:00','yyyy-mm-dd hh24":"mi":"ss'),to_date('2099-01-01','yyyy-mm-dd'),v_comcode,v_MAKECOM,'','','','','','','','3',
'','1','','','','','','','','','','0','','','',p_id,'','','OB',p_id,'','','');
INSERT INTO Cc_UserGrade (ComCode,UserCode,GradeCode,InvalidDate,ValidStatus,Remark,Flag) VALUES('00000000',p_id,'501','','1','','');
INSERT INTO Cc_CompanyUser (UserCode,ComCode,Flag,Reserve1,Reserve2,Reserve3,Reserve4 ) VALUES(p_id,v_comcode,'1','','','','');
insert into ccic_handle(id,agentid,handlecode,operatorcode) values (ccic_handleid.Nextval,p_id,p_comcode,p_comcodeid);

end testb;