plsql 学习日志

20180302

最简单的plsql过程，输出‘Hello World’，serverouput 打开输出，以显示字符

declare
begin
dbms_output.put_line('Hello World');   --输出字符
end;
/

set serveroutput on;
/

定义变量，注意赋值语句使用 := ,为了和SQL的表连接语句区分

declare
pnumber number(7, 2);
pname varchar2(20);
pdate date;
begin
  pnumber := 1;
  dbms_output.put_line(pnumber);
  pname := 'Tom';
  dbms_output.put_line(pname);
  pdate := sysdate;
  dbms_output.put_line(pdate);
  dbms_output.put_line(pdate + 1);
end;

变量类型可以使用原表中的类型指定 

declare
pname scott.emp.ename%type;
psal scott.emp.sal%type;
begin
  select ename, sal into pname,psal from scott.emp where empno = 7839;
  dbms_output.put_line(pname||psal);
end;

可以指定一‘行’为一个变量，并用变量.属性的方式调用

declare
emp_rec scott.emp%rowtype;
begin
  select * into emp_rec from scott.emp where empno = 7839;
  dbms_output.put_line(emp_rec.ename||emp_rec.sal);
end;

流程控制语句，if示例，&num为暂停程序，以读取一个键盘输入

set serveroutput on;
accept num prompt 'input a number:';
declare
pnum number := #
begin
  if pnum = 0 then dbms_output.put_line('the number is 0');
  elsif pnum = 1 then dbms_output.put_line('the number is 1'); 
  elsif pnum = 2 then dbms_output.put_line('the number is 2'); 
  else dbms_output.put_line('the number is'||pnum); 
  end if;
end;
/

流程控制语句，循环示例，包括while、loop、for三种循环，for循环开头1..10代表从1到10，遍历每个数字。

declare
pnum number := 1;
begin
  while pnum <= 10 loop
    dbms_output.put_line('the number is:'||pnum);
    pnum := pnum + 1;
  end loop;
  pnum := 1;
  dbms_output.put_line('----------------------------------');
  loop
    exit when pnum > 10;
    dbms_output.put_line('the number is:'||pnum);
    pnum := pnum + 1;
  end loop;
  pnum := 1;
  dbms_output.put_line('----------------------------------');
  for pnum in 1..10 loop
    dbms_output.put_line('the number is:'||pnum);    
  end loop;

end;

游标定义，示例，从emp表中逐个获取姓名、薪水字段的值，并打印。

游标在使用前，需要在begin模块中打开，使用结束要关闭。

获取数据使用fetch语句，写入变量

cemp%notfound 为游标属性，判断最近一次执行fetch语句，是否从缓冲区中提取到数据，没有数据，返回True

declare
cursor cemp is select ename, sal from scott.emp;
pname scott.emp.ename%type;
psal scott.emp.sal%type;
begin
  open cemp;
  loop
    fetch cemp into pname,psal;
  exit when cemp%notfound;
  dbms_output.put_line(pname||':'||psal);
  end loop;  
  close cemp;
end;

游标，示例2，为主管和经理涨工资

declare
cursor cemp is select empno,job from scott.emp;
pempno scott.emp.empno%type;
pjob scott.emp.job%type;
begin
  open cemp;
  loop
    fetch cemp into pempno,pjob;
  exit when cemp%notfound;
    if pjob = 'PRESIDENT' then update scott.emp set sal = sal + 1000 where empno = pempno;
    elsif pjob = 'MANAGER' then update scott.emp set sal = sal + 800 where empno = pempno;
    else update scott.emp set sal = sal + 400 where empno = pempno;
    end if;
  dbms_output.put_line(pempno||':'||pjob);
  end loop;  
  close cemp;
end;

20180303

显示打开的游标数量

show parameter cursor;

修改游标数的限制，scope的取值：both（后面两个一起改）,memory,spfile(数据库需要重启)

alter system set open_cursors = 400 scope = both;

判断游标属性，%isopen 检测游标是否已经打开，%rowcount 返回目前为止缓冲区提取数据的行数，在fetch没有执行前为0

declare
cursor cemp is select empno,job from scott.emp;
pempno scott.emp.empno%type;
pjob scott.emp.job%type;
begin
  open cemp;
  if cemp%isopen then
    dbms_output.put_line('cursor is open');
  else
    dbms_output.put_line('cursor is close');
  end if;
  loop
    fetch cemp into pempno,pjob;
    exit when cemp%notfound;
    dbms_output.put_line('rowcount:'||cemp%rowcount);
    dbms_output.put_line(pempno||':'||pjob);
    end loop;
  close cemp;
end;

游标参数，示例，参数设置了类型和默认值，在is select语句中应用参数

begin模块中，open游标时，指定参数的值

declare
cursor cemp(dno number :=10) is select ename from scott.emp where deptno = dno;
pname scott.emp.ename%type;
begin
  open cemp(20);
 
  loop
    fetch cemp into pname;
    exit when cemp%notfound;
    dbms_output.put_line('rowcount:'||cemp%rowcount);
    dbms_output.put_line(pname);
    end loop;
  close cemp;
end;