PLSQL操作

最新推荐文章于 2020-11-21 02:58:50 发布

cqcj198213

最新推荐文章于 2020-11-21 02:58:50 发布

阅读量126

点赞数

点击(此处)折叠或打开

Declare
counter Number;
Begin
For counter In Reverse 0..10 Loop
dbms_output.put_line(counter);
End Loop;
End ;
Declare
First Varchar2(20);
Last Varchar2(20);
email Varchar2(25);
Begin
Select first_name,last_name,email Into First,Last,email
From scott.employees Where employee_id=100;
dbms_output.put_line('Employee Information for ID: ' || First || ' ' || Last || ' - ' || email);
Exception
When no_data_found Then
dbms_output.put_line('No employee matches the given ID');
When too_many_rows Then
dbms_output.put_line('More than one employee matches the given ID');
End;
Declare
Cursor emp_cursor Is
Select first_name,last_name,email From employees Where employee_id = &emp_id;
First Varchar2(20);
Last Varchar2(20);
email Varchar2(20);
Begin
Open emp_cursor;
Fetch emp_corsor Into First,Last,email;
If emp_cursor%Notfound Then
Raise no_data_found;
Else
--Perform second fetch to see if more than one row is returned
Fetch emp_cursor Into First,Last,email;
If emp_cursor%Found Then
Raise too_many_rows;
Else
dbms_output.put_line(
'Employee Information for ID: ' || First || ' ' || Last || ' - ' || email);
End If ;
End If ;
Close emp_cursor;
Exception
When no_data_found Then
dbms_output.put_line('No employee matches the given ID');
When too_many_rows Then
dbms_output.put_line('More than one employee matches the given ID');
End;
--限定列名与变量名
Create Or Replace Procedure retrieve_emp_info(last_name In Varchar2) As
First Varchar2(20);
Last Varchar2(20);
email Varchar2(20);
Begin
Select first_name,last_name,email Into First,Last,email From employees Where last_name = retrieve_emp_info.last_name;
dbms_output.put_line('Employee Inforation for ID: ' || First || ' ' || last_name || ' - ' || email);
Exception
When no_data_found Then
dbms_output.put_line('No employee matches the last name ' || last_name);
End ;
Declare
First employees.first_name%Type;
Last employees.last_name%Type;
email employees.email%Type;
Begin
Select first_name,last_name,email Into First,Last,email From employees Where employee_id = &emp_id;
dbms_output.put_line('Employee Information for ID: ' || First || ' ' || Last || ' - ' || email);
Exception
When no_data_found Then
dbms_output.put_line('No matching employee was found,please try again.');
When Others Then
dbms_output.put_line('An unknown error has occured,please try again.');
End ;
Declare
Cursor emp_cur Is
Select * From employees Where employee_id = &emp_id;
--Declaring a local variable using the Rowtype attribute
--of the employees table
emp_rec employees%Rowtype;
Begin
Open emp_cur;
Fetch emp_cur Into emp_rec;
If emp_cur%Found Then
dbms_out.put_line('Employee Information for ID: ' || emp_rec.first_name || ' ' || emp_rec.last_name || ' - ' || emp_rec.email);
Else
dbms_output.put_line('No matching employee for the given ID') ;
End If ;
Close emp_cur ;
Exception
When no_data_found Then
dbms_output.put_line('No employee matches the given emp ID') ;
End ;
Declare
Type emp_info Is Record(First employees.first_name%Type,
employees.last_name%Type,
employees.email%Type);
emp_info_rec emp_info;
Begin
Select first_name,last_name,email Into emp_info_rec From employees Where last_name = 'Vargas' ;
dbms_output.put_line('The queried employee''s email address is ' || emp_info_rec.email) ;
Exception
When no_data_found Then
dbms_output.put_line(\'No employee matches the last name provided\') ;
End ;
Declare
Cursor emp_cur Is
Select first_name,last_name,email From employees Where employee_id = 100 ;
emp_rec emp_cur%Rowtype ;
Begin
Open emp_cur;
Fetch emp_cur Into emp_rec;
If emp_cur %Found Then
Close emp_cur;
dbms_output.put_line(emp_rec.first_name || ' ' || emp_rec.last_name ||
'''s email Is ' || emp_rec.email);
Else
dbms_output.put_line('No employee matches The provided Id Number');
End If ;
exception
when no_data_found then
dbms_output.put_line( ' No employee matches The Last Name provided');
End ;
---循环遍历查询结果
Begin
For emp In
(
Select first_nam,last_name,email From employees Where commission_pct Is Not Null
)
Loop
dbms_output.put_line(emp.first_name || ' ' || emp.last_name || ' - ' || emp.email);
End Loop ;
End ;
Declare
Cursor emp_cur Is
Select first_name,last_name,email From employees Where commission_pct Is Not Null ;
emp_rec emp_cur%Rowtype ;
Begin
For emp_rec In emp_cur Loop
dbms_output.put_line(
emp_rec.first_name || ' ' || emp_rec.last_name || ' - ' || emp_rec.email );
End Loop ;
End ;