• 周六. 7 月 13th, 2024

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

存储过程获取主键序列号

admin

11 月 28, 2021

v_userId:=seq_user_id.nextval; –返回user_id
–插入用户注册信息表
INSERT INTO t_user_register_info (USER_ID,ACCOUNT,PASSWORD,USER_TYPE_ID,CREATE_TIME)
values(v_userId,v_phone,v_password,v_userType,sysdate);

seq1:=SEQ_ADD_FRIEND_ID.NEXTVAL;
INSERT INTO T_ADD_FRIEND (RECORD_ID,OPERATE_ID,OPERATE_TYPE,OBJECTIVE_ID,OBJECTIVE_TYPE,GROUP_ID,OPREATE_STATUS,OBJECTIVE_GROUP_ID,IS_DELETE,MODIFY_TIME)
VALUES(seq1,addUserId,1,v_userId,2,mpId,1,v_GROUP_ID,1,SYSDATE);

seq2:=SEQ_ADD_FRIEND_ID.NEXTVAL;

INSERT INTO T_ADD_FRIEND (RECORD_ID,OPERATE_ID,OPERATE_TYPE,OBJECTIVE_ID,OBJECTIVE_TYPE,GROUP_ID,OPREATE_STATUS,OBJECTIVE_GROUP_ID,IS_DELETE,MODIFY_TIME)
VALUES(seq2,v_userId,1,addUserId,2,v_GROUP_ID,1,mpId,1,SYSDATE);

–添加医生信息pc端
PROCEDURE T_ADDDOC_PC
(
v_userType IN NUMBER,
v_archId IN NUMBER,
v_birth IN DATE,
v_conTime IN NUMBER,
v_jianjie IN VARCHAR2,
v_touxiang IN VARCHAR2,
v_price IN NUMBER,
v_name IN VARCHAR2,
v_sex IN NUMBER,
v_password IN VARCHAR2,
v_jobid IN NUMBER,
v_hosId IN NUMBER,
v_depId IN NUMBER,
v_phone IN VARCHAR2,
v_role IN NUMBER,
v_email IN VARCHAR2,
v_err_code OUT NUMBER
)
IS
v_uCount NUMBER;
v_userId NUMBER;

BEGIN
–查询该用户是否存在
SELECT COUNT(*) INTO v_uCount FROM t_user_register_info u WHERE u.account=v_phone and u.user_type_id=1;
if v_uCount>0 then
v_err_code:=0;
else
–如果手机号码没有注册
v_userId:=seq_user_id.nextval; –返回user_id
–插入用户注册信息表
INSERT INTO t_user_register_info (USER_ID,ACCOUNT,PASSWORD,USER_TYPE_ID,CREATE_TIME)
values(v_userId,v_phone,v_password,v_userType,sysdate);
–插入医生用户信息表
INSERT INTO T_DOCTOR_INFO (DOCTOR_ID,HEAD_PORTRAIT,USER_NAME,MOBILE,EMAIL,SEX,BIRTHDAY,JOBTITLE_ID,INTRODUCE,HOSPITAL_ID,DEPT_ID,create_time,DOCTOR_TYPE)
values (v_userId,v_touxiang,v_name,v_phone,v_email,v_sex,v_birth,v_jobid,v_jianjie,v_hosId,v_depId,sysdate,v_role);
–添加默认好友分组

INSERT INTO T_FRIEND_GROUP_INFO
(GROUP_ID,GROUP_NAME,GROUP_TYPE,USER_ID,CREATE_TIME,LIMIT)
VALUES
(1,’哮喘’,v_userType,v_userId,SYSDATE,1);
INSERT INTO T_FRIEND_GROUP_INFO
(GROUP_ID,GROUP_NAME,GROUP_TYPE,USER_ID,CREATE_TIME,LIMIT)
VALUES
(2,’高血压’,v_userType,v_userId,SYSDATE,1);
INSERT INTO T_FRIEND_GROUP_INFO
(GROUP_ID,GROUP_NAME,GROUP_TYPE,USER_ID,CREATE_TIME,LIMIT)
VALUES
(3,’慢阻肺’,v_userType,v_userId,SYSDATE,1);
INSERT INTO T_FRIEND_GROUP_INFO
(GROUP_ID,GROUP_NAME,GROUP_TYPE,USER_ID,CREATE_TIME,LIMIT)
VALUES
(4,’糖尿病’,v_userType,v_userId,SYSDATE,1);
INSERT INTO T_FRIEND_GROUP_INFO
(GROUP_ID,GROUP_NAME,GROUP_TYPE,USER_ID,CREATE_TIME,LIMIT)
VALUES
(5,’慢性呼吸衰竭’,v_userType,v_userId,SYSDATE,1);
INSERT INTO T_FRIEND_GROUP_INFO
(GROUP_ID,GROUP_NAME,GROUP_TYPE,USER_ID,CREATE_TIME,LIMIT)
VALUES
(6,’ARDS’,v_userType,v_userId,SYSDATE,1);
INSERT INTO T_FRIEND_GROUP_INFO
(GROUP_ID,GROUP_NAME,GROUP_TYPE,USER_ID,CREATE_TIME,LIMIT)
VALUES
(7,’睡眠’,v_userType,v_userId,SYSDATE,1);
INSERT INTO T_FRIEND_GROUP_INFO
(GROUP_ID,GROUP_NAME,GROUP_TYPE,USER_ID,CREATE_TIME,LIMIT)
VALUES
(8,’肺结节’,v_userType,v_userId,SYSDATE,1);
INSERT INTO T_FRIEND_GROUP_INFO
(GROUP_ID,GROUP_NAME,GROUP_TYPE,USER_ID,CREATE_TIME,LIMIT)
VALUES
(9,’其它’,v_userType,v_userId,SYSDATE,1);
IF v_price>0 then
INSERT INTO T_EXPERT_PRICE_INFO (ID,EXPERT_ID,PRICE,TIME,CREATE_TIME,USER_TYPE)
values(SEQ_EXPERT_PRICE_INFO_ID.nextval,v_userId,v_price,v_conTime,sysdate,v_userType);
end if;
v_err_code:=1;
end if ;
COMMIT;
END T_ADDDOC_PC ;

发表回复