ORACLE【PLSQL编程】笔记

金刚管管

已于 2024-03-25 16:54:29 修改

阅读量542

点赞数 9

文章标签：笔记

于 2024-03-25 09:38:39 首次发布

本文链接：https://blog.csdn.net/qq_54040611/article/details/136901245

版权

plsql编程笔记

一、程序块格式：

declare声明部分，可选
begin 执行部分，必须
exception异常处理部分，可选
end表示结束

二、合法字符：

所有的大写和小写英文字母
数字0-9
基本符号（） + - * / < > = !
赋值 =
并置 || （和concat函数效果相同）

三、变量和常量

变量：
变量名数据类型[长度:=初始值]
常量
常量名 constant 数据类型:=常量值
注： plsql中未初始化的变量的初始化值是null

四、基本数据类型

1.数值类型
NUMBER 整数或者浮点数 number(m,n) m是总的位数,n是小数的位数
PLS_INTEGER 整数
BINARY_INTEGER 整数
2.字符类型
VARCHAR2 varchar2(m) m<32767
CHAR char(m) m<32767 有默认值1
LONG
NCHAR
NVACHAR2
3.日期类型
DATE 存储日期和时间
使用一个字节存储世纪、年月日小时分钟和秒
4.布尔类型
BOOLEAN 用于程序的流程控制和业务逻辑判断，其变量值可以是TRUE,FALSE和NULL中的一种

五、特殊数据类型

1.%TYPE类型
使用该关键字可以声明一个与指定列名称相同的数据类型，通常紧跟在指定列名的后面。
2.RECORD类型
记录类型就好像是一个封装了多个属性的类，也就是一个变量里包含其他变量的结构体。在记录类型的定义结构中包含成员变量及其数据类型
2.%ROWTYPE
该类型的变量结合了%TYPE类型和记录类型变量的优点，根据数据表中行的结构定义一种特殊的数据类型，来存储从数据表中检索到的一行数据。

六、选择与循环

if （）then ; end if;
if () then; elsif () then; else (); end if;

declare
  v_grade number;
begin
  dbms_output.put_line('please input a value for grade');
  v_grade:=&grade;
  if v_grade < 60 then
    dbms_output.put_line('fail');
  elsif v_grade < 70 then
    dbms_output.put_line('qualified');
  elsif v_grade < 90 then
    dbms_output.put_line('good');
  elsif v_grade < 100 then
    dbms_output.put_line('excellet');
  else
    dbms_output.put_line('perfect');
  end if;
end;

七、循环语句

loop () ;exit when(); end loop;

declare
  sum_i int:=0;
  i int:=0;
begin
  loop
    i:=i+1;
    sum_i:=sum_i+i;
    exit when i=100;
  end loop;
  dbms_output.put_line(sum_i);
end;

while（）loop （）；end loop;

declare
   sum_i int:=0;
   i int:=0;
begin 
  while i<100 loop
    i:=i+1;
    sum_i:=sum_i+i;
  end loop;
  dbms_output.put_line(sum_i);
end;

for in loop(); end loop;

declare
  sum_i int:=0;
  i int :=0;
begin
  for i in 1..100 loop
    sum_i:=sum_i+i;
   end loop;
   dbms_output.put_line(sum_i);
end;

八、选择与跳转

case

declare
  season int;
  aboutinfo varchar2(50);
begin
  season :=&season;
  case season
    when 1 then
      aboutinfo :=season||'month:1、2、3';
    when 2 then
      aboutinfo :=season||'month:4、5、6';
    when 3 then
      aboutinfo :=season||'month:7、8、9';
    when 4 then
      aboutinfo :=season||'month:10、11、12';
    else
      aboutinfo:='season error';
   end case;
   dbms_output.put_line(aboutinfo);
end;

goto

declare
  v_result int :=1;
begin
  for v_result in 1..100 loop
    if v_result=2 then
      goto endpoint;
     end if;
     dbms_output.put_line(v_result);
  end loop;
  <<endpoint>>
  dbms_output.put_line('over');
end;

九、游标

显式游标

declare
  cursor cur_stu is select sno,sname,sage from student where sdept='CS';
  type record_stu is record
  (
    var_sno student.sno%type,
    var_sname student.sname%type,
    var_sage student.sage%type
  );
  stu_row record_stu;
begin
  open cur_stu;
  fetch cur_stu into stu_row;
  while cur_stu%found loop
    dbms_output.put_line(stu_row.var_sname||'id:'||stu_row.var_sno||'age:'||stu_row.var_sage);
    fetch cur_stu into stu_row;
  end loop;
end;

隐式游标
oracle会为执行的sql自动创建一个隐式游标。该游标是内存中处理该语句的工作区域。隐式游标主要是处理数据操纵语句的执行结果，在使用隐式游标的属性时，需要在前面加上隐式游标的默认名称sql。

begin
  update student set sage=sage+1 where sdept='CS';
 if sql%notfound then
  dbms_output.put_line('not found any student');
 else
  dbms_output.put_line(sql%rowcount||'students has changed');
 end if;
end;

十、异常处理

编译时异常
运行时异常
异常处理语法

declare
  v_vara varchar(1);
  v_varb varchar(4):='java';
begin
  v_vara:=v_varb;
  dbms_output.put_line('here...');
exception
  when value_error then
    dbms_output.put_line('value error');
end;

用户自定义异常

declare
  v_data number;
  v_myexp exception;
begin
  v_data:=&inputdata;
  if v_data > 100 then
    RAISE v_myexp;
  end if;
  dbms_output.put_line(v_data);
exception
  when others then
    Dbms_Output.put_line('MY EXCEPTION');
end;

十一、存储过程的调用

create or replace procedure my_proc2 is
begin
  insert into student(sno,sname,sage) values('201215202','LISI',21);
  dbms_output.put_line('insert success');
end my_proc2;

–调用存储过程

begin
  my_proc2;
end;

–删除存储过程

drop procedure my_proc2;

十二、函数

函数一般用于计算或者表示某种功能，函数可以接收参数，有的函数也可以没有参数。但是函数必须要有返回值。

create or replace function get_avg_age(param_sdept varchar2) return number is avg_age_result number;
begin
  select avg(sage) into avg_age_result from student where trim(sdept)=param_sdept;
  return(round(avg_age_result,2));
exception
  when no_data_found then
    dbms_output.put_line('sdept not found');
    return (0);
end;

–调用函数

declare
  avg_age number;
begin
  avg_age:=get_avg_age('CS');
  dbms_output.put_line(avg_age);
end;

–删除函数

drop function get_avg_age;

十三、触发器

触发器trigger的执行是事件触发，比如当对一个表进行操作（insert,delete,update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则。触发器可以从DBA_TRIGGERS,USER_TRIGGERS数据字典中查到。sql3的触发器是一个能由系统自动执行对数据库修改的语句。

语句级触发器：针对一条DML语句引起的。无论操作数据影响多少行，触发器只会执行一次。
替换触发器：定义在视图上的触发器，当用户操作视图时执行替换触发器，实现对基表的操作。
系统事件触发器

语句触发器
–定义语句触发器

create or replace trigger tri_student before insert or update or delete on student
declare
       var_tag varchar2(10);
begin
       if inserting then
         var_tag:='insert';
       elsif updating then
         var_tag:='update';
       elsif deleting then
         var_tag:='delete';
       end if;
       insert into student_log values(var_tag,sysdate);
end tri_student;

–创建表

create table student_log
(
       operate_tag varchar(10),
       operate_time date
)

替换触发器
–创建视图

create view view_student_sc
as select s.sno,s.sname,sc.cno,sc.grade from student s,sc where s.sno=sc.sno;

–定义替换触发器

create or replace trigger tri_insert_view
instead of insert
on view_student_sc
for each row
begin
  insert into student(sno,sname) values(:new.sno,:new.sname);
  insert into sc(sno,cno,grade) values(:new.sno,:new.cno,:new.grade);
end tri_insert_view;

–插入数据

select * from view_student_sc;
insert into view_student_sc values('202315206','zhaoliu','1',100);

十四、程序包

程序包的包头即为规范，用来规定程序中使用哪些变量、函数、存储过程等
–包声明

create or replace package pack_student is
 function fun_avg_sage(num_sdept varchar2) return number;
 procedure pro_regulage_sage(num_sdept varchar2);
end pack_student;

–包主体

create package body pack_student is
 function fun_avg_sage(num_sdept varchar2) return number is
   num_avg_sage number;
begin
   select avg(sage) into num_avg_sage from student where trim(sdept)=num_sdept;
   return(num_avg_sage);
exception
   when no_data_found then
     dbms_output.put_line('no this sdept');
     return 0;
   end fun_avg_sage;
   procedure pro_regulage_sage(num_sdept varchar2) is
   begin
     update student set sage=sage+1 where trims(sdept)=num_sdept;
     end pro_regulage_sage;
end pack_student;

十五、索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和对应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

select * from student where sname like 'zhaoliu%';

–创建索引

create index idx_sname on student(sname);

–删除索引

drop index idx_sname;

十六、用户管理

创建用户

create user guanguan identified by 123456
	default tablespace users
	temporary tablespace temp;

修改用户在表空间上的磁盘上限

alter user guanguan quota 20m on users; --修改用户在users表空间上的磁盘空间为20m

–修改用户密码

alter user guanguan identified by 12345678;

–解锁被锁定的用户

alter user guanguan account unlock;

–删除用户

drop user guanguan cascade;--cascade级联删除，一并删除用户拥有的对象

授权

系统权限
对象权限

–系统授权

grant connect to guanguan  [with admin option] ;--把连接权限给guanguan用户 with admin option可将权限授予其他人

–撤销用户权限

revoke connect from guanguan;

–对象授权

grant select,insert,delete,update on scott.student to guanguan;

–回收对象权限

revoke delete,update on scott.student from guanguan;

十七、角色管理

角色是一个独立的数据库实体，它包括一组权限
角色包括一个或者多个权限的集合
–创建角色

create role hr identified by 123456;

–给角色授权

grant create view,create table to hr;

–把角色授权给用户

grant hr to guanguan;

–查看角色所包含的权限

select * from role_sys_privs where role='hr';--role_sys_privs是存放角色权限的数据字典

–删除角色

drop role hr;

十八、资源配置profile文件

使用profile可以实现：
1.账户锁定：连续两次输错密码
2.账户锁定时间：30天警告，第33天oracle强制用户修改密码
3.密码历史：密码的可重复次数和可重用时间
4.密码的复杂程度：强制密码具有一定的复杂度，比如不能和用户名相同
管理用户资源
一次会话使用的cpu时间
用户的并发会话数
会话总计连接时间
–建立profile文件

create profile password_life_time limit password_life_time 30 password_grace_time 3;

十九、数据库控制

事务
	原子性
	一致性
	隔离性
	持久性
	事务提交：
		显式提交 commit
		自动提交 set autocommit on
		隐式提交 程序中断或关闭会话、关闭数据库等操作，也会完成一次事务提交
	rollback 回滚事务 撤回，让数据恢复到修改之前的状态
		设置回退点 savepoint sp1;
		回滚到回退点 rollback to sp1;
锁控制并发
	1.共享锁
		通过数据存取的高并行性来实现。如获得一个共享锁，用户就可以共享相同的资源，共享锁使用在读取数据的维度。
	2.独占锁
	 防止共同改变相同的资源。如果一个事务获得一个独占锁。那么只能当前事务进行修改数据。其他不能修改数据，但可以读取数据。
死锁
	当两个用户或多个用户等待其中一个被锁住的资源时，就有可能发生死锁现象
	oracle会自动进行定期搜索，通过回滚死锁中造成死锁的一个语句来解决死锁
	用户在设计应用程序时要尽量避免死锁