达梦数据库笔记

--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;