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;

Oracle筆記 六、PL/SQL簡單語句塊、變數定義