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;
最后修改:2022 年 01 月 18 日
如果觉得我的文章对你有用,请随意赞赏