1107c# Foundation--statement, stored procedure of database

"Follow-up practice"-Query the ' Li number ' teacher to teach math scores more than 80 points of information, and the number of more than 3 words, output standards; otherwise the output is not standard.

stored Procedure -a program code block with parameters---represents the execution of commands stored in the database, stored code, not called

1 、--Create a stored procedure

Create procedure--(abbreviated to proc)--+ stored procedure name-----stored procedure format

@bianliang1--+ Data type----As and stored procedure names can be parameter parameters (passed formal parameters)

@bianliang2--+ Data types

As

Begin

You can define variables----temporary parameters.

Statement

End

2 、--Execute the stored procedure:

Method 1: Programmability--Stored procedures--Executing stored procedures

The default automatic definition variable accepts the return value.

"Note" Whether successful or unsuccessful, comes with a return value of success or failure,

Returns 0 for execution success.

Method 2: How to write code to execute a stored procedure

Exec (Execute, execute) procedure

"Note" The difference between writing code and Method 1 is that because no variable is defined to accept the return value will not automatically return, want to get the result of the variable, you need to define a variable to receive stored procedures to print.

3 、--Modifying stored procedures

ALTER PROC FIRSTPROC------format with modified table

As

Select code,chinese,math,english, name from Score,student where score,student--writes a stored procedure (querying a table) can not write begin. End

Go

Exec Firstproc

4 、--Query Multiple tables must be added begin. End

Create proc Secondproc
As
Begin
Select *from Score
Select *from Student
Select *from Teacher
End
Go
EXEC Secondproc

5 stored procedures for 、--plus statements

6 、--stored procedure with parameters (emphasis)

Format:

Create proc Fourproc--Creating a stored procedure
@name varchar (20)--There are multiple variables, the data type followed by commas--can be added parameter
As
Begin--begin is equivalent to the left side of curly braces
declare @counts int, @kecheng varchar (20)--Define temporary variable + data type
Select @counts =count (*) from teacher where[email protected]--Statement execution
If @counts = 0--Add a branch statement
Begin
print ' didn't find this teacher '
End
Else
Begin
Select @kecheng =course from teacher where[email protected]
DECLARE @count int
If @kecheng = ' language '
Begin
Select @count =count (*) from score where Stucode in (
Select code from Student where chteacher= (select code from teacher where[email protected])
) and chiese>=80
End
If @kecheng = ' math '
Begin
Select @count =count (*) from score where Stucode in (
Select code from Student where mateacher= (select code from teacher where[email protected])
) and math>=80
End
If @kecheng = ' English '
Begin
Select @count =count (*) from score where Stucode in (
Select code from Student where enteacher= (select code from teacher where[email protected])
) and english>=80
End
If @count >=3
Begin
print ' compliance '--printing stored procedures
End
Else
Begin
print ' Not compliant '
End
End
End
Go
exec fourproc @name = ' Mo Yan 1 '

"Practice with the Church"

7 、--return to the worthwhile stored procedure using return

"Practice 1"

"Practice 2"

8 、--stored procedure with return value, return parameter, input parameter

9 、--Delete a stored procedure

Drop proc Fiveproc (+ stored procedure name)

"After-school practice"--practice topics
/*
Create a cargo table: number, goods name, unit, price, quantity in stock, notes
10 New data
After, the incoming, if already has this goods, increases the quantity, otherwise, adds into the database table
Shipment, if someone wants goods, judge whether the quantity is sufficient, enough to reduce inventory, otherwise tell insufficient
*/

1107c# Foundation--statement, stored procedure of database