Functions, packages, and triggers in Oracle databases

Function:

• • /** Functions are very similar to stored procedures. The only difference is that functions return values. **/Create or replace function firstFun return varchar2is begin return 'Hello world'; end firstFun;

    
    

  • /** How much is the annual salary calculated? **/Create or replace function paramFun (salary number) return numberis begin return salary * 12; end paramFun;

    
    

• • /** Here is the header, mainly for data definition **/create or replace package firstPack is -- defines a variable v_bonus number: = 200; -- Define a function yearSalary (salary number) return number; -- Define a stored procedure mydrop; end firstPack;

    /** Package subject: calculate the declaration of the implementation of the annual salary = monthly salary * 12 + bonus */create or replace package body firstPack is -- the Baotou declaration function implementation function yearSalary (salary number) return number is begin return salary * 12 + v_bonus; -- the variable 'end' stated earlier is used here; -- procedure myrop is begin -- insert a data insert into tab_stu (stu_id, stu_name, stu_age, class_id) values (9, 'ken', 21,2) into the student table ); commit; end firstPack;

    
    
    

• • /** Create a trigger 't_back_tab_sut '. When the record in the tab_stu table is deleted, execute **/create or replace trigger t_back_tab_sut after delete on tab_stu -- when the row-Level trigger is triggered, if you want to access the values in the inserted, updated, or deleted records. You can use -- NEW: the value after the access operation is complete. -- Old: The value before the access operation is complete. -- You can obtain new or old data that you insert, update, or delete. Referencing new as new old as old for each row -- indicates that the trigger is a row-Level Trigger declare r_tab_stu tab_stu % rowtype; begin -- get the deleted data r_tab_stu.stu_id :=: old. stu_id; r_tab_stu.stu_name: =: old. stu_name; r_tab_stu.stu_age: =: old. stu_age; r_tab_stu.class_id: =: old. class_id; -- insert into tab_stu_back2 (stu_id, stu_name, stu_age, class_id) values (r_tab_stu.stu_id, r_tab_stu.stu.stu_name, primary, r_tab_stu.class_id) into the backup table );-- There cannot be a commit statement here-because if there is a commit, the delete operation executed by the user cannot be rolled back. -- It can be understood that the insert statement and the user's delete statement are a transaction. End t_back_tab_sut;