Oracle procedure return dataset Summary

To obtain a data set from Oracle Procedure, the Ref Cursor method is usually used. To obtain this Cursor, there are several ways:

1. dynamic SQL return:

In this case, Procedure operations are usually relatively simple. For example, you can use a combination of SQL statements or Join operations on multiple tables, but you can use one SQL statement to complete the query.

1. Create or replace procedure sp_getcurrentstockpallet (
2. Startdate DATE,
3. Enddate DATE,
4. Status CHAR,
5. Material_no VARCHAR2,
6. Pallet_id VARCHAR2,
7. Box_id VARCHAR2,
8. Plant VARCHAR2,
9. Stloc VARCHAR2,
10. Customer VARCHAR2,
11. Creator VARCHAR2,
12. Mat_doc VARCHAR2,
13. Box_count NUMBER,
14. Result out sys_refcursor
15. )
16. IS
17. V_ SQL VARCHAR2 (1000 );
18. BEGIN
19. V_ SQL: =
20. 'Select B. status, B. pallet_id, B. wm_pallet_id, count (B. box_id) box_count, sum (B. glass_qty) total_qty, B. unit, B. material_no,
21. B. grade, a. plant, a. stloc, a. area, a. bin, B. customer, B. product_type, B. CREATE_TIME, B. remark
22. From sd_current_pallet a, sd_current_box B
23. Where a. pallet_id = B. pallet_id ';
25. IF material_no IS NOT NULL
26. THEN
27. V_ SQL: = v_ SQL |'And B. material_no ='|''''| Material_no |'''';
29. End if;
31. V_ SQL: =
32. V_ SQL
33. | 'Group by B. status, B. pallet_id, B. wm_pallet_id, B. unit, B. material_no,
34. B. grade, a. plant, a. stloc, a. area, a. bin, B. customer, B. product_type, B. CREATE_TIME, B. remark ';
36. Open result for v_ SQL;
37. EXCEPTION
38. WHEN NO_DATA_FOUND
39. THEN
40. NULL;
41. WHEN OTHERS
42. THEN
43. RAISE;
44. END sp_getcurrentstockpallet;

2. If the Procedure logic operation is complicated, it cannot be completed in one SQL statement. In general, intermediate calculation data needs to be temporarily stored in the operation process.
In this case, you can use the following methods:
1) Use a nested Table to dynamically generate a dataset and use the Table () function to return the dataset. In this way, you need to create an Object in the database and create a nested table,
The Type defined in the local scope cannot be recognized.
Create an Object.

1. Create or replace type stockPallet_typeAs Object
2. (
3. Status CHAR (1 ),
4. Material_no VARCHAR2 (20 ),
5. Pallet_id VARCHAR2 (30 ),
6. Box_id VARCHAR2 (30 ),
7. Plant VARCHAR2 (4 ),
8. Stloc VARCHAR2 (4 ),
9. Customer VARCHAR2 (10 ),
10. Creator VARCHAR2 (10 ),
11. Mat_doc VARCHAR2 (20 ),
12. Box_count NUMBER (10)
13. )

Create a nested table with the Object stockPallet_type created above

1. Create or replace type. t_stockpallet_nestAsTable of stockpallet_type;

1 2 3 Next Page