codestory

Die Anleitung zu SQL Server Transact-SQL

  1. Was ist Transact-SQL?
  2. Die Überblick von Transact-SQL
  3. Mit SQL Server Management Studio beginnen
  4. Die grundlegende Statement Transact-SQL 
  5. Die abgefragten Daten zu Variables zuweisen.
  6. Das besondere Datentypen in T-SQL
  7. Der Cursor
  8. Die Ausnahmebehandlung
  9. Die Funktion
  10. Das Verfahren
  11. Die Transaktion (Transaction)
  12. Trigger

1. Was ist Transact-SQL?

Transact-SQL (T-SQL sogenannt) ist eine verfahrenorientierte Programmierungssprache database von Microsoft Monopol, die im SQL Server benutzt wird.

Die Verfahrensprache wird entworfen um die Fähigkeit von SQL zu erweitern während sie sich gut in SQL integrieren kann. Einige Funktion wie die lokalen Variables und die Behandlung des String/Daten werden eingefügt. Diese Funktion macht die Sprache Transact-SQL als Turing-complete (**).

Sie werden auch benutzt um die Lagerungsverfahren zu schreiben: Ein Stück der Kode im Server zum Management der komplizierten Businessregelungen, die durch die pure set-based operations schwer oder unmöglich zu schaffen ist
Ein System Turing-Complete ist ein System, in dem ein Programm schrieben werden kann und das wird eine Anwort finden (obwohl mit keiner Sicherheiten über Runtime oder Speicherung).

2. Die Überblick von Transact-SQL

T-SQL wird in jedes Block vom Statement organisiert. Ein Statement Block kann in einem anderen Statement Block umarmen.Ein Statement Block startet mit BEGIN und endet mit END. Innerhalb des Statement gibt es viele Statement und die Statement trennen miteinander durch das Semikolon (;)
Die Struktur vom Block:
BEGIN
    -- Declare variables
    -- T-SQL Statements
END;

3. Mit SQL Server Management Studio beginnen

In diesem Dokument werde ich Sie bei der Programmierung von SQL Server in das visuelle Tool SQL Server Management Studio.
Das ist das Bild SQL Server Management Studio während Sie es geöffnet haben. Einige database sind verfügbar wenn Sie SQLServer voll installiert haben.
Oder Sie können learningsql erstellen, eine kleine Datenbank wird in einige Anleitungsartikel SQLServer in o7planning.orgbenutzt.
Rechtsklicken Sie auf eine database, wählen Sie "New Query" um eine Arbeitsfenster mit dieser database zu öffnen.
Sie sind bereit, database mit SQL Server zu programmieren.
Unten ist das einfache Statement Block : Die Summe von 2 Zahlen rechnen:
Begin

  -- Declaring a variable
  Declare @v_Result Int;
  -- Declaring a variable with a value of 50
  Declare @v_a Int = 50;
  -- Declaring a variable with a value of 100
  Declare @v_b Int = 100;


  -- Print out Console (For developer).
  -- Using Cast to convert Int to String
  -- Using + operator to concatenate 2 string
  Print 'v_a= ' + Cast(@v_a as varchar(15));

  -- Print out Console
  Print 'v_b= ' + Cast(@v_b as varchar(15));

  -- Sum
  Set @v_Result = @v_a + @v_b;

  -- Print out Console
  Print 'v_Result= ' + Cast(@v_Result as varchar(15));

End;
Klicken Sie auf das Icon um das Statement Block auszuführen und sehen Sie das Ergebnis in SQL Server Management Studio:

4. Die grundlegende Statement Transact-SQL 

Hier stelle ich die Überblick der grundlegenden Statement von T-SQL vor. Sie werden es durch die Beispiele in die nächste Teilen mehr verstehen.
Das Statement If-elsif-else
Die Syntax:
IF <condition 1> THEN
    Job 1;
[ELSIF <condition 2> THEN
     Job 2;
]
[ELSE
     Job n + 1;
]
END IF;
Zum Beispiel:
Else_If_Example
BEGIN

 -- Declare a variable
 DECLARE @v_Option integer;
 DECLARE @v_Action varchar(30);

 SET @v_Option = 2;

 IF @v_Option = 1
   SET @v_Action = 'Run';
 ELSE IF @v_Option = 2
     BEGIN
       PRINT 'In block else if @v_Option = 2';
       SET @v_Action = 'Backup';
     END;
 ELSE IF @v_Option = 3
   SET @v_Action = 'Stop';
 ELSE
   SET @v_Action = 'Invalid';


 -- Logging
 PRINT '@v_Action= ' + @v_Action;

END;
Das Ergebnis vom Laufen des Beispiel:
Die Schleife WHILE
Syntax:
LOOP
 -- Do something here
EXIT WHEN <Condition>;
END LOOP;
In die Schleife WHILE können Sie BREAK benutzen um aus der Schleife zu beenden.
Verwenden Sie das Statement CONTINUE um die Statement Linie ins Block WHILE und unter es zu ignorieren um eine neue Schleife fortzufahren.
While_Example1
BEGIN

 -- Declaring 2 variables x and y.
 DECLARE @x integer = 0;
 DECLARE @y integer = 10;

 -- Step
 DECLARE @step integer = 0;

 -- While @x < @y
 WHILE (@x < @y)
 BEGIN

   SET @step = @step + 1;

   -- Every time loop execute, x increases by 1.
   SET @x = @x + 1;
   -- Every time loop execute, x decreases by 2.
   SET @y = @y - 2;

   PRINT 'Step =' + CAST(@step AS varchar(10));
   PRINT '@x =' + CAST(@x AS varchar(10)) + ' / @y = ' + CAST(@y AS varchar(10));

 END;

 -- Write log
 PRINT 'x,y = ' + CAST(@x AS varchar(10)) + ', ' + CAST(@y AS varchar(10));

END;
Das Ergebnis vom Laufen des Beispiel:
BREAK ist das Statement, das erlaubt, aus der Schleife zu beenden. Unter ist das das Beispiel:
While_Example2
BEGIN

 -- Declaring 2 variables x and y
 DECLARE @x integer = 0;
 DECLARE @y integer = 10;

 -- Step
 DECLARE @step integer = 0;

 -- While @x < @y
 WHILE (@x < @y)
 BEGIN

   SET @step = @step + 1;

   -- Every time the loop execute, x increases by 1
   SET @x = @x + 1;
   -- Every time the loop execute, y decreases by 1
   SET @y = @y - 2;

   PRINT 'Step =' + CAST(@step AS varchar(10));
   PRINT '@x =' + CAST(@x AS varchar(10)) + ' / @y = ' + CAST(@y AS varchar(10));

   -- If @x > 2 then exit the loop
   -- (Although conditions in the WHILE is still true).
   IF @x > 2
     BREAK;

 END;

 -- Write log
 PRINT 'x,y = ' + CAST(@x AS varchar(10)) + ', ' + CAST(@y AS varchar(10));

END;
Das Ergebnis vom Laufen des Beispiel:
Das Statement CONTINUE erlaubt, die Statement unter es (noch in die Schleife) zu ignorieren um eine neue Schleife fortzufahren.
While_Example3
BEGIN

 -- Declaring 2 variables x and y.
 DECLARE @x integer = 0;
 DECLARE @y integer = 10;

 -- Step
 DECLARE @step integer = 0;

 -- While @x < @y
 WHILE (@x < @y)
 BEGIN

   SET @step = @step + 1;

   -- Every time the loop execute, x increases by 1
   SET @x = @x + 1;
   -- Every time the loop execute, x decreases by 2
   SET @y = @y - 2;

   -- If @x < 3 , then skip the statements below
   -- And continue new step
   IF @x < 3
     CONTINUE;

   -- If @x < 3 the statements below 'CONTINUE' will not be run.
   PRINT 'Step =' + CAST(@step AS varchar(10));
   PRINT '@x =' + CAST(@x AS varchar(10)) + ' / @y = ' + CAST(@y AS varchar(10));

 END;

 -- Write log
 PRINT 'x,y = ' + CAST(@x AS varchar(10)) + ', ' + CAST(@y AS varchar(10));

END;

5. Die abgefragten Daten zu Variables zuweisen.

Die Variables können die Wert aus einer Abfragen zugewiesen werden. Unter ist das ein Illustrationsbeispiel:
Assign_Value_Example
BEGIN

-- Declaring a variable @v_Emp_ID
DECLARE @v_Emp_ID integer = 1;

DECLARE @v_First_Name varchar(30);
DECLARE @v_Last_Name varchar(30);
DECLARE @v_Dept_ID integer;

-- Assgin values to variables
SELECT
  @v_First_Name = emp.First_Name,
  @v_Last_Name = emp.Last_Name,
  @v_Dept_Id = emp.Dept_Id
FROM Employee Emp
WHERE Emp.Emp_ID = @v_Emp_Id;

-- Print out values
PRINT '@v_First_Name = ' + @v_First_Name;
PRINT '@v_Last_Name = ' + @v_Last_Name;
PRINT '@v_Dept_Id = ' + CAST(@v_Dept_ID AS varchar(15));

END;
The results run the example:

6. Das besondere Datentypen in T-SQL

Das Datentyp TABLE (explizit)
T-SQL erlaubt Sie, eine Variable mit dem Datentyp TABLE zu deklarieren.
Die Syntax:
-- Define a variable of type TABLE.
-- NOTE: The constraints can also participate in declaration (See example).
Declare @v_variable_name  TABLE  (
  Column1 DataType1,
  Column2 DataType2
);
Zum Beispiel:
-- Declare a variable of type TABLE.
Declare @v_Table TABLE  (
First_Name Varchar(30),
Last_Name Varchar(30),
Dept_ID Integer,
Salary Float
);


-- The constraints can also participate in declaration:
Declare @v_table TABLE  (
 Product_ID Integer IDENTITY(1,1) PRIMARY KEY,
 Product_Name  DataType2 NOT NULL Default ('Unknown'),
 Price Money CHECK (Price < 10.0)
);
Zum Beispiel: Insert die Daten zur Variable mit dem Typ TABLE.
Insert Into
    @v_Table (First_Name, Last_Name, Dept_ID, Salary)
Select Emp.First_Name, Emp.Last_Name, Emp.Dept_Id, 1000
From
    Employee Emp
Where Emp.Emp_ID < 4;
Sie können Update in die Variable mit dem Typ vom TABLE:
Update
   @v_Table
Set Salary = Salary + 100
Where Dept_Id = 10;
Delete in die Variable mit dem Typ TABLE:
Delete From @v_Table Where Dept_ID = 10;
Query die Daten in die Variables mit dem Typ TABLE:
Select * from @v_Table
Where Dept_ID = 10
Order by First_Name;
Zum Beispiel:
BEGIN

 DECLARE @v_Emp_ID integer = 1;

 -- Declare a variable of type TABLE.
 DECLARE @v_Table TABLE (
   First_Name varchar(30),
   Last_Name varchar(30),
   Dept_Id integer,
   Salary float DEFAULT 1000
 );

 -- Using INSERT INTO statement to insert data into @v_Table.
 INSERT INTO @v_Table (First_name, Last_Name, Dept_ID)
   SELECT
     emp.First_Name,
     emp.Last_Name,
     emp.Dept_Id
   FROM Employee Emp
   WHERE Emp.Emp_ID < 4;

 -- Update @v_Table
 UPDATE @v_Table
 SET Salary = Salary + 100
 WHERE First_name = 'Susan';

 -- Query @v_Table.
 SELECT
   *
 FROM @v_Table;

END;
Das Ergebnis vom Laufen vom Beispiel:
Das Daten mit dem Typ TABLE (implizit)
T-SQL erlaubt Sie, eine Variable mit dem Typ TABLE implizit zu deklarieren. Der Name beginnt mit #.
Table_Example
BEGIN

-- Using SELECT INTO statement to insert data into #v_My_Table.  
SELECT
  emp.First_Name,
  emp.Last_Name,
  emp.Dept_Id,
  1000 Salary INTO #v_My_Table
FROM Employee Emp
WHERE Emp.Emp_ID < 4;

-- Update #v_My_Table
UPDATE #v_My_Table
SET Salary = Salary + 100
WHERE First_name = 'Susan';

-- Query #v_My_Table.
SELECT
  *
FROM #v_My_Table;

END;
Das Ergebnis vom Beispiel-Laufen:

7. Der Cursor

Was ist der Cursor?
Cursor ist das Typ von der strukturierten Variable, die Sie erlaubt, die Daten mit die umfangreichen Linie zu behandeln. Die Anzahl der Linie hängt von dem Statement Datenabfragen. Im Behandlungsverfahren können Sie mit cursor durch jede Daten Linie manipulieren. Diese Datenlinie wird durch einen Cursort lokalisiert. Für die Bewegung des Cursor können Sie auf alle Daten-Linie zugreifen.
Den Cursor deklarieren
Die Syntax:
-- ISO Syntax
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
    FOR select_statement
    [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]

-- Transact-SQL Extended Syntax
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
    [ FORWARD_ONLY | SCROLL ]
    [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
    [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
    [ TYPE_WARNING ]
    FOR select_statement
    [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]
Das Beispiel mit dem Cursor
Cursor_Example
USE learningsql;

BEGIN

--  
-- Declare a variable:
DECLARE @v_Emp_ID integer;
DECLARE @v_First_Name varchar(50);
DECLARE @v_Last_Name varchar(50);

DECLARE @v_Count integer;

-- Declare a CURSOR.
DECLARE My_Cursor CURSOR FOR
SELECT
  Emp.EMP_ID,
  Emp.FIRST_NAME,
  Emp.LAST_NAME
FROM Employee Emp
WHERE Emp.EMP_ID < 3;



-- Open Cursor
OPEN My_Cursor;

-- Move the cursor to the first record.
-- And assign column values to variables.
FETCH NEXT FROM My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name;

-- The FETCH statement was successful. ( @@FETCH_STATUS = 0 )
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT 'First Name = '+ @v_First_Name+' / Last Name = '+ @v_Last_Name;

  -- Move to the next record.
  -- And assign column values to the variables
  FETCH NEXT FROM My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name;
END

-- Close Cursor.
CLOSE My_Cursor;
DEALLOCATE My_Cursor;

END;
Das Ergebnis vom Beispiel Laufen
Das Beipiel von der Cursor Benutzung (als Variable deklarieren)
Cursor_Example2
USE learningsql;

BEGIN

--  
-- Declare a variable:
DECLARE @v_Emp_ID integer;
DECLARE @v_First_Name varchar(50);
DECLARE @v_Last_Name varchar(50);

-- Declaring a cursor variable.
DECLARE @My_Cursor CURSOR;

-- Set Select statement for CURSOR variable.
Set @My_Cursor = CURSOR FOR
SELECT
  Emp.EMP_ID,
  Emp.FIRST_NAME,
  Emp.LAST_NAME
FROM Employee Emp
WHERE Emp.EMP_ID < 3;


-- Open Cursor
OPEN @My_Cursor;

-- Move the cursor to the first line.
-- And assign column values to the variables.
FETCH NEXT FROM @My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name;

-- The FETCH statement was successful. ( @@FETCH_STATUS = 0)
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT 'First Name = '+ @v_First_Name+' / Last Name = '+ @v_Last_Name;

  -- Move to the next record.
  -- And assign column values to the variables.
  FETCH NEXT FROM @My_Cursor INTO @v_Emp_ID, @v_First_Name, @v_Last_Name;
END

-- Close Cursor.
CLOSE @My_Cursor;
DEALLOCATE @My_Cursor;

END;
The results run the example:

8. Die Ausnahmebehandlung

Bei der Programmierung von T-SQL können die Fehler in Ihre Code auftreten, z.B der Fehler von der Devision durch 0. Oder der Fehler auftritt wenn Sie ein Rekord einfügt, das den Hauptschlüssel dupliziert, ... Sie können die Situationen behandeln.
Ein einfaches Beispiel sehen. Die Fehlerbehandlung der Devision durch 0.
TryCatch_Example
USE learningsql;

BEGIN

--  
-- Declare a variable:
DECLARE @v_a float = 20;
DECLARE @v_b float = 0;
DECLARE @v_c float;
DECLARE @v_Error_Number integer;

-- Use BEGIN TRY .. END TRY to trap errors.
-- If an error occurs in this block
-- It will jump to block BEGIN CATCH .. END CATCH.
BEGIN TRY

  ---
  PRINT '@v_a = ' + CAST(@v_a AS varchar(15));
  PRINT '@v_b = ' + CAST(@v_b AS varchar(15));
  -- Divide by 0 error, occurring here.
  SET @v_c = @v_a / @v_b;

  -- Below this line will not be running.
  -- Program jump to block BEGIN CATCH .. END CATCH
  PRINT '@v_c= ' + CAST(@v_c AS varchar(15));

END TRY
-- BEGIN CATCH .. END CATCH must be placed immediately behind BEGIN TRY .. END TRY.
BEGIN CATCH
  -- Error Number.
  SET @v_Error_Number = ERROR_NUMBER();
  -- Print out error number:
  PRINT 'Error Number: ' + CAST(@v_Error_Number AS varchar(15));
  -- Error message:
  PRINT 'Error Message: ' + ERROR_MESSAGE();
  --  The severity of the error:
  PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS varchar(15));
  -- Error State:
  PRINT 'Error State: ' + CAST(ERROR_STATE() AS varchar(15));
  -- Line Number:
  PRINT 'Error Line: ' + CAST(ERROR_LINE() AS varchar(15));
  -- Name of procedure (or function, or trigger).
  PRINT 'Error Procedure: ' + ERROR_PROCEDURE();
END CATCH;

END;
The results run the example:
Die Fehlerinformation:
Die Funktion
Die Bezeichnung
ERROR_NUMBER()
Die Fehlernummer zurückgeben
ERROR_MESSAGE()
Den kompleten Text der Fehleranmeldungen zurückgeben. Der Text schließt die angegeboten Werte für die Paramters wie die Länge, der Name des Objekt, oder die Zeit ein.
ERROR_SEVERITY()
Die Schwere des Fehler zurückgeben.
ERROR_STATE()
Den Feherzustand zurückgeben
ERROR_LINE()
Die Nummerlinie, in die der Fehler verursacht wird, zurückgeben.
ERROR_PROCEDURE()
Den Name von stored procedure oder trigger zurückgeben, wo der Fehler auftritt.

9. Die Funktion

Wie procedure (das Verfahren) ist function (die Funktion) der Gruppe des Statement T-SQL ,die eine Rolle spielt. Anders als das Verfahren werden die Funktion gleich in der Zeitpunkt von ihrer Aufruf zurückgeben.

Die Funktion können auch in database in die Format von Store procedure gelagert werden.
Die Syntax erstellt function 
-- function_name:  
-- argument: 
-- mode:  INPUT, OUTPUT,  default INPUT
-- datatype: 

CREATE FUNCTION <function_name>
           (
              [
               @argument1 datatype1 [mode1] ,
               @argument2  datatype2 [mode2],
               ...
               ]
            )
           RETURNS  datatype
AS
BEGIN
 -- Declare variables
 -- Statements
 -- Return value
END;
Zum Beispiel:
-- Function with parameters
CREATE FUNCTION Sum_Ab(a Integer, b Integer)
RETURNS Integer
AS
Begin
return a + b;
End;

-- Function without parameters
CREATE FUNCTION Get_Current_Datetime()
RETURNS Date
AS
Begin
return CURRENT_TIMESTAMP;
End;
Die Function streichen (Drop function):
-- Drop Function

DROP FUNCTION <function_name>;

-- For example:

DROP FUNCTION My_Function;
Das Beispiel für die Erstellung einer Funktion.
Das ist ein Beispiel für die Erstellung Ihrer ersten function mit SQL Server:
  1. Eine Funktion erstellen
  2. Diese Funktion übersetzen
  3. Die Funktion laufen
-- Check the existence of the function
-- If it did exist, should drop it in order to create a new one.
IF OBJECT_ID(N'dbo.My_Sum', N'FN') IS NOT NULL
DROP FUNCTION My_Sum;
GO

CREATE FUNCTION My_Sum (@p_a float, @p_b float)
RETURNS float
AS
BEGIN

-- Declaring a variable type of Float
DECLARE @v_C float;

-- Assign value for v_C
SET @V_C = @p_A + @p_B;

-- Return value.
RETURN @v_C;

END;
Klicken Sie auf das Icon ụm die Funktion zu übersetzen.
Die oben erstellte Funktion ist eine einfache Funktion, die eine Skalar Value (scalar-value) zurückgibt. Sie können sehen, dass sie in SQLServer Management Studio erstellt wird:
Sie können die Funktion durch das rechtsklicken auf die Funktion prüfen, wählen Sie
  • Script function as -> SELECT to -> New Query Editor Window
Ein Test Fenster wird geöffnet. Sie können die Parameter Werte ändern:
Ändern Sie die Parameter Werte und klicken Sie die Ausführungsbutton um zu prüfen.
Die Funktion können an das Statement SELECT teilnehmen.
SELECT
  acc.account_id,
  acc.cust_id,
  acc.avail_balance,
  acc.pending_balance,
  dbo.MY_SUM(acc.avail_balance, acc.pending_balance) balance
FROM account acc;
Das Ergebnis vom Laufen des Statement SQL :

10. Das Verfahren

No ADS
Eine Gruppe von Statements T-SQL zur Durchführung einer Funktion kann in einem Verfahren (procedure) sammeln um die Behandlungs-, die gemeinsame Nutzungsfähigkeit, die Datensicherheit und die Utility in die Entwicklung zu erhöhen.

Das Verfahren kann in database wie ein Objekt von database gelagern und ist bereit für die Wiederverwendung. Das Verfahren wird jetzt Store procedure genannt. Für Store procedure nachdem Store procedure gelagert wird, wurden sie in p-code übersetzt. So erhöht seine Leistungsfähigkeit.
Das Verfahren gibt die Wert direkt wie die Funktion nicht zurückgeben. Allerdings kann es 0 oder viele Output Parameters haben
Die Syntax zur Erstellung eines Verfahren:
-- procedure_name:
-- argument: 
-- mode:  input type: INPUT or OUTPUT, default is INPUT
-- datatype: 
-- Note: The procedure parameters can put in an (), or unnecessary.

CREATE PROCEDURE <procedure_name>
     [
      argument1  datatype1 [mode1]  ,
      argument2   datatype2 [mode2]  ,
     ...
      ]
AS
BEGIN
 -- Declare variables.
 -- Statements ..
END;

-- OR:

CREATE PROCEDURE <procedure_name>
   (
     [
      argument1  datatype1 [mode1]  ,
      argument2   datatype2 [mode2]  ,
     ...
      ]
   )
AS
BEGIN
 -- Declare variables.
 -- Statements ..
END;
Zum Beispiel:
-- Procedure without parameters.
CREATE Procedure Do_Something
AS
Begin
     -- Declare variables here.
     Declare @v_a Integer;
      -- Do something here
      -- ....
End;

-- Procedure with parameters
--  1 input parameter and 2 output parameters

CREATE Procedure Do_Something (@p_Param1 Varchar(20),
                             @v_Param2 Varchar(50) OUTPUT )
AS
Begin
  -- Declare variables
  Declare @v_a Integer;

  -- Do something here.
  -- ...
End;
Das Verfahren streichen (Drop procedure):
-- Drop Procedure:

DROP PROCEDURE <Procedure_Name>
Die Schritte for ein Verfahren:
Das Beispiel für die Erstellung eines Verfahren:
Hier erstelle ich ein einfaches Verfahren mit einem Input Parameter @p_Emp_ID und 3 Outputparameter @v_First_Name, @v_Last_Name, @v_Dep_ID.
Get_Employee_Infos
-- Drop procedure Get_Employee_Infos if it already exists.
-- (To enable recreate)
IF OBJECT_ID(N'dbo.Get_Employee_Infos', N'P') IS NOT NULL
 DROP PROCEDURE Get_Employee_Infos;
GO

-- Procedure with input parameter: p_Emp_Id
-- And output: v_First_Name, v_Last_Name, v_Dept_Id.
CREATE PROCEDURE Get_Employee_Infos (@p_Emp_Id integer
       , @v_First_Name varchar(50) OUTPUT
       , @v_Last_Name varchar(50) OUTPUT
       , @v_Dept_Id integer OUTPUT)
AS
BEGIN
 -- Use the Print command to print out a string (for programmers).
 -- Use Cast to convert Integer to string (Varchar).
 -- Use the + operator to concatenate two strings.
 PRINT 'Parameter @p_Emp_Id = ' + CAST(@p_Emp_ID AS varchar(15));
 --
 -- Query data from the table and assign values to variables.
 --
 SELECT
   @v_First_Name = Emp.First_Name,
   @v_Last_Name = Emp.Last_Name,
   @v_Dept_Id = Emp.Dept_Id
 FROM Employee Emp
 WHERE Emp.Emp_Id = @p_Emp_Id;

 --
 -- Log (For developers).
 --
 PRINT 'Found Record!';
 PRINT ' @v_First_Name= ' + @v_First_Name;
 PRINT ' @v_Last_Name= ' + @v_Last_Name;
 PRINT ' @v_Dept_Id= ' + CAST(@v_Dept_Id AS varchar(15));

END;
Klicken Sie auf das Icon um das Verfahren zu übersetzen.
Nachdem das Verfahren erstellt wird, können Sie es in SQL Server Management Studio sehen:
Das Verfahren prüfen
In der Programmierung sind die Prüfung eines Verfahren und die Fehleraufdeckung sehr wichtig. Rechtsklicken Sie auf das Verfahren, das Sie prüfen möchten und wählen:
  • Script stored Procedure as -> EXECUTE to -> New Query Editor Window
Die Test Skript wird wie die folgende Illustration (Default) erstellt:
Setzen Sie die Wert für die Input Parameters:
Drücken Sie auf die Button um das Verfahren auszuführen:

11. Die Transaktion (Transaction)

No ADS
Warum sollen wir die Transaktion behandeln?
Die Transaktion ist ein wichtiger Begriff in SQL. Sehen Sie eine Situation:

Eine Bank Transaktion. Die Person A überträgt der Person B einen Betrag von 100$. In diesem Zeit gibt es 2 Manipulation in die Datenbank:
  1. 100$ vom Konto des Person A abziehen
  2. 100$ vom Konto des Person B gutschreiben
Was wird passieren wenn nur eine Manipulation erfolgreich ist?
Ein anderes Beispiel sehen:
Wenn Sie einen Student zu einer Klasse einfügen, aktualisieren Sie die Anzahl der Studenten. Wenn die Einfügung der Studenten Information versagt, aber die Anzahl der Studenten wird 1 addiert, ist die Vollständigkeit kaputt.
-- Insert into Student table.
Insert into Student (Studen_Id, Student_Name, Class_ID)
values (100, 'Tom', 1);

-- Update Studen_Count.
Update Class_Table
set Student_Count = Student_Count + 1
Where Class_Id = 1;
Die Transaktion wird erfolgreich betrachtet wenn alle Statement Einheiten erfolgreich ausgeführt werden. Umgekehrt ist eine der Statement einheiten fehlerhaft, wird die ganze Transaktion zum Anfangzustand wiederholt (rollback)
Die Transaktion deklarieren und benutzen
Related statements:
  • Begin transaction:
    • begin tran / begin transaction
  • Finish transaction:
    • commit/ commit tran / commit transaction
  • Rollback transaction:
    • rollback / rollback tran / rollback transaction
  • Mark a savepoint in transaction: save transaction name_of_savepoint
  • @@trancount variable: shows the number of transactions is being executed (has not been finished with rollback or commit) in the current connection.
Die Notiz:
  • Das Statement rollback tran + name_of_savepoint hilft beim Rollback der Transaktion zu der entsprechenden Stelle von savepoint (ohne die Auswirkung zur Erledigung der Transaktion), Die Schloss (locks) werden eingesetzt wenn die Manipulationen in die Teil rollback aufgeschlossen (unlock).
  • Bei die Deklaration der expliziten transaction stellen Sie sicher, dass sie dann rollback oder commit explizit gemacht wird. Wenn nicht, existiert transaction andauernd und besetzt die Ressourcen und verhindert die Durchführung der anderen transaction .
  • Das Statement rollback hilft nur beim Rollback der Transaktion in die Datenbank (insert, delete, update). Die anderen Statement, z.B das Statement zuweisen, wird von dem Statement rollback nicht ausgewirkt.
Zum Beispiel:
Transaction_Example1
BEGIN

-- In this example the accounts ACCOUNT_ID = 1, 2 actually exists in DB
-- In fact you can write statements to check before the start of transaction
--
-- account A (Already guarantees exist in DB)
DECLARE @Account_Id_A integer = 1;
-- account B (Already guarantees exist in DB)
DECLARE @Account_Id_B integer = 2;
-- Amount
DECLARE @Amount float = 10;
-- Bank
DECLARE @Execute_Branch_Id integer = 1;

-- Write out transaction Count.
-- In fact, at this time there is no transaction yet
PRINT '@@TranCount = ' + CAST(@@Trancount AS varchar(5));

PRINT 'Begin transaction';

-- Begin transaction
BEGIN TRAN;

  -- Error trapping.
  BEGIN TRY
    --
    -- Subtract $10 from account A
    UPDATE Account
    SET AVAIL_BALANCE = AVAIL_BALANCE - @Amount
    WHERE Account_Id = @Account_Id_A;
    --
    -- Insert transaction info  into Acc_Transaction table.
    INSERT INTO ACC_TRANSACTION (TXN_DATE, FUNDS_AVAIL_DATE, TXN_TYPE_CD,
     ACCOUNT_ID, AMOUNT, EXECUTION_BRANCH_ID)
      VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'CDT',
       @Account_Id_A, -@Amount, @Execute_Branch_Id);
    --
    -- Add $10 to Account B.
    UPDATE Account
    SET AVAIL_BALANCE = AVAIL_BALANCE + @Amount
    WHERE Account_Id = @Account_Id_B;
    --
    -- Insert transaction info  into Acc_Transaction table.
    INSERT INTO ACC_TRANSACTION (TXN_DATE, FUNDS_AVAIL_DATE, TXN_TYPE_CD,
      ACCOUNT_ID, AMOUNT, EXECUTION_BRANCH_ID)
      VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'CDT', @Account_Id_B,
       @Amount, @Execute_Branch_Id);
    -- Commit transaction
    IF @@Trancount > 0
      PRINT 'Commit Transaction';
  COMMIT TRAN;

END TRY
-- If there are errors Catch block will be execute.
BEGIN CATCH
  PRINT 'Error: ' + ERROR_MESSAGE();
  PRINT 'Error --> Rollback Transaction';
  IF @@Trancount > 0
    ROLLBACK TRAN;
END CATCH;



END;
Das Ergebnis des Beispiel Laufen:

12. Trigger

Das Dokument von Trigger ist getrennt. Sie können die Anleitung bei... sehen:
  • SQL Server Trigger
No ADS

Neueste Artikel

Show More