--1.存储过程
create or replace procedure 模式名称.存储过程名称(参数名 in/out 参数的数据类型)
as
--声明
begin
--程序的语句
end;
--调用
call 模式名.存储过程名称;
--2. 存储函数
create or replace function 模式名.函数名
return 返回的类型
as
--声明
begin
--程序语句
end;
--调用
模式名.函数名();
--删除
drop function/procedure 模式名.函数名/存储过程名
-----------------------------------------
drop table dmhr.student;
create table dmhr.student(
id char(3) primary key,
name varchar(50),
sex char(2),
salary float(2)
);
insert into dmhr.student values
('001','a','男',100.0),
('002','b','女',200.0),
('003','c','女',300.0),
('004','d','男',400.0),
('005','e','男',500.00);
--添加触发器 触发器关键字 触发器名字
create or replace trigger dmhr.formatsex
--触发描述 触发语句 触发对象
before insert or update on dmhr.student
for each row --触发级别,每一行都检测
declare
--声明异常
e_sex exception for -20001;
begin
--sql语句
--当新增或者更新时,检查性别
if (inserting or updating) and :NEW.sex not in('男','女') then
--print('sex列只能为男或女');
raise e_sex;
end if;
end;
insert into dmhr.student values
('010','f','1');
create or replace trigger dmhr.salary
before update on dmhr.student
for each row
declare
--e_salary exception for -20001;
begin
if updating and :new.salary - :old.salary > :old.salary*0.1 then
PRINT(123);
--raise e_salary;
end if;
end;
update dmhr.student set salary=120.0 where id = '001';
-------------------------------------------------------------------
--创建一个视图
create or replace view dmhr.view_employee
as
select
D.department_id,
D.department_name,
E.department_id,
E.employee_id,
E.employee_name,
E.email,
E.job_id
from dmhr.department D,dmhr.employee E
where D.department_id = E.department_id;
--向视图插入数据
insert into dmhr.view_employee values
('20011','信创事业部','20011','20010011','张信创1','645@qq.com1',11);
SELECT * FROM DMHR.EMPLOYEE;
create or replace trigger dmhr.update_view_employee
instead of insert on dmhr.view_employee for each row
declare
begin
if inserting then
--拆解成更新组成视图的两张表 dmhr.department,dmhr.employee
insert into dmhr.department(department_id,department_name)
values (:new.department_id,:new.department_name,);
insert into dmhr.employee(employee_id,employee_name,email,job_id,hire_date)
values (:new.employee_id,:new.employee_name,:new.email,:new.job_id,now());
end if;
end;
commit;
select * from dmhr.view_employee where employee_name = '张信创';
select * from dmhr.employee where employee_name = '张信创1';
select * from dmhr.department where department_id = '20011';
--------------------------------------------------------------------------------------
--时间触发器
create table dmhr.log_object
(objectname varchar(50),
databasename varchar(50),
optuser varchar(50),
opttime datetime);
--触发器
create or replace trigger dmhr.tr_obj
before create or truncate or alter or drop on database
begin
insert into dmhr.log_object values
(:eventinfo.objectname,
:eventinfo.databasename,
:eventinfo.opuser,
:eventinfo.optime);
end;
select * from dmhr.log_object;
create schema test;