序列:
create sequence my_suquence --序列名称
minvalue 0 --序列最小值
start with 0 --序列起始值
maxvalue 20 --序列最大值
increment by 5 --序列的增长值
nocache --不预先在内存中缓存
cycle; --达到最大值后再重新循环
--删除序列
drop my_suquence;
--调用序列
my_suquence.nextval --下一个值
my_suquence.currval --当前值
create or replace procedure
insert_student(
v_student_id varchar2,
v_student_name varchar2,
v_college_major varchar2,
v_status varchar2,
v_state varchar2,
v_license_no varchar2
)
is
check_constraint_violation exception;
pragma exception_init(check_constraint_violation,-2290);
begin
insert into students1 values(
v_student_id ,
v_student_name ,
v_college_major ,
v_status ,
v_state ,
v_license_no
);
dbms_output.put_line('insert complete');
exception
when dup_val_on_index then
dbms_output.put_line('违反唯一性约束.');
when check_constraint_violation then
dbms_output.put_line('检查性约束。');
end;
exec insert_student()
--带一个入参的存储过程
create or replace procedure update_info
(xm in char)
as
begin
select zxf into xf from xs where xm=xm;
if xf>60 then
update xs set bz='三好学生' where xm=xm;
end if;
if xf<35 then
update xs set bz='学分未修满' where xm=xm;
end if;
end update_info;
exec update_info;
--带一个入参和出参的存储过程
CREATE OR REPLACE PROCEDURE count_num
( sex IN char,
num OUT number )
AS
BEGIN
IF sex=’男’ THEN
SELECT COUNT(XB) INTO num
FROM XS
WHERE XB=’男’;
ELSE
SELECT COUNT(XB) INTO num
FROM XS
WHERE XB=’女’;
END IF;
END count_num;
在调用过程count_num时,需要先定义OUT类型参数,调用如下:
DECLARE
man_num NUMBER;
BEGIN
count_num(‘男’,man_num);
END;