???? ????????? (Stored Procedures and Trigger)

1
?????????????(Stored Procedures and Trigger)
2
Chapter 12 Outline

• 12-1 ??????
• 12-2 ???????
• 12-3 ???????????
• 12-4 ??RECOMPILE?ENCRYPTION ?????
• 12-5 ???????????
• 12-6 ??????
• 12-7 ???????
• 12-8 INSERTED?DELETED??
• 12-9 ??DDL????
• 12-10 ????

3
12-1 ??????

• ?????????SQL????????,?????????,???????,??????????
  ?
• ?????SQL????????????????????????????????SQL???????
  ???,?????????(Result Set) ?
• ????????????
• ????????,??SELECT???
• ????????????,???????????,??DELETE????

4
12-1 ??????

• ???????????
• CREATE PROCEDURE schema_name.
  procedure_name number
• _at_parameter data_type VARYING
  default OUTPUT
• ,...n
• WITH RECOMPILE ENCRYPTION
• RECOMPILE, ENCRYPTION
• FOR REPLICATION
• AS
• sql_statements

5
12-1 ??????

• ????????
• ??SQL??
• ??????SQL??
• ??????????????,????????
• ????????

6
12-1 ??????

• ???????????????????

7
12-1 ??????

• ???????SQL??????????
• ?????????????

8
12-2 ???????

• ???????????????????????CREATE??ALTER,??
• ALTER PROCEDURE schema_name. procedure_name
  number

9
12-2 ???????
???????????
??cust_count???????????????????????
10
12-3 ???????????

• ??????????????????
• CREATE PROCEDURE schema_name. procedure_name
• _at_parameter_name1 datatype,
• _at_paremeter_name2 datatype ,
• _at_paremeter_result datatype OUTPUT
• AS sql_statements
• parameter_name1?parameter_name2???????
• OUTPUT????????
• datatype??????????

11
12-3 ???????????

• ??????????????????
• EXEC proc_name value,value
• ?????????????
• EXEC proc_name
• _at_parameter_name2value2OUTPUT
• _at_parameter _name1value1

12
12-3 ???????????

• ??12-3.1 ????????????????????????
• create proc dis_name
• _at_pname varchar(20) --?????????????
• as --????????T-SQL
• select from customers
• where cust_name like ''_at_cname'

13
12-3 ???????????

• ?????????
• exec cust_name ??
• exec cust_name _at_panme??

14
12-3 ???????????

• ??12-3.2???????????????
• create procedure cust_num
• _at_cust_num int, --?????
• _at_cust_name char(20) OUTPUT --?????
• as
• select _at_cust_namecust_name from
  customers
• where cust_num _at_cust_num
• if _at__at_rowcount0 --?????SQL?????????
• set _at_cust_name'??????'

15
12-3 ???????????

• ??12-3.3???????????????
• declare _at_cname char(20)
• exec cust_num 1004,_at_cname OUTPUT
• print _at_cname
• ?????????????
• declare _at_cname char(20)
• exec cust_num _at_cust_name _at_cname
  OUTPUT,_at_cust_num1004
• print _at_cname

16
12-3 ???????????

• ??12-3.4?????????????
• create proc cust_countr
• as
• declare _at_custcount
• select _at_custcount count() from customers
• return _at_ custcount --????????
• ????
• declare _at_cust_count int
• exec _at_cust_count cust_countr
• print _at_cust_count

17
12-4 ??RECOMPILE?ENCRYPTION?????

• WITH RECOMPILE??,???SQL Server?????????????????,??
  ????????????????
• ENCRYPTION??????????,???????????,?????????????????
  ??????????????alter????????

18
12-4 ??RECOMPILE?ENCRYPTION?????

• ??,?????????????????????,?????????????????????????
  ??????????,?????????????????????????????EXEC??????
  WITH RECOMPILE?? ,??
• exec cust_name ?? WITH RECOMPILE

19
12-5 ???????????

• ??????????128 MB
• ?????????????????????
• ?????????????????????????????
• ?????????Create Default?Create Procedure?Create
  Rule?Create Trigger??Create View?SQL???

20
12-5 ???????????

• ???????????,??????????????????????????????,???????
  ??????????????,????????,??????????32?
• CREATE PROCEDURE ??????????????T-SQ??,??CREATE
  PROCEDURE??????????

21
12-6 ??????

• SQL Server 2005????????????????????????,??????????
  (CHECK)???????????????????????????????
• ??????????????????????,????????????????? ?
• ?SQL Server 2005??????????,???DDL??,?????DML??

22
12-6 ??????

• DML ???????????
• ????????????????
• ??????????INSERT?UPDATE??DELETE ??
• DML??????????????????
• ???????? (INSERT?UPDATE ? DELETE) ??? DML
  ????,???????????????????
• ????????????????,???????????

23
12-6 ??????

• Products????supp_cod???suppliers?supp_code??,??sup
  pliers??????????products?supp_code????????,???????
  ????????
• ??SQL Server 2005?????????????????,???????????????
  ??????

24
12-6 ??????

• ???????????
• CREATE TRIGGER schema_name.trigger_name
• ON table_nameview_name
• WITH ENCRYPTION
• FOR DELETE , INSERT , UPDATE
• AS
• sql_statements
• ????DML?????,??????????????????????,???????(INSERT
  ?UPDATE??DELETE)?????????????

25
12-6 ??????

• ??12-6.1???????????
• create trigger ins_message
• on orders
• for insert
• as
• print ????????!
• ??????????,?????????????????????????--???????!?

26
12-6 ??????

• ????????????????,??????????????????????
• ??12-6.2????????????????
• create trigger u_pdate_message
• on orders
• for update
• as
• if update(paid_date)
• print ???????

27
12-7 ???????

• ????????????????
• ????
• ???????????????????????????
• ????
• ???????????????????????????????,??????????????????
  ?????,?????????????

28
12-8 INSERTED?DELETED???

• ???INSERT/DELETE????/?????,??????????????--INSERTE
  D??DELETED??????? ?
• ???????????????
• ??SELECT??????????
• ??????????INSERTED?DELETED?????????????????????

29
12-8 INSERTED?DELETED???

• ??????inserted??????,???items????unit_price??,???
  ?items???????products??????????????????????,??????
  ??????????items??????????????

30
12-8 INSERTED?DELETED???

• ??12-8.1???????????Trigger
• create trigger auto_uint_price
• on items
• for insert
• As --?products?????????????????
• update items set items.unit_pricep.unit_price
• from inserted i,products p
• --????supp_code?prod_num??????
• where items.supp_codep.supp_code and
  items.prod_num p.prod_num
• --???????????,?????INSERTED??????
• and items.order_numi.order_num and
  items.item_num i.item_num

31
12-8 INSERTED?DELETED???
??items?????unit_price????0
??2??????????????(1???????????)???4???????????????
?
32
12-8 INSERTED?DELETED???

• DELETED????????????,?????????????????????,????????
  ?????????????????? ?
• ??,?????????????????????,??????????????,??????????
  ?,???????????

33
12-9 ??DDL????

• DDL?????SQL Server 2005?????,?????????????????
  (DDL)????,?????????? ?
• DDL?????????CREATE?ALTER ??DROP??????SQL???
• ?????????????????????,????????????????????????????

34
12-10 ????

• ???????SQL Server?????,???????????????????????????
  ?,?????????????? ?
• ?????????????,????????????????,???????????,???????
  ??SQL???????
• ???????????????? ?

35
12-10 ????

• ???????????????????????????????,????????DML?DDL??
  ?
• DML?????????????,??????INSERT?UPDATE??DELETEDDL??
  ???????????,??????CREATE?ALTER??DROP?