Oracle Note Six, PL/SQL simple statement block, variable definition

1. Simple SQL statement, hellworld example

--Output information

Begin

  Dbms_output.put_line (' Oracle Hello world! ');

End

2, the definition of variables, use

--Defining variables

Declare

  SName varchar2 (20);

Begin

  SName: = ' Jack ';

  Dbms_output.put_line (SName);

End

--Common types

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: = ' 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

--Define Table variable type

  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

--Define the record variable type

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

--Declaring the record variable with RowType

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 Statement Completion variable assignment

Declare

  V$sal Emp.sal%type;

  V$ename Emp.ename%type;

Begin

  Select Sal, ename to V$sal, v$ename from emp where rownum = 1;

  Dbms_output.put_line (V$sal | | "| | V$ename);

End

--sql statement Completion RowType variable assignment

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 statement completion variable insert data

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 Note Six, PL/SQL simple statement block, variable definition