[Oracle] Chapter 5 cursor and trigger, Chapter 5 oracle cursor

[Oracle] Chapter 5 cursor and trigger, Chapter 5 oracle cursor

Chapter 5 cursor and trigger

Cursor:

Implicit cursor: % FOUND, % NOTFOUND, % ROWCOUNT

1.% FOUND usage. The % FOUND attribute returns TRUE only when the DML statement affects one or more rows. The following example demonstrates the usage of % FOUND:

Begin

Update employees2 set first_name = 'Scott 'where employee_id = 2;

If SQL % found then

Dbms_output.put_line ('data updated ');

-- Dbms_output.put_line ('rowcount = '| mrowcount );

Else

Dbms_output.put_line ('data not found ');

End if;

End;

/

The following code creates a cursor and returns records with salary greater than 300000 in Table employees2. Note the usage of type:

Declare

Csalary employees2.salary % type;

Cursor emp2_cursor is select salary from employes2 where salary> 300000;

Begin

Open emp2_cursor;

Loop

Fetch emp2_cursor into csalary;

Exit when emp2_cursor % notfound;

Dbms_output.put_line ('csalary = '| csalary );

End loop;

End;

/

The following code creates a cursor and returns the division_id = 'sal 'record in the table "employees2.

Note the usage of rowtype:

Declare

Cursor employee2_cursor is select * from employees2 where division_id = 'sal ';

Myrecord employees2 % rowtype;

Begin

Open employee2_cursor;

Fetch employee2_cursor into myrecord;

While employee2_cursor % found loop

Dbms_output.put_line ('employee id = '| myrecord. employee_id );

Dbms_output.put_line ('first Name = '| myrecord. first_name );

Dbms_output.put_line ('Last name = '| myrecord. last_name );

Fetch employee2_cursor into myrecord;

End loop;

End;

/

The following code demonstrates a cursor with parameters and queries a specified Record Based on the division id:

Declare

Myrecord employees2 % rowtype;

Cursor emp_cursor (divisionid varchar2) is select * from employees2 where division_id = divisionid;

Begin

Open emp_cursor ('& divisionid ');

-- Loop

Fetch emp_cursor into myrecord;

While emp_cursor % found loop

-- Exit when emp_cursor % notfound;

Dbms_output.put_line ('employee id = '| myrecord. employee_id );

Dbms_output.put_line ('division id = '| myrecord. division_id );

Dbms_output.put_line ('first name = '| myrecord. first_name );

Fetch emp_cursor into myrecord;

End loop;

Close emp_cursor;

End;

/

The following code specifies fields in the update table when creating a cursor. Update the first_name field in Table employees2:

Set serveroutput on

Declare

FirstName varchar2 (20 );

Cursor employees2_cursor is select first_name from employees2 where employee_id = 1 for update

First_name;

Begin

Open employees2_cursor;

Loop

Fetch employees2_cursor into firstName;

Exit when employees2_cursor % notfound;

Update employees2

Set first_Name = 'jeff 'where current of employees2_cursor;

End loop;

Close employees2_cursor;

Commit;

End ;/

Trigger:

A trigger is a stored procedure that is automatically executed when a specific event occurs.

A specific event can be an updated DML statement and DDL statement.

The trigger cannot be explicitly called.

Trigger functions:

Automatically generate data

Customize complex security permissions

Provides audit and logging

Enable complex business logic

Trigger types include: Row-Level Trigger, statement-Level Trigger, instead of trigger, mode trigger, and database-Level Trigger.

Trigger creation Syntax:

CREATE [or replace] TRIGGER trigger_name

AFTER | BEFORE | INSTEAD

[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;

Create a row-level trigger. The following Code demonstrates how to insert or modify the first_name in Table employees2. If it is equal to 'Scott ', the trigger will execute:

Create or replace trigger tri_employees2

Before insert or update of first_name

On employees2

Referencing NEW as newdata OLD as olddata

For each row

When (newdata. first_name = 'Scott ')

Begin

: Newdata. salary := 20000;

Dbms_output.put_line ('new. salary: '|: newdata. salary );

Dbms_output.put_line ('old. salary: '|: olddata. salary );

End;

Execute the above trigger:

Insert into employees2 values (38, 'sup', 'wor', 'Scott ', 'mp', 50000 );

Or:

Update employees2 set salary = 90000, first_name = 'Scott 'where employee_id = 38;

Run the following code to perform operations on data integrity (delete the ID in the dept table and delete the corresponding deptid information in the employee table of the foreign key table ):

Create a table:

Create table dept (deptId number (10), deptName varchar2 (20 ));

Insert into dept (deptid, deptname) values (1, 'academic authorization ')
Insert into dept (deptid, deptname) values (2, 'department of Education ')
Insert into dept (deptid, deptname) values (3, 'marketing Department ')
Insert into dept (deptid, deptname) values (4, 'Finance authorization ')

Create table emp (empid number (10), empName varchar2 (20), deptid number (10 ))
Insert into emp (empid, empName, deptid) values (1, 'Scott ', 1)
Insert into emp (empid, empName, deptid) values (2, 'jack', 2)
Insert into emp (empid, empName, deptid) values (3, 'Mike ', 3)
Insert into emp (empid, empName, deptid) values (4, 'jeff ', 4)
Insert into emp (empid, empName, deptid) values (5, 'VERA', 1)
Insert into emp (empid, empName, deptid) values (6, 'linda ', 2)
Insert into emp (empid, empName, deptid) values (7, 'marho', 3)
Insert into emp (empid, empName, deptid) values (7, 'dick', 4)

Delete operation:

Create or replace trigger del_deptid

After delete on dept

For each row

Begin

Delete from employee where deptid =: old. id;

End del_deptid;

/

Execute the above trigger (because a trigger is created in the dept table, delete the ID record of the dept table and delete the corresponding deptid information of the employee table ):

Delete from dept where id = 1; view the deptid record in the employee table;

Add operation:

Create or replace trigger insert_dept

After insert on dept

For each row

Begin

Insert into employee (id, name, deptid) values ('6', 'chenmp',: new. id );

End;

/

Execute the above trigger:

Insert into dept values (6, 'sales departments ');

View the deptid record in the employee table

Modify (create an UPDATE trigger in the dept table, modify the DEPT table ID, and modify the DEPTID in the employee table ):

Create or replace trigger update_dept

After update on dept

For each row

Begin

Update employee set deptid =: new. id where deptid =: old. id;

End;

/

Execute the above trigger:

Update dept set id = 8 where id = 1;

View the deptid record in the employee table

The following code demonstrates a row-Level Trigger:

Create a table:

Drop table rowtable;

Create table rowtable (id number (8), name varchar2 (100 ));

Create Sequence

Create sequence rowtablesequence;

Create a trigger:

Create or replace trigger set_sequence

Before insert on rowtable

For each row

Declare

Rsequence number (8 );

Begin

Select rowtablesequence. nextval into rsequence from dual;

: NEW. id: = rsequence;

End;

/

Execute the SQL statement:

Insert into rowtable values (232, 'Scott ');

The following code demonstrates a statement-Level Trigger:

Create a table:

Create table mylog (curr_user varchar2 (100), curr_date date, opera varchar2 (10 ));

Create trigger

Create or replace trigger tri_mylog

After insert or delete or update on employees2

Begin

If inserting then

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

Elsif deleting then

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

Else

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

End if;

End;

/

Mode trigger: You can create a trigger in Mode-level operations, such as create, alter, drop, grant, revoke, and truncate DDL statements:

The following example records all objects deleted by the user.

1. Create a database table:

Drop table dropped_obj;

Create table dropped_obj

(

Obj_name VARCHAR2 (30 ),

Obj_type VARCHAR2 (20 ),

Drop_date DATE

);

2. Create a trigger:

Create or replace trigger log_drop_obj

AFTER DROP ON SCHEMA

BEGIN

Insert into dropped_obj

VALUES (ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE );

END;

/

3. Create and delete objects:

Create table for_drop (x CHAR );

Delete object: drop table for_drop;

4. view the information in the log table:

SELECT * FROM dropped_obj;

Start trigger and disable trigger:

The following code disables the biu_emp_deptno trigger:

Alter trigger biu_emp_deptno DISABLE;

The following code enables the biu_emp_deptno trigger:

Alter trigger biu_emp_deptno enable;

You can use:

Alter table table_name {enable | disable} all triggers;

Disable or use all triggers created on a specific table.

Delete trigger:

Drop trigger trigger_name;

You can use the user_trigers data dictionary view to view trigger information.

Desc user_triggers