1 / 70

Simple Programming Part 1 SECTION 7

Simple Programming Part 1 SECTION 7. Procedures and Functions. Introduction. SQL is only a query language. T-SQL (SQL) does not have features that allow sophisticated computations. Users need more sophisticated SQL-oriented programming capabilities. Microsoft SQL solution

meek
Download Presentation

Simple Programming Part 1 SECTION 7

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Simple ProgrammingPart 1SECTION 7 Procedures and Functions

  2. Introduction • SQL is only a query language • T-SQL (SQL) does not have features that allow sophisticated computations

  3. Users need more sophisticated SQL-oriented programming capabilities • Microsoft SQL solution • T-SQL Procedures and Functions • Looking at basics

  4. Stored Procedures and Functions • Lets you use all the SQL data manipulations • Fully SQL data types • Allows you to do sophisticated processing

  5. The Front End • Lets you create SQL data manipulations • Also lets you do programming • The usual front-end displayed to the user

  6. Stored Procedures • Stored in the database • A stored procedure is a group of Transact-SQL statements compiled into a single execution plan.

  7. A Generic Example IF (@QuantityOrdered < (SELECT QuantityOnHand FROM Inventory WHERE PartID = @PartOrdered) ) BEGIN -- SQL statements to update tables and process order. END ELSE BEGIN -- SELECT statement to retrieve the IDs of alternate items -- to suggest as replacements to the customer. END

  8. Stored Functions • A function is designed to return a value used within a larger SQL statemen • Functions cannot be used to make changes to the database, whereas stored procedures allow you to do inserts and updates, etc.

  9. Triggers • EG: When users log on or off • Gets executed automatically • Need a triggering event

  10. Basic Procedure Structure • Four sections • Header section (optional) • Declaration section (optional) • Execution section • Exception section (optional)

  11. Header Section • Anonymous block header • The specification of the function -- ================== -- Author -- Create date: -- Description -- ================== • Block labels make it easier to read codes

  12. Declaration Section (Optional) • Ends at keyword • BEGIN • Starts with keyword • DECLARE • Contains declarations for • Variables, constants, cursors, exceptions

  13. DECLARE @num_a NUMERIC = 6, @num_b NUMERIC; • What are we doing here? • When a procedure has finished executing • Declarations stop existing

  14. Execution Section • Ends with END • Starts with keyword • BEGIN

  15. Exception Section • Ends with END CATCH • Starts with keyword • BEGIN CATCH

  16. In our example: BEGIN try -- Generate a divide-by-zero error. SET @num_b = 0; SET @num_a = @num_a / @num_b; PRINT @num_a; END try BEGIN catch SELECT ERROR_MESSAGE() AS ErrorMessage; END catch The TRY statement lets you test a block of code for errors The CATCH statement lets you handle the error

  17. Creating a Simple Procedure Create procedure test_proc as Select * from customer; • We will try the following code

  18. Use • CREATE procedure_name • AS procedure_body CREATE PROCEDURE test_proc AS DECLARE @alpha nVARCHAR(30) BEGIN SET @alpha = ‘HELLO WORLD’ PRINT @alpha END

  19. Notice the different colouring

  20. Calling Procedures or Functions Without any parameters Execute procedure_name; With formal parameters Execute procedure_name @alpha = 50, @bravo = 20; • A procedure or function may not have formal parameters or default values

  21. Variables • Storing numbers • NUMERIC datatype • Storing text • CHAR, VARCHAR, nVARCHAR datatypes • Variables: • Essentially containers with name tags

  22. Unicode Characters • Unicode is a computing industry standard for the consistent encoding, representation and handling of text expressed in most of the world's writing systems. • Unicode's success at unifying character sets has led to its widespread and predominant use in the internationalization of computer software. • nVARCHAR • All modern operating systems and development platforms use Unicode internally.

  23. Declaring Variables • The syntax @variable_name datatype or @variable_name (datatype) = default_value_expression e.g. @num_b NUMERIC or @num_a NUMERIC = 6 @num_c NUMERIC(4,2) • A valid variable_name • Up to 30 characters • Letters, 0-9, underscore(_), $, and # • Starts with @ • Cannot use a reserved word that is used by the DBMS

  24. Datetime2 • Text – non-Unicode

  25. A Simple Procedure – calculates a percent price increase CREATE PROCEDURE increase_prices @old_price NUMERIC, @percent_increase NUMERIC = 5 AS DECLARE @new_price NUMERIC BEGIN SET @new_price = @old_price + @old_price * @percent_increase/100 PRINT 'New Price: $' + CAST(@new_price as nVARCHAR) END

  26. CREATE PROCEDURE increase_prices @old_price NUMERIC, @percent_increase NUMERIC = 5 AS DECLARE @new_price NUMERIC BEGIN SET @new_price = @old_price + @old_price * @percent_increase/100 PRINT 'New Price: $' + CAST(@new_price as nVARCHAR) END

  27. CAST The PRINT will print a numeric by itself if there are no other items to be printed. If there are two or more data types that are numeric you must use CAST or CONVERT. @new_price is numeric. Therefore CAST converts @new_price to a nvarchar datatype.

  28. CONVERT Works like CAST but structured differently

  29. Control Structures • One can do conditional processing • Also can do iterations • Many times you may want to do one thing if something is true or something else if it is not true

  30. IF Statement • The syntax: IF condition_1 BEGIN (SET) Actions_1 END ELSE IF condition_2 BEGIN (SET) Actions_2 END …….. ELSE BEGIN (SET) Actions_lastEND

  31. Want to develop a procedure to calculate your Chinese birth sign.

  32. Create procedure CZP @birthyear numeric AS IF @birthyear IN(1900,1912,1924,1936,1948,1960,1972,1984,1996,2008) BEGIN PRINT 'You are a rat' END ELSE BEGIN PRINT 'You are not a rat' END

  33. CREATE PROCEDURE Chinese_Zodiac_Proc (@BirthDate NUMERIC) --This procedure deals with birth years and the Chinese Zodiac AS PRINT ('*****'); IF @BirthDate IN( 1924, 1936, 1948, 1960, 1972, 1984, 1996) BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @BirthDate) + ', the year of the Rat.') END ELSE IF @BirthDate IN( 1925, 1937, 1949, 1961, 1973, 1985, 1997) BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @BirthDate) + ', the year of the Bull.') END ELSE IF @BirthDate IN( 1926, 1938, 1950, 1962, 1974, 1986, 1998) BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @BirthDate) + ', the year of the Tiger.') END ELSE IF @BirthDate IN( 1927, 1939, 1951, 1963, 1975, 1987, 1999) BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @BirthDate) + ', the year of the Rabbit.') END ELSE IF @BirthDate IN( 1928, 1940, 1952, 1964, 1976, 1988, 2000) BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @BirthDate) + ', the year of the Dragon.') END ELSE IF @BirthDate IN( 1929, 1941, 1953, 1965, 1977, 1989, 2001) BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @BirthDate) + ', the year of the Snake.') END ELSE IF @BirthDate IN( 1930, 1942, 1954, 1966, 1978, 1990, 2002) BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @BirthDate) + ', the year of the Horse.') END ELSE IF @BirthDate IN( 1931, 1943, 1955, 1967, 1979, 1991, 2003) BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @BirthDate) + ', the year of the Sheep.') END ELSE IF @BirthDate IN( 1932, 1944, 1956, 1968, 1980, 1992, 2004) BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @BirthDate) + ', the year of the Monkey.') END ELSE IF @BirthDate IN( 1933, 1945, 1957, 1969, 1981, 1993, 2005) BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @BirthDate) + ', the year of the Rooster.') END ELSE IF @BirthDate IN( 1934, 1946, 1958, 1970, 1982, 1994, 2006) BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @BirthDate) + ', the year of the Dog.') END ELSE IF @BirthDate IN( 1935, 1947, 1959, 1971, 1983, 1995, 2007) BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @BirthDate) + ', the year of the Pig.') END ELSE BEGIN PRINT (' Your birth date is out of the program range!') END PRINT ('*****')

  34. Now to develop the procedure using a calculation for birth sign 1944/12 = 162.00 MONKEY 1945/12 = 162.083333 ROOSTER 1946/12 = 162.166667 DOG 1947/12 = 162.25 PIG 1948/12 = 162.33333 RAT 1949/12 = 162.416667 BULL 1950/12 = 162.5 TIGER 1951.12 = 162.583333 RABBIT 1952/12 = 162.666667 DRAGON 1953/12 = 162.75 SNAKE 1954/12 = 162.833333 HORSE 1955/12 = 162.916667 SHEEP 1956/12 = 163.00 MONKEY

  35. Therefore:

  36. Thus have to calculate the remainder for each birth year Use @birthyear = 1950 Declare three variables: @X NUMERIC(10,6) @Y NUMERIC(4) @Z NUMERIC(3,2) Now SET @X =@birthyear/12 (162.5) SET @Y = @birthyear/12 (162) SET @Z = @X - @Y (0.50)

  37. Only need two significant digits Multiply by 12. Gets rid of decimal points

  38. CREATE PROCEDURE Chinese_Zodiac_Proc02 (@birthyear NUMERIC) --This procedure deals with birth years and the Chinese Zodiac AS DECLARE @X NUMERIC(10,6), @Y NUMERIC(4), @Z NUMERIC(2) SET @X = @birthyear/12 SET @Y = @birthyear/12 BEGIN IF @Y > @X SET @Y = @Y-1 SET @Z = (@X - @Y)*12 END PRINT ('*****'); IF @Z = 0 BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @birthyear) + ', the year of the Monkey.') END ELSE IF @Z = 1 BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @birthyear) + ', the year of the Rooster.') END ELSE IF @Z = 2 BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @birthyear) + ', the year of the Dog.') END ELSE IF @Z = 3 BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @birthyear) + ', the year of the Pig.') END ELSE IF @Z = 4 BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @birthyear) + ', the year of the Rat.') END ELSE IF @Z = 5 BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @birthyear) + ', the year of the Bull.') END ELSE IF @Z = 6 BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @birthyear) + ', the year of the Tiger.') END ELSE IF @Z = 7 BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @birthyear) + ', the year of the Rabbit.') END ELSE IF @Z = 8 BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @birthyear) + ', the year of the Dragon.') END ELSE IF @Z = 9 BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @birthyear) + ', the year of the Snake.') END ELSE IF @Z = 10 BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @birthyear) + ', the year of the Horse.') END ELSE IF @Z = 11 BEGIN PRINT ('You were born in ' + CONVERT(nVARCHAR(20), @birthyear) + ', the year of the Sheep.') END PRINT ('*****')

  39. How can @Y be greater than @X? Correction if @Y > @X No correction if @Y > @X @Z is negative @Z is positive

More Related