PL/SQL实战积累

comeonwyj 2018-06-01

展开全文

查询空表，一列，select 2013 AS year from dual。

PLSQL输出语句DBMS_OUTPUT.PUT_LINE(***)。

在output可以看到结果，相当于java的system.out.println 和 c++的 cout

但是pl/sql的比较弱，不能实时展示结果，要运行到end才会一次性显示结果。

而且在一个存储过程中不能使用太多次DBMS_OUTPUT.PUT_LINE，由于一次性输出，所以在执行到END之前，输出函数都会将结果保存在缓存区。用太多次会导致缓冲区放不下，会报错哒。

case when ... then ... else ... end 作用类似于decode函数。

CASE sex

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE '其他' END

或者像下面：

SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1      /*sex 1为男生，2位女生*/

                       ELSE NULL
                       END) 男生数,
                COUNT (CASE WHEN sex = 2 THEN 1
                       ELSE NULL
                       END) 女生数
    FROM students GROUP BY grade;

除此之外，在CASE WHEN 里面可以使用EXISTS关键字，例如：

CASE EXISTS(SELECT 1 FROM TABLE1 T1 WHERE T1.COL1=T2.COL1) THEN 1ELSE 0 END,....

select /*+parallel(a,4)*/ * from a 其中/*+parallel(a,4)*/表示多个线程并行执行，能够提高速度。

/*+提示信息*/ 用于SQL语句优化。

sql中的游标用在多行语句中，游标类似于C++中的指针，或者java中的iterator能够一行一行的迭代多行数据。

定义记录类型相当于定义一个含有多个返回值的类。

触发器：当事件被触发时隐式的执行，事件指update，insert，delete...

oracle执行一条字符串sql语句的方法为：

declare

sql varchar2(100);

begin

sql := .....;

execute immediate command;

end;

WITH ... AS 用法

with

sql1 as (select to_char(a) s_name from test_tempa),

sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))

select * from sql1

union all

select * from sql2

union all

select 'no records' from dual

where not exists (select s_name from sql1 where rownum=1)

and not exists (select s_name from sql2 where rownum=1)

遇到长的sql多用with as 分开sql的各个部分也方便后期修改。

如果要加快数据库查询的速度，可以采用不写日志和并发的方式执行，具体方法可以上网找，因为数据库几乎所有操作都会写日志，所以不写日志对于查询数据过大的情况会加快很多。

如果不采用pl/sql直接进入oracle服务器，可以采用指令对数据库进行操作。

PL\SQL在字符串中使用单引号“'”
在字符串中使用“''”，两个连接单引号可以表示一个单引号，第一个单引号表示“跳过”
例如：
select 'select * from table where name = ''yanghuanbin'' ' from dual;
输出结果为：
select * from table where name = 'yanghuanbin'

特殊符号ascii定义
制表符 chr(9)
换行符 chr(10)
回车符 chr(13)

在存储过程中返回多行需要用到游标：

DECLARE

query_sql varchar2(100 );

queryresult varchar2(50 );

bizcursor SYS_REFCURSOR;

type my_record is record(

servid NUMBER(16 ),

custid NUMBER(16 ));

my_rec my_record;

BEGIN

query_sql:= 'select servid,custid from sys_servst where rownum<10' ;

OPEN bizcursor FOR query_sql;

loop

fetch bizcursor into my_rec;

--判读是否提取到值，没取到值就退出

--取到值c_job%notfound 是false

--取不到值c_job%notfound 是true

exit when bizcursor%notfound;

dbms_output.put_line(my_rec.servid|| ' ' ||my_rec.custid);

end loop ;

--关闭游标

close bizcursor;

END;

存储过程中执行动态sql，可以使用execute immediate 如下例子（注意execute immediate 只可以使用在返回一行的sql中）：

--执行动态sql

declare

v_sql varchar2( 100) := 'drop table test_' ||'YF';

begin

execute immediate v_sql;

end;

--execute immediate 还可以用在存储过程的赋值中：

declare

l_cnt varchar2(20 );

begin

execute immediate 'select count(1) from emp'

into l_cnt;

dbms_output.put_line(l_cnt);

end;

在pl/sql的函数中不能调用DDL语句，要使用DDL语句只能写在存储过程中。启动存储过程的方式 call produce_name. 定义无参函数和无参存储过程的时候，函数名(存储过程名)后面不要加括号。但是在调用的时候，无参函数或无参存储过程后面的括号不能省略。
例如：
定义时
CREATE OR REPLACE Procedure truncate_boCheckTable as begin ....
调用时
call truncate_boCheckTable(); 将一个表的数据插入到另一个表：
1.不新建表：
insert into table1 select col1，col2，col3 from table2；
要求插入select的字段在table1中有。
2.新建表：
create table1 as select col1，col2，col3 from table2；
创建的表和select出来的字段是同类型。如果多个表连接速度太慢，我们可以先连接出一个中间表，然后再连接大表。要把大表分开，不要放在一起连接。
exists和子查询都比自然连接快得多。 PL/SQL debug

选中存储过程的名字右键单击.选中"Add debug infomation"选项.会弹出一个提示框,点击"oK"即可

选中存储过程的名字右键单击.选中"Test"选项,进入存储过程的测试页面.如图

按着Ctrl,鼠标单击.进入"view"视图.,找到自己要调试的行.单击设置断点.然后进入测试页面.单击"Start debugger"按钮,或者是"F9",然后点击"run"按钮."step into"跳入."step over"跳过,"step out"跳出.慢慢调试即可

pl/sql order by 默认升序排列 dba_objects/all_objects/user_objects
在oracle数据库中有dba/all/user三种范围的视图，其中dba表示整个数据库，all表示当前用户可见的，user表示当前用户拥有的。所以有dba>=all>=user。具体使用有如下的例子（查数据库中的所有表）：

SELECT * FROM dba_objects t WHERE t.OBJECT_TYPE= 'TABLE';

dba/all/user下有很多的视图，就像下面这样：

可以用dba/all/user 加下划线在pl/sql中直接查看有什么视图，可以查询什么内容。

修改列对应操作：

对字段操作	操作方法
更新字段名	alter table TABLE_NAME rename column column_old to column_new;
添加字段	alter table TABLE_NAME add COLUMN_NAME varchar(10);
删除字段	alter table TABLE_NAME drop column COLUMN_NAME;
添加字段并附值	alter table TABLE_NAME ADD COLUMN_NAME NUMBER(1) DEFAULT 1;
修改字段值	update TABLE_NAME set filedname=value where filedname=value;
修改字段数据类型	alter table tablename modify filedname varchar2(20);

oracle表连接和优化

如果在sql语句没有聚集函数的情况下使用group by ，这个group by 的效果和distinct一样 purge关键字用法：
oracle在用drop删除表之后，会把表放入类似回收站的地方，使用 drop table tableName purge; 这样语句就能将表彻底删除，不放入回收站，但是这样删除表格就不能还原了。在update语句中使用聚集函数，会很慢。例如下面这句sql就执行很慢

UPDATE TMP.TMP_SALESPKG_NUMS_DG T1 SET T1.TIMES=

(SELECT COUNT(1 ) FROM NODS.TO_BIZ_SALESPKG_DG T2 WHERE T2.SERIALNO=T1.SERIALNO AND T2.SALESPKGID=T1.SALESPKGID);

可以先把需要的信息放在另一个新建表里，在关联表存进去。
把上面的变成下面这样：

CREATE TABLE TMP.TMP_SERIPKG_TIMES AS

SELECT T1.SERIALNO,T1.SALESPKGID,COUNT (1) TIMES FROM NODS.TO_BIZ_SALESPKG_DG T1 WHERE

EXISTS(SELECT 1 FROM TMP.TMP_SALESPKG_NUMS_DG T2 WHERE T1.SERIALNO=T2.SERIALNO AND T1.SALESPKGID=T2.SALESPKGID)

GROUP BY T1.SERIALNO,T1.SALESPKGID;

UPDATE TMP.TMP_SALESPKG_NUMS_DG T1 SET T1.TIMES=

(SELECT T2.TIMES FROM TMP.TMP_SERIPKG_TIMES T2 WHERE T1.SERIALNO=T2.SERIALNO AND T1.SALESPKGID=T2.SALESPKGID);

COMMIT;

数据库交、并、差集运算

1.并集的运算

select name from test1

union [all]

select name from test2;

使用union时，默认将对结果进行排序，union all则不进行排序操作，所以会消耗更少的资源；然而，union all将不进行去重的操作~

2.交集的运算

select name from test1

intersect

select name from test2;

Oracle不支持Intersect all关键字！

3.差的运算

select name from test1

minus

select name from test2;

Oracle中差的运算不同于SQL标准，在SQL标准中，我们使用以下函数进行差运算

select name from test1

except [all]

select name from test2;

PL/SQL 三种参数类型，in/out/in out
in参数在函数和存储过程内部不能被修改，也就是in参数不能放在:=左边，
out参数在函数和存储过程中只能被赋值，不能给其他参数赋值，也就是不能放在:=右边，可以将结果带出存储过程或函数。
in out参数在函数和存储过程中，能被赋值也能为别的参数赋值，综合了两者。
其中in 是参数的默认模式。
例子如下：

/*以下存储过程测试用，用来区分 in/out/in out 的用法

以下过程可以交换参数 num3，num4; num1和num2参数只是用来测试。*/

create or replace procedure swaptest(num1 in number,num2 out number ,num3 in out number,num4 in out number)

tmpnum number;

begin

/*以下legal 如果写成 num1:=10 就会编译失败，in参数只能放在:=的右边，

in参数就是常量，在存储过程和函数这种不能修改。*/

tmpnum := num1;

DBMS_OUTPUT.PUT_LINE( 'tmpnum='||tmpnum);

/*以下legal，如果写成tmpnum:=num2 就会编译失败，out参数只能放:=的左边，

也就是说out 只能被赋值，而且赋值之后out参数还能带回到函数或存储过程外部*/

num2 := tmpnum;

DBMS_OUTPUT.PUT_LINE( 'num2='||num2);

/*以下实现num3和num4数字的交换，并且将交换结果传递到存储过程外部，

inout参数可以在:=的左边右边都行*/

num3 := num3+num4;

num4 := num3-num4;

num3 := num3-num4;

end;

--注意，out和inout参数只能传入变量作为参数，所以以下调用会报错：

call swaptest(1 ,2, 3,4 );

调用以上存储过程例子：

declare

num2 number := 20;

num3 number := 30;

num4 number := 40;

begin

swaptest(10,num2,num3,num4);

DBMS_OUTPUT.PUT_LINE( 'num2='||num2);

DBMS_OUTPUT.PUT_LINE( 'num3='||num3);

DBMS_OUTPUT.PUT_LINE( 'num4='||num4);

end;

结果如下：

%TYPE 使用%type给变量设置类型，可以动态的绑定数据类型。
例如：
v_custid sys_cust.custid%type ;
表示将sys_cust表custid字段的类型，赋给v_custid。 复合数据类型：
type type_name is {record|table|varray} 加上%rowtype一共4种方法。
record：可以将多个数据类型定义到一个type_name里。

TYPE record_type IS RECORD(
    var_name1 type
    [,var_name2 type…]
  );

table：建立一个索引数组。
varray：建立一个变长数组。
%rowtype类似%type：可以将一个复合数据类型或一个表的字段类型全部赋值给特定的type_name，
例如：v_cust sys_cust%rowtype就是将 sys_cust表的所有字段类型赋给v_cust类型。 exists与in对比
一般来说exists比in快得多，优化sql的时候建议用exists代替in。
（如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。） 同义词：
私有同义词，只能被当前用户使用：

Create [OR REPLACE] SYNONYM [schema.]synonym_name FOR [schema.]object_name;

公有同义词，可以被所有用户使用：

Create PUBLIC SYNONYM synonym_name FOR [schema.]object_name;

=>操作符的作用：
=>操作符可以在使用存储过程，函数的时候，指定参数名赋值，不按顺序。和python 调用函数的时候，指定参数名进行赋值的作用是一样的。
例如，我创建一个存储过程如下：

create or replace procedure yhb_test(str1 in varchar2,str2 in varchar2 ) as

begin

dbms_output.put_line(str1);

dbms_output.put_line(str2);

end;

则，

call yhb_test('1111' ,'22222');

输出：

call yhb_test(str2=>'1111' ,str1=>'22222');

输出：

defult关键字给参数，表字段设置默认值。
例如：create or replace procedure yhb_test(str1 in varchar2 default 11111 ,str2 in varchar2 ) as 。。。 oracle exit与return区别。
exit ：退出循环。
return ：退出函数或存储过程。 oracle 解锁：

一些ORACLE中的进程被杀掉后，状态被置为"killed"，但是锁定的资源很长时间不释放，

有时实在没办法，只好重启数据库。现在提供一种方法解决这种问题，那就是在ORACLE中杀不掉的，在OS一级再杀。
1.下面的语句用来查询哪些对象被锁：

select object_name,machine,s.sid,s.serial#

from v$locked_object l,dba_objects o ,v$session s

where l.object_id　=　o.object_id and l.session_id=s.sid;

2.下面的语句用来杀死一个进程：

--其中24,111分别是上面查询出的sid,serial#

alter system kill session '24,111';

【注】以上两步，可以通过Oracle的管理控制台来执行。
3.如果利用上面的命令杀死一个进程后，进程状态被置为"killed"，但是锁定的资源很长时间没有被释放，

那么可以在os一级再杀死相应的进程（线程），首先执行下面的语句获得进程（线程）号：

--24是上面的sid

select spid, osuser, s.program

from v$session s,v$process p where

s.paddr=p.addr and s.sid=24

4.在OS上杀死这个进程（线程）：
1)在unix上，用root身份执行命令:
#kill -9 12345（即第3步查询出的spid）
2)在windows（unix也适用）用orakill杀死线程，orakill是oracle提供的一个可执行命令，语法为：
orakill sid thread
其中：
sid：表示要杀死的进程属于的实例名
thread：是要杀掉的线程号，即第3步查询出的spid。例：c:>orakill orcl 12345

oracle 递归

create or replace function yhb_test(n in number) return number

begin

if n= 0 then

return 1 ;

elsif n=1 then

return 1 ;

else

return yhb_test(n-1 )+yhb_test(n-2);

end if;

end;

SELECT yhb_test(5 ) from dual;

左右连接：

--table2是主表

from table1 t1 right join table2 t2 on t1.col1=t2.col1;

--table1是主表

from table1 t1 left join table2 t2 on t1.col1=t2.col1;

(+)在哪边，哪边就是小表，另一边就是主表。

a(+)=b,b所在的表就是主表。

绑定变量：
例子：

declare

　　l_sql varchar2(2000);

　　l_count number;

　　l_param1 varchar2(100);

　　l_param2 varchar2(100);

　　begin

　　l_param1:=’a';

　　l_param2:=’b';

　　l_sql:=’select count(*) into :x from table1 where col_1=:y and col_2=:z ’;

　　Execute Immediate l_sql into l_count using l_param1,l_param2;

　　dbms_output.put_line(l_count);

　　end;

　　:x,:y,:z相当于占位符

绑定变量说明：

变量绑定是指在sql语句的条件中使用变量而不是常量。比如shared pool里有两条sql语句，
select * from tab1 where col1=1;
select * from tab1 where col1=2;
对oracle数据库来说，这是两条完全不同的SQL，对这两条语句都需要进行hard parse。因为oracle会根据sql语句的文本去计算每个字符在内存里的hash值，因此虽然上述两条SQL只有一个字符不一样，oracle根据hash算法在内存中得到的hash地址就不一样，所以oracle就会认为这是两条完全不同的语句。而如果将上述SQL改写成select * from tab1 where col1=:var1;,然后通过对变量var1的赋值去查询，那么oracle对这条语句第一次会进行hard parse，以后就只进行soft parse。假设某条语句被重复执行了几十万次，那么使用bind var带来的好处是巨大的。一个应用程序如果bind var使用不充分，那么几乎一定会伴随着严重的性能问题。

多次重复的sql就用绑定变量，这对数据库性能有很大好处。

select...into...for update:

Select…For Update语句的语法与select语句相同，只是在select语句的后面加FOR UPDATE [NOWAIT]子句。

该语句用来锁定特定的行（如果有where子句，就是满足where条件的那些行）。当这些行被锁定后，其他会话可以选择这些行，但不能更改或删除这些行，直到该语句的事务被commit语句或rollback语句结束为止。

例子：

SELECT type ,curid

INTO v_type,v_curid

FROM prv_serial

WHERE name = seq_name FOR UPDATE;

标记符号<<>>：

plsql（oracle的编程语言）的标记符号，常用来跳出循环。
使用goto可以跳到标记<<outer>>的位置，举例如下：
for i in 1..100 loop   
  if i > 10 then  
    goto outer;

  end if;   
end loop;    
<<outer>>   
dbms_output.put_line('loop 循环了10次提前结束了！' );

本站是提供个人知识管理的网络存储空间，所有内容均由用户发布，不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息，谨防诈骗。如发现有害或侵权内容，请点击一键举报。

转藏分享

QQ空间 QQ好友新浪微博微信

献花（0） +1

来自： comeonwyj > 《ORACLE》

举报/认领

0条评论

发表

请遵守用户评论公约

类似文章 更多

comeonwyj

关注对话

TA的最新馆藏

substr,substring,slice,splice.txt
VUE 项目引入Sass后启动报错 TypeError [ERR
vue中this.$route.query和this.$route.params & query传参和params传参的使用和区别
【Linux】Linux环境下Oracle SqlPlus中方向键问题的解决方法
cross-env的作用
学习vue3的新姿势（vite的使用及源码流程分析）

喜欢该文的人也喜欢更多

热门阅读换一换