Oracle笔记 六、PL/SQL简单语句块、变量定义

1、简单SQL语句，HellWorld示例
--输出信息
begin
  dbms_output.put_line('Oracle Hello World!');
end;

2、变量的定义、使用
--定义变量
declare
  sName varchar2(20);
begin
  sName := 'jack';
  dbms_output.put_line(sName);
end;

--常用类型
declare
    sNum number(1);
    sCount binary_integer := 0;
    sSal number(7, 2) := 5000.00;
    sDate date := sysdate;
    sPI number(3, 2) := 3.14;
    sValid boolean := true;
    sName varchar2(20) := 'Jackson';
begin
    dbms_output.put_line('sName:' || sName);
    dbms_output.put_line('sCount:' || sCount);
    dbms_output.put_line('sSal:' || sSal);
    dbms_output.put_line('sDate:' || sDate);
    dbms_output.put_line('sPI:' || sPI);
    --dbms_output.put_line('sValid:' || sValid);
    dbms_output.put_line('sName:' || sName);
end;

--定义Table变量类型
declare
  type type_table_emp_empno is table of emp.empno%type index by binary_integer;
  empnos type_table_emp_empno;
begin
  empnos(0) := 7369;
  empnos(2) := 6789;
  empnos(-1) := 6543;
  dbms_output.put_line(empnos(-1));
end;

--定义record变量类型
declare
  type type_record_dept is record (
       deptno dept.deptno%type,
       dname dept.dname%type,
       loc dept.loc%type
  );
  temp type_record_dept;
begin
  temp.deptno := 56;
  temp.dname := 'software';
  temp.loc := 'gz';
  dbms_output.put_line(temp.deptno || ' ' || temp.dname  || ' ' || temp.loc);
end;

--使用rowtype声明record变量
declare
  temp dept%rowtype;
begin
  temp.deptno := 57;
  temp.dname := 'it';
  temp.loc := 'sz';
  dbms_output.put_line(temp.deptno || ' ' || temp.dname  || ' ' || temp.loc);
end;

--sql语句完成变量赋值
declare
  v$sal emp.sal%type;
  v$ename emp.ename%type;
begin
  select sal, ename into v$sal, v$ename from emp where rownum = 1;
  dbms_output.put_line(v$sal || ' ' || v$ename);
end;

--sql语句完成rowtype变量赋值
declare
  v_row_emp emp%rowtype;
begin
  select * into v_row_emp from emp where empno = 7698;
  dbms_output.put_line(v_row_emp.sal || ' ' || v_row_emp.ename);
end;

--sql语句完成变量插入数据
create table dept2 as select * from dept;
declare
   deptno dept.deptno%type := 57;
   dname dept.dname%type := 'software';
   loc dept.loc%type := 'gz';
begin
    insert into dept2 values(deptno, dname, loc);
      commit;
end;
select * from dept2;