Oracle stored Procedures-a complete example of accessing stored procedure procedures in an application

Understanding stored procedures and functions

Stored procedures and functions are also a PL/SQL block, which is a PL/SQL block deposited into the database. But stored procedures and functions are different from the PL/SQL programs that have been described, and we usually refer to the PL/SQL program as a nameless block, and stored procedures and functions are stored in the database in a named way. Compared to the PL/SQL program, there are many advantages to stored procedures, which are summarized as follows:
* Stored procedures and functions are stored in the database as named database objects. The advantages that are stored in the database are obvious because the code is not saved locally, and the user can log on to the database on any client computer and invoke or modify the code.
* Stored procedures and functions can be secured by the database, and to use stored procedures and functions, the owner of the stored procedure and function is authorized, and only the authorized user or creator can execute the stored procedure or call the function.
* Stored procedures and functions are written to the data dictionary, so stored procedures can be thought of as a common module that can be called by user-written PL/SQL programs or other stored procedures (but stored procedures and functions cannot invoke PL/SQL programs). A reusable function can be designed as a stored procedure, such as: Display a payroll table, can be designed as a stored procedure; a frequently-invoked calculation can be designed as a storage function, and the employee's name can be designed as a storage function based on the employee number.
* Like the procedures and functions of other high-level languages, parameters can be passed to stored procedures or functions, and parameters are passed in several ways. A stored procedure can have a return value, or it can have no return value, and the return value of the stored procedure must be brought back through the parameter, the function has a certain data type, like other standard functions, we can return the function value by calling the function name.
Stored procedures and functions need to be compiled to exclude syntax errors that can only be called by compilation.

First you have to have the box data, so the first step is to create the box to insert the data:

Sql> CREATE TABLE emp  2  (empno number,  3  ename varchar2,  4  sal number,  5  Job VARCHAR2 (6)  ; Table created sql> INSERT INTO EMP values (1, ' Wang Yi ', 10000, ' software engineer '); 1 row inserted sql> INSERT INTO EMP values (2, ' King II ', 8000, ' photographer '); 1 row inserted sql> select * from EMP;      EMPNO ename             SAL JOB---------------------------------------------------------------------------------------- ----------------------         1 Wang Yi          10000 software engineer         2 Wang ER           

Now ask us to inquire about an employee's name, salary and position, then we create a stored procedure as follows:

--out parameters: Query for an employee's name, monthly salary and position/*  think:  1, query All information of an employee-->out too many parameters?  2. Query All information about all employees in a department returns a collection in-->out? */--Delete stored procedure Create or Replace procedure queryempincome1 (Eno in number,                                           pename out varchar2,                                           psal out number,                                           Pjob out VARCHAR2)                                          as begin   --Get the employee's name, salary and position    select Ename,sal,job into Pename,psal,pjob from EMP where empno =eno;                                    end;/                           

Then we write Java code in Eclipse to link the Oracle database and access the stored procedure, and in the console output the user's basic information for the specified ID:

Package Cn.edu.jdbc;import Java.sql.callablestatement;import Java.sql.connection;import oracle.jdbc.OracleTypes;                                           Import org.junit.test;/** * Create or Replace procedure Queryempinform (Eno in number,                                           Pename out VARCHAR2, psal out number, Pjob out VARCHAR2) * @author WF * */public class TestProcedure {@Testpublic void TestProcedure () {//{call <procedu re-name[(&LT;ARG1&GT;,&LT;ARG2&GT;,&LT;ARH3&GT; ...)] >}string sql = "{call queryempincome1 (?,?,?,?)}"; Connection Connection = null; CallableStatement call = null;try {//get a connection connection = Jdbcutils.getconnection ();//create output by connection Statementcall = Connection.preparecall (SQL);//For the In (input) parameter, assign a value of Call.setint (1, 2);//For the first stored procedure the parameter is assigned to 1 for the query number 1 Employee information//for Out (output), need to declare// Call.registeroutparameter (2, SqlType); Call.registeroutparameter (2, Oracletypes.varchar);// Specifies the output parameter type of Oracle Call.registeroutparameter (3, oracletypes.number); CALL.REGISTEROUTPArameter (4, Oracletypes.varchar);//Execute call Call.execute ();//Fetch result string name = Call.getstring (2);D ouble sal = Call.getdouble (3); String job = call.getstring (4); System.out.println ("Name:" +name+ "\ T" + "Salary:" +sal+ "\ T" + "work:" +job);} catch (Exception e) {e.printstacktrace ();}}}

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle stored Procedures-a complete example of accessing stored procedure procedures in an application