1. Introduction to PLSQL helloworld

declare 
  #Declaration of variables
  i integer;
  #Start of method
begin
  #Similar to Java system out. Println ('Hello world '), put() output; put_lint() line feed output;
  Dbms_Output.put_line('hello world!');
  #Method end
end;

2. Variables

Common variables in PLSQL programming are divided into two categories:
1. Common data types (char, varcher2, date, number, Boolean, long, etc.)
2. Special variable type (reference variable, record variable)
Reference type means that the type of variable depends on the type of field in the table.
Record type means that this variable does not accept the value of a field, but the value of a whole record.

How to declare variables:

#Variable name variable type (variable length)
v_name varcher(20);

2.1 common variables

There are two ways to assign variables:
1. Direct assignment statement:

 v_name := 'zhangsan'

2. Statement assignment, using select... Into... Assignment: (syntax select value into variable), usually this value comes from the query results in the table.

#Print employee personal information, including: name / salary / address
declare 
  -- #full name
  v_name VARCHAR(20) := 'Zhang San';
  v_sal NUMBER;
  v_addr VARCHAR(200);
begin
  -- #Direct assignment
  v_sal := 2000;
  -- #Statement assignment
  select 'China' into v_addr from dual;
  -- #Printout
  dbms_output.put_line('full name:' || v_name || ',salary:' || v_sal || ',address:' || v_addr);
end;

2.2 reference variables

The type and length of variables depend on the type and length of fields in the table.
By showing The column name% type specifies the type and length of the variable, for example: v_name emp.ename%TYPE.

#Query the personal information of employee No. 7839 in the emp form and print the name and salary
declare
#The type and field length in the same table are the same
  v_name emp.ename%TYPE;
  v_sal emp.sal%TYPE；
begin
  select ename,sal into v_name,v_sal from emp where empno = 7839;
  dbms_output.put_line('full name:' || v_name || ',salary:' || v_sal );
end;

The benefits of reference variables are that you need to know the types of columns in the table. At this time, you can use reference types without considering the types of columns. You can use% TYPE because it makes PL/SQL more flexible and more suitable for updating database definitions.

2.3 recorded variables

Receive a whole row of records in the table, which is equivalent to an object in java.
Syntax: variable name table name% ROWTYPE, for example: v_emp emp%rowtype;

#Query and print the personal information of employee 7839
declare
#Declare record type variable
  v_emp emp%rowtype
begin
  select * into v_emp from emp where empno = 7839;
  dbms_output.put_line('full name:' || v_emp.ename || ',salary:' || v_emp.sal );
end;

Record variables should be used with caution. If a table has 100 fields and the 100 fields are used in the program, record variables can be used at this time to avoid the declaration of reference variables one by one.
be careful:
1. Record type variables can only store one complete row of data.
Otherwise, too many rows are returned and the record type variable cannot be accepted.
2. You must select *, not ename and sal, otherwise an error will be reported because you cannot install all v_emp variable.

3. Process control

3.1 conditional branch

Syntax:

begin 
  if condition then Execution 1
    elseif condition then Execution 2
    else  Execution 3
  end if;
end;

For example, judge whether there are more than 20 records in the emp table, between 10-20, or less than 10.

declare
  #The number of records in the emp table received by the declaration variable
  v_count NUMBER;
begin
  select count(1) into v_count from emp;
  if v_count>20 then
    dbms_output.put_line('emp There are more than 20 records in the table:'|| v_count);
  elseif v_count>=10 then
    dbms_output.put_line('emp The number of records in the table is 10-20 Article, which is:'|| v_count);
  else 
    dbms_output.put_line('emp The number of records in the table is less than 10, which are:'|| v_count);
  end if;
end;

3.2 cycle

There are three circulation modes in oracle
Loop loop
Syntax:

begin
  loop
    exit when Exit loop condition
  end loop;
end;

Example: print numbers 1-10

declare 
  -- #Declare loop variable
  v_num NUMBER := 1;
begin
  loop
    exit when v_num>10;
    Dbms_Output.put_line(v_num);
    -- #Self increment of cyclic variable
    v_num := v_num+1;
  end loop;
end;

4. Cursor

Cursors are used to temporarily store multiple rows of data returned by a query (result set, similar to the resultSet set returned by java jdbc connection). By traversing cursors, you can access and process the data of the result set row by row.
Usage of cursor: Declaration - > Open - > Open - > read - > close
Syntax:

#Cursor declaration
cursor Tour label[(parameter list)] is Query statement;#That is, the result of the query statement is placed in the cursor
#Cursor opening
open Tour label;
#Value of cursor
fetch Tour label into List of variables;
#Cursor closing
close Tour label;

4.1 cursor properties

%rowcount #Gets the number of data rows returned by the fetch statement. Return value type: integer
%found  #If the latest fetch statement returns a row of data, it is true, otherwise it is false. Return value type: Boolean
%notfound  #Contrary to the return value of the% found property. Return value type: Boolean type, which is generally used to deduce loop judgment.
%isopen  #True when the cursor is already open, false otherwise. Return value type: Boolean

4.2 creation and use

Example: use the cursor to query the names and wages of all employees in the emp table and print them at one time.

declare
  #The cursor c_emp contains the names and salaries of all employees in the queried emp table
  cursor c_emp is select ename,sal from emp;
  #You need to traverse and print all the values, so you need a value to connect. Declare variables to receive elements in cursors
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin
  #Open cursor
  open c_emp;
  #Traverses the cursor through a loop
  loop
  #Get the data in the cursor, and pay attention to the order and number of corresponding variables
  fetch c_emp into v_name,v_sal;
  #Launch cycle conditions
  exit when c_emp%notfound;
  dbms_output.put_line(v_name||'-'||v_sal);
  end loop;
  #Close cursor
  close c_emp;
end;

4.3 cursor with parameters

Example: use the cursor to query and print the name and salary of employees in a department. The department number is entered manually during operation

declare
  #Transfer parameter, a department as a parameter
  cursor c_emp(v_deptno emp.deptno%type) is select ename,sal from emp where deptno = v_deptno;
  #Declare variables to receive data from cursors
  v_name emp.ename%type;
  v_sal emp.sal%type;
begin
  #Open the cursor and pass the specific value
  open c_emp(10);
  #Traversal cursor
  loop 
  #Get data in cursor
  fetch c_emp into v_name,v_sal;
  #Launch cycle condition
  exit when c_emp%notfound;
  dbms_output_put_line(v_name||'-'||v_sal);
  end loop;
  #Close cursor
  close c_emp;
end;  

Note: the first thing after the loop is fetch, and then judge.

5. Stored procedure

PL/SQL program can perform table operation, judgment and circular logic processing, but it can not be reused.
It can be understood that the previous code is all written in the main method, which is an anonymous program. java can solve the reuse problem by encapsulating objects and methods.
The stored PLSQL programs are called stored procedures.

effect:
In the development program, for a specific business function, the connection to the database will be closed many times (it takes a lot of resources to connect and close), and the database needs multiple I/O reads and writes, with low performance. If these businesses are put into PLSQL, we can connect and close the database once only by calling PLSQL in the application, which can greatly improve the efficiency.

Syntax:

creat or replace procedure Process name[(parameter list)] is
begin
end [Process name]；

According to the parameter type, we divide it into three categories:
1. Without parameters
2. With input parameters
3. With input and output parameters (return value)

5.1 parameterless stored procedure

Create stored procedure:

create or replace procedure p_hello is
begin
  Dbms_Output.put_line('hello');
end p_hello;

Call via PLSQL

begin
  -- plsql Call stored procedure
  p_hello;
end;

be careful:
1.is and ad can be used mutually.
2. There is no declare keyword in the procedure. Declare is used in the statement block.

5.2 stored procedure with input parameters

Example: query and print the name and salary of an employee (such as employee 7839): required, the employee number will be passed in when calling, and the console will print automatically.

#in identifies the input
creat or replace procedure p_querynameandsal(i_empno in emp.empno%type) is
  #Declare variables to receive query results
  v_name emp.ename%type;
  v_sal emp.sal%type;
begin  
  #Query the name and salary according to the employee number passed by the user
  select ename,sal into v_ename,v_sal from emp where empno=i_empno;
  #Print results
  dmbs_output.put_line('full name:'||v_ename||',Salary:'||v_sal)
end p_querynameandsal;

PLSQL program call

begin
  -- plsql Call stored procedure
  p_querynameandsal(7839);
end;
``

5.3 stored procedure with input and output parameters

Example: enter the employee number to query the information of an employee (employee 7839). It is required to output the salary as the return value to the calling program.

#in is the input parameter; out is the output parameter
creat or replace procedure p_querysal_out(i_empno in emp.empno%type,o_sal out emp.sal%type) is
begin
  select sal into o_sal from emp where empno=i_empno;
end;

PLSQL program call

declare
  #Declare variables to receive the output parameters of the stored procedure
  v_sal emp.sal%type;
begin
  p_querysal_out(7839,v_sal);
  dbms_output.put_line(v_sal);
end;

6.for loop and cursor are used together

declare
  #The cursor c_emp contains the names and salaries of all employees in the queried emp table
  cursor c_emp is select ename,sal from emp;
 begin
    #Use for to loop through the cursor and put the looped object into tmp. tmp can name itself at will
  for tmp in c_emp loop
    #You can use tmp Column name call
    dbms_output.put_line(tmp.ename||'-'||tmp.sal);
  end loop;
end;