gaussdb 基础管理数据库表用户模式权限存储过程

最编程 2024-10-17 18:03:02

...

#创建表插入数据

CREATE TABLE employee (id varchar(100),name varchar(100),salary int);

CREATE TABLE ep_grade (id varchar(100),grade varchar(100),flag int);

Insert into employee values(1,'张三',5000);

Insert into employee values(2,'李四',5000);

Insert into employee values(3,'王五',5000);

Insert into employee values(4,'赵六',5000);

Insert into employee values(5,'太一',5000);

Insert into employee values(6,'太二',5000);

Insert into employee values(7,'太三',5000);

Insert into employee values(8,'太四',5000);

Insert into ep_grade values(1,'A',2022);

Insert into ep_grade values(2,'S',2021);

Insert into ep_grade values(3,'B',2021);

Insert into ep_grade values(4,'B',2021);

Insert into ep_grade values(5,'A',2021);

Insert into ep_grade values(6,'C',2022);

Insert into ep_grade values(7,'C',2021);

Insert into ep_grade values(8,'B',2022);

#创建存储过程

CREATE OR REPLACE PROCEDURE proc_emp()

DECLARE

EP_ID VARCHAR(100);

GRADE VARCHAR(10);

SALARY INT;

CURSOR C1 IS select distinct id,grade FROM ep_grade where Flag = 2021;

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO EP_ID,GRADE;

EXIT WHEN C1%NOTFOUND;

Case GRADE

when 'S'

then update employee set salary = salary+1000 where id=EP_ID ;

when 'A'

then update employee set salary = salary+500 where id=EP_ID ;

when 'B'

then update employee set salary = salary+100 where id=EP_ID;

when 'C'

then update employee set salary = salary-200 where id=EP_ID;

END Case;

DBE_OUTPUT.PRINT_LINE('ID: '||EP_ID||', Grade: '||GRADE||', updated.');

END LOOP;

CLOSE C1;

END;

#调用存储过程

call proc_emp();

上一篇：传输层协议 UDP 解说

下一篇：前端拖放知识

gaussdb 基础管理 数据库 表 用户 模式 权限 存储过程