akevintg

SQLearning(PROCEDURE, TRIGGER, CURSOR, ETC)

May 29th, 2015
321
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 3.40 KB | None | 0 0
  1. /*
  2. CREATE DATABASE OOVEO_Salon
  3. GO
  4. USE OOVEO_Salon
  5. */
  6.  
  7. -- Create MsCustomer
  8. CREATE TABLE MsCustomer(
  9.     CustomerId CHAR(5) PRIMARY KEY,
  10.     CustomerName VARCHAR(50),
  11.     CustomerGender VARCHAR(10),
  12.     CustomerPhone VARCHAR(13),
  13.     CustomerAddress VARCHAR(100),
  14.     CONSTRAINT cekIDCust CHECK(CustomerId LIKE 'CU[0-9][0-9][0-9]')
  15. )
  16.  
  17. INSERT INTO MsCustomer VALUES
  18. ('CU001', 'Franky', 'Male', '08566543338', 'Daan mogot baru Street no 6'),
  19. ('CU002', 'Ernalia Dewi', 'Female', '085264782135', 'Tanjung Duren Street no 185'),
  20. ('CU003', 'Elysia Chen', 'Female', '085754206611', 'Kebon Jeruk Street no 120'),
  21. ('CU004', 'Brando Kartawijaya', 'Male', '081170225561', 'Greenvil Street no 88'),
  22. ('CU005', 'Andy Putra', 'Male', '087751321421', 'Sunter Street no 42')
  23.  
  24. /*1. Create a procedure to view all data on MsCustomer based on specific CustomerId
  25. (PROCEDURE, EXEC)*/
  26.  
  27. CREATE PROCEDURE psc1 @passingId VARCHAR(6) AS
  28.     SELECT *
  29.     FROM MsCustomer
  30.     WHERE CustomerId=@passingId
  31.  
  32. EXEC psc1 'CU001'
  33.  
  34. /*2. Create a procedure to view all data on MsCustomer based on specific CustomerName,
  35. if name is odd print 'Name is Odd'(PROCEDURE, IF, LEN, PRINT)*/
  36.  
  37. CREATE PROCEDURE psc2 @passingName VARCHAR(50) AS
  38.     IF LEN(@passingName)%2=1
  39.         PRINT 'Name is Odd'
  40.     ELSE
  41.         SELECT *
  42.         FROM MsCustomer
  43.         WHERE CustomerName LIKE '%'+@passingName+'%'
  44.  
  45. psc2 'Franky'
  46. psc2 'Kartawijaya'
  47.  
  48. /*3. Create a trigger to show all data before and after update on MsCustomer
  49. (TRIGGER, UPDATE, INSERTED, DELETED)*/
  50.  
  51. CREATE TRIGGER trig1 ON MsCustomer FOR UPDATE AS
  52.     SELECT * FROM deleted
  53.     SELECT * FROM inserted
  54.  
  55. UPDATE MsCustomer
  56. SET CustomerName='Antonius', CustomerAddress='Kelapa Gading Utara'
  57. WHERE CustomerId='CU001'
  58.  
  59. /*4. Create a triger to show CustomerId,CustomerName, CustomerPhone being inserted on MsCustomer
  60. then insert this data ('CU009','Dani Pedrosa','Male','08161834576', 'Kelapa Gading')
  61. (TRIGER, INSERT, DECLARE, PRINT)*/
  62.  
  63. CREATE TRIGGER trig2 ON MsCustomer FOR INSERT AS
  64.     DECLARE @name VARCHAR(50), @id VARCHAR(6),@phone VARCHAR(12)
  65.     SELECT @name=CustomerName,@id=CustomerId,@phone=CustomerPhone FROM inserted
  66.     PRINT 'Inserted Data : CustomerId ('+@name+'), CustomerId ('+@id+') & CustomerPhone ('+@phone+')'
  67.  
  68. INSERT INTO MsCustomer
  69. VALUES ('CU009','Dani Pedrosa','Male','08161834576', 'Kelapa Gading')
  70.  
  71. /*5. Create a triger to show CustomerId being deleted on MsCustomer
  72. then delete CustomerId CU009
  73. (TRIGER, DELETE, DECLARE, PRINT)*/
  74.  
  75. CREATE TRIGGER trig3 ON MsCustomer FOR DELETE AS
  76.     DECLARE @Id VARCHAR(6)
  77.     SELECT @id=CustomerId
  78.     FROM deleted
  79.     PRINT 'Data deleted: Id('+@id+')'
  80.  
  81. DELETE FROM MsCustomer WHERE CustomerId='CU009'
  82.  
  83. /*6. Make cursor that can select all data by fetch next,prio,first,last,relative -2, absolute 3 on MsCustomer
  84. (CURSOR SCROLL, OPEN, FETCH, CLOSE, DEALLOCATE)*/
  85.  
  86. DECLARE crs1 CURSOR SCROLL FOR
  87.     SELECT *
  88.     FROM MsCustomer
  89.  
  90. OPEN crs1
  91.     FETCH NEXT FROM crs1
  92.     FETCH PRIOR FROM crs1
  93.     FETCH FIRST FROM crs1
  94.     FETCH LAST FROM crs1
  95.     FETCH RELATIVE -2 FROM crs1
  96.     FETCH ABSOLUTE 3 FROM crs1
  97. CLOSE crs1
  98.  
  99. DEALLOCATE crs1
  100.  
  101. /*7. Make cursor that print name for all the data on MsCustomer
  102. (CURSOR, OPEN, WHILE, BEGIN, END)*/
  103.  
  104. DECLARE crs2 CURSOR FOR
  105.     SELECT CustomerName
  106.     FROM MsCustomer
  107.  
  108. OPEN crs2
  109.     DECLARE @nama VARCHAR(50)
  110.     FETCH NEXT FROM crs2 INTO @nama
  111.     WHILE @@FETCH_STATUS=0
  112.         BEGIN
  113.             PRINT 'Nama= '+@nama   
  114.             FETCH NEXT FROM crs2 INTO @nama
  115.         END
  116. CLOSE crs2
  117.  
  118. DEALLOCATE crs2
Add Comment
Please, Sign In to add comment