Stored Procedure for oracle Learning (1) syntax

Oracle learning stored procedure (1) syntax stored procedure creation Syntax: create or replace procedure stored procedure name (param1 in type, param2 out type) as variable 1 type (value range ); variable 2 type (value range); Begin Select count (*) into variable 1 from Table A where column name = param1; If (Judgment condition) then Select column name into variable 2 from Table A where column name = param1; Dbms_output. Put_line ('print info'); Elsif (Judgment condition) then Dbms_output. Put_line ('print info'); Else Raise Exception name (NO_DATA_FOUND); End if; Exception When others then Rollback; End; Note: 1. stored procedure parameters do not have a value range, in indicates input, out indicates output 2, and the variable has a value range followed by a semicolon (3). It is best to use the count (*) function to determine whether the operation record exists 4 before determining the statement, select... Into... Assign a value to the variable 5. In the code, throw an exception. Use raise + Exception name to name the system exception. Cause: access_assist_null undefined object CASE_NOT_FOUND CASE. If the corresponding WHEN is not included, when ELSE is not set, the COLLECTION_IS_NULL set element does not initialize the CURSER_ALREADY_OPEN cursor. The DUP_VAL_ON_INDEX unique index column has repeated values INVALID_CURSOR. The INVALID_NUMBER nested SQL statement cannot be operated on the invalid cursor. character conversion to number NO_DATA_FOUND use select into no row is returned, or when TOO_MANY_ROWS that are not initialized in the Application Index Table executes select into, when the result set exceeds the ZERO_DIVIDE division of one row, the 0SUBSCRIPT_BEYOND_COUNT element subscript exceeds the maximum value of the nested table or VARRAY subscript_outside, when the subscript is specified as a negative VALUE_ERROR value, the variable length is insufficient to accommodate the actual data when the LOGIN_DENIED PL/SQL application connects to the oracle database, an incorrect user name or password NOT_LOGGED_ON PL/SQL application is provided. If the application does not connect to the oralce database, internal problems of data PROGRAM_ERROR PL/SQL may occur. You may need to reinstall the data dictionary & pl. /When the ROWTYPE_MISMATCH host cursor variable in the SQL System package is incompatible with the return type of PL/SQL cursor variable SELF_IS_NULL and the object type is used, when the object method STORAGE_ERROR is called on the null object to run PL/SQL, out of memory space SYS_INVALID_ID invalid ROWID string TIMEOUT_ON_RESOURCE Oracle timeout syntax while waiting for Resources 1. basic Structure create or replace procedure stored PROCEDURE name (parameter 1 in number, parameter 2 in number) IS variable 1 INTEGER: = 0; variable 2 DATE; BEGINEND stored PROCEDURE name 2. select into statement stores the select query results INTO variables. Multiple columns can be stored in multiple variables at the same time. One record is required; otherwise, an exception is thrown (if no record exists, NO_DATA_FOUND is thrown) example: begin select col1, col2 into variable 1, variable 2 FROM typestruct where xxx; exception when NO_DATA_FOUND THEN xxxx; END ;... 3.IF judge IF V_TEST = 1 then begin do something END; end if; 4. while loop while V_TEST = 1 loop begin xxxx end; end loop; 5. variable value: V_TEST: = 123; 6. use cursor with for in... is cursor cur is select * FROM xxx; begin for cur_result in cur loop begin V_SUM: = cur_result. column name 1 + cur_result. column name 2 END; end loop; END; 7. cursor CURSOR C_USER (C_ID NUMBER) with parameters is select name from user where typeid = C_ID; OPEN C_USER (variable value); loop fetch C_USER INTO V_NAME; exit fetch C_USER % NOTFOUND; do something end loop; CLOSE C_USER; 8. after connecting to the database with pl/SQL developer debug, create a Test WINDOW and enter the code for calling SP in the WINDOW. F9 starts debug and CTRL + N single-step debugging.