oracle基础之pl/sql基础知识

作者：sunhuan | 来源：互联网 | 2023-05-18 05:15

oracle基础之plsql基础知识博客分类：oracleSQLOracleD语言F#1.块（Block）是plsql的基本程序单元，编写plsql程序实际上就是编写plsql块

oracle基础之pl/sql基础知识

博客分类：

oracle

SQLOracleD语言F#

1. 块（Block）是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块
   块由定义部分，执行部分和异常处理部分组成。
   定义部分用于定义常量，变量，游标，异常，复杂数据类型等，Declare。
   执行部分用于实现应用模块功能，主要包括要执行的sql语句和pl/sql语句，begin。
   异常处理用于处理块运行时可能出现的错误，Exception。
   end是pl/sql的结束标记；declare，begin和exception后面没有分号，而end后面有。

2. pl/sql标识符命名规范
    当使用标识符定义变量，常量时每行只能定义一个标识符；标识符只能使用字母，数字
    下划线，$和#，如要使用其它字符则必须加上双引号；标识符名称必须以字符（A-Z a-z）
    开头，并且最大长度30个字符，如果以其他字符开头，必须加上双引号；不能使用oracle关键字
    ，如果要使用则必须加上双引号。

    注意：如果把一个带有单引号的字符串赋给一个变量,可以使用 q'[要赋的值]' 格式;
      s_var := q'[i'm a boy!]'; -- 把 i'm a boy! 赋给变量s_var
3. pl/sql的块划分为匿名块，命名块，子程序和触发器。
   匿名块：没有名字的pl/sql块。

Sql代码

DECLARE
v_name varchar2(10);
BEGIN
SELECT t.name INTO v_name FROM emp t WHERE t.id = &id;
dbms_output.put_line('名字是:' || v_name);
END;
3. 命名块：有名字（标示）的pl/sql，块前使用《》加以标示
<>
DECLARE
v_name varchar2(10);
v_age number(4);
BEGIN
<>
BEGIN
SELECT t.name INTO v_name FROM emp t WHERE t.id = &id;
dbms_output.put_line('名字是:' || v_name);
END;
SELECT t.age INTO v_age FROM emp t WHERE t.name = v_name;
dbms_output.put_line('年龄:' || v_age);
END;

  DECLARE
   v_name varchar2(10);
 BEGIN
   SELECT t.name INTO v_name FROM emp t WHERE t.id = &id;
   dbms_output.put_line('名字是:' || v_name);
 END;
3. 命名块：有名字（标示）的pl/sql，块前使用《》加以标示
        <>
 DECLARE
   v_name varchar2(10);
   v_age  number(4);
 BEGIN
   <>
   BEGIN
     SELECT t.name INTO v_name FROM emp t WHERE t.id = &id;
     dbms_output.put_line('名字是:' || v_name);
   END;
   SELECT t.age INTO v_age FROM emp t WHERE t.name = v_name;
   dbms_output.put_line('年龄:' || v_age);
 END;

3.子程序：包括存储过程，函数和包
存储过程：用于执行特定的操作，可以指定输入参数（in）或者输出参数（out），不指定默认输入。

Sql代码

CREATE PROCEDURE test_update(name VARCHAR2, age NUMBER) IS
BEGIN
UPDATE emp t SET t.age = number WHERE t.name = name;
COMMIT;--这里不加可以吗？
END;

 CREATE PROCEDURE test_update(name VARCHAR2, age NUMBER) IS
 BEGIN
   UPDATE emp t SET t.age = number WHERE t.name = name;
   COMMIT;--这里不加可以吗？
 END;

函数：用于返回特定数据。函数的头部必须包含return字句，函数体内必须包含return语句返回的数据

Sql代码

CREATE FUNCTION test_function(name VARCHAR2, age NUMBER) RETURN VARCHAR2 IS
v_addr VARCHAR2(100);
BEGIN
SELECT '中国.山东' || t.addr
INTO v_addr
FROM emp t
WHERE t.name = name
and t.age = age;
RETURN v_addr;
END;

     CREATE FUNCTION test_function(name VARCHAR2, age NUMBER) RETURN VARCHAR2 IS
   v_addr VARCHAR2(100);
 BEGIN
   SELECT '中国.山东' || t.addr
     INTO v_addr
     FROM emp t
    WHERE t.name = name
      and t.age = age;
   RETURN v_addr;
 END;

包：用于逻辑组合相关的存储过程和函数，由包规范和包体组成。包规范用于定义公用的常量，变量，存储过程和函数。

Sql代码

-- 包规范知识定义了一个存储过程和函数而没有实现代码
CREATE PACKAGE test_pkg IS
PROCEDURE test_procedure(name VARCHAR2, age NUMBER);
FUNCTION test_function(name VARCHAR2, age NUMBER) RETURN NUMBER;
END;

-- 包规范知识定义了一个存储过程和函数而没有实现代码
 CREATE PACKAGE test_pkg IS
   PROCEDURE test_procedure(name VARCHAR2, age NUMBER);
   FUNCTION test_function(name VARCHAR2, age NUMBER) RETURN NUMBER;
 END;

包体：

Java代码

CREATE PACKAGE BODY test_pkg IS
PROCEDURE test_procedure(name VARCHAR2, age NUMBER) IS
BEGIN
UPDATE emp t set t.name = name WHERE t.age = age;
END;
FUNCTION test_function(name VARCHAR2, age NUMBER) RETURN NUMBER IS
v_slary NUMBER(4, 1);
BEGIN
SELECT t.slary
INTO v_slary
WHERE t.name = name
and t.age = age;
RETURN NUMBER v_slary;
END;
END;

CREATE PACKAGE BODY test_pkg IS
   PROCEDURE test_procedure(name VARCHAR2, age NUMBER) IS
   BEGIN
     UPDATE emp t set t.name = name WHERE t.age = age;
   END;
   FUNCTION test_function(name VARCHAR2, age NUMBER) RETURN NUMBER IS
     v_slary NUMBER(4, 1);
   BEGIN
     SELECT t.slary
       INTO v_slary
      WHERE t.name = name
        and t.age = age;
     RETURN NUMBER v_slary;
   END;
 END;

   在使用包中的存储过程和函数时，必须在存储过程或者函数名之前加上包名作为前缀
   (包名.子程序名)

4. 触发器：隐形执行的存储过程，必须指定触发事件以及触发操作，触发事件包括insert，update和delete；而触发操作实际上是一个pl/sql块。

Sql代码

-- 当更新dept表中的d_name列时更新emp表中的d_name列
REATE TRIGGER test_trigger
AFTER UPDATE OF d_name ON dept
FOR EACH ROW
BEGIN
UPDATE emp t SET t.d_name = :new.d_name WHERE t.d_name = o :d_name;
END;

  -- 当更新dept表中的d_name列时更新emp表中的d_name列
CREATE TRIGGER test_trigger
   AFTER UPDATE OF d_name ON dept
   FOR EACH ROW
 BEGIN
   UPDATE emp t SET t.d_name = :new.d_name WHERE t.d_name = o :d_name;
 END;

5. pl/sql数据类型：标量（Scalar）类型，复合（Composit）类型，参照（Reference）类型和LOB（Large Object）类型。

        标量（Scalar）类型：只能存放单个数值的变量；定义标量时必须指定数据类型。
       varchar2(n) : 定义可变长字符串，最大值32767字节；定义变量时必须指定长度。varchar2定义列时最大长度是4000字节。
       char(n) : 定义变量时如果没有指的那个长度默认为1，定义列数据时最大长度是2000字节。
       boolean ：布尔类型值为true，false和null。表列不能使用该数据类型
        binary_integer:定义整数-2147483647到2147483647
        binary_float 和 binary_double 是oracle10新增，分别用来定义单精度浮点数和双精度浮点数
       binary_float 应带有后缀f ,binary_double应带后缀d

   定义标量变量：

Sql代码

v_name varchar2(10);
v_age number(10,4);--整数部分是6位
v_sex constant vachar(10):='woman';
v_date Date;
v_flag boolean not null default false;

 v_name varchar2(10);
 v_age number(10,4);--整数部分是6位
 v_sex constant vachar(10):='woman';
 v_date Date;
 v_flag boolean not null default false;

Sql代码

--如果在定义变量时没有指定初始值那么变量的初始值为null。

%TYPE：当使用%type定义变量时,它会按照数据库列或者其他变量来确定新变量的类型和长度。

Sql代码

DECLARE
v_name emp.name%TYPE;
v_t_name v_name%TYPE;
v_age CONSTANT NUMBER(2) := 11;
BEGIN
SELECT name, age INTO v_name, v_age FROM emp WHERE emp.id = '45';
v_t_name := v_name;
dbms_output.put_line('名字是：' || v_t_name);
END;

  DECLARE   v_name   emp.name%TYPE;   v_t_name v_name%TYPE;   v_age CONSTANT NUMBER(2) := 11; BEGIN   SELECT name, age INTO v_name, v_age FROM emp WHERE emp.id = '45';   v_t_name := v_name;   dbms_output.put_line('名字是：' || v_t_name); END;

复合（Composit）类型:
复合变量用来存放多个值的变量。pl/sql复合数据类型包括记录，表，嵌套表以及数组（varry）；
记录：首先在定义部分定义记录类型和记录变量，然后再执行部分引用该记录变量

Sql代码

DECLARE
TYPE test_type_emp IS RECORD(
v_name emp.name%TYPE,
v_age emp.age%Type);
v_r_emp test_type_emp; -- 定义一个record变量test_type_emp
BEGIN
SELECT name, age INTO v_r_emp FROM emp WHERE emp.id = '45';
dbms_output.put_line('名字是：' || v_r_emp.v_name);
END;

 DECLARE
   TYPE test_type_emp IS RECORD(
     v_name emp.name%TYPE,
     v_age  emp.age%Type);
   v_r_emp test_type_emp; -- 定义一个record变量test_type_emp
 BEGIN
   SELECT name, age INTO v_r_emp FROM emp WHERE emp.id = '45';
   dbms_output.put_line('名字是：' || v_r_emp.v_name);
 END;

嵌套表（Nested Table）：类似高级语言的数组，只是嵌套表的个数没有限制。嵌套表可以作为
表列的数据类型。pl/sql表不能作为表列的数据类型。

Sql代码

--创建嵌套表
REATE OR REPLACE TYPE test_emp_type AS OBJECT
(
name varchar2(10),
age number
) ;

 --创建嵌套表
CREATE OR REPLACE TYPE test_emp_type AS OBJECT
 (
   name varchar2(10),
   age  number
 ) ;

Sql代码

CREATE OR REPLACE TYPE emp_type IS TABLE OF test_emp_type;

CREATE OR REPLACE TYPE emp_type IS TABLE OF test_emp_type;

       创建嵌套表后，就可以在表列或对象属性中将其作为用户自定义类型来引用。
       值得注意的是当使用嵌套表作为表列时必须专门为其制定专门的存储表。

Sql代码

CREATE TABLE hoom(
hname varchar2(100);
vemp emp_array
)NESTED TABLE STORE AS vemp

CREATE TABLE hoom(
    hname varchar2(100);
    vemp emp_array
 )NESTED TABLE STORE AS vemp

数组：类似嵌套表，varray的元素是有限制的。当使用varray数组时要先建立一个varray。当建立了varry类型之后，可以在表列或对象属性中将其作为用户自定义数据类型。

Sql代码

CREATE OR REPLACE TYPE test_emp_type AS OBJECT
(
name varchar2(10),
age number
)
;
CREATE OR REPLACE TYPE emp_array IS VARRY(10) OF test_emp_type;
CREATE TABLE hoom(
hname varchar2(100);
vemp emp_array
)

CREATE OR REPLACE TYPE test_emp_type AS OBJECT
 (
   name varchar2(10),
   age  number
 )
 ;

CREATE OR REPLACE TYPE emp_array IS VARRY(10) OF test_emp_type;
        
CREATE TABLE hoom(
    hname varchar2(100);
    vemp emp_array
 )

参照变量：用于存放数值指针的变量，使用参照变量，可以使应用程序共享相同对象包括游标（Cursor）和对象类型变量（obj_type）

Sql代码

DECLARE
TYPE c1 IS REF CURSOR;
emp_cursor c1;
v_name emp.name%TYPE;
v_age emp.age%TYPE;
BEGIN
OPEN emp_cursor FOR
SELECT name, age FROM emp WHERE emp.name = 'a';
LOOP
FETCH emp_cursor
INTO v_name, v_age;
EXIT WHEN emp_cursor%notfound;
dbms_output.put_line('名字是：' || v_name);
END LOOP;
CLOSE emp_cursor;
END;

网址：http://xiaomogui.iteye.com/blog/859245

推荐阅读

io
MyBatis多表查询与动态SQL使用

本文介绍了MyBatis多表查询与动态SQL的使用方法，包括一对一查询和一对多查询。同时还介绍了动态SQL的使用，包括if标签、trim标签、where标签、set标签和foreach标签的用法。文章还提供了相关的配置信息和示例代码。 ... [详细]

蜡笔小新 2023-12-12 17:12:51
io
VB.NET在线急等问题解决方法，如何统计数据库字段下的数据并显示在文本框里？

本文介绍了一个在线急等问题解决方法，即如何统计数据库中某个字段下的所有数据，并将结果显示在文本框里。作者提到了自己是一个菜鸟，希望能够得到帮助。作者使用的是ACCESS数据库，并且给出了一个例子，希望得到的结果是560。作者还提到自己已经尝试了使用"select sum(字段2) from 表名"的语句，得到的结果是650，但不知道如何得到560。希望能够得到解决方案。 ... [详细]

蜡笔小新 2023-12-13 15:15:30
fetch
PHP操作MySql数据库_PHP教程

PHP操作MySql数据库_PHP教程:链接数据库$conn@mysql_connect(localhost,root,88888888)ordie(链接错误);解决中文乱码mys ... [详细]

蜡笔小新 2023-10-17 04:43:28
io
基于PgpoolII的PostgreSQL集群安装与配置教程

本文介绍了基于PgpoolII的PostgreSQL集群的安装与配置教程。Pgpool-II是一个位于PostgreSQL服务器和PostgreSQL数据库客户端之间的中间件，提供了连接池、复制、负载均衡、缓存、看门狗、限制链接等功能，可以用于搭建高可用的PostgreSQL集群。文章详细介绍了通过yum安装Pgpool-II的步骤，并提供了相关的官方参考地址。 ... [详细]

蜡笔小新 2023-12-14 19:10:25
blob
数据库的存储结构及其重要性

本文介绍了数据库的存储结构及其重要性，强调了关系数据库范例中将逻辑存储与物理存储分开的必要性。通过逻辑结构和物理结构的分离，可以实现对物理存储的重新组织和数据库的迁移，而应用程序不会察觉到任何更改。文章还展示了Oracle数据库的逻辑结构和物理结构，并介绍了表空间的概念和作用。 ... [详细]

蜡笔小新 2023-12-14 16:00:02
process
如何限制php数据库链接数和连接超时时间？

本文介绍了如何使用php限制数据库插入的条数并显示每次插入数据库之间的数据数目，以及避免重复提交的方法。同时还介绍了如何限制某一个数据库用户的并发连接数，以及设置数据库的连接数和连接超时时间的方法。最后提供了一些关于浏览器在线用户数和数据库连接数量比例的参考值。 ... [详细]

蜡笔小新 2023-12-14 14:06:10
io
如何使用Java获取服务器硬件信息和磁盘负载率

本文介绍了使用Java编程语言获取服务器硬件信息和磁盘负载率的方法。首先在远程服务器上搭建一个支持服务端语言的HTTP服务，并获取服务器的磁盘信息，并将结果输出。然后在本地使用JS编写一个AJAX脚本，远程请求服务端的程序，得到结果并展示给用户。其中还介绍了如何提取硬盘序列号的方法。 ... [详细]

蜡笔小新 2023-12-14 13:56:20
io
Oracle Database 10g许可授予信息及高级功能详解

本文介绍了Oracle Database 10g许可授予信息及其中的高级功能，包括数据库优化数据包、SQL访问指导、SQL优化指导、SQL优化集和重组对象。同时提供了详细说明，指导用户在Oracle Database 10g中如何使用这些功能。 ... [详细]

蜡笔小新 2023-12-14 13:12:10
spring
SpringJdbcTemplate的使用详解

本文详细介绍了Spring的JdbcTemplate的使用方法，包括执行存储过程、存储函数的call()方法，执行任何SQL语句的execute()方法，单个更新和批量更新的update()和batchUpdate()方法，以及单查和列表查询的query()和queryForXXX()方法。提供了经过测试的API供使用。 ... [详细]

蜡笔小新 2023-12-13 14:27:11
io
高质量SQL书写的30条建议

本文提供了30条关于优化SQL的建议，包括避免使用select *，使用具体字段，以及使用limit 1等。这些建议是基于实际开发经验总结出来的，旨在帮助读者优化SQL查询。 ... [详细]

蜡笔小新 2023-12-13 13:24:33
io
问题2：重新命名或者移动数据文件、日志文件到新的位置

本文讨论了在数据库打开和关闭状态下，重新命名或移动数据文件和日志文件的情况。针对性能和维护原因，需要将数据库文件移动到不同的磁盘上或重新分配到新的磁盘上的情况，以及在操作系统级别移动或重命名数据文件但未在数据库层进行重命名导致报错的情况。通过三个方面进行讨论。 ... [详细]

蜡笔小新 2023-12-13 13:02:24
filter
关于Linq to sql 实现模糊查询 string数组

前景：当UI一个查询条件为多项选择，或录入多个条件的时候，比如查询所有名称里面包含以下动态条件，需要模糊查询里面每一项时比如是这样一个数组条件：newstring[]{兴业银行, ... [详细]

蜡笔小新 2023-12-13 09:34:59
fetch
如何在php中将mysql查询结果赋值给变量

本文介绍了在php中将mysql查询结果赋值给变量的方法，包括从mysql表中查询count(学号)并赋值给一个变量，以及如何将sql中查询单条结果赋值给php页面的一个变量。同时还讨论了php调用mysql查询结果到变量的方法，并提供了示例代码。 ... [详细]

蜡笔小新 2023-12-12 18:22:57
io
Oracle seg,V$TEMPSEG_USAGE与Oracle排序的关系及使用方法

本文介绍了Oracle seg,V$TEMPSEG_USAGE与Oracle排序之间的关系，V$TEMPSEG_USAGE是V_$SORT_USAGE的同义词，通过查询dba_objects和dba_synonyms视图可以了解到它们的详细信息。同时，还探讨了V$TEMPSEG_USAGE的使用方法。 ... [详细]

蜡笔小新 2023-12-12 17:57:15
io
PHP连接MySQL的2种方法小结以及防止乱码【PHP】

后端开发|php教程PHP,MySQL,乱码后端开发-php教程PHP的MySQL配置报错信息：ClassmysqlinotfoundinAnswer:1.在confphp.ini ... [详细]

蜡笔小新 2023-10-17 11:15:45

sunhuan

Tags | 热门标签

RankList | 热门文章