本文是《使用 SQL 计算宝宝每次吃奶的时间间隔》的续篇, 因为我工作繁忙, 时常不能及时帮助媳妇儿记录, 为了让不懂数据库的媳妇儿也可以自己用手机熟练操作. 我继续做了一些修正和封装:
1. 给媳妇儿的手机下载一个 terminal 终端软件 terminus, 实现只需打开 App 就可以连接到环境;
2. 跟媳妇儿进一步沟通需求, 按她能够接受的使用习惯来修正并封装程序, 原则是尽可能的简化操作.
先来看下改进后最终的使用效果:
--1. 手机上打开 terminus 软件, 点击进入到终端, 输入 h 可以看到所有命令的说明:
- [oracle@jystdrac1 ~]$ h
- **Command for Baby's feed_time:**
- Usage:
- v - View Today's Result.
- vv <'mmdd'> - View one day's Result.
- i - Insert a row using current time.
- ii <'mmdd hh24:mi'> - Insert a row using input time.
- d <id> - Delete a mistake row by id.
- u <label> <id> - Update a mistake row by id.
- h - Help
--2. 输入 v 可以看到今天的喂奶时间和间隔:
- [oracle@jystdrac1 ~]$ v
- View Today's Result:
- ID FEED_TIME L LAG(min) LAG(h)
- ---------- ----------- - ---------- ----------
- 53 12-15 01:00 N 140 2.33
- 54 12-15 04:08 N 188 3.13
- 55 12-15 07:35 N 207 3.45
- 56 12-15 10:40 N 185 3.08
- 69 12-15 13:16 N 156 2.6
- 70 12-15 14:32 N 76 1.27
- 82 12-15 17:14 N 163 2.71
- 83 12-15 19:15 N 121 2.01
- 8 rows selected.
--3. 输入 i 可以直接以当前系统时间插入一条数据, 并显示插入后的结果:
- [oracle@jystdrac1 ~]$ i
- Insert a row using current time:
- 1 row created.
- Commit complete.
- View Today's Result:
- ID FEED_TIME L LAG(min) LAG(h)
- ---------- ----------- - ---------- ----------
- 53 12-15 01:00 N 140 2.33
- 54 12-15 04:08 N 188 3.13
- 55 12-15 07:35 N 207 3.45
- 56 12-15 10:40 N 185 3.08
- 69 12-15 13:16 N 156 2.6
- 70 12-15 14:32 N 76 1.27
- 82 12-15 17:14 N 163 2.71
- 83 12-15 19:15 N 121 2.01
- 94 12-15 23:02 N 227 3.78
- 9 rows selected.
--4. 输入 d <id> 可以删除 id 对应的那行记录, 并显示删除后的结果:
- [oracle@jystdrac1 ~]$ d 94
- Delete a mistake row by id:
- Enter value for id: old 1: delete from t_baby where id = &id
- new 1: delete from t_baby where id = 94
- 1 row deleted.
- Commit complete.
- View Today's Result:
- ID FEED_TIME L LAG(min) LAG(h)
- ---------- ----------- - ---------- ----------
- 53 12-15 01:00 N 140 2.33
- 54 12-15 04:08 N 188 3.13
- 55 12-15 07:35 N 207 3.45
- 56 12-15 10:40 N 185 3.08
- 69 12-15 13:16 N 156 2.6
- 70 12-15 14:32 N 76 1.27
- 82 12-15 17:14 N 163 2.71
- 83 12-15 19:15 N 121 2.01
- 8 rows selected.
--5. 输入 u <label> <id> 可以更新指定 id 的 label 值, 比如将 id=55 的记录 label 值修改为'L', 代表这次喂奶量很少, 不参与计算
- [oracle@jystdrac1 ~]$ u l 55
- Update a mistake row by id:
- Enter value for label: Enter value for id: old 1: update t_baby set label = upper('&label') where id = &id
- new 1: update t_baby set label = upper('l') where id = 55
- 1 row updated.
- Commit complete.
- View Today's Result:
- ID FEED_TIME L LAG(min) LAG(h)
- ---------- ----------- - ---------- ----------
- 53 12-15 01:00 N 140 2.33
- 54 12-15 04:08 N 188 3.13
- 56 12-15 10:40 N 392 6.53
- 69 12-15 13:16 N 156 2.6
- 70 12-15 14:32 N 76 1.27
- 82 12-15 17:14 N 163 2.71
- 83 12-15 19:15 N 121 2.01
- 7 rows selected.
--6. 输入 u <label> <id> 可以更新指定 id 的 label 值, 比如将 id=55 的记录 label 值修改回'N', 代表这次喂奶量正常, 参与计算
- [oracle@jystdrac1 ~]$ u n 55
- Update a mistake row by id:
- Enter value for label: Enter value for id: old 1: update t_baby set label = upper('&label') where id = &id
- new 1: update t_baby set label = upper('n') where id = 55
- 1 row updated.
- Commit complete.
- View Today's Result:
- ID FEED_TIME L LAG(min) LAG(h)
- ---------- ----------- - ---------- ----------
- 53 12-15 01:00 N 140 2.33
- 54 12-15 04:08 N 188 3.13
- 55 12-15 07:35 N 207 3.45
- 56 12-15 10:40 N 185 3.08
- 69 12-15 13:16 N 156 2.6
- 70 12-15 14:32 N 76 1.27
- 82 12-15 17:14 N 163 2.71
- 83 12-15 19:15 N 121 2.01
- 8 rows selected.
--7. 输入 ii <'mmdd hh24:mi'> 可以插入指定日期和时间
- [oracle@jystdrac1 ~]$ ii '1215 23:00'
- Insert a row using input time(mmdd mi:ss) eg: 1215 10:00
- Enter value for feed_time: old 1: insert into t_baby(id,feed_time) values(s1.nextval,to_date('&feed_time','mmdd hh24:mi'))
- new 1: insert into t_baby(id,feed_time) values(s1.nextval,to_date('1215 23:00','mmdd hh24:mi'))
- 1 row created.
- Commit complete.
- View Today's Result:
- ID FEED_TIME L LAG(min) LAG(h)
- ---------- ----------- - ---------- ----------
- 53 12-15 01:00 N 140 2.33
- 54 12-15 04:08 N 188 3.13
- 55 12-15 07:35 N 207 3.45
- 56 12-15 10:40 N 185 3.08
- 69 12-15 13:16 N 156 2.6
- 70 12-15 14:32 N 76 1.27
- 82 12-15 17:14 N 163 2.71
- 83 12-15 19:15 N 121 2.01
- 95 12-15 23:00 N 225 3.74
- 9 rows selected.
--8. 删除 id=95 的这条测试记录
- [oracle@jystdrac1 ~]$ d 95
- Delete a mistake row by id:
- Enter value for id: old 1: delete from t_baby where id = &id
- new 1: delete from t_baby where id = 95
- 1 row deleted.
- Commit complete.
- View Today's Result:
- ID FEED_TIME L LAG(min) LAG(h)
- ---------- ----------- - ---------- ----------
- 53 12-15 01:00 N 140 2.33
- 54 12-15 04:08 N 188 3.13
- 55 12-15 07:35 N 207 3.45
- 56 12-15 10:40 N 185 3.08
- 69 12-15 13:16 N 156 2.6
- 70 12-15 14:32 N 76 1.27
- 82 12-15 17:14 N 163 2.71
- 83 12-15 19:15 N 121 2.01
- 8 rows selected.
--9. 使用 vv 'mmdd' 可以显示指定日期的喂奶间隔情况:
- [oracle@jystdrac1 ~]$ vv 1214
- View Today's Result:
- Enter value for feed_time: Enter value for feed_time: old 2: from (select t.*, lag(feed_time) over(order by id) l_time from t_baby t where label <> 'L') where feed_time>= to_date('&feed_time','mmdd') and feed_time <to_date('&feed_time','mmdd') + 1
- new 2: from (select t.*, lag(feed_time) over(order by id) l_time from t_baby t where label <> 'L') where feed_time>= to_date('1214','mmdd') and feed_time <to_date('1214','mmdd') + 1
- ID FEED_TIME L LAG(min) LAG(h)
- ---------- ----------- - ---------- ----------
- 41 12-14 01:55 N 265 4.42
- 42 12-14 04:58 N 183 3.05
- 43 12-14 08:25 N 207 3.45
- 44 12-14 11:23 N 178 2.97
- 45 12-14 12:23 N 60 1
- 46 12-14 15:00 N 157 2.62
- 47 12-14 17:22 N 142 2.37
- 48 12-14 18:45 N 83 1.38
- 49 12-14 22:40 N 235 3.92
- 9 rows selected.
经过一番演示, 媳妇儿的反馈是非常满意的, 实际她最常用的还是 v 和 i, 非常方便, 其他命令偶尔使用, 其实只需记住 h 可以获取到帮助即可. 下面将本次的修正和封装过程记录一下:
1. 环境变量配置别名
2. 系统 shell 脚本
3. 底层 SQL 文本
1. 环境变量配置别名
为了简化操作, 我将环境变量设置一些别名.
- [oracle@jystdrac1 ~]$ cat ~/.bash_profile
- # 这里省略原有环境变量内容, 下面为在源文件基础上追加的内容:
- # Alias for baby's feed_time:
- alias h=/home/oracle/baby_readme.sh
- alias v=/home/oracle/baby_view.sh
- alias i=/home/oracle/baby_insert.sh
- alias d=/home/oracle/baby_delete.sh
- alias u=/home/oracle/baby_update.sh
- alias ii=/home/oracle/baby_insert_diy.sh
- alias vv=/home/oracle/baby_view_diy.sh
2. 系统 shell 脚本
- baby_readme.sh
- vi baby_readme.sh
- #!/bin/bash
- # script:baby_readme.sh
- # version:1.01
- #-------------------------------------------------------------
- echo "**Command for Baby's feed_time:**"echo"Usage:"echo" v - View Today's Result."
- echo "vv <'mmdd'> - View one day's Result."echo" i - Insert a row using current time."echo" ii <'mmdd hh24:mi'> - Insert a row using input time."echo" d <id> - Delete a mistake row by id."echo" u <label> <id> - Update a mistake row by id."echo" h - Help"
- #-------------------------------------------------------------
- baby_view.sh
- vi baby_view.sh
- #!/bin/bash
- # script:baby_view.sh
- # version:1.01
- #-------------------------------------------------------------
- sqlplus -S test/test <<EOF
- PROMPT View Today's Result:
- @v3
- EOF
- #-------------------------------------------------------------
- baby_insert.sh
- vi baby_insert.sh
- #!/bin/bash
- # script:baby_insert.sh
- # version:1.01
- #-------------------------------------------------------------
- sqlplus -S test/test <<EOF
- PROMPT Insert a row using current time:
- @i1
- PROMPT View Today's Result:
- @v3
- EOF
- #-------------------------------------------------------------
- baby_delete.sh
- vi baby_delete.sh
- #!/bin/bash
- # script:baby_delete.sh
- # version:1.01
- #-------------------------------------------------------------
- sqlplus -S test/test <<EOF
- PROMPT Delete a mistake row by id:
- @d1
- $1
- commit;
- PROMPT View Today's Result:
- @v3
- EOF
- #-------------------------------------------------------------
- baby_insert_diy.sh
- vi baby_insert_diy.sh
- #!/bin/bash
- # script:baby_insert_diy.sh
- # version:1.01
- #-------------------------------------------------------------
- sqlplus -S test/test <<EOF
- PROMPT Insert a row using input time(mmdd mi:ss) eg: 1215 10:00
- @i2
- $1
- commit;
- PROMPT View Today's Result:
- @v3
- EOF
- #-------------------------------------------------------------
- baby_update.sh
- vi baby_update.sh
- #!/bin/bash
- # script:baby_update.sh
- # version:1.01
- #-------------------------------------------------------------
- sqlplus -S test/test <<EOF
- PROMPT Update a mistake row by id:
- @u1
- $1
- $2
- commit;
- PROMPT View Today's Result:
- @v3
- EOF
- #-------------------------------------------------------------
- baby_view_diy.sh
- vi baby_view_diy.sh
- #!/bin/bash
- # script:baby_view.sh
- # version:1.01
- #-------------------------------------------------------------
- sqlplus -S test/test <<EOF
- PROMPT View one day's Result:
- @v4
- $1
- $1
- EOF
- #-------------------------------------------------------------
3. 底层 SQL 文本
- --echo "v - View Today's Result."
- [oracle@jystdrac1 ~]$ cat v3.sql
- select id, to_char(feed_time,'mm-dd hh24:mi') "FEED_TIME", label, round((feed_time - l_time) * 24 * 60) "LAG(min)", round((feed_time - l_time) * 24, 2) "LAG(h)"
- from (select t.*, lag(feed_time) over(order by id) l_time from t_baby t where label <> 'L') where feed_time>= trunc(sysdate)
- /
- -- echo "vv <'mmdd'> - View one day's Result."
- [oracle@jystdrac1 ~]$ cat v4.sql
- select id, to_char(feed_time,'mm-dd hh24:mi') "FEED_TIME", label, round((feed_time - l_time) * 24 * 60) "LAG(min)", round((feed_time - l_time) * 24, 2) "LAG(h)"
- from (select t.*, lag(feed_time) over(order by id) l_time from t_baby t where label <> 'L') where feed_time>= to_date('&feed_time','mmdd') and feed_time < to_date('&feed_time','mmdd') + 1
- /
- --echo "i - Insert a row using current time."
- [oracle@jystdrac1 ~]$ cat i1.sql
- insert into t_baby(id,feed_time) values(s1.nextval,sysdate)
- /
- commit
- /
- --echo "ii - Insert a row using input time."
- [oracle@jystdrac1 ~]$ cat i2.sql
- insert into t_baby(id,feed_time) values(s1.nextval,to_date('&feed_time','mmdd hh24:mi'))
- /
- --echo "d - Delete a mistake row by id."
- [oracle@jystdrac1 ~]$ cat d1.sql
- delete from t_baby where id = &id
- /
- --echo "u - Update a mistake row by id."
- [oracle@jystdrac1 ~]$ cat u1.sql
- update t_baby set label = upper('&label') where id = &id
- /
SQL 文本独立出来也方便后续需求有变化时快速更改.
来源: https://www.cnblogs.com/jyzhao/p/12046838.html