Shulou(Shulou.com)06/02 Report--

Business rules and business logic can be stored in Oracle through a program, which is a stored procedure.

Stored procedures are a combination of SQL, PL/SQL, and Java statements that enable you to move code that executes business rules from your application to the database. The result is that the code is stored once but can be used by multiple programs.

To create a process object (procedural object), you must have CREATE PROCEDURE system permissions. If this process object needs to be used by another user schema, then you must have CREATE ANY PROCEDURE permission. Excute permission may be required when performing procedure. Or EXCUTE ANY PROCEDURE permissions. If permissions are granted separately, as shown in the following example:

Grant execute on MY_PROCEDURE to Jelly

An example of calling a stored procedure:

Execute MY_PROCEDURE ('ONE PARAMETER')

The difference between stored procedures (PROCEDURE) and functions (FUNCTION).

Function has a return value, and you can reference function and or use the return value of function directly in Query.

In essence, there is no difference, all PL/SQL programs, can have a return value. The most fundamental difference is that a stored procedure is a command and a function is part of an expression. For example:

Select max (NAME) FROM

But cannot exec max (NAME) if max is a function at this time.

PACKAGE is a combination of function,procedure,variables and sql statements. Package allows multiple procedure to use the same variable and cursor.

The syntax for creating a procedure:

CREATE [OR REPLACE] PROCEDURE [schema.] procedure

[(argument [IN | OUT | IN OUT] [NO COPY] datatype

[, argument [IN | OUT | IN OUT] [NO COPY] datatype]...

)]

[authid {current_user | definer}]

{is | as} {pl/sql_subprogram_body |

Language {java name 'String' | c [name, name] library lib_name

}]

Sql Code:

CREATE PROCEDURE sam.credit (acc_no IN NUMBER, amount IN NUMBER) AS

BEGIN

UPDATE accounts

SET balance = balance + amount

WHERE account_id = acc_no

END

You can use the create or replace procedure statement, and the purpose of this statement is that all the excute permissions you have previously given will be retained.

IN, OUT, IN OUT are used to modify parameters.

IN indicates that the variable must be assigned by the caller and passed into PROCEDURE for processing.

OUT indicates that PRCEDURE passes the value back to the caller through this variable.

IN OUT is a combination of the two.

Authid represents two permissions:

Definer permissions (difiner right default), executor permissions (invoker right).

The definer permission indicates that the required permissions for tables, views and other objects involved in the procedure can be accessed as long as the definer has the permission.

Executor permissions require that the user calling the procedure has permissions on the relevant tables and objects.

Basic syntax of Oracle stored procedures

1. Basic structure

CREATE OR REPLACE PROCEDURE stored procedure name

(

Parameter 1 IN NUMBER

Parameter 2 IN NUMBER

) AS

Variable 1 INTEGER: = 0

Variable 2 DATE

BEGIN

END stored procedure name

2. SELECT INTO STATEMENT

Save the results of a select query into variables. Multiple columns can be stored in multiple variables at the same time. There must be one.

Record, otherwise throw an exception (throw NO_DATA_FOUND if there is no record)

Example:

BEGIN

SELECT col1,col2 into variable 1, variable 2 FROM typestruct where xxx

EXCEPTION

WHEN NO_DATA_FOUND THEN

Xxxx

WHEN OTHERS THEN

Xxxx

END

...

3. IF judgment

IF V_TEST=1 THEN

BEGIN

Do something

END

END IF

4. While cycle

WHILE V_TEST=1 LOOP

BEGIN

XXXX

END

END LOOP

5. Variable assignment

V_TEST: = 123

6. Use for in to use cursor

...

IS

CURSOR cur IS SELECT * FROM xxx

BEGIN

FOR cur_result in cur LOOP

BEGIN

V_SUM: = cur_result. Listed as 1+cur_result. Column 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

Establish a Test WINDOW after connecting to the database

Enter the code to call SP in the window, and F9 starts debug,CTRL+N step debugging

9. Execute stored procedures in Pl/Sql

In sql*plus:

Declare

Necessary variable declarations, depending on your process

Begin

Execute yourprocudure (parameter1,parameter2,...)

End

/

Call the stored procedure in SQL/PLUS to display the result:

SQL > set serveoutput on-- Open output

SQL > var info1 number;-- output 1

SQL > var info2 number;-- output 2

SQL > declare

Var1 varchar2 (20);-- enter 1

Var2 varchar2 (20);-- enter 2

Var3 varchar2 (20);-- enter 2

BEGIN

Pro (var1,var2,var3,:info1,:info2)

END

/

SQL > print info1

SQL > print info2

Note: the EXECUTE IMMEDIATE STR statement is a dynamic execution statement in SQLPLUS, which is automatically submitted during execution, similar to the FORMS_DDL statement in DP, in which str cannot wrap, only through the concatenation character "| |", or by adding the "-" concatenation character in the newline.

Notes on some questions about Oracle stored procedures

1. In Oracle, data table aliases cannot be added with as. Such as:

Select a.appname from appinfo a Teng Mui-correct

Select a.appname from appinfo as a Ting Mui-error

Perhaps it is afraid of conflicts with the keyword as in stored procedures in Oracle

two。 In a stored procedure, when you select a field, it must be followed by into, which is another matter if you select the entire record and use cursors. Select af.keynode into kn

From APPFOUNDATION af

Where af.appid=aid and af.foundationid=fid;-with into, compiled correctly

Select af.keynode

From APPFOUNDATION af

Where af.appid=aid and af.foundationid=fid;-- does not have into, compilation error is reported, prompt: Compilation Error: PLS-00428: an INTO clause is expected in this SELECT statement

3. Taking advantage of select...into... Syntax, you must first make sure that the record is in the database, otherwise a "no data found" exception will be reported. Before this syntax, you can use select count (*) from to check whether the record exists in the database, and if so, use select...into...

4. In a stored procedure, the alias cannot be the same as the field name, otherwise, although the compilation can pass, an error select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid will be reported at run time

-- run correctly

Select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid

-- an error is reported during the run phase, prompting:

ORA-01422:exact fetch returns more than requested number of rows

5. In the stored procedure, the question about the occurrence of null assumes that there is a table A 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 are in a stored procedure, use the following statement:

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

If the record of bid= "xxxxxx" does not exist in table A, fcount=null (even if the default value is set when fcount is defined, such as: fcount number (8): = 0 is still invalid, fcount will still become null), so there may be problems when using fcount in the future, so it is best to make a judgment here:

If fcount is null then

Fcount:=0

End if

That makes everything 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

}

});

Summary of calling Oracle stored procedures with Java

1. Stored procedure test table with no return value:

-- Create table

Create table TESTTB

(

ID VARCHAR2 (30)

NAME VARCHAR2 (30)

)

Tablespace BOM

Pctfree 10

Initrans 1

Maxtrans 255

Storage

(

Initial 64K

Minextents 1

Maxextents unlimited

);

Example: the stored procedure is (of course, this first requires the creation of a table TESTTB, in which two fields (Icoded ID _ itemic name).

):

CREATE OR REPLACE PROCEDURE TESTA (PARA1 IN VARCHAR2, PARA2 IN VARCHAR2) AS

BEGIN

INSERT INTO BOM.TESTTB (ID, NAME) VALUES (PARA1, PARA2)

END TESTA

When called in Java, use the following code:

Package com.yiming.procedure.test

Import java.sql.CallableStatement

Import java.sql.Connection

Import java.sql.DriverManager

Import java.sql.ResultSet

Import java.sql.SQLException

Import java.sql.Statement

Public class TestProcedureDemo1 {

Public TestProcedureDemo1 () {

}

Public static void main (String [] args) {

String driver = "Oracle.jdbc.driver.OracleDriver"

String strUrl = "jdbc:Oracle:thin:@10.20.30.30:1521:vasms"

Statement stmt = null

ResultSet rs = null

Connection conn = null

CallableStatement proc = null

Try {

Class.forName (driver)

Conn = DriverManager.getConnection (strUrl, "bom", "bom")

Proc = conn.prepareCall ("{call BOM.TESTA (?)}")

Proc.setString (1,100,100)

Proc.setString (2, "TestOne")

Proc.execute ()

} catch (SQLException ex2) {

Ex2.printStackTrace ()

} catch (Exception ex2) {

Ex2.printStackTrace ()

} finally {

Try {

If (rs! = null) {

Rs.close ()

If (stmt! = null) {

Stmt.close ()

}

If (conn! = null) {

Conn.close ()

}

}

} catch (SQLException ex1) {

}

}

}

}

2. Stored procedures with return values (non-list) example: stored procedures are:

CREATE OR REPLACE PROCEDURE TESTB (PARA1 IN VARCHAR2, PARA2 OUT VARCHAR2) AS

BEGIN

SELECT NAME INTO PARA2 FROM TESTTB WHERE ID = PARA1

END TESTB

When called in Java, use the following code:

Package com.yiming.procedure.test

Import java.sql.CallableStatement

Import java.sql.Connection

Import java.sql.DriverManager

Import java.sql.ResultSet

Import java.sql.SQLException

Import java.sql.Statement

Import java.sql.Types

Public class TestProcedureDemo2 {

Public static void main (String [] args) {

String driver = "Oracle.jdbc.driver.OracleDriver"

String strUrl = "jdbc:Oracle:thin:@10.20.30.30:1521:vasms"

Statement stmt = null

ResultSet rs = null

Connection conn = null

CallableStatement proc = null

Try {

Class.forName (driver)

Conn = DriverManager.getConnection (strUrl, "bom", "bom")

Proc = conn.prepareCall ("{call BOM.TESTB (?)}")

Proc.setString (1,100,100)

Proc.registerOutParameter (2, Types.VARCHAR)

Proc.execute ()

String testPrint = proc.getString (2)

System.out.println ("= testPrint=is=" + testPrint)

} catch (SQLException ex2) {

Ex2.printStackTrace ()

} catch (Exception ex2) {

Ex2.printStackTrace ()

} finally {

Try {

If (rs! = null) {

Rs.close ()

If (stmt! = null) {

Stmt.close ()

}

If (conn! = null) {

Conn.close ()

}

}

} catch (SQLException ex1) {

}

}

}

}

Note that the value 2 in proc.getString (2) here is not arbitrary, but corresponds to the out column in the stored procedure. If out is in the first position, it is proc.getString (1), and if it is the third position, it is proc.getString (3). Of course, there can also be multiple return values at the same time, that is, add a few more out parameters.

Return list because the Oracle stored procedure has no return value, all its return values are replaced by out parameters, and the list is no exception, but because it is a collection, you can't use general parameters, you have to use pagkage. So it's divided into two parts.

1. Build a package. As follows:

CREATE OR REPLACE PACKAGE TESTPACKAGE AS

TYPE TEST_CURSOR IS REF CURSOR

End TESTPACKAGE

two。 Establish a stored procedure, which is:

CREATE OR REPLACE PROCEDURE TESTC (P_CURSOR out TESTPACKAGE.TEST_CURSOR) IS

BEGIN

OPEN P_CURSOR FOR

SELECT * FROM BOM.TESTTB

END TESTC

As you can see, it returns the value of the cursor (which can be understood as a pointer) as an out parameter.

When called in Java, use the following code:

It should be noted here that the driver package of Oracle must be placed in the class path before execution, otherwise an error will be reported.

Package com.yiming.procedure.test

Import java.sql.CallableStatement

Import java.sql.Connection

Import java.sql.DriverManager

Import java.sql.ResultSet

Import java.sql.SQLException

Import java.sql.Statement

Public class TestProcedureDemo3 {

Public static void main (String [] args) {

String driver = "Oracle.jdbc.driver.OracleDriver"

String strUrl = "jdbc:Oracle:thin:@10.20.30.30:1521:vasms"

Statement stmt = null

ResultSet rs = null

Connection conn = null

CallableStatement proc = null

Try {

Class.forName (driver)

Conn = DriverManager.getConnection (strUrl, "bom", "bom")

Proc = conn.prepareCall ("{call bom.testc}")

Proc.registerOutParameter (1, Oracle.jdbc.OracleTypes.CURSOR)

Proc.execute ()

Rs = (ResultSet) proc.getObject (1)

While (rs.next ()) {

System.out.println ("" + rs.getString (1) + ""

+ rs.getString (2) + "")

}

} catch (SQLException ex2) {

Ex2.printStackTrace ()

} catch (Exception ex2) {

Ex2.printStackTrace ()

} finally {

Try {

If (rs! = null) {

Rs.close ()

If (stmt! = null) {

Stmt.close ()

}

If (conn! = null) {

Conn.close ()

}

}

} catch (SQLException ex1) {

}

}

}

}

Make a simple dynamic query in a stored procedure

Do simple dynamic query code in the stored procedure, such as:

CREATE OR REPLACE procedure ZXM_SB_GZ_GET

(p_table in varchar2

P_name in varchar2

P_value in varchar2

Outpara out lntxdba.zxm_pag_cs_power.c_type

)

As

Begin

Declare

Wherevalue varchar2 (200)

Begin

Wherevalue:=select * from | | p_table | | where | | p_name | | = | p_value

Open outpara for

Wherevalue

End

End

In general PL/SQL programming, SQL can be used directly in DML and transaction control statements, but DDL statements and system control statements can not be directly used in PL/SQL. If you want to use DDL statements and system control statements in PL/SQL, you can use dynamic SQL.

First of all, we should understand what dynamic SQL is. The SQL we use in Oracle database development PL/SQL block is divided into static SQL statement and dynamic SQL statement. The so-called static SQL means that the SQL statement used in the PL/SQL block is explicit at compile time and executes the determined object. Dynamic SQL means that the SQL statement is uncertain when the PL/SQL block is compiled, such as performing different operations according to the parameters entered by the user. The compiler does not deal with the dynamic statement part, but dynamically creates the statement, parses the statement, and executes the statement while the program is running.

Dynamic SQL in Oracle can be executed either through the local dynamic SQL or through the DBMS_SQL package. The following two cases are explained respectively:

1. Local dynamic SQL local dynamic SQL is realized by using EXECUTE IMMEDIATE statement.

1. Local dynamic SQL executes DDL statements:

Requirements: create a table dynamically according to the table name and field name entered by the user.

Create or replace procedure proc_test

(

Table_name in varchar2,-- Table name

Field1 in varchar2,-- Field name

Datatype1 in varchar2,-- Field type

Field2 in varchar2,-- Field name

Datatype2 in varchar2-Field Typ

) as

Str_sql varchar2 (500)

Begin

Str_sql:='create table'| | table_name | |'('| | field1 | |'| | datatype1 | |','| | field2 | |'| | datatype2 | |')'

Execute immediate str_sql;-dynamic execution of DDL statements

Exception

When others then

Null

End

The above is the compiled stored procedure code. Let's execute the stored procedure to build the table dynamically.

SQL > execute proc_test ('dinya_test','id','number (8) not null','name','varchar2')

PL/SQL procedure successfully completed

SQL > desc dinya_test

Name Type Nullable Default Comments

-

ID NUMBER (8)

NAME VARCHAR2 (100) Y

SQL >

At this point, we realize our requirement by using local dynamic SQL to dynamically execute DDL statements according to the table name and field name, field type and other parameters entered by the user.

2. Local dynamic SQL executes DML statements.

Requirements: insert the values entered by the user into the dinya_ test table created in the above example.

Create or replace procedure proc_insert

(

Id in number,-- enter the serial number

Name in varchar2-enter a name

) as

Str_sql varchar2 (500)

Begin

Str_sql:='insert into dinya_test values (: 1Jing Zhi 2)'

Execute immediate str_sql using id,name;-perform insert operations dynamically

Exception

When others then

Null

End

Execute the stored procedure to insert data into the test table.

SQL > execute proc_insert (1)

PL/SQL procedure successfully completed

SQL > select * from dinya_test

ID NAME

1 dinya

In the above example, the local dynamic SQL uses the using clause to execute the DML statement and binds the input values to variables sequentially. If you need to output parameters, you can use the RETURNING INTO clause when executing dynamic SQL, such as:

Declare

P_id number:=1

V_count number

Begin

V_string:='select count (*) from table_name a where a.

Execute immediate v_string into v_count using p_id

End

2. Using DBMS_SQL package to implement dynamic SQL using DBMS_SQL package is as follows:

A, the SQL statement or a block of statements to be executed first is placed in a string variable.

Use the parse procedure of the DBMS_SQL package to parse the string.

C. use the bind_variable procedure of the DBMS_SQL package to bind variables.

D. use the execute function of the DBMS_SQL package to execute the statement.

1. Use the DBMS_SQL package to execute DDL statements

Requirements: use DBMS_SQL package to build tables based on table names, field names, and field types entered by the user.

Create or replace procedure proc_dbms_sql

(

Table_name in varchar2,-- Table name

Field_name1 in varchar2,-- Field name

Datatype1 in varchar2,-- Field type

Field_name2 in varchar2,-- Field name

Datatype2 in varchar2-Field Typ

) as

V_cursor number;-defines the cursor

V_string varchar2;-- define string variables

V_row number;-number of Lin

Begin

Vandalism cursorside opens the cursor for processing.

V_string:='create table'| | table_name | |'('| | field_name1 | |'| | datatype1 | |','| | field_name2 | |'| | datatype2 | |')'

Dbms_sql.parse (vandalism cursorrevedstring.native);-- parsing statement

V_row:=dbms_sql.execute (v_cursor);-- execute the statement

Dbms_sql.close_cursor (v_cursor);-- close the cursor

Exception

When others then

Dbms_sql.close_cursor (v_cursor);-- close the cursor

Raise

End

After the above procedure has been compiled, the execution process creates the table structure:

SQL > execute proc_dbms_sql ('dinya_test2','id','number (8) not null','name','varchar2')

PL/SQL procedure successfully completed

SQL > desc dinya_test2

Name Type Nullable Default Comments

-

ID NUMBER (8)

NAME VARCHAR2 (100) Y

SQL >

2. Use DBMS_SQL package to execute DML statement

Requirements: use the DBMS_SQL package to update the corresponding records in the table based on the values entered by the user.

View the records already in the table:

SQL > select * from dinya_test2

ID NAME

1 Oracle

2 CSDN

3 ERP

SQL >

Build the stored procedure and compile it through:

Create or replace procedure proc_dbms_sql_update

(

Id number

Name varchar2

) as

V_cursor number;-defines the cursor

V_string varchar2;-- string variable

V_row number;-number of Lin

Begin

Vandalism cursorside opens the cursor for processing.

V_string:='update dinya_test2 a set a.name=:p_name where a. Idlers.

Dbms_sql.parse (vandalism cursorrevedstring.native);-- parsing statement

Dbms_sql.bind_variable (vastly cursorforcecontrol name);-- binds variables

Dbms_sql.bind_variable (vastly cursorforcecontrolling _ id);-- binding variables

V_row:=dbms_sql.execute (v_cursor);-- execute dynamic SQL

Dbms_sql.close_cursor (v_cursor);-- close the cursor

Exception

When others then

Dbms_sql.close_cursor (v_cursor);-- close the cursor

Raise

End

Perform the process to update the data in the table according to the parameters entered by the user:

SQL > execute proc_dbms_sql_update (2)

PL/SQL procedure successfully completed

SQL > select * from dinya_test2

ID NAME

1 Oracle

2 csdn_dinya

3 ERP

SQL >

Update the data of the name field in Article 2 to the new value csdn_dinya after performing the procedure. This completes the function of using the dbms_sql package to execute DML statements.

In DBMS_SQL, if the dynamic statement to be executed is not a query statement, use DBMS_SQL.Execute or DBMS_SQL.Variable_Value to execute, if the dynamic statement to be executed is a query statement, use DBMS_SQL.define_column to define the output variable, and then use DBMS_SQL.Execute, DBMS_SQL.Fetch_Rows, DBMS_SQL.Column_Value and DBMS_SQL.Variable_Value to execute the query and get the results.

Summary description:

In the process of Oracle development, we can use dynamic SQL to execute DDL statements, DML statements, transaction control statements and system control statements. However, it should be noted that the use of dynamic SQL to execute DDL statements in PL/SQL blocks is different from others. It is illegal to use binding variables in DDL (bind_variable). After analysis, there is no need to execute DBMS_SQL.Bind_Variable, just add the input variables to the string. In addition, DDL is executed when DBMS_SQL.PARSE is called, so DBMS_SQL.EXECUTE may not be used, that is, it can be omitted in the v_row:=dbms_sql.execute (v_cursor) section of the example above.

The Oracle stored procedure calls the Java method

Call Java program segment in stored procedure

Software environment:

1. Operating system: Windows 2000 Server

2. Data library: Oracle 8i R2 (8.1.7) for NT Enterprise Edition

3. Installation path: C:\ ORACLE

Implementation method:

1. Create a file as Test.java

Public class Test {

Public static void main (String args []) {

System.out.println ("HELLO THIS iS A Java PROCEDURE")

}

}

2 、 javac Test.java

3 、 java Test

4. SQL > conn system/manager

SQL > grant create any directory to scott

SQL > conn scott/tiger

SQL > create or replace directory test_dir as'd:\'

The directory has been created.

SQL > create or replace java class using bfile (test_dir,'TEST.CLASS')

2 /

Java has been created.

SQL > select object_name,object_type,STATUS from user_objects

SQL > create or replace procedure test_java

As language java

Name 'TEST.main (java.lang.String [])'

/

The procedure has been created.

SQL > set serveroutput on size 5000

SQL > call dbms_java.set_output (5000)

The call is complete.

SQL > execute test_java

HELLO THIS iS A Java PROCEDURE

The PL/SQL process completed successfully.

SQL > call test_java ()

HELLO THIS iS A Java PROCEDURE

The call is complete.

Oracle 8I 9I passed the test.

An example of Oracle efficient paging stored procedure

Create or replace package p_page is

-- Author: PHARAOHS

-- Created: 2006-4-30 14:14:14

-- Purpose: the paging process

TYPE type_cur IS REF CURSOR;-defines a cursor variable to return a recordset

PROCEDURE Pagination (

Pindex in number,-- pagination index

Psql in varchar2,-- generate the sql statement of dataset

Psize in number,-- Page size

Pcount out number,-- returns the total number of pages

V_cur out type_cur-returns the current page data record

);

Procedure PageRecordsCount (

Psqlcount in varchar2,-- generate the sql statement of dataset

Prcount out number-returns the total number of records

);

End p_page

/

Create or replace package body p_page is

PROCEDURE Pagination (

Pindex in number

Psql in varchar2

Psize in number

Pcount out number

V_cur out type_cur

)

AS

V_sql VARCHAR2 (1000)

V_count number

V_Plow number

V_Phei number

Begin

-take the total number of pages

V_sql: = 'select count (*) from (' | | Psql | |')'

Execute immediate v_sql into v_count

Pcount: = ceil (v_count/Psize)

-display any page of content

V_Phei: = Pindex * Psize + Psize

V_Plow: = v_Phei-Psize + 1

-- Psql: = 'select rownum rn,t.* from zzda t';-- must contain a rownum field

V_sql: = 'select * from (' | | Psql | |') where rn between'| | v_Plow | | 'and' | | v_Phei

Open v_cur for v_sql

End Pagination

-- *

Procedure PageRecordsCount (

Psqlcount in varchar2

Prcount out number

)

As

V_sql varchar2 (1000)

V_prcount number

Begin

V_sql: = 'select count (*) from (' | | Psqlcount | |')'

Execute immediate v_sql into v_prcount

Prcount: = vested printers;-- returns the total number of records

End PageRecordsCount

-- *

End p_page

/

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.