1. Grammar

• Break executable method saved on the database server for other developers to call

• It can effectively reduce the data interaction between database and server, improve efficiency and reduce bandwidth consumption

• Syntax format:

  declare -- define part, save variable, reference variable, record exception
  
  begin -- logical processing part
  
  Exception -- need to catch exception is to write
  
  End; -- end
  

2. Define variables

• Example

declare
	-- Defining variables
	i number := 1;
	-- Defining constants
	pi constant number := 2;
	-- Define record variables/Record a row of data
	-- Variable name table name%rowtype;
	v_emp emp%rowtype;
	-- Defining referential variables
	-- Variable name.List clearly%type
	pname emp.ename%type;
	
begin
	select * into pemp from emp;
end;

3. if judgment

• Syntax:

  /*
  if Judgment:
  if Conditional expression then
  
  elsif Conditional expression then
  
  else 
  
  end if;
  */
  
  -- Example:
  declare
  	age number := 18;
  begin
  	if age = 17 then
  	dbms_output.put_line('I'm 17');
          elsif age = 18 then
          dbms_output.put_line('I'm 18');
          	else
          	dbms_output.put_line('I am??');
  	end if;
  end;
  

4. loop loop

• Syntax:

  /*
  Syntax 1: equivalent to while loop
  while Conditional expression loop
  
  end loop;
  */
  -- Example
  declare
    i number := 0;
  begin
    while i < 10 loop
      i := i + 1;
      dbms_output.put_line(i);
    end loop;
  end;
  
  /*
  Grammar 2:
  loop
  	exit when Exit cycle condition
  
  end loop;
  */
  -- Example:
  declare
    i number := 0;
  begin
    loop
      i := i + 1;
      exit when i > 10;
      dbms_output.put_line(i);
    end loop;
  end;
  
  /*
  Grammar 3:
  1..10: It can also be the result set of query
  for Variable in 1..10 loop;
  
  end loop
  */
  -- Example:
  declare
    i number := 0;
  begin
    for i in 1..10 loop
      dbms_output.put_line(i);
    end loop;
  end;
  

5. cursor

• Is a private SQL workspace, divided into implicit cursors and display cursors. We usually declare display cursors

• Used to operate the result, equivalent to the iterator in java

• Syntax:

  /*
  Development steps:
  	1. Declare cursor: cursor name is query result set
  	2. Open cursor: open game name
  	3. Get data from cursor: fetch cursor name into variable name
  			Game name% found: data found
  			Game name% notfound: no data found
      4. Close cursor
  */
  
  declare
    emp_rows emp%rowtype;
    -- 1.
    cursor rows is select * from emp;
  begin
    -- 2.
    open rows;
    	-- 2.1
      loop
        -- 3.
        fetch rows into emp_rows;
        -- 3.1
        exit when rows%notfound;
        dbms_output.put_line(emp_rows.ename);
      -- 3.2
      end loop;
    -- 4.
    close rows;
  end;
  

6. exception

• Syntax:

  /*
  yvfa:
  declare
  
  begin
  
  exception
  	when Exception type then
  	Handle
  end;
  */
  -- Example:
  declare
    num number := 1;
  begin
    num := num/0 ;
  exception
    when zero_divide then
      dbms_output.put_line('Abnormal division by zero');
  end;
  
  -- Custom exception:
  declare
      no_date exception;
  begin 
      raise no_date;
  
  exception
      when no_date then
        dbms_output.put_line('Custom exception');
  end;