[技术交流] GaussDB 100 存储过程综合练习(一)

 

                                                                                存储过程综合练习


练习一;输入部门名称,显示内部成员的工号,姓名、基本工资及总薪资。要求员工按照字母顺序依次显示。

 

建表语句;create table test_sections(SECTION_ID BINARY_INTEGER  primary key auto_increment , SECTION_NAME VARCHAR(20 BYTE), SECTION_EMPLOYEE BINARY_INTEGER) ;
添加数据;insert into test_sections values(1,'开发部' ,0);
   insert into test_sections values(2,'维护部' ,0);   
   insert into test_sections values(3,'运营部' ,0);


部门表: 
----------------------------------- -------- ------------------------------------
SECTION_ID                          NOT NULL BINARY_INTEGER
SECTION_NAME                                 VARCHAR(20 BYTE)
SECTION_EMPLOYEE                             BINARY_INTEGER


建表语句;create table test_employee(EMPLOYEE_ID BINARY_INTEGER primary key auto_increment ,
SECTION_ID BINARY_INTEGER ,EMPLOYEE_NAME VARCHAR(20 BYTE) ,BASE_PAY BINARY_INTEGER ,EMPLOYEE_SALARY BINARY_INTEGER) ;


添加数据;insert into test_employee values(1,1,'张三',4000,5000);
   insert into test_employee values(2,2,'李四',3000,4100);
   insert into test_employee values(3,3,'王五',3500,4000);
   insert into test_employee values(4,1,'赵六',4020,5010)
   insert into test_employee values(5,1,'张小三',3080,4400)
   insert into test_employee values(6,1,'李小四',4000,5000)
   insert into test_employee values(7,1,'王小五',4000,5000)
   insert into test_employee values(8,1,'赵小六',4000,5000)
   insert into test_employee values(9,1,'马七',4000,5000)
   insert into test_employee values(10,2,'马小七',4000,5000)
   insert into test_employee values(7,1,'wxw',4000,5000)


员工信息表;
----------------------------------- -------- ------------------------------------
EMPLOYEE_ID                         NOT NULL BINARY_INTEGER
SECTION_ID                                   BINARY_INTEGER
EMPLOYEE_NAME                                VARCHAR(20 BYTE)
BASE_PAY                                     BINARY_INTEGER
EMPLOYEE_SALARY                              BINARY_INTEGER

 

更新部门人数;

    update  test_sections  set  SECTION_EMPLOYEE=(select count(1) from test_employee where  test_sections.SECTION_ID=test_employee.SECTION_ID ) ;

 

创建存储过程;

 create or replace procedure test_select(name in varchar2 )
 is
  letter varchar2(5) :='a' ; -- 定义变量,用来存储首字母,从a开始,循环递增至z 。
  type cur_type is ref cursor ; -- 声明游标类型。
  result_set cur_type; --定义游标变量。
  TYPE record_type is record(
         emp_id test_employee.EMPLOYEE_ID%type,
                              emp_name test_employee.EMPLOYEE_NAME%type,
         sec_name test_sections.SECTION_NAME%type,
         emp_bas test_employee.BASE_PAY%type ,
         emp_sal test_employee.EMPLOYEE_SALARY%type );    --声明记录类型。

  result_record  record_type;   --定义记录变量。

  department_count int ;  --记录输入的部门是否存在。

  is_have int  ;   --记录部门内是否有员工。

  employee_num int ; --记录是否还有以某个字母开头的当前部门的员工。

 begin
  select count(*) into department_count from test_sections where SECTION_NAME like name;
  if department_count<1 then
            dbms_output.put_line('部门名称输入有误!');
            return;
        end if;
  select t3.SECTION_EMPLOYEE into employee_num from test_sections t3 where t3.SECTION_NAME like name;
  if employee_num=0  then
            dbms_output.put_line('部门暂无员工!');
            return;
        end if;
  loop
   exit when letter='z' ;
   letter:=chr(ascii(letter)+1);

   open result_set for select t1.EMPLOYEE_ID,t1.EMPLOYEE_NAME,t2.SECTION_NAME,t1.BASE_PAY,t1.EMPLOYEE_SALARY
                         from test_employee t1
                      join test_sections t2  on t1.SECTION_ID=t2.SECTION_ID 
          where t2.SECTION_NAME=name and EMPLOYEE_NAME like  letter||'%' ;
         
   select count(*) into is_have from test_employee where  EMPLOYEE_NAME like  letter||'%'  ;
   if is_have>0 then
    dbms_output.put_line('首字母'||letter||':');
    dbms_output.put_line('员工编号  员工姓名  所在部门  基本工资  实发工资');
   end if ;
 
   loop
    fetch result_set into result_record ;
    exit when result_set%notfound; 
    dbms_output.put_line(  result_record.emp_id||'                            '||result_record.emp_name||'                  '||result_record.sec_name  
    ||'                    '||result_record.emp_bas||'                       '||result_record.emp_sal);
   end loop;
  end loop ;
  dbms_output.put_line('打印完毕!');
  exception
  when no_data_found then
   dbms_output.put_line('部门名称有误!');
  when  others  then
   dbms_output.put_line('程序异常!');
 end ;
 /
 
call test_select('开发部');

 

test1.PNG


call test_select('支撑部');

test2.PNG


 
 各位看官,看都看完了,点个赞再走呗!