SQL/PSM

1. SQL/PSM Procedures Stored in the Database General-Purpose Programming

2. Stored Procedures • PSM, or “persistent, stored modules,” allows us to store procedures as database schema elements. • PSM = a mixture of conventional statements (if, while, etc.) and SQL. • Lets us do things we cannot do in SQL alone.

3. Functionality Stored procedures can be used to • Return information to the caller • Modify data in databases • Implement business logic in data tier • Control access to data • Improve performance of the system • Reduce network traffic • Perform other actions and operations (such as process e-mail, execute operating • system commands and processes, and manage other SQL server objects)

4. Basic PSM Form CREATE PROC[EDURE] procedure_name [ {@ parameter data_type} [= default] [OUTPUT] ] [,... n] AS sql_statement [... n]

5. Example Create Procedure prGetBar @barName varchar(50) as Select * from Bar where name = @barName Bars(name, addr, license)

6. Execute the Stored Procedure [ [ EXEC [ UTE ] ]    {         [ @return_status = ]            { procedure_name [ ;number ] | @procedure_name_var    }     [ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ]        [ ,...n ] [ WITH RECOMPILE ] • Example • ExecuteprGetBar @barName=‘Joe’ • prGetBar @barName=‘Joe’ • prGetBar ‘Joe’

7. Receive Information from a Stored Procedure • Result set • Output parameters • Return value • Global cursor

8. Returning Result Sets • To obtain a result set from a stored procedure, insert a Transact-SQL statement that returns a result set into the body of the stored procedure. The simplest way is by using a Select statement, but you could also call another stored procedure.

9. Input and Output Parameters • The header of the stored procedure contains a list of parameters delimited with a comma (,) character. Each parameter is defined with an identifier and a data type. Parameter identifiers must begin with the at sign (@). • If the parameter with the keyword Output after the data type, it can return a value to the caller.

10. Returning Values with Output Parameters • CREATE PROCEDURE dbo.mathtutor • @m1 smallint, • @m2 smallint, • @result smallint OUTPUT • AS • SET @result = @m1 * @m2 • DECLARE @answer smallint • EXECUTE mathtutor 5, 6, @answer OUTPUT • SELECT 'The result is: ' , @answer • The result is: 30 Creating Stored Procedure Executing Stored Procedure Results of Stored Procedure

11. Another Example Create Procedure prGetBeerPrice @bar varchar(50), @beer varchar(50),@price money output as Select @price=price from Sells where bar = @bar and beer=@beer • Execute the stored procedure • Declare @price money • Execute prGetBeerPrice @bar=‘Joe’,@beer=’Bud’,@price Output • Select @price Sells(bar, beer, price)

12. Return Value • Each stored procedure can end with a Return statement. The statement can be followed by an integer value that can be read by the caller. If the return value is not explicitly set, the server will return the default value—zero (0). • Because return values are limited to integer data types, they are most often used to signal an status or error code to the caller.

13. Example Create Procedure prGetBeerPrice @bar varchar(50), @beer varchar(50),@price money output as Select @price=price from Sells where bar = @bar and beer=@beer Return @@error • Execute the stored procedure • Declare @price money • Declare @errorCode • Execute @errorCode =prGetBeerPrice @bar=‘Joe’,@beer=’Bud’,@price Output • Select @price result, @errorCode ErrorCode

14. Default Values Default values are defined at the end of a parameter definition, behind the data types. All that is needed is anassignment (=) and a value.

15. Example if exists (select * from sysobjects where id = object_id(' prGetBar ') and OBJECTPROPERTY(id, 'IsProcedure') = 1) drop procedure prGetBar GO Create Procedure prGetBar @barName varchar(50) = ‘Joe’ as Select * from Bar where name = @barName • Execute prGetBar @barName=‘Smith’ • Execute prGetBar

16. Renaming Stored Procedures sp_rename [ @objname = ] 'object_name' ,    [ @newname = ] 'new_name'    [ , [ @objtype = ] 'object_type' ] • Example Sp_rename prGetBar getBar

17. Altering Stored Procedures Alter Procedure prGetBar @barName varchar(50) = ‘Joe’ as Select * from Bar where name = @barName

18. Deleting Stored Procedures • DROP PROCEDURE { procedure } [ ,...n ] • Example Drop Procedure getBar

19. T-SQL Identifiers The following are the rules for creating identifiers: • Identifiers in SQL Server 2000 may have between 1 and 128 characters. There are exceptions to this rule: certain objects are limited (for instance, temporary tables can have identifiers up to only 116 characters long). • The first character of the identifier must be a letter, underscore ( _ ), at sign(@), or number sign (#). The first letter must be defined in the Unicode 2.0 standard. Among other letters, Latin letters a–z and A–Z can be used as a first character. Some characters (@ and #) have special meanings in T-SQL. They act as signals to SQL Server to treat their carriers differently. • Subsequent characters must be letters from the Unicode 2.0 standard, or decimal digits, or one of the special characters @, #, _, or $. • SQL Server reserved words should not be used as object identifiers. • Identifiers cannot contain spaces or other special characters except for @, #, _, or $.

20. T_SQL Data Type(1)

21. T_SQL Data Type(2)

22. T_SQL Data Type(3)

23. Variables • Local variable • begin with an @ prefix. • Global variable • begin with an @@ prefix.

24. Declaring Variables • They must begin with @: • Declare @LastName varchar(50) • It is possible to define several variables in a single Declare statement. You just need to separate them with commas: • Declare @LastName varchar(50), • @FirstName varchar(30), • @BirthDate smalldatetime • You can also define variables based on user-defined data types: • Declare @OfficePhone phone

25. Assigning Values with the Select Statement SELECT @name=name,@manf=manf FROM Beers WHERE manf = ’Anheuser-Busch’; SELECT @name name,@manf manufacture;

26. Assigning Values with the Set Statement • Set @name=‘Bud’,@manf=‘Anheuser-Busch’

27. Displaying the Values of Variables • Select @LastName • Print @FirstName

28. Global Variables • @@error • @@rowcount select Make, Model, EqTypeid into OldEquipment from Equipment where EqTypeid = 2 if @@rowcount = 0 Print "No rows were copied!"

29. Flow-Control Statements • Comments • Statement block • If…Else • While…Break • Break • Continue • GoTo • WaitFor • Begin…End

30. Comments • Single-Line Comments -- • A complete line or part of the line canbe marked as a comment if the user places two hyphens (--) at the beginning. • Multiline Comments: /* … */

31. Statement Blocks: Begin…End Begin Insert Order(OrderDate, RequestedById, TargetDate, DestinationLocation) Values(@OrderDate, @ContactId, @TargetDate, @LocId) Select @ErrorCode = @@Error, @OrderId = @@Identity if @ErrorCode <> 0 begin RaiseError('Error occurred while inserting Order!', 16,1) Return @@ErrorCode end End

32. Conditional Execution: The If Statement • Syntax 1 If boolean_expression {Transact-SQL_statement | statement_block} [else {Transact-SQL_statement | statement_block}]

33. Example Create Procedure prInsertUpdateSells -- store values in Sells table. ( @bar varchar(50), @beer varchar(50), @price money ) As declare @bn varchar(50) Select @bn = beer From sells Where bar = @bar and beer=@beer Sells(bar, beer, price)

34. Example(Cont.) -- does such bn already exists in the database If @bn IS NOT NULL --insert a new record Insert sells (bar, beer, price) Values (@bar, @beer, @ price) Else --if it does not exist Begin -- update the beer’s price Update sells Set price=@price Where bar=@bar and beer=@beer End

35. Conditional Execution: The If Statement • Syntax 2 If [NOT] Exists(subquery) {Transact-SQL_statement | statement_block} [else {Transact-SQL_statement | statement_block}]

36. Example Create Procedure prInsertUpdateSells_2 @bar varchar(50),@beer varchar(50),@price money As If not exists(Select @bn = beer From sells Where bar = @bar and beer=@beer) --insert a new record Insert sells (bar, beer, price) Values (@bar, @beer, @ price) Else --if it does not exist Begin -- update the beer’s price Update sells Set price=@price Where bar=@bar and beer=@beer End

37. Looping: The While Statement While Boolean_expression {sql_statement | statement_block} [Break] {sql_statement | statement_block} [Continue]

38. Example Create Procedure prCalcFactorial -- calculate factorial -- 1! = 1 -- 3! = 3 * 2 * 1 -- n! = n * (n-1)* . . . 5 * 4 * 3 * 2 * 1 @inyN tinyint, @intFactorial int OUTPUT As Set @intFactorial = 1 while @inyN > 1 begin set @intFactorial = @intFactorial * @inyN Set @inyN = @inyN - 1 end return 0

39. System Stored Procedure • SP_Databases • SP_Tables • SP_Addlogin • SP_GrantAccess • SP_Password • SP_Attach_DB • SP_Detach_DB • Sp_Rename • XP_cmdshell • Xp_Fileexist • Xp Fixeddrives

40. Cursors • A cursor is essentially a tuple-variable that ranges over all tuples in the result of some query. • Declare a cursor c by: DECLARE c CURSOR FOR <query>;

41. Opening and Closing Cursors • To use cursor c, we must issue the command: OPEN c; • The query of c is evaluated, and c is set to point to the first tuple of the result. • When finished with c, issue command: CLOSE c;

42. Fetching Tuples From a Cursor • To get the next tuple from cursor c, issue command: FETCH FROM c INTO x1, x2,…,xn ; • The x ’s are a list of variables, one for each component of the tuples referred to by c. • c is moved automatically to the next tuple.

43. Breaking Cursor Loops --- (1) • The usual way to use a cursor is to create a loop with a FETCH statement, and do something with each tuple fetched. • A tricky point is how we get out of the loop when the cursor has no more tuples to deliver.

44. Breaking Cursor Loops --- (2) • Each SQL operation returns a status, which is a 5-digit number. • For example, 00000 = “Everything OK,” and 02000 = “Failed to find a tuple.” • In PSM, we can get the value of the status in a variable called SQLSTATE.

45. Breaking Cursor Loops --- (3) • We may declare a condition, which is a boolean variable that is true if and only if SQLSTATE has a particular value. • Example: We can declare condition NotFound to represent 02000 by: DECLARE NotFound CONDITION FOR SQLSTATE ’02000’;

46. Breaking Cursor Loops --- (4) • The structure of a cursor loop is thus: cursorLoop: LOOP … FETCH c INTO … ; IF NotFound THEN LEAVE cursorLoop; END IF; … END LOOP;

47. Example: Cursor • Let’s write a procedure that examines Sells(bar, beer, price), and raises by $1 the price of all beers at Joe’s Bar that are under $3. • Yes, we could write this as a simple UPDATE, but the details are instructive anyway.

48. Used to hold beer-price pairs when fetching through cursor c Returns Joe’s menu The Needed Declarations CREATE PROCEDURE JoeGouge( ) DECLARE theBeer CHAR(20); DECLARE thePrice REAL; DECLARE NotFound CONDITION FOR SQLSTATE ’02000’; DECLARE c CURSOR FOR (SELECT beer, price FROM Sells WHERE bar = ’Joe’’s Bar’);

49. Check if the recent FETCH failed to get a tuple If Joe charges less than $3 for the beer, raise it’s price at Joe’s Bar by $1. The Procedure Body BEGIN OPEN c; menuLoop: LOOP FETCH c INTO theBeer, thePrice; IF NotFound THEN LEAVE menuLoop END IF; IF thePrice < 3.00 THEN UPDATE Sells SET price = thePrice + 1.00 WHERE bar = ’Joe’’s Bar’ AND beer = theBeer; END IF; END LOOP; CLOSE c; END;

50. Function alternative: CREATE FUNCTION <name> ( <parameter list> ) RETURNS <type>