3、GaussDB数据库高级SQL

## **实验一：触发器审计日志**

```sql
-- 审计表
create table userlog(
    operation   varchar(10),
    userid      varchar(20),
    logdate     date
);

-- 函数
create or replace function log_user() returns trigger as $$
begin
    insert into userlog(operation, userid, logdate)
    values(TG_OP, NEW.userid, now());
    return NEW;
end;
$$ language plpgsql;

-- 触发器
create trigger user_trigger
after insert on users
for each row
execute procedure log_user();

-- 测试
insert into users(userid, username, password, email)
values('u123', '张三', '123456', 'zhangsan@example.com');

select * from userlog;
```

---

## **实验二：匿名块中的自治事务**

```sql
declare
    pragma autonomous_transaction;
begin
    insert into errorlog(error_date, error_message)
    values(sysdate, '测试错误信息');
    commit;
end;
```

---

## **实验三：Package 支持自治事务**

```sql
-- 包头
create or replace package student_pkg as
    procedure log_score(student_id varchar2, course varchar2, score number);
end student_pkg;
/

-- 包体
create or replace package body student_pkg as
    procedure log_score(student_id varchar2, course varchar2, score number) is
        pragma autonomous_transaction;
    begin
        insert into score_log(student_id, course, score, log_date)
        values(student_id, course, score, sysdate);
        commit;
    end;
end student_pkg;
/

-- 调用测试
begin
    student_pkg.log_score('2023001', '数据库', 95);
end;
/
```

---

## **实验四：窗口函数实现环比、同比**

```sql
select
    year,
    month,
    sales,
    lag(sales, 1) over (order by year, month) as last_month_sales,
    lag(sales, 12) over (order by year, month) as last_year_sales,
    (sales - lag(sales, 1) over (order by year, month)) / 
        lag(sales, 1) over (order by year, month) * 100 as mom_growth,
    (sales - lag(sales, 12) over (order by year, month)) / 
        lag(sales, 12) over (order by year, month) * 100 as yoy_growth
from sales_data;
```

FYF

导航1234

最近发表

友情链接