declare
定义变量
begin
具体操作
exception
异常处理
end ;
结束
----------------------------------------------
程序变量 V_name
程序常量 C_name
游标变量 name_
异常标识 E_too_many
表类型 Emp_record_type
表 Name_table
记录类型 Name_record
----------------------------------------------
declare
V_num number;
V_str varchar2(20);
V_student hand_student%rowtype;
V_student_name hand_student.student_name%type;
begin
select * into V_student
from hand_student
where hand_student.student_no = 's010';
dbms_output.put_line('');
dbms_output.put('');
end ;
-------------------控制语句---------------------------
if then
else then
end if;
'elsif then '
-----------------------
and true false null
true true false null
false false false false
null null false null
-----------------------------
or true false null
true true true true
false true false null
null true null null
-------------------------------
not
true false
false true
null null
-----------------------------
case
when 1 then ;
when 2 then ;
else 3 ;
end case;
--------------循环结构-----------------------------------
for j in 1..10 loop
***
end loop;
---------------------
loop
exit when ;
end loop;
-------------------------
while loop
end loop;
--------游标-----------------
%found 当最近一次读入记录成功时返回true
%notfound 同上 相反
%isopen 判断游标是否已经打开
%rowcount 返回已从游标中读取的记录数
使用游标分为四个步骤
1 声明游标cursor 游标变量is 查询语句
2 打开游标open游标变量
3 提取数据fetch游标变量 into 变量1 ，变量2
4 关闭游标释放系统资源close 游标变量
declare
cursor My_cursor is select * from ***;
begin
open My_cursor ;
fetch My_cursor into **;
close My_cursor;
end;
for c in My_cursor loop
end loop;
隐式游标
写一段sql代码
sql指代写的sql代码
if sql%notfound then
end if;
for update of (salary) nowait;//锁定对象，不允许被修改
--------复杂自定义数据类型------------------------
type emp_record_type is record(
last_name varchar2(25),
job_id varchar2(10),
salary number(8,2)
);
emp_record emp_record_type;
类似于c中的结构体
-----------------------------------
嵌套使用
begin
<<outer>>
declare
birthdate date;
begin
declare
birthdate date;
begin
outer.date:=to_date('***');
end;
end;
end;
-------------------------------------------
异常错误处理
1 预定义错误 24个
2 非预定义错误
3 用户定义错误
exception
when too_many_rows then *** ;
when no_data_found then *** ;
when others then *** ;
declare
e_too_high_sal exception;
begin
raise e_too_high_sal ; 抛出异常
exceptiom
when e_too_high_sal then
定义异常
pragma exception_init(temp_exception,-2292);
exception
when temp_exception then ***;
-----------存储函数---------------------
create or replace function f_name(id number,salary number)
return return_type;
is
--定义变量
begin
exception
end;
有返回值
-----------存储过程-------------------------
没有返回值
create or replace procedure query_emp(
p_id in ** default **;
p_age in out **;
p_name out **;
)
=>传递
---------------------------------------------------
动态sql
execute immediate 'delete from'||p_table_name;
练习
------------------------------------------------
'定义V_sal变量为number形态且不可为空值'
V_sal number not null :=10;
'同上例V_sal默认值为100'
V_sal number not null default 100;
'定义变量V_tax为常数值(34.5)'
V_tax constant number :=34.5
'定义变量v_hiredate继承原emp表格中hiredate字段数据型态'
V_hiredate emp.hiredate%type;
'同上例，定义一变量v_hiredate2继承v_hiredate变量数据型态'
V_hiredate2 V_hiredate%type;
'声明dept_rec_type Record数据型态，包含deptno number及dname varchar2(40)'
type dept_rec_type is record(
deptno number ;
dname varchar2(40);
)
' 由上例数据型态，定义Record变量dept_rec'
dept_rec dept_rec_type;
--------------------------------------------
bulk collect
select empno,ename,hiredate
bulk collect into emp_tab
from emp;
--使用bulk collect 将所得的结果的结果集一次性绑定到记录变量emp_tab中
2. 编写一个存储过程(PROCEDURE)， 输入参数为部门ID,输出参数：部门人数、最高工资、最低工资（工资来源为employees表）
create or replace procedure my_pro(
p_dep_id number,
p_sum out number,
p_max_sal number,
p_min_sal number
)
is
begin
select count(*),max(e.salary),min(e.salary) into p_sum,p_max_sal,p_min_sal from employees e where department_id = p_dep_id;
end;
-----------------------------------------------------------------------------
declare
e_low_salary exception;
v_last_name varchar2(20);
v_years number;
v_sal number;
begin
for c_emp in(
select last_name,
round(months_between(SYSDATE, hire_date) / 12, 0) as years,
salary
from employees) loop
v_last_name := c_emp.last_name;
v_years := c_emp.years;
v_sal := c_emp.salary;
if v_years > 10 and v_sal < 3500 then
--抛出例外之后，for就终止了;
begin
raise e_low_salary;
exception
when e_low_salary then
insert into analysis(
ename,
years,
sal)
values(v_last_name, v_years, v_sal);
end;
else
dbms_output.put_line('not due a raise!');
end if;
end loop;
end;
/
----------------------------------------------------------------------------
3. 在2完成的基础上，写一个Pkg，pkg 命名CUX_STUDY_工号,写一个游标，处理所有的部门信息，如果该部门无员工，则抛出例外，在例外里面插入一条记录到EMPLOYEES表；EMPLOYEE_ID自动增长、其他字段自由发挥；如果存在员工，则将信息通过DBMS_OUTPUT输出，输出格式自由发挥
create or replace package cux_study_20786 is
procedure my_pro;
end cux_study_20786;
/
create or replace package body cux_study_20786 is
procedure dep_find_emp(p_dep_id number)
is
cursor my_cur is select last_name from employees where department_id = p_dep_id;
v_name employees.last_name%type;
begin
open my_cur;
fetch my_cur into v_name;
while my_cur%found loop
dbms_output.put_line(p_dep_id||' '||v_name);
fetch my_cur into v_name;
end loop;
exception when no_data_found then dbms_output.put_line(p_dep_id||'部门没有员工');
when dep_no_emp then dbms_output.put_line(p_dep_id||'部门没有员工');
end;
procedure my_pro is
cursor my_cur is select department_id from departments order by department_id desc;
v_dep_id departments.department_id%type;
begin
open my_cur;
fetch my_cur into v_dep_id;
while my_cur%found loop
dep_find_emp(v_dep_id);
fetch my_cur into v_dep_id;
end loop;
end;
end cux_study_20786;
/----------------表记录类型--------------------
写一个匿名块，查询employees 表中数据，并通过dbsm_output输出；
要求使用table表变量进行实现；可百度（bulk collect into ）
declare
type t_rd is record (last_name varchar2(10));
type t_tb is table of t_rd index by binary_integer;
v_tb t_tb;
begin
select e.last_name
bulk collect into v_tb
from employees e
where e.department_id = 50;
for i in v_tb.first..v_tb.last loop
dbms_output.put_line(v_tb(i).last_name);
end loop;
end;
-------------------------------------------
基本数据类型
扫一扫
971
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
