oracle 自增长、触发器、函数

oracle 跟mysql 实现 主键自增长的方式不一样

实现自增

create table student(id number,name VARCHAR2(32),primary key(id));

select * from student;

create sequence student_seq minvalue 1 nomaxvalue
increment by 1 start with 1 nocache;

select student_seq.nextval from dual;

create or replace trigger student_trigger_insertid
before insert on student for each row
begin
	select student_seq.nextval into:new.id from dual;
end;


insert into student(name) values('zhangsan');

通过触发器 将序列的自增插入到表中 实现表的自增

触发器

create or replace trigger tr_stu
 before delete on student
 begin
	if user != 'LIHUA' then
			raise_application_error(-20001,'权限不足，只能是LIHUA用户');
	end if;
	end;
	
	select * from student;
	
	delete from student where id=2;

上面的触发器的意思是 LIHUA 用户才能对表student有删除权限

切换用户LIHUA
执行语句成功

delete from student where id=2;

函数

	create or replace function test_function return number as
	begin
		declare total_count number;
		begin
			select count(*) into total_count from student ;
			return total_count;
		end;
	end;
		
	
select test_function() as num from dual;

函数传参
切换到scott

	create or replace function emp_function(ss number) return VARCHAR2 as
	begin
		declare emp_count number;
		begin
			select count(*) into emp_count from SCOTT.emp where sal>= +ss;
			if emp_count>0 then
			dbms_output.put_line('有'|| emp_count || '名员工薪资大于'|| ss);
			return '有'|| emp_count || '名员工薪资大于'|| ss;
			else if emp_count<=0 then
			dbms_output.put_line('没有员工薪资大于' || ss);
			return '没有员工薪资大于' || ss;
			end if;
			end if;
		end;
	end;
	
	select emp_function(2000) from dual;