1. 删除用户, 创建用户, 授权:
drop user atguigu cascade;
create user atguigu identified by atguigu;
grant connect, resource, dba to atguigu;
2. 创建序列:
CREATE SEQUENCE CRM_SEQ
INCREMENT BY 1
START WITH 100
3. 查询出 1 天(6 个月)以内没有下订单的客户 id, name, manager_id, order_date, 且每个客户只查询出距离当前最近的一个订单.
SELECT c.id, name, manager_id, o.order_date
from customers c
JOIN (SELECT customer_id, MAX(order_date) order_date
FROM orders
GROUP BY customer_id) o
--from customers c JOIN orders o
ON c.id = o.customer_id
WHERE o.order_date < sysdate - 1
--WHERE o.order_date < add_months(sysdate, -6);
4. 存储过程: 客户状态: 正常, 删除, 预警, 流失
CREATE OR REPLACE PROCEDURE check_drain
IS
--声明游标
CURSOR drain_cursor IS
SELECT c.id, name, manager_id, o.order_date
FROM customers c
JOIN (SELECT customer_id, MAX(order_date) order_date
FROM orders
GROUP BY customer_id) o
ON c.id = o.customer_id
WHERE o.order_date < SYSDATE - 5
--WHERE o.order_date < ADD_MONTHS(SYSDATE, -6)
AND c.state = '正常';
BEGIN
FOR drain_info IN drain_cursor LOOP
INSERT INTO customer_drains(id, customer_id, last_order_date, status)
VALUES(CRM_SEQ.NEXTVAL, drain_info.id, drain_info.order_date, '流失预警');
UPDATE customers SET state = '流失预警' WHERE id = drain_info.id;
END LOOP;
END;
CRM客户关系管理系统带SQL
2星 需积分: 12 5 浏览量
2015-07-24
13:15:47
上传
评论 3
收藏 24.37MB ZIP 举报 
CRM客户关系管理系统带SQL （479个子文件） 
procedures.txt.bak 814B Reflections.class 8KB BSAbstractMultiCheckedElementTag.class 8KB JPACriteriaTest.class 7KB Customer.class 7KB JsonMapper.class 7KB SalesChanceService.class 5KB DynamicSpecifications$1.class 5KB StringHttpMessageConverter.class 5KB Collections3.class 5KB CommonDao.class 5KB BeanValidators.class 4KB CustomerServiceController.class 4KB PropertiesLoader.class 4KB SalesChance.class 4KB Role.class 4KB BaseService.class 4KB Cryptos.class 4KB CustomerService.class 4KB CustomerService.class 4KB ShiroDbRealm.class 3KB RoleController.class 3KB SalesChanceController.class 3KB Servlets.class 3KB Authority.class 3KB SearchFilter.class 3KB BSCheckboxesTag.class 3KB Digests.class 3KB Encodes.class 3KB SalesPlanCotroller.class 3KB User.class 3KB JPACriteriaTest$1.class 3KB CustomerActivityController.class 2KB ContactController.class 2KB UserService.class 2KB Hibernates.class 2KB Threads.class 2KB Order.class 2KB CustomerController.class 2KB UserController.class 2KB StorageController.class 2KB ContactService.class 2KB CustomerDrainController.class 2KB AuthorityService.class 2KB ProductController.class 2KB BaseController.class 2KB DictController.class 2KB CustomerActivity.class 2KB Contact.class 2KB ReportController.class 2KB CustomerDrain.class 2KB JPATest.class 2KB SalesPlan.class 2KB OrderController.class 2KB CustomerActivityService.class 2KB OrderService.class 2KB CustomerServiceService.class 2KB DictService.class 2KB ShiroDbRealm$ShiroUser.class 2KB Exceptions.class 2KB Storage.class 2KB OrderItem.class 2KB Product.class 2KB SearchFilter$Operator.class 2KB CustomerDrainService.class 1KB Dict.class 1KB Identities.class 1KB Threads$WrapExceptionRunnable.class 1KB DynamicSpecifications.class 991B MenuController.class 976B DictRepository.class 947B BSRadioButtonsTag.class 937B IdEntity.class 884B HasAnyPermissionsTag.class 851B DiGui.class 665B DateProvider$ConfigurableDateProvider.class 639B DateProvider.class 589B SalesPlanService.class 579B ProductService.class 571B StorageService.class 571B DateProvider$CurrentDateProvider.class 563B RoleService.class 559B StopWatch.class 558B BaseRepository.class 496B ContactRepository.class 483B UserRepository.class 371B CustomerActivityRepository.class 332B CustomerServiceRepository.class 329B CustomerDrainRepository.class 323B SalesChanceRepository.class 317B JFreeChartUtils.class 312B AuthorityRepository.class 311B SalesPlanRepository.class 311B CustomerRepository.class 308B StorageRepository.class 305B ProductRepository.class 305B OrderRepository.class 299B RoleRepository.class 296B .classpath 838B org.eclipse.wst.common.component 694B共 479 条
- 1
- 2
- 3
- 4
- 5
资源评论
tianhenjie2018-08-20学习一下，谢谢
openth2019-11-15积分有点贵
