Oracle executes parameterized SQL statements and stored procedures

1. Using system;
2. Using system. Collections. Generic;
3. Using system. text;
4. Using system. Data. oracleclient;
5. Using system. Data;
7. Namespace oracleopdemo
8. {
9. Class Program
10. {
11. Private Static string connstr = "Data Source = oratest; user id = m; Password = m123 ;";
12. Private Static string connstrsql = "Data Source = oratest; user id = CTD; Password = cc ;";
14. Static void main (string [] ARGs)
15. {
16. Executeproc ();
17. Executeparesql ();
19. Console. writeline ("execute completed ");
21. Console. Read ();
23. }
25. Private Static void executeparesql ()
26. {
27. Using (oracleconnection conn = new oracleconnection (connstrsql ))
28. {
29. Try
30. {
31. Conn. open ();
33. String SQL = "insert into ivan_test (ID, name) values (: p_id,: p_name )";
35. Oraclecommand cmd = new oraclecommand (SQL, Conn );
37. Oracleparameter para = new oracleparameter ("p_id", oracletype. number );
38. Para. value = 510;
39. Cmd. Parameters. Add (para );
41. Para = new oracleparameter ("p_name", oracletype. varchar );
42. Para. value = "ivantest ";
43. Cmd. Parameters. Add (para );
45. Cmd. executenonquery ();
47. }
48. Catch (oracleexception ex)
49. {
50. Throw new exception (ex. Message );
51. }
52. Finally
53. {
54. Conn. Close ();
55. }
57. }
58. }
60. Private Static void executeproc ()
61. {
63. Using (oracleconnection conn = new oracleconnection (connstr ))
64. {
65. Try
66. {
67. Conn. open ();
68. Oraclecommand cmd = conn. createcommand ();
69. Cmd. Connection = conn;
70. Cmd. commandtype = commandtype. storedprocedure;
71. Cmd. commandtext = "p_test ";
73. Oracleparameter para = new oracleparameter ("p_checkid", "09531c6083fc4e579eb376dd9861b299 ");
74. Cmd. Parameters. Add (para );
75. Para = new oracleparameter ("p_item", 10 );
76. Cmd. Parameters. Add (para );
77. Para = new oracleparameter ("p_value", 11 );
78. Cmd. Parameters. Add (para );
79. Para = new oracleparameter ("p_delayday", 1 );
80. Cmd. Parameters. Add (para );
81. Para = new oracleparameter ("p_description", "DESC ");
82. Cmd. Parameters. Add (para );
84. Cmd. executenonquery ();
86. }
87. Catch (oracleexception ex)
88. {
89. Throw new exception (ex. Message );
90. }
91. Finally
92. {
93. Conn. Close ();
94. }
96. }
97. }
98. }
99. }

Oracle procedure:

1. Create or replace procedure Ptest (
2. P_checkid in varchar2, p_item in number,
3. P_value in number, p_delayday in number, p_description in varchar2
5. ) Is
6. V_state iqc_check.verify_state % type;
7. Begin
8. Declare cursor mycursor1 is
9. Select a. verify_state from check a where a. ID = p_checkid;
10. Begin
11. Open mycursor1;
12. Fetch mycursor1 into v_state;
13. If v_state = 0 then
14. Insert into check_pack (checkid, item, value, delayday, description)
15. Values (p_checkid, p_item, p_value, p_delayday, p_description );
16. Else
17. Update check_pack
18. Set Value = p_value,
19. Delayday = p_delayday,
20. Description = p_description
21. Where checkid = p_checkid and item = p_item;
22. End if;
23. Close mycursor1;
24. End;
26. -- Commit;
27. End Ptest;