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