关于PL\SQL应用的碎记

ゞ浴缸里的玫瑰 2022-09-18 12:52 139阅读 0赞

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命令