Oracle Database Fragmented Knowledge 03

21, stored procedures, simplify complex operations, increase data independence, improve security, improve performance

Compare with function creation:

The Create or Replace function fun_01 (v_01 in number) return    number--must require a return value of as    result.    Begin    Result: = Power (v_01,2);    return result;    End;    /function Created.select fun_01 (9) from dual; FUN_01 (9)----------        81

　　

The stored procedure creates an example and does not require a return value:

Create or Replace procedure pro_01 (v_01 in number)    as    result number;    Begin    Result: = Power (v_01,2);    Dbms_output.put_line (result);    End;    /procedure Created.execute pro_01 (9); 81pl/sql Procedure successfully completed.

　　

22, trigger, a special stored procedure

CREATE TABLE log_tab--creates a log table    (    l_id Number (3),    l_old varchar2,--Delete and the updated primary key    L_type VARCHAR2 (10), --The type of operation performed    L_new varchar2,--added and updated primary key    l_date date,--changed dates    constraint Pk_log_tab primary KEY (L_ID)    ); Table created.

　　

Create sequence log_tab_id--creates a sequence    MinValue    maxvalue start with the    increment by    1; Sequence created.

　　

Create or Replace trigger tr_student02--creates a trigger    after insert or UPDATE or delete– insert update delete    on student02--for student 02 Table    for each row-trigger for row    begin case when    inserting then-insert triggers insert INTO    log_tab values    (   Log_tab_id.nextval,   null,   ' Insert ',   : New.sno,--Import Data sysdate in the inserted table   );   When deleting then-delete triggers   insert into log_tab values   (   log_tab_id.nextval,   : Old.sno,-- Import data   ' delete ' in deleted table,   null,   sysdate);   When updating then-update triggers   INSERT INTO log_tab values   (   log_tab_id.nextval,   : Old.sno,   ' update ',   : New.sno,   sysdate);   End case;  End

　　

Delete from student02 where rownum = 1;insert into student02 (sno,sname,ssex) VALUES (123, ' Hook ', ' m '); Update student02 Set sname = ' Tokl ' where sno = 123; SELECT * from Log_tab;      l_id l_old                l_type     l_new                l_date---------------------------------------------------------------------       101 109                  Delete                          28-oct-15       102                      Insert     123                  28-oct-15       103 123                  Update     123                  28-oct-15

23,oracle alias as used, cannot be used on a table, only used on columns, the table uses a space alias:

24, Wildcard ' _ ' when used, if the field uses char type, fixed length, need to complete with wildcard character

25, the sort null value is before the nulls first. Nulls last

Oracle Database Fragmented Knowledge 03