plsql 语言

最新推荐文章于 2024-05-23 18:28:57 发布

weixin_34405332

最新推荐文章于 2024-05-23 18:28:57 发布

阅读量110

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/wangzilong/blog/795591

版权

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

组合索引前面放的值要重复值少一点，性能高

plsql 过程化查询
块结构

1 匿名的plsql

        没有取名字，以块的结构在使用，注意用来测试，不能被调用
        语法：以declare 开头，也是声明部分，是可选可不选的，当有变量声明的时候需啊哟有declare，没有变量的时候可以不写declare，变量，常量，数据类型，游标写在declare后面
,declare 后面跟上执行语句，以begin 开头
declare
        变量声明，
        常量声明(constant)，
        begin
                赋值，
                sql（select，dml，ddl，dcl）
                结构/分支/循环
        exception when not exis then -- 程序的异常处理
        end；

2 有名的plsql

        有自己的名字，能被程序调用
        存储过程，函数，包，触发器
        语法：
        create or replace procdure <> (name in|out type,****) is|as
        声明
        declare
                begin
                        sql语句
                exception
                end procude_name
        函数：

create or replace procdure <> (name in|out type,****) is|as
        声明
        declare
                begin
                        sql语句
                exception
                end procude_name
        函数：
                create or replace function <> (name in|out type,***)
                return type
                is|as
                declare
                        begin
                                return ***
                        excepion
                                return ***
                        end function_name

        包：过程和包封装在一起
                包头：声明变量常量，过程，函数
                        create or replace package <>
                        is|as
                        变量，常量，过程，函数，游标（共有的）
                        end package_name

                包体：create or replacey package day <> is|as
                变量，常量，过程，函数，（私有的）
                        begin
                                sql
                        excepion

end

触发器：trigger

DML触发：insert，update，delete 的时候触发某个动作
替代：instead of,create,alter,drop
事物:database,schema,logon|login

                create or replace trigger <>
                [insert|update***]
                begin
                        DML
                END trigger_name

____________________________

1 变量的声明
var datatype；变量必须是字符开头的，不可以包含特殊字符，可以字符或者数组下划线，不能超过30个字符，必须是分号结束；
char(); 32767个字符
varchar2();变长的字符类型，长度可以达到32767个字符
number：包括浮点性的字符性，可以达到30位
int:整型
long：长整型，2G-1 个长度
clob：二进制字符长度，也叫做大对象字符类型4G-1个长度
blog：多媒体字符，4G-1
date：存储年月日十分秒，微秒
rowid:指的是数据块在数据库存储的位置
注意：变量在声明的时候可以赋值：
        var number；
        var number := 10;
        var number := &e; &e 需要在键盘上手动输入
        var varchar2(20);
        var varchar2(20) := 'hello word';
        var varchar2(20) := '&abc';

var date today :=sysdate|to_date('2016-11-18','yyyy-mm-dd');

%type 匹配数据类型
var v_name emp.ename%type; 就是匹配数据库类型

SQL> declare
  2  v_sal emp.sal%type;
  3  v_name emp.ename%type;
  4  begin
  5  select a.ename,a.sal into v_name,v_sal from emp a where a.empno = 7788;
  6  dbms_output.put_line(v_name||' '||v_sal);
  7  end;
  8  /
SCOTT 3000

PL/SQL procedure successfully completed.

%rowtype 匹配所有的列属性

SQL> declare
  2  v_emp emp%rowtype;
  3  begin
  4  select a.* into v_emp from emp a where empno = 7788;
  5  dbms_output.put_line(v_emp.ename);
  6  dbms_output.put_line(v_emp.sal);
  7  end;
  8  /
SCOTT
3000

自定义数据类型,符合变量

type type_name is record(name1,name2,**)

上面的是全部匹配表中所有字段，那如果我只要匹配部分字段，怎么办了？

SQL> declare 
  2  type empsal is record (vname varchar(20),vsal number(6));
  3  v_name_sal empsal;
  4  begin
  5  select a.ename,a.sal into v_name_sal from emp a where a.empno = 7788;
  6  dbms_output.put_line(v_name_sal.vname||' '||v_name_sal.vsal);
  7  end;
  8  /
SCOTT 3000

PL/SQL procedure successfully completed.
# 自定义数据类型，也叫做符合类型，当然自定义类型里面的类型如果不自动原表中字段是什么类型，可以在里面使用%type

2 游标

2.1 隐式游标

由oracle内部自动定义的游标，叫做隐式游标。隐式游标通常是以sql开头的，一般由DML 调用，用来返回某个值。

sql%rowcount 统计记录中的总行数

sql%found 用来判断数据在表中是否存在。值是true

sql%notfound 用来判断是否找不到，返回值为true

sql%isopen 用户判断游标是否已经打开

2.2 显式游标

# loop 循环
SQL> declare
  2  v_name emp.ename%type;
  3  v_sal emp.sal%type;
  4  cursor cur_emp is
  5  select ename,sal from emp;
  6  begin
  7  if cur_emp%isopen then
  8  close cur_emp; 
  9  end if;
 10  open cur_emp;                           # 打开游标
 11  loop
 12  fetch cur_emp into                      # 取数据
 13  v_name,v_sal;
 14  exit when cur_emp%notfound;
 15  dbms_output.put_line(v_name||'    '||v_sal);
 16  end loop;
 17  close cur_emp;                          # 关闭游标
 18  end;

# 下面的 用自定义类型
  1  declare
  2  cursor cur_dept is
  3  select * from dept;
  4  mess cur_dept%rowtype;
  5  begin
  6  if cur_dept%isopen then
  7  close cur_dept;
  8  end if;
  9  open cur_dept; loop
 10  fetch cur_dept into mess;
 11  exit when cur_dept%notfound;
 12  dbms_output.put_line(mess.deptno||'  '||mess.dname);
 13  end loop;
 14  close cur_dept;
 15* end;
SQL> /
10  ACCOUNTING
20  RESEARCH
30  SALES
40  OPERATIONS
50  DBA
60  SA
70  SA1
71  SA2
72  SA3
80  4G
90  IOS

# 用for循环，不需要打开游标，自动抓取
SQL> declare
  2  begin
  3  for i in 1 .. 10 loop
  4  dbms_output.put_line(i);
  5  end loop;
  6  end;
  7  /
1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.

SQL> begin
  2  for i in (select * from dept) loop
  3  dbms_output.put_line(i.deptno||'   '||i.dname);
  4  end loop;
  5  end;
  6  /
10   ACCOUNTING
20   RESEARCH
30   SALES
40   OPERATIONS
50   DBA
60   SA
70   SA1
71   SA2
72   SA3
80   4G
90   IOS

PL/SQL procedure successfully completed.

# while 循环
  1  declare
  2  v1 number :=1;
  3  begin
  4  while v1 < 11 loop
  5  v1 := v1+1;
  6  dbms_output.put_line(v1);
  7  end loop;
  8* end;
SQL> /
2
3
4
5
6
7
8
9
10
11

PL/SQL procedure successfully completed.

游标后面还可以传值，cursor cur_emp (v_name,****) select ****[这里可以更上for update of column wait|no wait 锁]

当有事务执行这个操作的时候，可以等待或者不等待。

游标用完要关闭，游标存放在pga中，如果不关闭的话，pga内存会一直会占用。

SQL> l
  1  declare
  2  cursor cur_d is
  3  select deptno,dname from dept;
  4  d_mess cur_d%rowtype;
  5
  6  cursor cur_e (v_deptno number) is
  7  select ename from emp where deptno = v_deptno;
  8  e_mess cur_e%rowtype;
  9
 10  begin
 11  if cur_d%isopen then
 12  close cur_d;
 13  end if;
 14  open cur_d;
 15  loop
 16  fetch cur_d into d_mess;
 17  exit when cur_d%notfound;
 18  dbms_output.put_line(d_mess.dname||'------');
 19  if cur_e%isopen then
 20  close cur_e;
 21  end if;
 22  open cur_e(d_mess.deptno);
 23  loop
 24  fetch cur_e into e_mess;
 25  exit when cur_e%notfound;
 26  dbms_output.put_line('------'||e_mess.ename);
 27  end loop;
 28  close cur_e;
 29  end loop;
 30  close cur_d;
 31* end;
# 游标嵌套循环传参数

3 动态plsql

execute immediate ‘’；

  1  begin
  2  execute immediate 'create table tmp1 (int number)';
  3* end;
SQL> /

PL/SQL procedure successfully completed.
# 程序中建表必须要用动态sql


SQL> begin      
  2  execute immediate 'insert into tmp1 (int) values (:1)' using 10;
  3  commit;
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> select * from tmp1;

       INT
----------
	10
# 动态sql 使用站位符

alter index index_name rebuild

declare
cursor cur_index is
select index_name from user_indexes;
v_index cur_index%rowtype;
begin
if cur_index%isopen then
close cur_index;
end if; 
open cur_index;
loop
fetch cur_index into v_index;
exit when cur_index%notfound;
dbms_output.put_line(v_index.index_name);
execute immediate 'alter index '|| v_index.index_name||' rebuild';
end loop;
close cur_index;
end;
/
SYS_C0010994
PK_EMP
PK_DEPT

PL/SQL procedure successfully completed.

碎片整理

alter table table_name enable row movement;

alter table table_name shrink space ;

alter table table_name disable row movement;

declare
cursor cur_index is
select table_name from user_tables;
v_index cur_index%rowtype;
begin
if cur_index%isopen then
close cur_index;
end if; 
open cur_index;
loop
fetch cur_index into v_index;
exit when cur_index%notfound;
dbms_output.put_line(v_index.table_name);
execute immediate 'alter table '|| v_index.table_name||' enable row movement';
execute immediate 'alter table '|| v_index.table_name||' shrink space';
execute immediate 'alter table '|| v_index.table_name||' disable row movement';

end loop;
close cur_index;
end;
/
EMPNEW
TMP1
EMP1
DEPT1
TMP_WZL_20161125_1
SALGRADE
BONUS
EMP
DEPT

PL/SQL procedure successfully completed.

批量删除表，闪会表

declare
cursor cur_index is
select table_name from user_tables;
v_index cur_index%rowtype;
begin
if cur_index%isopen then
close cur_index;
end if; 
open cur_index;
loop
fetch cur_index into v_index;
exit when cur_index%notfound;
dbms_output.put_line(v_index.table_name);
execute immediate 'drop table '|| v_index.table_name;

end loop;
close cur_index;
end;
/
EMPNEW
TMP1
EMP1
DEPT1
TMP_WZL_20161125_1
SALGRADE
BONUS
EMP
DEPT

PL/SQL procedure successfully completed
# 所有表都已经删除

declare
cursor cur_index is
select object_name from recyclebin where
to_date(droptime,'yyyy-mm-dd:HH24:MI:SS') > sysdate -1; 
v_index cur_index%rowtype;
begin
if cur_index%isopen then
close cur_index;
end if; 
open cur_index;
loop
fetch cur_index into v_index;
exit when cur_index%notfound;
dbms_output.put_line(v_index.object_name);
execute immediate 'flashback table "'|| v_index.object_name||'" to before
drop';

end loop;
close cur_index;
end;
/

4 type

type type_name is table of number;

5 bulk collect into v_1

bulk connect 是用列plsql 表，数据into 里面就相当与一个数组，可以放入多条数据，可以不使用游标

declare
type v_type is table of number;
v_mess v_type;
begin
execute immediate 'select sal  from emp where deptno = 10' bulk collect into v_mess;
dbms_output.put_line(v_mess(1));
dbms_output.put_line(v_mess(2));
dbms_output.put_line(v_mess(3));
dbms_output.put_line(v_mess.count);
end;
/
3675
7500
1950
3

6 关联游标

type r1 is ref cursor

  1  declare
  2  type cur_type is ref cursor;
  3  v1 cur_type;
  4  v_ename emp.ename%type;
  5  v_sal emp.sal%type;
  6  begin
  7  open v1 for 'select ename,sal from emp';
  8  loop
  9  fetch v1 into v_ename,v_sal;
 10  exit when v1%notfound;
 11  dbms_output.put_line(v_ename||'  '||v_sal);
 12  end loop;
 13* end;
SQL> /
SMITH  800
ALLEN  1600
WARD  1250
JONES  2975
MARTIN	1250
BLAKE  2850
CLARK  3675
SCOTT  3000
KING  7500
TURNER	1500
ADAMS  1100
JAMES  950
MILLER	1950

PL/SQL procedure successfully completed.

关联游标的优点就是不在申明部分写死游标，只需要申明一个游标类型，在begin 部分动态的取定义游标即可

7 分支判断

7.1 if ……then ……end if;

7.2 if ……then ……else ……end if;

7.3 if ……then ……elsif ……then ……else ……end if;

SQL> declare
  2  type v_type is ref cursor;
  3  v_name emp.ename%type;
  4  v_sal emp.sal%type;
  5  v_cur v_type;
  6  begin
  7  open v_cur for 'select ename,sal from emp';
  8  loop
  9  fetch v_cur into v_name,v_sal;
 10  exit when v_cur%notfound;
 11  if v_sal < 1500 then
 12  dbms_output.put_line(v_name||' your sal is too low,please hurry up');
 13  elsif v_sal >= 1500 and v_sal <3000 then
 14  dbms_output.put_line(v_name||' your sal is good');
 15  else
 16  dbms_output.put_line(v_ename||' you are tuhao');
 17  end if;
 18  end loop;
 19  close v_cur;
 20  end;
 21  /
dbms_output.put_line(v_ename||' you are tuhao');
                     *
ERROR at line 16:
ORA-06550: line 16, column 22:
PLS-00201: identifier 'V_ENAME' must be declared
ORA-06550: line 16, column 1:
PL/SQL: Statement ignored


SQL> l16
 16* dbms_output.put_line(v_ename||' you are tuhao');
SQL> c/v_ename/v_name
 16* dbms_output.put_line(v_name||' you are tuhao');
SQL> l
  1  declare
  2  type v_type is ref cursor;
  3  v_name emp.ename%type;
  4  v_sal emp.sal%type;
  5  v_cur v_type;
  6  begin
  7  open v_cur for 'select ename,sal from emp';
  8  loop
  9  fetch v_cur into v_name,v_sal;
 10  exit when v_cur%notfound;
 11  if v_sal < 1500 then
 12  dbms_output.put_line(v_name||' your sal is too low,please hurry up');
 13  elsif v_sal >= 1500 and v_sal <3000 then
 14  dbms_output.put_line(v_name||' your sal is good');
 15  else
 16  dbms_output.put_line(v_name||' you are tuhao');
 17  end if;
 18  end loop;
 19  close v_cur;
 20* end;
SQL> /
SMITH your sal is too low,please hurry up
ALLEN your sal is good
WARD your sal is too low,please hurry up
JONES your sal is good
MARTIN your sal is too low,please hurry up
BLAKE your sal is good
CLARK you are tuhao
SCOTT you are tuhao
KING you are tuhao
TURNER your sal is good
ADAMS your sal is too low,please hurry up
JAMES your sal is too low,please hurry up
MILLER your sal is good

7.4 case

case 条件 when 1 then ；when 2 then ；end case；

case when 条件1 then ；when 条件2 then ；end case；

7.5 条用程序

exec <>

call <>

8 异常

8.1 预定义异常

由oracle内部已经定义好的异常，在用户的使用过程中遇到某种错误，由oracle内部自动引发的异常

（异常名字，异常的代码，异常的信息）

SQL> select text from dba_source where name = 'STANDARD' AND TEXT LIKE '%EXCE%';

TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    pragma EXCEPTION_INIT(CURSOR_ALREADY_OPEN, '-6511');
    pragma EXCEPTION_INIT(DUP_VAL_ON_INDEX, '-0001');
    pragma EXCEPTION_INIT(TIMEOUT_ON_RESOURCE, '-0051');
    pragma EXCEPTION_INIT(INVALID_CURSOR, '-1001');
    pragma EXCEPTION_INIT(NOT_LOGGED_ON, '-1012');
    pragma EXCEPTION_INIT(LOGIN_DENIED, '-1017');
# 例如下面的这个错误报错编码1017，在上面就可以看到是登录异常
SQL> CONN scott/tigerr
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

8.2 异常错误处理方法

bash-4.1$ oerr ora 1017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:
# 在oracle 用户下执行上面的oerr 命令就可以看到解释，原因，处理方法

8.3 非预定义异常

（异常的代码，异常的信息，没有异常的名字）

e1 exception; 自定义异常的名字

pragma exception_init(e1,942);绑定异常

  1  declare
  2  e1 exception;
  3  pragma exception_init(e1,-942);
  4  begin
  5  execute immediate 'select * from dept ';
  6  exception
  7  when e1 then
  8  dbms_output.put_line('we not have this table');
  9* end;
SQL> /
we not have this table

PL/SQL procedure successfully completed.

8.4 自定义异常

用户在书写程序的时候让程序在某种情况下抛出异常

自定义异常代码从-20000 到 -20999

（没有异常信息，没有异常名，异常代码）

e2 exception

pragma exception_init(e2,-20001);

raise e2;

SQL> declare 
  2  v1 number :=1;
  3  v2 number :=0;
  4  begin
  5  v1 := v1/v2;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 5
# 上面报错了，我们下面去查一下1476 异常是什么原因和名字叫什么
SQL> select text from dba_source where name = 'STANDARD' AND TEXT LIKE '%1476%';

TEXT
--------------------------------------------------------------------------------
    pragma EXCEPTION_INIT(ZERO_DIVIDE, '-1476');

# 报错名字叫ZERO_DIVIDE 由于除数为0导致的，我们下面做一下异常抛出
  1  declare
  2  v1 number :=1;
  3  v2 number :=0;
  4  begin
  5  v1 :=v1/v2;
  6  exception
  7  when ZERO_DIVIDE then
  8  dbms_output.put_line('zeor');
  9* end;
SQL> /
zeor

PL/SQL procedure successfully completed.

  1  declare
  2  v_name emp.ename%type;
  3  v_sal emp.sal%type;
  4  e1 exception;
  5  pragma exception_init(e1,'-20011');
  6  cursor cur_e is select ename,sal from emp;
  7  begin
  8  open cur_e ;
  9  loop
 10  fetch cur_e into v_name,v_sal;
 11  exit when cur_e%notfound;
 12  if v_sal < 3000 then
 13  raise e1;
 14  end if;
 15  exception
 16  when e1 then
 17  dbms_output.put_line('is too small')
 18  end loop;
 19  close cur_e;
 20* end;

raise_application_error('skfjsdlfdl','-20034');

转载于:https://my.oschina.net/wangzilong/blog/795591