create or replace procedure sp_pro2 is
--执行部分
begin
delete from mytest where name='lgd';
end;
------------------------------------------------------------------------------------------------------------------
--创建一个新表根据已经有的表mytest来创建一个名字为mytest2的新表
create table mytest2 as select * from mytest;
set serveroutput on --打开输出选项
begin
dbms_output.put_line('hello');
end;
----------------------------------------------------------------------------------------------------------
--定义部分和执行部分的pl/sql块
declare
v_ename varchar2(5);
begin
select ename into v_ename from emp where empno=&no;
dbms_output.put_line('雇员名:'||v_ename);
end;
------------------------------------------------------------------------------------------------------------
--create or replace procedure sp_pro3 is
--定义部分
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;
-----------------------------------------------------------------------------------------------------------------
--案例4 输入参数，输出
create or replace procedure sp_pro3 (sp_name varchar2,sp_newsal number) is
begin
update emp set sal=sp_newsal where ename=sp_name;
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 abc number;
call sp_fun1('KING') into:abc;
------------------------------------------------------------------------------------------------------- ---------
--计算一个人的个人所得税
--假设税率为0.03
declare
c_tax_rate number(3,2):=0.03;
--用户名
v_ename emp.ename%type;
v_sal emp.sal%type;
v_sal_tax number(7,2);
begin
--开始执行
select ename,sal into v_ename,v_sal from emp where empno=&no;
v_sal_tax:=v_sal*c_tax_rate;
dbms_output.put_line('客户姓名为:'||v_ename||'工资为:'||v_sal||'应交税为:'||v_sal_tax);
end;
-----------------------------------------------------------------------------------------------------
--pl/sql 记录实例1
declare
--定义一个pl/sql记录类型 emp_record_type的类型，类型包括三个数据类型name,salary,title
type emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type);
--定义一个sp_record变量，这个变量的类型是emp_record_type
sp_record emp_record_type;
begin
select ename,sal,job into sp_record
from emp where empno=7788;
dbms_output.put_line('员工名:'||sp_record.name);
end;
------------------------------------------------------------------------------------------------
--pl/sql 记录实例
declare
--定义一个pl/sql记录类型 emp_record_type的类型，类型包括三个数据类型name,salary,title
type emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type);
--定义一个sp_record变量，这个变量的类型是emp_record_type
sp_record emp_record_type;
begin
select ename,sal,job into sp_record
from emp where empno=7788;
dbms_output.put_line('员工名:'||sp_record.name||' 工资为:'||sp_record.salary);
end;
--------------------------------------------------------------------------------------------------
--pl/sql 表实例
declare
--定义了一个pl/sql表类型sp_table_type ,该类型是用于存放emp.ename%type
--index by binary_integer 表示下标是整数
type sp_table_type is table of emp.ename%type index by binary_integer;
sp_table sp_table_type;
begin
select ename into sp_table(0) from emp where empno=7788;
dbms_output.put_line('员工名:'||sp_table(0));
end;
--------------------------------------------------------------------------------------------------------
--请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工的姓名和工资
declare
--定义游标类型 sp_emp_cursor
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
--定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--执行
--把test_crusor和一个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;
-----------------------------------------------------------------------------------------------------
--编写一个过程，可以输入一个雇员名，如果该雇员的工资低于1000，就
--给该雇员的工资增加10%
create or replace procedure sp_pro4(spName varchar2) is
--定义变量
v_sal emp.sal%type;
begin
--执行
select sal into v_sal from emp where ename=spName;
--判断
if v_sal<1000 then
update emp set sal=sal*1.1 where ename=spName;
end if;
end;
--------------------------------------------------------------------------------------------------------------------------------
--二重条件分支 if-then-else
--编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0，就在原来的基础上增加100；如果补助为0则
--补助设置为200
create or replace procedure sp_pro5(spName varchar2) is
--定义变量
v_comm emp.comm%type;
begin
--执行
select comm into v_comm from emp where ename=spName;
if v_comm<>0
then update emp set comm=comm+100 where ename=spName;
else
update emp set comm=200 where ename=spName;
end if;
end;
----------------------------------------------------------------------------------------------------------------------
--编写一个过程，可以输入一个雇员编号，如果该雇员的职位为
--PRESIDENT 就给他的工资增加1000，如果该雇员的职位为MANAGER
--就给他的工资增加500，其他职位的工资增加200
create or replace procedure sp_pro6(sNo number) is
--定义变量
v_job emp.job%type;
begin
--执行
select job into v_job from emp where empno=sNo;
if v_job='PRESIDENT' then
update emp set sal=sal+1000 where empno=sNo;
elsif v_job='MANAGER'
then update emp set sal=sal+500 where empno=sNo;
else
update emp set sal=sal+200 where empno=sNo;
end if;
end;
---------------------------------------------------------------------------------------------------------
create or replace procedure sp_pro7(sNumber number) is
--定义变量
v_jobb emp.job%type;
begin
--执行
select job into v_jobb from emp where empno=sNumber;
if v_jobb='PRESIDENT' then
update emp set sal=sal+1000 where empno=sNumber;
elsif v_jobb='MANAGER'
then update emp set sal=sal+500 where empno=sNumber;
else
update emp set sal=sal+200 where empno=sNumber;
end if;
end;
------------------------------------------------------------------------------------------------------------
--创建一个存储过程，就是根据输入的一个用户名字，来插入10个用户，当插入10个用户的时候，则退出循环
--首先创建一个users表
create table users(userNo number,userName varchar2(20));
--创建一个存储过程
create or replace procedure sp_pro8(name varchar2) is
v_no number:=1;
begin
loop
insert into users values(v_no,name);
exit when v_no=10;
v_no:=v_no+1;
end loop;
end;
-----------------------------------------------------------------------------------------------------------
--用while循环来判断
create or replace procedure sp_pro9(name varchar2) is
v_no number:=11;
begin
while v_no<=20
loop
insert into users values(v_no,name);
v_no:=v_no+1;
end loop;
end;
----------------------------------------------------------------------------------------------------------
--day7
--创建一个book表
create table book(bookId number,bookName varchar2(50),publishHouse varchar2(50));
--编写过程
create or replace procedure sp_pro10(spbookId in number,spbookName in varchar2,sppublishHouse in varchar2) is
begin
insert into book values(spbookId,spbookName,sppublishHouse);
end;
--------------------------------------------------------------------------------------------------
--有输入和输出的存储过程
create or replace procedure sp_pro11(spno in number,spName out varchar2) is
begin
select ename into spName from emp where empno=spno;
end;
-----------------------------------------------------------------------------------------------------------------------------------
--1返回结果集的过程,在该包中定义了一个类型 test_cursor
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
--2 创建存储过程
create or replace procedure sp_pro12(spNo in number,p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from emp where deptno=spNo;
end;
--3 如何在java中调用存储过程
------------------------------------------------------------------------------------------------------------
--编写一个存储过程，要求输入表名，每页显示记录数，当前页，返回总记录数，总页数，和返回的结果集
create or replace procedure sp_page(sp_tableName in varchar2,sp_perpageRecord in number,
sp_currentPage in number,sp_alltotalRecord out number,sp_totalPage out number,sp_resultcusor
out testpackage.test_cursor) is
declare
--开始索引位置
v_firstrow number;
--一页的末尾索引位置
v_lastrow number;
begin
select count(*) into sp_alltotalRecord from sp_tableName;
sp_totalPage=ceil(sp_alltotalRecord/sp_perpageRecord);
--当分页的时候为整页的时候
if sp_currentPage<=sp_totalPage
then
-- sp_totalPage=sp_alltotalRecord/sp_perpageRecord;
-- v_startindex=sp_currentPage*sp_perpageRecord+1;
-- v_endindex=v_startindex+sp_perpageRecord;
v_firstrow = (sp_currentPage–1)*sp_perpageRecord+1;
v_lastrow = sp_currentPage * sp_perpageRecord;
open sp_resultcusor for select * from sp_tableName where
--当分页的时候不为整页时
/**
else
then
sp_totalPage=sp_alltotalRecord/sp_perpageRecord+1;
v_startindex=sp_currentPage*sp_perpageRecord+1;
v_endindex=v_startindex+v_allpagetemp;
*/
end if;
end;
--------------------------------------------------------------------------------------------------------------
--创建显示索引的格式
select t1.*,rownum rn from(select * from emp) t1;
SQL> select t1.*,rownum rn from(select * from emp) t1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7369 SMITH CLERK 7902 1980-12-17 880.00 200.00 20 1
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 400.00 30 2
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 3
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 4
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 5
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 6
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 8
7839 KING PRESIDENT 1981-11-17 6000.00 10 9
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 10
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 11
7900 JAMES CLERK 7698 1981-12-3 1045.00 30 12
7902 FORD ANALYST 7566 1981-12-3 3000.00 20 13
7934 MILLER CLERK 7782 1982-1-23 1300.00 10 14
上面多了rn这列数值
----------------------------------------------------------------------------------------------------------
--选择一个小于等于10的记录
select t1.*,rownum rn from(select * from emp) t1 where rownum<=10;
--分页时可以使用下面的模板
select * from(select t1.*,rownum rn from(select * from emp) t1 where rownum<=10) where rn>=6;
--开始编写分页的过程
create or replace procedure fenye
(tableName in varchar2,
Pagesize in number,
pageNow in number,
myrows out number, --总记录数
myPageCount out number,--总页数
sp_cursor out testpackage.test_cursor) is
--定义部分
v_begin number:=(pageNow-1)*Pagesize+1;
v_end number:=pageNow*Pagesize;
v_sql varchar2(1000);
--定义
begin
--执行部分
v_sql:='select * from(select t1.*,rownum rn from(select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin||'';
--把游标和sql关联起来
open sp_cursor for v_sql;
--计算myrows和myPageCount
--组织一个sql
v_sql:='select count(*) from '||tableName||'';
--执行sql,并把返回的值，赋给myrows
execute immediate v_sql into myrows;
--计算myPageCount
if mod(myrows,Pagesize)=0 then
myPageCount:=myrows/Pagesize;
else
myPageCount:=myrows/Pagesize+1;
end if;
end;
--列外实例
--定义
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp where empno=&gno;
dbms_output.put_line('名字:'||v_ename);
exception
when no_data_found then
dbms_output.put_line('该客户编号没有');
end;
----------------------------------------------------------------------------------------------
declare
cursor emp_cursor is select ename,sal from emp;
emp_record emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line('客户名:'||emp_record.ename||'薪水:'||emp_record.sal);
end loop;
end;
----------------------------------------------------------------------------
扫一扫
274
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
