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

Source: Internet
Author: User
Tags rowcount throw exception

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
   Case 4:

       Output different sentences based on the teacher's salary!

if select structure and case select structure

(1). if select structure

t_name teacher.tname% type;
t_sal teacher.sal% type;
  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’);
      dbms_output.put_line (‘Advanced’);
      end if;
(2) Case selection structure


t_name teacher.tname% type;
t_sal teacher.sal% type;
t_result varchar2 (50);
  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’;
     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;

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
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!’);
          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:
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!

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);
        dbms_output.put_line (‘Change failed’);
        end if;
02. Show cursor

 -Show cursor
 declare-declare display cursor
   c_tname teacher.tname% type;
   c_sal teacher.sal% type;
   cursor teacher_cursor
   select tname, sal from teacher where tno <1005;-cursor data source
     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

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;
     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;
          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
ex_tidException exception; --- exception type
     if length (p_tid)! = 18 then
       raise ex_tidException; --throw exception
      end if;
      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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.