跪求Oracle中PL/SQL高手帮忙解决以下三题，自己怎么写都是... - 黑马程序员技术交流社区

--表emp：
DROP TABLE emp PURGE;
CREATE TABLE emp(
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(3)
);
insert into emp values(7369,'SMITH','CLERK',7902,'17-12月-05',800,NULL,20);
insert into emp values(7499,'ALLEN','SALESMAN',7698,'20-2月-02',1600,300,30);
insert into emp values(7521,'WARD','SALESMAN',7698,'22-2月-96',1250,500,30);
insert into emp values(7566,'JONES','MANAGER',7839,'02-4月-09',2975,NULL,20);
insert into emp values(7654,'MARTIN','SALESMAN',7698,'28-9月-10',1250,1400,30);
insert into emp values(7698,'BLAKE','MANAGER',7839,'01-5月-08',2850,NULL,30);
insert into emp values(7782,'CLARK','MANAGER',7839,'09-6月-05',2450,NULL,10);
insert into emp values(7788,'SCOTT','ANALYST',7566,'19-4月-10',3000,NULL,20);
insert into emp values(7839,'KING','PRESIDENT',NULL,'17-11月-11',5000,NULL,10);
insert into emp values(7844,'TURNER','SALESMAN',7698,'08-9月-07',1500,0,30);
insert into emp values(7876,'ADAMS','CLERK',7788,'23-5月-04',1100,NULL,20);
insert into emp values(7900,'JAMES','CLERK',7698,'03-12月-03',950,NULL,30);
insert into emp values(7902,'FORD','ANALYST',7566,'03-12月-07',3000,NULL,20);
insert into emp values(7934,'MILLER','CLERK',7782,'23-1月-09',1300,NULL,10);
--表dept：
DROP TABLE dept PURGE;
CREATE TABLE dept(
deptno NUMBER(3),
dname VARCHAR2(14),
loc VARCHAR2(13)
);
insert into dept values(10,'accounting','new york');
insert into dept values(20,'research','dallas');
insert into dept values(30,'sales','chicago');
insert into dept values(40,'operations','boston');

复制代码

ALTER TABLE dept ADD(totalPerson INT);
DECLARE
--定义REF游标
TYPE refcursor IS REF CURSOR;
--定义一个具体的ref游标
dept_cursor refcursor;
--部门编号
v_deptno NUMBER;
--总人数
v_count NUMBER;
BEGIN
OPEN dept_cursor FOR 'SELECT deptno,COUNT(*) FROM emp GROUP BY deptno';
LOOP
--下移游标，并读取数据
FETCH dept_cursor INTO v_deptno,v_count;
UPDATE dept SET totalPerson=v_count WHERE deptno=v_deptno;
--判断是否退出
EXIT WHEN dept_cursor%NOTFOUND;
--打印数据
DBMS_OUTPUT.PUT_LINE(v_deptno||' '||v_count);
END LOOP;
CLOSE dept_cursor;
END;
/
SELECT deptno,totalPerson FROM dept ;

复制代码

create or replace
procedure query_dept(v_empno number,p_dname out dept.dname%type)
as
begin
select dept.dname into p_dname from emp inner join dept on dept.deptno = emp.deptno where empno = v_empno;
dbms_output.put_line(p_dname);
exception
when no_data_found then
dbms_output.put_line('no data');
when others then dbms_output.put_line('unknown error');
end;
/
--调用
declare
p_dname dept.dname%type;
begin
query_dept('&v_empno',p_dname);
end;
/

复制代码

CREATE OR REPLACE FUNCTION getMax(a NUMBER,b NUMBER,c NUMBER) RETURN VARCHAR2
IS max1 VARCHAR2(50);
--exception1 EXCEPTION;
v_char VARCHAR2(50);
BEGIN
v_char :='请输入正数';
IF a<0 OR b<0 OR c< 0
THEN max1 := v_char;
ELSE
IF a > b THEN
IF a > c THEN
max1 := a;
ELSE
max1 := c;
END IF;
ELSE
IF b > c THEN
max1 := b;
ELSE
max1 := c;
END IF;
END IF;
END IF;
RETURN max1;
END;

复制代码

ALTER TABLE dept ADD(totalPerson INT);
declare v_count number;v_deptno number;
cursor dept_cursor is select deptno from dept for update;
begin
open dept_cursor;
loop
fetch dept_cursor into v_deptno;
exit when dept_cursor%notfound;
select count(*) into v_count from emp where deptno=v_deptno;
update dept
set totalPerson=v_count
where deptno=v_deptno;
end loop;
close dept_cursor;
end;
/

复制代码

set serveroutput on
create or replace procedure query_deptname
(
p_no in emp.empno%type,
p_dname out dept.dname%type
)
is
begin
select dname into p_dname from emp,dept where emp.deptno=dept.deptno and empno=p_no;
exception
when no_data_found then
dbms_output.put_line('no data');
when others then
dbms_output.put_line('unkown error');
end query_deptname;
/
variable g_dname varchar2(14)
execute query_deptname(7934,:g_dname);

复制代码

set serveroutput on
create or replace function f_max(a number,b number,c number) return number
is
v_num number(8);
begin
if a<0 or b<0 or c<0 then
dbms_output.put_line('请输入三个都大于0的数！');
elsif a>b then
v_num:=a;
else v_num:=b;
end if;
if c>v_num then
v_num:=c;
end if;
return v_num;
end;
/
select f_max(-1,22,3) from dual;
select f_max(998,2,38) from dual;

复制代码