有名PLSQL

最新推荐文章于 2024-09-24 08:39:48 发布

weixin_34354173

最新推荐文章于 2024-09-24 08:39:48 发布

阅读量89

点赞数

文章标签：数据库 python 运维

原文链接：https://my.oschina.net/liubaizi/blog/798265

版权

2019独角兽企业重金招聘Python工程师标准>>>

有名PLSQL

1、存储过程 ---用来完成用户的某种特定操作，如DDL，DML 可以被用户调用，DBA的某些操作，开发人员的调用
create or replace procedure <> [(in out in out)]
is|as
.. //申明变量，常量，游标
begin
..
exception //可选
..
end [procedurename];
/

exec procedure; //调用过程
show error;//查看我们的错误
过程在第一次运行的时候，会被分析，后面直接被调用

SQL> create or replace procedure psal
  2  is 
  3  vsal number;
  4  begin
  5  select sal into vsal from emp where empno=7788;
  6  dbms_output.put_line(vsal);
  7  end;
  8  /

Procedure created. //编译过程

SQL> 
SQL> exec psal;  //调用我们的过程
3000

PL/SQL procedure successfully completed.

SQL>

外部传参


SQL> l
  1  create or replace procedure psal
  2  is
  3  vsal number;
  4  vempno number:=&epo;
  5  begin
  6  select sal into vsal from emp where empno=vempo;
  7  dbms_output.put_line(vsal);
  8* end;
SQL>

用in进行传值

create or replace procedure psal(vempno in number)  // 用in 进行传值
is
vsal number;
begin
select sal into vsal from emp where empno=vempo;
dbms_output.put_line(vsal);
end;
/

定义默认值

create or replace procedure psal(vempno  number default 7788)
is
vsal number;
vename varchar2(30)
begin
select sal ,ename into vsal,venam  from emp where empno=vempo;
dbms_output.put_line(vsal||' '||vename);
end;
/

游标在过程中

create or replace procedure psal(vempno  number )
is
cursor c1 is select sal,ename from emp where deptno=vempno;
vc1 c1%rowtype;
begin
open c1;
fetch c1 into vc1;
exit when c1%notfound
dbms_output.put_line(vc1.sal||' '||vc1.ename);
end loop;
close c1;
end;
/

out：输出的变量

create or replace procedure pename(vempno number, vsal out number)
is 
begin
select sal into vsal from emp where empno=vempno;
dbms_output.put_line(vsal);
end;
/


SQL> declare                  //用匿名函数去调用我们的有名函数
  2  vsal number;
  3  begin
  4  pename(7788,vsal);
  5  end;
  6  /
3000

create or replace procedure qename(vempno number, vsal out number,vename out varchar2)
is 
begin
select sal into vsal from emp where empno=vempno;
end;
/



SQL> l
  1  declare
  2  v1 number;
  3  v2 varchar2(30);
  4  begin
  5  qename(7788,v1,v2);
  6  dbms_output.put_line(v1||' '||v2);
  7* end;
SQL> /
3000

PL/SQL procedure successfully completed.

in out //既是输入也是输出

电话号码的格式输出

SQL> @ /tmp/tel.sql

Procedure created.

SQL> l
  1  create or replace procedure ptel(v_tel in out varchar2)
  2  is
  3  begin
  4  v_tel:='['||substr(v_tel,1,3)||']-'||substr(v_tel,-8);
  5* end;

 匿名调用
 SQL> l
  1  declare
  2  v1 varchar2(100):='01234567891';
  3  begin
  4  ptel(v1);
  5  dbms_output.put_line(v1);
  6* end ;
SQL> /
[012]-34567891

PL/SQL procedure successfully completed.

>>>desc user_procedures; //查看过程
>>>select name,text from user_source where name='PSAL' //查看源代码
# 自己编写的过程，只有自己可以调用如果其他用户要调用，必须有exec权限
>>>show parameter group //查看我们的告警日志
background_dump_dest // 定义告警日志存放的位置
ho ls /u01...... 路径 xxx.log //查看告警日志
>>>select value from v$parameter where name=instance_name; //查找实例名
>>>select value || '/alter_'||'(select value from v$parameter where name=instance_name)' from v$parameter where name='background_dump_dest' //怎样找到我们的告警日志
>>>select name,status from v$datafile where status in('offline','recovery'); // 查看数据文件中状态不正常的
>>>alter database datafile <> online; //恢复数据文件

drop procedure <> ； //删除过程
alter procedure <> compile;//对无效的对象进行编译
$ORACLE——HOME/rdbms/admin/utlrp.sql ;//对所有无效的对象进行编译，一般用在数据库有迁移，有升级的时候

2、函数---用来返回值
create or replace function <> [in |out| in out]
return <datatype>
is|as
.. //定义常量变量，一般不是用游标
begin
..
exception
...
end functionname;
/

使用select调用函数

7556--ename 输入员工编号，显示名字

创建函数

create or replace function fname return varchar2 
is
vempno number:=7788;
vename varchar2(30);
begin
select ename into vename from emp where empno=vempno ;
return(vename);
end fname;

SQL> select fname from dual; //使用select调用函数

FNAME
--------------------------------------------------------------------------------
SCOTT

create or replace function pname (vempno number,vename varchar2)return number 
is
vsal number;
begin
select ename into vename from emp where empno=vempno  and ename=vename;                                                           
return(vsal);
end fname;

>>select pname(7788,'SCOTT')

out 参数


create or replace function fsal  (vempno number,vename varchar2 out varchar2)return varchar2
is
vsal number;
begin
select  sal,ename into vsal,vename from emp where empno=vempno  ;
return(vsal||' '||vename);
end fsal;


匿名调用
declare
v1 varchar2(30);
begin
v1:=fsal(7788,v1);
dbms_output.put_line(v1);
end
/

in out

有名定义函数

create or replace function fsal(vempno in out number,vename out varchar2)return varchar2 
is
vsal number;
begin
select  sal,ename,empno into vsal,vename,vempno from emp where  empno=vempno ;
return(vsal||' '||vename);
end fsal;


匿名调用
declare
v1 number :=7788;
v2 varchar2(30);
v3 varchar2(100);
begin 
v3:=fsal(v1,v2);
dbms_output.put_line(v3);
end;
/

desc <> //函数的描述
user_source
drop function <> //删除函数

3、包---过程和函数
作用1：包内部的存储过程和函数都调用到内存中，减少磁盘的读写
作用2：有利于DBA的管理

包头：声明过程和函数
包体：过程和函数的执行体

create or replace package<> //包头的定义
is |as
......... //变量常量，游标
end;

create or replace body package<> //包体的定义
is |as
....
exception
.....
end;

一个过程包

create or replace package p1
is
procedure psal(vempno number);
end
/
create or replace  package body p1
is
procedure psal(vempno number)
as
vsal number;
begin
select sal into vsal from emp where empno=vempno;
dbms_output.put_line(vsal);
end  psal;
end p1;
/

有过程有函数的包

create or replace package p1
is
procedure psal(vempno number);
procedure pename(vdeptno number);
function psal (vename varchar2) return number;
end;
/
create or replace  package body p1
is
procedure psal(vempno number)
as
vsal number;
begin
select sal into vsal from emp where empno=vempno;
dbms_output.put_line(vsal);
end  psal;
end p1;

procedure pename(vdeptno number)
as 
cursor c1 is select ename ,sal from emp where deptno=vdeptno;
vc1 c1%rowtype;
begin
open c1;
loop
fetch c1 into vc1;
exit when c1%notfound;
dbms_output.put_line(vc1.vsal||' '||vc1.vname);
end loop;
close c1
end pename;
end p1;

function psal(vename varchar2) return number
as
vsal number;
begin 
select sal into vsal from emp where ename=vename;
return vsal;
end psal;
end p1;


>>>desc p1  //可以描述我们的包

>>>exec p1.psal(7788)
>>>exec p1.pename(20);
>>>select p1.psal('KING') from dual;  //调用包中的函数

>>>select text from user_source where name=p1 ;// 查看源代码
>>>spool /tmp/p11.txt    //保存我们的源代码
>>>spool off;
>>>ho cat /tmp/p11.txt

常用的几个包：
DBMS_ROWID：恢复数据用的
DBMS_METADATA:查询到所有的对象的源代码
DBMS_MONITOR：监控我们的数据库
DBMS_OUTPUT:输出
DBMS——REDEFINTION:将普通表和分区表在线相互转换
UTL_FILE：将数据库中的表输入到文本文件
UTL_MAIL:数据库监控过程中，将出现的问题以邮件形式发给你
DBNS_ADDM:实现对数据库统计信息的收集
DBNS_ADVISOR：给数据库的优化提供一些建议向导
raise_application_error:抛出异常
DBMS_AUTO_TASK_ADMIN:自动任务管理
DBNS_DATAPUMP：数据的导出

>>>desc dbms_rowid //查看包的内容

4、触发器

4 触发器

当用户执行操作时，触发器用来完成某个特定的动作

分类：

4.1 DML 触发

当用户执行DML 语句的时候触发器完成某个动作。

insert,update,delete 触发

4.2 替代触发

通常状态下使用到视图中，当用户对视图进行操作的时候遇到视图有限制，而不可能完成，这个时候触发触发器，替代视图完成某种动作。比如：复杂的视图是不可以插入数据的，这个时候如果我们一定要在视图中插入数据就用触发器替代我们取插入数据。

4.3 事件触发

database| schema

用户在关闭数据库或者打开数据库引发的时间，登录退出数据库的时候引发的事件。

4.4 触发时机

after|before

动作之前或者之后触发。对于DML 之前和之后是没有区别的

对于事件触发之前和之后有区别

4.5 触发动作

是DML，DDL，还是事件触发

4.6 触发顺序

语句级触发：满足条件的行只触发一次

行级触发：for each row，满足条件每一行都触发一次

4.7 触发器关键谓词

inserting 表示插入动作

updating 更新动作

deleting 删除动作

：new

比如、：new.sal

：old

比如，：old.sal

4.8 触发器中执行的动作

DML 或者DQL ，不能执行DDL 操作，也不能执行TCL （事物操作，回退，提交）

4.9 触发器语法：

DML 触发

create or replace trigger triggername

before|after insert|or update|or delete of <column>

on tablename for each row

begin

触发器要执行的操作（操作内容不能超过32K）

end；

不跟for each row 默认就是语句级触发

同一个对象下涉及到的触发器不能超过12个

同一个对象中不能存在相同功能的触发器

1、DML 触发：

1 create or replace trigger emp_sal_tirgger
2 before update of sal on emp for each row
3 begin
4 insert into tmp_emp (empno,sal) values(:old.empno,:old.sal);
5 insert into tmp_emp (empno,sal) values(:old.empno,:new.sal);
6* end;
SQL> /
SQL> update emp a set a.sal=5500 where a.empno = 7788;

1 row updated.
SQL> select * from tmp_emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7788 5000

7788 5500
#上面触发器已经工作了，记录下列之前和之后的数据
SQL> rollback;

Rollback complete.

SQL> select * from emp where empno = 7788;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7788 SCOTT ANALYST 7566 19-APR-87 5000
20
# 更新表后回滚，不更新数据，
SQL> select * from tmp_emp;

no rows selected

# 触发器插入的数据也回滚列，触发器的回滚提交都是和被触发对象一起的。

SQL> create or replace trigger emp_trigger
2 before insert or update or delete on emp for each row
3 begin
4 if inserting then
5 dbms_output.put_line('we are insert data');
6 end if;
7 if updating then
8 dbms_output.put_line('we are update date');
9 end if;
10 if deleting then
11 dbms_output.put_line('we are delete data');
12 end if;
13 end emp_trigger;
14 /
# 使用触发器谓词
SQL> update emp a set a.sal = 4000 where a.empno = 7788;
we are update date

1 row updated.
# 谓词生效

更新sal 将修改前的sal和修改后的sal放到vsal中

create or replace trigger tsal
before update of sal on emp for each row
begin
insert into vsal values(:old.sal,:new.sal);
end;
/

create or replace tigger temp
before update or insrrt or delete on emp for each row
begin
if inserting then
dbms_output.put_line('insert rows');
elsif updating then
dbms_output.put_line('update data rows');
elsif deleting then
dbms_output.put_line('delete rows');
end if;
end;
/

b.替代触发只有行级簇发，默认为行级触发

语法：
create or replace trigger <>
instead of insert or |update |delete of <column> on <viewname> for each row
begin
....//触发器要执行的动作
end;
/

create or replace view v1 as select deptno ,avg(sal) vsal  from emp group by deptno;  //创建视图

create or replace trigger tv1    //
instead of update on v1
begin
update  emp set sal=:old.vsal+1;
end;
/

>>>update v1 set vsal=vsal+1;  //有了触发器，可以改视图了

create or replace trigger tv1
instead of update  or insert or delete on v1   //多种情况最好不要写到一起
begin
.........
update  emp set sal=:old.vsal+1;
end;
/

c.事件触发
----database 级别
当用户关闭数据库，打开数据库 ,logon,logoff,shutdown,startup
----schma级别：只针对一个用户，只有创建这个触发的这个用户，才能引发

记录用户登录的信息到我们的logt表中
create or replace trigger tdb
after startup on database
begin
insert into logt values(sysdate,ora_login_user,ora_database_name,'STUP');
end;
/

记录用户的关机信息到我们的logt表中
create or replace trigger sdn
before shutdown on database
begin
insert into logt values(sysdate,ora_login_user,ora_database_name,'SHUTDOWN');
end;
/

记录登录信息
create or replace trigger tlog
after logon/logoff on database
begin
insert into logt values(sysdate,ora_login_user,ora_client_ip_address);
end;
/

d、ddl---不让用户删我们的表
drop,alter

禁止scott用户删表
cerate or replace trigger tdrop
before drop or alter on scott.schema
begin
raise_application_error(-20001,'object is not drop');
end;
/

非工作时间不能做DML操作

create or replace trigger tdrop
before insert or update or delete on emp
begin
if to_char(sysdate,'day') in('satarday','sunday')then
raise_application_error() ;
elsif to_char(sysdate,'hh24:mi') between ‘18:00 and 8:00’ then
raise_application_error() ;
end if;
end;
/

怎样限制只有某些用户对表有操作权限
用户登录时在ora_login_user in ()有记录

只有DBA用户有权修改表结构，其他用户不可以修改表结构即alter操作
if ora_login__user<>'sys' then
raise ....
end if；

>>>desc user_triggers //触发器查看
>>>select trigger_name,status from user_triggers ;
>>>alter trigger <> disable ;//禁用触发器

转载于:https://my.oschina.net/liubaizi/blog/798265