系统功能的基本要求(感谢关注!)
1.订户管理:订户增加、修改、删除;
2.目录管理:目录增加、删除、修改;
3.订单管理:完成订户订阅数据的管理;
4.订单查询:按订户查询订单详细情况;
5.统计查询:按报刊目录统计各类报刊的订阅数量及金额。
代码已实现在vc++6.0以及vs2019中成功运行(运行出现问题欢迎私信)
此题目说明书,任务书,课程设计所有内容部分已打包,需要的私信取!!!
#include <stdlib.h>
#include <windows.h>
#include <sqlucode.h>
#include <iostream>
#include <tchar.h>
using namespace std;
#define CNO_LEN 10
#define NMNO_LEN 10
#define QTY_LEN 4
#define DATE_LEN 10
#define START_LEN 10
#define TERM_LEN 10
#define TITLE_LEN 20
#define PERIOD_LEN 10
#define PRICE_LEN 8
#define NAME_LEN 10
#define ADDR_LEN 20
void back(){}
int main()
{
//定义句柄括和变量
SQLHENV dinghenv; //环境句柄括
SQLHDBC dinghdbc; //连接句柄括
SQLHSTMT dinghstmt; //语句句柄括
SQLRETURN ret;
SQLDOUBLE PRICE;
SQLINTEGER QTY;
SQLCHAR CNO[CNO_LEN], NMNO[NMNO_LEN], DATE1[DATE_LEN], START[START_LEN], TERM[TERM_LEN], TITLE[TITLE_LEN], PERIOD[PERIOD_LEN], NAME[NAME_LEN], ADDR[ADDR_LEN];
SQLINTEGER cbCNO = SQL_NTS, cbNMNO = SQL_NTS, cbQTY = 0, cbDATE1 = SQL_NTS, cbSTART = SQL_NTS, cbTERM = SQL_NTS, cbTITLE = SQL_NTS, cbPERIOD = SQL_NTS, cbNAME = SQL_NTS, cbADDR = SQL_NTS;
SQLINTEGER cbPRICE = 0;
//初始化环境
ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &dinghenv);
ret = SQLSetEnvAttr(dinghenv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);
//建立连接
ret = SQLAllocHandle(SQL_HANDLE_DBC, dinghenv, &dinghdbc);
ret = SQLConnect(dinghdbc, (SQLCHAR*)(_T("sqlserver")), SQL_NTS, (SQLCHAR*)(_T("sa")), SQL_NTS, (SQLCHAR*)(_T("772739")), SQL_NTS);
if (!(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO))
{
printf("数据库连接失败!\n");
return -1;
}
else
{
printf("数据库连接成功!加载中.....\n");
Sleep(2000);//延时2秒
system("cls");//清屏
}
loop:
//初始化语句句柄括
ret = SQLAllocHandle(SQL_HANDLE_STMT, dinghdbc, &dinghstmt);
ret = SQLSetStmtAttr(dinghstmt, SQL_ATTR_ROW_BIND_TYPE, (SQLPOINTER)SQL_BIND_BY_COLUMN, SQL_IS_INTEGER);
//执行语句
int x, y, m, n;
cout << "" << endl;
cout << " 订户订阅报刊的应用系统 " << endl;
cout << "" << endl;
cout << " 1、订户管理" << endl;
cout << " 2、目录管理" << endl;
cout << " 3、订单管理" << endl;
cout << " 4、订单查询" << endl;
cout << " 5、统计查询" << endl;
cout << " 6、退出" << endl;
cout << "" << endl;
cout << "请输入您的选择:";
cin >> x;
switch (x)
{
case 1:x = 1;//订户管理
{
system("cls");//清屏
loop1:
cout << "" << endl;
cout << " 订户管理" << endl;
cout << "" << endl;
cout << " 1、增加订户" << endl;
cout << " 2、修改订户" << endl;
cout << " 3、删除订户" << endl;
cout << " 4、返回上级" << endl;
cout << "" << endl;
cout << "请输入您的选择:";
cin >> y;
switch (y)
{
case 1:y = 1; //增加订户
{
SQLCHAR CNO1[CNO_LEN], NAME1[NAME_LEN], ADDR1[ADDR_LEN];
ret = SQLPrepare(dinghstmt, (SQLCHAR*)(_T("INSERT INTO CUSTOMER(CNO,NAME,ADDR) VALUES(?,?,?)")), SQL_NTS);
cout << "输入订户信息(订户编号,姓名,地址):" << endl;
cin >> CNO1;
cin >> NAME1;
cin >> ADDR1;
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
cbCNO = SQL_NTS; cbNAME = SQL_NTS; cbADDR = SQL_NTS;
ret = SQLBindParameter(dinghstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, CNO_LEN, 0, CNO1, 0, &cbCNO);
ret = SQLBindParameter(dinghstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, NAME_LEN, 0, NAME1, 0, &cbNAME);
ret = SQLBindParameter(dinghstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, ADDR_LEN, 0, ADDR1, 0, &cbADDR);
}
cout << "添加订户成功!" << endl;
system("pause");//冻结屏幕
system("cls");//清屏
ret = SQLExecute(dinghstmt);//Step 6 处理结果集并执行预编译后的语句
}
goto loop1;
case 2:y = 2;//修改订户
{
SQLCHAR CNO2[CNO_LEN], NAME2[NAME_LEN], ADDR2[ADDR_LEN];
ret = SQLPrepare(dinghstmt, (SQLCHAR*)(_T("UPDATE CUSTOMER SET NAME=?, ADDR=? WHERE CNO=?")), SQL_NTS);
cout << "输入订户编号以及修改信息(姓名,地址):" << endl;
cin >> CNO2;
cin >> NAME2;
cin >> ADDR2;
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
cbCNO = SQL_NTS; cbNAME = SQL_NTS; cbADDR = SQL_NTS;
ret = SQLBindParameter(dinghstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, NAME_LEN, 0, NAME2, 0, &cbNAME);
ret = SQLBindParameter(dinghstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, ADDR_LEN, 0, ADDR2, 0, &cbADDR);
ret = SQLBindParameter(dinghstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, CNO_LEN, 0, CNO2, 0, &cbCNO);
}
cout << "修改订户信息成功!" << endl;
system("pause");//冻结屏幕
system("cls");//清屏
ret = SQLExecute(dinghstmt);
}
goto loop1;
case 3:y = 3;//删除订户
{
SQLCHAR CNO3[CNO_LEN];
ret = SQLPrepare(dinghstmt, (SQLCHAR*)(_T("DELETE FROM CUSTOMER WHERE CNO=?")), SQL_NTS);
cout << "输入要删除的订户编号" << endl;
cin >> CNO3;
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
cbCNO = SQL_NTS;
ret = SQLBindParameter(dinghstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, CNO_LEN, 0, CNO3, 0, &cbCNO);
}
cout << "删除订户成功!" << endl;
system("pause");//冻结屏幕
system("cls");//清屏
ret = SQLExecute(dinghstmt);
}
goto loop1;
case 4:y = 4;
system("cls");//清屏
goto loop;
}
}
case 2:x = 2;//报刊目录管理
{
system("cls");//清屏
loop2:
cout << "" << endl;
cout << " 报刊目录管理" << endl;
cout << "" << endl;
cout << " 1、增加报刊目录" << endl;
cout << " 2、修改报刊目录" << endl;
cout << " 3、删除报刊目录" << endl;
cout << " 4、返回上级" << endl;
cout << "" << endl;
cout << "请输入您的选择:";
cin >> m;
switch (m)
{
case 1:y = 1;//增加报刊目录
{
SQLCHAR NMNO1[NMNO_LEN], TITLE1[TITLE_LEN], PERIOD1[PERIOD_LEN];
SQLDOUBLE PRICE1;
ret = SQLPrepare(dinghstmt, (SQLCHAR*)(_T("INSERT INTO NMTABLE(NMNO,TITLE,PERIOD,PRICE) VALUES(?,?,?,?)")), SQL_NTS);
cout << "输入报刊信息(报刊号,名称,刊期,单价):" << endl;
cin >> NMNO1;
cin >> TITLE1;
cin >> PERIOD1;
cin >> PRICE1;
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
cbNMNO = SQL_NTS; cbTITLE = SQL_NTS; cbPERIOD = SQL_NTS; cbPRICE = 0;
ret = SQLBindParameter(dinghstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, NMNO_LEN, 0, NMNO1, 0, &cbNMNO);
ret = SQLBindParameter(dinghstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, TITLE_LEN, 0, TITLE1, 0, &cbTITLE);
ret = SQLBindParameter(dinghstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, PERIOD_LEN, 0, PERIOD1, 0, &cbPERIOD);
ret = SQLBindParameter(dinghstmt, 4, SQL_PARAM_INPUT, SQL_C_DOUBLE, SQL_DOUBLE, 0, 0, &PRICE1, 0, &cbPRICE);
}
cout << "录入报刊信息成功!" << endl;
system("pause");//冻结屏幕
system("cls");//清屏
ret = SQLExecute(dinghstmt);
}
goto loop2;
case 2:y = 2;//修改报刊目录
{
SQLCHAR NMNO2[NMNO_LEN], TITLE2[TITLE_LEN], PERIOD2[PERIOD_LEN];
SQLDOUBLE PRICE2;
ret = SQLPrepare(dinghstmt, (SQLCHAR*)(_T("UPDATE NMTABLE SET TITLE=?,PERIOD=?,PRICE=? WHERE NMNO=?")), SQL_NTS);
cout << "输入报刊号及修改信息(名称,刊期,单价):" << endl;
cin >> NMNO2;
cin >> TITLE2;
cin >> PERIOD2;
cin >> PRICE2;
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
cbNMNO = SQL_NTS; cbTITLE = SQL_NTS; cbPERIOD = SQL_NTS; cbPRICE = SQL_NTS;
ret = SQLBindParameter(dinghstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, TITLE_LEN, 0, TITLE2, 0, &cbTITLE);
ret = SQLBindParameter(dinghstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, PERIOD_LEN, 0, PERIOD2, 0, &cbPERIOD);
ret = SQLBindParameter(dinghstmt, 3, SQL_PARAM_INPUT, SQL_C_DOUBLE, SQL_DOUBLE, 0, 0, &PRICE2, 0, &cbPRICE);
ret = SQLBindParameter(dinghstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, NMNO_LEN, 0, NMNO2, 0, &cbNMNO);
}
cout << "修改报刊信息成功!" << endl;
system("pause");//冻结屏幕
system("cls");//清屏
ret = SQLExecute(dinghstmt);
}
goto loop2;
case 3:m = 3;//删除报刊目录
{
SQLCHAR NMNO3[NMNO_LEN];
ret = SQLPrepare(dinghstmt, (SQLCHAR*)(_T("DELETE FROM NMTABLE WHERE NMNO=?")), SQL_NTS);
cout << "输入要删除的报刊号" << endl;
cin >> NMNO3;
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
cbCNO = SQL_NTS;
ret = SQLBindParameter(dinghstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, NMNO_LEN, 0, NMNO3, 0, &cbNMNO);
}
cout << "删除报刊信息成功!" << endl;
system("pause");//冻结屏幕
system("cls");//清屏
ret = SQLExecute(dinghstmt);
}
goto loop2;
case 4:m = 4;
system("cls");//清屏
goto loop;
}
}
case 3:x = 3;//订单管理
{
system("cls");//清屏
loop3:
cout << "" << endl;
cout << " 订单管理" << endl;
cout << "" << endl;
cout << " 1、增加订单" << endl;
cout << " 2、修改订单" << endl;
cout << " 3、删除订单" << endl;
cout << " 4、返回上级" << endl;
cout << "" << endl;
cout << "请输入您的选择:";
cin >> n;
switch (n)
{
case 1:m = 1; //增加订单
{
SQLCHAR CNO4[CNO_LEN], DATE4[DATE_LEN], NMNO4[NMNO_LEN], START4[START_LEN], TERM4[TERM_LEN];
SQLINTEGER QTY4;
ret = SQLPrepare(dinghstmt, (SQLCHAR*)(_T("INSERT INTO [dbo].[ORDER](CNO,DATE1,NMNO,START,TERM,QTY) VALUES(?,?,?,?,?,?)")), SQL_NTS);
cout << "输入订单信息(订户编号,日期,报刊号,起订期,终订期,份数):" << endl;
cin >> CNO4;
cin >> DATE4;
cin >> NMNO4;
cin >> START4;
cin >> TERM4;
cin >> QTY4;
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
cbCNO = SQL_NTS; cbDATE1 = SQL_NTS; cbNMNO = SQL_NTS; cbSTART = SQL_NTS; cbTERM = SQL_NTS; cbQTY = 0;
ret = SQLBindParameter(dinghstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, CNO_LEN, 0, CNO4, 0, &cbCNO);
ret = SQLBindParameter(dinghstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, DATE_LEN, 0, DATE4, 0, &cbDATE1);
ret = SQLBindParameter(dinghstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, NMNO_LEN, 0, NMNO4, 0, &cbNMNO);
ret = SQLBindParameter(dinghstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, START_LEN, 0, START4, 0, &cbSTART);
ret = SQLBindParameter(dinghstmt, 5, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, TERM_LEN, 0, TERM4, 0, &cbTERM);
ret = SQLBindParameter(dinghstmt, 6, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &QTY4, 0, &cbQTY);
}
cout << "添加订单信息成功!" << endl;
system("pause");//冻结屏幕
system("cls");//清屏
ret = SQLExecute(dinghstmt);
}
goto loop3;
case 2:n = 2;//修改订单
{
SQLCHAR CNO5[CNO_LEN], DATE5[DATE_LEN], NMNO5[NMNO_LEN], START5[START_LEN], TERM5[TERM_LEN];
SQLINTEGER QTY5;
ret = SQLPrepare(dinghstmt, (SQLCHAR*)(_T("UPDATE [dbo].[ORDER] SET DATE1=?,START=?,TERM=?,QTY=? WHERE CNO=? AND NMNO=?")), SQL_NTS);
cout << "输入订单订户编号和报刊号及修改信息(日期,起订期,终订期,份数)" << endl;
cin >> CNO5;
cin >> NMNO5;
cin >> DATE5;
cin >> START5;
cin >> TERM5;
cin >> QTY5;
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
cbCNO = SQL_NTS; cbDATE1 = SQL_NTS; cbNMNO = SQL_NTS; cbSTART = SQL_NTS; cbTERM = SQL_NTS; cbQTY = 0;
ret = SQLBindParameter(dinghstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, DATE_LEN, 0, DATE5, 0, &cbDATE1);
ret = SQLBindParameter(dinghstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, START_LEN, 0, START5, 0, &cbSTART);
ret = SQLBindParameter(dinghstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, TERM_LEN, 0, TERM5, 0, &cbTERM);
ret = SQLBindParameter(dinghstmt, 4, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &QTY5, 0, &cbQTY);
ret = SQLBindParameter(dinghstmt, 5, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, CNO_LEN, 0, CNO5, 0, &cbCNO);
ret = SQLBindParameter(dinghstmt, 6, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, NMNO_LEN, 0, NMNO5, 0, &cbNMNO);
}
cout << "修改订单信息成功!" << endl;
system("pause");//冻结屏幕
system("cls");//清屏
ret = SQLExecute(dinghstmt);
}
goto loop3;
case 3:n = 3;//删除订单
{
SQLCHAR CNO6[CNO_LEN], NMNO6[NMNO_LEN];
ret = SQLPrepare(dinghstmt, (SQLCHAR*)(_T("DELETE FROM [dbo].[ORDER] WHERE CNO=? AND NMNO=?")), SQL_NTS);
cout << "输入要删除订单的用户号与报刊号:" << endl;
cin >> CNO6;
cin >> NMNO6;
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
cbCNO = SQL_NTS; cbNMNO = SQL_NTS;
ret = SQLBindParameter(dinghstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, CNO_LEN, 0, CNO6, 0, &cbCNO);
ret = SQLBindParameter(dinghstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, NMNO_LEN, 0, NMNO6, 0, &cbNMNO);
}
cout << "删除订单信息成功!" << endl;
system("pause");//冻结屏幕
system("cls");//清屏
ret = SQLExecute(dinghstmt);
}
goto loop3;
case 4:n = 4;
system("cls");//清屏
goto loop;
}
}
case 4:x = 4;//查询订单
{
SQLCHAR CNO7[CNO_LEN], DATE7[DATE_LEN], NMNO7[NMNO_LEN], START7[START_LEN], TERM7[TERM_LEN];
SQLINTEGER QTY7;
ret = SQLPrepare(dinghstmt, (SQLCHAR*)(_T("SELECT * FROM [dbo].[ORDER] WHERE CNO=?")), SQL_NTS);
cout << "输入查询的订户编号:";
cin >> CNO7;
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
cbCNO = SQL_NTS; cbDATE1 = SQL_NTS; cbNMNO = SQL_NTS; cbSTART = SQL_NTS; cbTERM = SQL_NTS; cbQTY = 0;
ret = SQLBindParameter(dinghstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, CNO_LEN, 0, CNO7, 0, &cbCNO);
ret = SQLBindCol(dinghstmt, 1, SQL_C_CHAR, CNO7, CNO_LEN, &cbCNO);
ret = SQLBindCol(dinghstmt, 2, SQL_C_CHAR, DATE7, DATE_LEN, &cbDATE1);
ret = SQLBindCol(dinghstmt, 3, SQL_C_CHAR, NMNO7, NMNO_LEN, &cbNMNO);
ret = SQLBindCol(dinghstmt, 4, SQL_C_CHAR, START7, START_LEN, &cbSTART);
ret = SQLBindCol(dinghstmt, 5, SQL_C_CHAR, TERM7, TERM_LEN, &cbTERM);
ret = SQLBindCol(dinghstmt, 6, SQL_C_LONG, &QTY7, 0, &cbQTY);
}
cout << "订户编号 日期 报刊号 起订期 终订期 份数" << endl;
ret = SQLExecute(dinghstmt);
while (SQLFetch(dinghstmt) != SQL_NO_DATA_FOUND)
{
cout << CNO7 << DATE7 << NMNO7 << START7 << TERM7 << QTY7 << endl;
}
system("pause");//冻结屏幕
system("cls");//清屏
goto loop;
}
case 5:x = 5;//统计查询
{
SQLINTEGER SUM1;
SQLDOUBLE SUM2;
SQLINTEGER cbSUM1;
SQLINTEGER cbSUM2;
SQLCHAR NMNO[NMNO_LEN];
ret = SQLPrepare(dinghstmt, (SQLCHAR*)(_T("SELECT [dbo].[ORDER].NMNO,SUM(QTY),SUM(QTY*PRICE) FROM [dbo].[ORDER],NMTABLE WHERE [dbo].[ORDER].NMNO=NMTABLE.NMNO GROUP BY [dbo].[ORDER].NMNO")), SQL_NTS);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
cbNMNO = SQL_NTS; cbSUM1 = 0; cbSUM2 = 0;
ret = SQLBindCol(dinghstmt, 1, SQL_C_CHAR, NMNO, NMNO_LEN, &cbNMNO);
ret = SQLBindCol(dinghstmt, 2, SQL_C_LONG, &SUM1, 0, &cbSUM1);
ret = SQLBindCol(dinghstmt, 3, SQL_C_DOUBLE, &SUM2, 0, &cbSUM2);
}
cout << "报刊号 份数 总价"<<endl;
ret = SQLExecute(dinghstmt);
while (SQLFetch(dinghstmt) != SQL_NO_DATA_FOUND)
{
cout << NMNO << SUM1 << " " << SUM2 << endl;
}
system("pause");//冻结屏幕
system("cls");//清屏
goto loop;
}
default:return 0;
}
//中止处理
SQLFreeHandle(SQL_HANDLE_STMT, dinghstmt);
SQLDisconnect(dinghdbc);
SQLFreeHandle(SQL_HANDLE_DBC, dinghdbc);
SQLFreeHandle(SQL_HANDLE_ENV, dinghenv);
return 0;
}