Oracle练习题
1. 正则校验手机号码，分批提交事务
己知一个客户手机号码表，T_CUSIMOBILE (CUSTNO NUMBER,MOBILENO VARCHAR2(20)); 请通过程序实现对该表数据进行逐条遍历，找出手机号码合法的客户号，并将客户号保存到 T_TMPCUST(CUSTNO NUMBER); 表中，并考虑当数据量较大的情况下实现分批提交事务。
己知手机号码合法的检查规则:号码由长度11位的数字组成，并由13、15、18开头，请优先考虑使用正则表达式。
1.1 创建表，插入测试数据
CREATE TABLE T_CUSIMOBILE (CUSTNO NUMBER,MOBILENO VARCHAR2(20));
CREATE TABLE T_TMPCUST(CUSTNO NUMBER);
INSERT INTO T_CUSIMOBILE(CUSTNO,MOBILENO) VALUES (123,'180****6527');
INSERT INTO T_CUSIMOBILE(CUSTNO,MOBILENO) VALUES (124,'130****6527');
INSERT INTO T_CUSIMOBILE(CUSTNO,MOBILENO) VALUES (125,'150****6527');
INSERT INTO T_CUSIMOBILE(CUSTNO,MOBILENO) VALUES (126,'110****6527');
INSERT INTO T_CUSIMOBILE(CUSTNO,MOBILENO) VALUES (127,'180****652');
INSERT INTO T_CUSIMOBILE(CUSTNO,MOBILENO) VALUES (128,'180****65278');
INSERT INTO T_CUSIMOBILE(CUSTNO,MOBILENO) VALUES (101,'180****6527');
INSERT INTO T_CUSIMOBILE(CUSTNO,MOBILENO) VALUES (102,'180****6527');
INSERT INTO T_CUSIMOBILE(CUSTNO,MOBILENO) VALUES (103,'180****6527');
INSERT INTO T_CUSIMOBILE(CUSTNO,MOBILENO) VALUES (104,'180****6527');
INSERT INTO T_CUSIMOBILE(CUSTNO,MOBILENO) VALUES (105,'180****6527');
INSERT INTO T_CUSIMOBILE(CUSTNO,MOBILENO) VALUES (106,'180****6527');
INSERT INTO T_CUSIMOBILE(CUSTNO,MOBILENO) VALUES (107,'180****6527');
INSERT INTO T_CUSIMOBILE(CUSTNO,MOBILENO) VALUES (108,'180****6527');
INSERT INTO T_CUSIMOBILE(CUSTNO,MOBILENO) VALUES (109,'180****6527');
INSERT INTO T_CUSIMOBILE(CUSTNO,MOBILENO) VALUES (110,'180****6527');
select * from T_CUSIMOBILE;
1.2 编写存储过程
CREATE OR REPLACE PROCEDURE mobileRegular as
num number := 0;
BEGIN
FOR CUSTNOINFO in (select * from T_CUSIMOBILE where REGEXP_LIKE(MOBILENO,'^[1][358][0-9]{9}$')) loop
IF num = 2 THEN
COMMIT;
num := 0;
Dbms_output.Put_line('提交了一次！');
END IF;
INSERT INTO T_TMPCUST (CUSTNO) VALUES (CUSTNOINFO.CUSTNO);
num := num + 1;
end loop;
END;
1.3 运行结果
SELECT * from T_CUSIMOBILE where REGEXP_LIKE(MOBILENO,'^[1][358][0-9]{9}$');
call mobileRegular();
SELECT * FROM T_TMPCUST;
- 手机号正则结果
- 存储过程调用结果
- 查询T_TMPCUST表结果
2. 统计各渠道某年各季度的销售数量
己知一张渠道每日销售量表
T_AGENCY_SALESTATS
(AGENCYNO VARCHAR2(10),--集道
SALEDATE DATE,--日期
SALECOUNT NUMBER);--销售量
按照要求统计各渠道 2019年各季度的销售数量,按照如下表格输出统计结果。
2.1 创建表，插入测试数据
CREATE TABLE T_AGENCY_SALESTATS
(AGENCYNO VARCHAR2(10),--集道
SALEDATE DATE,--日期
SALECOUNT NUMBER);--销售量
INSERT INTO T_AGENCY_SALESTATS (AGENCYNO,SALEDATE,SALECOUNT) VALUES ('北京',TO_DATE('2019-02-06 22:19:25', 'yyyy-mm-dd HH24:MI:SS'),10000);
INSERT INTO T_AGENCY_SALESTATS (AGENCYNO,SALEDATE,SALECOUNT) VALUES ('上海',TO_DATE('2019-02-06 22:19:25', 'yyyy-mm-dd HH24:MI:SS'),20000);
INSERT INTO T_AGENCY_SALESTATS (AGENCYNO,SALEDATE,SALECOUNT) VALUES ('深圳',TO_DATE('2019-02-06 22:19:25', 'yyyy-mm-dd HH24:MI:SS'),30000);
INSERT INTO T_AGENCY_SALESTATS (AGENCYNO,SALEDATE,SALECOUNT) VALUES ('香港',TO_DATE('2019-02-06 22:19:25', 'yyyy-mm-dd HH24:MI:SS'),40000);
INSERT INTO T_AGENCY_SALESTATS (AGENCYNO,SALEDATE,SALECOUNT) VALUES ('北京',TO_DATE('2019-05-06 22:19:25', 'yyyy-mm-dd HH24:MI:SS'),10000);
INSERT INTO T_AGENCY_SALESTATS (AGENCYNO,SALEDATE,SALECOUNT) VALUES ('上海',TO_DATE('2019-05-06 22:19:25', 'yyyy-mm-dd HH24:MI:SS'),20000);
INSERT INTO T_AGENCY_SALESTATS (AGENCYNO,SALEDATE,SALECOUNT) VALUES ('深圳',TO_DATE('2019-05-06 22:19:25', 'yyyy-mm-dd HH24:MI:SS'),30000);
INSERT INTO T_AGENCY_SALESTATS (AGENCYNO,SALEDATE,SALECOUNT) VALUES ('香港',TO_DATE('2019-05-06 22:19:25', 'yyyy-mm-dd HH24:MI:SS'),40000);
INSERT INTO T_AGENCY_SALESTATS (AGENCYNO,SALEDATE,SALECOUNT) VALUES ('北京',TO_DATE('2019-08-06 22:19:25', 'yyyy-mm-dd HH24:MI:SS'),10000);
INSERT INTO T_AGENCY_SALESTATS (AGENCYNO,SALEDATE,SALECOUNT) VALUES ('上海',TO_DATE('2019-08-06 22:19:25', 'yyyy-mm-dd HH24:MI:SS'),20000);
INSERT INTO T_AGENCY_SALESTATS (AGENCYNO,SALEDATE,SALECOUNT) VALUES ('深圳',TO_DATE('2019-08-06 22:19:25', 'yyyy-mm-dd HH24:MI:SS'),30000);
INSERT INTO T_AGENCY_SALESTATS (AGENCYNO,SALEDATE,SALECOUNT) VALUES ('香港',TO_DATE('2019-08-06 22:19:25', 'yyyy-mm-dd HH24:MI:SS'),40000);
INSERT INTO T_AGENCY_SALESTATS (AGENCYNO,SALEDATE,SALECOUNT) VALUES ('北京',TO_DATE('2019-10-06 22:19:25', 'yyyy-mm-dd HH24:MI:SS'),10000);
INSERT INTO T_AGENCY_SALESTATS (AGENCYNO,SALEDATE,SALECOUNT) VALUES ('上海',TO_DATE('2019-10-06 22:19:25', 'yyyy-mm-dd HH24:MI:SS'),20000);
INSERT INTO T_AGENCY_SALESTATS (AGENCYNO,SALEDATE,SALECOUNT) VALUES ('深圳',TO_DATE('2019-10-06 22:19:25', 'yyyy-mm-dd HH24:MI:SS'),30000);
INSERT INTO T_AGENCY_SALESTATS (AGENCYNO,SALEDATE,SALECOUNT) VALUES ('香港',TO_DATE('2019-10-06 22:19:25', 'yyyy-mm-dd HH24:MI:SS'),40000);
SELECT * FROM T_AGENCY_SALESTATS;
2.2 编写SQL
SELECT AGENCYNO "渠道",
"第一季度","第二季度","第三季度","第四季度"
from (
SELECT AGENCYNO,
CASE WHEN TO_CHAR(SALEDATE, 'Q') = '1' THEN '第一季度'
WHEN TO_CHAR(SALEDATE, 'Q') = '2' THEN '第二季度'
WHEN TO_CHAR(SALEDATE, 'Q') = '3' THEN '第三季度'
WHEN TO_CHAR(SALEDATE, 'Q') = '4' THEN '第四季度'
END AS QUARTER,
SUM(SALECOUNT) AS TOTAL_SALE
FROM T_AGENCY_SALESTATS
WHERE TO_CHAR(SALEDATE, 'YYYY') = '2019'
GROUP BY AGENCYNO, TO_CHAR(SALEDATE, 'Q')
ORDER BY AGENCYNO, TO_CHAR(SALEDATE, 'Q'))
pivot(
sum(TOTAL_SALE)
for QUARTER IN ('第一季度' "第一季度",'第二季度' "第二季度",'第三季度' "第三季度",'第四季度' "第四季度")
)
3. 求博物馆曾最长连续闭馆多少天
现有一个数据表 museum，记录了一个小型博物馆每天的访客数量:
博物馆会不定期闭馆,闭馆当天不会向该表添加记录,例如上面缺少的 20160707、20160708、20160711 三天为闭馆。尝试以查询SQL 或存储过程的形式，求该博物馆曾最长连续闭馆多少天。(时间范围以record_date 字段的最小值、最大值为准)
以上面5行数据为例，求出的结果为2，即 20160705~20160712这个时间范围内，最长闭馆2天，对应 20160707、20160708;如果表中的日期数据是连续的，求出的结果为0。
3.1 创建表，插入测试数据
CREATE TABLE museum (record_date VARCHAR2(32),visitors VARCHAR2(16));
#第一次新增测试数据
INSERT INTO museum(record_date,visitors) VALUES ('20160705','371');
INSERT INTO museum(record_date,visitors) VALUES ('20160706','128');
INSERT INTO museum(record_date,visitors) VALUES ('20160709','85');
INSERT INTO museum(record_date,visitors) VALUES ('20160710','246');
INSERT INTO museum(record_date,visitors) VALUES ('20160712','169');
select * from museum;
#第二次新增测试数据
INSERT INTO museum(record_date,visitors) VALUES ('20160718','169');
#第三次新增测试数据
INSERT INTO museum(record_date,visitors) VALUES ('20160707','169');
INSERT INTO museum(record_date,visitors) VALUES ('20160708','169');
INSERT INTO museum(record_date,visitors) VALUES ('20160711','169');
INSERT INTO museum(record_date,visitors) VALUES ('20160713','169');
INSERT INTO museum(record_date,visitors) VALUES ('20160714','169');
INSERT INTO museum(record_date,visitors) VALUES ('20160715','169');
INSERT INTO museum(record_date,visitors) VALUES ('20160716','169');
INSERT INTO museum(record_date,visitors) VALUES ('20160717','169');
3.2 编写存储过程
CREATE OR REPLACE procedure daysOfClosure
is
n_count1 number := 0;
n_count2 number := 0;
num_min number;
num_max number;
num number;
begin
SELECT MIN(record_date),MAX(record_date) into num_min,num_max FROM museum;
while num_min <= num_max loop
begin
SELECT record_date into num from museum where record_date = num_min;
exception when no_data_found then num := 0;
end;
Dbms_output.Put_line('num_min值：' || num_min);
Dbms_output.Put_line('num值：' || num);
IF num = 0 THEN
n_count2 := n_count2 + 1;
ELSE
if n_count2 > n_count1 THEN
n_count1 := n_count2;
END IF;
n_count2 := 0;
END IF;
num_min := num_min + 1;
end loop;
Dbms_output.Put_line('该博物馆曾最长连续闭馆' || n_count1 || '天');
end;
3.3 运行结果
- 第一次测试数据结果
- 第二次测试数据结果
- 第三次测试数据结果
4. 按照指定的分割符拆分字符串
请用 PL/SQL 编程实现一个字符串拆分函数。
函数及返回值类型定义如下:
type arrytype is table of varchar2(4000);
function func_split(i_SourceStr in varchar2,i_Separator in varchar2)return arrytype;
其中:
1） i_SourceStr：待拆字符串
2） i_Separator：分隔符
实现按照指定的分割符拆分字符串的功能。
4.1 以集合类型返回结果集
4.2 以Table形式返回结果集
- 编写函数
CREATE OR REPLACE FUNCTION func_split (i_SourceStr IN VARCHAR2, i_Separator IN VARCHAR2)
RETURN sys.odcivarchar2list PIPELINED
AS
l_start_pos PLS_INTEGER := 1;
l_end_pos PLS_INTEGER;
BEGIN
LOOP
l_end_pos := INSTR(i_SourceStr, i_Separator, l_start_pos);
IF l_end_pos = 0 THEN
PIPE ROW(SUBSTR(i_SourceStr, l_start_pos));
EXIT;
END IF;
PIPE ROW(SUBSTR(i_SourceStr, l_start_pos, l_end_pos - l_start_pos));
l_start_pos := l_end_pos + 1;
END LOOP;
RETURN;
END;
- 运行结果
SELECT
column_value AS split_value
FROM
TABLE(func_split('a,b,c,d', ','));
Oracle 文章被收录于专栏
Oracle
