Shulou(Shulou.com)05/31 Report--

This article shares with you the content of a sample analysis of database stored procedures. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

/ * stored procedures can be seen as stored t-sql scripts in the database why to use stored procedures 1, increase performance local storage sends less content, call fast, precompile, cache the execution of general statements: check permissions, check syntax, establish requirements for execution plan processing statements stored procedures: syntax has been checked at creation time The execution plan is created and compiled the first time it is executed When executing again, there is no need to recheck syntax, no need to recompile, to decide whether or not to recreate the execution plan according to the cached plan, enhanced security encryption, separation (permission setting, the user only needs to have the right to execute the stored procedure. Do not need permission to access objects used by stored procedures) 3. Use non-database technology dll4 in transact-sql, programming mode-use external programming language to call 1) input 2) output 3) feedback status code or descriptive text 4) Modularization, reusability, can call other stored procedures 5) hide program logic Easy to program 6) can call dynamic link libraries (external programs) basic principles: the simpler the better single task * / / * classification 1, system stored procedures exist in the master database It generally starts with sp_ to provide support for system tabular data calls, database management functions, security management functions-form authorization use pubs go execute sp_table_privileges stores-displays all processes execute sp_who @ loginame='W2K3SERVER\ Administrator' of kylin\ administrator-reports information about orphaned microsoft windows nt users and groups that are no longer in the windows nt environment but still have entries in the microsoft sql server system tables. Execute sp_validatelogins2, local stored procedures created by users to solve specific problems 3. Temporary stored procedures are stored in tempdb creation, database usage life cycle when calling # local unlimited database creation connections are valid from the time of creation Disappear when the connection created disappears # # global all connections to the database start at the time of creation, disappear when the connection created is broken and directly create the stored procedure tempdb in tempdb all connections start at the time of creation Disappear create proc # local as select'# local' go exec # local go create proc # # global as select'# global' go exec # # global go use tempdb go create procedure directtemp as select * from [pubs]. [authors] go use northwind go exec tempdb.dbo.directtemp4, extended stored procedure C++ xp xp_sendmail are both system stored procedures when the database server service stops It is also the extended stored procedure that uses objectproperty to determine whether it is an extended stored procedure use master-- extended stored procedure select objectproperty (object_id ('sp_prepare'),' isextendedproc')-- non-extended stored procedure select objectproperty (object_id ('xp_logininfo'),' isextendedproc') 5. Remote stored procedures are only for backward compatibility in the current version Has been replaced by distributed query * / / * how the stored procedure stores the name sysobjects text syscomments * / / * exercise 1: view the stored procedure through the object Viewer in query Analyzer * / / * exercise 2: view the content graphic statement of the stored procedure * / select * from sysobjectsselect * from syscomments goselect * from syscomments where id = object_id ('custorderhist') goselect name Textfrom sysobjects inner join syscomments on sysobjects.id = syscomments.idwhere sysobjects.name = 'custorderhist'gosp_helptext sp_helptextgouse northwindgoexec sp_help custorderhistexec sp_helptext custorderhistexec sp_depends custorderhistexec sp_stored_procedures' custorderhist' / * system stored procedure is mainly used * / / * creation, modification and deletion of local stored procedure 1, t-sql statement create procedurealter proceduredrop procedurecreate procedure stored procedure name as stored procedure text go alter procedure stored procedure name as stored procedure name 2, Enterprise Manager right-click wizard * / / * simple * /-- select top 1 * from products-- select top 1 * from orders-- select top 1 * from [order details] / * 1, and View comparison * / alter proc sp_qry_salesdetailsasselect a.productid as item number A.productname as commodity name, b.unitprice as quantity, b.quantity as price, b.unitprice*b.quantity as amount, c.requireddate as sales time from [order details] as b join products as aon b.productid=a.productidjoin orders as con b.orderid=c.orderidgoprint 'test' execute sp_qry_salesdetails-- recursive algorithm-- View stored procedure function alter view v_qry_salesdetailsasselect a.productid as item number, a.productname as commodity name, b.unitprice as quantity B.quantity as price, b.unitprice*b.quantity as amount, c.requireddate as sales time from [order details] as b join products as aon b.productid=a.productidjoin orders as con b.orderid=c.orderidprint 'test' select * from v_qry_salesdetails / * the execution plan of the first execution is saved by default This execution plan is used for later execution until the server is restarted or the table used by the stored procedure changes when the stored procedure changes, such as parameters Need to recompile and make a new execution plan: 1. Specify with recompile 2, sp_recompile * / create procedure sp1as select * from customersexec sp1alter procedure sp1as select * from customersalter procedure sp1with recompileas select * from customerssp_recompile sp1-- encrypted stored procedure with encryption select objectproperty (object_id ('sp_qry_salesdetails')) when creating. 'isencrypted') / * delete stored procedure dropproc * / use northwindgocreate proc dbo.sp_dropprocasselect' northwind.dbo.sp_dropproc'goexec northwind.dbo.sp_dropprocgouse mastergocreate proc dbo.sp_dropprocasselect 'master.dbo.sp_dropproc'goexec master.dbo.sp_dropprocgouse northwindgodrop proc sp_dropprocgoexec sp_dropprocexec master.dbo.sp_dropproc/* provides input parameters input*/create proc qry_salesdetails @ y int,@m int-- varchar (10) asselect a.productid as item number, a.productname as item name B.unitprice as quantity, b.quantity as price, b.unitprice*b.quantity as amount, c.requireddate as sales time from [order details] as b join products as aon b.productid=a.productidjoin orders as con b.orderid=c.orderid--where convert (varchar (2), month (c.requireddate)) = @ mwhere year (c.requireddate) = @ y and month (c.requireddate) = @ mgo exec qry_salesdetails 1996 9exec qry_salesdetails 910exec qry_salesdetails @ masks 914exec qry_salesdetails @ yearly 1996 The @ m=9go/*northwind database orders order details table * requires the stored procedure text encryption based on the order records of the specified user shown by ID between 1996-07-01 and 1997-07-01 * / use northwindgo-- creates the stored procedure-- drop proc qry_showorders create proc qry_showorders @ custid nchar (5) with encryption-- encryption asif @ custid is null-- begin-- print 'provides incorrect parameters'-- Return-- endselect * from orders od inner join [order details] oddton od.orderid = oddt.orderidwhere shippeddate > = '1996-07-01' and shippeddate

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.