关于PL\SQL应用的碎记

select*from recyclebin//orcle回收站

purge recyclebin清空回收站

flashback table 表名 to before drop  回闪

alter table 表名
add constraint stu_uk unique(唯一值)

check约束

age number check(age>0 and age <=100)

实体完整性
Primary key

alter table tb_user
add constraint stu_pk primary key(sno)

引用完整性(参照完整性)

foreign key(eid) references department(eid)

解锁scott用户

sqlplus/nolog 回车

SQL>conn/as sysdba 回车

SQL>alter user scott account unlock; 回车

 

显示结果:
set serveroutput on
最后结束 /

设置查询的显示格式

set pagesiz n  查询显示的行数

set newpage n 设置页与页之间的间隔

set linesize n 设置行字符数
SHOW LINESIZE

保存查询的语句
save filename;

保存查询的结果
SPOOL filename
SPOOL OFF 

追加文本命令
APPEND TEXT;

增加文本命令
INPUT  INPUT TEXT

删除命令
del text

清除缓冲区命令
 CLEAR BUFFER

调用外部脚本文件
@filename 
清屏
host cls

 

show命令
show all:显示当前所有系统变量的值
show error：查看错误信息
show release :显示数据库的版本
show user:显示当前连接的用户
show sqlcode :显示数据库操作之后的状态代码
show arraysize:数据库一次提取的行数

创建表
create table 表名（id number,name varchar2(30)）;
创建序列

create sequence bank_id

start with 1

increment by 1

minvalue 1

nomaxvalue
 用触发器创建序列
create trigger bank_autoid

    before insert on bank_user

    for each row

begin

    select bank_autoid.nextval into :new.id from dual;

end bank_autoid;

删除序列
drop squence序列名
 
插入数据
  insert into 表名 values（autoid.nextval，列值， …）

流程控制结构
分支结构

{

IF-THEN-END IF

 IF-THEN-ELSE-END IF

 IF-THEN-ELSIF-ELSE-END IF

}

if-then-elsif

declare
v_cnt number;
begin
select count(*) into v_cnt from t_my;
if v_cnt < 2 then
dbms_output.put_line('不足2条记录');
elsif v_cnt < 3 then
dbms_output.put_line('不足3条记录');
else
dbms_output.put_line('达到2条记录');
end if;
end;
/

declare
v_sal number(5);
v_tax number(5,2);
begin
select sal into v_sal from emp where empno = 7788;
if v_sal >= 3000 then
v_tax :=v_sal*0.08; --利率为8%
elsif v_sal >= 1500 then
v_tax :=v_sal*0.06; --利率为6%
else
v_tax :=v_sal*004; --利率为4%
end if;
dbms_output.put_line('应缴税金:'||v_tax);
end;
/

case选择结构

declare
grade char :='A';
remark varchar2(20);
begin
case grade
  when 'A' then remark :='is Excellent';
   when 'B' then remark :='is Good';
    when 'C' then remark :='is Normal';
     when 'D' then remark :='is Bad';
     else remark :='big Problem';
     end case;
     dbms_output.put_line('你的成绩是:'||remark);
     end;

 declare
   v_job varchar2(10);
   begin
  select job into v_job from emp where empno = 7788;
   case v_job
 WHEN 'PRESIDENT' THEN
   DBMS_OUTPUT.PUT_LINE('雇员职务:总裁');
  WHEN 'MANAGER' THEN
    DBMS_OUTPUT.PUT_LINE('雇员职务:经理');
   WHEN 'SALESMAN' THEN
    DBMS_OUTPUT.PUT_LINE('雇员职务:推销员');
   WHEN 'ANALYST' THEN
  DBMS_OUTPUT.PUT_LINE('雇员职务:系统分析员');
   WHEN 'CLERK' THEN
  DBMS_OUTPUT.PUT_LINE('雇员职务:职员');
  ELSE
   DBMS_OUTPUT.PUT_LINE('雇员职务:未知');
  END CASE;
  END;
  /

循环结构
1.LOOP EXIT WHEN END LOOP
求：1２+3２+5２+...+15２ 的值。
SET SERVEROUTPUT ON 
        DECLARE 
         v_total        NUMBER(5):=0; 
        v_count     NUMBER(5):=1; 
        BEGIN 
        LOOP 
            v_total:=v_total+v_count**2; 
            EXIT WHEN v_count=15;--条件退出 
v_count:=v_count+2; 
        END LOOP; 
         DBMS_OUTPUT.PUT_LINE(v_total); 
        END; 
基本循环一定要使用EXIT退出，否则就会成为死循环。

2.for loop 循环
示例：
declare
v_id number :=0;
begin
for i in 1..15 loop
v_id :=v_id+1;
insert into t_my values(v_id,'我爱你');
end loop;
end;
/
loop  exit when end 语句

declare
contor_var=0;
loop
exit when contor_var>5
contor_var :=contor_val_1;
end loop;

3.WHILE LOOP循环
SET SERVEROUTPUT ON 
DECLARE 
v_count NUMBER(2) := 1; 
BEGIN 
  WHILE v_count <6 LOOP 
    INSERT INTO emp(empno, ename) 
    VALUES (5000+v_count, '临时'); 
v_count := v_count + 1; 
  END LOOP; 
  COMMIT; 
END;
显示插入的记录
SELECT empno,ename FROM emp WHERE ename='临时';  
删除临时记录
DELETE FROM emp WHERE ename='临时'; 
        COMMIT; 

4．多重循环

SET SERVEROUTPUT ON 
DECLARE 
  v_total       NUMBER(8):=0; 
  v_ni      NUMBER(8):=1; 
BEGIN 
  FOR I IN 1..10 
  LOOP 
    v_ni:= v_ni*I;  --求n! 
    v_total:= v_total+v_ni; 
  END LOOP;     --循环求总和 
  DBMS_OUTPUT.PUT_LINE(v_total); 
END; 

 

 

GOTO语句

表达式

游标（cursor）

概念:
 游标(Cursor)它使用户可逐行访问由SQL Server返回的结果集。使用游标(cursor)的一个主要的原因就是把集合操作转换成单个记录处理方式。用SQL语言从数据库中检索数据后，结果放在内存的一块区域中，且结果往往是一个含有多个记录的集合。游标机制允许用户在SQL server内逐行地访问这些记录，按照用户自己的意愿来显示和处理这些记录。

分类:显示游标(用户操作)和隐世游标(oracle)

步骤:声明、打开、提取、关闭

读取一行数据
declare
fname varchar2(30);--定义一个变量用来存储读取的数据
cursor c_emp is --声明一个游标
select ename  from emp where empno = 7788;--处理的select语句，不能包含into子句
begin
open c_emp;--打开游标
if c_emp%notfound then --判断游标是否找到
dbms_output.put_line('没有找到数据');
else
fetch c_emp into fname;  --提取游标
dbms_output.put_line(fname);
end if;
close c_emp;--关闭游标
end;
/
循环读取多行数据,不需要显示地声明游标
declare
fname varchar2(30);--定义一个变量用来存储读取的数据
cursor c_emp is --声明一个游标
select emp.ename  from emp where empno <= 7788;--静态的--sql
begin
for c_emps  in c_emp loop
fname :=c_emps.ename;
dbms_output.put_line('姓名:'||fname);
end loop;
end;
/

静态SQL:在PL/SQL块中使用的SQL语句在编译期间是特定的，可预知的，执行的对象确定
动态SQL： 在运行期间发生变化
异常的处理

declare --声明异常
temp_ex exception;
temp_num number;
begin
select count(empno) into  temp_num from emp where empno = '7788';
if temp_num >=1 then
raise temp_ex; --触发异常
end if;
dbms_output.put_line('该用户不存在');
exception --处理异常
when temp_ex then
dbms_output.put_line('用户存在');
end;
/

参数化游标

游标变量
 引用类型，当程序运行时，可以指向不同的存储单元

1.声明游标变量

TYPE <类型名> is ref cursor
return <返回类型>--记录类型

declare
type t_emp is ref cursor --定义使用%rowtype
return emp%rowtype;
v_emp t_emp;
begin
open v_emp for
select *from emp;
end;
/

<过程> 一组代码集合
过程、函数、包、触发器，将商业逻辑、，通过企业规划等写成过程或函数保存到数据中，通过名称进行调用

create or replace procdure 过程名
（<参数1>，[方式1]<数据类型1>）

调用过程 ： execute

删除过程: drop procedure 过程名

过程类型

in 参数类型

out参数类型

in out 参数类型

<函数> 返回一个表示结果的值
create or replace function<>
(<参数1>，[方式1]<数据类型1>.....)
return <表达式>
is|as
pl\sql程序体 --其中必须定义一个return字句

示例1：
declare
v_count number;
begin
v_count :=getcount('M');
DBMS_OUTPUT.PUT_LINE('男生一共有:'||v_count);
end;
/

调用:
declare
v_count number;
begin
v_count :=getcount('M');
DBMS_OUTPUT.PUT_LINE('男生一共有:'||v_count);
end;
/

 

create or replace function COUNT_NUM
(in_gender t_my.gender%type)
return number
as
out_num number;
begin
if in_gender ='M' THEN
SELECT COUNT(GENDER) INTO OUT_NUM
FROM T_MY
WHERE GENDER ='M';
ELSE
SELECT COUNT(GEDER) INTO OUT_NUM
FROM T_MY
WHERE GENDER ='F';
END IF;
RETURN(OUT_NUM);
END COUNT_NUM;
/

调用函数

variable man_num number
variable woman_num number
exxecute man_num v:=count_num('M')
exxecute woman_num v:=count_num('F')

删除函数

drop function 函数名

<过程和函数的区别>
1.标识符不同，过程是procedure ，函数是:function;
2.返回值的形式不同，函数是通过函数名直接返回函数值，过程是通过变量的形参
3.不能给过程名赋值，也不能定义过程的类型，函数反之；
4.函数自定义的时候必须定义函数的类型，过程则不需要；
5.函数的调用出现在表达式中，过程由独立的过程调用语句完成；
6.过程是完成一系列的数据处理，函数只为返回一个函数值

 

程序包:便于共享和再次使用

 

包说明部分和包体部分（具体的实现）

[触发器]

类型:DML触发器，instead of触发器，系统触发器
概念:
触发事件
触发条件
触发对象：表、视图、模式、数据库
触发操作：PL\SQL程序
触发时机 before 、after
条件谓词:针对多个触发事件，inserting,updating,deleting
触发子类型:

创建触发器
create trigger 触发器名称
触发条件
触发体

执行触发器

create trigger my_trigger
after insert or update or delete on tb_user --在DML操作之后触发
for each row
declare
info char(10);
brgin
if inserting then --如果执行插入操作
info ：=‘insert’;
elsif updating then --如果执行修改操作
info:='update';
else --如果执行删除操作
info:='delete';
end if;
insert into SQL_INFO VALUES(INFO) --记录这次操作信息

删除触发器
drop trigger 触发器名

oracle视图

视图相当于一个虚拟表，对真实表的引用只能从真实的表中检索数据，可以对数据进行改变

创建视图
create or replace view 试图名  as select * from 数据库名

删除视图
drop view 视图名

数据库链(Database link)
用于不同数据库间的访问(本地或远程)

创建
create database link tb_user
connect to system indentified by 123456
using '(description=
(address_list=
(address = (protocol = TCP) (HOST = 127.0.0.1)(PORT1521))
)
(connect_data =
(service_name = oracle)
)
)';

删除
drop database link 数据库链名

<Oracle系统参数>

1.起源参数

2带GC前缀的全局高速缓存参数

3与操作系统有关的参数

4可变参数

5异类服务参数

初始化参数文件SPFILE(Server Parameter File) 二进制文件

《Oracle SGA调整》

SGA: System Global Area
组成的三个部分:共享池、数据缓冲区、日志缓冲区

共享池(Share Pool)：库缓存、数据字典缓冲区

库缓存:用来存储SQL命令

数据字典缓存:存储数据库运行的动态信息

缓冲区高速缓冲:(DataBase Buffer Cache)：用于缓存从数据文件中检索出来的数据块，可以大大的提高查询和更新的速度

对库高速缓存的调整
pins 用于显示在库高速缓存中执行的次数或者请求数

reloads 用于显示在库高速缓存中执行的不执行数
select sum(pins) "请求数",sum(reloads) "不命中数" from v$librarycache

select(sum(pins-reloads)) sum(pins)) /sum(reloads) "使用率" from v$librarycacher

对数据字典缓冲的调整

select sum(gets) "请求数" ,sum(getmisses) "不命中数" from v$rowcache

select (sum(gets-getmisses-usage-fixed)）/sum(gets) "使用率" from v$rowcache

数据库缓存的命中率 = 1-(physical read)/(db block gets+consistent gets)

select name , value from v$sysstat where name in('db block gets','consistent gets','physical reads')
/