ORACLE stored procedure details, oracle Stored Procedure

ORACLE stored procedure details, oracle Stored Procedure
ORACLE Stored Procedure explanation 1. Definition

A Stored Procedure is a set of SQL statements used to complete specific database functions. The SQL statement set is compiled and Stored in the database system. When using this function, you can call and execute a stored procedure by specifying the name of the stored procedure you have defined and providing the relevant stored procedure parameters to complete one or more database operations.

2. Create a stored procedure

The Oracle stored procedure consists of three parts: Process Declaration, execution process, and stored procedure exception.

(1) No-parameter Stored Procedure syntax

Create or replace procedure NoParPro as // Declaration; begin // execution; exception // storage process exception; end;

(2) Example of stored procedure with Parameters

create or replace procedure queryempname(sfindno emp.empno%type)   as     sName emp.ename%type;     sjob emp.job%type;  begin         ....  exception         ....  end; 

(3) stored procedures with parameters include the Assignment Method

Create or replace procedure runbyparmeters (isal in emp. sal % type, sname out varchar, sjob in out varchar) as icount number; begin select count (*) into icount from emp where sal> isal and job = sjob; if icount = 1 then .... else .... end if; exception when too_many_rows then DBMS_OUTPUT.PUT_LINE ('Return value more than 1 line'); when others then DBMS_OUTPUT.PUT_LINE ('error in RUNBYPARMETERS process! '); End;

The IN parameter indicates the input parameter, which is the default mode of the parameter.

OUT indicates the return value parameter. The type can be of any legal type in Oracle.

The parameter defined in OUT mode can only be assigned a value within the process body, indicating that the parameter can pass a value back to the process of calling it.

In out indicates that this parameter can pass a value IN the process or pass a value OUT.

(4) usage of cursor definitions in Stored Procedures

As // definition (a result set that can be traversed by the CURSOR) CURSOR cur_1 is select area_code, CMCODE, SUM (RMB _amt)/10000 RMB _amt_sn, SUM (usd_amt) /10000 usd_amt_sn FROM BGD_AREA_CM_M_BASE_T WHERE ym> = vs_ym_sn_beg AND ym <= vs_ym_sn_end group by area_code, CMCODE; begin // execute (usually For statement traversal cursor) FOR rec IN cur_1 loop update xxxxxxxxxxx_T SET RMB _amt_sn = rec. RMB _amt_sn, usd_amt_sn = rec. usd_amt_sn WHERE area_code = rec. area_code and cmcode = rec. cmcode and ym = is_ym; end loop;

(5) cursor Definition

-- Display cursor processing declare --- declare cursor, create and name an SQL workspace cursor cursor_name is select real_name from account_hcz; v_realname varchar2 (20); begin open cursor_name; --- open cursor, fetch cursor_name into v_realname; -- extract cursor, extract records in the result set dbms_output.put_line (v_realname); close cursor_name; -- disable cursorend;

3. Calling stored procedures in Oracle (1) method 1

Declare realsal emp. sal % type; realname varchar (40); realjob varchar (40); begin // process call start realsal: = 1100; realname: = ''; realjob: = 'cler '; runbyparmeters (realsal, realname, realjob); -- DBMS_OUTPUT.PUT_LINE (REALNAME | ''| REALJOB); END; // process call ends

(2) process call method 2

Declare realsal emp. sal % type; realname varchar (40); realjob varchar (40); begin // process call start realsal: = 1100; realname: = ''; realjob: = 'cler '; -- The variable sequence corresponding to the specified value can be runbyparmeters (sname => realname, isal => realsal, sjob => realjob); DBMS_OUTPUT.PUT_LINE (REALNAME | ''| REALJOB); END; // process call ends

(3) method 3 (SQL command line)

1. SQL> exec proc_emp ('parameter 1', 'parameter 2'); // No return value is returned.

2. SQL> var vsal number

SQL> exec proc_emp ('parameter 1',: vsal); // a process call with a return value

Or: call proc_emp ('parameter 1',: vsal); // call a process with a returned value

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;

Notes

Stored procedure parameters do not have a value range; in indicates input; out indicates output; variables include a value range, followed by a semicolon; it is best to use count (*) before determining a statement (*) the function determines whether the operation record exists. select... Into... Assign a value to the variable. if an exception is thrown in the code, use raise + Exception name;

An error occurred while naming

Cause of naming system exception

ACCESS_INTO_NULL: the undefined object CASE_NOT_FOUND CASE. If no corresponding WHEN exists, 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 a row ZERO_DIVIDE division of 0 SUBSCRIPT_BEYOND_COUNT, the subscript of the element 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 while waiting for resources

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); FETCH C_USER INTO V_NAME; exit when fetch C_USER % NOTFOUND; 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

Questions about oracle Stored Procedure

1. in oracle, the data table alias 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.

6. Hibernate calls the oracle Stored Procedure

    this.pnumberManager.getHibernateTemplate().execute(                new HibernateCallback() {                    public Object doInHibernate(Session session)                            throws HibernateException, SQLException {                        CallableStatement cs = session                                .connection()                                .prepareCall("{call modifyapppnumber_remain(?)}");                        cs.setString(1, foundationid);                        cs.execute();                        return null;                    }                });