oracle pl sql programming

The main content of this article is as follows: 6.1 Introduction 6.2 create a function 6.3 stored procedures 6.3.1 creation process 6.3.2 call a stored procedure 6.3.3 AUTHID 6.3.4 PRAGMA AUTONOMOUS_TRANSACTION 6.3.5 procedure 6.3.6 deletion process and Function 6.3.7 comparison of processes and functions 6.1 IntroductionProcedures and functions (In addition, packages and triggers) are named PL/SQL blocks (

Pl/SQL block that only contains the execution part Set serveroutput on -- enable output options Begin Dbms_output.put_line ('hello '); End; Instructions: Dbms_output is a package provided by Oracle locks (similar to java). This package contains some processes. put_line is a process in the dbms_output package. The set serveroutput on Clause indicates that t

Oracle Database Programming: PL/SQL program control structure: condition control: if statement: if condition 1 then if condition 1 is true execute the statement here ...... Elsif condition 2 then if condition 2 is true, execute the statement here ...... If both conditions 1 and 2 of else are not true, execute the state

; --------------------------------------------------------------------Cursor------------------------------------------------- --explicit cursors, reading employee information Declare cursorCur_emp (var_jobinch varchar2:='salesman') is SelectEmpno,ename,sal fromscott.empwhereJob=Var_job; Type Record_emp isrecord (Var_empno scott.emp.empno%type, Var_ename scott.emp.ename%type, var_sal scott.emp.sal%type); Emp_row record_emp; begin OpenCur_emp ('MANAGER'); FetchCur_emp intoEmp_row; whileC

cascadeINSERT into Person3 values (3, ' Yuan ', ' Weixiang ', Varray_phone (' 12345 ', ' 34567 ', ' 56789 ', ' 34567 ', ' 56789 '));4. Cursors? Description: In PL/SQL programming, it is sometimes necessary to do a row-by-line processing of the query return result set, which requires that the query return the result set to a memory area, in order to be able to pe

taxV_tax_sal:=v_sal*v_tax_rate;--OutputDbms_output.put_line (' Name: ' | | v_ename| | ' Wages ' | | V_sal | | ' Tax: ' | | V_tax_sal);End--Composite Type composite----PL/SQL records a struct similar to a high-level language--Define a plsql record type Emp_record_typeDeclareType Emp_record_type is record (Aname emp.ename%type,salary emp.sal%type,title emp.job%type);--Defines a variable sp_record the type of

= ' sql ';--DeleteDelete from table (select Ss.score from Stuinfo ss where Stuid = 3) twhere t.subname= ' SQL ';The similarities and differences between----variable arrays and nested tables----------------Same point:1, are abstract types2. Can be used as a data type for a column in a table (record and fast table are not data types for columns)Different points:1. The mutable array itself is stored in the or

the number of elements in the variable-length array. Alter type varray_phonemodify limit 2 cascade -- an error is reported. The varray limit can only be increased. Alter type varray_phonemodify limit 5 cascade Insert into person3 values (3, 'yuanyuan ', 'weixiang', varray_phone ('000000', '000000', '000000', '000000', '000000 ')); 4. cursor Introduction: In the PL/SQL program design, you sometimes need to

was a problem here, I gave Scott that dbms_pipe permission, forgot to enter the user, directly with the SYS run, and then quitUsing Scott to run is not, error, and then should be used in the time of SYS to create a pipeline, Scott does not use this. Then I changed another name.But why can't I quit ...DECLARE pipename VARCHAR2 (n): = ' signaler1 '; Result INTEGER: = Dbms_pipe.create_pipe (Pipename); BEGIN dbms_pipe.pack_message (' Stop '); end;/This is the one that stopped, but it's not good. Lo

Label:The seventh chapter is also quite miscellaneous, see not very clear. Although I finished it, I felt that I had not got too many things and had to go back to see them. See this CHR function, The original is to turn the number into ASCII code, such as Chr (10) is the carriage return.BEGIN Dbms_output.Put_Line (' Gogo ' | |CHR (10) | | ' Tot '); end;/Use here | | To connect characters, or you can use Concat to connect. However, Concat has only two parameters, that is, connecting two s

Tags: Oracle for whileSELECT statement1. If...then statementsGrammar:If Condition_expression: Represents a conditional expression whose value is true when the program executes the PL/SQL statement below if;If the value is False, the program skips the statement following the IF statement and executes the statements immediately after the end If.The statement to exe

ondeclare vnum number;begin vnum:=1/0; --handling Exception exception when zero_divide then Dbms_output.put_line (' exception: by 0 '); end;/Operation Result:10. Custom ExceptionsSet Serveroutput on;--Custom Exception declare -declares an exception type my_exception exception; begin -Throws an exception raise My_ Exception; Exception --Exception handling when My_exception and then Dbms_output.put_line (' Custom exception '); end;Operation Result: Copyright NOTICE: This arti

PL/SQL Basic structurePL/SQL data type Numeric type: Number (p,s), Pls_integer, Binary_integerCharacter types: CHAR, NCHAR, VARCHAR2, NVARCHAR2, LONGDay Type: DateBoolean Type: BooleanDefine data type: typeType Data type: Oracle allows you to define Recode, TABLEPL/SQL Const

=spno;End-----3. How to call in Java---1. Create CallableStatement CS =ct.preparecall ([Call Sp_pro9 (?,?)]);----Cs.setint (1,10);----Cs.registeroutparameter (2,oracle.jdbc.oracletypes.cursor);--run--cs.execute ();--Get the result set/*resultset rs= (ResultSet) Cs.getobject (2);while (Rs.next ()) {....}*/---------------------Exception Handling---------Case_not_foundData_not_foundCursor_already_openDup_val_on_index Unique index repeatedInvaild_cursor run operation on an illegal cursor for example

later found that it is not at all, and that the error Dup_val_on_index, his book is also wrong.Here is an anonymous blockDate.sqlDECLARE l_date date;BEGIN l_date: = SYSDATE-5; Dbms_output. Put_Line (l_date); end;/I still don't know how to call that process, although I tried it before, but I didn't remember it.I'm going to try to write a call on my own, I write a process for printing time, and then call it in an anonymous block.Get_date.sqlCREATE OR REPLACE PROCEDURE get_date (num_in in number)

match.Then I wrote an example:CREATE OR REPLACE PROCEDURE give_bonus (emp_no_id in number, bonus in number) AUTHID Current_userisbegin dbms_output.Put_Line (emp_no_id); Dbms_output. Put_Line (bonus); END give_bonus;/This is followed by the use of the case expressiondeclarel_salnumber; beginselectsalintol_salfromemp whereempno=7839;dbms_output. Put_Line (l_sal); give_bonus (7839, CASE whenl_sal>=1000andl_salSo it's actually an expression, and then the expression gets a result.After the introduct

Today, I am working on an OA report. I use a stored procedure to calculate some data. When doing a for xxx in xxxx loop end loop; loop, I never knew it was Mao. I can't catch the no_data_found exception in for any more. Once this no_data_found Oracle occurs, it automatically jumps out of the for loop, but I don't know what went wrong, that was depressing for an hour. Shit. Later I remembered that pl/

Today, I am working on an OA report. I use a stored procedure to calculate some data. When doing a for XXX in xxxx loop end loop; loop, I never knew it was Mao. I cannot catch the no_data_found exception in for any more. Once this no_data_found occurs Oracle will automatically Jump Out Of The for loop, but I don't know where the error occurred. It was a depressing hour. Shit. Later I remembered that PL