oracle函数及存储过程

计算一个数的两倍后返回
　　CREATE OR REPLACE FUNCTION f_double
　　(
　　v_number NUMBER
　　)
　　RETURN NUMBER
　　IS
　　v_num NUMBER;
　　BEGIN
　　v_num :=v_number*2;
　　RETURN v_num;
　　END;

 

--删除指定工号的员工,如果该员工所在部门人数少于3,则不能删除
　　CREATE OR REPLACE PROCEDURE sp_del
　　(
　　p_number emp.empno%TYPE
　　)
　　IS
　　v_count NUMBER;
　　BEGIN
　　SELECT COUNT(*) INTO v_count FROM emp WHERE deptno=(
　　SELECT deptno FROM emp WHERE empno=p_number
　　);
　　IF (v_count<3) THEN
　　raise_application_error(-20093,'该部门人数太少');
　　ELSE
　　EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno='||p_number;
　　--COMMIT;
　　END IF;
　　END;
　　SELECT * FROM emp;
　　CALL sp_del(7839);
　　SELECT * FROM dept

 

1、存储过程和函数的区别。
（1）、存储过程是作为PL/SQL语句执行，而函数是作为表达式的一部分调用；
（2）、存储过程在规格说明中不包含return语句，而在函数的规格说明这包含return子句；
(3)、存储过程不返回值，而函数必须返回值；
（4）、在存储过程中可以包含return语句，但不返回任何值，他只表示退出存储过程，而函数中必须包含一个return语句；
2、触发器分为事前触发和事后触发，这两种触发有何区别。语句级触发和行级触发有何区别。
事前触发是在数据没有写入数据库时就触发，而事后触发是在把数据写入数据库后再触发
语句级触发值所有的相同的语句只触发一次，而行级触发是每执行一条语句就触发一次。
3、根据students表(stuID,stuName,stuSex,stuAge,stuTel)编写一个存储过程，将students表中的学生ID号传递给这一过程，并向调用应用程序返回学生的姓名和电话号码。再编写一个具有过程调用的匿名块。
存储过程
create or replace procedure pro_stu(id in number,stuname out varchar2,
stutel out varchar2) as
begin
select A.stuname,A.stutel into stuname,stutel from students A
where A.stuid=id;
end pro_stu;
匿名块：
declare
stuid number(3);
stuname varchar2(12);
stutel varchar2(15);
begin
stuid:=&stuid;
pro_stu(stuid,stuname,stutel);
DBMS_OUTPUT.PUT_LINE('姓名：' || stuname);
DBMS_OUTPUT.PUT_LINE('电话：' || stutel);
EXCEPTION
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('未找到符合条件的数据！！');
end;
4、在显示游标上可以执行哪些操作？举例说明每一种语句的作用。
*声明游标
*打开游标
*提取游标
*关闭游标
5、集合运算符INTERSECT的用途是什么？
可以用来查询两个查询结果中相同的部分
6、找出与John Smith在相同的部门工作的雇员信息。
select A.* from employee A where deptid =(
select B.deptid from employee B where B.fname='john' and B.lname='smith') and
A.fname<>'john' and A.lname<>'smith'
7、通过一个外部连接，显示雇员名和部门信息。
select A.fname||'--'||A.lname as 雇员名,B.* from employee A,dept B
where A.deptid(+)=B.deptid;
8、抽取Employee表中按FNAME的字母顺序的第2条记录开始的3条记录的SQL语句。
select A.lname from (select rownum as rn, B.* from employee B order by
B.fname) A where rn>=2 and rn<5
9、INSERT ALL和INSERT　FIRST语句的用途是什么？
Insert all 是向所以符合条件的表中插入数据；
Insert first 当有多个符合条件的时只向第一个中插入数据
10、使用多层子查询显示FINANCE部门的雇员的亲属信息。
select A.* from Dependent A where A.employeeid in(
select B.employeeid from employee B where B.deptid in(
select C.deptid from dept C where C.detpname='Finance'))
11、编写一个PL/SQL 程序，包括两个变量，分别用于姓和名，显示完整的姓名，姓和名之间用逗号和空格分隔开。
declare
familyname employee.fname%type;
name employee.lname%type;
cursor cur_emp is
select A.fname,A.lname from employee A where 1 = 1;
begin
open cur_emp;
fetch cur_emp into familyname,name;
while cur_emp%FOUND
loop
DBMS_OUTPUT.PUT_LINE('员工的姓名为：'||familyname||', '||name);
fetch cur_emp into familyname,name;
end loop;
end;
12、使用for循环，以相反的顺序显示10~1。
declare
begin
for num in reverse 1 .. 10
loop
DBMS_OUTPUT.PUT_LINE(num);
end loop;
end;
13、编写一个PL/SQL块，要求用户输入第一个数字、第二个数字和算术运算符（+、-、*、/）。如果运算符无效，则抛出并处理一个用户定义的异常。如果第二个数字是零，而且运算符是/，则处理ZERO_DIVIDE预定义的服务器异常。

declare
fnum number(4);
snum number(4);
oper varchar2(4);
result number(6);
nooper EXCEPTION;
begin
fnum:=&fnum;
snum:=&snum;
oper:='&oper';
result:=
case
when '+' then fnum+snum;
when '_' then fnum-snum;
when '*' then fnum*snum;
when '/' then fnum/snum;
else raise nooper;
DBSM_OUTPUT.PUT_LINE(fnum || oper|| snum||'='||result);
EXCEPTION
when nooper then
DBSM_OUTPUT.PUT_LINE('你输入了非法操作符！！');
when
ZERO_DIVIDE then
DBMS_OUTPUT.PUT_LINE('除数不能为零！！');
end;
14、表结构以及数据如下：
CREATE TABLE 表
(ID int, 日期 varchar2(11), 单据 char(3))
INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 1 , '2004-08-02' , '001' );
INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 2 , '2004-09-02' , '001' );
INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 3 , '2004-10-02' , '002' );
INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 4 , '2004-09-02' , '002' );
要求:设计一个查询，返回结果如下：
ID 日期 单据
---------- ----------- ---
1 2004-08-02 001
4 2004-09-02 002
即对于每个单据号，返回日期最小的行。

15、表内容：
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负

如果要生成下列结果, 该如何写sql语句?

胜 负
2005-05-09 2 2
2005-05-10 1 2
Sql脚本：
------------------------------------------
create table #tmp(rq varchar2(10),shengfu char(2))

insert into tmp values('2005-05-09','胜')
insert into tmp values('2005-05-09','胜')
insert into tmp values('2005-05-09','负')
insert into tmp values('2005-05-09','负')
insert into tmp values('2005-05-10','胜')
insert into tmp values('2005-05-10','负')
insert into tmp values('2005-05-10','负')

select A.rq,A.负,B.胜 from (select rq, count(*) as 负 from tmp where shengfu='负' group by rq) A
join
(select rq, Count(*) as 胜 from tmp where shengfu='胜' group by rq) B
on
A.rq=B.rq

 

select rq, sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负' from tmp group by rq