SQL，PL/SQL，存储过程及包语句

------------------------1.统计函数 自动去空
select count(*) from scott.emp;
----return 14
select count(comm) from scott.emp;
----return 4
select avg(comm) from scott.emp;
------------------------2.空值转换
select avg(nvl(comm,0)) from scott.emp;

------------------------3.group by,having
select deptno,avg(sal) from scott.emp group by deptno order by deptno asc;

select deptno,avg(sal),job from scott.emp group by deptno,job;

select deptno,max(sal) msal from scott.emp group by deptno having max(sal)>&＃61;3000 order by msal desc;

---error
select deptno,max(sal) msal from emp group by deptno having msal>&＃61;3000 ;
---ORA-00904: "MSAL": invalid identifier,having 后不可以是别名

-------------查询平均薪水最大的部门薪水
select max(avg(sal)) from scott.emp group by deptno;

------------------------4.数值转换

--(1)floor

FLOOR ( n )

FLOOR returns the largest integer equal to or less than n.

SQL> select max(avg(sal)) msal,floor(max(avg(sal))) fsal from scott.emp group by deptno;

MSAL FSAL
---------- ----------
2916.66666 2916

SQL> select floor(-100.81) from dual;

FLOOR(-100.81)
--------------
-101

SQL> select floor(100.81) from dual;

FLOOR(100.81)
-------------
100

--(2)ceil

CEIL ( n )
 

CEIL returns the smallest integer that is greater than or equal to n.

SQL> select max(avg(sal)) msal,ceil(max(avg(sal))) csal from scott.emp group by deptno;

MSAL CSAL
---------- ----------
2916.66666 2917

SQL> select ceil(-100.81) from dual;

CEIL(-100.81)
-------------
-100

SQL> select ceil(100.81) from dual;

CEIL(100.81)
------------
101

--(3)round

ROUND returns n rounded to integer places to the right of the decimal point. If you
omit integer, then n is rounded to zero places. If integer is negative, then n is
rounded off to the left of the decimal point.

ROUND is implemented using the following rules:

1. If n is 0, then ROUND always returns 0 regardless of integer.
2. If n is negative, then ROUND(n, integer) returns -ROUND(-n, integer).
3. If n is positive, then
ROUND(n, integer) &＃61; FLOOR(n * POWER(10, integer) &＃43; 0.5) * POWER(10, -integer)

SQL> select max(avg(sal)) msal,round(max(avg(sal)),2) csal from scott.emp group by deptno;

MSAL CSAL
---------- ----------
2916.66666 2916.67

SQL> select round(100.8165) from dual;

ROUND(100.8165)
---------------
101

SQL> select round(100.8165,2) from dual;

ROUND(100.8165,2)
-----------------
100.82

SQL> select round(-100.8165,2) from dual;

ROUND(-100.8165,2)
------------------
-100.82
 

---------1.create
begin
execute immediate &＃39;create table k1(id int)&＃39;;
end;
/

select * from u01.k1;
-----
---------2.insert
begin
insert into k1 values(1);
insert into k1 values(2);
commit;
end;
/
select * from k1;
-----
---------3.delete
begin
delete k1 where id&＃61;1;
commit;
end;
/
select * from k1;
-----

SQL> declare
2 a int;
3 b varchar2(20);
4 begin
5 a:&＃61;1;
6 b:&＃61;&＃39;Hello Everybody&＃39;;
7 dbms_output.put_line(a);
8 dbms_output.put_line(b);
9 end;
10 /

PL/SQL procedure successfully completed

SQL> set serverout on;-------console输出开启
SQL> / ------------上一个PL/SQL再次执行

1
Hello Everybody

PL/SQL procedure successfully completed

SQL>

SQL> declare
2 a int;
3 b int;
4 c int;
5 begin
6 a:&＃61;10;
7 b:&＃61;20;
8 c:&＃61;a&＃43;b;
9 dbms_output.put_line(to_char(a)||&＃39;&＃43;&＃39;||to_char(b)||&＃39;&＃61;&＃39;||to_char(c));
10 end;
11 /

10&＃43;20&＃61;30

PL/SQL procedure successfully completed

SQL> ed

 -----------------PL/SQL编辑

SQL> declare
2 myid scott.dept.deptno%type;
3 myname scott.dept.dname%type;
4 begin
5 select deptno,dname into myid,myname from scott.dept where deptno&＃61;10;
6 dbms_output.put_line(myid||&＃39; &＃39;||myname);
7
8 end;
9 /

10 ACCOUNTING

PL/SQL procedure successfully completed

SQL> create table dept as select * from scott.dept where 1&＃61;0;

Table created

SQL>
SQL> declare
2 type type_dept is table of dept%rowtype index by binary_integer;
3 a type_dept;
4 begin
5 a(1).deptno:&＃61; 1;
6 a(1).dname :&＃61; &＃39;dev&＃39;;
7 a(2).deptno:&＃61; 2;
8 a(2).dname :&＃61; &＃39;manager&＃39;;
9 a(3).deptno:&＃61; 2;
10 a(3).dname :&＃61; &＃39;manager&＃39;;
11 dbms_output.put_line(a.count);
12
13 end;
14 /

3

PL/SQL procedure successfully completed

-------------------------同样’type type_dept is table of‘可以转化为record形式显示

SQL> declare
2 d1 date :&＃61; sysdate;
3 a1 varchar2(10) default &＃39;001&＃39;;
4 b1 varchar2(20) not null :&＃61; &＃39;us&＃39;;
5 type type1 is record(
6 id dept.deptno%type,
7 name dept.dname%type
8 );
9 c1 type1;
10 begin
11 select deptno,dname into c1 from scott.dept where deptno&＃61;10;
12 dbms_output.put_line(c1.id||&＃39; &＃39;||c1.name);
13 end;
14 /

10 ACCOUNTING

PL/SQL procedure successfully completed

SQL> variable x number;
SQL> exec :x :&＃61; 10;

PL/SQL procedure successfully completed
x
---------
10

SQL> select * from scott.dept where deptno&＃61;:x
2 ;

DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK

create or replace procedure u01.pl(a int) is
begin

if a > 10 then
dbms_output.put_line(a || &＃39;>10&＃39;);
elsif a<&＃61;10 then
dbms_output.put_line(a || &＃39;<&＃61;10&＃39;);

end if;

end;

create or replace procedure u01.pl(a int) is
begin

if a > 10 then
dbms_output.put_line(a || &＃39;>10&＃39;);
elsif a<&＃61;10 then
dbms_output.put_line(a || &＃39;<&＃61;10&＃39;);

end if;

end;

-----------------------------------------------------

create or replace procedure u01.p2 is
a int;
begin
a :&＃61; 20;
if a > 10 then
dbms_output.put_line(a || &＃39;>10&＃39;);
elsif a <&＃61; 10 then
dbms_output.put_line(a || &＃39;&＃61;10&＃39;);
else
dbms_output.put_line(a || &＃39;<10&＃39;);
end if;

end;
 

create or replace procedure p3 is
a1 int :&＃61; 1;
begin

case
when a1 &＃61; 1 then
dbms_output.put_line(&＃39;a1&＃61;1&＃39;);
when a1 > 1 then
dbms_output.put_line(&＃39;a1>1&＃39;);
when a1 <1 then
dbms_output.put_line(&＃39;a1<1&＃39;);
else
dbms_output.put_line(&＃39;Error&＃39;);

end case;

end;
 

SQL> begin
2 for i in 1 .. 9 loop
3 dbms_output.put_line(i);
4 end loop;
5
6 end;
7 /

1
2
3
4
5
6
7
8
9

PL/SQL procedure successfully completed

-------------------------------游标

SQL> declare
2 cursor c1 is
3 select * from scott.emp;
4 begin
5 for a1 in c1 loop
6 dbms_output.put_line(a1.empno || &＃39; &＃39; || a1.ena me);
7 end loop;
8 end;
9 /

7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER

PL/SQL procedure successfully completed
 

-------------------1.动态参数&参数名及 no_data_found then异常处理

SQL> declare
2 v_ename scott.emp.ename%type;
3 begin
4 select ename into v_ename from scott.emp where empno &＃61; &no;
5 dbms_output.put_line(&＃39;name is &＃39; || v_ename);
6 exception
7 when no_data_found then
8 dbms_output.put_line(&＃39;NO DATA!&＃39;);
9 end;
10 /

NO DATA!

PL/SQL procedure successfully completed

-------------------2.case_not_found异常处理

create or replace procedure u01.p100(a1 int) is
v1 emp.sal%type;
begin
select sal into v1 from emp where empno &＃61; a1;
case
when v1 <1000 then
update emp set sal &＃61; sal &＃43; 100 where empno &＃61; a1;
commit;
dbms_output.put_line(&＃39;sal from &＃39; || to_char(v1) || &＃39; to &＃39; ||
to_char(v1 &＃43; 100));
when v1 > 1000 then
update emp set sal &＃61; sal &＃43; 200 where empno &＃61; a1;
commit;
dbms_output.put_line(&＃39;sal from &＃39; || to_char(v1) || &＃39; to &＃39; ||
to_char(v1 &＃43; 200));
end case;
exception
when case_not_found then
dbms_output.put_line(&＃39;"case" can not find in your conf &＃39;);
end;
/

SQL> exec p100(7369);

sal from 900 to 1000

PL/SQL procedure successfully completed

SQL> exec p100(7788);

sal from 3000 to 3200

PL/SQL procedure successfully completed

SQL> exec p100(100);

begin p100(100); end;

ORA-01403: no data found
ORA-06512: at "U01.P100", line 4
ORA-06512: at line 2

---------------------------------------------------------

-------------------3.invalid_cursor异常处理

SQL> declare
2 cursor c1 is select ename,sal from emp where empno&＃61;&＃39;7369’ ;
3 record1 c1%rowtype;
4
5 begin
6 open c1;
7 fetch c1 into record1;
8 dbms_output.put_line(record1.ename);
9 close c1;
10 exception
11 when invalid_cursor then
12 dbms_output.put_line(&＃39;check the cursor c1 is open ?&＃39;);
13 end;
14 /
SMITH
PL/SQL procedure successfully completed

----------------------------------------------------------

-------------------4.too_many_rows 异常处理

SQL> declare
2 v1 emp.empno%type;
3 begin
4 select ename into v1 from emp;
5 exception
6 when too_many_rows then
7 dbms_output.put_line(&＃39;return more than one row&＃39;);
8 end;
9 /

return more than one row

PL/SQL procedure successfully completed

-------------------------------------------

-------------------5.zero_divide异常处理

SQL> declare
2 v1 int;
3 begin
4 select 5/&num into v1 from dual;
5 exception
6 when zero_divide then
7 dbms_output.put_line(&＃39;Divider is zero&＃39;);
8 end;
9 /

Divider is zero

PL/SQL procedure successfully completed

create or replace procedure u01.p11(c1 int) is
e1 exception;
begin

update emp set sal &＃61; sal &＃43; 1000 where empno &＃61; c1;
if sql%notfound then
raise e1;
end if;
exception
when e1 then
dbms_output.put_line(&＃39;no data update&＃39;);
end;
 

SQL> exec p11(1);

no data update

PL/SQL procedure successfully completed

SQL> create or replace package p101 is
2 type c1 is ref cursor;
3 procedure p01(a1 int,return_list out c1);
4 end p101;
5 /

Package created

------------------查看包状态

select * from user_objects where object_type like &＃39;PACKAGE%&＃39;;

-------------------建包体

SQL> create or replace package body p101 is
2 procedure p01(a1 int,return_list out c1)
3 as
4 begin
5 open return_list for select * from emp;
6 end p01;
7 end p101;
8 /

Package body created

------------------------

select * from user_objects where object_type like &＃39;PACKAGE%&＃39;;

-------------------------在PL/SQL sql窗口调用

declare

v_cur P101.c1;
l_data emp%rowtype;
begin

P101.P01(0, v_cur);

loop
fetch v_cur into l_data;
exit when v_cur%notfound;
DBMS_OUTPUT.put_line(l_data.EMPNO || &＃39; &＃39; || l_data.ENAME );

end loop;
dbms_output.put_line(&＃39;---------------------------&＃39;);
dbms_output.put_line(v_cur%rowcount);
close v_cur;
end;
 

7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
---------------------------
14 

-------------------------在PL/SQL command窗口调用

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as u01&＃64;bitc

SQL> var cur refcursor;
REFCURSOR not supported

#-----------The PL/SQL DEV tool doesn&＃39;t support host cursor variable!

-------------------------应在SQLPLUS 下调用

SQL> variable &＃xff1b;
Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]
 

SQL> set linesize 100;
SQL> var cur refcursor;
SQL> exec p101.p01(100,:cur);

PL/SQL procedure successfully completed.

SQL> print cur;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1000 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3200 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> variable
variable cur
datatype REFCURSOR