实验 12

mysql> use xscj;
Database changed
mysql> delimiter $$
mysql> drop procedure if exists get_a_character;
    ->     create procedure  get_a_character(in s char(100),out w char(1))
    ->      begin
    ->        declare len int;
    ->       declare pos int;
    ->        set len = char_length(s);
    ->       set pos = floor(rand() * len) + 1;
    ->   set w = substring(s, pos, 1);
    ->   end  $$
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql> call get_a_character('刘王张赵李钱朱何孙曾陈吴黄刘邓周毛江胡', @k);
Query OK, 0 rows affected (0.00 sec)

mysql> select @k;
+------+
| @k   |
+------+
| 刘     |
+------+
1 row in set (0.00 sec)

mysql> delimiter $$
mysql> drop procedure if exists get_name;
    ->     create procedure  get_name(out m varchar(2),out xb int)
    ->     begin
    ->      declare cd int;
    ->       declare ms1,ms2 varchar(50);
    ->      declare m1,m2,m3 varchar(2);
    ->    set ms1='依秋香巧紫萱莉玉碧丽念雅红燕艳莲荷蕾紫莹颖琪';
    ->    set ms2='澄德海超阳昌瀚亮锋涵煦杰俊诚毅峰衍浩广邈言博畅';
    ->
    ->      set xb = floor(rand() * 2);
    ->       set cd = floor(rand() * 2) + 1;
    ->
    ->        if cd = 1 then
    ->         if xb = 1 then
    ->        call get_a_character(ms2, m);
    ->      else
    ->         call get_a_character(ms1, m);
    ->       end if;
    ->        else
    ->        if xb = 1 then
    ->          set m = '';
    ->         call get_a_character(ms2, m1);
    ->      call get_a_character(ms2, m2);
    ->        set m = concat(m1, m2);
    ->     else
    -> set m='';
    ->      call get_a_character(ms1, m1);
    ->        call get_a_character(ms1, m2);
    ->      set m = concat(m1, m2);
    ->      end if;
    ->    end if;
    -> end  $$
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql> call get_name(@name,@sex);
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select @name,@sex;
+-------+------+
| @name | @sex |
+-------+------+
| 澄言      |    1 |
+-------+------+
1 row in set (0.00 sec)

mysql> delimiter $$
mysql>  drop procedure if exists main;
    ->     create procedure main()
    ->     begin
    ->     declare x varchar(100);
    ->    declare xing,ming,xm varchar(3);
    ->     declare sex,n int;
    ->      declare xh varchar(8);
    ->      create  table xs2 as select 学号,姓名,性别 from xs where 1<>1 ;
    ->      set xh = '191100';
    ->      set n = 1;
    ->      while n<=20 do
    ->       call get_a_character(@x,@k);
    ->   set xing=@k;
    ->  call get_name(@name,@sex);
    ->      set ming = @name;
    ->       set sex = @sex;
    ->     set xm = concat(xing,ming);
    ->       insert into xs2 values(xh,xm,sex);
    ->       set n = n+1;
    ->  set xh = xh +1;
    ->   end while;
    ->     end $$
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select* from xs2;

 
mysql> +--------+--------+------+
    -> | 学号   | 姓名   | 性别 |
    -> +--------+--------+------+
    -> | 191100 | 陈颖莲 |    0 |
    -> | 191101 | 刘香秋 |    0 |
    -> | 191102 | 胡香   |    0 |
    -> | 191103 | 李博   |    1 |
    -> | 191104 | 邓玉莹 |    0 |
    -> | 191105 | 钱毅德 |    1 |
    -> | 191106 | 陈蕾   |    0 |
    -> | 191107 | 刘巧燕 |    0 |
    -> | 191108 | 孙依   |    0 |
    -> | 191109 | 吴香   |    0 |
    -> | 191110 | 曾玉红 |    0 |
    -> | 191111 | 毛萱碧 |    0 |
    -> | 191112 | 王琪   |    0 |
    -> | 191113 | 刘邈   |    1 |
    -> | 191114 | 张锋   |    1 |
    -> | 191115 | 江燕   |    0 |
    -> | 191116 | 孙莲蕾 |    0 |
    -> | 191117 | 刘莹   |    0 |
    -> | 191118 | 何德峰 |    1 |
    -> | 191119 | 张德畅 |    1 |
    -> +--------+--------+------+
    -> 20 rows in set (0.00 sec)