--pl/sql
--块：由函数（pl语法，sql语句）组成；异常，过程，函数，包，触发器，事务控制（对数据库产生改变时）
--支持流程控制，块的嵌套
/*块的三个部分其中第一三部分可以省略
1.声明部分
declare
2.执行部分
begin
end;
3.异常处理部分
exception
when ?? then ??;
*/
--块可以命名和匿名
--set serveroutput on;
begin
dbms_output.put_line('hello');
end;
create procedure sp_insert is
begin
insert into mytable values (25, 'jack');
end;
/
exec sp_insert; create or replace procedure sp_delete is
begin
delete from mytable where id = 25;
end;
/
exec sp_delete;
set serveroutput on --打开输出选项；
begin
dbms_output.put_line('hello,word');
end;
/
declare v_ename varchar2(5);
begin
select ename into v_ename from emp where empno = &no; dbms_output.put_line('雇员名：' || v_ename);
end;
/
-------------
declare v_ename varchar2(5); v_sal number(7, 2);
begin
select ename, sal into v_ename, v_sal from emp where empno = &no; dbms_output.put_line('雇员名：' || v_ename || ';工资：' || v_sal);
--异常处理
exception
when no_data_found then dbms_output.put_line('编号找不到，或输入有误!');
end;
/
--过程
/*
过程用于执行特定的，当建立过程时，既可以指定输入参数in，
也可以批定输出out，通过地过程中使用输入参数，可以次数据
传递到执行部分；通过使用输出参数，要以快乐幸福执行部分的
数据传递到用，在sqlplus中要create procedura命令来建立过程
*/
create procedure sp_update(spName varchar2, newSal number) is
begin
--执行部分，根据用户名支无话不谈工资，
update myemp set sal = newSal where ename = spName;
end;
/
--函数：输入雇员的姓名，返回该雇员的年薪
create function sp_fun1(spName varchar2) return number is
yearSal number(7, 2);
begin
select sal * 12 + nvl(comm, 0) * 12 into yearSal from emp where ename = spName; return yearSal;
end;
/
var ac number; call sp_fun1('SCOTT') into :ac;
--包
/*
包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成
create package sp_package is
procedure update_sal(name varchar2,newSal number);
function sp_fun1(name varchar2) return number;
end;
*/
--触发器
/*
指隐含的执行的存储过程，当定义触发器时，必须要指定触发的事件
和触发的操作，常用的触发事件包括insert,update,delete语句
面触发实际就是一个pl/sql块，可以使用create tigger 来建立触发器
*/
--变量类型
/*
scalar标量类型
v_ename varchar2(10);
v_sal number(6,2):=5.4;
v_hiredate date;
v_valid boolean not null default false;
*/
declare c_tax_rate number(3, 2) := 0.03; v_ename emp.ename%type; --v_ename的类型和表emp.ename的类型一样
v_sal number(7, 2); v_tax_sal number(7, 2);
begin
select ename, sal into v_ename, v_sal from emp where empno = &no; v_tax_sal := v_sal * c_tax_rate; dbms_output.put_line('name=' || v_ename || '--sal' || v_sal || '==tax' || v_tax_sal);
end;
/
---------------------------
declare
--定义一种数据类型，类似于c语言中的结构体
type emp_record_type is
record(name emp.ename%type, salary emp.sal%type);
--声明一个emp_record_type类型的变量sp_record
sp_record emp_record_type;
begin
select ename, sal into sp_record from emp where empno = &no; dbms_output.put_line('姓名=' || sp_record.name || '；工资=' || sp_record.salary);
end;
/
-----------------------
declare
--定义一种数据类型，类似于c语言中的结构体
type emp_record_type is
table of emp.ename%type index by binary_integer;
--声明一个emp_record_type类型的变量sp_record
sp_record emp_record_type;
begin
select ename into sp_record(1) from emp where empno =7369;
dbms_output.put_line('姓名=' || sp_record(1) );
end;
/
---------输入部门号，显示所有员工
declare
--定义游标
type sp_emp_cursor is ref cursor;
--定义游标变量
test_cursor sp_emp_cursor;
--定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--把test_cursor和一个select结合
open test_cursor for select ename,sal from emp where deptno=&no;
--循环取出
loop
fetch test_cursor into v_ename,v_sal;
--判断test_cursor是否为空
exit when test_cursor%notfound;
dbms_output.put_line('姓名：'||v_ename||'工资：'||v_sal);
end loop;
end;
--编写一个过程，可以输入一个雇员名，如果亥雇员的工资低于2000，
--就给该雇员式资增加10%；
create or replace procedure sp_addSal(spName varchar2) is
v_sal myemp.sal%type;
begin
select sal into v_sal from emp where ename=spName;
if v_sal<2000 then
update myemp set sal = sal + sal*0.1 where ename = spName;
end if ;
end;
--编写一个过程，可以输入一个雇员名，如果该雇员
--的补助不是0就在原来的基础上加100，是0就加200；
--
create or replace procedure sp_addCommit(spName varchar2) is
v_comm myemp.comm%type;
begin
select nvl(comm,0) into v_comm from myemp where ename=spName;
if v_comm<>0 then
update myemp set comm=comm+100 where ename=spName;
else
dbms_output.put_line(v_comm||'');
update myemp set comm=nvl(comm,0)+200 where ename=spName;
end if;
end;
/*
编写一个过程，可以输入一个雇员编号，如果该雇员的职位是president就给他的
工资增加1000，如果该雇员的职位是manger就给他的工资增加500，其它职位的
雇员工资增加200
*/
create or replace procedure sp_updateSal(spEmpno number) is
v_job myemp.job%type;
begin
select job into v_job from myemp where empno = spEmpno;
if v_job='PRESIDENT' then
update myemp set sal = sal + 1000 where empno=spEmpno;
elsif v_job='MANAGER'then
update myemp set sal = sal + 500 where empno=spEmpno;
else
update myemp set sal = sal + 200 where empno=spEmpno;
end if;
end;
------循环语句
create table userTest(
id number(4),
name varchar(10)
);
create or replace procedure sp_For(spName varchar2) is
v_i number:=1;
begin
/*loop
insert into userTest values(v_i,spName);
exit when v_i=10;
v_i:=v_i+1;
end loop;*/
<<start_loop>>
while v_i<=10 loop
insert into userTest values(v_i,spName);
exit when v_i=10;
v_i:=v_i+1;
if v_i=5 then
go to start_loop;
else
null;--空语句
end if;
end loop;
end;
--编写分页过程
create table book
(
id number,
name varchar2(50),
publishhouse varchar2(50)
);
create or replace sp_insertBook(spId number,spName varchar2,spHouse varchar2)
is
begin
insert into book values(spId,spName,spHouse);
end;
select rownum,e.* from (select * from emp) e where rownum<=10 ;
select * from emp;
select * from (select rownum rn,e.* from (select * from emp) e where rownum<=10) where rn>5;
select * from (select t1.*,rownum rn from (select * from emp where rownum<=10) t1) where rn>=5;
--------------------------------------------------------------
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
/*
2
1 1,2
2 3,4
3
1 1,3
2 4,6
3 7,8
4(m)
1 1,4
2 5,8
3 9,12
4 13,16
. .,.
n s,e
e=n*m;
s=(n*m)-m+1=n*m-m+1;
13=4*4-4+1;
3=2*2-2+1;
9=4*3-4+1;
*/
create or replace procedure fenye(tableName in varchar2,
mypagesize in number,--一页显示的记录数
pagenow in number,--向显示第几页
myrows out number,--总记录数
mypagecount out number,--总页数
p_cursor out testpackage.test_cursor--返回的记录数
) is
v_sql varchar2(1000);
v_begin number:=pagenow*mypagesize-mypagesize+1;--开始记录次序
v_end number:=pagenow*mypagesize;--结束记录次序
begin
v_sql:= 'select * from (select t1.*,rownum rn from (select * from '
||tableName||' where rownum<='||v_end||') t1) where rn>='||v_begin||';';
open p_cursor for v_sql;--自动转换为sql语句
--计算总记录数和总也数；
v_sql:='select count(*) from '||tableName;
execute immediate v_sql into myrows;
if mod(myrows,mypagesize)=0 then
mypagecount = myrows/mypagesize;
else
mypagecount = myrows/mypagesize+1;
end if;
end;
--异常处理：预定义例外，非预定义例外和自定义例外三种
set serveroutput on --打开输出选项；
create or replace procedure sp_query
is
v_name emp.ename%type;
begin
select ename into v_name from emp where empno=&no;
dbms_output.put_line('姓名是：'||v_name);
exception
when no_data_found then dbms_output.put_line('编号找不到，或输入有误!');
end;
/
declare
v_name emp.ename%type;
begin
select ename into v_name from emp where empno=&no;
dbms_output.put_line('姓名是：'||v_name);
exception
when no_data_found then --no_data_found 预定义列外
dbms_output.put_line('编号找不到，或输入有误!');
end;
/
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=&no;
case
when v_sal<1000 then
dbms_output.put_line('<10000');
when v_sal<2000 then
dbms_output.put_line('<20000');
end case;
exception
when case_not_found then
dbms_output.put_line('不再范围呃逆');
end;
/
--dup_val_on_index在唯一索引所对应的列上插入重复的值时，会隐含的触发例外
--invalid_cursor当试图在不合法的游标上报告操作时，会触发该例外，例如；试
--图、从没有打开的游标提取数据，或是关关闭没有打开的游标则会该例外
--invalid_number当输入的数据有误时
--too_many_rows当执行 select into语句时，如果返回超过一行刚会触发该例外
--zero_divide 10/0
--value_error当在执行同仁时，如果变量的长度不足以容纳实际数据，则会触发该例外 v_ename varchar2(5)
--ename varchar2(50)；
--自定义例外
create or replace procedure ex_test(spNo number)
is
myex exception;
begin
update myemp set sal = sal +1000 where empno=spNo;
if sql%notfound then --这是表示没有update成功
raise myex;--raise触发myex例外
end if;
when myex then
dbms_output.put_line('没有更新成功！');
end;
-------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
/*
2
1 1,2
2 3,4
3
1 1,3
2 4,6
3 7,8
4(m)
1 1,4
2 5,8
3 9,12
4 13,16
. .,.
n s,e
e=n*m;
s=(n*m)-m+1=n*m-m+1;
13=4*4-4+1;
3=2*2-2+1;
9=4*3-4+1;
*/
create or replace procedure fenye(tableName in varchar2,
mypagesize in number,--一页显示的记录数
pagenow in number,--向显示第几页
myrows out number,--总记录数
mypagecount out number,--总页数
p_cursor out testpackage.test_cursor--返回的记录数
) is
v_sql varchar2(1000);
v_begin number:=pagenow*mypagesize-mypagesize+1;--开始记录次序
v_end number:=pagenow*mypagesize;--结束记录次序
begin
v_sql:= 'select * from (select t1.*,rownum rn from (select * from '
||tableName||' where rownum<='||v_end||') t1) where rn>='||v_begin||';';
open p_cursor for v_sql;--自动转换为sql语句
--计算总记录数和总也数；
v_sql:='select count(*) from '||tableName;
execute immediate v_sql into myrows;
if mod(myrows,mypagesize)=0 then
mypagecount := myrows/mypagesize;
else
mypagecount := myrows/mypagesize+1;
end if;
end;
/
分享到：
相关推荐
PL/SQL是Oracle公司开发的一种过程化SQL扩展，它是Oracle数据库的重要组成部分，用于在数据库服务器上编写存储过程、函数、触发器、包等可执行...通过学习和掌握PL/SQL，开发者可以构建高效、复杂的数据库应用程序。
### PL/SQL学习笔记4 —— 集合与成员函数 #### 一、PL/SQL 表（索引表） 在PL/SQL中，**索引表**（也称为**PL/SQL表**）是一种非常有用的结构，它类似于数组但具有更多的灵活性。这种表是非持久化的，即它们不会...
在这个“我的PL/SQL学习笔记（一）”中，我们将探讨PL/SQL的基础知识，包括其语法结构、变量声明、流程控制以及如何与Oracle数据库中的数据进行交互。 首先，PL/SQL的基本结构分为声明部分、执行部分和异常处理部分...
在PL/SQL编程中，游标是处理查询结果集的重要工具。它们允许程序逐行处理结果，而不是一次性加载所有数据，这对于大型数据集尤其有用，因为它可以节省内存并提高性能。下面将详细解释游标的基本概念、分类以及如何在...
本文将深入探讨从"SQL,PL/SQL学习笔记"中提取的关键知识点，帮助编程人员更好地理解和运用这两种语言。 首先，我们关注SQL并行查询。通过`ALTER SESSION ENABLE PARALLEL DMl`，我们可以开启会话的并行DML操作，这...
在PL/SQL编程中，存储过程和函数是关键的组件，它们允许我们将可重用的代码逻辑存储在数据库中，以便于管理和调用。本文主要探讨了存储过程、函数以及程序包的概念，特点，创建方法，执行方式，权限管理以及参数处理...
PL/SQL数据库学习笔记 PL/SQL是一种高级的程序语言，主要用于Oracle数据库管理系统中。下面是PL/SQL数据库学习笔记的知识点总结。 一、基本原则 *瀑布模型：需求分析→设计（概要设计，详细设计：SQL语句，变量...
在学习PL/SQL的过程中，理解这些基本概念和结构是至关重要的。通过实践编写存储过程、函数和触发器，你可以逐渐掌握PL/SQL的强大功能，并利用它来解决实际的数据库编程问题。对于初学者来说，循序渐进、结合实例学习...
在PL/SQL的学习中，分区是数据库管理大型数据集的一种高效方法，特别是在处理大数据量时。本篇笔记主要探讨了何时应该使用分区以及Oracle支持的分区类型。 首先，当面对超过2GB的大数据表时，分区是十分必要的。这...
ORACLE PL/SQL是从入门到精通的专业知识，涵盖了数据库开发与管理的多个方面，包括...这本书籍将为读者提供一个全面、系统的学习路径，帮助数据库管理员、开发人员深入理解并掌握ORACLE PL/SQL的强大功能和应用技巧。
总的来说，“PL/SQL超级笔记”应该涵盖了从基本语法到高级特性的全面教程，通过学习，新手可以逐步掌握如何使用PL/SQL进行数据库编程，从而更好地管理和操作Oracle数据库。配合"oracle_ppt"中的PPT材料，学习效果会...
### PL/SQL听课笔记 #### 一、PL/SQL简介 **PL/SQL**（Procedural Language for SQL）是一种专门为Oracle数据库设计的过程化语言扩展。它是在标准SQL基础上增加了一系列高级编程特性，如变量、控制结构、函数、...
### PL/SQL 学习笔记知识点详解 #### 1. PL/SQL 基本结构 - **DECLARE**: 在此部分声明变量、常量、数据类型及游标。 - **BEGIN**: 主程序体开始，可以包含任何合法的PL/SQL语句。 - **EXCEPTION**: 异常处理部分，...
这篇课堂笔记主要涵盖了基础的SQL查询语法和部分PL/SQL概念。 首先，SQL查询的基础是从数据库中选择数据。`SELECT`语句用于指定需要选取的列，如`SELECT ename, sal, job FROM emp;`。字段顺序可以自由调整，例如`...