扩展了标准sql
开放数据库互联ODBC OCI
SQLJ技术允许在java程序里面直接写sql语句
都能访问Oracle数据库 第一章
1. Oracle应用编程
1) Pro*C/C++: C语言中连接Oracle数据库的方式 2) ODBC: 微软公司1991年提出的链接数据库的标准 3) OCI: 也是C语言链接数据库的方式,比较底层 4) JDBC 5) SQLJ: 一种很新的链接数据库的方式,允许在应用程序中直接嵌入静态的
SQL语句 6) PL/SQL: 存储在数据库中,供外部程序使用 2. 什么是PL/SQL?
1) 标准的sql基础上增加了过程化的处理语句 2) Oracle客户端访问数据库服务器的语言 3) 扩展了标准的sql 3. PL/SQL优缺点
优点
1) 结构化模块化的编程
2) 良好的可维护性(因为其存储在数据库的内部) 数据库的内部是哪儿啊? 3) 良好的可移植性(oracle数据库支持不同的操作系统) 4) 提升系统的性能(减少了与数据库链接的次数) 缺点: 不便于向异构的数据库系统移植
第二章
1. PL/SQL的结构 Declare
//声明部分(可有可无) Begin
//主题程序 Exception //异常处理部分(可有可无) End
Set serverout on size 30000必须先执行这个
declare --dclare v_var varchar2(30); begin /* Outasdf Hello world */ v_var :=’hello world!’; dbms_output.put_line(v_var); end; /
输出一个hello world 2. 注释
--: 单行注释 /**/: 多行注释 3. 转义字符的使用
Select * from table_name where name like ‘\\_%’ escape ‘\\’;
输出一个单引号’’’’(4个单引号),输出两个单引号’’’’’’(6个单引号),oracle里也是这样的 begin dbms_output.put_line(‘’’’); end;
4. 变量的声明
Var_name [constant] type [not null] [:=value]; 1) Var_name: 变量的名字 2) Type: 变量的类型 3) [constant]: 表示是常量 4) [not null]: 表示变量一定要有值 “:=” 赋值符号 declare
v_var varchar2(30) :=’hello world’ not null default ‘world’; begin
v_var:=’helllos’;
dbms_output.put_line(v_var); end;
5) 如果是常量必须要有初始值
6) 如果变量有not null约束，也必须要有初始 7) 没有初始化的都是null
8) 在声明的时候初始化值可以使用default关键字
第三章 数据类型
标量型 数字类型 NUMBER 有效位:从左非零开始到虚拟移位的位置 刻度负数的话,补零 DEC[DECLARE] FLOAT REAL BINARY_INTEGER 字符型 VARCHAR,VARCHAR2,STRING CHAR LONG最大长度是2G BOOLEAN DATE
组合型 %type Record类型 参考型
1. %type: 表示变量与数据库中的某张表的某个列具有相同的类型 declare
v_id t_student.id%type;
v_name t_student.name%type; begin
select id,name into v_id,v_name from t_student where id=30; end; /
Show errors显示详细的错误信息 2. Record类型 declare type t_rec is record( v_id number(5); v_name t_student.name%type); v_rec t_rec; begin
select id,name into v_rec from t_student where id=30; end; /
Record类型的复制 declare type t_rec is record(
v_id number(5), v_name t_student.name%type); v_rec1 t_rec; v_rec2 t_rec;//这才是同类型 begin
select id,name into v_rec1 from t_student where id=30; v_rec2:=v_rec1; end; /
下面代码有错 declare type t_rec is record( v_id number(5), v_name t_student.name%type); type t_rec1 is record( v_id number(5), v_name t_student.name%type); v_rec1 t_rec; v_rec2 t_rec1;//不同类型 begin
select id,name into v_rec1 from t_student where id=30; v_rec2:=v_rec1; end; /
3. %rowType: 变量与数据库当中的某张表的类型一致 declare v_row t_student%rowType; begin select id,name into v_row from t_student where id=10; end;
4. Table: 很类似于java当中的数组 declare type t_tab is table of number index by binary_integer ; v_tab t_tab; begin select id into v_tab(10) from t_student where id=10; dbms_output.put_line('id='||v_tab(10)); end; /
把所有的值都拿出来怎么办? 5. 变量的作用域与可见性
1) 语句块是可以嵌套使用的
2) 变量的作用域是从声明开始到该块的结束为止
declare
v_out varchar2(30) :='out'; begin
dbms_output.put_line(v_out||'1'); declare v_in varchar2(30) :='in'; begin dbms_output.put_line(v_in||'2'); dbms_output.put_line(v_out||'3'); end;
dbms_output.put_line(v_in||'4');//访问不到 end; /
3) 内部快中可以访问外部快中声明的变量,但是外部快不能访问内部快中声明的变量 第四章 控制语句 1. 分支语句
If … then …
elsif…then … Else … end if; 问题: 统计表中的记录
1) 如果没有记录,输出”no record”
2) 如果记录数载1到50之间输出准确的记录数 3) 如果大于5就输出”more than 50” declare v_count number(5); begin select count(id) into v_count from t_student; if v_count=0 then dbms_output.put_line('no record'); elsif v_count<50 then dbms_output.put_line(v_count); else dbms_output.put_line('more than 50'); end if; end; /
2. 循环语句
1) Loop循环
declare
v_num number :=1; begin loop dbms_output.put_line(v_num); v_num :=v_num+1; if v_num>10 then exit; end if; end loop; end; /
2) While
declare
v_num number :=1; begin
while v_num<=10 loop dbms_output.put_line(v_num); v_num :=v_num+1; end loop; end; /
3) for
declare
v_num number :=1; begin
for v_num in reverse 1..50 loop dbms_output.put_line(v_num); end loop; end; / 4) goto
declare
v_num number :=1; begin loop dbms_output.put_line(v_num); v_num :=v_num+1; if v_num>10 then goto l_label; end if; end loop; < dbms_output.put_line(''); end; / 5) null语句 : 补充语句的完整性 declare v_num number :=1; begin for v_num in 1..10 loop if mod(v_num,2)=0 then null; else dbms_output.put_line(v_num); end if; end loop; end; / 注意: 只有DML语句和事务控制语句可以直接使用PL/SQL语句块中直接使用 Oracle的内置包DBMS_SQL declare v_sql varchar2(200); begin v_sql:='create table t_king(id number(5),name varchar2(30))'; execute immediate v_sql; end; / 第五章 游标 1. 游标(cursor) 作用: 提取多行的数据集 使用步骤: 1) 声明游标(指定表) cursor cur_name is select语句(select_ statement ) 2) 打开游标(执行sql语句) open cur_name; 3) 提取数据(把数据复制给内存变量) Fetch cur_name into v_var(变量); 4) 处理数据 Dbms_output.put_line(…); 5) 关闭游标释放内存 Close cur_name; 注意: 1) 游标只能打开或者关闭一次 2) 没有打开游标是不能提取数据的 3) 游标关闭了以后也不能在此提起数据 4) 参数只能制定类型varchar,不能指定长度约束.如:不可以是varchat2(30) declare --声明游标 cursor v_cur is select id,name from t_student; v_id number; v_name varchar2(30); begin --打开游标 open v_cur; --提取数据 fetch v_cur into v_id,v_name; --处理数据 dbms_output.put_line('id='||v_id); dbms_output.put_line('name='||v_name); --关闭游标 close v_cur; end; / 游标具有的属性: 1) %found: 有数据就是true,否则false 2) %notfound: 与%found相反 3) %isopen: 判断游标是否打开 4) %rowcount: 游标的指针偏移量 游标属性的使用方式: 游标的名字+游标的属性 比如:v_cur%found 联系: 结合前面所学的所有知识以及游标的属性,显示整个表中的内容(两种方式) declare cursor v_cur is select id,name from t_student; v_id number; v_name varchar2(30); begin open v_cur; if v_cur%isopen then fetch v_cur into v_id,v_name; while v_cur%found loop dbms_output.put_line('id='||v_id); dbms_output.put_line('name='||v_name); fetch v_cur into v_id,v_name; end loop; end if; close v_cur; end; / declare cursor v_cur is select id,name from t_student; v_id number; v_name varchar2(30); begin open v_cur; if v_cur%isopen then fetch v_cur into v_id,v_name; loop if v_cur%notfound then exit; else dbms_output.put_line('id='||v_id||' name='||v_name); fetch v_cur into v_id,v_name; end if; end loop; end if; close v_cur; end; / declare cursor v_cur is select id,name from t_student; v_id number; v_name varchar2(30); begin open v_cur; if v_cur%isopen then fetch v_cur into v_id,v_name; loop exit when v_cur%notfound; dbms_output.put_line('id='||v_id||' name='||v_name); fetch v_cur into v_id,v_name; end loop; end if; close v_cur; end; / 作业: 写一个pl/sql程序完成如下功能; 1) 创建两张结构完全相同的表(表的名字不同) 2) 向第一张表中插入50条记录 3) 查询第一章表中的前30条记录插入到第二张表中,在插入之前首先清空第二张表 4) 把这些记录打印到控制台上 5) 把30和50通过参数实现 6) 都是一个程序中完成 drop table t_student1,t_student2; create table t_student1(id number(5),name varchar2(30)); create table t_student2(id number(5),name varchar2(30)); declare cursor stu_cur(num number) is select * from t_student1 where id<=num; cursor stu_cur1(num number) is select * from t_student2 where id<=num; type student is record(s_id number(5),s_name varchar2(30)); stu1 t_student1%rowType; stu50 number; v_id number(5); v_name t_student2.name%type; v_sql varchar2(200); begin stu50 :=50; for i in 1..stu50 loop insert into t_student1 values(i,''||i); end loop; open stu_cur(30); delete from t_student2; fetch stu_cur into stu1; while stu_cur%found loop insert into t_student2 values(stu1.id,stu1.name); fetch stu_cur into stu1; end loop; close stu_cur; open stu_cur1(30); fetch stu_cur1 into v_id,v_name; loop exit when stu_cur1%notfound; dbms_output.put_line('id='||v_id||' name='||v_name); fetch stu_cur1 into v_id,v_name; end loop; close stu_cur1; end; / 2. For 循环游标 begin for v_row in (select id ,first_name from s_emp) loop dbms_output.put_line('id='||v_row.id); dbms_output.put_line('name='||v_row.first_name); end loop; end; 3. 异常 declare e1 exception; e2 exception; e3 exception; begin raise e1;--手动抛出异常 exception when e1 then dbms_output.put_line('exception e1'); when e2 then dbms_output.put_line('exception e2'); when others then dbms_output.put_line('exception others'); end; 第七章 子程序 匿名语句块: 没名字,不保存在数据库中 命名语句块: 有名字,可以保存在数据库中的 1. Procedure存储过程 可以看成高级语言的特殊的方法 Create [or replace] procedure proc_name [(arg_name [ { in|out\\in out}] Type, …)] {is|as} Procedure_body [or replace]: 如果存在同名的对象就替换其内容 create or replace procedure pro_king is v_var varchar2(30) :='hello world'; begin dbms_output.put_line(v_var); end; 使用存储过程,执行 begin pro_king; end; --或者exec pro_king; 三种不同模式的参数 1) In: 用来给其他变量赋值,只能在等号的右边 2) Out: 只能接受别的参数的赋值,位于等号的左边. 3) In out: in和out两个的结合 4) 不写,默认的是in Out模式 create or replace procedure pro_king3( v_in out varchar2 ) is v_var varchar2(30) :='aasdfasdfasdfadsfasdfasdf'; begin v_in :=v_var; dbms_output.put_line(v_in); end; / declare v_var varchar2(30); begin pro_king3(v_var); end; / In模式 create or replace procedure pro_king3( v_in varchar2 ) is v_var varchar2(30) :='aasdfasdfasdfadsfasdfasdf'; begin v_var :=v_in; dbms_output.put_line(v_var); end; / exec pro_king3('hello world'); in out模式的两样都行 注意: 1) Out或者是in out模式参数传值时只能是变量.不然开辟不了空间 2) 传递参数的时候既可以按照位置类标示,也可以按照名字来标示 3) 两种不同的传值方式可以混合使用 4) 两种方式混合使用的时候,第一个参数必须是按照位置标示的 5) (缺省值)声明形参的时候可以有默认值 6) 使用位置标示时,所有的缺省值都放在最后 2. FUNCTION函数 与procedure的区别: 1) 函数有返回值,存储过程没有 2) 函数是PL/SQL语句的一部分,存储过程是完整的语句. 3) Create [or replace] function func_name [(arg_name [ { in|out\\in out}] 4) Type, 5) …)] 6) Return type 7) {is|as} 8) func_body 3. 删除 Drop …. 数据字典表user_source 4. 包 1) 将一组相关的对象存放在一起的PL/SQL结构 2) 由包头部体部组成 3) 如果包的头部不没有procedure或者是function可以没有包的替补 4) 先创建包的头部,在创建包的体部 5) 包的头部包含的procedure或者是function的信息与体部一定要一致. create or replace package pac_king is v_num number(5) :=888; procedure pro_king; function fun_king return varchar2; cursor v_cur is select id,first_name from s_emp; end; / create or replace package body pac_king is procedure pro_king is v_var varchar2(30) :='helloworld'; begin dbms_output.put_line(v_var); end; function fun_king return varchar2 is v_var varchar2(30) :='helloworld'; begin return v_var; end; end; / declare v_arg varchar2(30); begin v_arg :=pac_king.fun_king; dbms_output.put_line(v_arg); dbms_output.put_line(pac_king.v_num); end; / declare v_id s_emp.id%type; v_name s_emp.first_name%type; begin open pac_king.v_cur; loop fetch pac_king.v_cur into v_id,v_name; exit when pac_king.v_cur%notfound; dbms_output.put_line('id='||v_id||' name='||v_name); end loop; close pac_king.v_cur; end; / 5. 触发器TRIGGER 与子程序的区别: 触发器是自动执行的 触发器的种类(12种): 1) 触发事件: a) Insert b) Update c) Delete 2) 触发时间: a) Before b) After 3) 触发级别 a) 行级别row b) 语句级别statement FOR EACH ROW :标示行级别,不加这句就表示语句级别 create or replace trigger trigger_king after update on t_student begin dbms_output.put_line('update one row'); end; / 问题: 触发器执行的先后顺序? 创建一个触发器,当往表中插入一条记录,就查询该表中一共有多少条记录? After是不行的 不能使用事务控制语句 调用的存储过程和函数里也不能用 create or replace trigger trigger_king after update on t_student declare num number ; begin --select count(*) into num from t_student; dbms_output.put_line('after update statement'); dbms_output.put_line(num); end; / create or replace trigger trigger_king1 after update on t_student FOR EACH ROW declare num number ; begin --select count(*) into num from t_student; dbms_output.put_line('after update row'); dbms_output.put_line(num); end; / create or replace trigger trigger_king2 before update on t_student FOR EACH ROW declare num number ; begin select count(*) into num from t_student; dbms_output.put_line('before update statement'); dbms_output.put_line(num); end; / create or replace trigger trigger_king3 before update on t_student declare num number ; begin select count(*) into num from t_student; dbms_output.put_line('before update row'); dbms_output.put_line(num); end; / create or replace trigger trigger_king4 after insert on t_student declare num number ; begin --select count(*) into num from t_student; dbms_output.put_line('after insert statement'); dbms_output.put_line(num); end; / create or replace trigger trigger_king5 after insert on t_student FOR EACH ROW declare num number ; begin --select count(*) into num from t_student; dbms_output.put_line('after insert row'); dbms_output.put_line(num); end; / create or replace trigger trigger_king6 before insert on t_student FOR EACH ROW declare num number ; begin select count(*) into num from t_student; dbms_output.put_line('bifore insert row'); dbms_output.put_line(num); end; / create or replace trigger trigger_king7 before insert on t_student declare num number ; begin select count(*) into num from t_student; dbms_output.put_line('before insert statement'); dbms_output.put_line(num); end; / create or replace trigger trigger_king8 after delete on t_student declare num number ; begin --select count(*) into num from t_student; dbms_output.put_line('after delete statement'); dbms_output.put_line(num); end; / create or replace trigger trigger_king9 after delete on t_student FOR EACH ROW declare num number ; begin --select count(*) into num from t_student; dbms_output.put_line('after delete row'); dbms_output.put_line(num); end; / create or replace trigger trigger_king10 before delete on t_student FOR EACH ROW declare num number ; begin select count(*) into num from t_student; dbms_output.put_line('before delete row'); dbms_output.put_line(num); end; / create or replace trigger trigger_king11 before delete on t_student declare num number ; begin select count(*) into num from t_student; dbms_output.put_line('before delete statement'); dbms_output.put_line(num); end; / insert into t_student values(51,'studnet51'); update t_student set name='king' where id>45; delete from t_student where id=50; 定时调用: dbms_job.submit(arg0,arg1,arg2,arg3); arg0: 定时执行任务的任务号 arg1: 定时调用要执行的操作 arg2: 定时调用从什么时间开始执行 arg3: 定时调用执行的频率(每隔多长时间执行一次) declare v_code binary_integer :=100; begin dbms_job.submit(v_code,'insert into test_date values(sysdate);',sysdate,'sysdate+1/(24*60)'); end; / 删除定时调用的 Select job,what from user_jobs; begin dbms_job.remove(23);--参数是任务号 end; / :new :old 取新值 取旧值 1. DBMS_SQL执行DDL create or replace procedure pro_king4 is v_cur binary_integer; v_sql varchar2(300); begin --打开动态的游标 v_cur :=dbms_sql.open_cursor(); --定义要执行的ddl语句 v_sql :='create table test_king1(id number(5), name varchar2(30))'; --解析动态sql语句 --参数1:游标 --单数2:要执行的sql语句 --参数3:数据库的版本,一般是dbms_sql.native dbms_sql.parse(v_cur,v_sql,dbms_sql.native); --关闭动态游标 dbms_sql.close_cursor(v_cur); end; / create or replace procedure pro_king(n number) is v_cur binary_integer; v_sql varchar2(300); v_int number:=1; begin v_cur :=dbms_sql.open_cursor(); loop if v_int-1=n then exit; end if; v_sql :='create table test_king'||n||'(id number(5), name varchar2(30))'; dbms_sql.parse(v_cur,v_sql,dbms_sql.native); v_int:=v_int+1; end loop; dbms_sql.close_cursor(v_cur); end; / 2. DBMS_SQL执行DML语句 create or replace procedure pro_king(n number) is v_cur binary_integer; v_sql varchar2(300); v_int number:=7; begin v_cur :=dbms_sql.open_cursor(); loop if v_int-7=n then exit; end if; v_sql :='create table test_king'||v_int||'(id number(5), name varchar2(30))'; dbms_sql.parse(v_cur,v_sql,dbms_sql.native); v_int:=v_int+1; end loop; dbms_sql.close_cursor(v_cur); end; / exec pro_king(3); select table_name from user_tables; 3. DBMS_SQL执行DML语句 create or replace procedure pro_king is v_cur binary_integer; v_sql varchar2(300); v_result binary_integer; v_name varchar2(30):='jd0902'; begin v_cur:=dbms_sql.open_cursor(); v_sql:='update t_student set name:=:=name where id<10'; dbms_sql.parse(v_cur,v_sql,dbms_sql.native); dbms_sql.bind_variable(v_cur,':name',v_name); v_result:=dbms_sql.execute(v_cur); dbms_output.put_line('result= '||v_result); dbms_sql.close_cursor(v_cur); end; / exec pro_king; select * from t_student; 注意使用:使用动态执行DML语句时必须直接给数据库用户授权create table权限,不能通 过角色授权 因篇幅问题不能全部显示，请点此查看更多更全内容