“This is the 15th day of my participation in the First Challenge 2022. For details: First Challenge 2022.”

 Table relation design

  • More than a pair of

The many side quotes the one side

  • One to one

Both tables share a primary key

  • Many to many

Through the third table implementation, establish two one-to-many relationships

PLSQL

PL/SQL(Procedural Language/Structured Query Language)

1. PL/SQL The basic format

A PL/SQL block consists of variable declarations, program code, and exception handling code:

DECLARE (DECLARE)

  • Declare variables, constants, user-defined data types, and cursors:
  • name varchar(30); — Declare without setting a value
  • The name varchar (30) : = ‘Jack’; — Declaration with default values
  • name preson.name%type; References a table’s data type directly

BEGIN (body)

  • Main program body, where various legal statements can be added

EXCEPTION (EXCEPTION handling)

  • An exception handler that executes when an error occurs in the program

END (END)

2. PL/SQL Valid characters

1. Upper and lower case Letters

Arabic numerals ranging from 2.0 to 9

3. The underlined

4. Operators include +, -, *, /, <, >, and! , =, @, %, etc

5. A maximum of 30 characters are case insensitive, but appropriate use of case is recommended to improve the readability of programs

3. Variable/constant declaration

Declare statement variables (char, varchar2, date, number, Boolean, long, integer, type, rowtype)

  varl             char(15);

  married          boolean := true;

Psal number (7, 2);

my_name emp.ename%type; A reference variable, that is, my_name has the same type as ename in the EMP table

emp_rec emp%row type; Recordset variable

Emp_rec. ename:=’ADAMS’;

Declare constant: constant name data type := value;

Once a constant is defined, its value does not change in future use. Some fixed sizes are defined as constants in case someone changes them.

For example, pass_score constant INTEGER := 60;

4. The output

Set SeverOutput on –SQL*Plus, PL/SQL Developer

  • declare
name varchar(10):='HelloWorld'; begin dbms_output.put('test:'); Dbms_output.put_line (name); -- Newline output end;Copy the code

5. Receive user input assignment & variable name

Declare the begin dbms_output. Put_line (' your input value is: '| |' & word); end;Copy the code

6. Into fu value

The into keyword can be used to assign the value of the query result to a variable:

declare

name varchar2(100); id number; begin select id,name into id,name from student where id = 1; Dbms_output. Put_line (' id value: '| | id | |', the value of the name: '| | name); end;Copy the code