Oracle数据库存储过程的创建
1.触发器语法
create [or replace] tigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
pl/sql语句
end
实例:创建自增长字段
create or replace trigger test_id
before insert on another --before:执行DML等操作之前触发
for each row --行级触发器
begin
select testsq.nextval into :new.epl_id from dual;
end;
2.存储过程语法
CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
END 存储过程名字
实例:
create or replace procedure up_another(v_empid in number)
as
v_large number;
begin
v_large:=2;
update another set salary=v_large*salary where epl_id=v_empid;
end up_another;
调用:
begin
up_another(103);
end up_nother;
3.创建自增序列
create sequence testsq
increment by 1
start with 1
maxvalue 7
cycle
nocache
4.存储函数
–查询某个员工的年收入
create or replace function queryempincome(eno in number)
return number
as
--月薪和奖金
psal emp.sal%type;
pcomm emp.comm%type;
begin
select sal,comm into psal,pcomm from emp where empno=eno;
--返回年收入
return psal*12+nvl(pcomm,0);
end;
调用:
declare
sal_yeats number(9,2);
begin
sal_yeats := queryempincome(7369);
dbms_output.put_line(sal_yeats);
end;