Introduction to Oracle (PLSQL) 8

Learning video: https://www.bilibili.com/video/BV1tJ411r7EC?p=75

Cursor: a container for holding multiple pieces of data. You need to start open ing and closing. Use "fetch...into..." to move the cursor down.

declare
  cursor myCursor is
    select * from emp;
  yb myCursor%rowtype;
begin
  open myCursor;
  for i in 1 .. 3 loop
    fetch myCursor
      into yb;
    dbms_output.put_line(yb.empno || yb.ename);
  end loop;
  close myCursor;
end;

Use the% found and% notfound attributes to determine whether the cursor has a value. The premise of using these two attributes is that the cursor must be moved down.

declare
  cursor myCursor is
    select * from emp;
  yb myCursor%rowtype;
begin
  open myCursor;
  loop
    fetch myCursor
      into yb;
    dbms_output.put_line(yb.empno || '-' || yb.ename);
    exit when myCursor%notfound;
  end loop;
  close myCursor;
end;
declare
  cursor myCursor is
    select * from emp;
  yb myCursor%rowtype;
begin
  open myCursor;
  fetch myCursor
    into yb; --The cursor must be moved down before it can be used%found and%notfound attribute
  while myCursor%found loop
    fetch myCursor
      into yb;
    dbms_output.put_line(yb.empno || '-' || yb.ename);
  end loop;
  close myCursor;
end;

Smart cursor: variable definition, auto switch, auto move down.

--Smart cursor
--Variable auto definition
--Cursor auto open
--Cursor auto down
--Auto close

declare
  cursor myCursor is
    select * from emp;
begin
  for employee in myCursor loop
    dbms_output.put_line(employee.empno || '-' || employee.ename);
  end loop;
end;

Exception: unexpected program error.

declare
  employee emp%rowtype;
begin
  select * into employee from emp where empno = 70369;
exception
  when no_data_found then
    dbms_output.put_line('Data not found');
  when others then
    dbms_output.put_line('Default exception,,,');
end;

Stored procedure: send the stored procedure name remotely, without sending specific sql, to avoid being intercepted and tampered with sql during sending. Advantages: increased efficiency and safety.

create or replace procedure getmax(x number, y number) is
begin
  if x > y then
    dbms_output.put_line(x);
  else
    dbms_output.put_line(y);
  end if;
end;

call getmax(10,12);
exec getmax(11,12);
execute getmax(45,56);

Call the three methods of stored procedure call, exec and execute.

Stored procedure parameter mode: in indicates read-only, out indicates write only, in out indicates read-write.

--Parameter variable parameter mode parameter type. The default mode is in. 
create or replace procedure getmax(x in number, y in number,z out number) is
begin
  if x > y then
    z:=x;
  else
    z:=y;
  end if;
end;

declare
  max_result number;
begin
  getmax(89, 85, max_result);
  dbms_output.put_line(max_result);
end;

Function: different from stored procedure, it can return value.

create or replace function fgetmax(x number, y number) return number is
begin
  if y > x then
    return y;
  else
    return x;
  end if;
end;
/

select fgetmax(45,44) from dual;

Tags: Oracle Stored Procedure SQL Attribute

Posted on Wed, 06 May 2020 12:43:45 -0400 by ecko