Create and use ORACLE database objects

The database has a worker table with the following content:

SQL> select * from worker;

Employee number name gender Date of Birth Party member does not participate in work department number
------------------------------------------------------------
1 Sun Hua male 1952/3/1 no 1970/10/10 1
2 Chen Ming male 2045/8/1 no 1965/1/1 2
3 Cheng xinv 1980/4/6 no 2002/10/7 3

I. cursor

Applications, especially interactive online applications, do not always take the entire result set as a unit for effective processing. These applications require a mechanism to process one or more rows at a time. A cursor is a result set extension that provides this mechanism.

Use a cursor to add '_ t' to the name column of the worker table ':

-- Declare a cursor

Declare
Cursor mycur is
Select name from worker for update;

-- Declare a v_text variable to store the cursor result set.
V_text worker. name % type;
Begin

-- Open the cursor
Open mycur;

-- Extract the first row of data and save it in v_text

Fetch mycur into v_text;
While mycur % found

Loop

-- Add '_ t' to the name'

Update worker set name = Name | '_ t' where current of mycur;
Fetch mycur into v_text;
End loop;

Close cursor
Close mycur;
End;

Ii. triggers

A trigger is a special stored procedure that automatically takes effect when the data in the specified table changes, the insert, update, and delete statements are automatically executed when the table or view is modified.

When adding, deleting, and modifying a worker table, record the user, date, and operation type in a tr_log table:

Create or replace trigger tr_1

After insert or delete or update on worker

Begin

If inserting then

Insert into mylog values (user, sysdate, 'I ');

Elsif deleting then

Insert into mylog values (user, sysdate, 'd ');

Else

Insert into mylog values (user, sysdate, 'U ');

End if;

End;

3. View

A view is exported from one or more tables using the SELECT statement. In a database, the view definition is stored, not the data queried by the view. Through this definition, the View query is finally converted to the query of the base table.

Use the view to query the employee information of department 1:

Create or replace view myview
As
Select * from worker where Department No. = 1;

Iv. Stored Procedure

A stored procedure is a set of pre-compiled SQL statements and optional control statements. It is stored in a name and processed as a unit.

Enter employee ID to query personal information:

-- In indicates the input type, out indicates the output type, and % rowtype indicates the omnipotent row type. Parameter size cannot be specified.

Create or replace procedure pr_1 (v_id in number, v_info1 out worker % rowtype)
Is
Begin
Select * into v_info from worker where employee ID = v_id;
End;
-- Process call
Declare
V_info2 worker % rowtype;
Begin

-- Execute, v_info2 receives the output result

Pr_1 (1, v_info2 );

Dbms_output.put_line (v_info2. employee ID | ''| v_info2. name | ''| v_info2. gender | ''| v_info2. date of Birth | ''| v_info2. party member no | ''| v_info2. work | ''| v_info2. department No );
End;

5. User-Defined Functions

Function is used to return specific data. During execution, you must find a variable to receive the return value of the function.

Enter the employee ID to query its name:

-- You do not need to specify the size when you participate in the type declaration.

Create or replace function fun_1 (v_name1 number)

-- Specify the return type
Return varchar
Is
Declare v_name2 varchar2 (8 );
Begin
Select name into v_name2 from worker where employee ID = v_name1;
Return v_name2;
End;
-- Function call

-- Declare the receiving variable
VaR v_name varchar2 (8)

-- Execute
Exec: v_name: = fun_1 (1)

Vi. Packages

A package is used to combine logic-related processes and functions. It consists of a package specification and a package body. The package specification is used to define common constants, variables, procedures, and functions. To create a package specification, you can use the create package command to create a package body.

-- Create a package

Create or replace package sp_package

Is

Begin

-- Function declaration

Function fun_1 (v_name1 number) return varchar2;
-- Stored procedure Declaration

Procedure pr_1 (v_id in number, v_info1 out worker % rowtype );
End;

-- Create a package

Create package body sp_package is

Begin

-- Function execution

Function fun_1 (v_name1 number)
Return varchar
Is
V_name2 varchar2 (8 );
Begin
Select name into v_name2 from worker where employee ID = v_name1;
Return v_name2;
End;

-- Execution of Stored Procedures

Procedure pr_1 (v_id in number, v_info1 out worker % rowtype)
Is
Begin
Select * into v_info from worker where employee ID = v_id;
End;

End;

-- Call functions in the package

VaR v_name varchar2 (8)
Exec: v_name: = sp_package.fun_1 (1)

-- Call the stored procedure in the package

Declare
V_info2 worker % rowtype;
Begin

Sp_package.pr_1 (1, v_info2 );

Dbms_output.put_line (v_info2. employee ID | ''| v_info2. name | ''| v_info2. gender | ''| v_info2. date of Birth | ''| v_info2. party member no | ''| v_info2. work | ''| v_info2. department No );
End;

VII. Sequence

In Oracle, sequence is the so-called serial number, which is automatically increased every time it is obtained. It is generally used in places where the sequence numbers need to be sorted.

The student table contains the student ID, name, and gender fields. The student ID field orchestration rule starts from 1 and increments to 1. The Code is as follows:

Create sequence student_sequence

-- The increment value is 1.
Increment by 1

-- Starting from 1
Start with 1

-- No maximum value
Nomaxvalue

-- No loop
Nocycle

-- Auto insert of student ID Using Sequence

Insert into student values (student_sequence.nextval, 'Li jing', 'femal ');

8. Synonyms

Oracle Synonyms (synonyms) literally refer to aliases. Similar to views, synonyms is a ing relationship.

For example, create a synonym for a worker table:

Create public synonym Wo for worker;

You can use the select * from Wo statement to query.

Public means public. All users with such synonyms can access the table object. If public is not added, the table object owner can directly access the table object. Other users must add the owner before the table to access the table, whether or not the table object owner is added with public, the table must have the permission to select the table.