PL/SQL LEARNING NOTES(1)

最新推荐文章于 2025-08-16 09:13:57 发布

原创最新推荐文章于 2025-08-16 09:13:57 发布 · 627 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#variables #integer #structure #extension #sql #database

oracle学习专栏收录该内容

2 篇文章

订阅专栏

本文介绍了PL/SQL的基础知识，包括其定义、块结构、变量声明及使用等。通过示例展示了如何执行匿名块和打印盲变量，适合初学者快速入门。

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

1.About PL/SQL
Stands for "Procedural Language extension to SQL"
Is Oracle Corporation's standard data access language for relational databases
Prodides a block structure for executable units of code
2.PL/SQL Block Structure
DECLARE(Optional)
    -Variables,cursors,User-defined exceptions
BEGIN(mandatory)
    -SQL statements
    -PL/SQL statements
EXCEPTION(Optional)
    -Actions to perform when errors occur
END
3.Block Types
Anonymous
Procedure
    -Procedure name IS
Function
    -Function name RETURN datatype is
4.Execute an Anonymous Block(first set serverout on)
1 declare
2 v_fname varchar2(20);
3 begin
4     select first_name into v_fname from employees where employee_id=100;
5     dbms_output.put_line('The result is ' || v_fname);
6 end;
7 /
5.Declaring an initializing PL/SQL Variables
   syntax:identifier [constant] datatype [NOT NULL] [:= | DEFAULT expr];
6.Types of Variables
PL/SQL variables
   -Scalar
   -Composite
   -Reference
Non-PL/SQL variables:Blind variables
   -Large object(LOB)
7.Base Scalar Data Types
   -CHAR
   -VARCHAR2
   -NUMBER
-BINARY_INTEGER
   -PLS_INTEGER
-BOOLEAN
   -BINARY_FLOAT
-BINARY_DOUBLE
8.%TYPE Attribute
    -A database column definition
    -Another declare variable
Syntax
    -identifier table.column_name%TYPE
Examples
   -emp_lname employee.last_name%TYPE
9.Printing Blind variables
   SQL> variable b_emp_salary number
   SQL> variable b_emp_salary number
   SQL> begin
        2 select salary into :b_emp_salary
        3 from employees where employee_id=178;
        4 end;
        5 /
   PL/SQL procedure successfully completed.

SQL> print b_emp_salary

   B_EMP_SALARY
   ------------
           7000
   select first_name, last_name from employees
     2 where salary=:b_emp_salary;

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Oliver               Tuvault
Sarath               Sewall
Kimberely            Grant