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;



阅读更多
文章标签: 学习日志
个人分类: plsql
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭