存储过程的学习

第一个链接学习存储过程是会遇到的问题：   https://www.cnblogs.com/bekeyuan123/p/7028101.html

第二个链接存储过程基本语法介绍：  https://www.cnblogs.com/zhongshiqiang/p/6169946.html

第三个链接mysql和sqlserver中的存储过程介绍：  https://blog.csdn.net/cc41798520101/article/details/52095055

存储过程扫盲贴： https://blog.csdn.net/yangzhawen/article/details/8617179

 

oracle数据库相关权限设定

 

采用sys or system / manager as sysdba; 连接数据库。
创建普通用户qiaochengqiang: create user qiaochengqiang identified by pwd_oracle;
以下栗子中均是以qiaochengqiang为数据库用户举例；

删除用户
drop user qiaochengqiang;

授予用户登录数据库的权限
grant create session to qiaochengqiang;

授予用户操作表空间的权限：
grant unlimited tablespace to qiaochengqiang;
grant create tablespace to qiaochengqiang;
grant alter tablespace to qiaochengqiang;
grant drop tablespace to qiaochengqiang;
grant manage tablespace to qiaochengqiang;

授予用户操作表的权限：
grant create table to qiaochengqiang; (包含有create index权限, alter table, drop table权限)

授予用户操作视图的权限:
grant create view to qiaochengqiang; (包含有alter view, drop view权限)

授予用户操作触发器的权限：
grant create trigger to qiaochengqiang; (包含有alter trigger, drop trigger权限)

授予用户操作存储过程的权限：
grant create procedure to qiaochengqiang;(包含有alter procedure, drop procedure 和function 以及 package权限)

授予用户操作序列的权限：
grant create sequence to qiaochengqiang; (包含有创建、修改、删除以及选择序列)

授予用户回退段权限：
grant create rollback segment to qiaochengqiang;
grant alter rollback segment to qiaochengqiang;
grant drop rollback segment to qiaochengqiang;

授予用户同义词权限：
grant create synonym to qiaochengqiang;(包含drop synonym权限)
grant create public synonym to qiaochengqiang;
grant drop public synonym to qiaochengqiang;

授予用户关于用户的权限：
grant create user to qiaochengqiang;
grant alter user to qiaochengqiang;
grant become user to qiaochengqiang;
grant drop user to qiaochengqiang;

授予用户关于角色的权限：
grant create role to qiaochengqiang;

授予用户操作概要文件的权限
grant create profile to qiaochengqiang;
grant alter profile to qiaochengqiang;
grant drop profile to qiaochengqiang;

允许从sys用户所拥有的数据字典表中进行选择
grant select any dictionary to qiaochengqiang;

存储过程学习准备工作：

1、给用户赋予权限（当提示权限不足时，看上面并赋予权限）

grant create procedure to qiaochengqiang; --赋予qiaochengqiang这个用户创建存储过程的权限
grant create table to qiaochengqiang; --赋予qiaochengqiang创建表的权限

 2、创建表

create table qq(  --创建表qq  三个属性 id name age,id设为主键
       id number(10) primary key,
       name varchar2(100),
       age number(10)
)

 3、创建序列（为主键自增做准备）

create sequence qq_seq  --创建序列，让qq的主键为自增
       increment by 1  --每次加几个
       start with 1  --开始位置
       nomaxvalue  --没有最大值
       nocycle  --一直累加，不循环
       nocache  --不建缓冲区

 4、创建触发器

create trigger qq_tri before  --然后创建触发器将序列和表结合起来从而达到主键自增
       insert on qq for each row when (new.id is null)
       begin 
              select qq_seq.nextval into:new.id from dual;
              end;

5、在表中添加数据

insert into qq(name,age) values('qcq',26);  --添加语句，因为主键自增，所以就不用再给主键赋值了
insert into qq(name,age) values('yxj',27);
insert into qq(name,age) values('tt',18);

添加一条信息的存储过程：

create or replace procedure addMsg(pid number,pname varchar2,page number) 
       is
       begin
        insert into q(id,xingming,age)values(pid,pname,page);
        --dbms_output.put_line()  数据库输出信息
        commit;  
       end; 

调用存储过程两种（以下相同）：

第一种

call addMsg(1,'3',4);

第二种　

begin
     addMsg(1,'3',4);
     end;　

编辑一条信息：

create or replace procedure uptMsg
       (
          pid in number,pname in varchar2,page in number
       )
       is
       begin 
          update q set xingming=pname,age=page where id=pid;
          if SQL%Found Then
          dbms_output.put_line('更新成功');
          else
          dbms_output.put_line('更新失败');
          end if;
          commit;
          end;

删除一条信息：　

create or replace procedure delMsg
       (
          pid in number
       )
       is
       begin 
          delete q where id=pid;
          if SQL%Found Then
          dbms_output.put_line('更新成功');
          else
          dbms_output.put_line('更新失败');
          end if;
          commit;
          end;

查询一条信息

create or replace procedure selMsg
       (inid in qq.id%type)  --输入参数  判断条件
as
       outid qq.id%type;  --输出参数  outid作为qq表中的id输出
       outname qq.name%type;  --outname作为qq表中的name输出
begin
       select id,name into outid,outname from qq where id = inid;
       dbms_output.put_line('id: '||outid||',name: '||outname);  --引号里面放的是输出的字符串等内容，||为拼接的意思，这边一定要处理好不然存储过程会报错
       exception 
       when no_data_found then  
       dbms_output.put_line('没有符合的记录');
       when too_many_rows then
       dbms_output.put_line('返回数据过多');
       when others then
       dbms_output.put_line('发生意外错误');
       end;
       
call selMsg(2);  --执行存储过程，传进去参数为2

还涉及游标（貌似有错，还在查找过程，请慎重尝试，如果有高手或者知道问题在哪的希望能够指点一下，谢谢！！！）

create or replace procedure selMsg_cursor
       (inid in qq.id%type)  --输入参数
as 
       cursor cur is select name from qq where id = inid;
       outname qq.name%type;
begin
       open cur;
       fetch cur into outname;
       if cur%found then 
       dbms_output.put_line('name:'||outname);  --游标结果集只有一列
       else
       dbms_output.put_line('没有符合条件的结果')
       end if;
       close cur;
       end;

已改正以上错误，代码如下，橘黄色为修改处！

 

create or replace procedure selMsg_cursor
       (inid in number)  --输入参数的类型不能用那种形式 切记切记
as
       cursor cur is select name from qq where id = inid;
       outname qq.name 

%type;
begin
       open cur;
       fetch cur into outname;
       if cur%found then
       dbms_output.put_line('name:'||outname);  --游标结果集只有一列
       else
       dbms_output.put_line('没有符合条件的结果');
       end if;
       close cur;
       end;
       
call selMsg_cursor(2)

 

 

 

截止到目前我发现有三种方法可以在存储过程中给变量进行赋值：

1、直接法     :=     

   如：v_flag := 0; 

2、select into

   如：假设变量名为v_flag，select count（*） into v_flag from students;

3、execute immediate 变量名（一般是sql的select语句） into 变量名

   如：

   v_sqlfalg   := 'select count(*) from user_tables where table_name='''||v_tablename || '''';
   execute immediate v_sqlfalg into v_flag;

   其中，v_tablename也是变量 

当然2和3实质是一样的。只不过3中的select语句是根据变量生成的。 

在存储过程中，是不能直接写select语句的。