Oracle (PLSQL) introduction 7

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;

Tags: Oracle SQL Database Attribute Programming

Posted on Tue, 05 May 2020 13:48:10 -0400 by fiddler80