oracle_note

======================================day01=================================

desc 表名  —>查看表结构

edit 编辑上一次sql语句

set linesize 1000 /

设置sql在终端的页面显示。

set pagesize 1000 /

select sysdate from dual;显示系统日期。

rollback  回滚

 

create table emp_zsl(

eld number(3),

name varchar2(10),

age number(3),

addr varchar2(10)

);

insert into emp_zsl values(101,'zhang',12,'bj');

insert into emp_zsl values(102,'de',22,'wh');

insert into emp_zsl values(103,'kuai',33,'sh');

insert into emp_zsl values(104,'zhou',16,'bj');

insert into emp_zsl values(105,'shu',28,'sh');

insert into emp_zsl values(106,'long',32,'wh');

 

select * from emp_zsl;

--distinct去除重复记录

select name||'is'||age from emp_zsl;

--|| 连接符号

insert into emp_zsl(eld,name,age) values(134,'aaaa',23);

--null 缺少参数不能插入

insert into emp_zsl(eld,name,age,addr) values(134,'aaaa',23,null);

select eld,name,age,nvl(addr,'bj') from emp_zsl;

--nvl() addr字段中的空字符串被bj替换。

----------------------------------where

select * from emp_zsl where age>12;

select eld,name,age from emp_zsl where age beteen 12 and 22;   /

查找年龄在1222之间的表数据。(查询结果包含1222)----------------

select * from emp_zsl where age>=12 and age<=22;               /

 

or:查找addr等于        等价于in

select * from emp_zsl where addr='asd' or addr='bj';

select * from emp_zsl where addr in('asd','bj');

 

like:模糊查询

select * from emp_zsl where name like'a%';查询name中包含a开头的名字记录 

select * from emp_zsl where name like'_a%';查询name中开头第2个字母为a的名字记录 

select * from emp_zsl where name like'%a%';查询name中包含a的名字记录 

特殊案例:

insert into emp_zsl values(125,'a_b',23,'bj%s');

select * from emp_zsl where name like'%_%';返回结果全部显示出来,出错。

 

select * from emp_zsl where name like'%/_%';

添加了转义字符‘/’,结果就不一样了。

 

null:与空值的比较。

select * from emp_zsl where addr is null;查询地址为空的记录

select * from emp_zsl where addr is not null;查询地址记录不为空的

 

--------------------order by  排序(默认升序esc,降序desc

 

select * from emp_zsl where addr='asd' order by name;查询addr等于asd的记录按名字顺序排列

select * from emp_zsl where addr='asd' order by name,age;同上先按名字排序,再按年龄排序。

 

-----------------------------函数function

1.characler

select * from emp_zsl where lower(addr)='bj';查询字段addr字段转化成小写为‘bj'的记录

select upper(name) from emp_zsl;  name转大写

select initcap(name) from emp_zsl;  name首字母转大写。

 

select concat(name) from emp_zsl;       连接

select substr(name,1,2) from emp_zsl ;   截取

select length(name) from emp_zsl ;    长度

 

2number

trunc()    截取

raund() 45

 

3to_char

insert into emp_zsl values(156,'aa','ab','sh');默认转化‘ab’

select to_char(1234,567,'$999,999,999') from emp_zsl; 输出格式$1234567

select to_char(1234,567,'$000,000,000') from emp_zsl; 输出格式$001234567

数字转字符串,参数2表格转化后的格式,观察 和 的格式区别。

select to_number('12') from emp_zsl;

字符串转化成数字

 

 

---------------------------join  多表的连接查询

create table student_zsl(s_id number(3),s_name varchar2(10),age number(3));

insert into student_zsl values(101,'zhou',18);

insert into student_zsl values(102,'wang',19);

insert into student_zsl values(103,'zhang',17);

 

create table lesson_zsl(l_id number(3),l_name varchar2(10));

insert into lesson_zsl values(1,'yuwen');

insert into lesson_zsl values(2,'shuxu');

 

create table sl_zsl(l_id number(3),s_id number(3),score number(3));

insert into sl_zsl values(1,101,98);

insert into sl_zsl values(1,102,88);

insert into sl_zsl values(2,101,78);

 

 

--查询出参加了考试的人的姓名和考试成绩 --

select a.s_name,b.score from student_zsl a,sl_zsl b

where a.s_id=b.s_id;

 

--查询参加了考试的人的姓名  , 考试成绩以及所考试课程名称 --

select a.s_name,b.score,c.l_name from student_zsl a,sl_zsl b,lengso_zsl c

where a.s_id=b.s_id  and  b.l_id=c.l_id;

 

--不等值连接:

create table greade_zsl(lowscore number(3),highscore number(3),grade varchar2(2));

insert into greade_zsl values(61,70,'D');

insert into greade_zsl values(71,80,'C');

insert into greade_zsl values(81,90,'B');

insert into greade_zsl values(91,100,'A');

--查询出产假了考试的学生的名称,课程名称以及成绩等级--

select a.s_name,b.l_name,d.grade from student_zsl a,lesson_zsl b,sl_zsl c,greade_zsl d

where a.s_id=c.s_id and c.l_id=b.l_id and c.score between d.lowscore and d.highscore;

 

 

--自连接--

select a.eld,a.name from emp_zsl a,emp_zsl b

where a.addr=b.addr and b.name='zhang' and a.eld=b.eld;

 

--外连接--

查询出所有学生的名称,考试成绩

方法1

select s_name,score from student_zsl a,sl_zsl b

where a.s_id=b.s_id(+);右没有记录补空值--左连接

方法2

select s_name,score from student_zsl a join sl_zsl b on a.s_id=b.s_id;效果同上

select s_name,score from student_zsl a join sl_zsl b on a.s_id=b.s_id(+);加号在这里也可以用。

select s_name,score from student_zsl a left join sl_zsl b on a.s_id=b.s_id;左外连接

 

查询参加了考试的人的姓名,考试成绩以及所考试课程名称

select s_name,score,l_name from 

student_zsl a join sl_zsl b on a.s_id=b.s_id

join lesson_zsl c on c.l_id=b.l_id; 

 

左连接以左表为基础,显示又表,没有多应的数据补null

 

--------------------------------group by分组

 

select count(*) from emp_zsl;计数(括号中用字段,统计该字段非空的记录的数量)

select sum(age) from emp_zsl;

select avg(age) from emp_zsl;

select max(age) from emp_zsl;

 

-- 查询出每个地址的人数

select addr,count(*) from emp_zsl

group by addr;

 

--查询出每个年龄段的人数

select (trunc(age/10)*10)||'-'||(trunc(age/10)*10+9),count(*) from emp_zsl

group by trunc(age/10);

 

===================day02==================

 

----------trunc截取

select count(*) from emp_zsl 

where addr='bj'

group by trunc(age/10)

order by count(*) desc;

查询地址在bj的所有人数,按年纪来分组并从大到小排序。

------

找到年龄最大的人:

select eld,name from emp_zsl 

where age=(select max(age) from emp_zsl);

 

create table test_zsl(

s_name varchar2(10),l_name varchar2(10),score number(3)

);

insert into test_zsl values('zhang','shuxu',80);

insert into test_zsl values('de','shuxu',90);

insert into test_zsl values('kuai','shuxu',50);

insert into test_zsl values('zhou','shuxu',60);

insert into test_zsl values('shu','yuwen',30);

insert into test_zsl values('long','yuwen',100);

查询成绩在80分以上的名字:

select s_name from test_zsl 

where s_name not in(select s_name from test_zsl where score<80);

 

去除重复记录(名字相同就认为是重复的):

delete from emp_zsl 

where eld not in(select  max(eld) from emp_zsl group by name);

特殊案例(2条记录完全一样的时候该如何去重):

delete from emp_zsl 

where rowid not in(select  max(rowid) from emp_zsl group by name,eld,age,addr);

rowid(记录在硬盘中存放的位置,记录一定是连续存放的)

rownum(显示行号,不是连续存放的)

 

查找重第3条到第6条之间的记录:rownum<=6(显示前6条记录,rownum也可以放在select后面使用)

方法1

select eld,name,age from 

(select rownum a,eld,name,age,addr emp_zsl from emp_zsl) 

where a<=6 and a>=3;

方法2:效率比方法1要高一些

select eld,name,age from 

(select rownum a,eld,name,age,addr emp_zsl from emp_zsl where rownum<=6) 

where a>=3;

 

按照模个字段排序后,查询第3到第6条:

select eld,name,age from 

(select rownum a,eld,name,age,addr emp_zsl from

(select * from emp_zsl order by name)

emp_zsl where rownum<=6) 

where a>=3;

 

----------------------------------------------ER

从表:

create table pesson_zsl(

pid number(3),

name varchar2(10),

addrid number(3) unique,------外建唯一约束

constraints paf_zsl foreign key(addrid) references address_zsl(aid)-----定义外建

);

insert into pesson_zsl values(101,'aa',1);

 

主表:

create table address_zsl(

aid number(3) primary key,-------定义主键

city varchar2(30),

code number(3)

);

insert into address_zsl values(1,'bj',101);

建表要先建主表,再建从表。

插入数据也是先插主表,再插从表。

删除数据要先从再主。

one 对  many: 

create table stu_zsl(

sid number(3) primary key,

name varchar2(10),

cid number(3),

constraints paf1_zsl foreign key(cid) references class_zsl(id)

);

主表:

create table class_zsl(

id number(3) primary key,

name varchar2(30)

);

 

mary 对 mary

create table less_zsl(

lid number(3) primary key,

name varchar2(10)

);

主表:

create table link_zsl(

studentid number(3),

lessonid number(3),

score number(3),

constraints linkpk_zsl prinary key(studentid,lessonid),

constraints linkfk_zsl foreign key(studentid) references stu_zsl(sid),

constraints linkfk1_zsl foreign key(lessonid) references less_zsl(lid)

);------关联表,必须在主表和从表都创建了才能创建

create table default_zsl(

id number(3) primary key,

name varchar2(10) default 'abc'

);---------------------------------没有插入name数据的时候该属性默认为abc

insert into default_zsl(id) values(101);

 

 

数据类型: 

字符串:    char(定长,最大2000)   varchar(不定长,是标准的任何数据库都可以用)    carchar2(不定长,是oracle自己的类型,最大4000

  char效率最高(浪费了空间提高了时间)

create table chartest_zsl(

a char(5),

b varchar(5),

c varchar2(5)

);

insert into chartest_zsl values('aaa','aaa','aaa');

select length(a),length(b),length(c) from chartest_zsl;

 

数字类型: 

create table numbertest_zsl(

a number(3),----------只能存放整数,小数点后面的四舍五入

b number(5,3)---------有效数字不能超过5位,小数部分不能超过3位,并且整数部位不能超过53位。

);

insert into numbertest_zsl(a) values(12.5);

 

日期类型:

create table datetest_zsl(

id number(3) primary key,

name varchar(10),

bith date

);

insert into datetest_zsl values(1,'aaa',sysdate);

insert into datetest_zsl values(2,'bbb','05-MAY-09');

 

to_data指定格式转换为日期型:

insert into datetest_zsl values(3,'ccc',to_date('MAY-09-01','MM-YYYY-DD'));

insert into datetest_zsl values(5,'ddd',to_date('MAY-09-01 13-23-34','MM-YYYY-DD HH24-MI-SS'));

 

to_char指定格式的日期型转换为字符串:

select id,name,to_char(bith,'MM-YYYY-DD HH24-MI-SS') from datetest_zsl;

 

日期类型的查找:

select * from datetest_zsl where to_char(bith,'YY')<'09'; 

 

bolb clob-------------

create table lobtest_zsl(

id number(3) primary key,

a blob,

b clob

);

insert into lobtest_zsl(id,b) values(1,'asdasdasdasd');-----插入大文件文本类型的数据

 

===================day03===============

1.contraints

----primary key主键

create table pktest_zsl(

id number(3) primary key,-----------定义主键别名:  id number(3) constraint pkid_zsl primary ker,(一般都不加别名)

name varchar2(10)

);

数据字典表:

user_tables    user_constraints

 

查找表中的约束名:

select constraint_name,constraint_type from user_constraints 

where table_name='PKTEST_ZSL';

 

修改约束:

alter table pktest_zsl drop constraint  SYS_C00141192;

 

                                                                                                                ----查找到的约束名字(在定义主键的时候自己起个名字便于修改,不需要再查找了)

alter table pktest_zsl add constraint pkid_zsl primary key(id);

alter table pktest_zsl drop primary key;--------删除主键

alter table pktest_zsl add (fid number(3) constraint pktest_zsl foreign key(cid) references fk2test_zsl(id));---添加外建

alter table pktest_zsl rename column id to eid;-------修改表中间的字段名

alter table pktest_zsl modify(name varchar2(10) not null)------添加非空约束

 

----foreign key外建

create table fk1test_zsl(

id number(3) primary key,

name varchar2(10),

cid number(3),

constraint fkid_zsl foreign key(cid) references fk2test_zsl(id));

insert into fk1test_zsl values(101,'aaa',1);

insert into fk1test_zsl values(102,'bbb',2);

insert into fk1test_zsl values(103,'ccc',1);

 

create table fk2test_zsl(

id number(3) primary key,

name2 varchar2(10)

);

insert into fk2test_zsl values(1,'c');

insert into fk2test_zsl values(2,'c++');

 

alter table fk1test_zsl drop constraint fkid_zsl;

alter table fk1test_zsl add constraint fkid_zsl foreign key(cid)

references fk2test_zsl(id) on detele cascade;-------级联删除,在删除主表的时候同时删除从表的对应记录

alter table fk1test_zsl add constraint fkid_zsl foreign key(cid)

references fk2test_zsl(id) on detele set null;------同上,区别在从表的对应记录不会被完全删除,只删除从表的外建字段。

 

 ----not null     unique     check

create table nuctest_zsl(

id number(3) primary key,

a varchar(10) not null,

b char(10) unique,

c number(3)

);

insert into nuctest_zsl(id,a) values(1,'aaa');

not null修改

altar table nuctest_zsl modify a null;

unique的修改同前面主键约束的修改

check的修改

alter table nuctest_zsl add constraint check_zsl

check(c in(1,2,3));

 

----create table as subquery

使用子查询创建表,并复制记录

create table empnew_zsl

as

select eld,name from emp_zsl where addr='sh';---将一张大表中需要查询的记录写入一个新表,便于操作,提高效率

使用子查询添加记录

insert into empnew_zsl select eld,name from emp_zsl

where addr='bj';

 

--emp_zsl的结构复制到新的表empnew1_zsl中,不要记录:

create table empnew1_zsl

as

select * from emp_zsl

where eld is null;

 

--update 修改记录

update emp_zsl set name='zhangga',age=66 where eld=101;

 

把所有人的年龄都加1

update emp_zsl set age=age+1; 

 

----transaction

commit-------提交操作

savepoint abc;----设置保存点abc

rollback to abc------回退到保存点abc

 

事务的四个特性

a(automic 原子性)

c(consistency 一致性)

i(isolation 隔离性)

d(durability  持久性)

 

事务之间的隔离性:

read uncommitted:一个事务能读到另外一个事务没有提交的数据

read committed:一个事务只能读到另外事务提交了的数据。

read repetable:一个事务进行了查询,停顿了一会,又进行了同样的查询,发现两次查询的内容不同,

是因为有另外一个事务修改了数据,并提交。如果两次查询的一样,需要设置高隔离级别。

phantom read:一个事务进行了查询,停顿了一会,又进行了同样的查询,发现两次查询的内容不同,

是因为有另外一个事务添加了数据,并提交。如果不要幻影读,需要设置高隔离级别。

 

set transaction isolation level serializable;----设置隔离的最高级别。

set transaction isolation level read committed;-----设置隔离的最低级别。

 

----sequence序列       用来产生唯一的数字

 

create sequence seq_zsl;

insert into empenv_zsl values(seq1_zsl.nextval,'nnn',23,'bj');

insert into emp_zsl values(seq_zsl.nextval,'aaa',23,'bj');

select seq_zsl.currval from dual;----- 序列当前的值

alter/create sequence seq1_zsl increment by 2

start with 300 maxvalue 305 cycle;-------修改序列

 

user_sequences数据字典表

cache ----表示下一组可用的(自动添加的)

select * from user_sequences

where sequence_name='SEQ1_ZSL';

 

----index 索引----- 索引查找效率高(有主键约束的表就有主键索引,唯一约束的就是唯一索引)

desc user_indexs;

select index_name,index_type from user_indexs

where table_name='NUCTEST_ZSL';

 

create index index_zsl on emp_zsl(eld,name);-------给表emp_zsl创建索引

 

drop index from  表名

 

================day04==================

 

创建视图:

使用视图能够简化查询语句的编写

create or replace view myview_zsl

as

select s.name a,c.name b from shu_zsl s,class_zsl c

where s.cid=c.id;--------------需要查找的字段也要起个别名,不报错

 

select c from myview_zsl where c='aa';

//通过视图不能修改多个表,但是可以修改一个表

insert into myview_zsl values('bbb','cc');-----2表以上的复杂视图,这样的插入是不成功的(DML的增删改都不可以操作)

 

create or replace view simpleview_zsl

as

select sid,name,cid from stu_zsl;

//对于简单视图,可以通过视图向表添加记录

insert into simpleview_zsl values(201.'aaa',1);

 

with read only;----------限制DML的增删改             /

                                                                                                             2个不能一起用                               

check---------------检查是否满足条件                          /

 

create or replace simoleview_zsl

as

select sid,name,cid from stu_zsl where sid>203

with check option constraint viewconstraint_zsl;

 

focenofoce的区别:

create or replace foce/nofoce simoleview_zsl

as

select sid,name,cid from stu_zsl where sid>203

with check option constraint viewconstraint_zsl;

 

drop  view  视图表名;----删除一个视图

 

----------------执行计划

set autotrace on; ----打开

set autotrace off;----关闭

select * from shu_zsl where upper(name)='AAA';

create table plantest_zsl(

id number(3) primary key,

a char(10),

b char(10)

);

insert into plantest_zsl values(1,'aaa','bbb');

insert into plantest_zsl values(2,'aaa1','bbb1');

insert into plantest_zsl values(3,'aaa2','bbb2');

 

select * from plantest_zsl where id>1; ----全表扫描(没有使用主键的索引)

create index plantest_zsl on plantest_zsl(a,b);------创建索引

比较下面2个查询之间的区别:

select * from plantest_zsl where b='bbb';

select * from plantest_zsl where b='bbb' and a='aaa';

 

 

 

create table stu_zsl(

sid number(3) primary key,

name varchar2(30)

);

 

create table less_zsl(

lid number(3) primary key,

name varchar2(10)

);

 

 

create table link_zsl(

studentid number(3),

lessonid number(3),

score number(3),

constraints linkpk_zsl prinary key(studentid,lessonid),

constraints linkfk_zsl foreign key(studentid) references stu_zsl(sid),

constraints linkfk1_zsl foreign key(lessonid) references less_zsl(lid)

);

 

sqlplus sys/root as sysdba; 

sqlplus /noiog        --------管理员的身份登录

connect /as sysdba; 

create user abcde identified by abcd;----创建数据库管理员

grant connect,resource to abcde;-----授权数据库管理员abcde

revoke connect from abcde;

startup;

shuldown immdeiate;

 

c /旧串/新串    -----修改上一条sql语句(edit

get *.sql  ------读取sql语句到缓冲区,/运行缓冲区中的sql语句

@*.sql   -----------同上

 

spool on

spool *.sql(可以把spool的打开到关闭,中间的输出输入都保存到  *.sql的文件中)

---

---

spool off (spool的打开到结束,中间的输入输出都记录到缓冲去中,edit可以查看)

 

set head on

set head off

set feed off

set echo off

 

 

创建一个新的数据库

create database mydb;

use mydb;

show tables;

create table emp(

id int(3) primary key,

name varchar(10),

birth datetime

)

mysql -u root -p      管理员登陆mysql

================================day05==================================

set serveroutput on

定义匿名语句快,每次都需要复制到sqlplus执行

在后面,会将语句块定义在函数中,该函数会保存在oracle中,每次只需要调用函数名即可

declare  

sname varchar(10);

id number(3):=101; 

begin

select name into sname from stu_zsl where sid=id;

dbms_output.put_line('hello');

end;/                           ----运行

 

declare   

a boolean :=true;

b number(3):=23;

c date :='02-MAY-09';

d binary_integer :=56; 

begin

dbms_output.put_line(c);

end;/     

----------------数据类型

--%trpe

declare

name stu_zsl.name%type; --name的类型和表stu_zsl中的name的类型和长度保持一样.(这也是一种声明的方式

sname name%type; --snamename的类型一样;

 

--record       --记录

declare

type myrecord is record(

a number(3),

b varchar2(10),

c number(3)

);

onestu myrecord;

begin

select sid,name,sid into onestu from stu_zsl where sid=201;

dbms_output.put_line(onestu.a||onestu.b);

end

 

--%rowtype;      一个表的类型

declare

onestu stu_zsl%rowtype;

onestu stu_zsl%rowtype;

begin

select sid,name into onestu from stu_zsl where sid=201;

select sid,name into onestu1 from stu_zsl where sid=203;

dbms_output.put_line(onestu.sid||onestu.name||onestu.sid);

end;

 

--table

declare

type mytable is table of stu_zsl%rowtype index by binary_integer;

stu mytable;

begin

select sid,name into stu(1) from stu_zsl where sid=201;

select sid,name into stu(101) from stu_zsl where sid=203;

dbms_output.put_line(stu(1).sid||stu(101).name);

end;

/

stu(1)---相当于数组,(1)就是下标

 

--bfile 储存较大数据的类型      

blob

clob

create table bfiletest_zsl(

id number(3) primary key;

content bfile      -------相当于指针

);

使用管理员的权限创建directory

create directory DIR_ZSL as '/user/openlab(目录路径)'

将该目录授权给用户:

grant read on directory DIR_ZSL to csd0903;

grant write on directory DIR_ZSL to csd0903;

 

使用bfilename获得目标的指针:

insert into bfiletest_zsl values(1,bfilrname('DIR_ZSL(目录名字)','zsl.txt(文件名字)');   ----必须要这样写才能插入

 

declace

a bfile;   ----a就是一个指向(指针)

b raw(60);  -----raw表示的类型和varchar2是一样的。 

c binary_integer=60

begin

select content into a from bfiletest_zsl where id=1;

dbms_lob.open(a);

// dbms_lob.read(a,60,1,b); --读取a60个字符放入b

dbms_lob.read(a,c,1,b); --如果a中间不足60个字符,会出错,所以这里定义一个变量

dbms_lob.close(a);

dbms_output.put_line(UTL_LOB.cast_to_varchar2(b));----b的输出转化成一个字符串。

end;

 

变量的作用域:

<<abc>>

declare

a number(3) :=12;

begin

declare

a number(2) :=10;

b char(10) :='hello';

begin

dbms_output.put_line(abc.a);------引用外面A变量的值

end;

end;/

 

----if

declare

classid number(3);

name varchar2(10);

begin

select cid.name into classid,name from stu_zsl where sid=201;

if classid=1 and name='aaa' then

dbms_output.put_line('in 1');

elif classid=2 then

dbms_output.put_line('in 2');

else

dbms_output.put_line('in 3');

end;/

 

 

----loop

declare

i number(3) :=1;

id number(3) :=401;

begin

loop

insert into stu1_zsl values(id,'aaa',1);

id := id+1;

i :=i+1;

exit when i>10;

end loop;

end;/

 

--for

declare

j number(2) :=1;

begin

for i in reverse 1..10 loop

dbms_output.put_line(i||'--');

end loop;

while j<=10 loop

dbms_output.put_line(j||'--');

j :=j+1;

end loop;

end;/

 

--goto语句

declare

i number(3) :=1;

begin

<<abc>>

if i<=10 then

dbms_output.put_line();

i :=i+1;

goto abc;

end if;

end;/

 

--ps/sql语句块中创建表

declare

sqla varchar2(100);

begin

sqla :='create table day1_zsl(id number(3) primary key,name varchar2(10))';

execute immediate sqla;

end;/

 

declare

id number(3) :=1;

c varchar2(5) :='aa';

begin

while id<=10 loop

insert into day1_zsl values(id,c);

id :=id+1;

end loop;

end;/

 

--cursor        游标

declare

cursor mycursor is select * from stu1_zsl;-----这里并没有查询,只是定义了。这个时候游标是在第1条记录的前面。

one stu_zsl%rowtype;

begin

open mycursor; --open的时候返回游标查询的结果集。

loop

fetch mycursor into one;   -------游标移动到下一条

exit when mycursor%notfound;

dbms_output.put_line(one.sid||one.name||one.cid);

end lopp;

close mycursor;

end;/

 

 

create table temp_zsl(

id number(3),

name varchar2(10)

);

insert into temp_zsl values(101,'aaa');

insert into temp_zsl values(102,'ccc');

insert into temp_zsl values(101,'aaa');

insert into temp_zsl values(104,'bbb');

 

declare

one temp_zsl%rowtype;

cursor mycursor is select distinct * from temp_zsl;

begin

open mycursor;

delete from temp_zsl;

loop

fetch mycursor into one;

exit when mycursor%notfound;

insert into temp_zsl values(one.id,one.name);

end loop;

close mycursor;

end;/

 

--代参数的cursor

declare

cursor mycursor(a number) is select * from temp_zsl;

one temp_zsl%rowtype;

begin

open mycursor(101);

 

loop

fetch mycursor into one;

exit when mycursor%notfound;

dbms_output.put_line(one.id||one.name);

end loop;

close mycursor;

end;/

 

binary_integet:二进制整型

 

===============================day06=====================================

show errors   ---显示错误信息

异常处理的办法:

declare

name varchar2(10);

id number(3) :=1;

begin

select name into name from stu_zsl where sid=id;

exception

when no_data_found then

dbms_output.put_line('aaa');

when too_many_rows then

dbms_output.put_line('bbb');

end;/

 

declare

abc exception;

pragma exception_init(abc,-00001);

begin

insert into stu_zsl values(301,'aaa',1);

exception

when abc then

dbms_output.put_line('bbb');

end;/

 

declare

abc exception;

begin

dbms_output.put_line('aaa');

raise abc;                             --抛出一个异常

dbms_output.put_line('bbb');

exception

when abc then

dbms_output.put_line('ccc');

end;/

 

-----------存储过程

create or replace procedure mypro_zsl

as

n varchar2(10);

begin

select name into n from stu_zsl where sid=301;

dbms_output.put_line(n);

end;/

调用存储过程:

exec mypro_zsl;    ---方法1

call mypro_zsl();   ---方法2

 

带参数:参数不要写长度

create or replace procedure mypro_zsl(id number)

as

n varchar2(10);     --变量的声明

begin

select name into n from stu_zsl where sid=id;

dbms_output.put_line(n);

end;/

调用存储过程:

exec mypro_zsl(301);    ---方法1

call mypro_zsl(301);   ---方法2

 

 

---------in     能接收数据,在程序中不能修改值

---------out    不能接收值,在程序中修改时,把修改的值返回给调用这的变量。

---------in out 能接收值,在程序中也能修改值,并把值返回给调用者。

create or replace procedure testinout_zsl(a in number,b out number,c in out number)

as

begin

dbms_output.put_line(a||'-'||b||'-'||c);

--a :=12;

b :=13;

--c :=14;

end;/

(a in number default 67)给参数定义一个默认值67

 

declare

m number(3) :=1;

n number(3) :=2;

p number(3) :=3;

begin

testinout_zsl(m,n,p);

dbms_output.put_line(m||'--'||n||'--'||p);

end;/

 

declare

m number(3) :=1;

n number(3) :=2;

p number(3) :=3;

begin

testinout_zsl(m,c=>p,b=>n);  --- 参数对应 p对应cb对应n

dbms_output.put_line(m||'--'||n||'--'||p);

end;/

 

---------存储函数

create or replace function myfunction_zsl(n varchar2)

return number

as

a number(3);

sqla varchar2(80);

begin

sqla :='select count(*) into a from stu_zsl where name like ''%'||n||'%''' ;

dbms_output.put_line(sqla);

execute immediate sqla into a;

return a;

end;/

 

declare

b number(3);

begin

b :=myfunction_zsl('a');

dbms_output.put_line(b);

end;/

 

desc user_procedures;

 

---------package

定义包头部分:   包可以重载,重载的定义和C++一样

create or replace package mypack_zsl

as

procedure a_zsl;

procedure a_zsl(n varchar2);

procedure b_zsl(n number);

function c_zsl return number;

end mypack_zsl;/

 

create or replace package  body mypack_zsl

as

procedure a_zsl

as

begin

dbms_output.put_line('in a');

end a_zsl;

 

procedure a_zsl(n varchar2)

as

begin

dbms_output.put_line('in a'||n);

end a_zsl;

 

procedure b_zsl(n number)

as

begin

dbms_output.put_line('in b'||n);

end b_zsl;

 

function c_zsl return number

as

begin

return 12;

end c_zsl;

end mypack_zsl;

/

 

exec mypack_zsl.a_zsl;              调用对应的包名

exec mypack_zsl.a_zsl('a');

exec mypack_zsl.b_zsl(1);

exec mypack_zsl.c_zsl;

 

 

-------触发器                           作用:帮定的表被操作一次触发器就会发送一次消息       (语句触发器)

create or replace trigger mytrigger_zsl

before delete or insert or update on stu_zsl

declare

begin

dbms_output.put_line('before update');

end;/

 

update stu_zsl set name='aaaaa' where sid=301; 

insert into stu_zsl values(303,'abc',3);

 

desc user_triggers;

 

使用for each row的触发器为行级,可以使用:oldnew获得值

create or replace trigger mytrigger_zsl

before delete or insert or update on stu_zsl

for each row

declare

begin

dbms_output.put_line('before update'||:old.name||:new.name);

end;/

 

update stu_zsl set name='d';

 

------after    报错不输出

------before   出错的时候先输出再报错

create or replace trigger mytrigger_zsl

after insert or update on stu_zsl

for each row

declare

abc exception;

begin

/* if :old.sid>:new.sid then

--raise abc;

raise_application_error(-20007,'not!!');

end if;

*/

dbms_output.put_line(:old.name||'--'||:new.name);

end;/

 

insert into stu_zsl values(102,'aaa',1);

update stu_zsl set name='bbb' where sid=102; 

 

----系统触发器:

create table user_tables_zsl(

table_name varchar2(10),

type varchar2(10),

usern varchar2(10)

);

 

create or replace trigger systemtrigger_zsl

before create on database

declare

begin

dbms_output.put_line(ora_sysevent);

if ora_sysevent='CREATE' then

insert into user_tables_zsl values(ora_dict_obj_name,ora_dict_obj_type,ora_dict_obj_owner);

else

delete from user_tables_zsl where table_nameora_dict_obj_name;

end if;

end;/

 

alter trigger systemtrigger_zsl disable; 触发器无效

 

----instead of 触发器

视图操作view----------复杂视图操作:

create or replace view myview_zsl(id,sname,cname) as

select s.sid,s.name,c.name from stu_zsl s,class_zsl c

where s.cid=c.id;/

 

insert into myview_zsl values(102,'aaa',2,'o');       --这样插入无法成功

写个触发器, 把上面的插入分成2个部分插入。

create or replace trigger viewtrigger_zsl

instead of insert on viewtrigger_zsl

for each row

declare

begin

insert into class_zsl values(:new.cid,:new.cname);

insert into stu_zsl values(:new.id,:new.sname,:new.cid);

end;/

----DBMS_JOB

create sequence stu_job_zsl;

 

create or replace procedure test_job_zsl

as

begin

insert into stu_zsl values(stu_job_zsl.nextval,'aaa',1);

end;/

 

declare

jobno binary_integer;

begin

dbms_job.submit(jobno,'test_job_zsl;',sysdate,'sysdate+15/60/60/24');

dbms_output.put_line(jobno);

end;/

 

exec dbms_job.run(92);

exec dbms_job.remove(92);结束工作号41

 

=====================================day07======================================

-----------动态sql

1.pl/sql中使用create dropddl操作

create or replace procedure mydyna_zsl

as

sqla varchar2(100)

begin

for a in 1..30 loop

sqla :='create table mydyna_zsl'||a||'(id number(3),n varchar2(10))';

execute immediate sqla;

end loop;

end;/

 

2.pl/sql中使用update  insert  deletedml操作

create or replace procedure mydml_zsl(a number,b varchar2,c number)

as

begin

insert into stu_zsl values(a,b,c);

end;/

 

create or replace procedure mydml_zsl(a number,b varchar2,c number)

as

sqlc varchar2(100);

begin

--sqlc :='update stu_zsl set name=:n where sid=:s';----和:表示占位符

sqlc :='update stu_zsl set name='''||b||''' where sid='||a;

dbms_output.put_line(sqlc);-----------------把字符串保存到一个变量中,如果使用了连接,最好能够打印这个变量,查看字符串是否符合要求

--execute immediate sqla using b,a; ----------b对应:na对应:s   对应有占位符的sql语句使用。

execute immediate sqla;

end;/

 

3.使用单行查询

create or replace procedure mysinglerrow_zsl(id number)

as

a varcha2(10);

sql carchar2(100)

begin

--select name into a from stu_zsl where sid=id;

sql :='select name into a from stu_zsl';

if id>200 then

sql :=sql||'where sid='||id;

--sql :=sql||'where sid=:n';

else

sql :=sql||'where sid='||(id-1);

end if;

dbms_output.put_line(sql);

execute immediate sql into a;

dbms_output.put_line(a);

end;/

 

4. 使用多行查询--------动态子查询

create or replace procedure myrows_zsl

as

sqla varchar2(50);

type mycursor is ref cursor;

stucursor mycursor;

n stu_zsl%rowtype;

begin

sqla :='select * from stu_zsl';

open stucursor for sqla;

loop

fetch stucursor into n;

exit when stucursor%notfound;

dbms_output.put_line(n.sid||n.name);

end loop;

close stucursor;

end;/ 

-----------使用大数据类型

bfile

blob

clob

select * from stu_zsl for update;-----for update的作用:这次事物的操作没有结束,其他操作都无效。

create table testclon_zsl(

id number

)

 

declear

lobloc clob;

fileloc bfile;

amount number;

begin

file :=bfilename('G','zsl.txt');    -----文件的位置

dbms_lob.fileopen(fileloc,0);

amount :=dbms_lob.getlength(fileloc);

select 

end;

 

====================================proc========================================

precomplie 预编译

 

exec sql include sqlca;  sqlca是个结构     

1  #include <stdio.h>

2  exec sql begin declare section;

3   char name[10]="openlab";

4  // char pwd[10]="open123";

varchar pwd[10];

5   char sname[20];

short namenum;   ----指示变量

6  exec sql end declare section;

7  exec sql include sqlca;

exec sql include oraca;

exec oracle option(oraca=yes);

8  void myerror();

9  main()

10    {

oraca.orastxtf=3; 

strncpy(pwd.arr,"open123",7);

pwd.len=7;

11  exec sql whenever sqlerror do myerror();

12  exec sql connect :name identified by :pwd;

13  exec sql select name into :sname :namenum from stu_zsl where sid=301;

14  printf("/n===%s",sname);

printf("/n======%d",namenum);

15  exec sql commit work release;

exec sql insert into stu_zsl values(401,'aaa',1);

exec sql commit;

16  }

17 void myerror()

18  {

19  exec sql whenever sqlerror continue;

20  printf("/nerror===%s",sqlca.sqlerrm.sqlerrmc);

printf("/noracatest==%s",oraca.orastxt.orastxtc);

21  exec sql rollback work release;

22  exit(1);

21  }

 

namenum  指示变量      用于处理数据库的null

返回0便是赋值成功

返回-1表示赋值为NULL

返回>0表示值被截取后赋值

proc first.pc    预编译         ---------生成一个点C文件  first.c

gcc frist.c -lclntsh        --------------生成a.out文件

 

变量要放在申明区的,变量使用在sql语句中,变量名前面要加:

varchar   可以是个结构。   变长不是‘/0’结尾

struct carchar{

unsigned short len;

unsigned char arr[];

};

 

proc first.pc 预编译

预编译选项char_map

char_map=charz(默认设置)‘/0’结尾,定长,空格补齐

char_map=charf|varchar2   定长,空格补齐

char_map=string          ‘/0’结尾,变长。

 

oraca 结构的使用:

exec sql include oraca;

exec oracle option(oraca=yes);

main(){

oraca.orastxtf=3;     ----输出的错误信息保存成文本

}

void myerror()

{

printf("/noracatest==%s",oraca.orastxt.orastxtc);---出错打印

}

 

 

嵌入匿名语句快的一个存储过程,用proc编译时要使用如下命令:

proc plsql.pc sqlcheck=semantic userid=openlab/open123

 

plsql.pc:

1  #include <stdio.h>

2  exec sql begin declare section;

3  varchar name[10];

4  varchar pwd[10];

5  char sname[10];

6  int numtotal;

7  exec sql end declare section;

8  exec sql include sqlca;

9  void myerror();

10  main()

11  {

12  strncpy(name.arr,"openlab",7);

13  name.len=strlen(name.arr);

14  strncpy(pwd.arr,"open123",7);

15  pwd.len=strlen(pwd.arr);

16  exec sql whenever sqlerror do myerror();

17  exec sql connect :name identified by :pwd;

18  exec sql execute

19      begin

20  //      select name into sname from stu_zsl where sid=301;

21          select count(*) into :numtotal from stu_zsl;

22 mypro_zsl();         ----调用存储过程(存储过程在SQL中创建,程序中直接调用)

 //      :numtotal := mypro_zsl1();    ----调用存储函数,返回值保存在numtotal

23      end;

24  end-exec;

25  printf("%d",numtotal);

26  exec sql commit work;

27  }

28

29  void myerror()

30  {

31  exec sql whenever sqlerror continue;

32  printf("/nerror==%s",sqlca.sqlerrm.sqlerrmc);

33  exec sql rollback work release;

34  exit(1);

35  }

 

 

  连接2个服务器: 在服务器26上连接20服务器:

SQL>Create Database Link link_zsl connect to scott identified by tiger

using '(DESCRIPTION=

(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.20)(PORT=1521)))

(CONNECT_DATA=(SERVICE_NAME=tarena)))';

 

SQL>select * from stu_zsl@ling_zsl;  ------26服务器上查询20服务器上的表stu_zsl;

 

 

代码中连接其他服务器:

char  db26[]='tarena26';

main(){

exec sql connect :name using :db26;     ----连接26服务器

}

-------注意:tarena26的值等于(DESCRIPTION=

(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.20)(PORT=1521)))

(CONNECT_DATA=(SERVICE_NAME=tarena)))

 

 

连接2个数据库的代码:

1  #include <stdio.h>

2  exec sql begin declare section;

3      char sname[20];

4      char deptname[20];

5      char name1[20] = "openlab/open123";

6      char name2[20] = "scott/tiger";

7      char db1[10]="first";

8      char db2[10]="second";

9  exec sql end declare section;

1  #include <stdio.h>

2  exec sql begin declare section;

3      char sname[20];

4      char deptname[20];

5      char name1[20] = "openlab/open123";

6      char name2[20] = "scott/tiger";

7      char db1[10]="first";

8      char db2[10]="second";

9  exec sql end declare section;

10  exec sql include sqlca;

11  void myerror();

12  main()

13  {

14      exec sql whenever sqlerror do myerror();

15      exec sql connect :name1 at :db1;

16      exec sql connect :name2 at :db2;

17      exec sql at :db1 select name into :sname from stu_zsl where sid=301;

18      exec sql at :db2 select dname into :deptname from dept where deptno=10;

19      printf("openlab=%s",sname);

20      printf("scott=%s",deptname);

21      exec sql at :db1 commit work release;

22      exec sql at :db2 commit work release;

23  }

 

25  void myerror()

26  {

27      exec sql whenever sqlerror continue;

28      printf("/nerror=====%s",sqlca.sqlerrm.sqlerrmc);

29      exec sql rollback work release;

30      exit(1);

31  }

 

====================================day09====================================

#include <sqlca.h>        /

or                         2种方法都可以调用sqlca这个结构

exec sql include sqlca;  /

 

sqlca 不仅仅处理错误信息

proc中结构体的应用:

     1  #include <stdio.h>

     2  exec sql begin declare section;

     3  char name[10]="openlab";

     4  varchar pwd[10];

     5  char sname[30];

     6  struct a

              7  {

     8      int cid;

     9      char birth[20];

    10  }first;

    11  short namenum[30];

    12  exec sql end declare section;

    13  exec sql include sqlca;

    14 

    15 

    16  void myerror();

    17  main()

           18  {

        exec sql var birth is date;

    19  oraca.orastxtf = 3;

    20  int i=0;

    21  strncpy(pwd.arr,"open123",7);

    22  pwd.len=7;

    23  exec sql whenever sqlerror do myerror();

    24  //exec sql whenever not found do myerror();

    25  exec sql whenever not found goto yerror;

    26  exec sql connect :name identified by :pwd;

    27  exec sql select cid,to_char(birth,'YYYY-HH-dd HH:mi:ss') into first from stu_zsl where sid=301;

    28  printf("=========%d/n",first.cid);

    29  printf("-----%s/n",first.birth);

    30  exec sql commit work;

    31  exit(0);

    32  yerror:

    33      printf("/nnotfound:%s",sqlca.sqlerrm.sqlerrmc);

    34      exit(1);

           35  }

    36  void myerror()

       37  {

    38  exec sql whenever sqlerror continue;

    39  printf("/nerror===%s",sqlca.sqlerrm.sqlerrmc);

    40  printf("/nsqltest===%s",oraca.orastxt.orastxtc);

    41  exec sql rollback work release;

    42  exit(1);

           43  }

 

 

cursorproc中的应用:

     1  #include <stdio.h>

     2  exec sql begin declare section;

     3      char name[10]="openlab";

     4      varchar pwd[10];

     5      char sname[30];

     6      struct a

     7      {

     8          int cid;

     9          char birth[20];

    10      }first;

    11  short namenum[30];

    12  exec sql end declare section;

    13  exec sql include sqlca;

    14  exec sql include oraca;

    15  exec oracle option(oraca=yes);

    16  void myerror();

    17  main()

    18  {

    19  oraca.orastxtf = 3;

    20  int i=0;

    21  strncpy(pwd.arr,"open123",7);

    22  pwd.len=7;

    23  exec sql whenever sqlerror do myerror();

    24  //exec sql whenever not found do myerror();

    25  exec sql whenever not found do break;

    26  exec sql connect :name identified by :pwd;

    27  exec sql declare mycursor for select cid,to_char(birth,'YYYY-HH-dd HH:mi

:ss') into first from stu_zsl;

    28  exec sql open mycursor;

    29  while(1)

    30  {

    31      exec sql fetch mycursor into first;

    32      printf("=========%d/n",first.cid);

    33      printf("-----%s/n",first.birth);

    34  }

    35  exec sql whenever not found goto yerror;

    36  exec sql declare ycursor scroll cursor for

    37  select cid,birth into first from stu_zsl;

    38  exec sql open ysuesor;

    39  exec sql fetch  absolute 2 ysursor into first; //定位到cursor中的第2条记录

    40  printf("%d/n",first.cid);

    41  exec sql close mycursor;

    42  exec sql close mycursor;

    43  exec sql commit work;

    44  exit(0);

    45  yerror:

    46      printf("/nnotfound:%s",sqlca.sqlerrm.sqlerrmc);

    47      exit(1);

    48  }

    49  void myerror()

    50  {

    51  exec sql whenever sqlerror continue;

    52  printf("/nerror===%s",sqlca.sqlerrm.sqlerrmc);

    53  printf("/nsqltest===%s",oraca.orastxt.orastxtc);

    54  exec sql rollback work release;

    55  exit(1);

    56  }

 

 

动态sqlproc中的使用:

     1  #include <stdio.h>

     2  exec sql begin declare section;

     3      char name[10]="openlab";

     4      varchar pwd[10];

     5      char sname[30];

     6      int cid=301;

     7      char birth[20];

     8      char sqls[100];

     9  short namenum[30];

    10  exec sql end declare section;

    11  exec sql include sqlca;

    12  exec sql include oraca;

    13  exec oracle option(oraca=yes);

    14  void myerror();

    15  main()

    16  {

    17  oraca.orastxtf = 3;

    18  int i=0;

    19  strncpy(pwd.arr,"open123",7);

    20  pwd.len=7;

    21  exec sql whenever sqlerror do myerror();

    22  //exec sql whenever not found do myerror();

    23  exec sql whenever not found goto yerror;

    24  exec sql connect :name identified by :pwd;

    25  strcpy(sqls,"update stu_zsl set name='work' where sid=401");

    26  exec sql execute immediate :sqls;

    27  strcpy(sqls,"update stu_zsl set name='work' where sid=:efg");

    28  exec sql prepare abc from :sqls;   //abc来预编译一下sql语句

    29  exec sql execute abc using :cid;   //cid使用到占位符efg

    30  exec sql commit work;

    31  exit(0);

    32  yerror:

    33      printf("/nnotfound:%s",sqlca.sqlerrm.sqlerrmc);

    34      exit(1);

    35  }

    36  void myerror()

    37  {

    38  exec sql whenever sqlerror continue;

    39  printf("/nerror===%s",sqlca.sqlerrm.sqlerrmc);

    40  printf("/nsqltest===%s",oraca.orastxt.orastxtc);

    41  exec sql rollback work release;

    42  exit(1);

    43  }

mysql_library_init()

mysql_init()初始化

mysqo_real_connect(,服务器地址,登录用户名,)建立连接

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值