oracle pl/sql笔记-优快云博客

本文链接：https://blog.youkuaiyun.com/sunlin5000/article/details/6695493

本文介绍 Oracle PL/SQL 的基本概念与使用方法，包括 PL/SQL 块结构、变量声明、字符串字面量处理及嵌套块的使用等。通过示例演示如何执行 SQL 查询并处理结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

oracle pl/sql笔记

I 、Instoduction

1、Instoduction to pl/sql

1)pl/sql block Structure

declare

begin

--statements

exception

end;

2)Block Types

Anonymous

declare

begin

--statements

exception

end;

Procedure

Procedure name is

begin

--statements

exception

end;

Function

Function name return datatype is

begin

--statements

return value;

exception

end;

SQL> set serveroutput on;

SQL> declare
  v_fname varchar2(20);
begin
  select first_name into v_fname from employees where employee_id=100;
  dbms_output.put_line('the result is ' || v_fname);
end;

2、Declaring PL/SQL Variables

1)Delimiters in String Literals

SQL> declare
  2 v_event varchar2(15);
  3 begin
  4 v_event := q'! Father's day!';
  5 dbms_output.put_line('3rd Sunday in June is: '|| v_event);
  6 v_event := q'[Mother's day]'; --或者两个‘
  7 dbms_output.put_line('2nd Sunday in May is: ' || v_event);
  8 end;

9 /

3rd Sunday in June is: Father's day
2nd Sunday in May is: Mother's day

PL/SQL procedure successfully completed.

SQL>

2、Using a Qualifier with Nested Blocks

begin <<outer>>
declare
  v_father_name varchar2(20) :='Pitrick';
  v_date_of_birth date := '10-apr-1985';
begin
  declare
   v_child_name varchar2(20) := 'nike';
   v_date_of_birth date :='12-dec-2002';
  begin
   dbms_output.put_line('Father''s name: ' || v_father_name);
   dbms_output.put_line('Date of Birth: ' || outer.v_date_of_birth);
   dbms_output.put_line('Child''s Name: ' || v_child_name);
   dbms_output.put_line('Date of Birth: ' || v_date_of_birth);
  end;
end;

end outer;

SQL> /

Father's name: Pitrick
Date of Birth: 10-APR-85
Child's Name: nike
Date of Birth: 12-DEC-02

PL/SQL procedure successfully completed

SQL>

begin <<outer>>
declare
  v_father_name varchar2(20) :='Pitrick';
  v_date_of_birth date := '10-apr-1985';
begin
<<inner>>
  declare
   v_child_name varchar2(20) := 'nike';
   v_date_of_birth date :='12-dec-2002';
  begin
   dbms_output.put_line('Father''s name: ' || v_father_name);
   dbms_output.put_line('Date of Birth: ' || outer.v_date_of_birth);
   dbms_output.put_line('Child''s Name: ' || v_child_name);
   dbms_output.put_line('Date of Birth: ' || inner.v_date_of_birth);
  end;
end;

end outer;

SQL> /

Father's name: Pitrick
Date of Birth: 10-APR-85
Child's Name: nike
Date of Birth: 12-DEC-02

PL/SQL procedure successfully completed

SQL>