qlsql 学习实例代码2

--------------------------------创建过程 procedure

create or replace procedure myprocedure(id in number)  --参数的数据类型不跟字符类型的大小
is                           ---is == as
name varchar2(10);           ---过程里面声明的字符类型后面必须跟上字符类型的大小。
begin
  select ename into name from emp where empno = id;
  dbms_output.put_line(name);
 
end myprocedure;

execute myprocedure(7369);       -----命令控制台执行过程


create or replace procedure myproc2(id in number,name out varchar2) is
begin
  select ename into name from emp where empno = id;
  dbms_output.put_line(name);
end myproc2;


SQL> declare                     -----命令控制台执行过程
  2  tid number;
  3  tname varchar2(10);
  4  begin
  5  tid := 7369;
  6  myproc2(7369,tname);
  7  end;
  8  /

SMITH

PL/SQL procedure successfully completed

------------------------------------------------- 定义函数 function

create or replace function get_salary(dept_no in number,emp_count out number)
return number is
  V_sum number;
begin
  select sum(sal),count(*) into V_sum,emp_count from emp where deptno=dept_no;
  return V_sum;
exception
  when no_data_found then
  dbms_output.put_line('你需要的数据不存在');
  when others then
  dbms_output.put_line(sqlcode||'---'||sqlerrm); 
end get_salary;

SQL> declare                -----命令控制台执行定义的函数
  2  v_num number;
  3  v_sum number;
  4  begin
  5  v_sum := get_salary(10,v_num);      ---可以这样传递参数：v_sum := get_salary(dept_no => 10,emp_count => v_num);
  6  dbms_output.put_line('10号部门工资总和：'||v_sum||',people:'||v_num);
  7  end;
  8  /

10号部门工资总和：8750,people:3

PL/SQL procedure successfully completed

------------------------------------------------游标

游标是一个指向上下文的句柄（handle）或指针。

对于不同的SQL语句，游标的使用情况不同

SQL语句                            游标

非查询语句                       隐式游标
结果是单行的查询语句             隐式游标或显示游标
结果是多行的查询语句             显示游标


游标的声明部分是唯一可以出现在模块声明部分中的步骤
在声明部分的末尾声明游标



SQL> declare
  2  v_ename emp.ename%type;
  3  v_sal emp.sal%type;
  4  cursor c_cursor is select ename,sal from emp where rownum < 8;    ----定义一个游标
  5  begin
  6  open c_cursor;                         ---打开游标
  7  fetch c_cursor into v_ename,v_sal;       -----提前游标工作区域中的数据集合
  8  while c_cursor %found  loop
  9  dbms_output.put_line(v_ename||'----'||to_char(v_sal));
10  fetch c_cursor into v_ename,v_sal;
11  end loop;
12  close c_cursor;                ----关闭游标
13  end;
14  /

SMITH----800
ALLEN----1600
WARD----1250
JONES----2975
MARTIN----1250
BLAKE----2850
CLARK----2450

PL/SQL procedure successfully completed

==============
   显示游标属性                        描述

%found                 布尔型属性，当最近一次读记录时成功返回，则值为true;

%notfound              布尔型属性，与%found相反

%isopen                 布尔型属性，当游标已打开时返回true

%rowcount               数字型属性，返回已从游标中读取的记录数

===================

==============
   隐式游标属性                        描述

%found                 布尔型属性，当最近一次读记录时成功返回，则值为true;

%notfound              布尔型属性，与%found相反

%isopen                 布尔型属性，取值总是false，sql命令执行完毕立即关闭隐式游标。

%rowcount               数字型属性，返回已从游标中读取的记录数

===================

----------------------------处理隐式游标

SQL> declare
  2  v_deptno emp.deptno%type := &p_deptno;
  3  begin
  4  delete from emp where deptno = v_deptno;
  5  if sql%notfound then
  6  delete from dept where deptno=20;
  7  end if;
  8  end;
  9  /

PL/SQL procedure successfully completed            -----不要忘记commit

-----------------------------------------------------游标检索循环
SQL> declare
  2  v_empno emp.empno%type;
  3  v_sal   emp.sal%type;
  4  cursor c_cursor is select empno,sal from emp;
  5  begin
  6  open c_cursor;
  7  loop
  8    fetch c_cursor into v_empno,v_sal;
  9    exit when c_cursor %notfound;
10    if v_sal <= 1000 then
11       update emp set sal = sal +50 where empno = v_empno;
12       dbms_output.put_line('编码为'||v_empno||'工资已更新');
13     end if;
14     dbms_output.put_line('记录数为'||c_cursor %rowcount);
15  end loop;
16  close c_cursor;
17  end;
18  /

编码为7369工资已更新
记录数为1
记录数为2
记录数为3
记录数为4
记录数为5
记录数为6
记录数为7
记录数为8
记录数为9
记录数为10
记录数为11
编码为7900工资已更新
记录数为12
记录数为13
记录数为14

PL/SQL procedure successfully completed
--------------------------------------------------------------游标的for循环

SQL> declare
  2  cursor c_sal is select empno,ename,sal from emp;
  3  begin
        --隐含打开游标
  4     for v_sal in c_sal loop
        --隐含执行一个fetch语句
  5     dbms_output.put_line(to_char(v_sal.empno)||'---'||v_sal.ename||'---'||to_char(v_sal.sal));
        --隐含监测c_sal%notfound
  6     end loop;
        --隐含关门游标
  7  end;
  8  /

7369---SMITH---850
7499---ALLEN---1600
7521---WARD---1250
7566---JONES---2975
7654---MARTIN---1250
7698---BLAKE---2850
7782---CLARK---2450
7788---SCOTT---3000
7839---KING---5000
7844---TURNER---1500
7876---ADAMS---1100
7900---JAMES---1000
7902---FORD---3000
7934---MILLER---1300

PL/SQL procedure successfully completed

---------------------------------------------------------带参数的游标的for循环

SQL> declare
  2  cursor c_cursor(dept_no number default 10)is
  3  select dname,loc from dept where deptno <= dept_no;
  4  begin
  5  dbms_output.put_line('dept_no 参数值为30:');
  6  for c1_rec in c_cursor(30) loop
  7    dbms_output.put_line(c1_rec.dname||'---'||c1_rec.loc);
  8  end loop;
  9  dbms_output.put_line(chr(10)||'dept_no 参数值为10:');
10  for c1_rec in c_cursor(10) loop
11    dbms_output.put_line(c1_rec.dname||'---'||c1_rec.loc);
12  end loop;
13  end;
14  /

dept_no 参数值为30:
ACCOUNTING---NEW YORK
RESEARCH---DALLAS
SALES---CHICAGO

dept_no 参数值为10:
ACCOUNTING---NEW YORK

PL/SQL procedure successfully completed
-------------------------------------------------------for update 游标锁定
SQL> declare
  2  v_deptno emp.deptno%type :=&p_deptno;
  3  cursor emp_cursor is
  4  select empno,sal from emp where deptno = v_deptno FOR UPDATE NOWAIT;   --当加上NOWAIT子句时，
                                                                            --如果这些行真的被另一个会话锁定，
                                                                            --则open立即返回并给出错误提示：
                                                                            --ORA-0054:resource busy and acquire with nowait specified
  5  begin
  6    for emp_rec in emp_cursor loop
  7       if emp_rec.sal < 1500 then
  8         update emp set sal=1500 where current of emp_cursor;
  9       end if;
10    end loop;
11  end;
12  /

PL/SQL procedure successfully completed
--------------------------------------------------------游标变量的应用    游标变量类型无返回值

SQL> declare
  2  type emp_cursor_type is ref cursor;          ---定义游标变量类型的名称
  3  emp_cursor emp_cursor_type;                  ---声明一个变量为游标变量类型（无返回值）
  4  emp_record emp%rowtype;                      ---定义一个与表字段 字段类型相同的一个记录 
  5  begin
  6    open emp_cursor for select * from emp where deptno = 10;    ---打开游标变量 open ... for
  7    loop
  8      fetch emp_cursor into emp_record;
  9      exit when emp_cursor%notfound;
10      dbms_output.put_line('第'||emp_cursor%rowcount||'个雇员:'||emp_record.ename);
11    end loop;
12  close emp_cursor;
13  end;
14  /

第1个雇员:CLARK
第2个雇员:KING
第3个雇员:MILLER

PL/SQL procedure successfully completed

--------------------------------------------------------游标变量的应用    游标变量类型有返回值
SQL> declare
  2  type emp_record_type is record(
  3     name varchar2(10),salary number(6,2));
  4  type emp_cursor_type is ref cursor return emp_record_type;         -- 有返回值的游标变量类型  那么select语句的返回结果必须与return的结果类型想匹配。
  5  emp_cursor emp_cursor_type;
  6  emp_record emp_record_type;
  7  begin
  8  open emp_cursor for select ename,sal from emp where deptno = 20;
  9  loop
10    fetch emp_cursor into emp_record;
11    exit when emp_cursor%notfound;
12      dbms_output.put_line('第'||emp_cursor%rowcount||'个雇员:'||emp_record.name);
13  end loop;
14  close emp_cursor;
15  end;
16  /

第1个雇员:SMITH
第2个雇员:JONES
第3个雇员:SCOTT
第4个雇员:ADAMS
第5个雇员:FORD

PL/SQL procedure successfully completed
-------------------------------------------------------------

==========================================包定义
create or replace package demo_pack is

  deptrec dept%rowtype;
 
  function add_dept(
     dept_no number,dept_name varchar2,location varchar2)
     return number;
  function remove_dept(dept_no number)
     return number;
  procedure query_dept(dept_no in number);     
end demo_pack;

=============================================  包主体  实行function和procedure
create or replace package body demo_pack is

  function add_dept(
     dept_no number,dept_name varchar2,location varchar2)
     return number 
   is
     empno_remaining exception;                 -----自定义一个异常
     pragma exception_init(empno_remaining,-1);
    begin
      insert into dept values(dept_no,dept_name,location);
      if sql%found then
      return 1;
      end if;
    exception
       when empno_remaining then
       return 0;
       when others then
       return -1;
     end add_dept;
     function remove_dept(dept_no number)
        return number
     is
     begin
        delete from dept where deptno = dept_no;
        if sql%found then
        return 1;
        else
        return 0;
        end if;
     exception
        when others then
        return -1;
     end remove_dept;
     procedure query_dept(dept_no in number)
     is
     begin
       select * into deptrec from dept where deptno = dept_no;
     exception
       when no_data_found then
       dbms_output.put_line('部门:'||dept_no||'不存在');
       when too_many_rows then
       dbms_output.put_line('程序运行错误，请使用游标');
       when others then
       dbms_output.put_line(sqlcode||'---'||sqlerrm);
     end query_dept;                  

end demo_pack;

======================================================命令窗口操作包及里面的function和procedure
SQL> declare
  2  var number;
  3  begin
  4  var := demo_pack.add_dept(90,'administration','beijing');
  5  if var = -1 then
  6    dbms_output.put_line(sqlcode||'---'||sqlerrm);
  7  elsif var = 0 then
  8    dbms_output.put_line('该部门记录已经存在');
  9  else
10    dbms_output.put_line('添加记录成功');
11    demo_pack.query_dept(90);
12    dbms_output.put_line(demo_pack.deptrec.deptno||'--'||demo_pack.deptrec.dname||'--'||demo_pack.deptrec.loc);
13    var := demo_pack.remove_dept(90);
14    if var = -1 then
15    dbms_output.put_line(sqlcode||'---'||sqlerrm);
16    elsif var = 0 then
17    dbms_output.put_line('该部门记录不存在');
18    else
19    dbms_output.put_line('删除记录成功');
20    end if;
21  end if;
22  end;
23  /

添加记录成功
90--administration--beijing
删除记录成功

PL/SQL procedure successfully completed