pl/sql基础语法

洋洋同学82331
348 阅读5分钟

语法结构：

declare
..
begin
..
exception
..
end;
/
语句块的类型: 
      1.存储过程 
      2.函数 
      3.包 
      4.触发器
变量类型：varchar2     char    long        number
使用%type方式复制变量类型，减少错误。
v_ename varchar(10);
可以写为
v_ename emp.ename%type;
使用%rowtype来代替record的类型定义 和%type类似 %type是参照一列的类型 而%rowtype是将表的所有列做为了record中的成员 列名即是成员名

declare
        emp_record scott.emp%rowtype;
begin
    select * into emp_record from scott.emp where empno=7788;
    dbms_output.put_line(emp_record.ename||' '||emp_record.sal);
end;
/

常量，一般变量

--输入员工号得到工资等信息 
--税率为0.03 
declare  
--常量用c开头，变量让v开头 
c_tax_rate number(7,2):=0.03; 
v_ename varchar(10); 
v_sal number(7,2); 
v_tax_sal number(7,2); 
begin  
select ename,sal into v_ename,v_sal from emp where empno=&no; 
--计算所得税，赋值符号一定不能错 
v_tax_sal:=v_sal*c_tax_rate; 
--输出信息 
dbms_output.put_line('此员工的信息如下：'||v_ename||'  工资：'||v_sal||'  税：'||v_tax_sal); 
end;

复合变量：记录类似高级语言中的结构体，表相当于数组

--使用记录变量的实例 
declare 
--定义一个pl/sql记录类型 
type emp_record_type is record(name emp.ename%type,sal emp.sal%type); 
--给记录一个别名 
sp_record emp_record_type; 
begin 
select ename,sal into sp_record from emp where empno=&no; 
--获取记录里的信息的方法 
dbms_output.put_line('此员工的信息如下：'||sp_record.name||'  工资：'||sp_record.sal); 
end;

--表类型使用的案例（接受一个值） 
declare 
--定义一个table类型的变量，存放的类型是emp.ename%type 
--使用的下标是整数型的 
type sp_table_type is table of emp.ename%type index by binary_integer; 
sp_table sp_table_type; 
begin  
--下标要对应才可以，可以自己定义，这样写只能取出一个记录 
select ename into sp_table(0) from emp where empno=7788; 
dbms_output.put_line('员工名'||sp_table(0)); 
end;

参照变量：游标和对象类型 一般都是用游标变量cursor

declare 
--定义游标类型  sp_cursor 
type sp_emp_cursor is ref cursor; 
--定义游标变量 
test_cursor sp_emp_cursor; 
--定义变量接受姓名和工资 
v_ename emp.ename%type; 
v_sal emp.sal%type; 
begin 
--把一个游标和select结合 
open test_cursor for select ename,sal from emp where deptno=&no; 
--循环取出数据,循环结构使用loop结构 
loop 
  --fetch是取出游标的意思 
   fetch test_cursor into v_ename,v_sal; 
  --判断退出的条件,没有就是死循环了哦 
   exit when test_cursor%notfound; 
  --输出 
   dbms_output.put_line('姓名：'||v_ename||'  工资:'||v_sal); 
end loop ; 
end ;

变量的作用范围：内部块可以调用外部块中的变量

declare
    v_var1 number := 123;
begin
        dbms_output.put_line('OUT: v_var1 :'||v_var1);
        declare
                v_var2 number := 456;
        begin
                dbms_output.put_line('IN: v_var1 :'||v_var1);
                dbms_output.put_line('IN: v_var2 :'||v_var2);
        END;
        -- dbms_output.put_line('IN: v_var2 :'||v_var2);  内部块中的变量不能被外部块使用. 去掉注释则报错
end;
/

使用select查询结果对变量赋值 PL/SQL中的select必需使用into将选出结果存入变量之内

declare
        v_ename varchar2(10);
        v_sal  number;
begin
    select ename,sal into v_ename,v_sal from scott.emp where empno=7788;
    dbms_output.put_line(v_ename||' '||v_sal);
end;
/

结构控制

if then elseif then else end if

if expr1
then
	action1
elif expr2
then
	action2
...
else
	default_action;
fi

declare
  v_hire_date date := to_date('1995-01-15','yyyy-mm-dd');
  v_five_years boolean;
begin
    if months_between(sysdate,v_hire_date)/12 > 5 then
      v_five_years := true;
    dbms_output.put_line('true');
    else
      v_five_years := false;
    dbms_output.put_line('false');
  end if;
end;

case when else

set serveroutput on
define p_grade='a'
declare
  v_grade char(1) := upper('&p_grade');
  v_appraisal varchar2(20);
begin
  v_appraisal :=
    case v_grade
        when 'A' then 'excellent'
        when 'B' then 'very good'
        when 'C' then 'good'
      else 'no such grade!'
    end;
  dbms_output.put_line ('grade: '|| v_grade || ' appraisal ' || v_appraisal);
end;
/

declare
  v_grade char(1) := upper('&p_grade');
begin
    case v_grade
        when 'A' then 
		dbms_output.put_line('grade: '|| v_grade || ' appraisal '||'excellent');
        when 'B' then
		dbms_output.put_line('grade: '|| v_grade || ' appraisal '||'very good');
        when 'C' then 
		dbms_output.put_line('grade: '|| v_grade || ' appraisal '||'good');
        else 
		dbms_output.put_line('grade: '|| v_grade || ' appraisal '||'no such grade!');
    end case;
end;
/

loop循环

declare
  i integer :=1;
begin
  loop
    dbms_output.put_line(i);
    i := i+1;
    exit when i>10;
  end loop;
end;
/

while循环

declare
  i integer :=1;
begin
  while i<=10 loop
    dbms_output.put_line(i);
    i := i+1;
  end loop;
end;

for循环

begin
  for i in 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;

双重循环

declare
  v_plus number(10);
begin
  <<outer_loop>>
   for i in 1..5 loop
     <<inner_loop>>
     for i in 1..5 loop
        v_plus:=i*outer_loop.i;
        exit when v_plus>15;
        dbms_output.put_line(v_plus);
     end loop inner_loop;
   end loop outer_loop;
end;

goto

declare
   v_counter number := 1;
begin
   loop 
       dbms_output.put_line('in loop V_counter current value:'||V_counter);
       v_counter := v_counter + 1;
     if v_counter > 10 then
       goto l_ENDofLOOP;
      end if;
   end loop;
   <<l_ENDofLOOP>>
   dbms_output.put_line('end loop V_counter current value:'||V_counter);
end;

使用bulk collect批量绑定和forall 避免sql引擎和plsql引擎频繁切换，提高效率 forall只执行一次，相当于一条sql语句，但插入的是多行

 declare
 v_max dept.deptno%type;
 type type_num is table of number index by binary_integer;
 type type_name is table of varchar2(20) index by binary_integer;
 v_num type_num;
 v_name type_name;
 begin
 select max(deptno) into v_max from dept;
 select rownum,'Test'||to_char(rownum) bulk collect into v_num,v_name from dual connect by rownum<=5;
  forall i in 1..v_num.count
    insert into dept(deptno,dname) values(v_max+v_num(i),v_name(i));
    commit;
 end;

事务处理控制语句(COMMIT 和 ROLLBACK):

declare
    v_num number:=0;
begin
    for v_loopcounter in 1..500 loop
    insert into emp values(v_loopcounter);
    v_num = 50 then commit;
    v_num :=0;
    end if;
    end loop;
commit;
end;

Plsql表+record+循环打印结果集(dept表的所有行所有列)

declare
  TYPE CharacterTab IS TABLE OF dept%rowtype
  index by binary_integer;
  v_Character CharacterTab;
  v_max number;
  v_deptno number;
begin
 select count(*) into v_max from dept;
 for i in 1..v_max loop
   select deptno 
   into v_deptno
   from
   (select rownum rn,d.* from dept d)
   where rn=i;
   select * 
   into v_Character(i) 
   from dept 
   where deptno=v_deptno;
  end loop;   
   for i in 1..v_max loop   
     dbms_output.put_line(v_Character(i).deptno||' '||v_Character(i).dname||' '||v_Character(i).loc);
   end loop;
end;

使用bulk collect 批量将数据绑定到数组中，可以减少sql执行次数，提高程序运行效率

declare
  TYPE CharacterTab IS TABLE OF dept%rowtype
  index by binary_integer;
  v_Character CharacterTab;
begin
   select * bulk collect into v_Character from dept;
   for i in v_Character.first..v_Character.last loop
     dbms_output.put_line(v_Character(i).deptno||' '||v_Character(i).dname||' '||v_Character(i).loc);
   end loop;\
end;

游标

游标的使用流程：声明、打开、获取、关闭

游标是在open时使用内存的.获取一次,内存中就少一行. 游标获取了之后应该使用相关的变量接收游标的值，就像select..into 一样。

declare
	cursor c1 is select ename,sal from scott.emp;
	v_ename scott.emp.ename%type;
	v_sal   scott.emp.sal%type;
begin
	open c1 ;
	loop
		fetch c1 into v_ename,v_sal;
		dbms_output.put_line(v_ename||' '||v_sal);
		exit when c1%rowcount>5;
	end loop;
	close c1;
end;

使用游标取表中的所有行所有列

declare
	cursor c1 is select * from scott.dept;
	v_deptno dept.deptno%type;
	v_dname  dept.dname%type;
	v_loc	 dept.loc%type;
begin
	open c1;
	loop
		fetch c1 into v_deptno,v_dname,v_loc;
		exit when C1%NOTFOUND;
		dbms_output.put_line(v_deptno||' '||v_dname||' '||v_loc);
	end loop;
	close c1;
end;

游标for循环:使用for循环来代替loop循环 for循环中的变量根据in关键字后面的内容而决定变量的类型 如果in后面是数字 则变量是number类型标量变量 如果in后面是游标 则变量是record类型复合变量

declare
        cursor c1 is select ename,sal from scott.emp;
begin
        for r1 in c1 loop
                exit when c1%rowcount>5;
                dbms_output.put_line(r1.ename||' '||r1.sal);
        end loop;
end;

参数游标

declare
	cursor c1(v_deptno scott.emp.deptno%type)
	is
		select deptno,ename,sal from scott.emp where deptno=v_deptno;
begin
	for r1 in c1 (&deptno) loop
		dbms_output.put_line(r1.deptno||' '||r1.ename||' '||r1.sal);
	end loop;
end;


avatar
文章
阅读
粉丝