Transfer transaction and SQL example Programming

Transfer Transactions

Create a data environment (account table) first)

 
1. Transfer Transactions (used during storage)

If exists (select name from sysobjects
Where name = 'zhuanzhang' and type = 'P ')
Drop procedure zhuanzhang
Go

-- Create a transfer stored procedure with Parameters
-- @ ZhOUT: transfer out account Account
-- @ ZhIN transfer to account Account
-- @ JinE amount
Create procedure zhuanzhang
@ ZhOUT varchar (8), @ zhIN varchar (8), @ JinE int
As

-- Account A transfers transactions to account B

Begin transaction

-- Record Point
Save transaction befor_zhuanzhang

-- A account transfers A certain amount
Declare @ xianYou int
Select @ xianyou = amount from account table
Where account No. = @ zhOUT

If @ xianyou <@ JinE
Begin
Print 'amount is insufficient! '
Return
End

Update account table set amount = amount-@ JinE
Where account No. = @ zhOUT

-- Transfer a certain amount to account B
Update account table set amount = Amount + @ JinE
Where account No. = @ zhIN
 
If @ Error <> 0 or @ rowcount = 0
Begin
Rollback transaction befor_zhuanzhang
Print 'transfer error. '
Return
End

Commit transaction

2. Transaction test (execute the Stored Procedure zhuanzhang)

1. execute zhuanzhang 'a, 'B', 5000
2. execute zhuanzhang 'C', 'D', 5000
Note the result changes.

Http://blog.csdn.net/cnlht/archive/2007/12/19/1953032.aspx
Use CASE structure in queries

Use CASE structure to classify query results

Example 1:
Select zkzh Admission Ticket No., xm name, total score =
Case
When zcj = 0 then 'failed'
When zcj = 1 then 'pass'
When zcj = 2 then 'good'
When zcj = 3 then 'excellent'
Else 'error'
End
From ncre26

Questions:
1. How to classify the grade query results in SC (excellent-90 or above, good-80 or above, pass-60 or above, fail-59 or below ).
2. How to display gender 1 as male and gender 2 as female.
Database Transaction operations

SQL Server 2000 transaction operations

1. Transaction operation instance 1

-- Start transaction
Begin transaction
Insert student (sno, sname, ssex, sage, sdept)
Values ('201312', 'aliyw', 'male', 20, 'is ')
If @ error <> 0
Begin
Print 'An error occurred while adding student records! '
Return
End
-- Store record points
Save transaction befor_insert_score
Insert SC
Values ('20140901', '8', 90)
If @ error <> 0
Begin
-- Rollback
Rollback transaction befor_insert_score
Print 'score record addition error! '
Return
End
Commit transaction

2. Transaction operation instance 2 (what is the difference with the previous example ?)

-- Start transaction
Begin transaction
-- Store record points
Save transaction befor_insert_student
Insert student (sno, sname, ssex, sage, sdept)
Values ('201312', 'aliyw', 'male', 20, 'is ')
If @ error <> 0
Begin
Print 'student record added error! '
Return
End
Save transaction befor_insert_score
Insert SC
Values ('20140901', '8', 90)
If @ error <> 0
Begin
-- Rollback
Rollback transaction befor_insert_student
Print 'registration record error! '
Return
End
Commit transaction

Questions
1. There is an account table (account number, account name, amount)
Do the following: Transfer 3000 of account A to account B.
Transaction operations are required to ensure security.

2. Student table, course list, and course selection table
To cancel a course, follow these steps.
Tip: First Delete the Course Selection Record of the expected course and then delete the course. (Transactional operations are required)
Embedded SQL statements in C Language

 

Lab content:

Master the use of NSQLPREP. EXE, a pre-compiled program of SQL Server 2000 (debug with example 1 in the textbook );

Lab procedure:

I. database environment Configuration

1. Create an xuesheng database and a student table;

2. Disable the SQL server 2000 Service Manager;

3. Copy the devtools folder to: C: \ Program Files \ Microsoft SQL Server

4. Copy the Binn folder to: C: \ Program Files \ Microsoft SQL Server \ MSSQL

5. Start the server;

Ii. VC ++ 6.0 editor configuration (initialize the Vc ++ environment)

1. Tools-> select-> directory-> Include Files

Add: C: \ Program Files \ Microsoft SQL Server \ devtools \ include

And set as the first item

2. Select Library Files

Add: C: \ Program Files \ Microsoft SQL Server \ devtools \ x86lib

And set as the first item

 

3. write programs, pre-compile, and finally compile and execute in VC ++

1. Edit the EXEC. sqc file and save it to the: C: \ Program Files \ Microsoft SQL Server \ MSSQL \ Binn directory.

The EXEC. sqc file is as follows:

// EXEC. cpp: Defines the entry point for the console application.
//

# Include <stdio. h>
# Include <stdlib. h>

Exec SQL BEGIN DECLARE SECTION;/* main variable description start */
Char deptname [64];
Char HSno [64];
Char HSname [64];
Char HSsex [64];
Int HSage;
Int NEWAGE;
Long SQLCODE;
Exec SQL END DECLARE SECTION;/* main variable description ends */

Exec SQL include sqlca;/* define the SQL Communication zone */

/*************************************** **********************************/

Int main (void)/* C main program starts */
{
Int count = 0;
Char yn;/* The variable yn indicates yes or no */
Printf ("Please choose the department name (CS/MA/IS ):");
Scanf ("% s", deptname);/* assign values to the main variable deptname */
Exec SQL CONNECT TO demin USER
Sa;/* connect to the database TEST */
Exec SQL DECLARE SX CURSOR FOR/* define a CURSOR */
Execution result of SELECT Sno, Sname, Ssex, Sage/* SX statement */
FROM Student
WHERE SDept =: deptname;
Exec SQL OPEN SX;/* OPEN the cursor SX to point to the first row of the query result */

For (;)/* use a loop structure to process records in the result set one by one */
{
Exec SQL FETCH SX INTO: HSno,: HSname,: HSsex,: HSage;
/* Push the cursor to put the current data into the main variable */
If (sqlca-> sqlcode! = 0)/* sqlcode! = 0, indicating operation failed */
Break;/* use the status information in SQLCA to determine when to exit the loop */
If (count ++ = 0)/* if it is the first line, start travel first */
Printf ("\ n %-10 s %-20 s %-10 s %-10s \ n", "Sno", "Sname", "Ssex ", "Sage ");
Printf ("%-10 s %-20 s %-10 s %-10d \ n", HSno, HSname, HSsex, HSage );
/* Print the query result */
Printf ("update age (y/n )? ");/* Ask the user if they want to update the student's age */
Do {
Scanf ("% c", & yn );
}
While (yn! = 'N' & yn! = 'N' & yn! = 'Y' & yn! = 'Y ');
If (yn = 'y' | yn = 'y')/* if you select an update operation */
{
Printf ("input new age :");
Scanf ("% d", & NEWAGE);/* enter the new age to the primary variable */
Exec SQL update Student/* embedded SQL */
SET Sage =: NEWAGE
Where current of sx;
}/* Update the student age pointed to by the current cursor */
}

Exec SQL CLOSE SX;/* CLOSE the cursor SX and no longer match the query result */
Exec SQL COMMIT WORK;/* submit updates */
Exec SQL DISCONNECT TEST;/* DISCONNECT the database */
}

2. Use MS-DOS for pre-Compilation:

1) enter the Binn file: cd C: \ Program Files \ Microsoft SQL Server \ MSSQL \ Binn

2. Call the precompiled program nsqlprep.exe program: nsqlprep EXEC

Result of successful compilation:

See Appendix 1.

 

3. Use Vc ++ for compilation. See Appendix 2.

4. Add a library file: Project-> Settings-> Link-> Object/Library module

Enter two file names: SQLakw32.lib Caw32.lib. Appendix 3 Note that the two files are separated by spaces.5. copy the dynamic link library SQLakw32.dll and sqlaiw32.dll to the subdirectory C: \ WINDOWS \ system32 in the operating system directory.

6. Connection: attachment 4

7. Execution: Appendix 5

Appendix 1:

Attachment 2 (VC-compiled EXEC. C)

Attachment 3 (configure to add a library file)

Attachment 4 (connection)

Trackback: http://tb.blog.csdn.net/TrackBack.aspx? PostId = 1930960

Cursor Application

-- Use sms
-- Goto
-- Define a cursor
/*
Declare cursor name cursor
[Local | clobal]
[Forward_only | scroll]
[Static | keyset | dynamic | fast_forward]
[Read_only | scroll_locks | optimistic]
[Type_warning]
For select statement
[For update [for field name] [,... n]
Understand meaning based on help
*/
Declare cj_cursor cursor
Scroll
For
Select zkzh ID card number, cj33 written test, cj44 computer, zcj total score from ncre26

-- Open the cursor

Open cj_cursor

-- Extract a row of records from the cursor
-- Because the scroll option is not specified, fetch next is the only available extraction Option
Fetch next from cj_cursor
Fetch next from cj_cursor
Fetch next from cj_cursor
Fetch next from cj_cursor
Fetch next from cj_cursor
Fetch prior from cj_cursor
Fetch first from cj_cursor
Fetch next from cj_cursor
Fetch absolute 3 from cj_cursor
Fetch relative-1 from cj_cursor
-- Check the rows
-- If @ cursor_rows> 0
Print 'Total' + convert (varchar, @ cursor_rows)
-- Close the cursor
Close cj_cursor
-- Delete a cursor
Deallocate cj_cursor
 

Simple Application of triggers

1. Define the data environment: The teacher table records the instructor's situation, and the sal_log records the salary modification and addition of the teacher table.

Create database T
Go

Use T
Go

Create table teacher
(
Eno numeric (4) primary key,
EName varchar (8) unique,
PJob varchar (8 ),
Sal numeric (7,2)
);
Go

Create table Sal_log
(
Eno numeric (4) references teacher (eno ),
Sal Numeric (7,2 ),
USERNAME CHAR (10 ),
Date datetime );
Go

Ii. Define triggers

1. insert_sal trigger. When a new Meta Group is inserted into the teacher table, add a record to sal_log.

If exists (select * from sysobjects
Where name = 'insert _ sal 'and type = 'tr ')
Drop trigger insert_sal

Go

-- New trigger
Create trigger insert_sal
On teacher
For insert
Set Nocount off
Declare @ new_eno numeric (4 ),
@ New_sal numeric (7,2)
-- If @ rowcount = 1
Begin
Select @ new_eno = eno, @ new_sal = sal from inserted
Insert into sal_log
Values (@ new_eno, @ new_sal, current_user, current_timestamp)
End

2. update_sal trigger. When the salary of the teacher table is modified, add the two tuples before and after modification to sal_log.

Create trigger update_sal
On teacher
For update
Declare @ new_sal numeric (7,2 ),
@ Old_sal numeric (7,2 ),
@ Eno numeric (4)
If @ rowcount = 1
Begin
Select @ eno = eno, @ new_sal = sal from inserted
Select @ old_sal = sal from deleted
If (@ new_sal <> @ old_sal)
Insert
Sal_log values (@ eno, @ old_sal,
Current_user, current_timestamp );
Insert
Sal_log values (@ eno, @ new_sal,
Current_user, current_timestamp );
End

3. Define the trigger insert_sal_little. When adding a record to the teacher, the minimum wage should be 4000 if it is detected as a professor.

If exists (select * from sysobjects
Where name = 'insert _ sal_little 'and type = 'tr ')
Drop trigger insert_sal_little

Go

-- New trigger
Create trigger insert_sal_little
On teacher
For insert, update
Set Nocount off
Declare @ new_eno numeric (4 ),
@ New_sal numeric (7,2)
-- If @ rowcount = 1
Begin
Select @ new_eno = eno, @ new_sal = sal from inserted
If (@ new_sal-4000.00 <0)
Begin
Update teacher set sal = 4000.00
Where eno = @ new_eno
End
End

Iii. Detection

-- Inser_salt trigger Detection

Insert into teacher values (2112, 'lil', 'Professor ', 6000)
Go

Select * from teacher
Select * from sal_log

-- Detect update_sal triggers --

Update teacher set sal = 7300
Where eno = 2112

Select * from teacher
Select * from sal_log

-- Detect the inser_sal_littlet trigger

Insert into teacher values (2113, 'wang ', 'Professor', 3000)
Go

Select * from teacher
Select * from sal_log