求答案,

[xxblhy]

1 编写一个PL/SQL程序，实现查找emp表中的员工信息，从键盘上输入员工编号，把该员工的其他信息输出来。
以带参数的过程形式实现上一程序，员工编号是以过程参数的方式提供。
2 编写一PL/SQL程序实现emp表中的ENAME、JOB、COMM、SAL四个字段内容的输出（用%type定义变量）。
3 编写一PL/SQL程序实现求M、N的最大公约数和最小公倍数（M、N均从键盘输入）。
4 建立一个用户自定义的记录数据类型，然后利用它保存emp表里面的ENAME、JOB、COMM、SAL字段值，遍历emp表。
5 利用游标编写一个匿名PL/SQL块，用以完成对emp表中的工资小于3000的记录进行删除。
6 请写出把create session, create table权限授给user的语句，之后再写出把以上权限回收的语句。

[范佩西_11]

虽然不难但没分啊。呵呵。

[noenoughmemory]

不懂，up

[范佩西_11]

1、select * from emp where empno=&empno;
这个最简单，但最烦人

create or replace procedure pro_1(in_empno number) as
  v_ename    varchar2(10);
  v_job      varchar2(9);
  v_mgr      number(4);
  v_hiredate date;
  v_sal      number(7, 2);
  v_comm     number(7, 2);
  v_deptno   number(2);
begin
  select emp.ename,
         emp.job,
         emp.mgr v_mgr,
         emp.hiredate v_hiredate,
         emp.sal v_sal,
         emp.comm v_comm,
         emp.deptno
    into v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno
    from emp
   where empno = in_empno;
  dbms_output.put_line(v_ename || '   ' || v_job || '   ' || v_mgr ||
                       '   ' || v_hiredate || '   ' || v_sal || '   ' ||
                       v_comm || '     ' || v_deptno);
end;
SQL> exec pro_1(7369);
 
SMITH   CLERK   7902   17-12月-80   800        20
 
PL/SQL procedure successfully completed
 

2、

create or replace procedure pro_2 as
  emprow emp%rowtype;
  cursor cu is
    select * from emp;
begin
  open cu;
  loop
    fetch cu
      into emprow;
    exit when cu%notfound;
    dbms_output.put_line(emprow.ename || '   ' || emprow.job || '   ' ||
                         emprow.comm || '     ' || emprow.deptno);
  
  end loop;
  close cu;

end;
SQL> exec pro_2;
 
SMITH   CLERK        20
ALLEN   SALESMAN   300     30
WARD   SALESMAN   500     30
JONES   MANAGER        20
MARTIN   SALESMAN   1400     30
BLAKE   MANAGER        30
CLARK   MANAGER        10
SCOTT   ANALYST        20
KING   PRESIDENT        10
TURNER   SALESMAN   0     30
ADAMS   CLERK        20
JAMES   CLERK        30
FORD   ANALYST        20
MILLER   CLERK        10
 
PL/SQL procedure successfully completed

3、

create or replace procedure pro_3(m int, n int) as
  t    int;
  avge number := 1;

begin

  if m <= n then
    t := m;
  else
    t := n;
  end if;
  for i in 1 .. t loop
    if mod(m, t) = 0 and mod(n, t) = 0 then
      avge := t;
    end if;
  end loop;
  dbms_output.put_line('最大公约数为：' || avge);
  dbms_output.put_line('最小公倍数为：' || m * n / avge);
end;
SQL> exec pro_3(6,7);
 
最大公约数为：1
最小公倍数为：42
 
PL/SQL procedure successfully completed

[范佩西_11]

4、

declare
  type emprecord is record(
    name   emp.ename%type,
    empjob emp.job%type,
    com    emp.comm%type,
    salary emp.sal%type);
  emp_r emp_record;
begin
  select ENAME, JOB, COMM, SAL into emp_r from emp where empno = &empno;
  dbms_output.put_line(emp_r.name || emp_r.empjob || emp_r.com ||
                       emp_r.salary);
end;

5、

SQL> declare
  2  cursor cu is select * from emp for update;
  3  emprow emp%rowtype;
  4  begin
  5  open cu;
  6  loop
  7  fetch cu into emprow;
  8  exit when cu%notfound;
  9  if emprow.sal<2000 then
 10  delete from emp where current of cu;
 11  end if;
 12  end loop;
 13  commit;
 14  end ;
 15  /
 
PL/SQL procedure successfully completed
 
SQL> select * from emp;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 
6 rows selected

6、

grant create session,create table to user;
revoke create session,create table from user;

[tiantom]

帮忙顶啦

[ticketliu]

up，up，up