Oracle beginner knowledge point summary

Summary of oracle beginner knowledge points 1. dba, sysdba, and sysoper are different roles. roles can include system permissions, object permissions, and roles. dba does not include sysdba and sysoper permissions. sysdba and sysoper are two types of system permissions; it is best to use sys and system to manage these two types of permissions. Do not assign them to common users. 2. publicpublic of oracle can be understood as a set of all users; grant dba to public; equivalent to all users are dba; --- plsql programming: plsql can be written: process, function, trigger; process, function, trigger in oracle; plsql is a very powerful database process language; process, function can be called in java; advantages of plsql: improve performance; (traditional jdbc database connection, execute SQL, compiled into a database language, returned values, closed connections, multiple calls for multiple compilations, write the process only once, and use it as needed;) modular design ideas; (paging process, order process, transferred (A large SQL statement is required, and now a process name is required) high security; (oracle database protection, fields and everything are in the database) disadvantages of plsql: portability is not good. (oracle language writing processes cannot be used in DB2.) 1. create or replace procedure p1 isbegininsert into mytable values ('gao', '2013'); end;/2. stored Procedure programming error. Check error: show errors; 3. how to execute the Stored Procedure exec p1; ------ write specification 1. single line comment: -- Multi-line comment :/*... */(same in java) 2. naming Convention of the symbol: variable: v _ (v_val) constant: c-(c_val) cursor:-cursor (emp_cursor) exception: e _ (e_error) 3. A block structure pl/SQL block consists of three parts: the definition part and the execution part, Partial declear/* definition part-definition constant, variable, exception, complex data types */begin/* Execution Section-pl/SQL statements to be executed and SQL statements */must have exception/* exception Handling Section-handle various running errors */optional public static void main (Sting args) {int a; // define part try {a ++; // execute part} catch (Exception e) {// Exception Handling part} -- only contains the block set serveroutput on for fast execution -- open the output service; begindbms_output.put_line ('hello, World! '); End;/-- pl/SQL block that contains the Declaration and execution; declarev_passwd varchar2 (10); v_name varchar2 (20); beginselect passwd, name into v_passwd, v_name from mytable where name = & name; -- the address character indicates that the variable is accepted from the console; dbms_output.put_line (v_name | 'password:' | v_passwd); end; /-- declarev_passwd varchar2 (10); v_name varchar2 (20); beginselect passwd, name into v_passwd, v_name from mytable where name = & name; -- the address character indicates that the variable is accepted from the console; dbms_output. Put_line (v_name | 'password:' | v_passwd); exceptionwhen no_data_found thendbms_output.put_line ('the user name entered does not exist! '); End; ---- the process is used to execute specific operations. It can have input and output parameters, -- eg4: create procedure changepasswd (name1 varchar2, passwd1 varchar2) isbegin -- update mytable set passwd = passwd1 where name = name1; end according to the username; -- execute call changepasswd in plsql ('wucaiming', '123 '); -- call the storage project in java // load the driver Class. forName ("oracle. jdbc. driver. oracleDriver "); // obtain the Connection c = DriverManager. getConnection ("jdbc: oracle: thin: @ 192.168.100.145: 1521: SDZC", "omgap", "om Gap "); // create CallableStatementCallableStatement cs = c. prepareCall (" {call changepasswd (?,?)} "); //? Assign cs. setString (1, "wucaiming"); cs. setString (2, "123"); // execute cs.exe cute ();/* how to use the process return value? */Function is used to return specific data. -- Eg5: create or replace function f_returnpasswd (name1 varchar2) return varchar2 ispwd varchar2 (30); beginselect passwd into pwd from mytable where name = name1; return pwd; end; -- call var a varchar2 (30) in plsql; call f_returnPasswd ('wucaiming') into: a; the package is used to logically combine processes and functions, it consists of a package specification and a package body. -- Create package p_package isprocedure changepasswd (name1 varchar2, passwd1 varchar2); function f_returnpasswd (name1 varchar2) return varchar2; end; -- create or replace package body p_package isprocedure changepasswd (name1 varchar2, passwd1 varchar2) isbegin -- update mytable set passwd = passwd1 where name = name1; end based on the user name; function f_returnpasswd (name1 varchar2) return varchar2 ispwd varchar2 (30 ); Beginselect passwd into pwd from mytable where name = name1; return pwd; end; -- how to call the process and function in the package? -- Call the process exec p_package.f_returnpasswd ('wucaiming'); -- call the function var a varchar2 (30); call p_package.f_returnpasswd ('wucaiming ') into: a; trigger: A trigger is an implicit stored procedure. There are four types of variables in the trigger pl/SQL: 1. scalar type (scalar) 2. composite 3. reference 4. clob (large object) 1. scalar v_name varchar2 (20); -- variable-length string v_sal number (6, 2); --- 9999.99 ~ + 9999.99v _ sql2 number (6, 2): = 5.4; -- defines a decimal number and assigns an initial value of 5.4, ": =" equivalent to "=" value assignment operator in java; v_hiredate date; -- Define a date data v_valid boolean not null default false; eg1: -- double the 'wucaiming' salary and output set serveroutput on; declarev_name varchar2 (30 ); -- v_name mytable. name % type; v_sal_add number (6, 2); beginselect name, sal * 2 into v_name, v_sal_add from mytable where name = & name; dbms_output.put_line ('name: '| v_name | 'salary:' | v_sal_add); end; 2. Composite variable pl/SQL record pl/SQL table nested table varray -- pl/SQL record is similar to the structure (or class) set serveroutput on in advanced languages; -- defines a plsql record type; declare type mytable_record_type is record (v_name mytable. name % type, v_passwd mytable. passwd % type, v_sal mytable. sal % type); -- defines a variable whose type is the record type defined above; r_record mytable_record_type; beginselect name, passwd, sal into r_record from mytable where name = 'wucaiming '; dbms_output.put_line ('name:' | r_record.v_name | 'password: '| R_record.v_passwd | 'salary: '| r_record.v_sal); end; -- pl/SQL table is equivalent to an array in advanced languages. The array subscript can be negative; set serveroutput on; declare type mytale_table_type is table of mytable. name % type index by binary_integer; t_table mytale_table_type; beginselect name, passwd, sal into t_table (-1), t_table (0), t_table (1) from mytable where name = 'wucaiming '; dbms_output.put_line ('name:' | t_table (-1) | 'password: '| t_table (0) | 'salary: '| t_table (1); end; 3. A select statement is not required for defining a cursor based on a cursor variable. A select statement is required for using a cursor. declaretype mytable_cur Sor is ref cursor; test_cursor mytable_cursor; v_name mytable. name % type; v_passwd mytable. passwd % type; v_sal mytable. sal % type; begin -- the cursor is associated with a select statement; open test_cursor for select name, passwd, sal from mytable where name = 'wucaiming'; loopfetch test_cursor into v_name, v_passwd, v_sal; -- determines whether the cursor is null; exit when test_cursor % notfound; dbms_output.put_line ('name: '| v_name | 'password:' | v_passwd | 'salary: '| v_sal); end loop; end; pl/s Advanced usage of ql: (can write Advanced procedures, order process module) control structure of pl/SQL: if... thenif... then... elseif... then... elsif... then... elseeg: create or replace procedure p_addsal (name1 varchar2) isv_sal mytable. sal % type; beginselect sal into v_sal from mytable where name = name1; if v_sal <2000 thenupdate mytable set sal = 200 where name = name1; end if; end; L2 condition Branch: eg: create or replace procedure p_addsal (name1 varchar2) isv_sal mytable. sal % type; beginse Lect sal into v_sal from mytable where name = name1; if v_sal <200 thenupdate mytable set sal = 200 where name = name1; elseupdate mytable set sal = v_sal + 100 where name = name1; end if; end; layer-3 branch: eg: create or replace procedure p_addsal (name1 varchar2) isv_sal mytable. sal % type; beginselect sal into v_sal from mytable where name = name1; if v_sal> = 300 thenupdate mytable set sal = v_sal + 150 where name = name1; elsif v_sal> = 200 thenupdate mytable set sal = v_sal + 100 where name = name1; elseupdate mytable set sal = v_sal + 50 where name = name1; end if; end; loop statement: loop structure: create or replace procedure p_add (name1 varchar2, passwd1 varchar2, sal number) isv_num number: = 1; beginloopinsert into mytable values (name1, passwd1, sal); exit when v_num = 3; -- end condition v_num: = v_num + 1; end loop; end; -- Use A while loop; create or replace procedure p_add (name1 varchar2, Passwd1 varchar2, sal number) isv_num number: = 1; beginwhile v_num <= 3 loopinsert into mytable values (name1, passwd1, sal); v_num: = v_num + 1; end loop; end; -- for Loop (not recommended) beginfor I in reverse 1 .. 10 loopinsert into users values (I, 'gao'); end loop; end; -- the sequence control statement goto statement (not recommended in general) is always jumping; null statement; no operation will be performed, and the control will be passed directly to the next statement; --- Before the paging process -- 1. create table book (bookId number, bookName varchar2 (50), bookPubl varchar2 (50); -- 2. create Stored procedure (only input parameters) create or replace procedure p_splitpage (bookId in number, bookName in varchar2, bookPubl in varchar2) isbegininsert into book values (bookId, bookName, bookPubl); end; -- 3. create or replace procedure p_splitpage1 (bookId1 in number, bookName1 out varchar2, bookPubl1 out varchar2) isbeginselect bookName, bookPubl into bookName1, bookPubl1 from book where bookId = bookId1; end; -- 4. create a stored procedure (Output parameter is a list) -- create a package create or replace package testpackage astype test_cursor is ref cursor; end testpackage; -- create or replace procedure p_splitpage2 (name1 in varchar2, c_cursor out testpackage. test_cursor) isbeginopen c_cursor for select * from mytable where name = name1; end; page: (input Table name, number of records displayed per page, current page, total number of records returned, total number of pages, and returned result set) -- The paging template sqlselect * from (select rownum rn, t. * from (select * from mytable) t where r Ownum <= 10) where rn> = 6; -- create a package create or replace package testpackage astype test_cursor is ref cursor; end testpackage; -- Define the stored procedure create or replace procedure p_pagesp.pdf (tableName in varchar2, -- table name pageSize in number, -- number of records displayed on each page pageNow in number, -- myRows out number on the current page, -- total number of returned records myPageCount out number, -- total number of pages p_cursor out testpackage. test_cursor -- returned result set) is -- defines an SQL statement v_ SQL varchar2 (1000); v_begin number: = (PageNow-1) * pageSize + 1; v_end number: = pageNow * pageSize; beginv_ SQL: = 'select * from (select rownum rn, t. * from (select * from' | tableName | ') t where rownum <=' | v_end | ') where rn> =' | v_begin; -- The cursor and SQL are associated with the returned result set open p_cursor for v_ SQL; -- total number of returned records v_ SQL: = 'select count (*) from' | tableName; execute immediate v_ SQL into myRows; -- total number of returned pages; if mod (myRows, pageSize) = 0 thenmyPageCount: = myRows/pageSize; elsemyPageCount: = MyRows/pageSize + 1; end if; if p_cursor % notfound thenclose p_cursor; end if; end; Exception Handling: declarev_name mytable. name % type; beginselect name into v_name from mytable where name = & a; exceptionwhen no_data_found thendbms_output.put_line ('data not found! '); End; 1. case_no_found -- this condition is not defined. 2. cursor_already_open -- repeated opening of the cursor 3. invaild_cursor -- the cursor is not opened. Open the closed cursor. 4. invaild_number -- sal = sal + '000000' 5. too_many_rows -- the number of returned records is more than 6 rows. zero_divide -- 2/07. value_error -- v_name varchar2 (1), which is too small for other predefined exceptions. login_denide -- illegal user login 2. not_logged_on -- execute the dml operation statement without logging in. 3. storage_error -- exceeds the memory space or the memory is damaged. 4. time_on_resource -- wait for the resource to time out. The custom exception create or replace procedure ex_test (name1 varchar2) ismyex exception; beginupdate mytable set sal = sal + 100 where name = name1; if SQL % notfound thenraise myex; end I F; predictionwhen myex thendbms_output.put_line ('no user updated '); end; view: (virtual table, not actually exists in the Database! Dynamic generation. Some data is taken from different tables to form a virtual table.) differences between views and tables: 1. tables occupy disk space, while views do not occupy disk space. 2. you cannot add an index to a view. 3. view can simplify complex query 4. view helps improve security to create a view: (MAP employees of sal <400 of mytable table to this View) create or replace view myview as select * from mytable where sal <= 400; Delete View: drop View myview; trigger-when a user logs in/out or operates a Data Object/or performs ddl operations, a stored procedure is hidden and executed. This special stored procedure is called a trigger. -- Raise a question -- when a user logs on, the username is automatically recorded, and when the user logs on to the ip address, the record is automatically saved to another table for regret recovery; -- A table cannot be operated on Sunday. -- trigger classification dml trigger -- add, delete, and modify trigger ddl trigger -- data definition trigger (create tabel | view .. dorp) system trigger -- System-related trigger (login, exit, start database, close database) -- Quick Start of trigger -- when adding data to a table, a message is prompted to add a data entry; set serviceoutput on; create or replace trigger trigger_test afterinsert on omgap. mytable -- Statement-Level Trigger; begindbms_output.put_line ('added record '); end; -- when multiple data entries are modified in a table, the system prompts' modified data multiple times! '(The difference between a row-Level trigger and a statement-Level trigger) set serviceoutput on; create or replace trigger trigger_test afterinsert on omgap. mytablefor each row -- row-Level trigger begindbms_output.put_line ('added record '); end; -- a table cannot be operated on create or replace trigger trigger_testbefore delete on omgap on Sunday. mytablebeginif to_char (sysdate, 'day') in ('sunday', 'saturday') then -- although the prompt is displayed, the delete operation cannot be blocked. A program error must be thrown to prevent deletion; dbms_output.put_line ('data cannot be deleted on weekends! '); End if; end; -- the deletion operation can be blocked only when a program error is thrown; create or replace trigger trigger_testbefore delete on omgap. mytablebeginif to_char (sysdate, 'day') in ('sunday', 'satur') thendbms_output.put_line ('data cannot be deleted on weekends! '); -- The first parameter range is-20000 ~ -20099raise_application_error (-20001, 'Sorry, data cannot be deleted on weekends! '); End if; end; -- the Sunday operation can be inert, update, and delete, which is the create or replace trigger triggerf_test before insert or update or delete onomgap. mytablebegincasewhen inserting thenraise_application_error (-20003, 'cannot add'); when updating failed (-20002, 'cannot Update'); when deleting thenraise_application_error (-20001, 'cannot delete '); end case; end; -- use: old and: new -- display the employee's salary before and after modification -- how to ensure that the employee's salary cannot be lower than the original salary create or replace trigger trigger_testbefore update on omgap. mytablefor each rowbeginif: new. sal <: old. sal thenraise_application_error (-20004, 'original salary cannot be lower than current salary '); elsedbms_output.put_line ('original salary:' |: old. sal | 'current salary: '|: new. sal); end if; end; -- use a trigger to back up a table create table mytable_bak (name varchar2 (30), passwd varchar2 (30), sal number (6, 2 )); create or replace trigger trigger_testbefore delete on omgap. mytablefor each rowbegininsert into mytable_bak values (: old. name,: old. passwd,: old. sal); end; -- system trigger ora_login_user // return logon userName ora_sysevent // return system event name -- Record logon and exit trigger; -- log on to create table log_table (userName varchar2 (30 ), logon_time date, logoff_time date, address varchar2 (30); create or replace trigger trigger_test after logon on databasebegininsert into log_table (userName, logon_time, address) values (values, sysdate, values ); end; -- exit create or replace trigger trigger_test before logoff on databasebegininsert into log_table (userName, logoff_time, address) values (ora_login_user, sysdate, ora_client_ip_address); end;