创建一个存储过程,实现向表students_basicinformation插入一个新同学信息

来源:百度知道 编辑:UC知道 时间:2024/05/04 12:14:24
考试题

①首先建表:
create table students_basicinformation
(
id char(2) not null,
name varchar2(8) not null,
sex char(2) not null
)
②创建存储过程:
CREATE OR REPLACE PROCEDURE procStudentInformationInsert(user_id IN CHAR,
user_id IN VARCHAR2,
user_sex IN CHAR,
user_count OUT NUMBER) IS

BEGIN

--查询用户是否存在
SELECT COUNT(1) user_count
FROM students_basicinformation
WHERE id = user_id;

--如果不存在,新增一条记录
IF user_count = 0 THEN
INSERT INTO students_basicinformation
(id, name, sex)
VALUES
(user_id, user_id, user_sex);
END IF;

END procStudentInformationInsert;