Developing PL/SQL subroutines and packages and writing triggers using PL/SQL, applying Oracle in JDBC

1. The various parts of the sub-program:

Declaration section, executable part, exception handling section (optional)

2. Sub-Program classification:

A. Procedures-performing certain operations

A. Syntax for creating a procedure:

CREATE [OR REPLACE] PROCEDURE <procedure name> [(<parameter list>)] Is|as

<local variable declaration>

BEGIN

<executable statements>

[EXCEPTION

<exception Handlers>]

END;

B. Three modes of process parameters:

In: Used to accept the value of the calling program, the default parameter mode

Out: Used to return a value to the calling program

In out: Used to accept the value of the calling program and return the updated value to the calling program

C. Grant the execution permissions of the procedure to other users:

Sql> GRANT EXECUTE on find_emp to MARTIN;

Sql> GRANT EXECUTE on swap to public;

D. Deletion process:sql> drop PROCEDURE find_emp;

B. Function-Perform operation and return value

A. Syntax for creating a function:

CREATE [OR REPLACE] FUNCTION <function name> [(param1,param2)] RETURN <datatype> is|as [local Declaratio ns

BEGIN

Executable statements;

RETURN result;

EXCEPTION

Exception handlers;

END;

B. Defining the limits of a function:

The function can only accept in arguments, but cannot accept in-out or out parameters

Formal parameters cannot be PL/SQL type

The return type of the function must also be a database type

C. Two ways to access a function: using PL/SQL blocks,

3. Advantages of sub-programs:

Modularity, reusability, maintainability, security

4. Package:

is the encapsulation of related processes, functions, variables, cursors, and exceptions.

A. The package consists of two parts: the specification and the main body

A. Specification: Declares a public object in a package. Includes types, variables, constants, exceptions, cursor specifications, and subroutine specifications

B. Principal: Declaring a package private object and implementing subroutines and cursors declared in the package specification

B. Creating packages

A. Package specifications

CREATE [OR REPLACE] Package package_name Is|as

[Public item declarations] [Subprogram specification]

END [Package_name];

B. Package body

CREATE [OR REPLACE] Package BODY package_name Is|as

[Private Item declarations]

[Subprogram bodies]

[BEGIN initialization]

END [Package_name];

5. Advantages of the Package

A. Modular

B. Easier application design

C. Information hiding

D. New features

E. Better performance

6. Cursors in the package

A. The definition of a cursor is divided into the cursor specification and the cursor body two parts

B. When declaring a cursor specification in a package specification, you must use the return clause to specify the return type of the tour cursor

The data type specified by the return clause can be:

A. Referencing a table-defined record type with the%rowtype property

B. Programmer-defined record types

7. Information about subroutines and packages

A. user_objects view contains information about the child programs and packages that are created by the user

SELECT object_name, object_type from User_objects WHERE object_type in (' PROCEDURE ', ' FUNCTION ', ' package ', ' package BOD ' Y ');

B. User_source view stores the source code for subroutines and packages

SELECT line, text from User_source WHERE name= ' TEST ';

8. Trigger:

is a stored procedure that executes automatically when a particular event occurs (a specific event can be a DML statement and a DDL statement that performs an update)

NOTE: Triggers cannot be explicitly called

9. Function of the trigger:

Automatically generate data, customize complex security permissions, provide auditing and logging, enable complex business logic

10. Syntax for creating triggers

CREATE [OR REPLACE] TRIGGER trigger_name after | Before | INSTEAD of [INSERT] [[or] UPDATE [of column_list]] [[or] DELETE] on Table_or_view_name

[Referencing {old [as] old/new [as] NEW}] [For each ROW]

[When (condition)] pl/sql_block;

Related concepts of DML triggers

A. Before Trigger: a trigger that executes before an operation occurs

B. After trigger: is a trigger that executes after an operation has occurred

C. Statement-level triggers: Executes only once, regardless of the number of rows affected

D. Row-level triggers: Execute once for each row modified by the DML statement

E. New pseudo record: Available only within UPDATE and insert DML triggers, it contains the values of the rows that were affected after the modification occurred

F. Old pseudo record: only available within update and delete DML triggers, it contains the values of the rows that were affected before the modification occurred

G. When clause: whether a piece of code is executed

12. The trigger consists of three parts:

A. Trigger statement (event): Defining DML and DDL events for activation triggers

B. Trigger limit: The condition that executes the trigger, which must be true to activate the trigger

C. Trigger action (Body): Contains SQL statements and code that run when a trigger statement is issued and the value of the triggering limit is true

. DML triggers:

PL/SQL blocks that are automatically executed when a table has data modification events (additions, deletions, modifications)

Grammar:

CREATE OR REPLACE TRIGGER trigger Name
{after| Before}--Specify trigger timing
{insert| delete| update}--specifying the trigger event type
On table name--Specify the monitored table
{For each ROW}--Specify the number of triggers

------above: Execution conditions--------------below: Code execution----------------
Declare

BEGIN
Code
END;

14. Conditional predicates

INSERTING, UPDATING, DELETING

15. Trigger Application

Use occasions: Control data security, achieve data audit, achieve data integrity, achieve referential integrity

INSTEAD of triggers (alternative triggers)

17. System Event Triggers

18. Managing triggers

A. Enabling and disabling triggers: When to prohibit: Prohibit data import and export

sql> ALTER TRIGGER aiu_itemfile DISABLE;

sql> ALTER TRIGGER aiu_itemfile ENABLE;

B. recompiling a trigger: After a database object has changed

C. Deleting a trigger

sql> DROP TRIGGER Aiu_itemfile;

19. Triggers and stored procedures

Trigger Stored procedures

Write no parameter, return value has parameter, return value (parameter)

Write cannot commit or rollback can

Call-way events occur automatically when called manually

Manipulating objects based on a table independent of the table

20.JDBC connecting Oracle database

Class.forName ("Oracle.jdbc.driver.OracleDriver");

Connection con = null;

con = drivermanager.getconnection (jdbc:oracle:thin: @localhost: 1521: db instance);

21. Execute PL/SQL anonymous blocks in JDBC

A. Steps:

A. Explicitly preparedstatement a string that executes an anonymous block

B. Creating a Preparedstatemen object

C. Set the binding variable as needed

D. Executing PreparedStatement objects

22. A stored procedure that executes PL/SQL in JDBC

A. Syntax:

A. {Call Procedure name [(?,?,?....)]} To access stored procedures with parameters

B. {?    = Cal process Name [(?,?,?.......)]} Accessing stored procedures that return result parameters

C. {Call Procedure name}//access to stored procedures without parameters

B. Steps:

A. Explicitly callablestatement call string

B. Create a CallableStatement object (callablestatement = Connection.preparecall (sql);)

C. Binding input (in) parameters

D. Registration output (out) parameters (CallableStatement. Registeroutparameter (Index, oracletypes. Data type))

E. Executing CallableStatement object return results

Developing PL/SQL subroutines and packages and writing triggers using PL/SQL, applying Oracle in JDBC