Develop PL/SQL subprograms and packages, write triggers using PL/SQL, and Apply Oracle and pljdbc to JDBC.

Develop PL/SQL subprograms and packages, write triggers using PL/SQL, and Apply Oracle and pljdbc to JDBC.

1. subprograms:

Declaration, executable, and Exception Handling (optional)

2. subprogram classification:

A. Process-execute some operations

A. syntax of the creation process:

CREATE [or replace] PROCEDURE <procedure name> [(<parameter list>)] IS |

<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, default parameter Mode

OUT: Used to return values to the caller.

In out: Used to accept the value of the caller and return the updated value to the caller.

C. Grant the execution permission of the process 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-execute the operation and return the value

A. syntax for creating a function:

CREATE [or replace] FUNCTION <function name> [(param1, param2)] RETURN <datatype> IS | AS [local declarations]

BEGIN

Executable Statements;

RETURN result;

EXCEPTION

Exception handlers;

END;

B. Restrictions on defining functions:

The function can only accept the IN parameter, but cannot accept the in out or OUT parameter.

The parameter cannot be of the PL/SQL type.

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

C. Two methods to access functions: Using PL/SQL blocks and using SQL statements

3. Advantages of subprograms:

Modular, reusable, maintainability, and security

4. Package:

Encapsulation of objects such as related processes, functions, variables, cursors, and exceptions

A. The package consists of A specification and A subject.

A. Specification: declare public objects in the package. Including types, variables, constants, exceptions, cursors, and subprograms

B. Subject: declare the private object of the package and the subroutine and cursor declared in the actual package specification.

B. Create a package

A. package specification

CREATE [or replace] PACKAGE package_name IS |

[Public item declarations] [Subprogram specification]

END [package_name];

B. Package owner

CREATE [or replace] package body package_name IS |

[Private item declarations]

[Subprogram bodies]

[BEGIN Initialization]

END [package_name];

5. Advantages of the package

A. Modularization

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 two parts: the cursor specification and the cursor subject.

B. When declaring a cursor specification in the packet specification, you must use the RETURN clause to specify the RETURN type of the cursor.

The data type specified by the RETURN clause can be:

A. Use the % ROWTYPE attribute to reference the record type defined in the table

B. Record types defined by programmers

7. Information about subroutines and packages

A. The USER_OBJECTS view contains information about user-created subprograms and packages.

SELECT object_name, object_type FROM USER_OBJECTS WHERE object_type IN ('Procedure ', 'function', 'package', 'package body ');

B. The USER_SOURCE view stores the source code of subroutines and packages.

SELECT line, text FROM USER_SOURCE where name = 'test ';

8. Trigger:

A stored procedure that is automatically executed when a specific event occurs (a specific event can be an updated DML statement and DDL Statement)

Note: triggers cannot be explicitly called.

9. Trigger functions:

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

10. Create a trigger syntax

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;

11. concepts related to DML triggers

A. BEFORE trigger: A trigger executed BEFORE an operation occurs.

B. AFTER trigger: A trigger executed AFTER an operation occurs.

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

D. Row-Level Trigger: Execute once for each row modified by the DML statement

E. NEW pseudo record: This record is only available in the UPDATE and insert dml triggers. It contains the value of the modified row that is affected.

F. OLD pseudo record: only available in the UPDATE and delete dml triggers. It contains the values of the rows affected before the modification.

G. WHEN clause: whether a code segment is executed

12. A trigger consists of three parts:

A. Trigger Statement (event): defines DML events and DDL events of the active trigger.

B. trigger restriction: the trigger execution condition. The trigger can be activated only when the trigger is true.

C. Trigger operation (subject): contains some SQL statements and code that run when the trigger statement is issued and the trigger limit value is true.

13. DML triggers:

PL/SQL blocks automatically executed when a table data modification event (add, delete, or modify) occurs

Syntax:

Create or replace trigger name
{AFTER | BEFORE} -- specifies the trigger time
{INSERT | DELETE | UPDATE} -- specifies the trigger event type.
ON Table name -- specify the monitored table
{For each row} -- specify the number of triggers

------ Above: Execution condition ------------ below: Execution Code ----------------
Declare

BEGIN
Code;
END;

14. Condition predicates

INSERTING, UPDATING, and DELETING

15. trigger application

Application scenarios: data security control, data audit, data integrity, and reference Integrity Control

16. instead of trigger (Alternative trigger)

17. system event triggers

18. Manage triggers

A. Enable and disable triggers: When to disable: when data is imported and exported

SQL> ALTER TRIGGER aiu_itemfile DISABLE;

SQL> ALTER TRIGGER aiu_itemfile ENABLE;

B. Re-compile trigger: After the database object changes

C. delete a trigger

SQL> DROP TRIGGER aiu_itemfile;

19. triggers and stored procedures

Trigger Stored Procedure

Write a program with no parameters. The returned values include parameters and return values (parameters)

Write cannot be commit or rollback

Method of call events are automatically called manually when an event occurs.

The operation object is irrelevant to the table based on a table.

20. JDBC connection to the Oracle database

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

Connection con = null;

Con = DriverManager. getConnection (jdbc: oracle: thin: @ localhost: 1521: database instance );

21. Execute PL/SQL anonymous blocks in JDBC

A. steps:

A. Specify the string for executing anonymous blocks in PreparedStatement.

B. Create a PreparedStatemen object

C. bind variables as needed

D. Execute the PreparedStatement object

22. stored procedures for executing PL/SQL in JDBC

A. Syntax:

A. {call process name [(?,?,?...)]} // Access the stored procedure with Parameters

B .{? = Cal process name [(?,?,?...)]} // Access the stored procedure of the returned result Parameters

C. {call process name} // access a stored procedure without Parameters

B. steps:

A. Specify the CallableStatement call String

B. Create a CallableStatement object (CallableStatement = Connection. prepareCall (SQL );)

C. Bind the input (IN) parameter

D. register the output (OUT) parameter (CallableStatement. registerOutParameter (index, OracleTypes. Data Type ))

E. Execute the CallableStatement object to return the result