tlr_upd.pc

数据结构学习点滴

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "tlr.h"

EXEC SQL BEGIN DECLARE SECTION;
	char usrname[10];
	char usrpwd[10];
EXEC SQL END  DECLARE SECTION;

EXEC SQL INCLUDE sqlca;

int		insert();
int		delete();
int		update();
int 	query();

int
main()
{
	char operator[3];

	memset( usrname, 0, sizeof( usrname ) );
	memset( usrpwd, 0, sizeof( usrpwd ) );
	memset( operator, 0, sizeof( operator ) );

	/**用户登录**/
	strcpy( usrname, "cbsdb" );
	strcpy( usrpwd, "cbsdb" );
	EXEC SQL CONNECT :usrname IDENTIFIED BY :usrpwd;
	printf( "\nOracle usr %s Connected\n", usrname );

	while( 1 )
	{
		/**建立菜单**/
		printf( "\n***********************************" );
		printf( "\n*                                 *" );
		printf( "\n*           1.QUERY               *" );
		printf( "\n*                                 *" );
		printf( "\n*           2.UPDATE              *" );
		printf( "\n*                                 *" );
		printf( "\n*           3.DELETE              *" );
		printf( "\n*                                 *" );
		printf( "\n*           4.INSERT              *" );
		printf( "\n*                                 *" );
		printf( "\n*           5.EXIT                *" );
		printf( "\n***********************************" );

		printf( "\nEnter Selection\n" );
		scanf( "%s", &operator );

		/**选择操作**/
		switch( operator[0] )
		{
			case '1': query();break;
			case '2': update();break;
			case '3': delete();break;
			case '4': insert();break;
			case '5': break;
			default : printf( "\nInvalid Selection,Try Again\n" );break;
		}

		if( operator[0] == '5' )
		{
			break;
		}
	}

	EXEC SQL COMMIT RELEASE;
	printf( "\n\nWelcome Back TO Use\n\n" );
	system( "clear" );
	printf( "\n\nWelcome Back TO Use\n\n" );
	return ( 0 );
}


/***int update() 该函数提示输入柜员号,查询该柜员，并更新它***/

int
update()
{
	EXEC SQL BEGIN DECLARE SECTION;
		struct s_tlr_mgmt	tlr_mgmt;
	EXEC SQL END   DECLARE SECTION;

	printf( "Enter the tlr_no, Please\n" );
    scanf( "%s", &tlr_mgmt.tlr_no ); 
	if( !strcmp( tlr_mgmt.tlr_no, "q" ) )
	{
		printf( "\n" );
		return( 0 );
	}

	EXEC SQL SELECT TLR_NO, BOX_NO, TLR_NAME 
		INTO :tlr_mgmt.tlr_no, :tlr_mgmt.box_no, :tlr_mgmt.tlr_name 
		FROM BAK_TLR_MGMT_04
		WHERE TLR_NO = :tlr_mgmt.tlr_no;

	if( sqlca.sqlcode == 1403 )
	{
		printf( " no data found\n " );
		return( sqlca.sqlcode );
	}
	else
	if( sqlca.sqlcode != 0 )
	{
		printf( "In update sql error [%d]\n", sqlca.sqlcode );
		return( sqlca.sqlcode );
	}
	else
	{
		printf( "\n\ntlr_mgmt.tlr_no\ttlr_mgt.box_no\ttlr_mgt.tlr_name\n");
		printf( "-------------\t---------------\t-------------------\n");
		printf( "%s\t\t%d\t\t%s\n", tlr_mgmt.tlr_no, tlr_mgmt.box_no,tlr_mgmt.tlr_name );
	}

	printf( "Enter the new box_no\n" );
	scanf( "%d", &tlr_mgmt.box_no );
	printf( "Enter the new tlr_name\n" );
	scanf( "%s", &tlr_mgmt.tlr_name );

	EXEC SQL UPDATE BAK_TLR_MGMT_04
		SET BOX_NO = :tlr_mgmt.box_no, TLR_NAME = :tlr_mgmt.tlr_name
		WHERE TLR_NO = :tlr_mgmt.tlr_no;
		
	printf( "Updated  %s tlr_name is %s, box_no is %d\n", tlr_mgmt.tlr_no, tlr_mgmt.tlr_name, tlr_mgmt.box_no );

	EXEC SQL COMMIT;
	return (0);
}		


/***输入柜员号,查询若不存在则插入,若已存在,退出***/
int
insert()
{
	EXEC SQL BEGIN DECLARE SECTION;
		struct s_tlr_mgmt	tlr_mgmt;
	EXEC SQL END   DECLARE SECTION;

	printf( "Input a tlr_no\n" );
	scanf( "%s", tlr_mgmt.tlr_no );
	if( !strcpy( tlr_mgmt.tlr_no, "q") )
	{
		printf( "\n" );
		return( 0 );
	}

	EXEC SQL SELECT TLR_NO, TLR_NAME
		INTO :tlr_mgmt.tlr_no, :tlr_mgmt.tlr_name
		FROM BAK_TLR_MGMT_04
		WHERE TLR_NO = :tlr_mgmt.tlr_no;
	
	if( sqlca.sqlcode == 0 )
	{
		printf( "the tlr has already exsist\n" );
		printf( "%s\t%s\n", tlr_mgmt.tlr_no, tlr_mgmt.tlr_name );
		return( 0 );
	}
	else
	if( sqlca.sqlcode !=0 && sqlca.sqlcode != 1403 )
	{
		printf( "Before insert ,query failed [%d]\n",sqlca.sqlcode );
		return( -1 );
	}
	else
	{
		printf( "Enter inst_no4\n" );
		scanf( "%s", &tlr_mgmt.inst_no );
		printf( "Enter tlr_name10\n" );
		scanf( "%s", &tlr_mgmt.tlr_name );
		printf( "Enter tlr_lvl1\n" );
		scanf( "%s", &tlr_mgmt.tlr_lvl );
		printf( "Enter set_tlr4\n" );
		scanf( "%s", &tlr_mgmt.set_tlr );
		printf( "Enter set_date\n" );
		scanf( "%d", &tlr_mgmt.set_date );
		printf( "Enter tlr_id\n" );
		scanf( "%s", &tlr_mgmt.tlr_id ); 
		printf( "Enter bgn_date\n" );
		scanf( "%d", &tlr_mgmt.bgn_date);
		printf( "Enter cls_date\n" );
		scanf( "%d", &tlr_mgmt.cls_date );
		printf( "Enter tlr_pwd6\n" );
		scanf( "%s", &tlr_mgmt.tlr_pwd );
		printf( "Enter lst_upt_date\n" );
		scanf( "%d", &tlr_mgmt.lst_upt_date );
		printf( "Enter pwd_cycle6\n" );
		scanf( "%s", tlr_mgmt.pwd_cycle );
		printf( "Enter pwd_flag1\n" );
		scanf( "%s", &tlr_mgmt.pwd_flag );
		printf( "Enter transet_no4\n" );
		scanf( "%s", &tlr_mgmt.transet_no );
		printf( "Enter condition1\n" );
		scanf( "%s", &tlr_mgmt.condition );
		printf( "Enter card_cond1\n" );
		scanf( "%s", &tlr_mgmt.card_cond );
		printf( "Enter box_no\n" );
		scanf( "%d",&tlr_mgmt.box_no ); 
		printf( "Enter lst_modify_tlr4\n" );
		scanf( "%s", &tlr_mgmt.lst_modify_tlr );
		printf( "Enter lst_modify_date\n" );
		scanf( "%d", &tlr_mgmt.lst_modify_date );
		printf( "Enter dac16\n" );
		scanf( "%s" ,&tlr_mgmt.dac );

		EXEC SQL INSERT INTO BAK_TLR_MGMT_04 VALUES( :tlr_mgmt );
		printf( "tlr_no %s inserted\n", tlr_mgmt.tlr_no );
	}
	
	EXEC SQL COMMIT;
	return( 0 );
}
		
/***输入柜员号，若有此柜员输出相关信息,无则返回***/
int
query()
{
	EXEC SQL BEGIN DECLARE SECTION;
		struct s_tlr_mgmt	tlr_mgmt;
	EXEC SQL END   DECLARE SECTION;

	printf( "Input a tlr_no to Query\n" );
	scanf( "%s", tlr_mgmt.tlr_no );

	EXEC SQL SELECT TLR_NO, TLR_NAME, INST_NO
		INTO :tlr_mgmt.tlr_no, :tlr_mgmt.tlr_name, :tlr_mgmt.inst_no
		FROM BAK_TLR_MGMT_04
		WHERE TLR_NO = :tlr_mgmt.tlr_no;
	
	if( sqlca.sqlcode == 1403 )
	{
		printf( "No Data Found\n" );
		return( -1 );
	}
	else
	if( sqlca.sqlcode != 0 )
	{
		printf( "In Query sql Error [%d]\n", sqlca.sqlcode );
		return( -1 );
	}
	else
	{
		printf( "柜员号\t姓名\t机构号\n" );
		printf( "------\t----\t-----\n" );
		printf( "%s\t%s%s\n", tlr_mgmt.tlr_no, tlr_mgmt.tlr_name, tlr_mgmt.inst_no );
	}
	return( 0 );
}

/***输入柜员号,有则删除,没有退出***/
int
delete()
{
	EXEC SQL BEGIN DECLARE SECTION;
		struct s_tlr_mgmt	tlr_mgmt;
	EXEC SQL END   DECLARE SECTION;

	printf( "Enter the tlr_no to Delete\n" );
	scanf( "%s", tlr_mgmt.tlr_no );
	if( !strcpy( tlr_mgmt.tlr_no, "q") )
	{
		printf( "\n");
		return( 0 );
	}

	EXEC SQL SELECT TLR_NO, TLR_NAME
		INTO :tlr_mgmt.tlr_no, :tlr_mgmt.tlr_name
		FROM BAK_TLR_MGMT_04
		WHERE TLR_NO = :tlr_mgmt.tlr_no;
	
	if( sqlca.sqlcode == 1403 )
	{
		printf( "No Data to Delete\n" );
		return( -1 );
	}
	else
	if( sqlca.sqlcode != 0 )
	{
		printf( "In delete sql error [%d]\n", sqlca.sqlcode );
		return( -1 );
	}
	else
	{
		EXEC SQL DELETE
			FROM BAK_TLR_MGMT_04
			WHERE TLR_NO = :tlr_mgmt.tlr_no;
		
		rtrim( tlr_mgmt.tlr_no );
		printf( "Tlr_no [%s] Deleted\n", tlr_mgmt.tlr_no );
	}

	EXEC SQL COMMIT;
	return( 0 );
}