Shulou(Shulou.com)05/31 Report--

This article introduces you how to call the Oracle stored procedure, the content is very detailed, interested friends can refer to, hope to be helpful to you.

The stored procedure syntax for Oracle is as follows:

Create procedure stored procedure name (random) is here you can define constants, variables, cursors, complex data types here you can define variables, constant begin execution part end

(2) stored procedure syntax with parameters:

Create procedure stored procedure name (random) (variable 1 data type, variable 2 data type,..., variable n data type) is can define constants, variables, cursors, complex data types here you can define variables, constant begin execution part end

(3) stored procedure syntax with input and output parameters:

Create procedure stored procedure name (random) (variable 1 in (or out) data type, variable 2 in (or out) data type,..., variable n in (or out) data type) is can define constants, variables, cursors, complex data types here you can define variables, constant begin execution part end

Note: when creating a stored procedure with the above syntax, you may encounter a stored procedure with the same name in the database, so that Oracle will pop up and say that the name is already used by an existing object. There are two solutions:

Method 1: change the name of the stored procedure

Method 2: add the or replace keyword between the first create procedure, for example: the name of the create or replace procedure stored procedure. However, this method is not recommended because it replaces the stored procedure with the same name with the one you are currently writing

Stored procedure case 1: stored procedure with no parameters

Create replace procedure procedure_1isbegin dbms_output.put_line ('procedure_1.'); end

Stored procedure case 2: stored procedure with parameters

Create procedure procedure_2 (vSecreti number,v_j number) is Vambim number (5); begin dbms_output.put_line ('procedure_2.'); vSecretm: = vSecreti + vSecretj; dbms_output.put_line (vSecreti | |' +'| | vSecretj |'='| | vSecretm); end

Stored procedure case 3: stored procedure with input and output parameters

The parameters of stored procedures are divided into input parameters and output parameters.

Input parameter: an input parameter usually adds in between the variable name and the data type to indicate that the parameter is an input parameter

Output parameter: the output parameter usually adds out between the variable name and the data type to indicate that the variable is an output parameter

If you do not write in and out, it defaults to input parameters

Create procedure procedure_3 (in number,v_j in number,v_ m out number in number,v_j in number,v_ m out number) isbegin dbms_output.put_line ('procedure_3.'); v_m:=v_i-vyogj; dbms_output.put_line (Veteri | |' -'| | vSecretj | |'='| | vSecretm); end

Calling stored procedures in PL/SQL blocks

The following is an example of calling the above three stored procedures

Declare v_param1 number (5): = 2; v_param2 number (5): = 8; v_result number (5); begin-- call the stored procedure procedure_1 () of case 1 above;-- call the stored procedure procedure_2 of case 2 above (vicious param1);-- call the stored procedure procedure_3 of case 3 above Dbms_output.put_line (v_result); end;/* execution result: * / procedure_1.procedure_2.2 + 8 = 10procedure_3.2-8 =-610

Java calls stored procedures

Case 1: java calls a stored procedure with no return value

Requirements: write a record like database emp table to insert a record with the number 6666, name Zhang San and position MANAGER

/ * stored procedure * / create procedure procedure_4 (v_empno emp.empno%type,v_ename emp.ename%type,v_job emp.job%type) isbegin insert into emp (empno,ename,job) values; end;//java calls stored procedure public static void main (String [] args) {Connection conn=null; CallableStatement cs=null; ResultSet rs=null; / / java calls stored procedure try {Class.forName ("oracle.jdbc.OracleDriver") Conn=DriverManager.getConnection ("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott", "tiger"); cs=conn.prepareCall ("{call procedure_4 (,)}"); / / assign cs.setInt (1, 6666) to input parameters; cs.setString (2, "Zhang San"); cs.setString (3, "MANAGER"); cs.execute () / / execute} catch (Exception e) {e.printStackTrace ();} finally {closeResource (conn,cs,rs); / / close Resources}} / / after execution, a record with the number 6666, name Zhang San and position MANAGER is inserted into the emp table of the database.

Case 2: java calls a stored procedure that returns a single column and a single row

Requirement: write a stored procedure to find the employee's name according to the employee number, and call the stored procedure with java

/ * stored procedure * / create procedure procedure_5 (v_empno in emp.empno%type,v_ename out emp.ename%type) isbegin select ename into v_ename from emp where empno=v_empno;end;//java calls stored procedure public static void main (String [] args) {Connection conn=null; CallableStatement cs=null; ResultSet rs=null; try {Class.forName ("oracle.jdbc.OracleDriver") Conn=DriverManager.getConnection ("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott", "tiger"); cs=conn.prepareCall ("{call procedure_5 (,)}"); cs.setInt (1, 6666); / / assign values to input parameters / * specify the data type syntax of output parameters: oracle.jdbc.OracleTypes. The data type of the output parameter in this example the data type of the output parameter is varchar, so it is oracle.jdbc.OracleTypes.VARCHAR*/ cs.registerOutParameter (2, oracle.jdbc.OracleTypes.VARCHAR); cs.execute (); / / execute / / get the value of the output parameter, and the position should correspond to the output parameter? The output parameter of this example corresponds to the second question mark, and the data type of the output parameter is cs.getString (2) String a=cs.getString (2); System.out.println ("employee name:" + a);} catch (Exception e) {e.printStackTrace ();} finally {closeResource (conn,cs,rs) / / close resources}} / * execute the result, the console prints: * / result: employee name: Zhang San

Case 3: java calls a stored procedure that returns a single row and multiple columns

Requirements: write a stored procedure to find the employee's name, position and salary according to the employee number, and call the stored procedure with java

/ * stored procedure * / create procedure procedure_6 (v_empno in emp.empno%type,v_ename out emp.ename%type,v_job out emp.job%type,v_sal out emp.sal%type) isbegin select ename,job,sal into vprincipenameMagazine JobMagazal from emp where empno=v_empno;end;//java calls stored procedure public static void main (String [] args) {Connection conn=null; CallableStatement cs=null; ResultSet rs=null Try {Class.forName ("oracle.jdbc.OracleDriver"); conn=DriverManager.getConnection ("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott", "tiger"); cs=conn.prepareCall ("{call procedure_6 (?)}"); cs.setInt (1, 7788); / / specify the data type of the output parameters. Note: the order corresponds to cs.registerOutParameter (2, oracle.jdbc.OracleTypes.VARCHAR). Cs.registerOutParameter (3, oracle.jdbc.OracleTypes.VARCHAR); cs.registerOutParameter (4, oracle.jdbc.OracleTypes.DOUBLE); cs.execute (); / execute / / get the return value String ename=cs.getString (2); / / get name String job=cs.getString (3); / / get position double sal=cs.getDouble (4) / / get salary System.out.println ("employee number is 7788: + ename+" position is: "+ job+" salary is "+ sal);} catch (Exception e) {e.printStackTrace ();} finally {closeResource (conn,cs,rs); / / close resources}} / * execution result, console print: * / employee number 7788 name: SCOTT position: ANALYST salary: 3000.0

Case 4: java calls a stored procedure that returns multiple rows and columns (return list)

Requirement: write a stored procedure that finds all employee information of the department according to the department number, and call the stored procedure with java

/ * define cursor * / create package my_package astype emp_cursor is ref cursor;end my_package;/* stored procedure * / create procedure procedure_7 (v_deptno in emp.deptno%type,emp_cursor out my_package.emp_cursor) isbegin open emp_cursor for select * from emp where deptno=v_deptno;end;//java call stored procedure public static void main (String [] args) {Connection conn=null; CallableStatement cs=null; ResultSet rs=null; try {Class.forName ("oracle.jdbc.OracleDriver") Conn=DriverManager.getConnection ("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott", "tiger"); cs=conn.prepareCall ("{call procedure_7 (,)}"); cs.setInt (1,20); / / assign cs.registerOutParameter (2, oracle.jdbc.OracleTypes.CURSOR) to the input parameter; / / specify the data type of the output parameter cs.execute (); rs= (ResultSet) cs.getObject (2) / / get the value of the output parameter while (rs.next ()) {/ / in the order before and after the fields in the database, for example, the fifth column in the database emp table is hiredate, and the data type is Date, so you should use rs.getDate (5) System.out.println (rs.getInt (1) + "+ rs.getString (2) +" + rs.getDate (5)) when getting the fifth column value. }} catch (Exception e) {e.printStackTrace ();} finally {closeResource (conn,cs,rs); / / close resources}}

/ * the following is the information of all the employees in Department 20. Here we only print the number, name and entry time for our convenience.

Run the result and print it on the console: * /

7369 SMITH 1980-12-177566 JONES 1981-04-027788 SCOTT 1987-04-197876 ADAMS 1987-05-237902 FORD

This is the code above that java calls the stored procedure code to close the resource method.

Public static void closeResource (Connection conn,CallableStatement cs,ResultSet rs) {if (rswatches null) {try {rs.close ();} catch (SQLException e) {e.printStackTrace ();}} if (csfolk null) {try {cs.close ();} catch (SQLException e) {e.printStackTrace () }} if (connexion null) {try {conn.close ();} catch (SQLException e) {e.printStackTrace ();}

Finally, give an application, paged stored procedure.

Paging stored procedures:

/ * define cursors * / create package page_package astype page_cursor is ref cursor;end page_package / * stored procedure * / create procedure pro_paging (how many v_page_count out number,-- pages per page is displayed by v_page_size in number,-- v_current_page in number,-- current page v_total_count out number -- the cursor of the query result set returned by the total number of records emp_cursor out page_package.page_cursor--) is v_begin number (5): = vaccounpageuplosize* (v_current_page-1) + 1 -- query start location v_end number (5): = vaccounpagetemporsizeuploading currentparts where rownum='.-query end location v_sql varchar2 (1000): = 'select empno,ename from (select a.empnomema. Rn from (select empno,ename from emp) a where rownum=' | | v_begin / * it cannot be written like the following, otherwise an error of inconsistent type will be reported when the stored procedure is called, because only empno,ename is checked on the inside, so the outside should be consistent with the inside (1000): =\ 'select * from (select a. Select * rn from (select empno,ename from emp) a where rownum=' | | vested beginscape / v_ename varchar2 (10) V_empno number (4); begin open emp_cursor for vSecretsql; loop fetch emp_cursor into vandalism empnoop from emp'; execute immediate v_sql into v_total_count; exit when emp_cursor%notfound; dbms_output.put_line (v_empno | |'| | v_ename); end loop; v_sql:='select count (empno) from emp'; execute immediate v_sql into v_total_count If (mod (vantaltaltaltaltaltaltaltaltalcountMagnevrecords size) = 0) then vprincippagecountGetWord (else v_page_count:=trunc) (v_total_count/v_page_size) + 1; end if; dbms_output.put_line ('total' | v_total_count | | 'records'); dbms_output.put_line ('total' | v_page_count | | 'page') Dbms_output.put_line ('current page:' | v_current_page); dbms_output.put_line ('display per page' | | v_page_size | | 'bars'); end

The Java call is the same as the above example of java calling a stored procedure. For convenience, it is called directly in pl/sql.

/ * call the paging stored procedure * / declare v_page_count number (5); v_cursor page_package.page_cursor; v_total_count number (5); begin dbms_output.put_line ('first page of data.') ; pro_paging (5 words words-5 entries per page); dbms_output.put_line ('- -'); dbms_output.put_line ('- -'); dbms_output.put_line ('page 2 data') -- display the second page of data pro_paging (5 minutes words-5 entries per page)-total number of pages 2-current page vposts totaltaltalcountLay-record total number of entries-cursor); end;/* run result: * / first page of data. 6666 Zhang San 20 empSu219 empSave27369 SMITH7499 ALLEN, a total of 17 records, a total of 4 pages of the current page: 1 each page displays 5 items-the second page of data. 7521 WARD7566 JONES7654 MARTIN7698 BLAKE7782 CLARK a total of 17 records a total of 4 pages current page: 2 each page shows 5 about how to call Oracle stored procedures to share here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.