SQL Server´æ´¢¹ý³ÌµÄÃüÃû±ê×¼
ÔÚ±¾ÎÄÖУ¬´ËʾÀý±ê×¼À¶Í¼µÄ´æ´¢¹ý³ÌÃüÃû·½·¨Ö»ÊÊÓÃÓÚSQLÄÚ²¿£¬¼ÙÈçÄãÕýÔÚ´´½¨Ò»¸öеĴ洢¹ý³Ì£¬»òÊÇ·¢ÏÖÒ»¸öûÓа´ÕÕÕâ¸ö±ê×¼¹¹ÔìµÄ´æ´¢¹ý³Ì£¬¼´¿ÉÒԲο¼Ê¹ÓÃÕâ¸ö±ê×¼¡£
×¢ÊÍ£º¼ÙÈç´æ´¢¹ý³ÌÒÔsp_ Ϊǰ׺¿ªÊ¼ÃüÃûÄÇô»áÔËÐеÄÉÔ΢µÄ»ºÂý£¬ÕâÊÇÒòΪSQL Server½«Ê×ÏȲéÕÒϵͳ´æ´¢¹ý³Ì£¬ËùÒÔÎÒÃǾö²»ÍƼöʹÓÃsp_×÷Ϊǰ׺¡£
´æ´¢¹ý³ÌµÄÃüÃûÓÐÕâ¸öµÄÓï·¨£º
[proc] [MainTableName] By [FieldName(optional)] [Action]
[ 1 ] [ 2 ] [ 3 ]¡¡¡¡[ 4 ]
(1) ËùÓеĴ洢¹ý³Ì±ØÐëÓÐǰ׺'proc'. ËùÓеÄϵͳ´æ´¢¹ý³Ì¶¼ÓÐǰ׺"sp_", ÍƼö²»Ê¹ÓÃÕâÑùµÄǰ׺ÒòΪ»áÉÔ΢µÄ¼õÂý¡£
(2) ±íÃû¾ÍÊÇ´æ´¢¹ý³Ì·ÃÎʵĶÔÏó¡£
(3) ¿ÉÑ¡×Ö¶ÎÃû¾ÍÊÇÌõ¼þ×Ӿ䡣 ÀýÈ磺
procClientByCoNameSelect, procClientByClientIDSelect
(4) ×îºóµÄÐÐΪ¶¯´Ê¾ÍÊÇ´æ´¢¹ý³ÌÒªÖ´ÐеÄÈÎÎñ¡£
Èç¹û´æ´¢¹ý³Ì·µ»ØÒ»Ìõ¼Ç¼ÄÇôºó׺ÊÇ£ºSelect
Èç¹û´æ´¢¹ý³Ì²åÈëÊý¾ÝÄÇôºó׺ÊÇ£ºInsert
Èç¹û´æ´¢¹ý³Ì¸üÐÂÊý¾ÝÄÇôºó׺ÊÇ£ºUpdate
Èç¹û´æ´¢¹ý³ÌÓвåÈëºÍ¸üÐÂÄÇôºó׺ÊÇ£ºSave
Èç¹û´æ´¢¹ý³Ìɾ³ýÊý¾ÝÄÇôºó׺ÊÇ£ºDelete
Èç¹û´æ´¢¹ý³Ì¸üбíÖеÄÊý¾Ý (ie. drop and create) ÄÇôºó׺ÊÇ£ºCreate
Èç¹û´æ´¢¹ý³Ì·µ»ØÊä³ö²ÎÊý»ò0£¬ÄÇôºó׺ÊÇ£ºOutput
Àý×Ó£º
Ò»¸ö½ö½ö·µ»ØÒ»¸öÊä³ö²ÎÊýµÄ´æ´¢¹ý³Ì:
ALTER PROCEDURE procClientRateOutput
@pstrClientID VARCHAR(6) = 'CABLE',
@pstrCategoryID VARCHAR(6) = '<All>',
@pstrEmpID VARCHAR(6)='AC',
@pdteDate datetime = '1996/1/1',
@curRate MONEY OUTPUT
AS
-- Description: Get the $Rate for this client and this employee
-- and this category from Table ClientRate
SET @curRate = (
SELECT TOP 1 Rate
from ClientRate
&n
Ïà¹ØÎĵµ£º
1.´ò¿ªSQL server enterprise mananger “ÆóÒµ¹ÜÀíÆ÷”
ÔÚÄãÒªµ¼³öµÄSQLÊý¾Ý¿âÉÏÊó±êÓÒ¼ü²Ëµ¥£ºËùÓÐÈÎÎñ-¡·µ¼³öÊý¾Ý
2.»Ø³öÏÖÒ»¸öµ¼³öÏòµ¼´°¿Ú¡£
Ñ¡Ôñ±»µ¼³öµÄÊý¾ÝÔ´£¬ÎªÄã¸Õ²ÅËùÑ¡ÔñµÄÊý¾Ý¿â£¬Èç¹û·¢ÏÖ²»¶ÔÓ¦×ÔÐÐÐ޸ġ£
3.½øÈëµ¼³öµ½Ä¿±êÊý¾ÝÔ´µÄÑ¡Ôñ£¬ÕâÀïÎÒÃÇҪת³ÉACCESSµÄÊý¾Ý¿â¡£×¢ÒâÑ¡ÔñÊý¾ÝÔ´ÀàÐÍÎ ......
import java.sql.*;
/*
* JAVAÁ¬½ÓACCESS£¬SQL Server,MySQL,OracleÊý¾Ý¿â
*
* */
public class JDBC {
public static void main(String[] args)throws Exception {
Connection conn=null;
//====Á¬½ÓACCESSÊý¾Ý¿â ......
create proc [dbo].[GenerateDataDictionary] as
begin
--»ñÈ¡Êý¾Ý±íÃû
declare @tableid int
declare mycursor Cursor
for select object_id from sys.objects where type='U' and name<>'dtproperties'
--»ñÈ¡×Ö¶ÎÃû³Æ¡¢±êʶ¡¢×Ö¶ÎÐòºÅ¡¢Õ¼ÓÃ×Ö½ÚÊý¡¢Ð¡ÊýλÊý¡¢ÔÊÐí¿Õµ ......
select * from student where name=?;
Èç¹û²»Óõ¥ÒýºÅÒýÆðÀ´£¬ pstmt.setString(1,"xx or 1=1");¼´sqlÓ¦¸Ã¾ÍÊÇselect * from student where name=xx or 1=1¾Í¿ÉÒÔÈ«²¿²é³ö¡£
Ç¿ÖƵ¥ÒýºÅÒýÆðÀ´£¬select * from student where name='xx or 1=1'¡£¾ÍÎÞЧÁË¡£
ÊýÖµÐ͵ÄûÓÐÒªÇóÓõ¥ÒýºÅÒýÆðÀ´£¬Ó¦¸ÃÊÇÓÉÓÚÓÐÒ»¸öת»»¹ý ......
Select * into customers from clients
(Êǽ«clients±íÀïµÄ¼Ç¼²åÈëµ½customersÖУ¬ÒªÇó£ºcustomers±í²»´æÔÚ£¬ÒòΪÔÚ²åÈëʱ»á×Ô¶¯´´½¨Ëü£»)
Insert into customers select * from clients
½â£ºInsert into customers select * from clients£©ÒªÇóÄ¿±ê±í£¨customers£©´æÔÚ£¬
ÓÉÓÚÄ¿±ê±íÒѾ´æÔÚ£¬ËùÒÔÎÒÃdzýÁ˲åÈëÔ´±í£ ......