Learning video: https://www.bilibili.com/video/BV1tJ411r7EC?p=74
Design database steps:
Good database design: save space, complete data, easy to develop.
Steps: 1. Collect information 2. Identify entity 3. Identify entity attribute 4. Identify relationship between entities 5. Draw E-R diagram and write Sql
Concept understanding:
- Mapping cardinality has one to one, one to many, many to one, many to many.
- Three paradigms: each column is an indivisible data unit. For example, the content of "address column" Beijing, China "can be divided into" country column "and" city column "; each table only describes one thing. Columns and primary keys should have a direct relationship, not an indirect relationship.
PL/SQL:
Is a process language, with structured SQL programming language, the expansion of SQL statements. There are three parts: declaration, execution and exception. If there is nothing to declare, you can not write declare, and you can not write exception without catching exception. Comments are divided into single line "-" and multi line "/ * * /", and the maximum length of variable identifier is 31.
Variables are declared as "variable name type: = value". Note that ": =" is used for assignment and "=" is used for judgment. If you want to declare a constant, add the keyword "constant" after the variable name, that is, "variable name constant type: = value". The declared constant must be assigned a value.
Property type:
Property types are% type,% RowType. The common types you should know are number (integer and floating point), char, VARCHAR2 (variable length), date, boolean, binary_integer (integer). Type refers to the type of the table field, using table name. Column name% type.
declare name varchar2(20) := 'data base'; i constant number :=3; salary emp.sal%type:=1000; begin dbms_output.put_line(name || 'hahhah'); end;
record type, just like defining the class in C ා
declare type student is record( stuid emp.empno%type, stuname emp.ename%type ); stu student; begin stu.stuid:=11; stu.stuname:='bibi'; dbms_output.put_line('Student ID' || stu.stuid); end;
%rowtype is a record type that refers to a table.
declare stu emp%rowtype; --emp Is a table, using rowtype Gets the row type of the table. stu In fact record type begin stu.empno := 5555; stu.ename := 'bibi'; dbms_output.put_line(stu.empno || stu.ename); select * into stu from emp where empno=7369; dbms_output.put_line(stu.empno || stu.ename); end;
table type, similar to the dictionary type of C ා.
declare type myTable is table of number index by binary_integer; tableone myTable; x number; begin tableone(0) := 100; tableone(1) := 200; tableone(2) := 200; tableone(4) := 200; tableone(3) := 200; dbms_output.put_line(tableone(0) || tableone(1)); x := tableone.first(); --Get the first key dbms_output.put_line('first key' || x); x := tableone.next(x); --Get current key Next key dbms_output.put_line('first key Next key' || x); x := tableone.last(); --Get the last key dbms_output.put_line('(Auto) sort the last key' || x); end;
Variable scope:
declare i number := 90; begin declare j number :=100; begin dbms_output.put_line(j); --Use inner variables dbms_output.put_line(i); --Use outer variables end; end;
If branch: if...elsif..else; if...; if...else..; if....elsif...elsif... Various combinations.
declare i number := 10; begin if i > 30 then dbms_output.put_line('ok ah'); elsif i > 5 then -- Note that elsif dbms_output.put_line('also ok ah'); else --Notice that it's not then dbms_output.put_line('No ok ah'); end if; end; / declare i number := 10; begin if i > 30 then dbms_output.put_line('ok ah'); else --Notice that it's not then dbms_output.put_line('No ok ah'); end if; end; / declare i number := 10; begin if i > 30 then dbms_output.put_line('ok ah'); end if; end; /
The null value does not participate in the comparison operation, and the operation result will not appear. If it is used in the if comparison branch, only else can be used.
Simple loop: loop... Exit when;. End loop; write exit conditions, or you will fall into a dead loop.
declare i number := 0; begin loop dbms_output.put_line(i); i := i + 1; exit when i = 10; --If you don't write exit conditions, you will fall into a dead cycle. --i := i + 1; You can also write conditions here end loop; end;
Normal loop: when is used, the condition is prefixed.
declare i number := 0; begin while i < 100 loop if mod(i, 2) = 0 then dbms_output.put_line(i); end if; i := i + 1; end loop; end;
For loop: variables of for can only be self increasing. But with reverse, you can reverse the growing sequence.
begin for i in 1 .. 10 loop dbms_output.put_line(i); end loop; end; / begin for i in reverse 1 .. 10 loop dbms_output.put_line(i); end loop; end; /
Multiple loops: if you want to exit the outer loop, you should give the outer loop an alias, "exit outer loop". A pure exit can only exit the current loop.
declare x number := 1; y number := 1; begin <<wai>> while x <= 10 loop dbms_output.put_line('----' || x); y := 1; while y <= 5 loop dbms_output.put_line(y); if x = 7 and y = 3 then exit wai; --Exit outer loop end if; y := y + 1; end loop; x := x + 1; end loop; end;
goto: jump, take a detour.
begin dbms_output.put_line('1'); dbms_output.put_line('2'); dbms_output.put_line('3'); goto hi; dbms_output.put_line('4'); dbms_output.put_line('5'); <<hi>> dbms_output.put_line('6'); dbms_output.put_line('7'); end;
begin dbms_output.put_line('1'); dbms_output.put_line('2'); dbms_output.put_line('3'); goto hi; dbms_output.put_line('4'); dbms_output.put_line('5'); <<hi>> null; end;