Oracle PL/SQL练习使用

declare
  v_num number := &input;
  v_last_num number := 0;
  v_result varchar(2000) := '';
begin
  loop
    v_last_num := mod(v_num,10);
    v_result := v_result || to_char(v_last_num);
    v_num := v_num - v_last_num;
    exit when v_num=0;
    v_num := v_num / 10;
  end loop;
  dbms_output.put_line(v_result);
end;

第六章  游标(cursor)管理
create SYNONYM emp for scott.emp;
--作用：
declare
  v_ename emp.ename%type;
begin
  select ename into v_ename
  from emp;
 
  dbms_output.put_line(v_ename);
end;

--显式游标的定义和遍历
--通过无条件的循环来遍历游标
declare
  --1.定义游标
  cursor c_emp_cursor
  is select ename from emp;
 
  v_ename emp.ename%type;
begin
    --2.打开游标
    open c_emp_cursor;
   
    --3.遍历游标
    loop
      fetch c_emp_cursor into v_ename; --rs.next();
     
      exit when c_emp_cursor%NOTFOUND;
     
      dbms_output.put_line(v_ename);
    end loop;
   
    --4.关闭游标
    close c_emp_cursor;
end;

--用WHILE循环来遍历游标
declare
  cursor c_emp_cursor
  is select * from emp;
 
  v_row emp%rowtype;
begin
  open c_emp_cursor;
 
  fetch c_emp_cursor into v_row;
 
  while c_emp_cursor%FOUND loop
    dbms_output.put_line(v_row.ename || ' ' || v_row.empno);
    fetch c_emp_cursor into v_row;
  end loop;
 
  close c_emp_cursor;
end; 

--FOR循环遍历游标的方式
declare
  cursor c_emp_cursor
  is select * from emp;
begin
  for v_row IN c_emp_cursor loop
    dbms_output.put_line(v_row.empno || ' ' || v_row.ename);
  end loop;
end;

--定义参数类型的游标
declare
  cursor c_emp_cursor(p_deptno emp.deptno%type)
  is select ename from emp where deptno=p_deptno;
 
  v_ename emp.ename%type;
begin
open c_emp_cursor(&input);

loop
   fetch c_emp_cursor into v_ename;
   exit when c_emp_cursor%NOTFOUND;
   dbms_output.put_line(v_ename);
end loop;

close c_emp_cursor;
end;

--为游标指定返回类型1-rowtype
declare
  cursor c_emp_cursor
  return emp%rowtype  --强制类型
  is select * from emp;
begin
  for v_row IN c_emp_cursor loop
    dbms_output.put_line(v_row.empno);
  end loop;
end;

--为游标指定返回类型2-记录(Record)类型
declare
  --定义一个记录类型
  type t_emp_record is record
  (
    empno emp.empno%type,
    ename emp.ename%type
  );

  v_emp_record t_emp_record;

  cursor c_emp_cursor
  return  v_emp_record   --强制类型
  is select empno,ename from emp;
begin
  open c_emp_cursor;
  loop
    fetch c_emp_cursor into v_emp_record;
    exit when c_emp_cursor%NOTFOUND;
    dbms_output.put_line(v_emp_record.empno);
    dbms_output.put_line(v_emp_record.ename);
  end loop;
  close c_emp_cursor;
end;

--如何通过游标来修改数据
declare
  --通过游标修改数据要上行级锁
  cursor c_emp_cursor
  is select * from emp where deptno=20 for update;
begin
  for v_row In c_emp_cursor loop
    delete from emp
    where current of c_emp_cursor;
  end loop;
  commit;
end;
select * from emp;

--REF引用类型的游标描述
declare
  type t_ref_cursor is ref cursor;
 
  v_ref_cursor t_ref_cursor;
begin
  open v_ref_cursor
  for select * from emp;
 
  close v_ref_cursor;
 
  open v_ref_cursor
  for select * from dept;
 
  close v_ref_cursor;
end;

--1.隐式游标(SQL)的使用
--用途：因为数据库系统在做insert,delete,update,select这些操作的时候
--都是通过游标去完成的，所以，在做操作之前我们的数据库系统都会隐式
--的打开一个游标，在执行操作完毕之后隐式的关闭游标，那么每次的操作的结果
--都会保存在一个叫做SQL的隐式游标中，我们可以通过操作这个隐式游标来获取
--数据库操作的信息

begin
  delete from emp where deptno=10;
 
  dbms_output.put_line(SQL%ROWCOUNT);
end;





第七章 存储过程(Procedure)和包(Package)

SQLServer - 存储过程 (output参数  return)
Oracle -存储过程(out参数) 函数(return)

--1.存储过程
create or replace procedure print(msg varchar2)
is
begin
  dbms_output.put_line(msg);
end;

--1.1 调用存储过程
execute print('helloworld');

--1.2
create or replace procedure findEmp(p_empno emp.empno%type)
is
  v_ename emp.ename%type;
begin
  select ename into v_ename
  from emp where empno=p_empno;
 
  print(v_ename);
exception
  when no_data_found then
    print('未发现指定员工.');
end;

--1.3 传递参数
create or replace procedure myabs(p_num1 IN number,p_num2 OUT number)
is
begin
  if p_num1 >0 then
    p_num2 := p_num1;
  else
    p_num2 := (0 - p_num1);
  end if;
end;

--调用
declare
  v_result number;
begin
  myabs(-987,v_result);
  print(v_result);
end;

create or replace procedure change_num(p_num1 IN OUT number,p_num2 IN OUT number)
is
  v_temp number;
begin
  v_temp := p_num1;
  p_num1 := p_num2;
  p_num2 := v_temp;
end;

--调用
declare
  v_num1 number :=111;
  v_num2 number :=222;
begin
  change_num(v_num1,v_num2);
 
  print(v_num1);
  print(v_num2);
end;

--2.函数(FUNCTION)
create or replace function myabs1(p_num1 number)
return number
is
begin
  if p_num1 >= 0 then
    return p_num1;
  else
    return (0 - p_num1);
  end if;
end;

begin
  print(myabs1(-999));
end;

create or replace function findEmp1(p_empno emp.empno%type)
return emp.ename%type
is
  v_ename emp.ename%type;
begin
  select ename into v_ename
  from emp
  where empno=p_empno;
 
  return v_ename;
exception
  when no_data_found then
    print('未发现员工');
end;

begin
  print(findEmp1(7499));
end;

--3.包(Package)-包(package-定义)和包体(package body-实现)
create or replace package mypkg
is
  procedure print(msg varchar2);
 
  function findEmp(p_empno number)
  return emp.ename%type;
end;

create or replace package body mypkg
is
  procedure print(msg varchar2)
  is
  begin
    dbms_output.put_line(msg);
  end;
 
  function findEmp(p_empno number)
  return emp.ename%type
  is
    v_ename emp.ename%type;
  begin
    select ename into v_ename
    from emp
    where empno=p_empno;
   
    return v_ename;
  exception
    when no_data_found then
      print('未发现员工');
  end;
end;

--4.通过函数返回结果集
create or replace function findAllEmps
return SYS_REFCURSOR
is
  v_ref_cursor SYS_REFCURSOR;
begin
  open v_ref_cursor
  for 'select * from emp';
 
  return v_ref_cursor;
end;
我上学时候练习用的