Oracle笔记(四)

最新推荐文章于 2024-07-18 16:32:46 发布

weixin_33889665

最新推荐文章于 2024-07-18 16:32:46 发布

阅读量68

点赞数

文章标签：数据库

原文链接：http://blog.51cto.com/globle/642466

版权

六、循环语句：
1. 基本循环：
LOOP
执行语句；
EXIT WHEN 条件成立；
END LOOP;

例：
Declare
         v_deptno emp.deptno%type:=&deptno;
         i number(2):=0;
     Begin
         loop
           i := i +1;
           insert into emp(empno,hiredate,deptno)
           values(i+7200,sysdate,v_deptno);
           dbms_output.put_line('i的当前值为:'||i);
          exit when i=10;
          end loop;
    End;

2. WHILE循环:

WHILE 条件成立
LOOP 执行语句；
END LOOP;

Declare
         v_deptno emp.deptno%type:=&deptno;
         i number(2):=0;
     Begin
         while i < 10   loop
           i := i +1;
           insert into emp(empno,hiredate,deptno)
           values(i+7200,sysdate,v_deptno);
           dbms_output.put_line('i的当前值为:'||i);
       --   exit when i=10;
          end loop;
    End;

3. FOR循环:
FOR 计数器 IN 低界..高界
LOOP 执行语句;
END LOOP;

例：
Declare
         v_deptno emp.deptno%type:=&deptno;
         i number(2):=0;
     Begin
         while i < 10   loop
           i := i +1;
           insert into emp(empno,hiredate,deptno)
           values(i+7200,sysdate,v_deptno);
           dbms_output.put_line('i的当前值为:'||i);
       --   exit when i=10;
          end loop;
    End;

七、光标设计(Cursor):

1、什么是光标？在PL/SQL中，当查询语句执行结果超过一行时，为处理每一行，必须定义一个cursor，叫光标。

2、光标使用方法：
(1) 定义光标：
语法：光标名 is select 语句;

(2) 打开光标：
语法：open 光标名;

(3) 取数据：
语法：Fetch 光标名 into 变量;

(4) 光标下移：使用loop循环

(5) 关闭光标：close 光标名;

例：
Declare
        v1 emp.empno%type;
        v2 emp.ename%type;
        v3 emp.sal%type;
        cursor c is select empno,ename,sal from emp;
     Begin
         open c;
         loop
         fetch c into v1,v2,v3;
         if v3<3000 then Begin
                           update emp set sal=sal+100
                           where empno=v1;
                    dbms_output.put_line('员工'||v2||'工资已经更新!');
                                    End;
         end if;
         exit when c%NOTFOUND;
         end loop;
         close c;
     End;
/

-- //要求从emp表中取出按用户输入要求的前几位工资最高的人员及其工资放入topsalary表中：
-- // 我的方法：
SQL>create table topsalary (
name varchar2(20),
sal number(7,2));

SQL>Declare
vcount number(7) := &n;
i number(7) :=0;
        vname emp.ename%type;
        vsal emp.sal%type;
        cursor c is select ename,sal from emp order by nvl(sal,0) desc; --// 使用nvl函数防止工资出现空值的情况，老师补充
     Begin
      delete topsalary; -- // 先清空topsalary表
         open c;

         for i in 1 .. vcount loop
         fetch c into vname, vsal;

         insert into topsalary values(vname,vsal);

         end loop;
         close c;
     End;

--//教师的方法
Declare
    i number(3) :=&i;
    j number(3) :=0;
   cursor c is select ename,sal from emp order by nvl(sal,0) desc;
   v1 emp.ename%type;
   v2 emp.sal%type;
Begin
   open c;
   loop
   j := j+1;
   fetch c into v1,v2;
   insert into topsalary values(v1,v2);
   exit when j=i;
   end loop;
   close c;
End;

3. 光标属性: 每一个光标有四种属性
%FOUND         查询语句(FETCH语句)返回记录
%NOTFOUND      查询语句(FETCH语句)无返回记录，用于循环退出条件
%ROWCOUNT     FETCH已获取的记录数
%ISOPEN        光标已打开标记

隐式光标属性: SQL%FOUND SQL%NOTFOUND SQL%ROWCOUNT

隐式光标的使用:
Declare
v_empno emp.empno%type:=&empno;
Begin
delete from emp where empno=v_empno;
if SQL%NOTFOUND then
dbms_output.put_line('你的删除失败,数据库无此人:'||v_empno);
end if;
End;

[例外处理Exception]
Declare
       v_empno emp.empno%type :=&empno;
       v_ename emp.ename%type;
       v_sal emp.sal%type;
Begin
      select sal,ename into v_sal,v_ename from emp
      where empno=v_empno;
       if v_sal<2000 then Begin
                  update emp set sal=sal+100
                  where empno=v_empno;
        dbms_output.put_line('员工'||v_ename||'工资已经修改!');
                                       End;
        elsif v_sal<2500 then Begin
                  update emp set sal=sal+50
                  where empno=v_empno;
        dbms_output.put_line('员工'||v_ename||'工资已经修改!');
                                       End;
        elsif v_sal<3000 then Begin
                  update emp set sal=sal+10
                  where empno=v_empno;
        dbms_output.put_line('员工'||v_ename||'工资已经修改!');
                                       End;
        else dbms_output.put_line('员工'||v_ename||'的工资已经超过规定值,不予更新!');
        End if;
Exception   -- // 例外处理
when NO_DATA_FOUND then dbms_output.put_line('数据库中没有编码为'||v_empno||'的员工。');
when TOO_MANY_ROWS then
dbms_output.put_line('你的查询语句返回结果出现多行，请定义光标后重试！');
when OTHERS then
dbms_output.put_line('你的程序是错误的，请仔细检查后重试！');
End;

[用户定义的例外]
(1)在Declare段定义
(2)在Begin段中用Raise引起。
(3)在Exception段中使用。

Declare
       v_empno emp.empno%type :=&empno;
       no_result exception;
Begin
delete from emp where empno = v_empno;
if SQL%NOTFOUND then raise no_result;
end if;

Exception   -- // 例外处理
when NO_DATA_FOUND then dbms_output.put_line('数据库中没有编码为'||v_empno||'的员工。');
when NO_RESULT then dbms_output.put_line('数据库中没有编码为'||v_empno||'的员工。');
when TOO_MANY_ROWS then
dbms_output.put_line('你的查询语句返回结果出现多行，请定义光标后重试！');
when OTHERS then
dbms_output.put_line('你的程序是错误的，请仔细检查后重试！');
End;

[存储过程(Storage Procedure)与函数(Function)设计]

一、什么是存储过程？
存储过程（函数）是把一个PL/SQL块存储到数据库中，作为一个数据库实体，可以在其它存储过程、函数、应用程序中调用。
1、存储过程的调用方法：
(1) 在SQL*Plus中调用方法：SQL> Execute 存储过程名称; // execute 可以简写为Exec。
(2) 在其它存储过程、函数、应用程序中调用方法：存储过程名称;

2、存储过程的设计方法：
编写程序->在SQL*PLUS中编译->修改错误->调用执行。

二、创建存储过程的语法：[参考PowerPoint教程:存储过程1.ppt(P3)]
说明：
IN: 调用者向过程传递参数
OUT: 过程向调用者传递参数
IN OUT: 双向传递参数
无Declare

[例1：]IN: 删除数据
Create or Replace procedure DelEmp(v_empno in emp.empno%type) is
Begin
delete from emp where empno=v_empno;
dbms_output.put_line('编码为'||v_empno||'的员工已被除名！');
End DelEmp;

调用方法：
SQL> Exec DelEmp(7788);
SQL> Exec DelEmp(7934);

[例2：]IN: 插入数据
Create or Replace procedure InsertEmp( v_empno in emp.empno%type,
v_ename in emp.ename%type,
v_deptno in emp.deptno%type) is
Begin
insert into emp(empno, ename, hiredate,deptno)
values(v_empno, v_ename, sysdate, v_deptno);
dbms_output.put_line('新员工“'||v_ename||'”录入成功！');
End InsertEmp;

[例3：]IN、OUT:数据查询
Create or Replace procedure QueryEmp(v_empno in emp.empno%type,
v_ename out emp.ename%type,
v_job out emp.job%type) is
Begin
select ename,job into v_ename,v_job from emp
where empno=v_empno;
End QueryEmp;

调用方法：
Declare
v1 emp.ename%type;
v2 emp.job%type;
   v emp.empno%type:=&empno;
Begin
    QueryEmp(v,v1,v2);
   dbms_output.put_line('编码为'||v||'的员工姓名是：'||v1||'职业是：'||v2);
End;

[* 查询存储过程源代码：]
SQL> select text from user_source where name = 'DELEMP'; // 名字要大写

三、创建函数语法：
例1：
Create or replace Function GetSalary( v empno%type) return numbwr is
v_sal emp.sal%type;
Begin
select sal into v_sal from emp
where empno=v;
return v_sal;
End GetSalary;

[调用方法：]
(1) SQL> Exec dbms_output.put_line(GetSalary(7788));
(2) SQL> select GetSalary(7788) from dual;
(3) 使用PL/SQL的方法：
Begin
dbms_output.put_line(GetSalary(7788));
End;

[函数例2]
Create or replace function getmaxsal
return number
is
v_maxsal emp.sal%type;
begin
select max(sal) into v_maxsal from emp;
return v_maxsal;
end getmaxsal;

[调用方法：]
(1) SQL> Exec dbms_output.put_line(GetMaxSal);
(2) SQL> select GetMaxSal from dual;
(3) 使用PL/SQL的方法：
Begin
dbms_output.put_line(GetMaxSal);
End;

[练习题]: 求n!函数（n的阶乘)。

[我的答案]
Create or replace function nj(n in number)
return number
is
   numtmp number;
Begin
   if n = 0 then
    numtmp := 1;
   else
    numtmp := n * nj(n - 1);
   end if;

   return numtmp;
End nj;

[教师的答案]// 高，　实在是高！！！
Create or replace function fn(n in number)
return number
is
Begin
if n=0 then return 1;
else return n*fn(n-1);
end if;
End fn;

四、存储过程及函数管理：
1、查询存储过程及函数的源代码：
SQL> select * from user_source;
SQL> select * from dba_source;
SQL> select * form all_source;

2、存储过程及函数的权限管理：
授权命令：
SQL> grant execute on 过程（或函数） to 用户名;
SQL> grant execute on 过程（或函数） to public; --//授权给全体用户

权限回收：
SQL> Revoke execute on 过程（或函数） from 用户名;
SQL> Revoke execute on 过程（或函数） from public;

3、查询错误信息：
SQL> select * from user_errors;
SQL> show errors;

4、查询依赖性信息：
依赖性：实体的结构定义修改时，对创建在这些实体上的存储过程及函数的影响叫依赖性。

显示依赖性关系：
SQL> select name,type, referenced_owner, referenced_name, referenced_type from user_dependencies;

5、删除存储过程及函数：
SQL> drop procedure 过程名;
SQL> drop function 函数名;

转载于:https://blog.51cto.com/globle/642466

weixin_33889665

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle笔记(四)

六、循环语句：1. 基本循环：LOOP 执行语句；EXIT WHEN 条件成立；END LOOP;例：Declare v_deptno emp.deptno%type:=&deptno; i number(2):=0; Begin loop...
复制链接

扫一扫