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)