---Oracle database design, PL/SQL (loop,for,if,case,while)

1. Database design:

For user-specific needs, then we create a most practical and high performance database!

Steps for database design:

01. Demand Analysis

02. Conceptual Structure Design

03. Logical Structure Design

04. Physical Organization Design

05. Implementation of the database

06. Operation and maintenance of the database

3 major paradigms of the database:

1. Ensure the atomicity of each column! Each column is a non-divided data!

2. Make sure each column is related to the primary key!

3. Make sure that each column has a direct relationship to the primary key, not an indirect dependency (transitive dependency)!

--------------------------------------------------------------------------------------------------------------- -----------------------

PL/sql: (procedural Language) procedural SQL language!

Added a choice or a logical judgment in our previous SQL statement!

When the database executes PL/SQL statements, PL is executed separately from it!

Oracle | | Stitching strings.

: = assignment operator.

= = = Equal to = = in Java

.. The range operator. such as: 1.. 10 is between 1 and 10.

! = <> ~= ^= not equal to

and Logic and

or Logical OR

not take counter

PL/SQL Syntax:

1.declare optional Part = = = "statement

2.begin must have = = = "Write sql and PL/

3.exception optional Part = = = "Exception"

4.end must have = = "PL/SQL code block end

Case 1;loop Cycle:

declare-declare part
    i number;
 begin-start of code
   i: = 1;
   loop-loop start
         dbms_output.put_line (i);-output statement
         i: = i + 1;
         exit when i = 10;
    end loop;-end of the loop
end;-end section
By column 2: while loop syntax:

        while condition loop

              Executed statement

         end loop;

declare-code declaration
  i number;
 begin-start of code
   i: = 1;
   while i <20 loop-loop statement
        dbms_output.put_line (i);-output statement
        i: = i + 1;
    end loop;-end of the loop
 end;-end part
By column 3: for loop syntax

   for variable in range loop

        Executed statement

    end loop;

declare-declare part
    i number;
begin-start of code
    for i in 1..30 loop-loop starts
        dbms_output.put_line (i);-output statement
    end loop;-end of the loop
end;-end
   Case 4:

       Output different sentences based on the teacher's salary!

if select structure and case select structure

(1). if select structure

declare
t_name teacher.tname% type;
t_sal teacher.sal% type;
begin
  select tname, sal into t_name, t_sal from teacher where tno = 1002;
  if t_sal> 5000 and t_sal <10000 then
        dbms_output.put_line (‘level’);
   elsif t_sal> = 10000 and t_sal <20000 then
      dbms_output.put_line (‘Secondary’);
    else
      dbms_output.put_line (‘Advanced’);
      end if;
end;
(2) Case selection structure

    

declare
t_name teacher.tname% type;
t_sal teacher.sal% type;
t_result varchar2 (50);
begin
  select tname, sal into t_name, t_sal from teacher where tno = 1002;
  if t_sal> 5000 and t_sal <10000 then
       t_result: = ‘First level’;
   elsif t_sal> = 10000 and t_sal <20000 then
       t_result: = ‘Secondary’;
    else
     t_result: = ‘Advanced’;
      end if;
      
      case t_result
        when ‘one level’ then
          dbms_output.put_line (‘Haha’);
          when ‘secondary’ then
            dbms_output.put_line (‘general’);
            when ‘advanced’ then
              dbms_output.put_line (‘Yes!’);
      end case;
end;


Case 5: Functions

    Need to hide your birth date from your ID number!

create: create

replace: modify

create or replace: create if not, modify if there are

fn_teacher_tid: Function name specification

f_tid: parameter name

varchar2: parameter type

create or replace function fn_teacher_tid (f_tid varchar2)
return varchar2-create a function pass a value of type varchar2 return a value of type varchar2
is
f_result varchar2 (50);-declare a variable
begin-begin writing function content
     if length (f_tid)! = 18 then
         dbms_output.put_line (‘Identity format is incorrect!’);
         else
          dbms_output.put_line (‘Identity format is correct!’);
          -If the input format is correct, replace the entered year, month, day with *
            f_result: = substr (f_tid, 1,6) || ‘********‘ || substr (f_tid, 15);
       end if;
     
      return f_result;
end fn_teacher_tid;-end of function


--call function
select fn_teacher_tid (‘1111111111111111111’) from dual;


Case 6:
Cursor:
01. It is a data buffer opened by the oracle system for our users!
02. Stored is the result set of the SQL statement execution!
03. Each cursor area has a name, and the user gets the required data row by row through the cursor!

classification:
01. Implicit cursor: non-query statement
As long as we use pl / sql, the program is automatically created when executing the sql statement! Cursor area ===》 sql
02. Display cursor: return multiple rows of records
03.REF cursors (dynamic cursors): deal with dynamic SQL query results that can be determined at runtime

Common properties of cursors:
01.sql% found affects one or more rows of data returns true
02.sql% notfound does not affect the row returns true
03.sql% rowcount returns true affecting the number of rows
04.sql% isopen Whether the cursor is open! Always false

To use cursors:
01. Declare cursor
02. Open the cursor
03. Use the cursor to get the record
04. Close the cursor

 

 

01.Hermit cursor

 

-------------------------------------------------- -------------------------------------------------- ---------------------------

--Implicit cursor

begin-implicit cursors are created automatically
     update teacher set tname = ‘You ’ve worked hard’ where tno = 1002; --Modify
     if sql% found then
       dbms_output.put_line (‘Teacher ’s information has changed’ || sql% rowcount);
     else
        dbms_output.put_line (‘Change failed’);
        end if;
  end;
02. Show cursor

 -Show cursor
 declare-declare display cursor
   c_tname teacher.tname% type;
   c_sal teacher.sal% type;
   cursor teacher_cursor
   is
   select tname, sal from teacher where tno <1005;-cursor data source
 begin
     open teacher_cursor; --Open cursor
       fetch teacher_cursor into c_tname, c_sal; --use cursor
       while teacher_cursor% found loop
          dbms_output.put_line (‘Teacher ’s name is ==》’ || c_tname);
           dbms_output.put_line (‘Teacher ’s salary is ==》’ || c_sal);
           fetch teacher_cursor into c_tname, c_sal; --read line by line
      end loop;
      close teacher_cursor; --Close the cursor
 end;


Case 7: Trigger

     The trigger is for adding, deleting, and modifying!

   update: old: new

   insert: new

   delete: old

 

: Old represents the value before modification

: New represents the modified value

======================================================= ===========

select * from teacher t for update
  --Create a table for the record of teacher operations
  create table teacher_log
  (
  logid number not null,
  old_value varchar2 (150),
  create_date date,
  log_type number,
  t_no number
  );
  
--Create a primary key
alter table teacher_log add constraint pk_teacher_logid
primary key (logid);

--Create sequence
create sequence sq_teacherLog_logid
minvalue 1
maxvalue 999999999
start with 1
increment by 1;


--Create trigger
create or replace trigger tr_teacher
after insert or update or delete-will trigger after adding, deleting, or modifying
on teacher for each row-function for each row in the teacher table
declare-declare variables
old_value teacher_log.old_value% type;
log_type teacher_log.log_type% type;
t_no teacher_log.t_no% type;
begin
     if inserting then
       log_type: = 1; --New
       t_no: =: new.tno;
       old_value: =: new.tname || ‘******‘ ||: new.sal;
      elsif deleting then
        log_type: = 2;-Delete
        t_no: =: old.tno;
        old_value: =: old.tname || ‘*****‘ || :: old.sal;
        else
          log_type: = 3;-Modify
          t_no: =: old.tno;
          old_value: =: old.tname || ‘******‘ || :: old.sal || ’Current salary:‘ ||: new.sal;
       end if;
-Put user modified data into teacher_log
insert into teacher_log
values (sq_teacherLog_logid.Nextval, old_value, sysdate, log_type, t_no);
end tr_teacher; --end


Case 8: Stored procedures
In order to complete a specific function, it is necessary to write a set of SQL statements!

When adding a new classroom, if the ID number is less than 18, an error will be reported!

 

create or replace procedure pro_addTeacher--stored procedure
(
p_no teacher.tno% type,
p_name teacher.tname% type,
p_tid teacher.tid% type
)
is
ex_tidException exception; --- exception type
begin
     if length (p_tid)! = 18 then
       raise ex_tidException; --throw exception
      end if;
      
      
      --New
      insert into teacher (tno, tname, tid)
      values (p_no, p_name, p_tid);
      commit; --- Automatic commit
 exception-the exception handling part
     when ex_tidException then
       dbms_output.put_line (‘Incorrect ID number’);
      when others then
        dbms_output.put_line (‘Other exception’);
 end pro_addTeacher; --end

          
-Calling stored procedures
 call pro_addTeacher (1112, 'Little White', '1122222222222222222');
 

 9 set the type of the field:% type% rowtype

 

name teacher.tname% type: It will change automatically according to the type of field in the table!

teacherRow teacher% rowtype: an entire row of records, including many fields, automatically changed! If you want a single field, you can click it directly (teacherRow.name)

 

--- oracle database design, PL / SQL (loop, for, if, case, while)