oracle复习

SQL> edit record.sql

SQL> create table type
  2  (
  3  typeid number(10) primary key,
  4  gradename varchar2(20) unique not null
  5  ) tablespace bookspace;

表已创建。

SQL> create table grade
  2  (
  3  gradeid number(10) primary key,
  4  gradename varchar2(20) unique not null
  5  ) tablespace bookspace;

表已创建。


SQL> create table book
  2  (
  3  bookid number(10) primary key,
  4  booknumber char(8) unique not null,
  5  bookname varchar(30) not null,
  6  bookpress varchar2(40) not null,
  7  bookprice number(8,2) not null,
  8  typeid number(10) default 1 not null,
  9  booktime date not null,
 10  constraint book_type foreign key (typeid) references type(typeid)
 11  )tablespace bookspace;

create table manager(
	managerid number(10) primary key,
	realname varchar2(10) not null,
	sex char(2) not null,
	username varchar2(16) not null,
	password varchar2(16) not null,
	registertime date not null,
	gradeid number(10) default 1 not null,
	constraint manager_grade foreign key (gradeid) references grade(gradeid)
	)tablespace bookspace;


create table student(
	studentid number(10) primary key,
	studentnumber char(7) not null,
	studentname varchar2(10) not null,
	studentsex char(2) not null check (studentsex in ('男','女'))
	)tablespace bookspace;

create table card(
	cardid number(10) primary key,
	cardnumber char(9) not null,
	studentid number(10) unique not null,
	registertime date not null,
	constraint card_student foreign key (studentid) references student(studentid)
	)tablespace bookspace;

create table lend(
	lendid number(10) primary key,
	cardid number(10) not null,
	bookid number(10) not null,
	lendtime date not null,
	constraint lend_card foreign key (cardid) references card(cardid),
	constraint lend_book foreign key (bookid) references book(bookid)
	)tablespace bookspace;

select bookpress,count(*) from book where booktime>='01-1 月-2013' group by bookpress having count(*)>0 order by count(*) desc;


scott/tiger用户下的表;

set serveroutput on
declare
 emp_number constant number(4) := 7900;
 emp_name varchar2(10);
 emp_job varchar2(9);
 emp_sal number(7,2);
begin
	select ename,job,sal into emp_name,emp_job,emp_sal 
	from emp where empno=emp_number;
	dbms_output.put_line('查询编号'||emp_number);
	dbms_output.put_line('员工姓名'||emp_name);
	dbms_output.put_line('员工职位'||emp_job);
	dbms_output.put_line('员工工资'||emp_sal);
end;
/


set serveroutput on
declare
 emp_number constant emp.empno%TYPE := 7900;
 emp_name emp.ename%TYPE;
 emp_job emp.job%TYPE;
 emp_sal emp.sal%TYPE;
begin
	select ename,job,sal into emp_name,emp_job,emp_sal 
	from emp where empno=emp_number;
	dbms_output.put_line('查询编号'||emp_number);
	dbms_output.put_line('员工姓名'||emp_name);
	dbms_output.put_line('员工职位'||emp_job);
	dbms_output.put_line('员工工资'||emp_sal);
end;
/


set serveroutput on
declare
 emp_number constant emp.empno%TYPE := 7900;
 one_emp emp%ROWTYPE;
begin
	select * into one_emp 
	from emp where empno=emp_number;
	dbms_output.put_line('查询编号'||emp_number);
	dbms_output.put_line('员工姓名'||one_emp.job);
	dbms_output.put_line('员工职位'||one_emp.ename);
	dbms_output.put_line('员工工资'||one_emp.sal);
end;
/


set serveroutput on
declare  
fag boolean :=true; 
begin  

  for i in 1..100 
  loop  
  for j in 2..i-1 
  loop  
  if mod(i,j)=0 then  
  fag:=false;  
  end if;  
  end loop;  
  if fag then 
  dbms_output.put_line(i);  
  end if; 
  fag:=true;  
  end loop;  
end;
/


set serveroutput on

declare

i binary_integer :=1;

begin
  
  if i>10 then
	dbms_output.put_line(i||'ddd');
  end if;

  loop 
	dbms_output.put_line(i);
	i:=i+1;
	exit when i>10;
  end loop;
end;
/

set serveroutput on

declare

i binary_integer :=1;

begin
	while i<=10
	loop
	 dbms_output.put_line(i);
	 i:=i+1;
	end loop;
end;
/

set serveroutput on

declare
i binary_integer :=1;
begin
	loop
	case i
	when '1' then dbms_output.put_line('*');
	when '3' then dbms_output.put_line('***');
	when '5' then dbms_output.put_line('*****');
	when '7' then dbms_output.put_line('*******');
	when '9' then dbms_output.put_line('*********');
	end case;
	i:=i+2;
	exit when i>9;
	end loop;
end;
/

set serveroutput on

declare
c varchar2(10);
begin
	c:='*';
	for i in 1..9
	loop
		if mod(i,2) then
		dbms_output.put_line(c);
		end if;
		concat(c,'*');
	end loop;
end;
/

/*打印**/
set serveroutput on

declare

begin
	for i in 1..5
	loop
		for j in 1..i*2-1 
		loop
		  dbms_output.put('*');
		end loop;
		dbms_output.put_line('');
	end loop;
end;
/

set serveroutput on

declare
ss varchar2(50) :='*'; 
begin
	CONCAT(ss,'dd');
	for i in 1..5
	loop
		for j in 1..i*2-1 
		loop
		  dbms_output.put(mod(j,2));
		end loop;
		dbms_output.put_line('');
	end loop;
end;
/

/*素数或质数*/

set serveroutput on

declare
flag boolean :=true;
begin
	for i in 1..100 
	loop
		for j in 2..i/2 
		loop
			if mod(i,j)=0 then
				flag:=false;
			end if;
		end loop;
		if flag then
			dbms_output.put_line(i);
		end if;
		flag:=true;
	end loop;
end;
/


/*偶数*/
set serveroutput on

declare

begin
	for i in 1..100 
	loop
		if mod(i,2)=0 then
			dbms_output.put_line(i);
		end if;
	end loop;
end;
/