StudyGroup
/
SQLServerClrDemo
2
0
Fork 0
Code Issues Pull Requests Releases Wiki Activity
You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
SQLServerClrDemo/ClrDemoNet35 SQL参考.md

272 lines
6.8 KiB
Markdown
Raw Permalink Normal View History Unescape Escape

添加项目
3 years ago
# ʾ<><CABE><EFBFBD>ο<EFBFBD>
## <20><><EFBFBD><EFBFBD>CLR<4C><52><EFBFBD><EFBFBD>
``` SQL
GO
-- <20><><EFBFBD><EFBFBD>CLR<4C><52><EFBFBD>ܿ<EFBFBD><DCBF><EFBFBD>
EXEC sp_configure 'clr enabled', 1;
-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ч
GO
RECONFIGURE;
```
## <20><><EFBFBD><EFBFBD>Ȩ<EFBFBD><C8A8>
+ <20><><EFBFBD><EFBFBD>һ<EFBFBD><D2BB><EFBFBD>ر<EFBFBD>"<22>ϸ<EFBFBD><CFB8><EFBFBD>ȫ"ѡ<><D1A1>
```SQL
-- <20><><EFBFBD>ø߼<C3B8>ѡ<EFBFBD><EFBFBD><EEA3AC><EFBFBD><EFBFBD>"clr strict security"ѡ<><D1A1><EFBFBD><EFBFBD>Ч
EXEC sys.sp_configure @configname = 'show advanced options', @configvalue = 1;
GO
-- <20><>Ч
RECONFIGURE;
GO
-- <20>ر<EFBFBD>"<22>ϸ<EFBFBD><CFB8><EFBFBD>ȫ"ѡ<><D1A1>
EXEC sys.sp_configure @configname = 'clr strict security', @configvalue = 0;
RECONFIGURE;
```
+ <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>򼯴<EFBFBD>ǩ<EFBFBD><C7A9>
```SQL
-- δ<><CEB4>֤
```
+ <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>򼯼<EFBFBD><F2BCAFBC><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
```SQL
-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Hashֵ<68><D6B5><EFBFBD><EFBFBD>
DECLARE @hash AS BINARY(64);
-- <20><>ȡ<EFBFBD><C8A1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ļ<EFBFBD><C4BC><EFBFBD>hashֵ
SELECT @hash = HASHBYTES('SHA2_512', (SELECT * FROM OPENROWSET (BULK 'D:\\Database\\SQL Server\\ClrDemo\\ClrDll\\ClrDemoNet35.dll', SINGLE_BLOB) AS [Data]));
-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>򼯰<EFBFBD><F2BCAFB0><EFBFBD><EFBFBD><EFBFBD>
EXEC sp_add_trusted_assembly @hash;
```
## <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
+ <20><><EFBFBD>س<EFBFBD><D8B3><EFBFBD><EFBFBD><EFBFBD>
+ <20><>ʽ1<CABD><31>ʹ<EFBFBD>ó<EFBFBD><C3B3><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ļ<EFBFBD>
```SQL
CREATE ASSEMBLY ClrDemoNet35 FROM 'D:\\Database\\SQL Server\\ClrDemo\\ClrDll\\ClrDemoNet35.dll';
-- <20><><EFBFBD><EFBFBD>
CREATE ASSEMBLY ClrDemoNet35 FROM 'D:\\Database\\SQL Server\\ClrDemo\\ClrDll\\ClrDemoNet35.dll' WITH PERMISSION_SET = UNSAFE;
```
+ <20><>ʽ2<CABD><32>ʹ<EFBFBD><CAB9><EFBFBD>ļ<EFBFBD>16<31><36><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
```SQL
-- <20><><EFBFBD><EFBFBD>0x12334<33><34><EFBFBD><EFBFBD><EFBFBD>dz<EFBFBD><C7B3><EFBFBD><EFBFBD><EFBFBD>dll<6C>ļ<EFBFBD><C4BC><EFBFBD>ʮ<EFBFBD><CAAE><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʹ<EFBFBD><CAB9>UltraEdit<69>ȱ༭<C8B1><E0BCAD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>dll<6C>ļ<EFBFBD><C4BC><EFBFBD>ʮ<EFBFBD><CAAE><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>copy<70><79><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
CREATE ASSEMBLY AssemblyName from 0x123344
```
+ <20><>ʽ3<CABD><33>SSMSͼ<53>λ<EFBFBD><CEBB><EFBFBD><EFBFBD><EFBFBD>
```SQL
<09><><EFBFBD>ݿ<EFBFBD> --> <20>ɱ<EFBFBD><C9B1><EFBFBD><EFBFBD><EFBFBD> --> <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD> --> <20>Ҽ<EFBFBD> --> <20>½<EFBFBD><C2BD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
```
+ <20><><EFBFBD>³<EFBFBD><C2B3><EFBFBD><EFBFBD><EFBFBD>
+ <20><>ʽ1<CABD><31>ʹ<EFBFBD>ó<EFBFBD><C3B3><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ļ<EFBFBD>
```SQL
ALTER ASSEMBLY ClrDemoNet35 FROM 'D:\\Database\\SQL Server\\ClrDemo\\ClrDll\\ClrDemoNet35.dll';
-- <20><><EFBFBD><EFBFBD>
ALTER ASSEMBLY ClrDemoNet35 FROM 'D:\\Database\\SQL Server\\ClrDemo\\ClrDll\\ClrDemoNet35.dll' WITH PERMISSION_SET = UNSAFE;
```
+ <20><>ʽ2<CABD><32>ʹ<EFBFBD><CAB9><EFBFBD>ļ<EFBFBD>16<31><36><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
```SQL
-- <20><><EFBFBD><EFBFBD>0x12334<33><34><EFBFBD><EFBFBD><EFBFBD>dz<EFBFBD><C7B3><EFBFBD><EFBFBD><EFBFBD>dll<6C>ļ<EFBFBD><C4BC><EFBFBD>ʮ<EFBFBD><CAAE><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʹ<EFBFBD><CAB9>UltraEdit<69>ȱ༭<C8B1><E0BCAD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>dll<6C>ļ<EFBFBD><C4BC><EFBFBD>ʮ<EFBFBD><CAAE><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>copy<70><79><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
ALTER ASSEMBLY AssemblyName from 0x123344
```
+ <20><>ʽ3<CABD><33>SSMSͼ<53>λ<EFBFBD><CEBB><EFBFBD><EFBFBD><EFBFBD>
```SQL
<09><><EFBFBD>ݿ<EFBFBD> --> <20>ɱ<EFBFBD><C9B1><EFBFBD><EFBFBD><EFBFBD> --> <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD> --> <20>Ҽ<EFBFBD> --> ˢ<><CBA2>
```
+ ɾ<><C9BE><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
+ <20><>ʽ1<CABD><31>ʹ<EFBFBD><CAB9>TSQL
```SQL
-- 1<><31>ɾ<EFBFBD><C9BE><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>洢<EFBFBD><E6B4A2><EFBFBD>̡<EFBFBD><CCA1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>͵<EFBFBD>
-- 2<><32>ɾ<EFBFBD><C9BE><EFBFBD><EFBFBD><EFBFBD>򼯣<EFBFBD>
DROP ASSEMBLY AssemblyName
```
+ <20><>ʽ2<CABD><32>SSMSͼ<53>λ<EFBFBD><CEBB><EFBFBD><EFBFBD><EFBFBD>
```SQL
<09><><EFBFBD>ݿ<EFBFBD> --> <20>ɱ<EFBFBD><C9B1><EFBFBD><EFBFBD><EFBFBD> --> <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD> --> <20>Ҽ<EFBFBD> --> ɾ<><C9BE>
```
## <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ô洢<C3B4><E6B4A2><EFBFBD><EFBFBD>
``` SQL
-- ===========================================
-- ˵ <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ÿͻ<C3BF><CDBB>˷<EFBFBD><CBB7><EFBFBD><EFBFBD>ı<EFBFBD>
-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>UseClr
-- ===========================================
GO
-- <20><><EFBFBD><EFBFBD><EFBFBD>洢<EFBFBD><E6B4A2><EFBFBD><EFBFBD>
CREATE PROCEDURE Pro_ClrDemoNet35_UseClr
AS
EXTERNAL NAME [ClrDemoNet35].[ClrDemoNet35.ClrDemo].[UseClr];
GO
-- <20><><EFBFBD>ô洢<C3B4><E6B4A2><EFBFBD><EFBFBD>
EXEC dbo.Pro_ClrDemoNet35_UseClr;
```
``` SQL
-- ===========================================
-- ˵ <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ȡһ<C8A1><D2BB><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>GetRandom
-- ===========================================
GO
-- <20><><EFBFBD><EFBFBD><EFBFBD>洢<EFBFBD><E6B4A2><EFBFBD><EFBFBD>
CREATE PROCEDURE Pro_ClrDemoNet35_GetRandom
AS
EXTERNAL NAME [ClrDemoNet35].[ClrDemoNet35.ClrDemo].[GetRandom];
GO
-- <20><><EFBFBD>ô洢<C3B4><E6B4A2><EFBFBD><EFBFBD>
EXEC dbo.Pro_ClrDemoNet35_UseClr;
```
``` SQL
-- ===========================================
-- ˵ <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
-- ʹ<><CAB9> SqlContext.Pipe.ExecuteAndSend ֱ<>ӷ<EFBFBD><D3B7><EFBFBD> SqlCommand <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͻ<EFBFBD><CDBB><EFBFBD>(<28>洢<EFBFBD><E6B4A2><EFBFBD>̽<EFBFBD><CCBD><EFBFBD>)
-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ReturnCount
-- ===========================================
GO
-- <20><><EFBFBD><EFBFBD><EFBFBD>洢<EFBFBD><E6B4A2><EFBFBD><EFBFBD>
CREATE PROCEDURE Pro_ClrDemoNet35_ReturnCount
AS
EXTERNAL NAME [ClrDemoNet35].[ClrDemoNet35.ClrDemo].[ReturnCount];
GO
-- <20><><EFBFBD>ô洢<C3B4><E6B4A2><EFBFBD><EFBFBD>
EXEC dbo.Pro_ClrDemoNet35_ReturnCount;
```
``` SQL
-- ===========================================
-- ˵ <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ȡ<EFBFBD><C8A1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,out<75><74><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ
-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>GetCount
-- ===========================================
GO
-- <20><><EFBFBD><EFBFBD><EFBFBD>洢<EFBFBD><E6B4A2><EFBFBD><EFBFBD>
CREATE PROCEDURE Pro_ClrDemoNet35_GetCount
(
@totalCount int output
)
AS
EXTERNAL NAME [ClrDemoNet35].[ClrDemoNet35.ClrDemo].[GetCount];
GO
-- <20><><EFBFBD>ô洢<C3B4><E6B4A2><EFBFBD><EFBFBD>
EXEC dbo.Pro_ClrDemoNet35_GetCount;
```
``` SQL
-- ===========================================
-- ˵ <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ѯ<EFBFBD><D1AF><EFBFBD>ݱ<EFBFBD>
-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>GetStudents
-- ===========================================
GO
-- <20><><EFBFBD><EFBFBD><EFBFBD>洢<EFBFBD><E6B4A2><EFBFBD><EFBFBD>
CREATE PROCEDURE Pro_ClrDemoNet35_GetStudents
AS
EXTERNAL NAME [ClrDemoNet35].[ClrDemoNet35.ClrDemo].[GetStudents];
GO
-- <20><><EFBFBD>ô洢<C3B4><E6B4A2><EFBFBD><EFBFBD>
EXEC dbo.Pro_ClrDemoNet35_GetStudents;
```
``` SQL
-- ===========================================
-- ˵ <20><><EFBFBD><EFBFBD>ʹ<EFBFBD><CAB9> SqlDataRecord <20><><EFBFBD>͵<EFBFBD><CDB5><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>UseSqlDataRecord
-- ===========================================
GO
-- <20><><EFBFBD><EFBFBD><EFBFBD>洢<EFBFBD><E6B4A2><EFBFBD><EFBFBD>
CREATE PROCEDURE Pro_ClrDemoNet35_UseSqlDataRecord
AS
EXTERNAL NAME [ClrDemoNet35].[ClrDemoNet35.ClrDemo].[UseSqlDataRecord];
GO
-- <20><><EFBFBD>ô洢<C3B4><E6B4A2><EFBFBD><EFBFBD>
EXEC dbo.Pro_ClrDemoNet35_UseSqlDataRecord;
```
``` SQL
-- ===========================================
-- ˵ <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Զ<EFBFBD><D4B6><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
-- ʹ<><CAB9> SendResultsStart SendResultsRow SendResultsEnd
-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>UseSendResultsRow
-- ===========================================
GO
-- <20><><EFBFBD><EFBFBD><EFBFBD>洢<EFBFBD><E6B4A2><EFBFBD><EFBFBD>
CREATE PROCEDURE Pro_ClrDemoNet35_UseSendResultsRow
AS
EXTERNAL NAME [ClrDemoNet35].[ClrDemoNet35.ClrDemo].[UseSendResultsRow];
GO
-- <20><><EFBFBD>ô洢<C3B4><E6B4A2><EFBFBD><EFBFBD>
EXEC dbo.Pro_ClrDemoNet35_UseSendResultsRow;
```
``` SQL
-- ===========================================
-- ˵ <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ѧУѧ<D0A3><D1A7><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>SummaryData
-- ===========================================
GO
-- <20><><EFBFBD><EFBFBD><EFBFBD>洢<EFBFBD><E6B4A2><EFBFBD><EFBFBD>
CREATE PROCEDURE Pro_ClrDemoNet35_SummaryData
AS
EXTERNAL NAME [ClrDemoNet35].[ClrDemoNet35.ClrDemo].[SummaryData];
GO
-- <20><><EFBFBD>ô洢<C3B4><E6B4A2><EFBFBD><EFBFBD>
EXEC dbo.Pro_ClrDemoNet35_SummaryData;
```
``` SQL
-- ===========================================
-- ˵ <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ѧ<EFBFBD><D1A7><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,ֱ<>ӷ<EFBFBD><D3B7>ؽ<EFBFBD><D8BD><EFBFBD>
-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>SummaryStudentCount
-- ===========================================
GO
-- <20><><EFBFBD><EFBFBD><EFBFBD>洢<EFBFBD><E6B4A2><EFBFBD><EFBFBD>
CREATE PROCEDURE Pro_ClrDemoNet35_SummaryStudentCount
AS
EXTERNAL NAME [ClrDemoNet35].[ClrDemoNet35.ClrDemo].[SummaryStudentCount];
GO
-- <20><><EFBFBD>ô洢<C3B4><E6B4A2><EFBFBD><EFBFBD>
EXEC dbo.Pro_ClrDemoNet35_SummaryStudentCount;
```
## <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Զ<EFBFBD><D4B6>庯<EFBFBD><E5BAAF>
``` SQL
-- ===========================================
-- ˵ <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,<2C><>ѯָ<D1AF><D6B8>ѧУ<D1A7><D0A3>ѧ<EFBFBD><D1A7><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>QuerySchoolStudentCount
-- ===========================================
CREATE FUNCTION dbo.Fun_ClrDemoNet35_QuerySchoolStudentCount
(
@schoolId INT
)
RETURNS INT
AS
EXTERNAL NAME [ClrDemoNet35].[ClrDemoNet35.ClrDemo].[QuerySchoolStudentCount]
GO
-- <20><><EFBFBD>ú<EFBFBD><C3BA><EFBFBD>
SELECT [dbo].[Fun_ClrDemoNet35_QuerySchoolStudentCount] (1)
GO
```
``` SQL
-- ===========================================
-- ˵ <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><D6B5><EFBFBD><EFBFBD>:<3A><><EFBFBD><EFBFBD>һ<EFBFBD><D2BB><EFBFBD><EFBFBD><EFBFBD>ݼ<EFBFBD><DDBC>ϣ<EFBFBD><CFA3><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>һ<EFBFBD><D2BB><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ݵķ<DDB5><C4B7><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>FillRowMethodName<6D><65><EFBFBD><EFBFBD>ʾ<EFBFBD><CABE><EFBFBD>ҷ<EFBFBD><D2B7><EFBFBD>ֵӦ<D6B5><D3A6>ΪIEnumerable<6C><65><EFBFBD><EFBFBD>
-- <20><>ѯָ<D1AF><D6B8>ѧУ<D1A7><D0A3><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ѧ<EFBFBD><D1A7>
-- <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>QueryStudent
-- ===========================================
CREATE FUNCTION dbo.Fun_ClrDemoNet35_QueryStudent()
RETURNS TABLE
(
Id INT,
SchoolName NVARCHAR(50),
ClassNumber INT,
StudentNumber INT
)
EXTERNAL NAME [ClrDemoNet35].[ClrDemoNet35.ClrDemo].[QueryStudent]
GO
-- <20><><EFBFBD>ú<EFBFBD><C3BA><EFBFBD>
SELECT * FROM dbo.Fun_ClrDemoNet35_QueryStudent();