oracle 入门使用

启动服务器

sqlplus /nolog;

连接服务器

connect sys as sysdba;

推出服务器

exit;

设置

#若命令行要输出DBMS_PUT.PUT_LINE 的内容。却没输出设置
set serveroutput on;

引用输出包极其过程进行输出

DBMS_OUTPUT.PUT_LINE(' The First Name of the
Employee is ' || f_name);

创建表

create table users (
    id number(10) primary key,
    username varchar2(20) not null,
    passwords varchar2(20) not null
);
/

查询表信息

desc users;

创建且替换过程

# hello示例
create or replace procedure hello
as
begin
    DBMS_OUTPUT.PUT_LINE('hello');
end;
/

# 选择指定单行
create or replace procedure selIdUser(
    uid in number)
is
cur users%ROWTYPE;
begin 
select id, username, passwords into cur 
from users
where id = uid;
     DBMS_OUTPUT.PUT_LINE(
        'id = ' || cur.id || 
        ' username = ' || cur.username || 
        ' password = ' || cur.passwords);
end;
/

# 查询多行
create or replace procedure selAllUser
is
cursor users_cursor is select id, username, passwords from users;
begin 
    for cur in users_cursor
        loop
    DBMS_OUTPUT.PUT_LINE(
        'id = ' || cur.id || 
        ' username = ' || cur.username || 
        ' password = ' || cur.passwords);
        end loop;
end;
/

#查询总人数
create or replace procedure selAllUserCOUNT
is
cnt number;
begin 
    select count(id) into cnt from users;
    DBMS_OUTPUT.PUT_LINE('总人数 = ' || cnt);
end;
/

删除过程

drop procedure users;