create table dept as select * from scott.dept;
create table emp as select * from scott.emp;
alter table emp add constraint pk_emp primary key(empno);
alter table dept add constraint pk_dept primary key(deptno);
alter table emp
add constraint FK_dept_emp foreign key(deptno) references dept(deptno);
alter table emp add constraint ck_emp check(sal>0);
set serveroutput on
declare
v_deptno emp.deptno%type;
total number;
begin
v_deptno:=&deptno;
select count(deptno) into total from emp where deptno = v_deptno;
dbms_output.put_line(v_deptno||'部门总人数是: '||total);
end;
set serveroutput on
declare
v_empno emp.empno%type;
v_addcomm emp.comm%type;
v_job emp.job%type;
begin
v_empno:=&empno;
select empno,job into v_empno,v_job from emp where empno=v_empno;
if v_job='CLERK' then v_addcomm:=50;
elsif v_job='SALESMAN' then v_addcomm:=60;
elsif v_job='ANALYST' then v_addcomm:=150;
else v_addcomm:=20;
end if;
dbms_output.put_line(v_empno||'增加的佣金为：'||v_addcomm);
end;
3.
Declare
total number;
begin
select count(*) into total from emp where to_char(hiredate,'MM') between '04' and '06';
dbms_output.put_line('4到6月的入职员工数：'||total);
end;
select * from emp;
4.
set serveroutput on
declare
username varchar2(20);
e1 exception;
begin
username:='&name';
if length(username)<4 then raise e1;
else dbms_output.put_line('欢迎');
end if;
exception
when e1 then dbms_output.put_line('请输入正确的姓名');
end;
5.
declare
prime number;
begin
prime:= &pri;
if prime=0 then dbms_output.put_line('不是');
elsif prime=1 then dbms_output.put_line('不是');
elsif prime=2 then dbms_output.put_line('是');
elsif prime mod 2 = 1 then dbms_output.put_line('是');
else
dbms_output.put_line('不是');
end if;
end;
6.
declare
v_empno EMP.EMPNO%type;
v_empname EMP.ENAME%type;
v_empjob EMP.JOB%type;
begin
v_empno:=&empno;
select empno,ename,job into v_empno,v_empname,v_empjob from emp where empno=v_empno;
dbms_output.put_line('员工号为：'||v_empno||'的姓名和工作分别是:'||v_empname||','||v_empjob);
exception
when no_data_found then DBMS_OUTPUT.PUT_LINE('查无此部门');
end;
7.
declare
dept_a dept%rowtype;
begin
select * into dept_a from dept where deptno=10;
dbms_output.put_line('部门号为：'||dept_a.deptno||'部门名是:'||dept_a.dname||'部门地址'||dept_a.loc);
exception
when no_data_found then DBMS_OUTPUT.PUT_LINE('查无此部门');
end;
8.
declare
jc number:=1;
n number;
begin
n:=&n;
for i in 1..n
loop
jc:=jc*i;
end loop;
dbms_output.put_line('1到'||n||'的阶乘是'|| jc);
end;
9.
declare
a number;
b number;
c varchar2(20);
begin
a:=&a;
select sum(sal+ nvl(comm,0)) into b from emp where empno=a;
dbms_output.put_line(a||'工资和佣金总和是：'||b);
end;
declare
v_empno emp.empno%type;
v_deptno dept.deptno%type;
v_dname dept.dname%type;
begin
v_empno:=&empno;
select e.empno,d.deptno,d.dname into v_empno,v_deptno,v_dname from dept d,emp e where e.DEPTNO = d.DEPTNO and e.empno=v_empno;
dbms_output.put_line(v_empno||'编号所在的部门编号为'||v_deptno||'，部门名称为'||v_dname);
end;

1, 拷贝SCOTT用户的部门表dept和员工表emp到新用户，并添加相应约束。
1) 使用scott用户登录，并授予新用户对象权限，即新用户对dept表和emp表的查看权限
grant select on emp toWinko;
grant update on emp toWinko;
grant delete on emp toWinko;
grant select on dept toWinko;
grant update on dept toWinko;
grant delete on dept toWinko;
2) 使用新用户登录，执行语句：createtable dept as select * from scott.dept;
用同样方法创建emp表。
create table emp asselect * from scott.emp;
create table dept asselect * from scott.dept;
3)分别给dept和emp表添加主键约束：约束名称分别是：pk_dept和pk_emp,主键分别是deptno和empno。
alter table emp
add constraint PK_emp primary key(empno);
alter table dept
add constraint PK_dept primary key(deptno);
4) 给emp表的部门号(deptno)添加外键约束：约束名称是fk_dept_emp,来自dept的部门号(deptno)
alter table emp
add constraint FK_dept_emp foreign key(deptno) referencesdept(deptno);
5）给表emp 添加检查约束,使工资sal>0,约束名称ck_emp
alter table emp addconstraint ck_emp check(sal>0);