Oracle -- SQL syntax Summary

Oracle -- SQL syntax Summary

-- Statement block declare v_name varchar2 (30): = 'jack' -- defines the variable begin select v_name from dual; exception when others then dbms_output.put_line ('exception occurred '); end; -- if judge declare v_num1 number; v_num2 number; v_result varchar2 (10); begin if v_num1 is null or v_num2 is null then v_result: = 'undefined'; elsif v_num1> v_num2 then v_result: = 'num1 is big '; else v_result: = 'num2 is big'; end if; end -- case statement declare grade char: = 'a'; remark varchar2 (20 ); begin case grade when 'a 'then remark = 'is Excellent'; when 'B' then remark = 'is good'; end case; end; -- for Loop declare total integer: = 0; begin for I In 1 .. 19 loop total: = total + 1; end loop; end; -- loop declare v_count integer: = 1; begin loop v_count: = v_count + 1; if v_count> = 10 then exit; end if; end loop; end; -- while loop declare v_num1: = 10; while v_num1> 1 loop v_num1: = v_num1 + 1; end loop; end; -- dynamic SQL -- execute immediate statement execute immediate dynamic_ SQL [into {define_variable [, define_variable2]... | recode}] [using [in | out | in out] bind_argument [, [in | out | in out] bind_argument2]...] [{returning | return}] into bind_argument [, bind_argument2]...]; dynamic_ SQL: indicates an SQL statement or the block string expression define_variable of the pl/SQL statement. It indicates the variable value recode of the selected column: indicates a user-defined record or % rowtype record stored in the selected row. % bind_argument: the input bind_argument parameter is an expression. Its value is passed to the dynamic SQL statement and the bind_argument parameter is output, an into variable that stores the return value of a dynamic SQL statement: When performing a single-row query, specify the variable or record that the value is assigned to the column. For each value retrieved from the query, the into clause must have a variable or field returing compatible with the corresponding type: it can only be used for DML operations, and returning into is used to specify the variable or record of the value to be retrieved, each return value of a DML statement must have a compatible variable or field using in the returing into clause: Use the using clause to bind parameters in a dynamic SQL statement. Specify in to indicate that only input is allowed, "out" indicates the output, and "in out" indicates the input and output of parameters. The default value is "in". For DML, an "out" parameter is placed in the returning into clause. If it is a using clause and a return into clause, then, the using clause can only contain the in parameter execute immediate statement and can only use data queries that process a single row, but cannot process multi-row data queries. -- execute DDL begin execute immediate 'create table temp_table (id integer, name varchar2 (20) '; end; declare plsql varchar2 (200); begin plsql: = 'Clare using IME varchar2 (20); ''begin select to_char (sysdate, ''dd-mm-yyyy day '') into upload IME from dual; dbms_output.put_line ('' current date: ''| upload IME) end; '; execute immediate plsql; end; -- bind the variable and execute dynamic sqldeclare plsql varchar2 (200); t_name varchar2 (20): = 'job'; t_id integer: = '000000'; begin plsql: = 'insert into temp_table values (: 1,: 2) '; execute immediate plsql using t_name, t_id; end; -- pl/SQL exception Handling declare exception_name // definition exception raise exception_name // trigger exception // handle exception when exception_name then statements; declare temp_ex exception; t_num integer; begin select count (id) into t_num from temp_table where id = '000000'; if t_num> = 1 then raise temp_ex; end if; DBMS_OUTPUT.PUT_LINE ('this user does not exist '); exception when temp_ex then DBMS_OUTPUT.PUT_LINE ('this user already exists '); end; -- declare the cursor cursor_name [{parameter [, parameter]...}] [return return_type] is selectSqlopen cursor_name // open the cursor fetch cursor_name into variable_list; // extract the cursor close cursor_name // close the cursor -- Common cursor value declare fname varchar2 (20 ); lname varchar2 (20); cursor c_student is select firstname, lastname from student where id = '000000'; begin open c_student; if c_student % NOTFOUND then dbms_output.put_line ('no record found '); else fetch c_student into firstname, lastname; dbms_output.put_line (fname | ''| lname); end if; close c_student; end; -- loop/while/for loop values declare fname varchar2 (20); lname varchar2 (20); cursor t_student is select firstname, lastname from student where id <1001; begin for stus in t_student loop fname: = t_student.firstname; lname: = t_student.lastname; dbms_output.put_line ('name: '| fname | ''| lname); end loop; end; -- create proc | procedure pro_name [{@ parameter data type} [= default value] [output], {@ parameter data type} [= default value] [output],...] as SQL _statements -- create or replace procedure showInfo as select * from student begin showInfo ('job'); -- execute the Stored procedure end; -- create or replace procedure showInfo (Major in varchar2) as // declare an input parameter select * from student where major = Major; begin showInfo ('job '); -- execute the Stored Procedure end; drop showInfo -- delete stored procedure -- function syntax create [or replace] function name [(parameter 1 [{in | out | in out} type parameter [{in | out | in out} type...]) return type {is | as}] function _ body; -- defines the function create or replace function getCount (Major in varchar2) return number as f_count number; // declare the return type begin select count (*) into f_count from students where major = 'magor' return f_count; // return the return statement end; -- use the declare v_count number function; begin v_count: = getCount ('music'); dbms_output.put_line (v_count); end; drop function getCount -- delete function -- create Baotou create or replace package emp_package as -- declare a stored procedure my_proc (optional varchar2; lend_name varchar2; ledn_sex varchar2; major varchar2;); end emp_package; -- create or replace package body emp_package as -- procedure Implementation (lend_num varchar2; lend_name varchar2; lend_sex varchar2; major varchar2;) is begin insert into emp (lnum, lname, lsex, major) values (lend_num, lend_name, lend_sex, major); end my_proc; end emp_package; -- call package_name.type_name; begin callback ('2017080', 'job ', 'male', 'music'); end; -- Define the view create or replace view v_student as select * from student; select * from v_student; // query the view drop view v_student; // Delete View -- sequence create sequence seq_name [increment by n] [start with n] [maxvalue n | nomaxvalue] // nomaxvalue: the maximum value specified for the ascending order is 1027, maximum Value in descending order:-1 [minvalue n | mominvalue] // nominvalue: specify the minimum value as 1 for ascending order, minimum-1026 in descending order -- modify the sequence alter sequence seq_name [increment by n] [maxvalue n | nomaxvalue] [minvalue n | mominvalue] -- delete the sequence drop sequence seq_name; create sequence seq_Id minvalue 1 maxvalue 1000 start with 1 increment by 1 cache 20; -- database chain create [public] datebase link link_name connect to username identified by password using 'servername/serverurl '; select * from tablename @ link_name; create database link link_goods connect to scott identified scott using '(description = (address_list = (address = (protocol = tcp) (host = 10.0.0.34) (port = 1521) (connect_data = (service_name = Orcl) '; select * from goods @ link_goods; -- index create [unique] index [schema.] index_name on table_name (col_name) [tablespace ts] [storage s] [pctfree pf] [nosort ns] schema: indicates the Oracle mode. By default, the current account tablespace: Index storage tablespace storage: storage parameter pctfree: Percentage of free space in the index data block nosort: not sorted (stored in ascending order, no need to sort again) create unique index I _id on student (id ); -- alter index [unique] index index_name [initrans n] [maxtrans n] rebuild [storage <storage>] initrans: number of entries to the initial transaction simultaneously accessed in a block, n is a decimal integer maxtrans: the maximum number of transaction entries simultaneously accessed in a block, and n is a decimal integer rebuild: re-create an index based on the original index structure, create index data storage after the full table scan is performed again: store data, same as create index alter index I _id rebuild storage (initial 1 M next 512 k) -- delete index drop index schema. index_name;