SQL，PL/SQL，存储过程及包语句__执行存储过程的sql语句

相关文章推荐

文武双全的围巾 · 日本地下偶像：聚光灯之外的“非主流娱乐圈”_ ...· 10 月前 ·

痴情的墨镜 · 邵宝华董事长带队赴华南师范大学城市文化学院考 ...· 1 年前 ·

仗义的烈酒 · 文字王者我老公呢二攻略详解_文字王者_九游手机游戏· 1 年前 ·

好帅的咖啡 · 天才小毒妃之芸汐传奇漫画｜官方在线漫画全集- ...· 1 年前 ·

豁达的开心果 · 神武帝尊 - 🌈️包子漫畫· 1 年前 ·

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)>=3000 order by msal desc;

---error
select deptno,max(sal) msal from emp group by deptno having msal>=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)
--------------
SQL> select floor(100.81) from dual;
FLOOR(100.81)
-------------

--(2)ceil

CEIL ( n )

C EIL 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)
-------------
SQL> select ceil(100.81) from dual;
CEIL(100.81)
------------

--(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) = FLOOR(n * POWER(10, integer) + 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)
---------------
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
3 myname scott.dept.dname%type;
4 begin
5 select deptno,dname into myid,myname from scott.dept where deptno=10;
6 dbms_output.put_line(myid||' '||myname);
8 end;
10 ACCOUNTING
PL/SQL procedure successfully completed

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:= 1;
6 a(1).dname := 'dev';
7 a(2).deptno:= 2;
8 a(2).dname := 'manager';
9 a(3).deptno:= 2;
10 a(3).dname := 'manager';
11 dbms_output.put_line(a.count);
13 end;
PL/SQL procedure successfully completed

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

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

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

create or replace procedure u01.p2 is
a int;
begin
a := 20;
if a > 10 then
dbms_output.put_line(a || '>10');
elsif a <= 10 then
dbms_output.put_line(a || '=10');
dbms_output.put_line(a || '<10');
end if;

-------------------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 = &no ;
5 dbms_output.put_line('name is ' || v_ename);
6 exception
7 when no_data_found then
8 dbms_output.put_line('NO DATA!');
9 end;

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 = a1;
when v1 < 1000 then
update emp set sal = sal + 100 where empno = a1;
commit;
dbms_output.put_line('sal from ' || to_char(v1) || ' to ' ||
to_char(v1 + 100));
when v1 > 1000 then
update emp set sal = sal + 200 where empno = a1;
commit;
dbms_output.put_line('sal from ' || to_char(v1) || ' to ' ||
to_char(v1 + 200));
end case;
exception
when case_not_found then
dbms_output.put_line('"case" can not find in your conf ');

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='7369’ ;
3 record1 c1%rowtype;
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('check the cursor c1 is open ?');
13 end;
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('return more than one row');
8 end;
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('Divider is zero');
8 end;

Divider is zero
PL/SQL procedure successfully completed

-----------------------------------自定义异常-------------------------
-------------------6.自定义异常

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

update emp set sal = sal + 1000 where empno = c1;
if sql%notfound then
raise e1;
end if;
exception
when e1 then
dbms_output.put_line('no data update');

SQL> exec p11(1);
no data update
PL/SQL procedure successfully completed

STANDARD 和 DBMS_STANDARD：定义和扩展 PL/SQL 语言环境
DBMS_LOB：提供对 Oracle LOB 数据类型进行操作的功能
DBMS_LOCK：用户定义的锁
DBMS_OUTPUT：处理 PL/SQL 块和子程序输出调试信息
DBMS_SESSION：提供 ALTER SESSION 命令的 PL/SQL 等效功能
DBMS_ROWID：获得 ROWID 的详细信息
DBMS_RANDOM：提供随机数生成器
DBMS_SQL：允许用户使用动态 SQL，构造和执行任意 DML 或 DDL 语句
DBMS_JOB：提交和管理在数据库中执行的定时任务
DBMS_XMLDOM：用 DOM 模型读写 XML 类型的数据
DBMS_XMLPARSER：XML 解析，处理 XML 文档内容和结构
DBMS_XMLGEN：将 SQL 查询结果转换为规范的 XML 格式
DBMS_XMLQUERY：提供将数据转换为 XML 类型的功能
DBMS_XSLPROCESSOR：提供 XSLT 功能，转换 XML 文档
UTL_FILE：用 PL/SQL 程序来读写操作系统文本文件

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;
Package created

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

select * from user_objects where object_type like 'PACKAGE%';

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

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

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

select * from user_objects where object_type like 'PACKAGE%';

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

declare

v_cur P101.c1;
l_data emp%rowtype;
begin

P101.P01(0, v_cur);

fetch v_cur into l_data;
exit when v_cur%notfound;
DBMS_OUTPUT.put_line(l_data.EMPNO || ' ' || l_data.ENAME );

end loop;
dbms_output.put_line('---------------------------');
dbms_output.put_line(v_cur%rowcount);
close v_cur;
--------------------------输出

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 command窗口调用

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as u01@bitc
SQL> var cur refcursor;
REFCURSOR not supported
#----------- The PL/SQL DEV tool doesn't support host cursor variable!

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

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

函数的主要特性是它必须返回一个值。创建函数时通过 RETURN 子句指定函数返回值的数据类型。
函数的一些限制：
● 函数只能带有 IN 参数，不能带有 IN OUT 或 OUT 参数。
● 形式参数必须只使用数据库类型，不能使用 PL/SQL 类型。
● 函数的返回类型必须是数据库类型。

CREATE OR REPLACE FUNCTION item_price_rage (price NUMBER)
/* 参数、指定返回类型 */
RETURN varchar2
/* 定义局部变量 */
min_price NUMBER;
max_price NUMBER;
BEGIN
SELECT MAX(ITEMRATE), MIN(ITEMRATE) INTO max_price, min_price
FROM itemfile;
IF price >= min_price AND price <= max_price THEN
RETURN '输入的单价介于最低价与最高价之间';
RETURN '超出范围';
END IF;
● 匿名块执行函数

DECLARE
p NUMBER := 300;
MSG varchar2(200);
BEGIN
MSG := item_price_range(p);
DBMS_OUTPUT.PUT_LINE(MSG);

● SELECT查询调用（因为函数必须有返回值）
SELECT myfunction FROM dual;

14.存储过程与函数的异同
作为 PL/SQL 语句执行；
在规范中不包含 RETURN 子句；
不返回任何值（只有输入/输出参数，结果集）；
可以包含 RETURN 语句，但是与函数不同，它不能用于返回值。
作为表达式的一部分调用；
必须在规范中包含 RETURN 子句；
必须返回单个值；
必须包含至少一条 RETURN 语句。