暑假做的笔记

sqlplus/nolog  直接登录
help index
set sqlblanklines on 设置空格有效
select * from dept where deptno=&tt;
tt=  --一次输入一个tt的值只执行一次执行计划
c/旧值/新值
? column
col deptno heading "编号"  设置标签，只在当前用户有效
col deptno format 999,999,999 字符格式化
select bytes from v$datafile;
sql>set linesize 50
>ttitle center "我的标题" skip 1-
>left "测试报表" right "页"-
>format 999 sql.pno skip 2
报表命令
break on column
comp count label "计数" of books_name on pub 对出版社分组
保存结果集 spool off
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select * from  aa where a2 is null;
select * from aa order by a1 asc;

聚集函数不能作为条件在where后出现
类似的功能用having子句实现
having sum（grade）》20；
select * from aa group by a1 having count(a1)>1
select eid,ename from emp e,dept d where e.deptno=d.deptno(+);外连接
无关子查询
相关子查询：子查询中条件与父查询相关
select * from e where exists (select id from d);无关子查询
select eid,ename from e union select id,name from d;合并行数据
insert into e(eid,ename) select id,name from d;批量插入数据
create table ttt as (select * from e);表的复制
set serveroutput on

pl/sql高级应用：
游标——可以对sql语句的处理进行显示的控制，便于对表的行数据逐条进行处理；
分类——显式、隐式
declare
cursor mycur is
select * from books;
myrecord books%rowtype;
begin
open mycur;
fetch mycur into myrecord;--将结果集放在变量里
while mycur%found loop--利用循环对取出来的的数据逐条显示
dbms_output.put_line(myrecord.books_id||myrecord.books_name);
fetch mycur into myrecord;--继续取数据
end loop;
close mycur;
end;


存储过程：
定义——
create or replace procedure myproc(id in varchar2)
is
name varchar2(10);
begin
select books_name into name from books where books_id=id;
dbms_output.putline(name);
end myproc;
/
procedure created.
执行——
declare
tid varchar2(10);
begin
tid='00001';
myproc(tid);
end;
/
/