☆ 6 training log oracle10g

Aug 17 posted in Oracle (RSS), 86 views.

----------------- Write plsql code segment, the current users have to remove all bound
rn
declare
cursor cur is select constraint_name, table_name from user_constraints;
str varchar (200);
begin
for i in cur loop
str: = 'alter table' | | i.table_name | | 'drop constraint' | | i.constraint_name;
execute immediate str;
end loop;
end;
/
rn

----------------------- Print name of all classes of students, age, grade
rn

Print ----------------- students aged 16.
declare
v_sname student.sname% type;
v_ssex student.ssex% type;
v_sage student.sage% type;
begin
select sname, ssex, sage into v_sname, v_ssex, v_sage
from student
where sage = 16;
dbms_output.put_line (v_sname | | v_ssex | | v_sage);
end;
/
rn
--------------- Exception handling are two errors: compile-time errors and run-time error
rn
Run-time error called abnormal
rn

declare
rn
begin
rn
exception
when anomalies were then
Statements;
when anomalies were then
Statements;
.....
when others then
Statements;
end
rn

------- Abnormal pre-defined
oracle error code naming certain
rn
excessive number of rows to return too_many_rows (-01422)
No data no_data_found return (-01403)
rn

declare
v_sname student.sname% type;
v_ssex student.ssex% type;
v_sage student.sage% type;
begin
select sname, ssex, sage into v_sname, v_ssex, v_sage
from student
where sage = 16;
dbms_output.put_line (v_sname | | v_ssex | | v_sage);
exception
when too_many_rows then
dbms_output.put_line ( 'the return of the data too much, use the cursor');
when no_data_found then
dbms_output.put_line ( 'No data to return');
when others then
dbms_output.put_line ( 'Other unknown error');
end;
/
rn

rn

rn
------ Non-pre-defined abnormal
pragma exception_init (unusual name, an error number) --- will be the wrong number and the abnormal binding of
SQL> create table temp
2 (id number,
3 name varchar2 (20) not null,
4 sex varchar2 (20),
5 primary key (id),
6 check (sex in ( 'M', 'F'))
7);
rn
Table has been created.
rn
ORA-02290: check constraint violation (ZOU.SYS_C005490)
rn
ORA-01400: unable to NULL to insert ( "ZOU". "TEMP". "NAME")
rn
ORA-00001: unique constraint violation conditions (ZOU.SYS_C005491)
rn
declare
e_check exception;
e_null exception;
e_unique exception;
pragma exception_init (e_check, -02290);
pragma exception_init (e_null, -01400);
pragma exception_init (e_unique, -00001);
begin
insert into temp
values (1, 'San', 'M');
exception
when e_check then
dbms_output.put_line ( 'buddy, can only be inserted between men and women');
when e_null then
dbms_output.put_line ( 'id and name can not be null');
when e_unique then
dbms_output.put_line ( 'id repeated');
when others then
dbms_output.put_line ( 'Other Error');
end;
/
rn

------ Custom definition of abnormal abnormal abnormal ----- ----- dished out to deal with abnormal
rn

declare
str varchar2 (200): = 'universities have to engage targets, and fed each other in the canteen, improper conduct';
e_error exception; ----- definition of abnormal
begin
if str like '% engage in object%' or str like '% feed%' then
out anomalies raise e_error ;-------
else
dbms_output.put_line ( 'This line students, at least not live error');
end if;
rn
to deal with unusual exception ----------
when e_error then
dbms_output.put_line ( 'this student should be expelled, in the schools where the target is not the development of very close relatives to marry');
when others then
dbms_output.put_line ( 'Other Error');
end;
/
rn
-------- Inquiries King's wages, if in excess of 20,000 as a custom exception, print wages are too high
rn

rn
----------------------- End of the database stored procedure, named after a paragraph compiled plsql
rn
--------------------- Information to print out all the boys
declare
cursor cur is select sname, ssex, sage from student where ssex = 'M';
v_stu cur% rowtype;
begin
open cur;
loop
fetch cur into v_stu;
exit when cur% notfound;
dbms_output.put_line (v_stu.sname | | v_stu.ssex | | v_stu.sage);
end loop;
close cur;
end;
/
rn

-------------- Without the stored procedure parameters
create or replace procedure p1
as
cursor cur is select sname, ssex, sage from student where ssex = 'M';
v_stu cur% rowtype;
begin
open cur;
loop
fetch cur into v_stu;
exit when cur% notfound;
dbms_output.put_line (v_stu.sname | | v_stu.ssex | | v_stu.sage);
end loop;
close cur;
end;
/
rn

rn
exec p1;
rn
SQL> show error; --- show the error message
rn
------------------- The highest wages in the names of three individuals, wages, employment date print
create or replace procedure p2
as
cursor cur is select first_name, salary, hire_date
from (select * from employees order by salary desc)
where rownum <4;
v_emp cur% rowtype;
begin
open cur;
loop
fetch cur into v_emp;
exit when cur% notfound;
dbms_output.put_line (v_emp.first_name | | v_emp.salary | | v_emp.hire_date);
end loop;
close cur;
end;
/
rn
exec p2;
rn

rn
-------------------- Stored procedure with parameters
create or replace procedure p3 (v_ssex student.ssex% type)
as
cursor cur is select sname, ssex, sage from student where ssex = v_ssex;
v_stu cur% rowtype;
begin
open cur;
loop
fetch cur into v_stu;
exit when cur% notfound;
dbms_output.put_line (v_stu.sname | | v_stu.ssex | | v_stu.sage);
end loop;
close cur;
end;
/
rn

rn
exec p3 ( 'M');
exec p3 ( 'F');
rn
------------------- Input n, the realization of the pre-n individual's name, salary, employment dates printed
create or replace procedure p4 (n number)
as
cursor cur is select first_name, salary, hire_date
from (select * from employees order by salary desc)
where rownum <= n;
v_emp cur% rowtype;
v_n number;
the definition of abnormal v_error exception ;----
begin
select count (*) into v_n from employees;
if n <= 0 or n> v_n then
out anomalies raise v_error ;--------
end if;
open cur;
loop
fetch cur into v_emp;
exit when cur% notfound;
dbms_output.put_line (v_emp.first_name | | v_emp.salary | | v_emp.hire_date);
end loop;
close cur;
exception
when v_error then
dbms_output.put_line ( 'you have entered incorrect');
when others then
dbms_output.put_line ( 'Other Error');
end;
/
rn
exec p4 (3);
rn
---------------------
create or replace procedure p5 (v_sdept student.sdept% type, v_ssex student.ssex% type)
as
cursor cur is select sname from student where sdept = v_sdept and ssex = v_ssex;
v_sname student.sname% type;
begin
open cur;
loop
fetch cur into v_sname;
exit when cur% notfound;
dbms_output.put_line (v_sname);
end loop;
close cur;
end;
/
rn

exec p5 ( 'computer', 'M');
exec p5 (v_ssex => 'M', v_sdept => 'computer');
rn
Note: stored procedure parameters in the definition of the type of when to write only and can not write the length of
rn

rn
---------------------- Insert stored procedure can be achieved
create or replace procedure p6 (v_sno student.sno% type,
v_sname student.sname% type,
v_ssex student.ssex% type,
v_sage student.sage% type,
v_sdept student.sdept% type)
as
begin
insert into student
values (v_sno, v_sname, v_ssex, v_sage, v_sdept);
commit;
end;
/
rn

exec p6 ('1 ',' San ',' M ', 12,' computer ');
rn

rn
P7 -------------- input through the course of its study, the deletion of the students, if not this person does not print
create or replace procedure p7 (v_sno student.sno% type)
as
begin
delete from student where sno = v_sno;
if sql% rowcount = 0 then
dbms_output.put_line ( ' Find no such person , please re-enter');
else
dbms_output.put_line ( 'deleted successfully');
end if;
end;
/
rn

exec p7 ('1 ');
rn

rn
create or replace procedure p7 (v_sno student.sno% type)
as
begin
delete from student where sno = v_sno;
if sql% notfound then
dbms_output.put_line ( ' Find no such person , please re-enter');
else
dbms_output.put_line ( 'deleted successfully');
end if;
end;
/
rn

--------------------- With the stored procedure output parameters
create or replace procedure p12 (v_sno sc.sno% type, v_n out number)
as
begin
select count (*) into v_n from sc where sno = v_sno;
end;
/
rn

No. ----------- enter school, printing can join the party to join the party a number of conditions must be greater than 3 classes
rn
create or replace procedure p11 (v_sno sc.sno% type)
as
n number;
begin
p12 (v_sno, n);
if n> 3 then
dbms_output.put_line ( 'can join the party');
else
dbms_output.put_line ( 'can not join the party');
end if;
end;
/
rn
exec p11 ('2000012 ');
rn

-------------------- The following code is all logic, that is, the list of parameters should be changed to varchar2 in student.sno% type
create or replace procedure ed (v_sno varchar2)
as
cursor cur is select * from student where sno = v_sno;
v_stu cur% rowtype;
v_error exception;
begin
open cur;
fetch cur into v_stu;
while cur% notfound loop
raise v_error;
end loop;
delete from student where sno = v_sno;
close cur;
exception
when v_error then
dbms_output.put_line ( 'did not this person');
when others then
dbms_output.put_line ( 'Other Error');
end;
/
rn

----------------- Preparation functions, calculation of square Note: only the function to write the type of parameter list, return back to return to the type of write-only and can not write the length of
create or replace function f1 (n number)
return number
as
m number;
begin
m: = n * n;
return m;
end;
/
rn

select f1 (2) from dual;
rn

No. ------------------ staff input, the return of the annual salary
create or replace function f2 (v_id employees.employee_id% type)
return number
as
m number;
begin
select salary * 12 + salary * 12 * nvl (commission_pct, 0) into m
from employees
where employee_id = v_id;
return m;
end;
/
rn

rn
SQL> select first_name, salary * 12 + salary * 12 * nvl (commission_pct, 0)
2 from employees;
rn

SQL> select first_name, f2 (employee_id)
2 from employees;
rn

rn
--------------------- Enter the Student ID and return to the name
create or replace function f2 (v_sno student.sno% type)
return student.sname% type
as
v_sname student.sname% type;
begin
select sname into v_sname from student where sno = v_sno;
return v_sname;
end;
/

rn
SQL> select sname, cno, grade
2 from student, sc
3 where student.sno = sc.sno;
rn
SNAME CNO GRADE
-------- ---- ----------
Wang 102,480
Wang 113,678
Wang 113,770
Wang 115,680
GE wave 102,488
GE wave 113,690
GE wave 115,688
Acclaimed 115,689
Gu Fong 113,777
Gu Fong 115,693
Where Jiang 113,789
rn
11 firms have chosen.
rn
SQL> column F2 (SNO) format a10; --- set F2 (SNO) showed a width of 10 characters
rn

SQL> select f2 (sno), cno, grade
2 from sc;
rn
F2 (SNO) CNO GRADE
---------- ---- ----------
Wang 102,480
Wang 113,678
Wang 113,770
Wang 115,680
GE wave 102,488
GE wave 113,690
GE wave 115,688
Acclaimed 115,689
Gu Fong 113,777
Gu Fong 115,693
Where Jiang 113,789
rn
11 firms have chosen.
rn

rn

rn

rn

rn

rn

rn