PLSQL基础语法一

2019独角兽企业重金招聘Python工程师标准>>>

--练习1：间接定义变量,打印helloworld
declare
/*声明*/
 str varchar2(18);
-- i number;
begin
  /*开始*/
   str := 'helloworld';
  -- i:='aaa';
   dbms_output.put_line(str);
exception
  /*异常*/
  when others then
  dbms_output.put_line('异常');
end;
--练习2：直接定义变量并且赋值，打印helloworld
declare
 str varchar2(18):='hello';
 str2 varchar2(18);
 i constant number(3):=100; --constant修饰的变量为常量
begin
   str2:='world';
   dbms_output.put_line(str||str2);
   dbms_output.put_line(i);
exception
  when others then
  dbms_output.put_line('异常');
end;
--练习3：通过sql查询赋值给变量并打印
declare
v_id number;
v_code varchar2(18);
begin
  select 1,'hello world' into v_id,v_code from dual;
   dbms_output.put_line(v_Id||v_code);
  exception
    when others then
      dbms_output.put_line('异常');
    end;
--练习4：插入数据   
declare
v_id number;
v_email varchar2(18);
begin
  v_email:='111590042@qq.com';
 insert into student(id,name,age,email) values(1,'张三',12,v_email); 
 commit;
  exception
    when others then
      dbms_output.put_line('异常');
    end;   
--练习5：%type定义变量类型可以和表的类型一致
declare
 v_id student.id%type; --v_id变量的数据类型是student表里面的id类型
 v_name student.name%type;
 v_email student.email%type;
 v_age student.age%type;
begin
  select id,name,age,email into v_id,v_name,v_age,v_email from student where id = 1;
  dbms_output.put_line('id:'||v_id||',name:'||v_name||',age:'||v_age||',email:'||v_email);
  end;
--练习6:%rowtype 可以理解成对数据库记录一行提取出来的一个副本
declare
 r_student student%rowtype;
begin
  select id,name,age,email into r_student.id,r_student.name,r_student.age,r_student.email from student where id = 1;
  dbms_output.put_line('id:'||r_student.id||',name:'||r_student.name||',age:'||r_student.age||',email:'||r_student.email);
  end;
declare
 r_student student%rowtype;
begin
  select * into r_student from student where id = 1;
  dbms_output.put_line('id:'||r_student.id||',name:'||r_student.name||',age:'||r_student.age||',email:'||r_student.email);
  end;

转载于:https://my.oschina.net/kkrgwbj/blog/470106