14. SQL Basic collation (stored procedure)

Stored procedure procedure (proc)

Database-programmability-stored procedures

To create a new stored procedure:

Create proc Firstproc

As

Select *from Fenshu

Go

To execute a stored procedure:

Stored procedure-Right-executes stored procedure

DECLARE @fanhuizhi int

exec @fanhuizhi = Firstproc

Select ' Return Value ' = @fanhuizhi

Return value execution success/failure (0 is successful)

Execute/exec Firstproc

modifying stored procedures

ALTER PROC Firstproc

As

Select Yuwen,shuxue,yingyu,name from Student,fenshu where Student.code = Fenshu.code

Go

Execute Firstproc

Querying multiple tables

Create proc Secondproc

As

Begin

Select*from Student

Select*from Teacher

Select*from Fenshu

End

Query process for a tape query

Create proc Threeproc

As

Begin

DECLARE @count int

Select @count =count (*) from (select *from Student where code = ANY (select code from Fenshu where code in

(select code from student where MT = (select code from teacher where name = ' Number One ')) and shuxue>80)

) as New

If @count > 3

print ' standards '

Else

print ' Not compliant '

End

EXEC Threeproc

Query procedure with parameters

Create proc Fourthproc

@hello varchar (20),

@ercan varchar-Parameters Section

As

Begin

Print @[email protected]

End

Go

exec fourthproc ' HelloWorld ', ' Hello world! ‘

Complex parameters

Create proc Fifthproc

@name varchar (20)

As

Begin

declare @countjs int, @lesson varchar (20)

Select @countjs = COUNT (*) from teacher where name = @name

If @countjs = 0

begin

print ' didn't find this teacher '

End

Else

begin

Select @lesson = course from teacher where name = @name

DECLARE @count int

If @lesson = ' language '

Begin

Select @count = count (*) from student where code = ANY (select code from Fenshu where code in

(select code from student where CT = (select code from teacher where name = @name)) and yuwen>80)

End

Else

If @lesson = ' math '

Begin

Select @count = count (*) from student where code = ANY (select code from Fenshu where code in

(select code from student where MT = (select code from teacher where name = @name)) and shuxue>80)

End

Else

If @lesson = ' English '

Begin

Select @count = count (*) from student where code = ANY (select code from Fenshu where code in

(select code from student where ET = (select code from teacher where name = @name)) and yingyu>80)

End

If @count >=3

print ' standards '

Else

print ' Not compliant '

End

End

Go

exec fifthproc (@name = can be ignored) ' number one '

EXEC fifthproc ' number '

To delete a stored procedure

drop proc Stored Procedure name

Practice

------------Enter the student's study number, see whether the completion of the graduation (all the courses are more than the distribution of excellent certificates, there are two courses or more fail to pass, there is a course failed to pass the graduation)------------

ALTER PROC Jieye

@xuehao int

As

Begin

DECLARE @shuxue int

DECLARE @yuwen int

DECLARE @yingyu int

DECLARE @zongfen int

Select @shuxue = count (*) from Fenshu where [email protected] and shuxue>80

Select @yuwen = count (*) from Fenshu where [email protected] and yuwen>80

Select @yingyu = count (*) from Fenshu where [email protected] and yingyu>80

Set @zongfen = @[email Protected][email protected]

If @zongfen = 3

print ' excellent '

If @zongfen = 2

print ' Graduation '

If @zongfen < 2

print ' Do not graduate '

End

Go

return value

-----------Enter a number so that its +10 return------define the variable to receive the value returned by the execution stored procedure

Create proc Jisuan

@sum int (can set default value @sum int=10)

As

Begin

Set @sum = @sum +10

Return @sum

End

DECLARE @shu int

exec @shu = Jisuan 2 (change 2 to default to print according to the output result of defaults)

Print @shu

---------return the number of total points and print them out-----------

Create proc Jieye2

@xuehao int

As

Begin

DECLARE @shuxue int

DECLARE @yuwen int

DECLARE @yingyu int

DECLARE @zongfen int

Select @shuxue = count (*) from Fenshu where [email protected] and shuxue>80

Select @yuwen = count (*) from Fenshu where [email protected] and yuwen>80

Select @yingyu = count (*) from Fenshu where [email protected] and yingyu>80

Set @zongfen = @[email Protected][email protected]

Return @zongfen

End

DECLARE @count int

exec @count = jieye2 1

Print @count

The stored procedure after return will no longer run (exceptions in sentences such as if)

Practice

--Enter a number n to n+n-1+......+1 and---

ALTER PROC Qiuhe

@n int

As

Begin

DECLARE @sum int =0

While @n>=1

Begin

Set @sum = @[email protected]

Set @n = @n-1

End

Return @sum

End

Go

DECLARE @n1 int

exec @n1 = Qiuhe 5

Print @n1

--------the stored procedure with return value, return parameter, input parameter--------

---Enter the study number and return to the results of the course

Create proc Sixproc

@yuwen decimal (18,2) output,

@shuxue decimal (18,2) output,

@yingyu decimal (18,2) output,

@code int

As

Begin

DECLARE @count int

Select @yuwen = Yuwen, @shuxue = Shuxue, @yingyu = Yingyu from Fenshu where code = @code

End

Go

---Define the value of the output parameter that the variable takes out of the stored procedure

DECLARE @yuwen decimal (18,2), @yingyu decimal (18,2), @shuxue decimal (18,2)

EXEC sixproc @yuwen output, @shuxue output, @yingyu output,1

Print @[email Protected][email protected]

----------------------------------------------

ALTER PROC Sixproc

@yuwen decimal (18,2) output,

@shuxue decimal (18,2) output,

@yingyu decimal (18,2) output,

@code int

As

Begin

DECLARE @count int

Select @count = count (*) from student where code = @code

Select @yuwen = Yuwen, @shuxue = Shuxue, @yingyu = Yingyu from Fenshu where code = @code

return @count

End

Go

DECLARE @yuwen decimal (18,2), @yingyu decimal (18,2), @shuxue decimal (18,2), @count int

exec @count = sixproc @yuwen output, @shuxue output, @yingyu output,15

Print @[email Protected][email protected]

Print @count

14. SQL Basic collation (stored procedure)