Stored Procedure-elementary tutorial

Stored PROCEDURE --- create or replace procedure cuiyaonan2000 (stored PROCEDURE name) (parameter 1 in number, parameter 2 in number, parameter 2 in number, parameter 3 OUT Number // The above is in, which indicates the parameter you pass to the stored procedure, and out indicates the parameter that the stored procedure returns to you. there can be multiple. different from the following variables) IS/AS // here we use IS or AS with my habits, there IS no difference in the variable 1 INTEGER: = 0; variable 2 DATE; // these variables are the internal variables of the stored procedure, which is equivalent to the local variables applied in the method. of course, there are global variables. We will not introduce them here. the function is used in the following statement block, which is consistent with the internal variables of the method. there can be multiple variables. www.2cto.com BEGIN here are some SQL statements controlled by if, while, for, and other flow statements; END cuiyaonan2000 // END sign CASE Environment oracle Database PL/SQL prepare Java code create or replace procedure cuiyaonan2000 (a in number, B out number) IS first1 char; second2 number (1 ); third3 number: = 123; begin NULL; exception when NO_DATA_FOUND THEN failed ('data not found error'); when others then dbms_output.put_line ('unknown error'); END; end cuiyaonan2000; // above: = is the equal sign assigned to the variable in the traditional sense. // As shown above: = It is the equal sign assigned to the variable in the traditional sense. // The exception above. when is the same as Try catch. // No_data_found is the exception name. others indicates all exceptions. // dbms_output.put_line is the database method. system. out. println II. flow Control statement www.2cto.com IF flow statement: Java code create or replace procedure cuiyaonan2000 (a in number, B out number) IS first1 char; second2 number (1); third3 number: = 123; begin if a> 0 then begin dbms_output.put_line (a); B: = 123; begin (a); end if; if third3> 0 then begin dbms_output.put_line (third3); third3: = third3 + 1; begin (third3); end if; xception WHEN NO_DATA_FOUND THEN failed ('data not found error'); when others then dbms_output.put_line ('unknown error '); end cuiyaonan2000; // The input parameter a above cannot be assigned a value. Otherwise, an error is returned. // As shown above: = is the equivalent of assigning values to variables. // B is used to receive the results returned by the cuiyaonan2000 stored procedure. when a few parameters are called, an error IS reported FOR the process statement. The Java code create or replace procedure cuiyaonan2000 (a in number, B out number) is Cursor cursor IS select name from test_table_user; begin FOR temp IN cursor LOOP dbms_output.put_line (temp. name); end loop; exception WHEN NO_DATA_FOUND THEN dbms_output.put_line ('data not found error'); when others then dbms_output.put_line ('unknown error'); end cuiyaonan2000; // The above cursor is similar to the jdbc cursor pointing to a record. then we use a loop to traverse all the records found. here, for temp in must be the Java code create or replace procedure cuiyaonan2000 (a in number, B out number) IS c number: = 1; begin while c <10 LOOP begin c: = c + 1; dbms_output.put_line (c); end LOOP; exception WHEN NO_DATA_FOUND THEN dbms_output.put_line ('data error not found '); WHEN others then dbms_output.put_line ('unknown error'); end cuiyaonan2000; www.2cto.com this is a simple example. We suggest you study the cursor after reading this. to call a stored procedure, enter the following Java code in plsql's SQL command // The test code in the plsql SQL window // I is used to receive the results returned by the cuiyaonan2000 stored procedure. if there are few parameters, the error declare I integer; begin cuiyaonan2000 (1, I); end;