Oracle学习交流（3） -----PL/SQL语言基础

 

declare
msg varchar2(20) :='hi,scce!';
begin
  dbms_output.put_line(msg);
end;

declare
v_pi constant number:=3.14;
v_r number:=&v_r;
v_area number;
begin
  v_area:=v_pi*v_r;
 dbms_output.put_line(v_area);
end;

 

select * from emp;

--查询指定员工所属部门的最高工资
declare
 v_ename varchar2(20):=upper('&name');
 v_deptNo number;
 v_totalSalary number;
begin
  select emp.deptno into v_deptNo from emp where emp.ename=v_ename;
  select Max(emp.sal) into v_totalSalary from emp where emp.deptno=v_deptNo;
  dbms_output.put_line(v_totalSalary);
  Exception when no_data_found then
    dbms_output.put_line('用户未找到');
end;

select * from emp

--查询指定员工所属部门的最高工资
declare
v_empName emp.ename%type :=upper('&empName');--指定特定表中特定列的类型
v_deptNo emp.deptno%type;
v_totalSalary emp.sal%type;
begin
    select emp.deptno into v_deptNo from emp where emp.ename=v_empName;
  select Max(emp.sal) into v_totalSalary from emp where emp.deptno=v_deptNo;
  dbms_output.put_line(v_totalSalary);
  Exception when no_data_found then
    dbms_output.put_line('用户未找到');
end;

select * from emp;

declare
v_empRow emp%rowtype;--此变量代表EMP中的一行(行类型)
begin
  select * into v_empRow from emp where emp.ename='ALLEN';
  dbms_output.put_line(v_empRow.sal);
end;

declare
type empRecord is record( --声明一个自定义类型
     firstName emp.ename%type,
     salary emp.sal%type
);
v_row empRecord;--声明一个自定义类型的变量
begin
  select emp.ename,emp.sal into v_row from emp where emp.empno=7499;--此类型只能存放一条数据
  dbms_output.put_line(v_row.salary);
end;

select * from emp

declare
type empNames is table of varchar2(20) index by binary_integer; --声明一个字符串数组的索引类型
type empNo is table of number index by binary_integer;          --声明一个数字数组的索引类型
v_empNames empNames;   --声明一个基于数字索引的字符串数组
v_empNos empNo;        --声明一个基于数字索引的数字数组
begin
  v_empNos(0):=7499;
  select emp.ename into v_empNames(0) from emp where emp.empno=v_empNos(0);
  dbms_output.put_line(v_empNames(0));
end;

--根据员工编号查询一条数据存放到EMP的行类型的集合的下标为0的位子
declare
type empRows is table of emp%rowtype index by binary_integer;--定义了一个emp表的行类型为元素集合并添加了索引
v_empRows empRows;--声明了一个EMP的行类型的变量
begin
  select * into v_empRows(0) from emp where emp.empno=7499;
  dbms_output.put_line(v_empRows(0).ename);
end;

declare
type v_array is array(3) of varchar2(10);--定义了一个字符串类型的数组长度为3.下边从1开始
v_array_eg v_array:=v_array(null,null,null);--声明了一个数组类型的变量,并初始化为null
begin
  v_array_eg(1):='aaa';--赋值
  v_array_eg(2):='bbb';
  v_array_eg(3):='ccc';
  dbms_output.put_line(v_array_eg(3));--输出数组下边为3的值
end;

--根据用户输入的值进行输出操作
declare
v_num number:=&flag;
begin
      if   v_num=1 then dbms_output.put_line(1);
      else if  v_num=2 then dbms_output.put_line(2);
       else
         dbms_output.put_line(0);
      end if;
      end if;
end;

--使用case 变量值 when 指定值形式
declare
v_num number:=&flag;
begin
  case v_num when 1 then dbms_output.put_line(1);
  when 2 then dbms_output.put_line(2);
  else dbms_output.put_line(0);
  end case;
end;

--使用case when 表达式形式
declare
v_num number:=&flag;
begin
  case  when v_num= 1 then dbms_output.put_line(1);
  when v_num=2 then dbms_output.put_line(2);
  else dbms_output.put_line(0);
  end case;
end;

 

--do-while循环
declare
type v_array is array(3) of number;--定义一个数字类型的数组
v_array_eg v_array;--声明一个数组
v_index number:=1;--声明一个变量
begin
  v_array_eg := v_array(1,2,3);--初始化一个数组
  loop--do-while
    dbms_output.put_line(v_array_eg(v_index));
    v_index:=v_index+1;
    exit when v_index> v_array_eg.count;--判断当前循环变量大于数组的长度退出循环
  end loop;
end;

--while循环
declare
type v_array is array(3) of number;
v_array_eg v_array;
v_index number:=1;
begin
  v_array_eg := v_array(1,2,3);
  while v_index <= v_array_eg.count loop
    dbms_output.put_line(v_array_eg(v_index));
    v_index:=v_index+1;
  end loop;
end;

--for循环
declare
type v_array is array(3) of number;
v_array_eg v_array;
v_index number:=1;
begin
  v_array_eg := v_array(1,2,3);
 for i in 1..v_array_eg.count loop
   dbms_output.put_line(v_array_eg(i));
 end loop;
end;

 

declare
v_row emp%rowtype;
begin
  select * into v_row from emp;--v_row只能保存一行,带查询却返回了多行.因此异常
  exception
    when too_many_rows then dbms_output.put_line('异常');
end;

--自定义异常并抛出
declare
myException exception;
begin
  update emp set sal = sal *100 where emp.empno=1;
  if sql%notfound then raise myException;
  else
    dbms_output.put_line('更新成功!');
    end if;
   exception
     when myException then dbms_output.put_line('未找到您要更新的员工!');
end;

--动态执行DDL语句(必须是字符串形式)
begin
  execute immediate 'create table tb_temp(id number primary key,name varchar2(20))';
end;

select * from tb_temp
drop table tb_temp

--循环
--LOOP循环
---语法:
--LOOP
--    CODE....
-- EXIT WHEN 条件
--END LOOP
DECLARE
TYPE namesArr is varray(4) of varchar2(10);
vindex number;
names namesArr;
begin
  names :=namesArr('1','2','3','4');
  vindex :=4;
  LOOP
    dbms_output.put_line(names(vindex));
    vindex:=vindex-1;
    EXIT when vindex<=0;
    END LOOP;
end;

 

--WHILE循环
DECLARE
TYPE namesArr is varray(4) of varchar2(10);
vindex number;
names namesArr;
begin
  names :=namesArr('1','2','3','4');
  vindex :=4;
  WHILE vindex>0 LOOP
    dbms_output.put_line(names(vindex));
    vindex:=vindex-1;
    END LOOP;
end;

--FOR循环
DECLARE
TYPE namesArr is varray(4) of varchar2(10);
names namesArr;
begin
  names :=namesArr('1','2','3','4');
  for i in 1..names.count loop
    dbms_output.put_line(names(i));
    end loop;
end;

--BULK COLLECT INTO 将EMP表的内容直接放进TABLE类型变量去.大数据量时效率高!(必须)
--PS:注意,此时TABLE类型变量下标从1开始的,如果像之前TABLE类型使用的例子使用的话,下标为0开始,比如v_emp(0):=xxx;那么这个时候下标是从0开始.
declare
type v_emp_table is table of emp%rowtype index by binary_integer;
v_emp v_emp_table;
begin
  select * BULK COLLECT  into v_emp from emp;
  for i in 1..v_emp.count loop
    dbms_output.put_line(v_emp(i).ename);
  end loop;
  exception
    when NO_DATA_FOUND then DBMS_OUTPUT.put_line('未找到数据');--NO_DATA_FOUND 表示未找到数据
end;

--异常
declare v_row emp%rowtype;
begin
  select * into v_row from emp ;
  dbms_output.put_line('员工编号'||v_row.EMPNO);
  dbms_output.put_line('员工工资'||v_row.SAL);
  exception
    when too_many_rows then dbms_output.put_line('行数太多!');
end;
select * from emp

--自定义异常
declare
myexception exception;
begin
  update emp set sal=sal+2 where empno=&nid;
  if SQL%notfound then
    raise myexception;
    else
      dbms_output.put_line('已经更新');
      end if;
      exception
        when myexception then dbms_output.put_line('未更新!');
end;

--在PLSQL中执行 动态的DDL,DCL
--示例1 using表示输入参数    returning into 表示输出参数
declare
      v_empid number:=&eid;
      v_dml varchar2(500);
      v_salary number;
begin
  v_dml:='update emp set sal=sal+100 where emp.empno=:empid returning sal into :sal';
  execute immediate v_dml using v_empid returning into v_salary;
  dbms_output.put_line(v_salary);
end;

select * from emp

--动态创建TABLE
declare
--声明 结构体
  TYPE create_table_record is RECORD(   --定义一个自定义类型
    filed_name    varchar2(12),
    filed_type    varchar2(15),
    filed_explain varchar2(15));
--声明表
  TYPE DYNAMIC_SQL_TABLE is table of create_table_record index by binary_integer;--定义一个基于自定义类型的集合
--创建表的实例
  v_dynamic_table    DYNAMIC_SQL_TABLE;--声明一个自定义类型集合的变量
 
  v_create_tablename varchar2(20);
  v_dynamic_sql_ddl  varchar2(500) := '';
  v_dynamic_sql_dcl  varchar2(500) := '';
  v_grant_user       varchar2(500) := '';
  v_grant_quanxian   varchar2(10);
begin
  --为变量赋值
  v_create_tablename := 'STUD';--表名
  v_grant_user := 'hr';--用户名
  v_grant_quanxian := 'select';--权限
 
  v_dynamic_table(0).filed_name := 'sid';--第一个字段
  v_dynamic_table(0).filed_type := 'varchar2(20)';--第一个字段类型
  v_dynamic_table(0).filed_explain := 'primary key';--第一个字段 描述
 
  v_dynamic_table(1).filed_name := 'sname';
  v_dynamic_table(1).filed_type := 'varchar2(20)';
  v_dynamic_table(1).filed_explain := 'not null';
 
  v_dynamic_table(2).filed_name := 'sclass';
  v_dynamic_table(2).filed_type := 'varchar2(20)';
  v_dynamic_table(2).filed_explain := 'not null';
  --构建DDL,DCL语句 CHR(13)函数返回"回车 "
  v_dynamic_sql_ddl := 'create table ' || v_create_tablename || chr(13) || '(' ||chr(13);
  --FOR循环构建DDL语句  --||表示连接符
  --动态拼接SQL语句时不能在字段后添加;号
  for i in 0 ..v_dynamic_table.count - 1 loop
    v_dynamic_sql_ddl := v_dynamic_sql_ddl || v_dynamic_table(i).filed_name || ' ' || v_dynamic_table(i)
                        .filed_type || ' ' || v_dynamic_table(i)
                        .filed_explain || ',' || chr(13);
  end loop;
  v_dynamic_sql_ddl := substr(v_dynamic_sql_ddl,0,length(v_dynamic_sql_ddl) - 2);
  v_dynamic_sql_ddl := v_dynamic_sql_ddl || chr(13) || ')';
   dbms_output.put_line(v_dynamic_sql_ddl);
   --构建DCL语句
  v_dynamic_sql_dcl := 'grant ' || v_grant_quanxian || ' on ' ||
                       v_create_tablename || ' to ' || v_grant_user;
  dbms_output.put_line(v_dynamic_sql_dcl);
  --执行DDL,DCL语句
  execute immediate v_dynamic_sql_ddl;
  execute immediate v_dynamic_sql_dcl;
end;

select * from scott.stud
drop table scott.stud

--简单的动态ddl
declare
--创建记录类型,此例每行两个字段
type v_table_record is record(
     fieldName varchar2(20),
     fieldType varchar2(50)
);
--创建索引表类型,用来组装DDL语句
type v_create_sql is table of v_table_record index by binary_integer;
--创建索引表变量
v_create v_create_sql;
--要创建的表名变量
v_tableName varchar2(10);
--最后组装好的DDL语句
v_ddl_sql varchar(500);
begin
--为各个变量赋值
  v_tableName:='TestTable';
  v_create(0).fieldName:='tid';
  v_create(0).fieldType:='number';
  v_create(1).fieldName:='tname';
  v_create(1).fieldType:='varchar2(10)';
  --组装DDL语句
  v_ddl_sql:='Create table '||v_tableName||chr(13)||'(';
  for i in 0..v_create.count-1 loop
   v_ddl_sql:=v_ddl_sql||v_create(i).fieldName||' '||v_create(i).fieldType||',';
  end loop;
  --去掉最后一个逗号,并加上创建语句的结束符号" ) "
  v_ddl_sql:=substr(v_ddl_sql,0,length(v_ddl_sql)-1)||')';
  --输出测试
  dbms_output.put_line(v_ddl_sql);
  --执行
  execute immediate v_ddl_sql;
end;

select * from testtable
drop table testtable

 

--returning 子句 可以将更新语句的里更新的值返回给某个变量
select * from emp;
declare
v_sal number;
begin
  update emp set sal=1900 where empno=7499 returning sal into v_sal;
  dbms_output.put_line(v_sal);
end;

--动态执行DML
declare
v_sal number;
v_dml varchar(500);
begin
  v_dml:='update emp set sal=1900 where empno=:empno returning sal into :v_sal';
  execute immediate v_dml using 7499 returning into v_sal;
  dbms_output.put_line(v_sal);
end;

--执行单行查询
declare
v_dynamic_sql varchar2(500);
empid number :=&number;
v_row emp%rowtype;
begin
  v_dynamic_sql:='select * from emp where emp.empno =:empid';
  execute immediate v_dynamic_sql into v_row using empid;
  dbms_output.put_line(v_row.ename);
end;
select * from emp
--执行多行查询
declare
Type v_table_emp is table of emp.ENAME%type index by Binary_Integer;
v_table v_table_emp;
deptid number:=&deptid;
v_dynamic_sql varchar2(500);
begin
  v_dynamic_sql:='select ename from emp where deptno =:deptid';
  execute immediate v_dynamic_sql bulk collect into v_table using deptid;
  --bulk collect into批量将查询结果集赋值给集合  动态执行SQL查询赋值必须使用bulk collect into
  for i in 1 .. v_table.count loop --注意:批量赋值循环迭代变量必须从1开始
    dbms_output.put_line(v_table(i));
  end loop;
end;
select * from emp