What is SQL in Oracle?
This article will explain in detail what SQL is in Oracle, and the editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.
100,-modify the table structure, add fields-
Alter table stu add (addr varchar2 (29))
101.-Delete fields--
Alter table stu drop (addr)
102.-modify the length of the table field-
Alter table stu modify (addr varchar2 (50));-- the changed length must be able to accommodate the original data
103.-Delete constraints-
Alter table stu drop constraint constraint name
104.-modify the table structure to add constraints-
Alter table stu add constraint stu_class_fk foreign key (class) references class (id)
105.-data dictionary table-
Desc dictionary
-- the data dictionary table has two fields table_name comments
-- table_name mainly stores the names of data dictionary tables
Comments is mainly a description of this data dictionary table.
-View all tables, views, and constraints under the current user-data dictionary table user_tables
Select table_name from user_tables
Select view_name from user_views
Select constraint_name from user-constraints
106.-Index-
Create index idx_stu_email on stu (email);-- create an index on the email field of the stu table: idx_stu_email
107,-Delete index-
Drop index index_stu_email
108.-View all indexes-
Select index_name from user_indexes
109.-create View-
Create view v$stu as selesct id,name,age from stu
The role of views: simplify queries to protect some of our private data, through the view can also be used to update data, but we generally do not use the disadvantage: to maintain the view
110,-create sequence-
Create sequence seq;-- creation sequence
Select seq.nextval from dual;-- looks at the next value of the seq sequence
Drop sequence seq;-- deletion sequence
111,-three paradigms of the database-
(1) it is necessary to have a primary key and the columns can not be separated.
(2) partial dependency cannot exist: when multiple fields are joined together as a primary key, a field that is not a primary key cannot be partially dependent on a field in the primary key.
(3) there can be no transitive dependency.
= PL/SQL==
112.-output helloworld-- on the client
Set serveroutput on;-- defaults to off, and sets it to on so that Oracle can output data on the client.
113 、 begin
Dbms_output.put_line ('helloworld')
End
/
Assignment and output of 114,114,114-pl/sql variables
Declare
V_name varchar2 (20);-- declare variable v_name the declaration of the variable begins with v _
Begin
V_name: = 'myname'
Dbms_output.put_line (v_name)
End
/
115,115,-pl/sql exception handling (divisor 0)-
Declare
V_num number: = 0
Begin
V_num: = 2/v_num
Dbms_output.put_line (v_num)
Exception
When others then
Dbms_output.put_line ('error')
End
/
116,-declaration of variables-
Binary_integer: integers, mainly used to count rather than represent field types, are more efficient than number
Number: numeric type
Char: fixed length string
Varchar2: variable length string
Date: date
Long: string, longest 2GB
Boolean: Boolean type. You can take a value of true,false,null--, preferably give an initial value.
Declaration of-variables, using the'% type' attribute
Declare
V_empno number (4)
V_empno2 emp.empno%type
V_empno3 v_empno2%type
Begin
Dbms_output.put_line ('Test')
End
/
-- using the% type attribute, you can automatically change the declaration of the variable according to the type of the table field, saving the trouble of maintenance, and the% type attribute can be used on the variable.
-Table variable type (table represents an array)-
Declare
Type type_table_emp_empno is table of emp.empno%type index by binary_integer
V_empnos type_table type_table_empno
Begin
V_empnos (0): = 7345
V_empnos (- 1): = 9999
Dbms_output.put_line (v_empnos (1))
End
119,119,-Record variable type
Declare
Type type_record_dept is record
(
Deptno dept.deptno%type
Dname dept.dname%type
Loc dept.loc%type
);
Begin
V_temp.deptno:=50
Vaaaaaaaaaaaaa'
Vandals temp.locore.locuper.Varititemp.
Dbms_output.put_line (v temp.deptno | |''| v temp.dname)
End
120.-declare record variables using% rowtype
Declare
V_temp dept%rowtype
Begin
V_temp.deptno:=50
Vaaaaaaaaaaaaa'
Vandals temp.locore.locuper.Varititemp.
Dbms_output.put_line (v temp.deptno | |''| v temp.dname)
End
121.-sql%count counts the number of records updated by a sql statement
122. the application of-sql sentence
Declare
V_ename emp.ename%type
V_sal emp.sal%type
Begin
Select ename,sal into viciename.vascal from emp where empno = 7369
Dbms_output.put_line (v_ename | |''| | v_sal)
End
Application of 123,- pl/sql statement
Declare
V_emp emp%rowtype
Begin
Select * into v_emp from emp where empno=7369
Dbms_output_line (v_emp.ename)
End
124. application of-pl/sql statement
Declare
V_deptno dept.deptno%type: = 50
V_dname dept.dname%type: = 'aaa'
V_loc dept.loc%type: = 'bj'
Begin
Insert into dept2 values (vascal deptnore.vedname.vandame.vantloc)
Commit
End
-ddl language, data definition language
Begin
Execute immediate 'create table T (nnn varchar (30) default' 'aura')'
End
126. application of-if else
Declare
V_sal emp.sal%type
Begin
Select sal into v_sal from emp where empno = 7369
If (v_sal
< 2000) then dbms_output.put_line('low'); elsif(v_sal >2000) then
Dbms_output.put_line ('middle')
Else
Dbms_output.put_line ('height')
End if
End
127,-Loop = do while
Declare
I binary_integer: = 1
Begin
Loop
Dbms_output.put_line (I)
I: = I + 1
Exit when (I > = 11)
End loop
End
128,-while
Declare
J binary_integer: = 1
Begin
While j
< 11 loop dbms_output.put_line(j); j:=j+1; end loop; end; 129、---------------------for begin for k in 1..10 loop dbms_output.put_line(k); end loop; for k in reverse 1..10 loop dbms_output.put_line(k); end loop; end; 130、-----------------------异常(1) declare v_temp number(4); begin select empno into v_temp from emp where empno = 10; exception when too_many_rows then dbms_output.put_line('太多记录了'); when others then dbms_output.put_line('error'); end; 131、-----------------------异常(2) declare v_temp number(4); begin select empno into v_temp from emp where empno = 2222; exception when no_data_found then dbms_output.put_line('太多记录了'); end; 132、----------------------创建序列 create sequence seq_errorlog_id start with 1 increment by 1; 133、-----------------------错误处理(用表记录：将系统日志存到数据库便于以后查看) -- 创建日志表: create table errorlog ( id number primary key, errcode number, errmsg varchar2(1024), errdate date ); declare v_deptno dept.deptno%type := 10; v_errcode number; v_errmsg varchar2(1024); begin delete from dept where deptno = v_deptno; commit; exception when others then rollback; v_errcode := SQLCODE; v_errmsg := SQLERRM; insert into errorlog values (seq_errorlog_id.nextval, v_errcode,v_errmsg, sysdate); commit; end; 133---------------------PL/SQL中的重点cursor(游标)和指针的概念差不多 declare cursor c is select * from emp; --此处的语句不会立刻执行，而是当下面的open c的时候，才会真正执行 v_emp c%rowtype; begin open c; fetch c into v_emp; dbms_output.put_line(v_emp.ename); --这样会只输出一条数据 134将使用循环的方法输出每一条记录 close c; end; 134----------------------使用do while 循环遍历游标中的每一个数据 declare cursor c is select * from emp; v_emp c%rowtype; begin open c; loop fetch c into v_emp; (1) exit when (c%notfound); --notfound是oracle中的关键字，作用是判断是否还有下一条数据 (2) dbms_output.put_line(v_emp.ename); --(1)(2)的顺序不能颠倒，最后一条数据，不会出错，会把最后一条数据，再次的打印一遍 end loop; close c; end; 135------------------------while循环，遍历游标 declare cursor c is select * from emp; v_emp emp%rowtype; begin open c; fetch c into v_emp; while(c%found) loop dbms_output.put_line(v_emp.ename); fetch c into v_emp; end loop; close c; end; 136--------------------------for 循环，遍历游标 declare cursor c is select * from emp; begin for v_emp in c loop dbms_output.put_line(v_emp.ename); end loop; end; 137---------------------------带参数的游标 declare cursor c(v_deptno emp.deptno%type, v_job emp.job%type) is select ename, sal from emp where deptno=v_deptno and job=v_job; --v_temp c%rowtype;此处不用声明变量类型 begin for v_temp in c(30, 'click') loop dbms_output.put_line(v_temp.ename); end loop; end; 138-----------------------------可更新的游标 declare cursor c --有点小错误 is select * from emp2 for update; -v_temp c%rowtype; begin for v_temp in c loop if(v_temp.sal < 2000) then update emp2 set sal = sal * 2 where current of c; else if (v_temp.sal =5000) then delete from emp2 where current of c; end if; end loop; commit; end; 139-----------------------------------procedure存储过程(带有名字的程序块) create or replace procedure p is--这两句除了替代declare，下面的语句全部都一样 cursor c is select * from emp2 for update; begin for v_emp in c loop if(v_emp.deptno = 10) then update emp2 set sal = sal +10 where current of c; else if(v_emp.deptno =20) then update emp2 set sal = sal + 20 where current of c; else update emp2 set sal = sal + 50 where current of c; end if; end loop; commit; end; --执行存储过程的两种方法： （1）exec p;(p是存储过程的名称) （2） begin p; end; / 140-------------------------------带参数的存储过程 create or replace procedure p (v_a in number, v_b number, v_ret out number, v_temp in out number) is begin if(v_a >Vladib) then
V_ret: = Ventra
Else
V_ret: = vsigb
End if
V_temp: = v_temp + 1
End
141mura-call 140
Declare
Vallea number: = 3
Veterb number: = 4
V_ret number
V_temp number: = 5
Begin
P (vyoga, vumbb, v_ret, v_temp)
Dbms_output.put_line (v_ret)
Dbms_output.put_line (v_temp)
End
142-Delete stored procedures
Drop procedure p
143Mutual-create a function to calculate personal income tax
Create or replace function sal_tax
(v_sal number)
Return number
Is
Begin
If (v_sal < 2000) then
Return 0.10
Elsif (v_sal
