PL/SQL详例和解释 - 数据库编程

/**example**/
set serveroutput on;  --将输出server打开
show serveroutput;

set verify off;
show verify;

--调取数据库中的值
declare
  emp_first_name varchar2(30);
  emp_last_name varchar2(30);
  emp_phone varchar2(30);
begin
  select first_name,last_name,phone_number into emp_first_name,emp_last_name,emp_phone from it_employees where employee_id=&v_employee_id;
  DBMS_OUTPUT.PUT_LINE('The information of this employee is '||emp_first_name||'  '||emp_last_name||'  '||emp_phone);
exception
  when no_data_found then dbms_output.put_line('There is not any information for this employee!');
end;

--替代变量
declare
  input_value number:=&v_input;
  output_result number;
begin
  output_result:=power(input_value, 2);
  DBMS_OUTPUT.PUT_LINE(output_result);
end;

--圆面积
declare
  radius number:=&v_radius;
  pai constant number:=3.14;
  area number;
begin
  area:=power(radius,2)*pai;
  DBMS_OUTPUT.PUT_LINE('The area is '||area);
end;

--输出系统时间
declare 
  v_day varchar2(20);
begin
  v_day:=to_char(sysdate,'Day, HH24:MI');
  DBMS_OUTPUT.PUT_LINE('Today is '||v_day);
end;

--嵌套语句块和标签
<< outer_block >>
declare
  num_test number:=123;
begin
  DBMS_OUTPUT.PUT_LINE('outer_block:'||num_test);
  << inner_block >>
  declare
    num_test number:=345;
  begin
    DBMS_OUTPUT.PUT_LINE('inner_block:'||num_test);
   DBMS_OUTPUT.PUT_LINE('in inner_block call outer_block:'||outer_block.num_test);
  END inner_block;
end outer_block;

--变量声明和IF语句嵌套
declare
  course_name varchar2(30);
  num number(8,2);
  room_num constant varchar2(10):='603D';
  check_res BOOLEAN:=true;
  begin_date date:=sysdate+7;
begin
  course_name:='Introduction to Oracle PL/SQL';
  num:=987654.55;/*NUMBER TYPE*/
  dbms_output.put_line('room number:'||room_num||',and begin date is:'||begin_date);
  if course_name='Introduction to Underwater Basketweaving'
  then dbms_output.put_line('course name is :'||course_name);
  else
    if room_num='603D'
    then dbms_output.put_line('course name is: '||course_name||',and room number is '||room_num);
    else dbms_output.put_line('there is not any information for this course!');
    end if;
  end if;
exception
  when no_data_found then dbms_output.put_line('NO DATA!');
end;
  

--PL/SQL语句块中的SELECT
---<扩展>





declare
  v_salary number;
  v_department_id number;
  v_department_name DEPARTMENTS.DEPARTMENT_NAME%type:='&Department_Name';
  cursor num is select salary from it_employees where department_id=v_department_id;
begin
  select department_id into v_department_id from departments where department_name=v_department_name;
  open num;
  loop
  fetch num into v_salary;
  exit when num%notfound;
  v_salary:=v_salary+&increase_salary;
  update it_employees set salary=v_salary where department_id=v_department_id;
  end loop;
  close num;
end;

--插入一条新员工记录
declare
  v_employee_id number;
begin
  select max(employee_id) into v_employee_id from it_employees;
  insert into it_employees (employee_id, first_name, last_name, e_mail, phone_number, job_id, salary, manager_id, birth_date, department_id)
  values (v_employee_id+1,'&First_name','&Last_name','&E_mail','&Phone_number',&Job_id,&Salary,&Manager_id,'&Birth_date',&Department_id);
end;

--COMMIT
declare
  v_counter number;
begin
  v_counter:=0;
  for i
PL/SQL详例和解释(一)
