Explain the stored procedures of Oracle and PLSQL.

Explain the stored procedures of Oracle and PLSQL.
1. Create a stored procedure in plsql

Open plsql, right-click procedures, and create.

If the creation is unresponsive

Direct file-New-program window-blank, create a program window

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;

Ii. instance: Write Stored Procedure (example)

-- Create or replace procedure p_contract_purchase_import, a stored procedure named p_contract_purchase_import (-- the following external parameters for writing stored procedures (input parameters) -- format: parameter name in parameter type -- note, the varchar here is not labeled with the size V_IN_SUBCOMPANYID in VARCHAR2, -- professional branch company V_IN_PURCONTRACTMONEY in NUMBER, -- Procurement contract amount V_IN_PARTYBNAME in VARCHAR2, -- Supplier name -- set a return value v_o_ret out number -- returns: succeeded; 1: failed; 4: unable to find the supplier; 5: failed to add the Association; 6: failed to add the procurement contract) -- write the following internal parameters -- format: parameter name parameter type -- note that the varchar value here must be asV_SUPPLIERID INTEGER; -- supplier ID V_PARTYBACCOUNT VARCHAR2 (100); -- payment account V_SQLERRM VARCHAR2 (4000 ); -- error details -- stored procedure start in -- assign initial values to some variables -- format: variable name: = value v_o_ret: = 1; V_SUPPLIERID: = ''; V_PARTYBACCOUNT: = ''; -- write specific operation statement (SQL) -- if statement if (V_IN_PARTYBNAME is not null) then begin select t. SUPPLIERID, t. PARTYBACCOUNT, t. PARTYBBANK, t. PARTYBNAME into V_SUPPLIERID, V_PARTYBACCOUNT, V_PARTYBBANK, V_PARTYBNAME from T_SUPPLIER t where t. PARTYBNAME = trim (V_IN_PARTYBNAME) and t. SUBCOMPANYID = trim (V_IN_SUBCOMPANYID); -- throw exception when others then v_o_ret: = 4; -- the supplier V_PARTYBNAME: = V_IN_PARTYBNAME cannot be found; -- write the cause of the exception to the stored procedure log table: = SQLERRM; insert into T_LOG_DBERR (ERRTIME, ERRMODEL, ERRDESC) VALUES (SYSDATE, 'Procedures ', 'P _ contract_purchase_import: ret =' | v_o_ret | ', '| V_SQLERRM); COMMIT; end if; · end; commit; v_o_ret: = 0; return; exception when others then rollback; -- INSERT error cause V_SQLERRM: = SQLERRM; insert into T_LOG_DBERR (ERRTIME, ERRMODEL, ERRDESC) VALUES (SYSDATE, 'Procedures ', 'P _ contract_purchase_import: ret = '| v_o_ret |', '| V_SQLERRM); COMMIT; -- end of Stored Procedure p_contract_purchase_import;

1. Note that begin and end are paired.

2. Make sure to print out the error content. Otherwise, even if the debugging shows which statement is incorrect, it is not clear where the error is.

Iii. storage process debugging

1. Press F8 or the second execution button on the third line of the menu bar to compile the stored procedure. In this case, if there is a syntax error, plsql will prompt you.

2. In procedures, right-click the stored procedure to be debugged and choose test. Note: Remember to check and add debugging information.

3. Open the debugging window and enter the input parameters.

4. Click the debugging button in the test window (The position of the circle in the middle) to start debugging.

5. Step-by-Step debugging

Click single-step debugging, which is mainly used for debugging. The executed code is highlighted. In addition, note the position 4 in the figure. You can enter the variable name here to view the current value of the variable.

Skip single step execution and complete execution, which are not recommended for debugging.

In the single-step debugging process, if the execution of any step directly jumps to the end of exception, this step is the problem. Remember this location and debug again, check the value of the variable nearby and the error details in the error log to confirm the specific cause of the error and modify it.

4. Calling in java

After debugging a stored procedure, you can call it in java.

Logger.info ("calling the Stored Procedure p_contract_purchase_import"); // name of the stored procedure. Question mark (including v_o_ret) String procName = "{Call p_contract_purchase_import (,)}"; DataSource ds = SessionFactoryUtils. getDataSource (this. getHibernateTemplate (). getSessionFactory (); Connection conn = null; CallableStatement call = null; // ResultSet rs = null; try {// create Connection conn = ds. getConnection (); call = conn. prepareCall (procName); // call the incoming data. setString (1, (importList. get (0 )). trim (); if (ratio_amou Nt = null) {call. setString (2, null);} else {call. setLong (2, ratio_amount);} call. setString (3, (importList. get (2 )). trim (); // The fourth parameter is the call that exists as the return value. registerOutParameter (4, Types. BIGINT); // execute the Stored Procedure call.exe cuteUpdate (); // obtain the returned result ret = call. getInt (17); logger.info ("ret:" + ret); // call. getInt (6); try {// close the connection call. close (); call = null; conn. close (); conn = null;} catch (SQLException e) {// TODO Auto-generated catch block} catch (SQLException e) {logger. error ("Opening Stored Procedure error:", e);} finally {try {if (call! = Null) {call. close () ;}if (conn! = Null) {conn. close () ;}} catch (SQLException e) {// TODO Auto-generated catch block conn = null ;}}

Note that call. setLong () cannot be null. If the content may be empty, you must determine whether it is empty before using the setLong () method. Otherwise, you must use the setString method.

5. Notes:

Stored procedure parameters do not have a value range. in indicates input, and out indicates output.

Variable with value range followed by semicolon

Use the count (*) function to determine whether the operation record exists before determining the statement.

Use select... Into... Assign values to variables

Use raise + Exception name to throw an exception in the code

Naming exception:

An error occurred while naming the system.	Cause
Access_assist_null	Undefined object
CASE_NOT_FOUND	If the CASE does not contain the corresponding WHEN and the ELSE is not set
COLLECTION_IS_NULL	The Set element is not initialized.
CURSER_ALREADY_OPEN	Cursor opened
DUP_VAL_ON_INDEX	The column corresponding to the unique index has repeated values.
INVALID_CURSOR	Operate on an invalid cursor
INVALID_NUMBER	Embedded SQL statements cannot convert characters into numbers.
NO_DATA_FOUND	If select into is used, no rows are returned, or the index table is not initialized.
TOO_MANY_ROWS	When select into is executed, the result set exceeds one row.
ZERO_DIVIDE	The divisor is 0.
SUBSCRIPT_BEYOND_COUNT	The element subscript exceeds the maximum value of the nested table or VARRAY.
SUBSCRIPT_OUTSIDE_LIMIT	When nested tables or VARRAY are used, the subscript is specified as a negative number.
VALUE_ERROR	When values are assigned, the variable length is insufficient to accommodate actual data.
LOGIN_DENIED	When the PL/SQL application connects to the oracle database, an incorrect user name or password is provided.
NOT_LOGGED_ON	PL/SQL applications access data without connecting to the oralce Database
PROGRAM_ERROR	For internal PL/SQL problems, you may need to reinstall the data dictionary & pl./SQL System package
ROWTYPE_MISMATCH	The host cursor variable is incompatible with the PL/SQL cursor variable return type.
SELF_IS_NULL	When using the object type, call the object method on the null Object
STORAGE_ERROR	The memory space is exceeded when PL/SQL is run.
SYS_INVALID_ID	Invalid ROWID string
TIMEOUT_ON_RESOURCE	Oracle timed out while waiting for resources

Vi. Basic Syntax 1. Basic Structure

Create or replace procedure the name of the stored PROCEDURE (parameter 1 in number, parameter 2 in number) IS variable 1 INTEGER: = 0; variable 2 DATE; BEGIN -- Name of the execution body END stored PROCEDURE;

2. SELECT INTO STATEMENT

Save the select query result to a variable. You can store multiple columns 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 judgment

 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 assignment

 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 with Parameters

CURSOR C_USER (C_ID NUMBER) 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. Use pl/SQL developer debug

Create a Test WINDOW after connecting to the database, enter the code to call SP in the WINDOW, F9 start debug, CTRL + N single-step debugging

8: Some Questions about oracle stored procedures 1. in oracle, data table aliases cannot be added with as, for example:

Select a. appname from appinfo a; -- the correct select a. appname from appinfo as a; -- Error

Maybe you are afraid of conflicts with the keyword as in the oracle stored procedure.

2. In the stored procedure, when selecting a certain field, it must be followed by into. If the entire select record uses the cursor, it is another matter.

Select af. keynode into kn from APPFOUNDATION af where af. appid = aid and af. foundationid = fid; -- with into, select af is compiled correctly. keynode from APPFOUNDATION af where af. appid = aid and af. foundationid = fid; -- no into, Compilation Error, prompt: Compilation Error: PLS-00428: an INTO clause is expected in this SELECT statement

3. Using select... Into... Make sure that this record exists in the Database. Otherwise, an "no data found" exception is reported.

You can use select count (*) from to check whether the record exists in the Database. If yes, use select... Into...

4. In the stored procedure, the alias cannot be the same as the field name. Otherwise, although the compilation is successful, an error is reported during the running stage.

-- Correct select keynode into kn from APPFOUNDATION where appid = aid and foundationid = fid; -- error select af. keynode into kn from APPFOUNDATION af where af. appid = appid and af. foundationid = foundationid; -- an error occurs during the runtime, prompting ORA-01422: exact fetch returns more than requested number of rows

5. the null issue occurs during the stored procedure.

Assume that table A is defined as follows:

Create table A (id varchar2 (50) primary key not null, vcount number (8) not null, bid varchar2 (50) not null -- foreign key );

If you use the following statement in the stored procedure:

select sum(vcount) into fcount from A where bid='xxxxxx';

If the bid = "xxxxxx" record does not exist in Table A, fcount = null (even if the default value is set during fcount definition, for example, fcount number (8): = 0, fcount will still be null), so there may be problems when using fcount in the future, so it is best to judge here:

if fcount is null then    fcount:=0;end if;

In this way, everything is OK.