Shulou(Shulou.com)05/31 Report--

This article mainly explains the "SQLServer2005 database cursor call function use method", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's train of thought slowly in depth, together to study and learn "SQLServer2005 database cursor call function use" bar!

1. Establish the base table

Createtableplanwork (planidint,empidint) insertintoplanworkvalues (1100) insertintoplanworkvalues (2200) insertintoplanworkvalues (3300) insertintoplanworkvalues (4400) insertintoplanworkvalues (5500) insertintoplanworkvalues (6600) insertintoplanworkvalues (7700) insertintoplanworkvalues (8800) select*fomplanwork

2. Set up the function

Dropfunctionfindworkplancreatefunctionfindworkplan (@ numint) returnsintasbegindeclare@eidintset@eid= (selectempidfromplanworkwhereplanid=@num) return@eid;end

3. Test function

Selectdbo.findworkplan (3)

How to use the SQLServer2005 database cursor calling function

4. Use cursor to call function

Create a table and use the values in the table to get the corresponding empno in the workplan table

Createtablexb_test1 (xidint) insertintoxb_test1values (1) insertintoxb_test1values (2) insertintoxb_test1values (3) insertintoxb_test1values (4) insertintoxb_test1values (5) insertintoxb_test1values (6) insertintoxb_test1values (7) insertintoxb_test1values (8) select*fromxb_test1

4.2.We can only loop through the xb_test1 table to find the empno of the corresponding table workplan. Considering that we need to traverse the entire xb_test1 table, we decided to use cursors. I don't know how to use the with function of oracle? This WHILE structure tests the return value of the function @ @ FETCH_STATUS for cursors. Because @ @ FETCH_STATUS may return-2,-1, or 0, all cases should be tested. If a row is deleted from the cursor result after the execution of this stored procedure, the row is skipped. After a successful extraction (0), the SELECT statement inside the BEGIN...END loop is executed.

Declareempno_cursorcursorforselectxidfromxb_test1openempno_cursordeclare@aint,@resultintfetchnextfromempno_cursorinto@awhile (@ fetch_status-1) beginif (@ @ fetch_status-2) begin--print@aset@result= (selectdbo.findworkplan (@ a)) print@resultendfetchnextfromempno_cursorinto@aendcloseempno_cursordeallocateempno_cursor.

Thank you for your reading, the above is the content of "the use of SQLServer2005 database cursor call function". After the study of this article, I believe you have a deeper understanding of the use of SQLServer2005 database cursor call function, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.